force re-planning of prepared statements?

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.h tml)

- 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/PnY6qxsbda5xAD52Aya1On7VD40YO QA3qlB2S2RuRhpQuc9Grmy1V/


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

Re: force re-planning of prepared statements?

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.h tml)
>
> - 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/PnY6qxsbda5xAD52Aya1On7VD40YO QA3qlB2S2RuRhpQuc9Grmy1V/
>
>
> --
> 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


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

Re: force re-planning of prepared statements?

On Mon, 2008-12-29 at 14:17 -0800, pgdba@hush.com wrote:
> 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.h tml)
>
> - 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.

I would imagine that there's some element of the supplied data which is
giving the planner some kind of unexpected selectivity, so the plan used
by the prepared statement is entirely the wrong one.

If you could post the statement itself we might have some useful
comment. Also consider asking on the pg-performance list, where these
sorts of questions are much more common, and people who really
understand query planning (i.e. Tom) are watching.

Have you tried to work out which parameter causes the difference in
performance? Also, does it make a difference if you call:

PDO::Statementexecute( array( $p1, $p2, ...) );

vs. using

PDOStatement::bindParam()

to bind them to named variables...

In general the 'prepare / execute / execute / ...' approach is
*supposed* to be faster, so if there is no special reason why you are
seeing bad performance the people on the 'performance' mailing list will
likely be very interested in your problem.

Regards,
Andrew McMillan.

------------------------------------------------------------ ------------
andrew (AT) morphoss (DOT) com +64(272)DEBIAN
You are confused; but this is your normal state.
------------------------------------------------------------ ------------



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

