Thread: Usage of index in "ORDER BY" operations

I notice following behaviour:

I have a table "adress" with 100'000 adresses 
with columns (last_name, first_name, adressline1, etc.)
and an index last_name_idx on the column "last_name".

The query
"SELECT * FROM adress ORDER BY last_name LIMIT 20 OFFSET 0;"

takes forever and "EXPLAIN" shows that the index on last_name 
is not being used.

On the other hand 

"SELECT * FROM adress WHERE last_name > '' ORDER BY last_name LIMIT 20 OFFSET 0;"

returns the result immediately and "EXPLAIN" shows that the index on
last_name is being used.

So it seems that inserting a WHERE-clause, even if it doesn't do 
anything at all (i.e. doesn't reduce the result-set), 
is necessary to force the DB to make use of the index.

It even says in the FAQ under 4.9) 
"Indexes are not used for ORDER BY operations."

So I was wondering: 
Am I doing something wrong here or is the lesson simply: 
"Include all attributes of an index in a where-clause
if you want the indexes to be used"?

Is there a better way to tell the DB to make use of the index?

BTW: This seems to be true for indexes on multiple columns, i.e.
if having an index on (last_name, first_name) the query had to be:
SELECT * FROM adress WHERE last_name >'' AND first_name >'' 
ORDER BY last_name, first_name LIMIT 20 OFFSET 0;
Omitting the where-clause again leads to a very slow query.

I apologize if this has been discussed many times before ...

Thanks for your help.
Matt

Matthias Ackermann <matt@webcraft.ch> writes:
> So it seems that inserting a WHERE-clause, even if it doesn't do 
> anything at all (i.e. doesn't reduce the result-set), 
> is necessary to force the DB to make use of the index.

This is true in 6.5: it never even considers an indexscan plan unless
there is a WHERE clause that could make use of the index.  7.0 will
be smarter.  (Current CVS sources already know about making an indexscan
plan with no other purpose than to satisfy an ORDER BY; in fact they are
probably *too* eager to make use of an index, and will pick that method
even when a linear scan and explicit sort would be faster.  I need to
rejigger the cost estimates to be more realistic, especially by taking
LIMIT into account.)
        regards, tom lane