Skip site navigation (1) Skip section navigation (2)

Re: force re-planning of prepared statements?

From: "V S P" <toreason(at)fastmail(dot)fm>
To: pgdba(at)hush(dot)com, pgsql-php(at)postgresql(dot)org
Subject: Re: force re-planning of prepared statements?
Date: 2008-12-30 00:47:10
Message-ID: 1230598030.11642.1292231721@webmail.messagingengine.com (view raw or flat)
Thread:
Lists: pgsql-php
Hi,
I do not have an answer for you

but, it is my understanding that
a) PHP drops the DB connection for every HTTP request
and then creates a new one (unless a proxy is used)
That means that prepare statement has a perfromance benefit
if the same SQL is used more than once per session

b) if prepare by itself takes long, than may be analyzing
tables/updating
statistics/vaccuming at least the tables involved in the query might
help

c) if b) does not help -- personally I would think that the problem
is somewhere outside the 'prepare' call (unless there is a PG bug in
that
functionality on that version of the server)




On Mon, 29 Dec 2008 14:17:05 -0800, pgdba(at)hush(dot)com said:
> 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.
> 
> Any ideas?
> 
> 
> --
> Click to become a massage therapist and work for yourself.
>  http://tagline.hushmail.com/fc/PnY6qxsbda5xAD52Aya1On7VD40YOQA3qlB2S2RuRhpQuc9Grmy1V/
> 
> 
> -- 
> Sent via pgsql-php mailing list (pgsql-php(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-php
-- 
  V S P
  toreason(at)fastmail(dot)fm

-- 
http://www.fastmail.fm - Access all of your messages and folders
                          wherever you are


In response to

pgsql-php by date

Next:From: Andrew McMillanDate: 2008-12-30 02:00:21
Subject: Re: force re-planning of prepared statements?
Previous:From: pgdbaDate: 2008-12-29 22:17:05
Subject: force re-planning of prepared statements?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group