Received: from localhost (unknown [200.46.208.211])
	by mail.postgresql.org (Postfix) with ESMTP id D9FAB635104
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Wed, 25 Mar 2009 15:30:19 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024)
	with ESMTP id 43350-09-2
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Wed, 25 Mar 2009 15:30:07 -0300 (ADT)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6
Received: from yw-out-2324.google.com (yw-out-2324.google.com [74.125.46.31])
	by mail.postgresql.org (Postfix) with ESMTP id 6F7BD6327A8
	for <pgsql-performance@postgresql.org>;
	Wed, 25 Mar 2009 14:53:38 -0300 (ADT)
Received: by yw-out-2324.google.com with SMTP id 3so103948ywj.73
	for <pgsql-performance@postgresql.org>;
	Wed, 25 Mar 2009 10:53:36 -0700 (PDT)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma;
	h=domainkey-signature:mime-version:received:in-reply-to:references
	:date:message-id:subject:from:to:cc:content-type
	:content-transfer-encoding;
	bh=QfWAt/j6va00GU23C46UN/SpFmbiVRa5IGzEg0+JQpE=;
	b=TolT/3oKGFgEo4t+JbNnYMT/Uiho5tB9dAdcYOEzFADeGMHOscMhIq0F4dIc+kIUC4
	kgrbYk1tBmi/QJZP/HC+ZYi7IpL0BY/g4vhSyfjykKAs87SmL2mcRiXmx67rpHbjA9JO
	B+vhNAgMKxbLHKij4h+fA5seWQ69l//Wru/r4=
DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:in-reply-to:references:date:message-id:subject:from:to
	:cc:content-type:content-transfer-encoding;
	b=ugpy9b2b5DoFrl81iDsreV7Q6BDPExspUKUf/3JIvpQwpqPPTtRWY4ZOOCqCJBTQTh
	3Y7hDXSYMjemXEjEeuAUx3N+buRDOhOvi6OwV1fmdfahz6JIEZ9LmTggs5KJKXnZ1Gh/
	RtlKbR3psfZ4iT9AXCqybpo9fh12nSEEDodzc=
MIME-Version: 1.0
Received: by 10.90.93.17 with SMTP id q17mr5700538agb.60.1238003616041; Wed,
	25 Mar 2009 10:53:36 -0700 (PDT)
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>
Date: Wed, 25 Mar 2009 12:53:35 -0500
Message-ID: <7fd310d10903251053g118ffd96xbe9902e0953765b4@mail.gmail.com>
Subject: Re: Help Me Understand Why I'm Getting a Bad Query Plan
From: Bryan Murphy <bmurphy1976@gmail.com>
To: Robert Haas <robertmhaas@gmail.com>
Cc: pgsql-performance@postgresql.org
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=1 tagged_above=0 required=5 tests=AWL=-1.000,
	SARE_RAND_1=2
X-Spam-Level: *
X-Archive-Number: 200903/368
X-Sequence-Number: 33290

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