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
seethe 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
pathas 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)
From the changes, it doesn't seem that any of such changes can cause the problem you have seen.
Do you think it can be due to
a. JDBC - communication, encoding or some other changes
b. can we assume that plans generated for all statements are same, if not it might have some cost for query plan
initialization(InitPlan) but again it should not be that big cost.
How do measure individual bind time cost (is the cost of only server side or it includes client bind or ..)?
With Regards,
Amit Kapila.
