From: Amit Kapila <amit.kapila@huawei.com>
To: "'Yuri Levinsky'" <yuril@celltick.com>, "'Ghislain ROUVIGNAC'"
	<ghr@sylob.com>, <pgsql-performance@postgresql.org>
References: 
 <CAH12p1AvzORBOBhpCOYc7NHpRrYdpLei3SX4oiCn3pw+LgT4xQ@mail.gmail.com>
	<007201ce62a2$56c35930$044a0b90$@kapila@huawei.com>
	<B72526FA2066E344AFD09734A487318103CB3882@falcon1.celltick.com>
In-Reply-To: <B72526FA2066E344AFD09734A487318103CB3882@falcon1.celltick.com>
Subject: Re: Not same plan between static and prepared query
Date: Mon, 10 Jun 2013 14:32:20 +0530
Message-ID: <00e701ce65b9$3f2caa50$bd85fef0$@kapila@huawei.com>
MIME-Version: 1.0
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Thread-Index: Ac5ij4wP5JpI2JlZTn2CwA8jHlte+gAEg66QAKBF9MAAJR2kwA==
Content-Language: en-us
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

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.=20

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?=20

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.
=20
> Sincerely yours,
>=20
>=20
> Yuri Levinsky, DBA
> Celltick Technologies Ltd., 32 Maskit St., Herzliya 46733, Israel
> Mobile: +972 54 6107703, Office: +972 9 9710239; Fax: +972 9 9710222
>=20
> -----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
>=20
>=20
> On Thursday, June 06, 2013 1:56 PM Ghislain ROUVIGNAC wrote:
> > Hello,
>=20
>=20
> > We have a strange issue related to a prepared statement.
>=20
>=20
> > 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).
>=20
>=20
> > PostgreSQL give us two different plans with a huge execution time for
> > the
> prepared query:
>=20
> It can generate different plan for prepared query, because optimizer
> uses default selectivity in case of bound parameters (in your case
> limit $1).
>=20
>=20
> > We met the same behaviour with both :
> > - PostgreSQL 8.4.8 on Windows 2008 (Prod)
> > -=A0PostgreSQL=A08.4.8 and=A08.4.17 on Windows 7 (Dev)
>=20
> 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.
>=20
> With Regards,
> Amit Kapila.
>=20
>=20
>=20
> --
> Sent via pgsql-performance mailing list (pgsql-
> performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>=20
> This mail was received via Mail-SeCure System.
>=20
>=20
>=20
>=20
> --
> Sent via pgsql-performance mailing list (pgsql-
> performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance


--=20
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance