Search Postgresql Archives

Re: Planner cost adjustments

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Fri, 29 May 2015 09:39:00 -0400
Daniel Begin <jfd553@xxxxxxxxxxx> 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@xxxxxxxxxxxxxxxxx>


-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general




[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux