Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by postgresql.org (Postfix) with ESMTP id 1FCD8475928 for ; Fri, 19 Jul 2002 12:44:22 -0400 (EDT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.5/8.12.5) with ESMTP id g6JGi8Vk013607; Fri, 19 Jul 2002 12:44:08 -0400 (EDT) To: josh@agliodbs.com Cc: pgsql-sql@postgresql.org Subject: Re: Two Index Questions In-reply-to: <200207190933.59283.josh@agliodbs.com> References: <200207190933.59283.josh@agliodbs.com> Comments: In-reply-to Josh Berkus message dated "Fri, 19 Jul 2002 09:33:59 -0700" Date: Fri, 19 Jul 2002 12:44:07 -0400 Message-ID: <13606.1027097047@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200207/300 X-Sequence-Number: 8678 Josh Berkus writes: > Q: If you group a table by multiple colums, e.g. > SELECT t1.A, t1.B, t1.C, MAX(t1.G) > FROM t1 > GROUP BY t1.A, t1.B, t1.C > Then would a multi-column index on A, B, C be faster than seperate indexes > on A, B and C? An indexscan can only use one index, so separate indexes would be completely worthless for this query. An index on A,B,C is potentially useful, but in most cases I think the planner will prefer an explicit sort anyway if there's no WHERE clause. > Q: In PostgreSQL 7.0, there was an issue that indexes where never consulted > for DESC alpha sorts. Has this been resolved? If so, does one need to > create any special indexes to take advantage of indexes for DESC sorts? Yes; no. regression=# create table foo (f1 text); CREATE TABLE regression=# create index fooi on foo(f1); CREATE INDEX regression=# explain select * from foo order by f1; QUERY PLAN --------------------------------------------------------------------- Index Scan using fooi on foo (cost=0.00..52.00 rows=1000 width=32) (1 row) regression=# explain select * from foo order by f1 desc; QUERY PLAN ------------------------------------------------------------------------------ Index Scan Backward using fooi on foo (cost=0.00..52.00 rows=1000 width=32) (1 row) regards, tom lane