DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=ymail.com;
	h=X-YMail-OSG:Received:X-Rocket-MIMEInfo:X-Mailer:References:Message-ID:Date:From:Reply-To:Subject:To:Cc:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding;
	b=JcoZ2KsodkqmG3PezFSgVuCa3EFXbIMdWJPIv+hWs3X1DLD36+F1aP8cKQ/d/kZ8iDzk6yy/y6Kiio5Fgm7AFCTclGoZc2yt0ofEQahwlxbnMCvv/fflD9m7Wm2ITcwPsAI6Cb3gfcGu/MKbLWA5a5ptq7Y/gDflAtWSUd3c6vU=;
References: <19B2B4ECC4724A438200C54257BA8B20@gmail.com>
	<1361566076.10158.YahooMailNeo@web162905.mail.bf1.yahoo.com>
	<D0D7098559DD470B9C2DE753E74B83E4@gmail.com>
Message-ID: <1361571474.31440.YahooMailNeo@web162901.mail.bf1.yahoo.com>
Date: Fri, 22 Feb 2013 14:17:54 -0800 (PST)
From: Kevin Grittner <kgrittn@ymail.com>
Reply-To: Kevin Grittner <kgrittn@ymail.com>
Subject: Re: Bad query plan with high-cardinality column
To: Alexander Staubo <alex@bengler.no>
Cc: "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
In-Reply-To: <D0D7098559DD470B9C2DE753E74B83E4@gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

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.=A0 (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.=A0 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.=A0 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


--=20
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance