From: Amit Kapila <amit.kapila@huawei.com>
To: 'Josh Berkus' <josh@agliodbs.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Performance bug in prepared statement binding in 9.2?
Date: Fri, 31 May 2013 13:09:01 +0530
Message-ID: <000601ce5dd1$ec1d6320$c4582960$@kapila@huawei.com> (raw)
In-Reply-To: <51A79512.4020200@agliodbs.com>
References: <51A699E6.3000008@agliodbs.com>
<00b301ce5d3d$697449a0$3c5cdce0$@kapila@huawei.com>
<WM!f3d412a8ff9fb2a52f4cfd0f48c225b6122ba5bf1e1523173b5dbc63db143b9cab09ac3f5518a12a19d2507538472d98!@asav-1.01.com>
<51A79512.4020200@agliodbs.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
On Thursday, May 30, 2013 11:36 PM Josh Berkus wrote:
> 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.
When you say interactively, does it mean that you are using psql to test the same?
> 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.
Today I tried to see the changes between 8.4 and 9.1 for bind path in server. Following is summary of whatever I could see the differences
1. 4 new parameters are added to ParamListInfo, for which palloc is done in exec_bind_message
2. changed function for converting client to server encoding, but it seems for bind path, it will still follow same path as for 8.4
2. small change in RevalidateCachedPlan() for new hook added in ParamListInfo
3. standard_ExecutorStart(), changes to setup After Statement Trigger context
4. InitPlan has some changes for FOR UPDATE/FOR SELECT statements and junk filter case (update/delete statements)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: amit.kapila@huawei.com, josh@agliodbs.com
Subject: Re: Performance bug in prepared statement binding in 9.2?
In-Reply-To: <000601ce5dd1$ec1d6320$c4582960$@kapila@huawei.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox