Обсуждение: Not same plan between static and prepared 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 

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);

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.

Amit,
It's very strength for me to hear that PostgreSQL generate execution plan for prepared statements during execution, I
alwayswas thinking that the purpose of the prepared statement is to eliminate such behavior. Can it lead to  some
performancedegradation in case of heavy "update batch", that can run for millions of different values? Is it some way
togive some kind of query hint that will eliminate execution path recalculations during heavy updates and instruct
regardingcorrect 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)

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.

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-tp5758115p5758516.html
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.

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