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

2009/11/22 Jonathan Blitz <jblitz(at)013(dot)net>

> 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