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@xxxxxxxxxxxxxx [mailto:pgsql-performance-owner@xxxxxxxxxxxxxx] On Behalf Of Amit Kapila Sent: Thursday, June 06, 2013 1:41 PM To: 'Ghislain ROUVIGNAC'; pgsql-performance@xxxxxxxxxxxxxx Subject: Re: 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@xxxxxxxxxxxxxx) 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@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
