Josh Berkus <josh@agliodbs.com> 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=1000width=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.00rows=1000 width=32)
(1 row)
regards, tom lane
