Re: Order by and index

On Fri, Aug 27, 2010 at 6:30 PM, Mladen Gogala <mgogala(at)vmsinfo(dot)com> wrote:
> It looks like the Postgres optimizer cannot use indexes for "order by"
> conditions. The query that made me conclude this, looks like this:

It looks to me like the reason that you have that heapsort step is
because of your WHERE clause involving the "created_at" timestamp.

> explain analyze
> select "document#" from moreover_documents
> where created_at<TIMESTAMP '2010-07-01'
> order by "document#"
> limit 10;

And your comparison showing Oracle to be faster doesn't use this WHERE clause:

> SQL> set autotrace on explain;
> SQL> select document# from (
>  2  select document# from moreover_documents
>  3  order by document#)
>  4  where rownum<=10;

Perhaps Oracle is smart enough to use indexes on "created_at" and
"document#" together to avoid a sort entirely, but your example
doesn't show this. Postgres should be able to use an Index Scan and
avoid that sort step if you don't involve "created_at":

Also, I'm not sure whether this would help in your case, but there was
some talk recently about implementing "Index Organized Tables" for
Postgres, borrowing from Oracle.

http://archives.postgresql.org/pgsql-hackers/2010-02/msg01708.php

Josh