From: Don Baccus <dhogaza@pacifier.com>
To: Zeugswetter Andreas SB SD <ZeugswetterA@spardat.at>
Cc: Bruce Momjian <pgman@candle.pha.pa.us>
Cc: Daniel Kalchev <daniel@digsys.bg>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-hackers@postgresql.org
Subject: Re: again on index usage
Date: Fri, 11 Jan 2002 08:41:11 -0800
Message-ID: <3C3F15A7.8000202@pacifier.com> (raw)
References: <46C15C39FEB2C44BA555E356FBCD6FA42128AD@m0114.s-mxs.net>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: dhogaza@pacifier.com, ZeugswetterA@spardat.at, pgman@candle.pha.pa.us, daniel@digsys.bg, tgl@sss.pgh.pa.us
Subject: Re: again on index usage
In-Reply-To: <3C3F15A7.8000202@pacifier.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox