From: Tom Lane <tgl@sss.pgh.pa.us>
To: sthomas@peak6.com
Cc: Prodan, Andrei <Andrei.Prodan@awinta.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: 'Interesting' prepared statement slowdown on large table join
Date: Wed, 11 May 2011 12:38:01 -0400
Message-ID: <217.1305131881@sss.pgh.pa.us> (raw)
In-Reply-To: <4DCAB16F.2060300@peak6.com>
References: <D33393149C01874DB3D72FE46A1B268B0C784D@vpmail05-x.intra.ads-root.de>
<4DCAB16F.2060300@peak6.com>
Shaun Thomas <sthomas@peak6.com> writes:
> On 05/11/2011 06:08 AM, Prodan, Andrei wrote:
>> Index Scan using attr_name_value on big_table (cost=0.00..22.85
>> rows=4 width=7) (actual time=0.176..757.646 rows=914786 loops=1)
> Holy inaccurate statistics, Batman!
> Try increasing your statistics target for attr_name and attr_value in
> your big table.
Actually, the big problem here is probably not lack of statistics, but
the insistence on using a parameterized prepared plan in the first
place. If you're going to be doing queries where the number of selected
rows varies that much, using a generic parameterized plan is just a
recipe for shooting yourself in the foot. The planner cannot know what
the actual search values will be, and thus has no way of adapting the
plan based on how common those search values are. Having more stats
won't help in that situation.
Forget the prepared plan and just issue the query the old-fashioned way.
I do suspect that the reason the plan is flipping back and forth is
instability of the collected statistics, which might be improved by
increasing the stats target, or then again maybe not. But that's really
rather irrelevant.
regards, tom lane
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: tgl@sss.pgh.pa.us, sthomas@peak6.com, Andrei.Prodan@awinta.com
Subject: Re: 'Interesting' prepared statement slowdown on large table join
In-Reply-To: <217.1305131881@sss.pgh.pa.us>
* 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