Re: Why DISTINCT ... DESC is slow?

From: "Brandon Aiken" <BAiken(at)winemantech(dot)com>
To: "Erik Jones" <erik(at)myemma(dot)com>, <pgsql-general(at)postgresql(dot)org>
Subject: Re: Why DISTINCT ... DESC is slow?
Date: 2006-12-12 16:41:22
Message-ID: F8E84F0F56445B4CB39E019EF67DACBA3C565F@exchsrvr.winemantech.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

If you have, say, an index(x, y) then that index will often double as an
index(x). It will generally not double as an index(y).

I'm not sure if that's how all RDBMSs work, but I'm pretty sure that's
how Oracle works. It never surprises me when PostgreSQL mimics Oracle.

--
Brandon Aiken
CS/IT Systems Engineer
-----Original Message-----
From: pgsql-general-owner(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Erik Jones
Sent: Tuesday, December 12, 2006 11:33 AM
To: Ron Johnson
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] Why DISTINCT ... DESC is slow?

Ron Johnson wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> On 12/12/06 01:28, Anton wrote:
>
>> Hi. With this table (about 800 000 rows):
>>
>> =# \d n_traffic
>> Table "public.n_traffic"
>> Column | Type | Modifiers
>>
--------------+-----------------------------+---------------------------
---
>> login_id | integer | not null
>> traftype_id | integer | not null
>> collect_time | timestamp without time zone | not null default now()
>> bytes_in | bigint | not null default
(0)::bigint
>> bytes_out | bigint | not null default
(0)::bigint
>> Indexes:
>> "n_traffic_collect_time" btree (collect_time)
>> "n_traffic_login_id" btree (login_id)
>> "n_traffic_login_id_collect_time" btree (login_id, collect_time)
>> Foreign-key constraints:
>> "n_traffic_login_id_fkey" FOREIGN KEY (login_id) REFERENCES
>> n_logins(login_id) ON UPDATE CASCADE
>> "n_traffic_traftype_id_fkey" FOREIGN KEY (traftype_id) REFERENCES
>> n_traftypes(traftype_id) ON UPDATE CASCADE
>>
>
> Why do you have indexes on both LOGIN_ID *and* LOGIN_ID +
COLLECT_TIME?
>
> ISTM that you can drop the LOGIN_ID index.
>
Hmm... Will queries that use only login_id and not collect_time use
the (login_id, collect_time) index?

--
erik jones <erik(at)myemma(dot)com>
software development
emma(r)

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tony Caduto 2006-12-12 16:44:34 Re: Running initdb from another process
Previous Message developer 2006-12-12 16:38:56 resetting sequence to cur max value