From: Hannu Krosing <hannu@tm.ee>
To: Don Baccus <dhogaza@pacifier.com>
Cc: 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: Sat, 12 Jan 2002 17:08:24 +0200
Message-ID: <3C405168.8AEAAB8E@tm.ee> (raw)
References: <46C15C39FEB2C44BA555E356FBCD6FA42128AD@m0114.s-mxs.net>
<3C3F15A7.8000202@pacifier.com>
Don Baccus wrote:
>
> 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.
One radical way to get better-than-average cache behaviour in such
pathologigal casescases would be to discard a _random_ page instead of
LRU page (perhaps tuned to not not select from 1/N of pages on that are
MRU)
-------------
Hannu
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: hannu@tm.ee, 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: <3C405168.8AEAAB8E@tm.ee>
* 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