Thread: 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.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/PnY6qxsbda5xAD52Aya1On7VD40YOQA3qlB2S2RuRhpQuc9Grmy1V/
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/PnY6qxsbda5xAD52Aya1On7VD40YOQA3qlB2S2RuRhpQuc9Grmy1V/ > > > -- > 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
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.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. 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. ------------------------------------------------------------------------
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/
On Mon, 29 Dec 2008 18:00:21 -0800 Andrew McMillan <andrew@morphoss.com> 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.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. > >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/PnY6qxtpbT1LMUDT2gOnN2zGYrIxT88yeX6GMgSkDLj8DzNS2Ra9t/
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
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/
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.
------------------------------------------------------------------------
Hi,
I am installing mysource matrix
For installation step 2
I got following errors please can you help me.
Attachment
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.
------------------------------------------------------------------------
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"!