Re: 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
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.h tml)
>>
>> - 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/PnY6qxsbda5xAD52Aya1On7VD40Y OQA3qlB2
>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/PnY6qxsZwTcf7Oemn5WzFssWfYRzs 4nJk5s2I9IYZS8jYesUJITCb/


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

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
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
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=20
>a new one (unless a proxy is used) That means that prepare statement=20
>has a perfromance benefit if the same SQL is used more than once per=20
>session
>
>b) if prepare by itself takes long, than may be analyzing=20
>tables/updating statistics/vaccuming at least the tables involved in=20
>the query might help
>
>c) if b) does not help -- personally I would think that the problem is=20
>somewhere outside the 'prepare' call (unless there is a PG bug in that=20
>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.
>>=20
>> I have a pretty simple query that runs:
>>=20
>> - sub-second when issued from the command line (not prepared)
>>=20
>> - takes 200+ seconds when run from the command line inside a prepared

>> statement (eg.
>> http://www.postgresql.org/docs/8.2/interactive/sql-prepare.h tml)
>>=20
>> - takes over 200s when run from our application, within the pdo=20
>> prepared functions
>>=20
>> - 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.
>>=20
>> postgresql 8.2.11, php 5.2.1
>>=20
>> What are my options here? I would like to continue to use bind=20
>> variables to prevent sql injection, but I'd like to force a plan
>re-
>> parse for every single query.
>>=20
>> Any ideas?
>>=20
>>=20
>> --
>> Click to become a massage therapist and work for yourself.
>>
>http://tagline.hushmail.com/fc/PnY6qxsbda5xAD52Aya1On7VD40Y OQA3qlB2
>S2RuRhpQuc9Grmy1V/
>>=20
>>=20
>> --
>> Sent via pgsql-php mailing list (pgsql-php@postgresql.org) To make=20
>> 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.
=20
http://tagline.hushmail.com/fc/PnY6qxsZwTcf7Oemn5WzFssWfYRzs 4nJk5s2I9IYZ
S8jYesUJITCb/


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

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

Re: force re-planning of prepared statements?

On Mon, 29 Dec 2008 18:00:21 -0800 Andrew McMillan
wrote:
>On Mon, 2008-12-29 at 14:17 -0800, pgdba@hush.com wrote:
>> 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.h tml)
>>
>> - 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.
>
>I would imagine that there's some element of the supplied data
>which is
>giving the planner some kind of unexpected selectivity, so the
>plan used
>by the prepared statement is entirely the wrong one.
>
>If you could post the statement itself we might have some useful
>comment. Also consider asking on the pg-performance list, where
>these
>sorts of questions are much more common, and people who really
>understand query planning (i.e. Tom) are watching.
>
>Have you tried to work out which parameter causes the difference
>in
>performance? Also, does it make a difference if you call:
>
> PDO::Statementexecute( array( $p1, $p2, ...) );
>
>vs. using
>
> PDOStatement::bindParam()
>
>to bind them to named variables...
>
>In general the 'prepare / execute / execute / ...' approach is
>*supposed* to be faster, so if there is no special reason why you
>are
>seeing bad performance the people on the 'performance' mailing
>list will
>likely be very interested in your problem.
>
>Regards,
> Andrew McMillan.

Hi Andrew,

You are correct in assuming that there is some unexpected
selectivity. It hinges on the client id being used as the filter,
in this case, that id comprises only a very small fraction of the
table (448 rows out of 43352606). My question isn't really whether
or not the incorrect plan is being chosen, that part is pretty
obvious by looking at the plan, but more along the lines of what I
can do about it.
I'll try your suggestion about "PDO::Statementexecute" vs
"PDOStatement::bindParam()" and see if that makes a difference. If
not, I'll re-post on the pgsql-perf list.

Thanks!








--
Click for information on the top Adult Education programs. Advance your career.
http://tagline.hushmail.com/fc/PnY6qxtpbT1LMUDT2gOnN2zGYrIxT 88yeX6GMgSkDLj8DzNS2Ra9t/


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

Re: force re-planning of prepared statements?

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
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
>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/PnY6qxsbda5xAD52Aya1On7VD40 YOQA3qlB
>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/PnY6qxsZwTcf7Oemn5WzFssWfYRz s4nJk5s2
>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/PnY6qxtYiNyIRKECCe7Pu7B6fjD2B If9IyUJsJQywidOI0GCsmmyv/


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

Re: force re-planning of prepared statements?

On Tue, 2008-12-30 at 07:34 -0800, pgdba@hush.com wrote:
>
> Hi Andrew,
>
> You are correct in assuming that there is some unexpected
> selectivity. It hinges on the client id being used as the filter,
> in this case, that id comprises only a very small fraction of the
> table (448 rows out of 43352606). My question isn't really whether
> or not the incorrect plan is being chosen, that part is pretty
> obvious by looking at the plan, but more along the lines of what I
> can do about it.
> I'll try your suggestion about "PDO::Statementexecute" vs
> "PDOStatement::bindParam()" and see if that makes a difference. If
> not, I'll re-post on the pgsql-perf list.

It seems to me that if PDO can *only* do prepared statements with
positional/named parameters then that is a pretty serious bug.

Potentially it can be fixed in the PostgreSQL driver, or in a wrapper
layer, but there should really be a way of calling PDO::query with
positional parameters as well, without the need for a prepare, as you
can in DBI.

I haven't used PDO myself yet, and was hoping to switch to it in a month
or two, but I can imagine a lot of circumstances where this would be
problematic.

A couple of maybe helpful suggestions, from further reading the PDO
documentation:

- Perhaps PDO::BindValue gives a different effect (I wouldn't hold my
breath though).

- Perhaps a partial index on client id would solve your bad plan.

CREATE INDEX client_id_partial ON client_whatsist(client_id) WHERE
client_id > 0;

Or something like that. Then in your query you can add a static part to
the WHERE clause that says client_id > 0 AND ... so that gets picked as
a high selectivity index.

It's a complete hack, but it's about the most likely thing I can think
of to work. In fact it may just be sufficient to add that in there.

Of course equally you can put the " ... WHERE client_id =
".intval($client_id)." ..." into the statement directly, so the client
ID is part of the preparation (or use PDO::quote if it isn't an intval,
of course). Sometimes a bit of pragmatism is easier than tracking down
the purist's solution.

Regards,
Andrew McMillan.

------------------------------------------------------------ ------------
andrew (AT) morphoss (DOT) com +64(272)DEBIAN
Q: What's a WASP's idea of open-mindedness?
A: Dating a Canadian.

------------------------------------------------------------ ------------



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

Please help me for mysouce installation

This is a multi-part message in MIME format.

------=_NextPart_000_0045_01C96B05.014AFD30
Content-Type: multipart/alternative;
boundary="----=_NextPart_001_0046_01C96B05.014AFD30"


------=_NextPart_001_0046_01C96B05.014AFD30
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: 7bit

Hi,



I am installing mysource matrix



For installation step 2



I got following errors please can you help me.
















------=_NextPart_001_0046_01C96B05.014AFD30
Content-Type: text/html;
charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

xmlns:o=3D"urn:schemas-microsoft-com:office:office" =
xmlns:w=3D"urn:schemas-microsoft-com:office:word" =
xmlns=3D"http://www.w3.org/TR/REC-html40">


charset=3Dus-ascii">

Re: Please help me for mysouce installation

Hi,

This mailing list is about using PostgreSQL in PHP, rather than general
PHP support questions. I doubt that any of us have ever had to deal
with mysource matrix.

Regards,
Andrew McMillan.

On Wed, 2008-12-31 at 05:02 +0530, Umesh Wani wrote:
> Hi,
>
>
>
> I am installing mysource matrix
>
>
>
> For installation step 2
>
>
>
> I got following errors please can you help me.
>
>
>
>
>

------------------------------------------------------------ ------------
andrew (AT) morphoss (DOT) com +64(272)DEBIAN
If you stand on your head, you will get footprints in your hair.
------------------------------------------------------------ ------------



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

Re: Please help me for mysouce installation

Umesh Wani wrote:
> Hi,
> I am installing mysource matrix
> For installation step 2
> I got following errors please can you help me.


I've never heard of this product; but this might help:

http://www.google.ca/search?q=mysource+matrix+sys0209

Wow; first hit is the "MySource Matrix support forum"!

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