Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1UkXdo-0006zE-Fj for pgsql-performance@arkaria.postgresql.org; Thu, 06 Jun 2013 10:42:00 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.72) (envelope-from ) id 1UkXdn-0007Jt-Rt for pgsql-performance@arkaria.postgresql.org; Thu, 06 Jun 2013 10:41:59 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1UkXdn-0007Jo-32 for pgsql-performance@postgresql.org; Thu, 06 Jun 2013 10:41:59 +0000 Received: from szxga01-in.huawei.com ([119.145.14.64]) by magus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1UkXdi-0000IX-Sr for pgsql-performance@postgresql.org; Thu, 06 Jun 2013 10:41:58 +0000 Received: from 172.24.2.119 (EHLO szxeml207-edg.china.huawei.com) ([172.24.2.119]) by szxrg01-dlp.huawei.com (MOS 4.3.4-GA FastPath queued) with ESMTP id BDH44615; Thu, 06 Jun 2013 18:41:42 +0800 (CST) Received: from SZXEML453-HUB.china.huawei.com (10.82.67.196) by szxeml207-edg.china.huawei.com (172.24.2.56) with Microsoft SMTP Server (TLS) id 14.1.323.7; Thu, 6 Jun 2013 18:41:17 +0800 Received: from blrprnc09ns (10.18.96.98) by SZXEML453-HUB.china.huawei.com (10.82.67.196) with Microsoft SMTP Server id 14.1.323.7; Thu, 6 Jun 2013 18:41:02 +0800 From: Amit Kapila To: "'Ghislain ROUVIGNAC'" , References: In-Reply-To: Subject: Re: Not same plan between static and prepared query Date: Thu, 6 Jun 2013 16:10:58 +0530 Message-ID: <007201ce62a2$56c35930$044a0b90$@kapila@huawei.com> MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable X-Mailer: Microsoft Office Outlook 12.0 Thread-Index: Ac5ij4wP5JpI2JlZTn2CwA8jHlte+gAEg66Q Content-Language: en-us X-Originating-IP: [10.18.96.98] X-CFilter-Loop: Reflected X-Pg-Spam-Score: -2.1 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org 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) > -=A0PostgreSQL=A08.4.8 and=A08.4.17 on Windows 7 (Dev)