Re: Why DISTINCT ... DESC is slow?

-----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.

- --
Ron Johnson, Jr.
Jefferson LA USA

Is "common sense" really valid?
For example, it is "common sense" to white-power racists that
whites are superior to blacks, and that those with brown skins
are mud people.
However, that "common sense" is obviously wrong.
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFFfsqwS9HxQb37XmcRAssSAKDYkQc0VlF7nuEcuMbe6Eub9T++egCgwNec
2ZT0LmH/iDaotUyKi/4hQjg=
=5y2t
-----END PGP SIGNATURE-----