Re: Not same plan between static and prepared query - Mailing list 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