On Fri, Aug 27, 2010 at 6:30 PM, Mladen Gogala <mgogala@vmsinfo.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