Re: Slowness of extended protocol

On 2016-07-31 17:57:12 -0400, Tom Lane wrote:
> Andres Freund <andres(at)anarazel(dot)de> writes:
> > FWIW, I've observed the same with (a bit) more complicated queries. A
> > part of this is that the extended protocol simply does
> > more. PQsendQueryGuts() sends Parse/Bind/Describe/Execute/Sync - that's
> > simply more work and data over the wire than a single Q message.
>
> Yeah. The extended query protocol was designed to offer a lot of
> functionality that people had asked for, like plan re-use and
> introspection of the data types assigned to query parameters, but that
> doesn't come at zero cost. I think the tie-in to the plan cache is a
> significant part of the added overhead, and so is the fact that we have to
> iterate the per-message loop in PostgresMain five times not once, with
> overheads like updating the process title incurred several times in that.

One approach to solving this, without changing the protocol, would be to
"fuse" parse/bind/execute/sync together, by peeking ahead in the
protocol stream. When that combination is seen looking ahead (without
blocking), optimize it by handing it to something closer to
exec_simple_query() which also handles parameters. Even if we don't
recognize that pattern everytime, e.g. because later messages are in
different, not yet arrived, tcp packets, that'd speed up the common
case. As our client socket is nearly always is in non-blocking mode
these days, that shouldn't be too expensive.

Not that that analogy is fitting perfectl;y, but the above approach
seems to work quite well on the CPU level ("macro op fusion"), to
increase execution throughput...

Andres