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.
