Re: Planner cost adjustments

On Fri, 29 May 2015 09:39:00 -0400
Daniel Begin <jfd553(at)hotmail(dot)com> wrote:

> Hi all,
>
> Running some queries, I found that the planner often selects sequential scan
> instead of an index scan, even if the latter is way faster (one order of
> magnitude faster if I consider some tests I made by setting enable_seqscan =
> ON/OFF). How can I figure out what parameter I should adjust to get the
> planner select an appropriate plan that would better consider my DB setup?
>
> I had a look at
> http://www.postgresql.org/docs/9.3/static/runtime-config-query.html but at
> this point it is too much information for me;-) Any rules of thumb, recipes
> I could use to select which parameters I should look at first?

Here's the correct way to handle this:

1) As mentioned elsewhere, first take the time to ensure that your
cost estimate settings are reasonable for your hardware. See
section 18.7.2:
http://www.postgresql.org/docs/9.4/static/runtime-config-query.html
2) If #1 doesnt' fix it, don't change enable_seqscan. Run a bunch of
tests on the query(s) to see how well it performs. Then do
ANALYZE DATABASE $insert_name_here; and run all the tests again.
If performance/planning improves, then the analyze settings on
your server aren't aggressive enough. Make changes to related
config settings to fix.
3) If #2 doesn't uncover the problem, run EXPLAIN ANALYZE on all the
queries in your test. It takes a bit of understanding to do this
step, so you'll want to read up a bit and possibly ask questions
if you have trouble interpreting the output, but you're looking
for discrepencies between the estimated and actual times for any
particular table. If you find them, that tends to indicate that
you'll need to update statistics targets on any tables with the
problem. See:
http://www.postgresql.org/docs/9.4/static/planner-stats.html
4) If #3 doesn't fix things, then the PostgreSQL developers want to
know about your problem so they can improve the planner. First,
if there are queries that are causing you problems, update the
application to disable sequential scans _for_those_particular_
_queries_ so your application continues to trundle along but
don't disable sequential scans globally, as that may cause
other queries to perform badly. Once that immediate problem is
out of the way, put together a test case that demonstrates the
problem you're having (but doesn't contain any proprietary
data, etc) and post it to the list so the developers can figure
out what to do to improve Postgres.

Hope this helps.

--
Bill Moran <wmoran(at)potentialtech(dot)com>