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@xxxxxxxxxxxxxx [mailto:pgsql-general-owner@xxxxxxxxxxxxxx] On Behalf Of Erik Jones Sent: Tuesday, December 12, 2006 11:33 AM To: Ron Johnson Cc: pgsql-general@xxxxxxxxxxxxxx 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@xxxxxxxxxx> software development emma(r) ---------------------------(end of broadcast)--------------------------- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
