Re: force re-planning of prepared statements?

On Mon, 29 Dec 2008 18:00:21 -0800 Andrew McMillan
<andrew(at)morphoss(dot)com> wrote:
>On Mon, 2008-12-29 at 14:17 -0800, pgdba(at)hush(dot)com wrote:
>> Hi all, I am experiencing some performance issues that I think
>are
>> stemming from the PDO prepared statements functions.
>>
>> I have a pretty simple query that runs:
>>
>> - sub-second when issued from the command line (not prepared)
>>
>> - takes 200+ seconds when run from the command line inside a
>> prepared statement (eg.
>> http://www.postgresql.org/docs/8.2/interactive/sql-prepare.html)
>>
>> - takes over 200s when run from our application, within the pdo
>> prepared functions
>>
>> - runs sub-second from our application if I prepend the query
>with
>> "explain analyze" and looking at the resulting plan, it shows
>the
>> same plan as when it runs quickly from the command line.
>>
>> postgresql 8.2.11, php 5.2.1
>>
>> What are my options here? I would like to continue to use bind
>> variables to prevent sql injection, but I'd like to force a plan
>re-
>> parse for every single query.
>
>I would imagine that there's some element of the supplied data
>which is
>giving the planner some kind of unexpected selectivity, so the
>plan used
>by the prepared statement is entirely the wrong one.
>
>If you could post the statement itself we might have some useful
>comment. Also consider asking on the pg-performance list, where
>these
>sorts of questions are much more common, and people who really
>understand query planning (i.e. Tom) are watching.
>
>Have you tried to work out which parameter causes the difference
>in
>performance? Also, does it make a difference if you call:
>
> PDO::Statementexecute( array( $p1, $p2, ...) );
>
>vs. using
>
> PDOStatement::bindParam()
>
>to bind them to named variables...
>
>In general the 'prepare / execute / execute / ...' approach is
>*supposed* to be faster, so if there is no special reason why you
>are
>seeing bad performance the people on the 'performance' mailing
>list will
>likely be very interested in your problem.
>
>Regards,
> Andrew McMillan.

Hi Andrew,

You are correct in assuming that there is some unexpected
selectivity. It hinges on the client id being used as the filter,
in this case, that id comprises only a very small fraction of the
table (448 rows out of 43352606). My question isn't really whether
or not the incorrect plan is being chosen, that part is pretty
obvious by looking at the plan, but more along the lines of what I
can do about it.
I'll try your suggestion about "PDO::Statementexecute" vs
"PDOStatement::bindParam()" and see if that makes a difference. If
not, I'll re-post on the pgsql-perf list.

Thanks!

--
Click for information on the top Adult Education programs. Advance your career.
http://tagline.hushmail.com/fc/PnY6qxtpbT1LMUDT2gOnN2zGYrIxT88yeX6GMgSkDLj8DzNS2Ra9t/