Re: Slowness of extended protocol

Robert Haas:

> but for some reason you can't use prepared statements, for example because
> the queries are dynamically generated and . That case is analogous to -M
> extended, not -M prepared. And -M extended is well-known to be SLOWER
>

I do not buy that "dynamically generated queries defeat server-prepared
usage" argument. It is just not true (see below).

Do you mean "in language X, where X != Java it is impossible to implement a
query cache"?
That is just ridiculus.

At the end of the day, there will be a finite number of hot queries that
are important.
Here's relevant pgjdbc commit: https://github.com/pgjdbc/pgjdbc/pull/319
It works completely transparent to the application, and it does use
server-prepared statements even though application builds "brand new" sql
text every time.

It is not something theoretical, but it is something that is already
implemented and battle-tested. The application does build SQL texts based
on the names of tables and columns that are shown in the browser, and
pgjdbc uses query cache (to map user SQL to backend statement name), thus
it benefits from server-prepared statements automatically.

Not a single line change was required at the application side.

Am I missing something?
I cannot imagine a real life case when an application throws 10'000+ UNIQUE
SQL texts per second at the database.
Cases like "where id=1", "where id=2", "where id=3" do not count as they
should be written with bind variables, thus it represents a single SQL text
like "where id=$1".

Robert>you have to keep sending a different query text every time

Do you agree that the major part would be some hot queries, the rest will
be much less frequently used ones (e.g. one time queries)?

In OLTP applications the number of queries is high, and almost all the
queries are reused.
server-prepared to rescue here.
"protocol optimization" would not be noticeable.

In DWH applications the queries might be unique, however the number of
queries is much less, thus the "protocol optimization" would be invisible
as the query plan/process time would be much higher than the gain from
"protocol
optimization".

Vladimir