Skip site navigation (1) Skip section navigation (2)

Re: Help Me Understand Why I'm Getting a Bad Query Plan

From: Bryan Murphy <bmurphy1976(at)gmail(dot)com>
To: Robert Haas <robertmhaas(at)gmail(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Help Me Understand Why I'm Getting a Bad Query Plan
Date: 2009-03-25 17:53:35
Message-ID: 7fd310d10903251053g118ffd96xbe9902e0953765b4@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
On Wed, Mar 25, 2009 at 8:40 AM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> On Tue, Mar 24, 2009 at 11:43 PM, Bryan Murphy <bmurphy1976(at)gmail(dot)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

In response to

pgsql-performance by date

Next:From: Josh BerkusDate: 2009-03-25 21:55:40
Subject: Re: Help Me Understand Why I'm Getting a Bad Query Plan
Previous:From: Scott CareyDate: 2009-03-25 16:16:33
Subject: Re: Raid 10 chunksize

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group