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@myemma.com>
software development
emma(r)