Re: again on index usage

Zeugswetter Andreas SB SD wrote:

> This is one of the main problems of the current optimizer which imho rather
> aggressively chooses seq scans over index scans. During high load this does
> not pay off.

Bingo ... dragging huge tables through the buffer cache via a sequential
scan guarantees that a) the next query sequentially scanning the same
table will have to read every block again (if the table's longer than
available PG and OS cache) b) on a high-concurrency system other queries
end up doing extra I/O, too.

Oracle partially mitigates the second effect by refusing to trash its
entire buffer cache on any given sequential scan. Or so I've been told
by people who know Oracle well. A repeat of the sequential scan will
still have to reread the entire table but that's true anyway if the
table's at least one block longer than available cache.

Of course, Oracle picks sequential scans in horribly and obviously wrong
cases as well. On one project over the summer I had a query Oracle
refused to use an available index on until I told it to do so explictly,
and when I did it sped up by a factor of about 100.

All optimizers will fail miserably for certain queries and datasets.

--
Don Baccus
Portland, OR
http://donb.photo.net, http://birdnotes.net, http://openacs.org