pg.ddx.io  pgsql-php@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: pgdba@hush.com
To: andrew@morphoss.com
Cc: pgsql-php@postgresql.org
Subject: Re: force re-planning of prepared statements?
Date: Tue, 30 Dec 2008 07:34:44 -0800
Message-ID: <20081230153444.DC59020040@smtp.hushmail.com> (raw)



On Mon, 29 Dec 2008 18:00:21 -0800 Andrew McMillan 
<andrew@morphoss.com> wrote:
>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.

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/




view thread (8+ messages)  latest in thread

Message-ID: <20081230153444.DC59020040@smtp.hushmail.com>
Permalink:  ../20081230153444.DC59020040@smtp.hushmail.com/
Also on:    postgresql.org/message-id/20081230153444.DC59020040@smtp.hushmail.com

 · 

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: pgdba@hush.com, andrew@morphoss.com
  Subject: Re: force re-planning of prepared statements?
  In-Reply-To: <20081230153444.DC59020040@smtp.hushmail.com>
  References: <20081230153444.DC59020040@smtp.hushmail.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