Re: force re-planning of prepared statements? - Mailing list pgsql-php

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
jason.minion@sigler.com


-----Original Message-----
From: pgsql-php-owner@postgresql.org
[mailto:pgsql-php-owner@postgresql.org] On Behalf Of pgdba@hush.com
Sent: Tuesday, December 30, 2008 9:25 AM
To: pgsql-php@postgresql.org; toreason@fastmail.fm
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 <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/PnY6qxsZwTcf7Oemn5WzFssWfYRzs4nJk5s2I9IYZ
S8jYesUJITCb/


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