On Thu, Aug 26, 2010 at 6:32 AM, Willy-Bas Loos <willybas@gmail.com> wrote:
> I have a colleague that is convinced that the website is faster if
> enable_seqscan is turned OFF.
> I'm convinced of the opposite (better to leave it ON), but i would like to
> show it, prove it to him.
> Now the first query we tried, would do a bitmap heap scan instead of a
> seqscan when the latter were disabled, to exclude about 50% of the records
> (18K of 37K records).
> The bitmap heap scan is 3% faster, so that didn't really plea my case.
> The thing is that by the time we tried it, the data had been cached, so
> there is no penalty for the use of the index (HDD retention on random
> access). So it's logical that the index lookup is faster, it looks up less
> records.
>
> Now i'm looking for a way to turn off the caching, so that we'll have a fair
> test.
>
> It makes no sense to me to set shared_buffers really low. Any tips?
setting shared_buffers low or high is not going to flush the cache. it
only controls whether the o/s cache or the pg buffer cache is used.
Disabling sequential scans is going to un-optimize a large class of
operations where a sequential scan is really the best choice of
action. In the old days of postgres, where the planner wasn't as
smart as it is today and some of the plan invalidation mechanics
weren't there, it wasn't that uncommon to disable them. Today, it's
really not a good idea unless you have a very specific reason to, and
even then I'd advise temporarily setting it and then setting it back
when your operation is done.
merlin