Re: Query-Planer from 6seconds TO DAYS

Böckler Andreas wrote:

> I've played with seq_page_cost and enable_seqscan already, but you
> have to know the right values before SELECT to get good results ;)

The idea is to model actual costs on your system.  You don't show
your configuration or describe your hardware, but you show an
estimate of retrieving over 4000 rows through an index and describe a
response time of 4 seconds, so you must have some significant part of
the data cached.

I would see how the workload behaves with the following settings:

effective_cache_size = <your shared_buffers setting plus what the OS
                        shows as cached pages>
seq_page_cost = 1
random_page_cost = 2
cpu_tuple_cost = 0.05

You can set these in a session and check the plan with EXPLAIN. Try
various other important important queries with these settings and
variations on them. Once you hit the right factors to model your
actual costs, the optimizaer will make better choices without needing
to tinker with it each time.

-Kevin