Re: Weirdly pesimistic estimates in optimizer

David Kubečka <kubecka(dot)dav(at)gmail(dot)com> wrote:

> I have read the optimizer README file and also looked briefly at
> the code, but this seems to be something not related to
> particular implementation of algorithm (e.g. nested loop).
> Perhaps it's the way how cost estimates are propagated down

It could be as simple as not having tuned your cost factors to
accurately reflect the relative costs of different actions in your
environment. If you are using the default configuration, you might
want to try a few of the adjustments that are most often needed (at
least in my experience):

cpu_tuple_cost = 0.03
random_page_cost = 2
effective_cache_size = <50% to 75% of machine RAM>
work_mem = <machine RAM * 0.25 / max_connections>

You can SET these on an individual connection, one at a time or in
combination, and EXPLAIN the query to see the effects on plan
choice.

Other advice, not all of which matches my personal experience, can
be found here:

https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server

The best thing to do is experiment with different values with your
own queries and workloads to see what most accurately models your
costs (and thus produces the fastest plans).

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company