index usage in multi-column ORDER BY

From: "Adam Pritchard" <vesper76(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: index usage in multi-column ORDER BY
Date: 2005-07-08 17:06:23
Message-ID: 1120842383.389687.177180@o13g2000cwo.googlegroups.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Scott Marlowe 2005-07-08 17:15:20 Re: anybody used ERWin with pg?
Previous Message Mark Harrison 2005-07-08 16:58:55 anybody used ERWin with pg?