pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedPoor plan choice in prepared statement
20+ messages / 10 participants
[nested] [flat]
* Poor plan choice in prepared statement
@ 2008-12-30 18:59 bricklen <bricklen@gmail.com>
0 siblings, 2 replies; 20+ messages in thread
From: bricklen @ 2008-12-30 18:59 UTC (permalink / raw)
To: pgsql-performance
Hi, I am re-posting my question here after trying to find a solution
in the PHP pgsql list with no luck.
I am experiencing some performance issues that I think are stemming
from prepared statements. I have a pretty simple query:
SELECT cl.idOffer,cl.idaffiliate ,cl.subid,cl.datetime
FROM click AS cl LEFT JOIN conversion AS co ON cl.clickgenerated =
co.clickgenerated
WHERE cl."date" >= '2008-12-01'
AND cl."date" <= '2008-12-23'
AND cl.idAffiliate = 49978
LIMIT 10;
Run times:
- sub-second when issued from the command line (not prepared)
- runs sub-second from our PHP 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.
- runs sub-second if I remove the "prepare" function from the PHP
application and execute the query with the variables in-line.
- takes 200+ seconds when run from the command line inside a prepared
statement (see query below)
- takes over 200s when run from our application, within the PHP PDO
prepared function (same as preparing from the command line)
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 (if necessary?)
Does anyone have any ideas?
postgresql 8.2.11, freshly loaded db, vacuumed and analyzed.
All indices in the plans are regular b-trees.
-- good plan, from non-prepared statement
--
dev=# EXPLAIN ANALYZE SELECT cl.idOffer,cl.idaffiliate ,cl.subid,cl.datetime
dev-# FROM click AS cl LEFT JOIN conversion AS co ON cl.clickgenerated
= co.clickgenerated
dev-# WHERE cl."date" >= '2008-12-01'
dev-# AND cl."date" <= '2008-12-23'
dev-# AND cl.idAffiliate = 49978
dev-# LIMIT 10;
Limit (cost=0.00..30.52 rows=10 width=48) (actual time=0.253..0.429
rows=10 loops=1)
-> Nested Loop Left Join (cost=0.00..2613524.29 rows=856328
width=48) (actual time=0.251..0.416 rows=10 loops=1)
-> Append (cost=0.00..77406.15 rows=19179 width=80) (actual
time=0.226..0.256 rows=10 loops=1)
-> Seq Scan on click cl (cost=0.00..15.07 rows=1
width=80) (actual time=0.001..0.001 rows=0 loops=1)
Filter: ((date >= '2008-12-01'::date) AND (date
<= '2008-12-23'::date) AND (idaffiliate = 49978))
-> Bitmap Heap Scan on click_00030 cl
(cost=406.48..77391.08 rows=19178 width=46) (actual time=0.222..0.241
rows=10 loops=1)
Recheck Cond: (idaffiliate = 49978)
Filter: ((date >= '2008-12-01'::date) AND (date
<= '2008-12-23'::date))
-> Bitmap Index Scan on
click_00030_idaffiliate_idx (cost=0.00..401.68 rows=21355 width=0)
(actual time=0.138..0.138 rows=484 loops=1)
Index Cond: (idaffiliate = 49978)
-> Index Scan using conversion_clickgenerated_idx on
"conversion" co (cost=0.00..131.67 rows=45 width=12) (actual
time=0.014..0.014 rows=0 loops=10)
Index Cond: ((cl.clickgenerated)::text =
(co.clickgenerated)::text)
Total runtime: 0.495 ms
-- bad plan, from prepared statement
--
dev=# prepare fooplan (date,date,int,int) as
dev-# SELECT cl.idOffer AS campaign, cl.idAffiliate AS affiliate,
cl.idCreative AS creative, cl.subid, cl.datetime
dev-# FROM click AS cl LEFT JOIN conversion AS co ON cl.clickGenerated
= co.clickGenerated
dev-# WHERE cl."date" >= $1
dev-# AND cl."date" <= $2
dev-# AND cl.idAffiliate = $3
dev-# LIMIT $4;
ERROR: prepared statement "fooplan" already exists
dev=# explain analyze execute fooplan ('2008-12-01','2008-12-23',49978,10);
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..15275.74 rows=3277 width=227) (actual
time=201350.494..201350.765 rows=10 loops=1)
-> Nested Loop Left Join (cost=0.00..152771.39 rows=32773
width=227) (actual time=201350.489..201350.748 rows=10 loops=1)
-> Append (cost=0.00..16031.56 rows=734 width=180) (actual
time=201350.438..201350.542 rows=10 loops=1)
-> Seq Scan on click cl (cost=0.00..15.07 rows=1
width=180) (actual time=0.001..0.001 rows=0 loops=1)
Filter: ((date >= $1) AND (date <= $2) AND
(idaffiliate = $3))
-> Bitmap Heap Scan on click_00028 cl
(cost=6701.97..8179.43 rows=372 width=177) (actual time=0.060..0.060
rows=0 loops=1)
Recheck Cond: ((idaffiliate = $3) AND (date >=
$1) AND (date <= $2))
-> BitmapAnd (cost=6701.97..6701.97 rows=372
width=0) (actual time=0.058..0.058 rows=0 loops=1)
-> Bitmap Index Scan on
click_00028_idaffiliate_idx (cost=0.00..1384.22 rows=74456 width=0)
(actual time=0.038..0.038 rows=86 loops=1)
Index Cond: (idaffiliate = $3)
-> Bitmap Index Scan on
click_00028_date_idx (cost=0.00..5317.31 rows=253151 width=0) (actual
time=0.010..0.010 rows=0 loops=1)
Index Cond: ((date >= $1) AND (date <= $2))
-> Bitmap Heap Scan on click_00030 cl
(cost=6383.03..7811.35 rows=360 width=141) (actual
time=201350.373..201350.466 rows=10 loops=1)
Recheck Cond: ((idaffiliate = $3) AND (date >=
$1) AND (date <= $2))
-> BitmapAnd (cost=6383.03..6383.03 rows=360
width=0) (actual time=201350.280..201350.280 rows=0 loops=1)
-> Bitmap Index Scan on
click_00030_idaffiliate_idx (cost=0.00..1337.96 rows=72058 width=0)
(actual time=0.101..0.101 rows=484 loops=1)
Index Cond: (idaffiliate = $3)
-> Bitmap Index Scan on
click_00030_date_idx (cost=0.00..5044.64 rows=240312 width=0) (actual
time=201347.064..201347.064 rows=43352606 loops=1)
Index Cond: ((date >= $1) AND (date <= $2))
-> Index Scan using click_current_massive_idx on
click_current cl (cost=0.00..25.70 rows=1 width=152) (never executed)
Index Cond: ((date >= $1) AND (date <= $2) AND
(idaffiliate = $3))
-> Index Scan using conversion_clickgenerated_idx on
"conversion" co (cost=0.00..185.51 rows=45 width=16) (actual
time=0.015..0.016 rows=0 loops=10)
Index Cond: ((cl.clickgenerated)::text =
(co.clickgenerated)::text)
Total runtime: 201350.887 ms
(24 rows)
Time: 201351.556 ms
dev=# deallocate fooplan;
DEALLOCATE
Thanks!
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Poor plan choice in prepared statement
@ 2008-12-30 19:42 Merlin Moncure <mmoncure@gmail.com>
parent: bricklen <bricklen@gmail.com>
1 sibling, 2 replies; 20+ messages in thread
From: Merlin Moncure @ 2008-12-30 19:42 UTC (permalink / raw)
To: bricklen <bricklen@gmail.com>; +Cc: pgsql-performance
On Tue, Dec 30, 2008 at 1:59 PM, bricklen <bricklen@gmail.com> wrote:
> Hi, I am re-posting my question here after trying to find a solution
> in the PHP pgsql list with no luck.
>
> I am experiencing some performance issues that I think are stemming
> from prepared statements. I have a pretty simple query:
> -- bad plan, from prepared statement
> --
> dev=# prepare fooplan (date,date,int,int) as
> dev-# SELECT cl.idOffer AS campaign, cl.idAffiliate AS affiliate,
> cl.idCreative AS creative, cl.subid, cl.datetime
> dev-# FROM click AS cl LEFT JOIN conversion AS co ON cl.clickGenerated
> = co.clickGenerated
> dev-# WHERE cl."date" >= $1
> dev-# AND cl."date" <= $2
> dev-# AND cl.idAffiliate = $3
> dev-# LIMIT $4;
Your problem is that the query as written is hard to plan. The
database has no idea what you pass in, it has to guess. (IMO, It
almost always guesses wrong...I think it should assume 1 row
returned). Also, the db has no idea what you want to pass in at plan
time for date.
what indexes do you have on click?
merlin
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Poor plan choice in prepared statement
@ 2008-12-30 19:55 bricklen <bricklen@gmail.com>
parent: Merlin Moncure <mmoncure@gmail.com>
1 sibling, 0 replies; 20+ messages in thread
From: bricklen @ 2008-12-30 19:55 UTC (permalink / raw)
To: Merlin Moncure <mmoncure@gmail.com>; +Cc: pgsql-performance
Hi Merlin,
On Tue, Dec 30, 2008 at 11:42 AM, Merlin Moncure <mmoncure@gmail.com> wrote:
> On Tue, Dec 30, 2008 at 1:59 PM, bricklen <bricklen@gmail.com> wrote:
>> Hi, I am re-posting my question here after trying to find a solution
>> in the PHP pgsql list with no luck.
>>
>> I am experiencing some performance issues that I think are stemming
>> from prepared statements. I have a pretty simple query:
>> -- bad plan, from prepared statement
>> --
>> dev=# prepare fooplan (date,date,int,int) as
>> dev-# SELECT cl.idOffer AS campaign, cl.idAffiliate AS affiliate,
>> cl.idCreative AS creative, cl.subid, cl.datetime
>> dev-# FROM click AS cl LEFT JOIN conversion AS co ON cl.clickGenerated
>> = co.clickGenerated
>> dev-# WHERE cl."date" >= $1
>> dev-# AND cl."date" <= $2
>> dev-# AND cl.idAffiliate = $3
>> dev-# LIMIT $4;
>
> Your problem is that the query as written is hard to plan. The
> database has no idea what you pass in, it has to guess. (IMO, It
> almost always guesses wrong...I think it should assume 1 row
> returned). Also, the db has no idea what you want to pass in at plan
> time for date.
>
> what indexes do you have on click?
>
> merlin
>
"click" is a partitioned table, but the child tables are all the same.
Here is the current partition:
dev=# \d click_current
Column | Type | Modifiers
----------------+-----------------------------+----------------------------------------------------
id | bigint | not null default
nextval('click_id_seq'::regclass)
idaffiliate | integer | not null
idsite | integer | not null
idoffer | integer | not null
idcreative | integer | not null
idoptimizer | integer |
clickgenerated | character varying | not null
subid | character varying |
datetime | timestamp without time zone | not null
date | date |
ip | inet | not null
xip | inet |
referrer | text |
countrycode | character varying |
timestamp | timestamp without time zone | not null
Indexes:
"click_current_pk" PRIMARY KEY, btree (id)
"click_current_clickgenerated_idx" btree (clickgenerated)
"click_current_date_idx" btree (date)
"click_current_idoffer_idx" btree (idoffer)
"click_current_massive_idx" btree (date, idaffiliate, idsite,
idoffer, idcreative, idoptimizer, subid)
Check constraints:
"click_current_date_chk" CHECK (date > '2008-12-29'::date)
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Poor plan choice in prepared statement
@ 2008-12-30 20:09 Scott Marlowe <scott.marlowe@gmail.com>
parent: Merlin Moncure <mmoncure@gmail.com>
1 sibling, 1 reply; 20+ messages in thread
From: Scott Marlowe @ 2008-12-30 20:09 UTC (permalink / raw)
To: Merlin Moncure <mmoncure@gmail.com>; +Cc: bricklen <bricklen@gmail.com>; pgsql-performance
On Tue, Dec 30, 2008 at 12:42 PM, Merlin Moncure <mmoncure@gmail.com> wrote:
> On Tue, Dec 30, 2008 at 1:59 PM, bricklen <bricklen@gmail.com> wrote:
>> Hi, I am re-posting my question here after trying to find a solution
>> in the PHP pgsql list with no luck.
>>
>> I am experiencing some performance issues that I think are stemming
>> from prepared statements. I have a pretty simple query:
>> -- bad plan, from prepared statement
>> --
>> dev=# prepare fooplan (date,date,int,int) as
>> dev-# SELECT cl.idOffer AS campaign, cl.idAffiliate AS affiliate,
>> cl.idCreative AS creative, cl.subid, cl.datetime
>> dev-# FROM click AS cl LEFT JOIN conversion AS co ON cl.clickGenerated
>> = co.clickGenerated
>> dev-# WHERE cl."date" >= $1
>> dev-# AND cl."date" <= $2
>> dev-# AND cl.idAffiliate = $3
>> dev-# LIMIT $4;
>
> Your problem is that the query as written is hard to plan. The
> database has no idea what you pass in, it has to guess. (IMO, It
> almost always guesses wrong...I think it should assume 1 row
> returned). Also, the db has no idea what you want to pass in at plan
> time for date.
One of the things you can try here is to build your query then execute
it so it has to be planned each time.
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Poor plan choice in prepared statement
@ 2008-12-30 20:14 bricklen <bricklen@gmail.com>
parent: Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 1 reply; 20+ messages in thread
From: bricklen @ 2008-12-30 20:14 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: Merlin Moncure <mmoncure@gmail.com>; pgsql-performance
Hi Scott,
On Tue, Dec 30, 2008 at 12:09 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
> On Tue, Dec 30, 2008 at 12:42 PM, Merlin Moncure <mmoncure@gmail.com> wrote:
>> On Tue, Dec 30, 2008 at 1:59 PM, bricklen <bricklen@gmail.com> wrote:
>>> Hi, I am re-posting my question here after trying to find a solution
>>> in the PHP pgsql list with no luck.
>>>
>>> I am experiencing some performance issues that I think are stemming
>>> from prepared statements. I have a pretty simple query:
>>> -- bad plan, from prepared statement
>>> --
>>> dev=# prepare fooplan (date,date,int,int) as
>>> dev-# SELECT cl.idOffer AS campaign, cl.idAffiliate AS affiliate,
>>> cl.idCreative AS creative, cl.subid, cl.datetime
>>> dev-# FROM click AS cl LEFT JOIN conversion AS co ON cl.clickGenerated
>>> = co.clickGenerated
>>> dev-# WHERE cl."date" >= $1
>>> dev-# AND cl."date" <= $2
>>> dev-# AND cl.idAffiliate = $3
>>> dev-# LIMIT $4;
>>
>> Your problem is that the query as written is hard to plan. The
>> database has no idea what you pass in, it has to guess. (IMO, It
>> almost always guesses wrong...I think it should assume 1 row
>> returned). Also, the db has no idea what you want to pass in at plan
>> time for date.
>
> One of the things you can try here is to build your query then execute
> it so it has to be planned each time.
>
Yeah, I've tested that in the application itself and it worked
correctly. I am trying to discover a way to use bind variables in PHP
without using the prepare function (to block sql injection), or if I
must use the prepare function, then force it to replan each time
somehow. That's part of where I'm stuck (and I'm no php guy).
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Poor plan choice in prepared statement
@ 2008-12-30 21:09 Scott Carey <scott@richrelevance.com>
parent: bricklen <bricklen@gmail.com>
0 siblings, 2 replies; 20+ messages in thread
From: Scott Carey @ 2008-12-30 21:09 UTC (permalink / raw)
To: bricklen <bricklen@gmail.com>; Scott Marlowe <scott.marlowe@gmail.com>; +Cc: Merlin Moncure <mmoncure@gmail.com>; pgsql-performance
There is no way to force Postgres to re-plan a prepared statement. In many cases, this would be a hugely beneficial feature (perhaps part of the definition of the statement?).
I have had similar issues, and had to code the application to prevent SQL injection (Postgres $ quotes and other stuff is helpful, but not always adequate or easy). With the current state of things, you'll have to often do your SQL injection defense in your application due to this weakness in prepared statements.
I have also had a case where one query would take a couple hundred ms to parse, but was fairly fast to plan and execute (1/3 the parse cost) -- yet another case where a prepared statement that re-plans each execution would be helpful. At least you can prevent SQL injection and cut the parse cost. Its not all about the cost of planning the query.
________________________________________
From: pgsql-performance-owner@postgresql.org [pgsql-performance-owner@postgresql.org] On Behalf Of bricklen [bricklen@gmail.com]
Sent: Tuesday, December 30, 2008 12:14 PM
To: Scott Marlowe
Cc: Merlin Moncure; pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Poor plan choice in prepared statement
Hi Scott,
On Tue, Dec 30, 2008 at 12:09 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
> On Tue, Dec 30, 2008 at 12:42 PM, Merlin Moncure <mmoncure@gmail.com> wrote:
>> On Tue, Dec 30, 2008 at 1:59 PM, bricklen <bricklen@gmail.com> wrote:
>>> Hi, I am re-posting my question here after trying to find a solution
>>> in the PHP pgsql list with no luck.
>>>
>>> I am experiencing some performance issues that I think are stemming
>>> from prepared statements. I have a pretty simple query:
>>> -- bad plan, from prepared statement
>>> --
>>> dev=# prepare fooplan (date,date,int,int) as
>>> dev-# SELECT cl.idOffer AS campaign, cl.idAffiliate AS affiliate,
>>> cl.idCreative AS creative, cl.subid, cl.datetime
>>> dev-# FROM click AS cl LEFT JOIN conversion AS co ON cl.clickGenerated
>>> = co.clickGenerated
>>> dev-# WHERE cl."date" >= $1
>>> dev-# AND cl."date" <= $2
>>> dev-# AND cl.idAffiliate = $3
>>> dev-# LIMIT $4;
>>
>> Your problem is that the query as written is hard to plan. The
>> database has no idea what you pass in, it has to guess. (IMO, It
>> almost always guesses wrong...I think it should assume 1 row
>> returned). Also, the db has no idea what you want to pass in at plan
>> time for date.
>
> One of the things you can try here is to build your query then execute
> it so it has to be planned each time.
>
Yeah, I've tested that in the application itself and it worked
correctly. I am trying to discover a way to use bind variables in PHP
without using the prepare function (to block sql injection), or if I
must use the prepare function, then force it to replan each time
somehow. That's part of where I'm stuck (and I'm no php guy).
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Poor plan choice in prepared statement
@ 2008-12-30 21:40 bricklen <bricklen@gmail.com>
parent: Scott Carey <scott@richrelevance.com>
1 sibling, 0 replies; 20+ messages in thread
From: bricklen @ 2008-12-30 21:40 UTC (permalink / raw)
To: Scott Carey <scott@richrelevance.com>; +Cc: Scott Marlowe <scott.marlowe@gmail.com>; Merlin Moncure <mmoncure@gmail.com>; pgsql-performance
On Tue, Dec 30, 2008 at 1:09 PM, Scott Carey <scott@richrelevance.com> wrote:
> There is no way to force Postgres to re-plan a prepared statement. In many cases, this would be a hugely beneficial feature (perhaps part of the definition of the statement?).
>
> I have had similar issues, and had to code the application to prevent SQL injection (Postgres $ quotes and other stuff is helpful, but not always adequate or easy). With the current state of things, you'll have to often do your SQL injection defense in your application due to this weakness in prepared statements.
>
> I have also had a case where one query would take a couple hundred ms to parse, but was fairly fast to plan and execute (1/3 the parse cost) -- yet another case where a prepared statement that re-plans each execution would be helpful. At least you can prevent SQL injection and cut the parse cost. Its not all about the cost of planning the query.
>
Can you point out any standard ways of preventing sql injection
(within or without php)? I would really rather not have to roll my own
input sanitation code if there are standard methods available.
For example, addslashes is apparently not recommended (according to a
warning in the postgresql docs at
http://wiki.postgresql.org/wiki/8.1.4_et._al._Security_Release_FAQ).
Thanks!
Bricklen
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Poor plan choice in prepared statement
@ 2008-12-30 23:02 Tom Lane <tgl@sss.pgh.pa.us>
parent: Scott Carey <scott@richrelevance.com>
1 sibling, 3 replies; 20+ messages in thread
From: Tom Lane @ 2008-12-30 23:02 UTC (permalink / raw)
To: Scott Carey <scott@richrelevance.com>; +Cc: bricklen <bricklen@gmail.com>; Scott Marlowe <scott.marlowe@gmail.com>; Merlin Moncure <mmoncure@gmail.com>; pgsql-performance
Scott Carey <scott@richrelevance.com> writes:
> I have also had a case where one query would take a couple hundred ms to parse, but was fairly fast to plan and execute (1/3 the parse cost) -- yet another case where a prepared statement that re-plans each execution would be helpful. At least you can prevent SQL injection and cut the parse cost. Its not all about the cost of planning the query.
The point of a prepared statement IMHO is to do the planning only once.
There's necessarily a tradeoff between that and having a plan that's
perfectly adapted to specific parameter values.
Reasonable client-side APIs should provide the option to use out-of-line
parameters, which is what you want to prevent SQL injection, without
hard-wiring that to the orthogonal concept of statements whose plan is
prepared in advance. In libpq, for instance, PQexecParams() does that.
regards, tom lane
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Poor plan choice in prepared statement
@ 2008-12-30 23:11 bricklen <bricklen@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
2 siblings, 0 replies; 20+ messages in thread
From: bricklen @ 2008-12-30 23:11 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
Hi Tom,
On Tue, Dec 30, 2008 at 3:02 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> The point of a prepared statement IMHO is to do the planning only once.
> There's necessarily a tradeoff between that and having a plan that's
> perfectly adapted to specific parameter values.
I agree, and normally it wouldn't be an issue. In this particular
case, we are seeing response time to go from sub-second with
non-prepared queries, to over 200 seconds w/ prepared queries. Note
that is not an isolated case in our application, merely the numbers
from this particular example.
>
> Reasonable client-side APIs should provide the option to use out-of-line
> parameters, which is what you want to prevent SQL injection, without
> hard-wiring that to the orthogonal concept of statements whose plan is
> prepared in advance. In libpq, for instance, PQexecParams() does that.
>
> regards, tom lane
>
Again, I agree completely. What I am after I guess are some pointers
on where to look for that, with regards to PHP. Whatever I turn up, I
will turn over to our developers, but before I do that I want to be
sure I am giving them the correct advice.
Thanks,
Bricklen
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Poor plan choice in prepared statement
@ 2008-12-31 00:19 david@lang.hm
parent: Tom Lane <tgl@sss.pgh.pa.us>
2 siblings, 1 reply; 20+ messages in thread
From: david@lang.hm @ 2008-12-31 00:19 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Scott Carey <scott@richrelevance.com>; bricklen <bricklen@gmail.com>; Scott Marlowe <scott.marlowe@gmail.com>; Merlin Moncure <mmoncure@gmail.com>; pgsql-performance
On Tue, 30 Dec 2008, Tom Lane wrote:
> Scott Carey <scott@richrelevance.com> writes:
>> I have also had a case where one query would take a couple hundred ms to parse, but was fairly fast to plan and execute (1/3 the parse cost) -- yet another case where a prepared statement that re-plans each execution would be helpful. At least you can prevent SQL injection and cut the parse cost. Its not all about the cost of planning the query.
>
> The point of a prepared statement IMHO is to do the planning only once.
> There's necessarily a tradeoff between that and having a plan that's
> perfectly adapted to specific parameter values.
actually, it does two things
1. planning only once
2. parsing only once.
I suspect that when this was initially setup the expectation was that the
planning was the expensive thing that should be avoided.
in this case a post earlier in the thread identified parsing of the query
as being the expensive thing (planning + execution was 1/3 the cost of the
parsing)
since there is not a pre-parsed interface for queries, it may make sense
to setup a way to have the query pre-parsed, but not pre-planned for cases
like this.
David Lang
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Poor plan choice in prepared statement
@ 2008-12-31 04:23 Gregory Stark <stark@enterprisedb.com>
parent: david@lang.hm
0 siblings, 0 replies; 20+ messages in thread
From: Gregory Stark @ 2008-12-31 04:23 UTC (permalink / raw)
To: david@lang.hm; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Scott Carey <scott@richrelevance.com>; bricklen <bricklen@gmail.com>; Scott Marlowe <scott.marlowe@gmail.com>; Merlin Moncure <mmoncure@gmail.com>; pgsql-performance
david@lang.hm writes:
> since there is not a pre-parsed interface for queries, it may make sense to
> setup a way to have the query pre-parsed, but not pre-planned for cases like
> this.
What would be more interesting would be to have plans that take into account
the outlier values and have alternative plans for those values.
One aspect that hasn't been discussed here is whether it's only certain
outlier arguments that cause Postgres to choose the poor plan for you or
whether it always chooses it for all the sets of arguments you actually use.
If it's the former then it's possible you're only being bitten if the first
time you prepare the query happens to have one of these outlier set of
parameters. I forget what version this went in but I think it was before 8.2
that Postgres started using the first set of arguments seen to plan the query.
This is usually an improvement over just guessing but if that first set is
unusual it could lead to strange results.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's On-Demand Production Tuning
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Poor plan choice in prepared statement
@ 2008-12-31 09:12 Guillaume Smet <guillaume.smet@gmail.com>
parent: bricklen <bricklen@gmail.com>
1 sibling, 1 reply; 20+ messages in thread
From: Guillaume Smet @ 2008-12-31 09:12 UTC (permalink / raw)
To: bricklen <bricklen@gmail.com>; +Cc: pgsql-performance
On Tue, Dec 30, 2008 at 7:59 PM, bricklen <bricklen@gmail.com> wrote:
> 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 (if necessary?)
As far as I understand your problem, you don't care about using
prepared statements.
If so, you can:
- either use pg_query_params():
http://www.php.net/manual/en/function.pg-query-params.php
- or use an unnamed prepared statements when you don't want a prepared
statement if, for some reason, you really need to use prepared
statements in a few cases: you can specify an empty string as plan
name. The condition for this one is that you need to upgrade to a
recent version of 8.3 as postponed planning of unnamed prepared
statements is a new feature of 8.3 and was broken in 8.3.0 and 8.3.1.
--
Guillaume
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Poor plan choice in prepared statement
@ 2008-12-31 15:11 bricklen <bricklen@gmail.com>
parent: Guillaume Smet <guillaume.smet@gmail.com>
0 siblings, 0 replies; 20+ messages in thread
From: bricklen @ 2008-12-31 15:11 UTC (permalink / raw)
To: Guillaume Smet <guillaume.smet@gmail.com>; +Cc: pgsql-performance
Hi Guillaume,
On Wed, Dec 31, 2008 at 1:12 AM, Guillaume Smet
<guillaume.smet@gmail.com> wrote:
> On Tue, Dec 30, 2008 at 7:59 PM, bricklen <bricklen@gmail.com> wrote:
>> 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 (if necessary?)
>
> As far as I understand your problem, you don't care about using
> prepared statements.
>
> If so, you can:
> - either use pg_query_params():
> http://www.php.net/manual/en/function.pg-query-params.php
> - or use an unnamed prepared statements when you don't want a prepared
> statement if, for some reason, you really need to use prepared
> statements in a few cases: you can specify an empty string as plan
> name. The condition for this one is that you need to upgrade to a
> recent version of 8.3 as postponed planning of unnamed prepared
> statements is a new feature of 8.3 and was broken in 8.3.0 and 8.3.1.
>
> --
> Guillaume
>
I will look into the pg_query_params() function to see if it works for
us. I don't think your second suggestion is going to work for us,
since we are stuck on 8.2 for the foreseeable future.
Thanks for the tips though, I appreciate it!
Bricklen
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Poor plan choice in prepared statement
@ 2008-12-31 16:01 Alvaro Herrera <alvherre@commandprompt.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
2 siblings, 2 replies; 20+ messages in thread
From: Alvaro Herrera @ 2008-12-31 16:01 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Scott Carey <scott@richrelevance.com>; bricklen <bricklen@gmail.com>; Scott Marlowe <scott.marlowe@gmail.com>; Merlin Moncure <mmoncure@gmail.com>; pgsql-performance
Tom Lane escribió:
> Scott Carey <scott@richrelevance.com> writes:
> > I have also had a case where one query would take a couple hundred
> > ms to parse, but was fairly fast to plan and execute (1/3 the parse
> > cost) -- yet another case where a prepared statement that re-plans
> > each execution would be helpful. At least you can prevent SQL
> > injection and cut the parse cost. Its not all about the cost of
> > planning the query.
>
> The point of a prepared statement IMHO is to do the planning only once.
> There's necessarily a tradeoff between that and having a plan that's
> perfectly adapted to specific parameter values.
I think it has been shown enough times that the performance drop caused
by a worse plan can be orders of magnitudes worse than what's gained by
producing the plan only once. It does not seem a bad idea to provide a
way to carry out only the parse phase, and postpone planning until the
parameters have been received.
--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Poor plan choice in prepared statement
@ 2009-01-01 13:44 Robert Haas <robertmhaas@gmail.com>
parent: Alvaro Herrera <alvherre@commandprompt.com>
1 sibling, 0 replies; 20+ messages in thread
From: Robert Haas @ 2009-01-01 13:44 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@commandprompt.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Scott Carey <scott@richrelevance.com>; bricklen <bricklen@gmail.com>; Scott Marlowe <scott.marlowe@gmail.com>; Merlin Moncure <mmoncure@gmail.com>; pgsql-performance
On Wed, Dec 31, 2008 at 11:01 AM, Alvaro Herrera
<alvherre@commandprompt.com> wrote:
>> The point of a prepared statement IMHO is to do the planning only once.
>> There's necessarily a tradeoff between that and having a plan that's
>> perfectly adapted to specific parameter values.
>
> I think it has been shown enough times that the performance drop caused
> by a worse plan can be orders of magnitudes worse than what's gained by
> producing the plan only once. It does not seem a bad idea to provide a
> way to carry out only the parse phase, and postpone planning until the
> parameters have been received.
+1.
...Robert
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Poor plan choice in prepared statement
@ 2009-01-01 18:22 Guillaume Smet <guillaume.smet@gmail.com>
parent: Alvaro Herrera <alvherre@commandprompt.com>
1 sibling, 1 reply; 20+ messages in thread
From: Guillaume Smet @ 2009-01-01 18:22 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@commandprompt.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Scott Carey <scott@richrelevance.com>; bricklen <bricklen@gmail.com>; Scott Marlowe <scott.marlowe@gmail.com>; Merlin Moncure <mmoncure@gmail.com>; pgsql-performance
On Wed, Dec 31, 2008 at 5:01 PM, Alvaro Herrera
<alvherre@commandprompt.com> wrote:
> I think it has been shown enough times that the performance drop caused
> by a worse plan can be orders of magnitudes worse than what's gained by
> producing the plan only once. It does not seem a bad idea to provide a
> way to carry out only the parse phase, and postpone planning until the
> parameters have been received.
It's already done in 8.3 for unnamed plans, isn't it?
--
Guillaume
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Poor plan choice in prepared statement
@ 2009-01-01 19:29 Guillaume Smet <guillaume.smet@gmail.com>
parent: david@lang.hm
0 siblings, 1 reply; 20+ messages in thread
From: Guillaume Smet @ 2009-01-01 19:29 UTC (permalink / raw)
To: david@lang.hm; +Cc: Alvaro Herrera <alvherre@commandprompt.com>; Tom Lane <tgl@sss.pgh.pa.us>; Scott Carey <scott@richrelevance.com>; bricklen <bricklen@gmail.com>; Scott Marlowe <scott.marlowe@gmail.com>; Merlin Moncure <mmoncure@gmail.com>; pgsql-performance
On Thu, Jan 1, 2009 at 9:24 PM, <david@lang.hm> wrote:
> forgive my ignorance here, but if it's unnamed how can you reference it
> later to take advantage of the parsing?
You can't. That's what unnamed prepared statements are for.
It's not obvious to me that the parsing phase is worth any "caching".
From my experience, the planning phase takes far much time on complex
queries.
--
Guillaume
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Poor plan choice in prepared statement
@ 2009-01-01 20:24 Tom Lane <tgl@sss.pgh.pa.us>
parent: david@lang.hm
0 siblings, 0 replies; 20+ messages in thread
From: Tom Lane @ 2009-01-01 20:24 UTC (permalink / raw)
To: david@lang.hm; +Cc: Guillaume Smet <guillaume.smet@gmail.com>; Alvaro Herrera <alvherre@commandprompt.com>; Scott Carey <scott@richrelevance.com>; bricklen <bricklen@gmail.com>; Scott Marlowe <scott.marlowe@gmail.com>; Merlin Moncure <mmoncure@gmail.com>; pgsql-performance
david@lang.hm writes:
> the poster who started this thread had a query where the parsing phase
> took significantly longer than the planning stage.
That was an anecdote utterly unsupported by evidence.
regards, tom lane
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Poor plan choice in prepared statement
@ 2009-01-01 20:24 david@lang.hm
parent: Guillaume Smet <guillaume.smet@gmail.com>
0 siblings, 1 reply; 20+ messages in thread
From: david@lang.hm @ 2009-01-01 20:24 UTC (permalink / raw)
To: Guillaume Smet <guillaume.smet@gmail.com>; +Cc: Alvaro Herrera <alvherre@commandprompt.com>; Tom Lane <tgl@sss.pgh.pa.us>; Scott Carey <scott@richrelevance.com>; bricklen <bricklen@gmail.com>; Scott Marlowe <scott.marlowe@gmail.com>; Merlin Moncure <mmoncure@gmail.com>; pgsql-performance
On Thu, 1 Jan 2009, Guillaume Smet wrote:
> On Wed, Dec 31, 2008 at 5:01 PM, Alvaro Herrera
> <alvherre@commandprompt.com> wrote:
>> I think it has been shown enough times that the performance drop caused
>> by a worse plan can be orders of magnitudes worse than what's gained by
>> producing the plan only once. It does not seem a bad idea to provide a
>> way to carry out only the parse phase, and postpone planning until the
>> parameters have been received.
>
> It's already done in 8.3 for unnamed plans, isn't it?
forgive my ignorance here, but if it's unnamed how can you reference it
later to take advantage of the parsing?
I may just be not understanding the terms being used here.
David Lang
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Poor plan choice in prepared statement
@ 2009-01-01 20:40 david@lang.hm
parent: Guillaume Smet <guillaume.smet@gmail.com>
0 siblings, 1 reply; 20+ messages in thread
From: david@lang.hm @ 2009-01-01 20:40 UTC (permalink / raw)
To: Guillaume Smet <guillaume.smet@gmail.com>; +Cc: Alvaro Herrera <alvherre@commandprompt.com>; Tom Lane <tgl@sss.pgh.pa.us>; Scott Carey <scott@richrelevance.com>; bricklen <bricklen@gmail.com>; Scott Marlowe <scott.marlowe@gmail.com>; Merlin Moncure <mmoncure@gmail.com>; pgsql-performance
On Thu, 1 Jan 2009, Guillaume Smet wrote:
> On Thu, Jan 1, 2009 at 9:24 PM, <david@lang.hm> wrote:
>> forgive my ignorance here, but if it's unnamed how can you reference it
>> later to take advantage of the parsing?
>
> You can't. That's what unnamed prepared statements are for.
>
> It's not obvious to me that the parsing phase is worth any "caching".
> From my experience, the planning phase takes far much time on complex
> queries.
the poster who started this thread had a query where the parsing phase
took significantly longer than the planning stage.
David Lang
^ permalink raw reply [nested|flat] 20+ messages in thread
end of thread, other threads:[~2009-01-01 20:40 UTC | newest]
Thread overview: 20+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-12-30 18:59 Poor plan choice in prepared statement bricklen <bricklen@gmail.com>
2008-12-30 19:42 ` Merlin Moncure <mmoncure@gmail.com>
2008-12-30 19:55 ` bricklen <bricklen@gmail.com>
2008-12-30 20:09 ` Scott Marlowe <scott.marlowe@gmail.com>
2008-12-30 20:14 ` bricklen <bricklen@gmail.com>
2008-12-30 21:09 ` Scott Carey <scott@richrelevance.com>
2008-12-30 21:40 ` bricklen <bricklen@gmail.com>
2008-12-30 23:02 ` Tom Lane <tgl@sss.pgh.pa.us>
2008-12-30 23:11 ` bricklen <bricklen@gmail.com>
2008-12-31 00:19 ` david@lang.hm
2008-12-31 04:23 ` Gregory Stark <stark@enterprisedb.com>
2008-12-31 16:01 ` Alvaro Herrera <alvherre@commandprompt.com>
2009-01-01 13:44 ` Robert Haas <robertmhaas@gmail.com>
2009-01-01 18:22 ` Guillaume Smet <guillaume.smet@gmail.com>
2009-01-01 20:24 ` david@lang.hm
2009-01-01 19:29 ` Guillaume Smet <guillaume.smet@gmail.com>
2009-01-01 20:40 ` david@lang.hm
2009-01-01 20:24 ` Tom Lane <tgl@sss.pgh.pa.us>
2008-12-31 09:12 ` Guillaume Smet <guillaume.smet@gmail.com>
2008-12-31 15:11 ` bricklen <bricklen@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox