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

Hi Jason,

Yeah, I came across that in the docs. Comparing the plans between
the two, I can definitively say that the problem is because the
inferior plan is using a date index, whereas the good plan is using
the "id" index for maximum selectivity. I don't think it has
anything to do with datatype either, because my test from the
command line (prepare'ing the statement) explicitly stated the
datatypes, and the bad plan was the same as what is selected via
the php application.

The indices in use (the date and id ones) are both normal b-trees.

Note also that this query is only the most obvious that I've
discovered, there are others that crop up with bad plans too (but
they are a bit more elusive).


On Tue, 30 Dec 2008 07:34:01 -0800 Jason Minion
<jason.minion@sigler.com> wrote:
>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/PnY6qxsbda5xAD52Aya1On7VD40YOQA3qlB
>2
>>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/PnY6qxsZwTcf7Oemn5WzFssWfYRzs4nJk5s2
>I9IYZ
>S8jYesUJITCb/
>
>
>--
>Sent via pgsql-php mailing list (pgsql-php@postgresql.org) To make
>changes to your subscription:
>http://www.postgresql.org/mailpref/pgsql-php
>
>--
>Sent via pgsql-php mailing list (pgsql-php@postgresql.org)
>To make changes to your subscription:
>http://www.postgresql.org/mailpref/pgsql-php

--
Be your own boss! Buy the business of your dreams.
 http://tagline.hushmail.com/fc/PnY6qxtYiNyIRKECCe7Pu7B6fjD2BIf9IyUJsJQywidOI0GCsmmyv/