Re: disabling seq scans

On Thu, 1 Apr 2004, Marcus Andree S. Magalhaes wrote:

> > I'm assuming you've read the tuning guide here:
> >
> > http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html
> >
>
> Yes. I've read them. That's why I asked if we can deal with it in
> another way (sending an enable_seqscan = false) without creating
> colateral damage to other parts of the program that use the
> same (pooled) connection

Like Tom said in his post, using set local and transactions should take
care of it.

> > And upped shared buffers, effect_cache_size, etc...
> >
>
> Shared buffers was set up a couple weeks ago. Didn't change the
> default values to planner-specific variables. I guess the random page
> cost is set to 4. So, I think a bit weird a seq scan is choosen, after all
> (yes, we do constant vacuum analyze).

I'm not sure here, are you saying you didn't change effective_cache_size,
or or the cpu_*_cost vars? Or random_page_cost?

Basically, random pages cost random_page_cost*1 seq_scan_page, unless it's
likely to be in memory. If effective cache size is small, the planner
is less likely to assume that the data is in kernel cache and will tend to
choose a sequential scan. I.e. pages in kernel cache have an effective
random_page_cost of 1.something where the .something is the extra cost of
hitting the index in memory as well as the table.

So if effective_cache_size is set to the default, then the planner is
assuming a random page cost of 4 and picking a seq scan. Without lowering
the random_page_cost, setting the effective_cache_size to be larger should
result in more index scan usage.

If you've got lots o ram and / or a fast RAID setup, you might do well to
drop random_page_cost to something lower but still conservative, like 2.0
with no bad consequences. Note that the effect on the planner seems
non-linear, and noticeably so as you approach 1.0.