Postgres Pro
Downloads
Home   >   mailing lists

Thread: index usage in multi-column ORDER BY

index usage in multi-column ORDER BY

From
"Adam Pritchard"
Date:
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

Re: index usage in multi-column ORDER BY

From
Michael Fuhr
Date:
On Fri, Jul 08, 2005 at 10:06:23AM -0700, Adam Pritchard wrote:
> 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);

Not that it changes anything, but I assume you meant to create the
first index on c1.  I'll defer an explanation of the sequential
scan behavior to somebody who understands the planner better.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

Re: index usage in multi-column ORDER BY

From
Tom Lane
Date:
"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

Re: index usage in multi-column ORDER BY

From
"Adam Pritchard"
Date:
> Not that it changes anything, but I assume you meant to create
> the first index on c1.

Yes, I did -- that was just a typo in the example.  Thanks for pointing
it out.


Adam Pritchard