Re: Why DISTINCT ... DESC is slow?

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