pg.ddx.io  pgsql-php@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: pgdba@hush.com
To: pgsql-php@postgresql.org
To: toreason@fastmail.fm
Subject: Re: force re-planning of prepared statements?
Date: Tue, 30 Dec 2008 07:24:36 -0800
Message-ID: <20081230152436.DE3D720040@smtp.hushmail.com> (raw)

All my data has been fully vacuumed and analyzed, so that isn't the 
problem. The problem is specifically that the incorrect plan is 
being selected, and I think that that is due to the re-use of a sub-
optimal plan.

On Mon, 29 Dec 2008 16:47:10 -0800 V S P <toreason@fastmail.fm> 
wrote:
>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/PnY6qxsbda5xAD52Aya1On7VD40YOQA3qlB2
>S2RuRhpQuc9Grmy1V/
>> 
>> 
>> -- 
>> 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

--
Click for free info on getting an MBA, $200K/ year potential.
 http://tagline.hushmail.com/fc/PnY6qxsZwTcf7Oemn5WzFssWfYRzs4nJk5s2I9IYZS8jYesUJITCb/




view thread (8+ messages)  latest in thread

Message-ID: <20081230152436.DE3D720040@smtp.hushmail.com>
Permalink:  ../20081230152436.DE3D720040@smtp.hushmail.com/
Also on:    postgresql.org/message-id/20081230152436.DE3D720040@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, toreason@fastmail.fm
  Subject: Re: force re-planning of prepared statements?
  In-Reply-To: <20081230152436.DE3D720040@smtp.hushmail.com>
  References: <20081230152436.DE3D720040@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