Re: force re-planning of prepared statements?

If you peek at the notes section of the link to the documentation, it
states that sometimes the query plan for the prepared statement will be
inferior, because the values of the parameters are unavailable for the
planner to use. It may be more useful to try to make some explicit casts
or reorganize some of the WHERE/ON clauses to try and help the planner
ascertain types and/or values. It may also be the case that you have one
or more partial indexes on the tables, and those are not being used by
the planner because the conditions are not being met with the unknown
status of the parameters?

HTH,

Jason Minion
[email protected]


-----Original Message-----
From: [email protected]
[mailto:[email protected]] On Behalf Of [email protected]
Sent: Tuesday, December 30, 2008 9:25 AM
To: [email protected]; [email protected]
Subject: Re: [PHP] force re-planning of prepared statements?

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 <[email protected]>
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, [email protected] 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 ([email protected]) To make 
>> changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-php
>--
>  V S P
>  [email protected]
>
>--
>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/PnY6qxsZwTcf7Oemn5WzFssWfYRzs4nJk5s2I9IYZ
S8jYesUJITCb/


--
Sent via pgsql-php mailing list ([email protected]) To make
changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-php

-- 
Sent via pgsql-php mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-php