Re: index usage in multi-column ORDER BY - Mailing list pgsql-general

"Adam Pritchard" <vesper76@gmail.com> writes:
> CREATE TABLE t ( c1 INT, c2 INT );
> CREATE INDEX c1_idx ON t(c2);
> CREATE INDEX c2_idx ON t(c2);
> EXPLAIN SELECT * FROM t ORDER BY c1, c2;
> "Sort  (cost=69.83..72.33 rows=1000 width=8)"
> "  Sort Key: c1, c2"
> "  ->  Seq Scan on t  (cost=0.00..20.00 rows=1000 width=8)"

> If we instead use an index on c1, c2:
> CREATE INDEX c1_c2_idx ON t(c1, c2);
> "Index Scan using c1_c2_idx on t  (cost=0.00..52.00 rows=1000 width=8)"

The latter index matches the sort ordering requested by the query;
no single-column index can do so.

            regards, tom lane