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