From: V S P <toreason@fastmail.fm>
To: pgdba@hush.com
To: pgsql-php@postgresql.org
Subject: Re: force re-planning of prepared statements?
Date: Mon, 29 Dec 2008 19:47:10 -0500
Message-ID: <1230598030.11642.1292231721@webmail.messagingengine.com> (raw)
In-Reply-To: <20081229221705.4464B20040@smtp.hushmail.com>
References: <20081229221705.4464B20040@smtp.hushmail.com>
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@hush.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@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-php--
V S P
toreason@fastmail.fm
--
http://www.fastmail.fm - Access all of your messages and folders
wherever you are
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: toreason@fastmail.fm, pgdba@hush.com
Subject: Re: force re-planning of prepared statements?
In-Reply-To: <1230598030.11642.1292231721@webmail.messagingengine.com>
References: <20081229221705.4464B20040@smtp.hushmail.com> <1230598030.11642.1292231721@webmail.messagingengine.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