From: Kevin Grittner <kgrittn@ymail.com>
To: Alexander Staubo <alex@bengler.no>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: Bad query plan with high-cardinality column
Date: Fri, 22 Feb 2013 14:17:54 -0800 (PST)
Message-ID: <1361571474.31440.YahooMailNeo@web162901.mail.bf1.yahoo.com> (raw)
In-Reply-To: <D0D7098559DD470B9C2DE753E74B83E4@gmail.com>
References: <19B2B4ECC4724A438200C54257BA8B20@gmail.com>
<1361566076.10158.YahooMailNeo@web162905.mail.bf1.yahoo.com>
<D0D7098559DD470B9C2DE753E74B83E4@gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
Alexander Staubo <alex@bengler.no> wrote:
> On Friday, February 22, 2013 at 21:47 , Kevin Grittner wrote:
>> In my experience these problems come largely from the planner
>> not knowing the cost of dealing with each tuple. I see a lot
>> less of this if I raise cpu_tuple_cost to something in the 0.03
>> to 0.05 range.
>
> Is this something I can just frob a bit without worrying about it
> adversely impacting database performance across the board, or
> should I be very careful and do lots of testing on a staging box
> first?
If possible, I would recommend trying it with the old indexes and
seeing whether it causes it to choose the better plan. (Of course,
you're not going to beat the plan you get with the two-column index
for this query, but it might help it better cost the other
alternatives, which would be a clue that it makes your overall
costing model more accurate and would have a more general benefit.)
You can play with settings like this in a single session without
affecting any other sessions.
I always recommend testing a change like this in staging and
closely monitoring after deploying to production, to confirm the
overall benefit and look for any odd cases which might suffer a
performance regression. For this particular change, I have never
seen a negative effect, but I'm sure that it's possible to have a
scenario where it isn't helpful.
Personally, I have changed this setting many times and have often
noted that 0.02 was not enough to cause choice of an optimal plan,
0.03 was always enough to do it if adjusting this setting was going
to help at all, and boosting it to 0.05 never caused further plan
changes in the cases I tested. I have never seen such increases
cause less optimal plan choice.
If you try this, please post your results.
--
Kevin Grittner
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
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: kgrittn@ymail.com, alex@bengler.no
Subject: Re: Bad query plan with high-cardinality column
In-Reply-To: <1361571474.31440.YahooMailNeo@web162901.mail.bf1.yahoo.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