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
