From: Bryan Murphy <bmurphy1976@gmail.com>
To: Robert Haas <robertmhaas@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Help Me Understand Why I'm Getting a Bad Query Plan
Date: Wed, 25 Mar 2009 12:53:35 -0500
Message-ID: <7fd310d10903251053g118ffd96xbe9902e0953765b4@mail.gmail.com> (raw)
In-Reply-To: <603c8f070903250640h775d4201w580b365ccf1669cb@mail.gmail.com>
References: <7fd310d10903231203r1bd37b8cn8baeadae83253f44@mail.gmail.com>
<7fd310d10903241521t645462bdpb3b8aee40b4f3ae4@mail.gmail.com>
<49C99732.8080703@agliodbs.com>
<7fd310d10903242043x28d24adenb729c0a77a21ce9@mail.gmail.com>
<603c8f070903250640h775d4201w580b365ccf1669cb@mail.gmail.com>
On Wed, Mar 25, 2009 at 8:40 AM, Robert Haas <robertmhaas@gmail.com> wrote:
> On Tue, Mar 24, 2009 at 11:43 PM, Bryan Murphy <bmurphy1976@gmail.com> wrote:
>> Looking through our configuration one more time, I see that at some
>> point I set random_page_cost to 2.0, but I don't see any other changes
>> to query planner settings from their default values.
>
> You don't by any chance have enable_<something> set to "off", do you?
>
> ...Robert
Alas, I wish it were that simple. Here's the whole query tuning
section in it's entirety. All sections with the comment #BPM just
before them are changes I made from the default value.
(sorry for any duplicates, I'm still having dropouts with gmail)
Thanks,
Bryan
#------------------------------------------------------------------------------
# QUERY TUNING
#------------------------------------------------------------------------------
# - Planner Method Configuration -
#enable_bitmapscan = on
#enable_hashagg = on
#enable_hashjoin = on
#enable_indexscan = on
#enable_mergejoin = on
#enable_nestloop = on
#enable_seqscan = on
#enable_sort = on
#enable_tidscan = on
# - Planner Cost Constants -
#seq_page_cost = 1.0 # measured on an arbitrary scale
#BPM
#random_page_cost = 4.0 # same scale as above
random_page_cost = 2.0
#cpu_tuple_cost = 0.01 # same scale as above
#cpu_index_tuple_cost = 0.005 # same scale as above
#cpu_operator_cost = 0.0025 # same scale as above
#BPM
#effective_cache_size = 128MB
effective_cache_size = 12GB
# - Genetic Query Optimizer -
#geqo = on
#geqo_threshold = 12
#geqo_effort = 5 # range 1-10
#geqo_pool_size = 0 # selects default based on effort
#geqo_generations = 0 # selects default based on effort
#geqo_selection_bias = 2.0 # range 1.5-2.0
# - Other Planner Options -
#BPM
#default_statistics_target = 10 # range 1-1000
default_statistics_target = 100
#constraint_exclusion = off
#from_collapse_limit = 8
#join_collapse_limit = 8 # 1 disables collapsing of explicit
# JOIN clauses
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: bmurphy1976@gmail.com, robertmhaas@gmail.com
Subject: Re: Help Me Understand Why I'm Getting a Bad Query Plan
In-Reply-To: <7fd310d10903251053g118ffd96xbe9902e0953765b4@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox