Re: reducing random_page_cost from 4 to 2 to force index scan

Sok Ann Yap  wrote:
 
> Anyway, the overhead of spawning 44 extra queries means that it is
> still better off for me to stick with the original query and tune
> PostgreSQL to choose index scan.
 
Maybe, but what is *best* for you is to tune PostgreSQL so that your
costs are accurately modeled, at which point it will automatically
pick the best plan for most or all of your queries without you
needing to worry about it.
 
If you set your effective_cache_size to the sum of shared_buffers
and what your OS reports as cache after you've been running a while,
that will help the optimizer know what size index fits in RAM, and
will tend to encourage index use.  If the active portion of your
data is heavily cached, you might want to set random_page_cost and
seq_page_cost to the same value, and make that value somewhere in
the 0.1 to 0.05 range.  If you have moderate caching, using 1 and 2
can be good.
 
If you're still not getting reasonable plans, please post again with
more information about your hardware along with the query and its
EXPLAIN ANALYZE output.
 
-Kevin