From: Gregory Stark <stark@enterprisedb.com>
To: david@lang.hm
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Scott Carey <scott@richrelevance.com>
Cc: bricklen <bricklen@gmail.com>
Cc: Scott Marlowe <scott.marlowe@gmail.com>
Cc: Merlin Moncure <mmoncure@gmail.com>
Cc: pgsql-performance\@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: Poor plan choice in prepared statement
Date: Tue, 30 Dec 2008 23:23:41 -0500
Message-ID: <877i5hotv6.fsf@oxford.xeocode.com> (raw)
In-Reply-To: <alpine.DEB.1.10.0812301615280.16936@asgard.lang.hm>
References: <33b743250812301059u31ce82faw3d72a38280b37402@mail.gmail.com>
<b42b73150812301142w4483978ahfb819b0313150dbb@mail.gmail.com>
<dcc563d10812301209i59bf612eidc42fa408d4441ab@mail.gmail.com>
<33b743250812301214k7a0ec4fby614d693ec7915d3@mail.gmail.com>
<BDFBB77C9E07BE4A984DAAE981D19F961ACD5F388E@EXVMBX018-1.exch018.msoutlookonline.net>
<2796.1230678149@sss.pgh.pa.us>
<alpine.DEB.1.10.0812301615280.16936@asgard.lang.hm>
david@lang.hm writes:
> since there is not a pre-parsed interface for queries, it may make sense to
> setup a way to have the query pre-parsed, but not pre-planned for cases like
> this.
What would be more interesting would be to have plans that take into account
the outlier values and have alternative plans for those values.
One aspect that hasn't been discussed here is whether it's only certain
outlier arguments that cause Postgres to choose the poor plan for you or
whether it always chooses it for all the sets of arguments you actually use.
If it's the former then it's possible you're only being bitten if the first
time you prepare the query happens to have one of these outlier set of
parameters. I forget what version this went in but I think it was before 8.2
that Postgres started using the first set of arguments seen to plan the query.
This is usually an improvement over just guessing but if that first set is
unusual it could lead to strange results.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's On-Demand Production Tuning
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: stark@enterprisedb.com, david@lang.hm, tgl@sss.pgh.pa.us, scott@richrelevance.com, bricklen@gmail.com, scott.marlowe@gmail.com, mmoncure@gmail.com
Subject: Re: Poor plan choice in prepared statement
In-Reply-To: <877i5hotv6.fsf@oxford.xeocode.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