pg.ddx.io  pgsql-php@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Andrew McMillan <andrew@morphoss.com>
To: pgdba@hush.com
Cc: pgsql-php@postgresql.org
Subject: Re: force re-planning of prepared statements?
Date: Tue, 30 Dec 2008 15:00:21 +1300
Message-ID: <1230602421.5932.61.camel@happy.mcmillan.net.nz> (raw)
In-Reply-To: <20081229221705.4464B20040@smtp.hushmail.com>
References: <20081229221705.4464B20040@smtp.hushmail.com>

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.
------------------------------------------------------------------------





view thread (8+ messages)  latest in thread

Message-ID: <1230602421.5932.61.camel@happy.mcmillan.net.nz>
Permalink:  ../1230602421.5932.61.camel@happy.mcmillan.net.nz/
Also on:    postgresql.org/message-id/1230602421.5932.61.camel@happy.mcmillan.net.nz

 · 

reply

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 from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-php@postgresql.org
  Cc: andrew@morphoss.com, pgdba@hush.com
  Subject: Re: force re-planning of prepared statements?
  In-Reply-To: <1230602421.5932.61.camel@happy.mcmillan.net.nz>
  References: <20081229221705.4464B20040@smtp.hushmail.com> <1230602421.5932.61.camel@happy.mcmillan.net.nz>

* 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