Re: Why is the query not using the index for sorting?

[Thom Brown <thombrown@xxxxxxxxx>]

2009/11/22 Jonathan Blitz <jblitz@xxxxxxx>

I have a table with a number of columns.

 

I perform

 

Select *

from table

order by a,b

 

There is an index on a,b which is clustered (as well as indexes on a and b alone).

I have issued the cluster and anyalze commands.

 

Nevertheless, PostgreSQL performs a Sequential Scan on the table and then performs a sort.

Am I missing something?

 

Jonathan Blitz

It depends on firstly the size of the table, and also the distribution of data in columns a and b.  If the stats for that table knows that the table has a natural order (i.e. they happen to be in roughly the order you've asked for them in), or the table isn't big enough to warrant using an index, then it won't bother using one.  It will pick whichever it believes to be the most efficient method.

Regards

Thom