Re: Two Index Questions

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