Re: 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)