Re: force re-planning of prepared statements?

On Mon, 2008-12-29 at 14:17 -0800, pgdba@hush.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.

------------------------------------------------------------------------
andrew (AT) morphoss (DOT) com                            +64(272)DEBIAN
            You are confused; but this is your normal state.
------------------------------------------------------------------------