> > 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"...
Thomas is correct on this. Vadim has run some tests, and with our
optimized psort() code, the in-memory sort is often faster than using
the index to get the tuple, because you are jumping all over the drive.
I don't remember, but obviously there is a break-even point where
getting X rows using the index on a table of Y rows is faster , but
getting X+1 rows on a table of Y rows is faster getting all the rows
sequentailly, and doing the sort.
You would have to pick only certain queries(no joins, index matches
ORDER BY), take the number of rows requested, and the number of rows
selected, and figure out if it is faster to use the index, or a
sequential scan and do the ORDER BY yourself.
Add to this the OFFSET capability. I am not sure how you are going to
get into the index and start at the n-th entry, unless perhaps you just
sequential scan the index.
In fact, many queries just get column already indexed, and we could just
pull the data right out of the index.
I have added this to the TODO list:
* Pull requested data directly from indexes, bypassing heap data
I think this has to be post-6.4 work, but I think we need to work in
this direction. I am holding off any cnfify fixes for post-6.4, but a
6.4.1 performance release certainly is possible.
But, you are correct that certain cases where in index is already being
used on a query, you could just skip the sort IF you used the index to
get the rows from the base table.
--
Bruce Momjian | http://www.op.net/~candle
maillist@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026