> I've done some tests and what I found out might be a bug in
> PostgreSQL's query optimizer.
> SELECT * FROM tab ORDER BY key;
> results in a sort->seqscan - I would have
> expected an indexscan!
Given that a table _could_ be completely unsorted on disk, it is
probably reasonable to suck the data in for a possible in-memory sort
rather than skipping around the disk to pick up individual tuples via
the index. Don't know if vacuum has a statistic on "orderness"...
> SELECT * FROM tab WHERE key > 'G' ORDER BY key;
> results in a sort->indexscan - hmmm.
> The last one is the query we would need in the web
> environment used over a cursor as in the example above. But
> due to the sort, the backend selects until the end of the
> table, sorts them and then returns only the first 20 rows
> (out of sorts result).
So you are saying that for this last case the sort was unnecessary? Does
the backend traverse the index in the correct order to guarantee that
the tuples are coming out already sorted? Does a hash index give the
same plan (I would expect a sort->seqscan for a hash index)?
- Tom