pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedNot same plan between static and prepared query
6+ messages / 4 participants
[nested] [flat]
* Not same plan between static and prepared query
@ 2013-06-06 08:25 Ghislain ROUVIGNAC <ghr@sylob.com>
2013-06-06 10:40 ` Re: Not same plan between static and prepared query Amit Kapila <amit.kapila@huawei.com>
2013-06-10 13:58 ` Re: Not same plan between static and prepared query Ghislain ROUVIGNAC <ghr@sylob.com>
0 siblings, 2 replies; 6+ messages in thread
From: Ghislain ROUVIGNAC @ 2013-06-06 08:25 UTC (permalink / raw)
To: pgsql-performance
Hello,
We have a strange issue related to a prepared statement.
We have two equals queries where the sole difference is in the limit.
- The first is hard coded with limit 500.
- The second is prepared with limit $1 ($1 is bound to 500).
PostgreSQL give us two different plans with a huge execution time for the
prepared query:
-----------------------------------------------------------------------------------------------------------------------
2- Static Query
-----------------------------------------------------------------------------------------------------------------------
explain analyze
select *
from dm2_lignecommandevente lignecomma0_
inner join dm2_lignedocumentcommercialvente lignecomma0_1_ on
lignecomma0_.id=lignecomma0_1_.id
inner join dm1_lignedocumentcommercial lignecomma0_2_ on
lignecomma0_.id=lignecomma0_2_.id
where (lignecomma0_.id not like 'DefaultRecord_%') and
(lignecomma0_2_.dateFinValidite is null)
order by coalescedate(lignecomma0_2_.dateCreationSysteme) desc
limit 500
-------------------
Static query plan
-------------------
Limit (cost=0.00..12165.11 rows=500 width=909) (actual time=73.477..90.256
rows=500 loops=1)
-> Nested Loop (cost=0.00..11241165.90 rows=462025 width=909) (actual
time=73.475..90.164 rows=500 loops=1)
-> Nested Loop (cost=0.00..9086881.29 rows=462025 width=852)
(actual time=4.105..11.749 rows=500 loops=1)
-> Index Scan Backward using
x_dm1_lignedocumentcommercial_14 on dm1_lignedocumentcommercial
lignecomma0_2_ (cost=0.00..2744783.31 rows=1652194 width=541) (actual
time=0.017..1.374 rows=1944 loops=1)
Filter: (datefinvalidite IS NULL)
-> Index Scan using dm2_lignecommandevente_pkey on
dm2_lignecommandevente lignecomma0_ (cost=0.00..3.83 rows=1 width=311)
(actual time=0.004..0.004 rows=0 loops=1944)
Index Cond: ((lignecomma0_.id)::text =
(lignecomma0_2_.id)::text)
Filter: ((lignecomma0_.id)::text !~~
'DefaultRecord_%'::text)
-> Index Scan using dm2_lignedocumentcommercialvente_pkey on
dm2_lignedocumentcommercialvente lignecomma0_1_ (cost=0.00..4.40 rows=1
width=57) (actual time=0.005..0.005 rows=1 loops=500)
Index Cond: ((lignecomma0_1_.id)::text =
(lignecomma0_.id)::text)
Total runtime: 90.572 ms
-----------------------------------------------------------------------------------------------------------------------
2- Prepared Query
------------------------------------------------------------
-----------------------------------------------------------
PREPARE query(int) AS
select *
from dm2_lignecommandevente lignecomma0_
inner join dm2_lignedocumentcommercialvente lignecomma0_1_ on
lignecomma0_.id=lignecomma0_1_.id
inner join dm1_lignedocumentcommercial lignecomma0_2_ on
lignecomma0_.id=lignecomma0_2_.id
where (lignecomma0_.id not like 'DefaultRecord_%')
and (lignecomma0_2_.dateFinValidite is null)
order by coalescedate(lignecomma0_2_.dateCreationSysteme) desc
limit $1;
explain analyze
execute query(500);
-------------------
Prepared query plan
-------------------
Limit (cost=879927.25..880042.76 rows=46202 width=909) (actual
time=69609.593..69609.642 rows=500 loops=1)
-> Sort (cost=879927.25..881082.32 rows=462025 width=909) (actual
time=69609.588..69609.610 rows=500 loops=1)
Sort Key: (coalescedate(lignecomma0_2_.datecreationsysteme))
Sort Method: top-N heapsort Memory: 498kB
-> Hash Join (cost=164702.90..651691.22 rows=462025 width=909)
(actual time=7786.467..68148.530 rows=470294 loops=1)
Hash Cond: ((lignecomma0_2_.id)::text =
(lignecomma0_.id)::text)
-> Seq Scan on dm1_lignedocumentcommercial lignecomma0_2_
(cost=0.00..102742.36 rows=1652194 width=541) (actual
time=0.009..50840.692 rows=1650554 loops=1)
Filter: (datefinvalidite IS NULL)
-> Hash (cost=136181.67..136181.67 rows=472579 width=368)
(actual time=7681.787..7681.787 rows=472625 loops=1)
-> Hash Join (cost=40690.06..136181.67 rows=472579
width=368) (actual time=986.580..7090.877 rows=472625 loops=1)
Hash Cond: ((lignecomma0_1_.id)::text =
(lignecomma0_.id)::text)
-> Seq Scan on dm2_lignedocumentcommercialvente
lignecomma0_1_ (cost=0.00..29881.18 rows=1431818 width=57) (actual
time=14.401..2288.869 rows=1431818 loops=1)
-> Hash (cost=15398.83..15398.83 rows=472579
width=311) (actual time=967.209..967.209 rows=472625 loops=1)
-> Seq Scan on dm2_lignecommandevente
lignecomma0_ (cost=0.00..15398.83 rows=472579 width=311) (actual
time=18.154..662.185 rows=472625 loops=1)
Filter: ((id)::text !~~
'DefaultRecord_%'::text)
Total runtime: 69612.191 ms
-----------------------------------------------------------------------------------------------------------------------
We saw that both folowing queries give the same plan :
- Static query with limit 500 removed
explain analyze
select *
from dm2_lignecommandevente lignecomma0_
inner join dm2_lignedocumentcommercialvente lignecomma0_1_ on
lignecomma0_.id=lignecomma0_1_.id
inner join dm1_lignedocumentcommercial lignecomma0_2_ on
lignecomma0_.id=lignecomma0_2_.id
where (lignecomma0_.id not like 'DefaultRecord_%') and
(lignecomma0_2_.dateFinValidite is null)
order by coalescedate(lignecomma0_2_.dateCreationSysteme) desc
- The bad prepared query
PREPARE query(int) AS
select *
from dm2_lignecommandevente lignecomma0_
inner join dm2_lignedocumentcommercialvente lignecomma0_1_ on
lignecomma0_.id=lignecomma0_1_.id
inner join dm1_lignedocumentcommercial lignecomma0_2_ on
lignecomma0_.id=lignecomma0_2_.id
where (lignecomma0_.id not like 'DefaultRecord_%')
and (lignecomma0_2_.dateFinValidite is null)
order by coalescedate(lignecomma0_2_.dateCreationSysteme) desc
limit $1;
explain analyze
execute query(500);
We met the same behaviour with both :
- PostgreSQL 8.4.8 on Windows 2008 (Prod)
- PostgreSQL 8.4.8 and 8.4.17 on Windows 7 (Dev)
I hope someone has any idea.
*Ghislain ROUVIGNAC*
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Not same plan between static and prepared query
2013-06-06 08:25 Not same plan between static and prepared query Ghislain ROUVIGNAC <ghr@sylob.com>
@ 2013-06-06 10:40 ` Amit Kapila <amit.kapila@huawei.com>
2013-06-09 15:14 ` Re: Not same plan between static and prepared query Yuri Levinsky <yuril@celltick.com>
1 sibling, 1 reply; 6+ messages in thread
From: Amit Kapila @ 2013-06-06 10:40 UTC (permalink / raw)
To: 'Ghislain ROUVIGNAC' <ghr@sylob.com>; pgsql-performance
On Thursday, June 06, 2013 1:56 PM Ghislain ROUVIGNAC wrote:
> Hello,
> We have a strange issue related to a prepared statement.
> We have two equals queries where the sole difference is in the limit.
> - The first is hard coded with limit 500.
> - The second is prepared with limit $1 ($1 is bound to 500).
> PostgreSQL give us two different plans with a huge execution time for the
prepared query:
It can generate different plan for prepared query, because optimizer uses
default selectivity in case of bound parameters (in your case limit $1).
> We met the same behaviour with both :
> - PostgreSQL 8.4.8 on Windows 2008 (Prod)
> - PostgreSQL 8.4.8 and 8.4.17 on Windows 7 (Dev)
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Not same plan between static and prepared query
2013-06-06 08:25 Not same plan between static and prepared query Ghislain ROUVIGNAC <ghr@sylob.com>
2013-06-06 10:40 ` Re: Not same plan between static and prepared query Amit Kapila <amit.kapila@huawei.com>
@ 2013-06-09 15:14 ` Yuri Levinsky <yuril@celltick.com>
2013-06-09 15:49 ` Re: Not same plan between static and prepared query David Johnston <polobo@yahoo.com>
2013-06-10 09:02 ` Re: Not same plan between static and prepared query Amit Kapila <amit.kapila@huawei.com>
0 siblings, 2 replies; 6+ messages in thread
From: Yuri Levinsky @ 2013-06-09 15:14 UTC (permalink / raw)
To: Amit Kapila <amit.kapila@huawei.com>; Ghislain ROUVIGNAC <ghr@sylob.com>; pgsql-performance
Amit,
It's very strength for me to hear that PostgreSQL generate execution plan for prepared statements during execution, I always was thinking that the purpose of the prepared statement is to eliminate such behavior. Can it lead to some performance degradation in case of heavy "update batch", that can run for millions of different values? Is it some way to give some kind of query hint that will eliminate execution path recalculations during heavy updates and instruct regarding correct execution plan?
Sincerely yours,
Yuri Levinsky, DBA
Celltick Technologies Ltd., 32 Maskit St., Herzliya 46733, Israel
Mobile: +972 54 6107703, Office: +972 9 9710239; Fax: +972 9 9710222
-----Original Message-----
From: pgsql-performance-owner@postgresql.org [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Amit Kapila
Sent: Thursday, June 06, 2013 1:41 PM
To: 'Ghislain ROUVIGNAC'; pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Not same plan between static and prepared query
On Thursday, June 06, 2013 1:56 PM Ghislain ROUVIGNAC wrote:
> Hello,
> We have a strange issue related to a prepared statement.
> We have two equals queries where the sole difference is in the limit.
> - The first is hard coded with limit 500.
> - The second is prepared with limit $1 ($1 is bound to 500).
> PostgreSQL give us two different plans with a huge execution time for
> the
prepared query:
It can generate different plan for prepared query, because optimizer uses default selectivity in case of bound parameters (in your case limit $1).
> We met the same behaviour with both :
> - PostgreSQL 8.4.8 on Windows 2008 (Prod)
> - PostgreSQL 8.4.8 and 8.4.17 on Windows 7 (Dev)
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Not same plan between static and prepared query
2013-06-06 08:25 Not same plan between static and prepared query Ghislain ROUVIGNAC <ghr@sylob.com>
2013-06-06 10:40 ` Re: Not same plan between static and prepared query Amit Kapila <amit.kapila@huawei.com>
2013-06-09 15:14 ` Re: Not same plan between static and prepared query Yuri Levinsky <yuril@celltick.com>
@ 2013-06-09 15:49 ` David Johnston <polobo@yahoo.com>
1 sibling, 0 replies; 6+ messages in thread
From: David Johnston @ 2013-06-09 15:49 UTC (permalink / raw)
To: pgsql-performance
Yuri Levinsky wrote
>> We have two equals queries where the sole difference is in the limit.
>> - The first is hard coded with limit 500.
>> - The second is prepared with limit $1 ($1 is bound to 500).
>
>
>> PostgreSQL give us two different plans with a huge execution time for
>> the
> prepared query:
>
> It can generate different plan for prepared query, because optimizer uses
> default selectivity in case of bound parameters (in your case limit $1).
>
>
>> We met the same behaviour with both :
>> - PostgreSQL 8.4.8 on Windows 2008 (Prod)
>> - PostgreSQL 8.4.8 and 8.4.17 on Windows 7 (Dev)
So the planner knows it needs a limit in both cases yet for the second
situation it has no idea what the limit value will be. For a sufficiently
large value of LIMIT it will conclude that a sequential scan will be optimal
and so that is what the plan uses. However, knowing the limit is only going
to be 500 it is able to conclude that an index scan will work better.
> From PostgreSQL 9.2, it generates plan for prepared query during execution
> (Execute command) as well.
> So I think you will not face this problem in PostgreSQL 9.2 and above.
See:
http://www.postgresql.org/docs/9.2/interactive/release-9-2.html
Section E.5.3.1.3 (First Bullet)
Someone more knowledgeable than myself will need to comment on how the
performance impact was overcome but my guess is that update statements
likely avoid this behavior if the where clauses are equality conditions
since indexes (if available) are going to be the most efficient plan
regardless of the specific values. Its when, in cases like this, the
planner knows the specific value of LIMIT will matter greatly that it is
going to need to use a run-time plan. Whether during the PREPARE phase the
planner tags the resultant plan with some kind of "allow runtime plan" flag
I do not know though so maybe the first few executions will always use
run-time plans and only after N executes does the cached plan come into
effect.
Its probably worth a search and read of the mailing list but I cannot do so
at this moment.
David J.
--
View this message in context: http://postgresql.1045698.n5.nabble.com/Not-same-plan-between-static-and-prepared-query-tp5758115p57...
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.
--
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] 6+ messages in thread
* Re: Not same plan between static and prepared query
2013-06-06 08:25 Not same plan between static and prepared query Ghislain ROUVIGNAC <ghr@sylob.com>
2013-06-06 10:40 ` Re: Not same plan between static and prepared query Amit Kapila <amit.kapila@huawei.com>
2013-06-09 15:14 ` Re: Not same plan between static and prepared query Yuri Levinsky <yuril@celltick.com>
@ 2013-06-10 09:02 ` Amit Kapila <amit.kapila@huawei.com>
1 sibling, 0 replies; 6+ messages in thread
From: Amit Kapila @ 2013-06-10 09:02 UTC (permalink / raw)
To: 'Yuri Levinsky' <yuril@celltick.com>; 'Ghislain ROUVIGNAC' <ghr@sylob.com>; pgsql-performance
On Sunday, June 09, 2013 8:45 PM Yuri Levinsky wrote:
> Amit,
> It's very strength for me to hear that PostgreSQL generate execution
> plan for prepared statements during execution, I always was thinking
> that the purpose of the prepared statement is to eliminate such
> behavior.
It doesn't always choose to generate a new plan, rather it is a calculative
decision.
As far as I understand, it generates custom plan (based on bound parameters)
for 5 times and then generates generic plan (not based on bound parameters),
after that it compares that if the cost of generic plan is less than 10%
more expensive than average custom plan, then it will choose generic plan.
> Can it lead to some performance degradation in case of heavy
> "update batch", that can run for millions of different values?
Ideally it should not degrade performance.
What kind of update you have and does the values used for execute can vary
plan too much every time?
> Is it
> some way to give some kind of query hint that will eliminate execution
> path recalculations during heavy updates and instruct regarding correct
> execution plan?
Currently there doesn't exist any way to give any hint.
> Sincerely yours,
>
>
> Yuri Levinsky, DBA
> Celltick Technologies Ltd., 32 Maskit St., Herzliya 46733, Israel
> Mobile: +972 54 6107703, Office: +972 9 9710239; Fax: +972 9 9710222
>
> -----Original Message-----
> From: pgsql-performance-owner@postgresql.org [mailto:pgsql-performance-
> owner@postgresql.org] On Behalf Of Amit Kapila
> Sent: Thursday, June 06, 2013 1:41 PM
> To: 'Ghislain ROUVIGNAC'; pgsql-performance@postgresql.org
> Subject: Re: [PERFORM] Not same plan between static and prepared query
>
>
> On Thursday, June 06, 2013 1:56 PM Ghislain ROUVIGNAC wrote:
> > Hello,
>
>
> > We have a strange issue related to a prepared statement.
>
>
> > We have two equals queries where the sole difference is in the limit.
> > - The first is hard coded with limit 500.
> > - The second is prepared with limit $1 ($1 is bound to 500).
>
>
> > PostgreSQL give us two different plans with a huge execution time for
> > the
> prepared query:
>
> It can generate different plan for prepared query, because optimizer
> uses default selectivity in case of bound parameters (in your case
> limit $1).
>
>
> > We met the same behaviour with both :
> > - PostgreSQL 8.4.8 on Windows 2008 (Prod)
> > - PostgreSQL 8.4.8 and 8.4.17 on Windows 7 (Dev)
>
> From PostgreSQL 9.2, it generates plan for prepared query during
> execution (Execute command) as well.
> So I think you will not face this problem in PostgreSQL 9.2 and above.
>
> With Regards,
> Amit Kapila.
>
>
>
> --
> Sent via pgsql-performance mailing list (pgsql-
> performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>
> This mail was received via Mail-SeCure System.
>
>
>
>
> --
> Sent via pgsql-performance mailing list (pgsql-
> performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
--
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] 6+ messages in thread
* Re: Not same plan between static and prepared query
2013-06-06 08:25 Not same plan between static and prepared query Ghislain ROUVIGNAC <ghr@sylob.com>
@ 2013-06-10 13:58 ` Ghislain ROUVIGNAC <ghr@sylob.com>
1 sibling, 0 replies; 6+ messages in thread
From: Ghislain ROUVIGNAC @ 2013-06-10 13:58 UTC (permalink / raw)
To: Amit Kapila <amit.kapila@huawei.com>; +Cc: pgsql-performance
Hello Amit,
Thank you for your help.
You are right, it work fine with PostgreSQL 9.2.
*Ghislain ROUVIGNAC*
2013/6/6 Amit Kapila <amit.kapila@huawei.com>
>
> On Thursday, June 06, 2013 1:56 PM Ghislain ROUVIGNAC wrote:
> > Hello,
>
>
> > We have a strange issue related to a prepared statement.
>
>
> > We have two equals queries where the sole difference is in the limit.
> > - The first is hard coded with limit 500.
> > - The second is prepared with limit $1 ($1 is bound to 500).
>
>
> > PostgreSQL give us two different plans with a huge execution time for the
> prepared query:
>
> It can generate different plan for prepared query, because optimizer uses
> default selectivity in case of bound parameters (in your case limit $1).
>
>
> > We met the same behaviour with both :
> > - PostgreSQL 8.4.8 on Windows 2008 (Prod)
> > - PostgreSQL 8.4.8 and 8.4.17 on Windows 7 (Dev)
>
> From PostgreSQL 9.2, it generates plan for prepared query during execution
> (Execute command) as well.
> So I think you will not face this problem in PostgreSQL 9.2 and above.
>
> With Regards,
> Amit Kapila.
>
>
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2013-06-10 13:58 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2013-06-06 08:25 Not same plan between static and prepared query Ghislain ROUVIGNAC <ghr@sylob.com>
2013-06-06 10:40 ` Amit Kapila <amit.kapila@huawei.com>
2013-06-09 15:14 ` Yuri Levinsky <yuril@celltick.com>
2013-06-09 15:49 ` David Johnston <polobo@yahoo.com>
2013-06-10 09:02 ` Amit Kapila <amit.kapila@huawei.com>
2013-06-10 13:58 ` Ghislain ROUVIGNAC <ghr@sylob.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