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