Re: Query preparation

John Lister wrote:
> Oliver Jowett wrote:
>> John Lister wrote:
>>
>>> Hi, i was wondering if there is any reason why the query preparation is
>>> done when the query is executed instead of when the preparedStatement is
>>> created.
>>>
>>
>> It would be an extra network round-trip on every query, and there's no
>> requirement to prepare it early.
>>
> Just going through the code again and please correct me if i'm wrong,
> but at the moment there is unnecessary network traffic/latency. Assuming
> a PreparedStatement, every time execute is called, sendParse and
> sendDescribePortal are called (the former checks if a server side
> prepared statement has been created and aborts if so). Admittedly
> sendParse is only called as many times as setPrepareThreshold is set to
> (which admittedly may be as low as 1)

Recreating the unnamed statement each time is exactly the point! Use of
an unnamed statement forces replanning (at Bind) using the actual
parameter values available at the time of execution, which can
substantially change the performance of the query plan (because when the
actual parameter values are known, the query planner can use gathered
column statistics to provide better cost estimates). Using a named
statement in all cases is *not* desirable.

Parameter types may change between executions which can completely
change the meaning of the query, so yes, it is necessary to re-Describe
each time.

There's no additional latency here (beyond latency induced by the extra
network traffic itself, which should be very minor) because the driver
doesn't wait for a response from the Parse/Bind/Describe before doing
the rest of the query.

> I appreciate that it would need a lot of refactoring to move things around, but are there any objections?

Moving to use named statements exclusively is a Bad Idea. You will kill
the performance of many simple queries. See above.

-O