Re: Slowness of extended protocol

Greg wrote:
> I think you're looking at this the wrong way around. 30% of what?
> You're doing these simple read-only selects on a database that
> obviously is entirely in RAM. If you do the math on the numbers you
> gave above the simple protocol took 678 microseconds per transaction
> and the extended protocol took 876 microseconds. The difference is 198
> microseconds. I'm not sure exactly where those 200us are going and
> perhaps it could be lower but in what real-world query is it going to
> have a measurable impact on the total time?

That's a valid question, but as Andres said, it may not matter for most
workloads but I think such a significant difference would matter to some...
Keep in mind that I'm writing from the point of view of a driver developer,
and not of a specific app - I know there are some point executing against a
local database and trying to get extremely high throughput, for RAM reading
queries or otherwise.

> The other danger in unrealistic test cases is that you're probably
> measuring work that doesn't scale and in fact optimizing based on it
> could impose a cost that *does* scale. For example if 150us of that
> time is being spent in the prepare and we cut that down by a factor of
> 10 to 15us then it would be only a 10% penalty over the simple
> protocol in your test. But if that optimization added any overhead to
> the execute stage then when it's executed thousands of times that
> could add milliseconds to the total runtime.

I think it's a bit too early to say that... We're not discussing any
proposed optimizations yet, just discussing what may or may not be a
problem... Of course any proposed optimization would have to be carefully
studied to make sure it doesn't cause performance degradation elsewhere.

Andres wrote:
> Shay, are you using unnamed or named portals? There's already a shortcut
> path for the former in some places.

The benchmarks I posted are simply pgbench doing SELECT 1 with extended vs.
simple, so I'm assuming unnamed portals. Npgsql itself only uses the
unnamed portal, I think it's documented somewhere that this is better for
performance.

Tom wrote:
> In hindsight it seems clear that what a lot of apps want out of extended
> protocol is only the ability to send parameter values out-of-line instead
> of having to quote/escape them into SQL literals. Maybe an idea for the
> fabled V4 protocol update is some compromise query type that corresponds
> precisely to PQexecParams's feature set: you can send parameter values
> out-of-line, and you can specify text or binary results, but there's no
> notion of any persistent state being created and no feedback about
> parameter data types.

That seems like a good way forward. It may be possible to generalize this
into a more "pay-per-play" protocol. You currently have a binary choice
between a simple but fast protocol supporting very little, and an extended
but slow protocol supporting everything. Making things more "pick and
choose" could help here: if you want to actually use plan reuse, you pay
for that. If you actually send parameters, you pay for that. It would be a
pretty significant protocol change but it would make things more modular
that way.

> 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.

I was thinking that something like that may be the cause. Is it worth
looking into the loop and trying to optimize? For example, updating the
process title doesn't seem to make sense for every single extended
message...