Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1Ui7F7-0003HS-8f for pgsql-performance@arkaria.postgresql.org; Thu, 30 May 2013 18:06:29 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.72) (envelope-from ) id 1Ui7F6-0002Tn-FW for pgsql-performance@arkaria.postgresql.org; Thu, 30 May 2013 18:06:28 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1Ui7F4-0002Ta-BL for pgsql-performance@postgresql.org; Thu, 30 May 2013 18:06:26 +0000 Received: from smtp.01.com ([199.36.142.181]) by magus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1Ui7Eu-0003ic-Vi for pgsql-performance@postgresql.org; Thu, 30 May 2013 18:06:25 +0000 Received: from localhost (localhost [127.0.0.1]) by smtp-out-2.01.com (Postfix) with ESMTP id A6374390122; Thu, 30 May 2013 13:06:14 -0500 (CDT) X-Virus-Scanned: amavisd-new at smtp-out-2.01.com Received: from smtp.01.com ([127.0.0.1]) by localhost (smtp-out-2.01.com [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id X-iCHHUOHO0Y; Thu, 30 May 2013 13:06:14 -0500 (CDT) Received: from smtp.01.com (localhost [127.0.0.1]) by smtp-out-2.01.com (Postfix) with ESMTP id 8394F390150; Thu, 30 May 2013 13:06:14 -0500 (CDT) Received: from localhost (localhost [127.0.0.1]) by smtp-out-2.01.com (Postfix) with ESMTP id 3607E390122; Thu, 30 May 2013 13:06:14 -0500 (CDT) X-Virus-Scanned: amavisd-new at smtp-out-2.01.com Received: from smtp.01.com ([127.0.0.1]) by localhost (smtp-out-2.01.com [127.0.0.1]) (amavisd-new, port 10026) with ESMTP id WKIY5h6NKaAr; Thu, 30 May 2013 13:06:14 -0500 (CDT) Received: from [172.47.23.103] (70-36-143-10.dsl.dynamic.sonic.net [70.36.143.10]) by smtp-out-2.01.com (Postfix) with ESMTPSA id 6FC31390150; Thu, 30 May 2013 13:06:13 -0500 (CDT) Message-ID: <51A79512.4020200@agliodbs.com> Date: Thu, 30 May 2013 11:06:10 -0700 From: Josh Berkus Organization: PostgreSQL Experts Inc. User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:17.0) Gecko/20130329 Thunderbird/17.0.5 MIME-Version: 1.0 To: Amit Kapila CC: pgsql-performance@postgresql.org Subject: Re: Performance bug in prepared statement binding in 9.2? References: <51A699E6.3000008@agliodbs.com> <00b301ce5d3d$697449a0$3c5cdce0$@kapila@huawei.com> In-Reply-To: X-Enigmail-Version: 1.5.1 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 7bit X-Pg-Spam-Score: -1.9 (-) 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 Amit, > I think it might be because of choosing custom plan option due to which it might be generating new plan during exec_bind_message(). > exec_bind_message()->GetCachedPlan()->choose_custom_plan(). If it chooses custom plan, then it will regenerate the plan which can cause extra cost > observed in test. > Though there is calculation that it should not choose custom plan always, but still I guess the variation observed in the test can be due to this reason. This is why I'm asking them to run tests on 9.1. If 9.1 doesn't exhibit this behavior, then customplan is liable to be at fault. HOWEVER, that doesn't explain why creating a plan for a query during application operation would take 80ms, but only 1.2ms when I do it interactively. FYI, per questions from IRC: the times for each "cycle" in my data are cumulative minutes. Each cycle runs around 500,000 queries, so that's the aggregate across all queries. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance