Re: Two Index Questions

Josh Berkus <josh(at)agliodbs(dot)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=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