From: Amit Kapila <amit.kapila@huawei.com>
To: "'Josh Berkus'" <josh@agliodbs.com>
CC: <pgsql-performance@postgresql.org>
References: <51A699E6.3000008@agliodbs.com>
	<00b301ce5d3d$697449a0$3c5cdce0$@kapila@huawei.com>
	<WM!f3d412a8ff9fb2a52f4cfd0f48c225b6122ba5bf1e1523173b5dbc63db143b9cab09ac3f5518a12a19d2507538472d98!@asav-1.01.com>
	<51A79512.4020200@agliodbs.com>
In-Reply-To: <51A79512.4020200@agliodbs.com>
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>
MIME-Version: 1.0
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Thread-Index: Ac5dYGHWaK21TqCTRta7LjF23F018gAYRsXQ
Content-Language: en-us
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

On Thursday, May 30, 2013 11:36 PM Josh Berkus wrote:
> Amit,
>=20
> > 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.
>=20
> 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.
>=20
> 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 th=
e 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 serve=
r. 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 ParamListIn=
fo
3. standard_ExecutorStart(), changes to setup After Statement Trigger conte=
xt
4. InitPlan has some changes for FOR UPDATE/FOR SELECT statements and junk =
filter case (update/delete statements)