Why aren't two single-column indexes used in a two-column ORDER BY
clause? And is there some way to work around this?
For example:
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)"
In practice, we've found that the performance reflects these EXPLAIN
results, and the results don't change with a larger number of records
in the DB.
We would like to be able to allow the user to specify a sort with
secondary (and maybe tertiary, etc.) sort columns in our applications,
but the results above suggest that an explosion of indexes would be
required to support that. Is there some better way of doing it?
Thank you.
Adam Pritchard
