Re: [HACKERS] Cached plans and statement generalization

[Юрий Соколов]

вс, 1 мар. 2020 г. в 22:26, Tom Lane :
>
> Konstantin Knizhnik  writes:
> > [ autoprepare-extended-4.patch ]
>
> The cfbot is showing that this doesn't apply anymore; there's
> some doubtless-trivial conflict in prepare.c.
>
> However ... TBH I've been skeptical of this whole proposal from the
> beginning, on the grounds that it would probably hurt more use-cases
> than it helps.  The latest approach doesn't really do anything to
> assuage that fear, because now that you've restricted it to extended
> query mode, the feature amounts to nothing more than deliberately
> overriding the application's choice to use unnamed rather than named
> (prepared) statements.  How often is that going to be a good idea?
> And when it is, wouldn't it be better to fix the app?  The client is
> likely to have a far better idea of which statements would benefit
> from this treatment than the server will; and in this context,
> the client-side changes needed would really be trivial.  The original
> proposal, scary as it was, at least supported the argument that you
> could use it to improve applications that were too dumb/hoary to
> parameterize commands for themselves.

The theme of this thread:
- it is not possible to reliably "fix the app" when pgbouncer or internal
  driver connection multiplexing are used.
- another widely spread case is frameworks (ORM or other).
  There is no way to prepare a concrete query because it is buried under
  levels of abstractions.

Whole "autoprepare" thing is a workaround for absence of "really trivial
client-side changes" in these conditions.

regards,
Yura

Re: [HACKERS] Cached plans and statement generalization

[Daniel Gustafsson]

> On 1 Mar 2020, at 20:26, Tom Lane  wrote:

> In short, I really think we ought to reject this patch and move on.
> Maybe it could be resurrected sometime in the future when we have a
> better handle on when to cache plans and when not.
> 
> If you want to press forward with it anyway, certainly the lack of
> any tests in this patch is another big objection.  Perhaps you
> could create a pgbench TAP script that exercises the logic.

Based on Tom's review, and that nothing has been submitted since, I've marked
this entry as returned with feedback.  Feel to open a new entry if you want to
address Tom's comments and take this further.

cheers ./daniel

Re: [HACKERS] Cached plans and statement generalization

[Tom Lane]

Konstantin Knizhnik  writes:
> [ autoprepare-extended-4.patch ]

The cfbot is showing that this doesn't apply anymore; there's
some doubtless-trivial conflict in prepare.c.

However ... TBH I've been skeptical of this whole proposal from the
beginning, on the grounds that it would probably hurt more use-cases
than it helps.  The latest approach doesn't really do anything to
assuage that fear, because now that you've restricted it to extended
query mode, the feature amounts to nothing more than deliberately
overriding the application's choice to use unnamed rather than named
(prepared) statements.  How often is that going to be a good idea?
And when it is, wouldn't it be better to fix the app?  The client is
likely to have a far better idea of which statements would benefit
from this treatment than the server will; and in this context,
the client-side changes needed would really be trivial.  The original
proposal, scary as it was, at least supported the argument that you
could use it to improve applications that were too dumb/hoary to
parameterize commands for themselves.

I'm also unimpressed by benchmark testing that relies entirely on
pgbench's default scenario, because that scenario consists entirely
of queries that are perfectly adapted to plan-only-once treatment.
In the real world, we constantly see complaints about cases where the
plancache mistakenly decides that a generic plan is acceptable.  I think
that extending that behavior to more cases is going to be a net loss,
until we find some way to make it smarter and more reliable.  At the
very least, you need to show some worst-case numbers alongside these
best-case numbers --- what happens with queries where we conclude we
must replan every time, so that the plancache becomes pure overhead?

The pgbench test case is laughably unrealistic in another way, in that
there are so few distinct queries it issues, so that there's no
stress at all on the cache-management aspect of this problem.

In short, I really think we ought to reject this patch and move on.
Maybe it could be resurrected sometime in the future when we have a
better handle on when to cache plans and when not.

If you want to press forward with it anyway, certainly the lack of
any tests in this patch is another big objection.  Perhaps you
could create a pgbench TAP script that exercises the logic.

regards, tom lane

Re: [HACKERS] Cached plans and statement generalization

[Konstantin Knizhnik]

On 01.12.2019 6:26, Michael Paquier wrote:

On Thu, Sep 26, 2019 at 10:23:38AM +0300, Konstantin Knizhnik wrote:

Sorry,
New version of the patch with corrected expected output for rules test is
attached.

It looks like the documentation is failing to build.  Could you fix
that?  There may be other issues as well.  I have moved the patch to
next CF, waiting on author.
--
Michael


Sorry, issues with documentation are fixed.


--
Konstantin Knizhnik
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

diff --git a/doc/src/sgml/autoprepare.sgml b/doc/src/sgml/autoprepare.sgml
new file mode 100644
index 000..c4d96e6
--- /dev/null
+++ b/doc/src/sgml/autoprepare.sgml
@@ -0,0 +1,62 @@
+
+
+ 
+  Autoprepared statements
+
+  
+   autoprepared statements
+  
+
+  
+PostgreSQL makes it possible prepare
+frequently used statements to eliminate cost of their compilation
+and optimization on each execution of the query. On simple queries
+(like ones in pgbench -S) using prepared statements
+increase performance more than two times.
+  
+
+  
+Unfortunately not all database applications are using prepared statements
+and, moreover, it is not always possible. For example, in case of using
+pgbouncer or any other session pooler,
+there is no session state (transactions of one client may be executed at different
+backends) and so prepared statements can not be used.
+  
+
+  
+Autoprepare mode allows to overcome this limitation.
+In this mode Postgres stores plans for all queries pass using extended protocol.
+Speed of execution of autoprepared statements is almost the same as of explicitly prepared statements.
+  
+
+  
+By default autoprepare mode is switched off. To enable it, assign non-zero
+value to GUC variable autoprepare_limit or autoprepare_memory_limit.
+This variables specify limit for number of autoprepared statement or memory used by them.
+Autoprepare is enabled if one of thme is non zero. Value -1 means unlimited.
+Please notice that event autoprepare is anabled, Postgres makes a decision about using
+generalized plan vs. customized execution plans based on the results
+of comparison of average time of five customized plans with
+time of generalized plan.
+  
+
+  
+Too large number of autoprepared statements can cause memory overflow
+(especially if there are many active clients, because prepared statements cache
+is local to the backend). So using unlimited autoprepare hash is debgerous and not recommended.
+Postgres is using LRU policy to keep in memory most frequently used queries.
+  
+
+  
+Autoprepare hash is local to the backend. It is implicitely reseted on any change of database schema or
+session variables.
+  
+
+  
+It is possible to inspect autoprepared queries in the backend using
+pg_autoprepared_statements view. It shows original text of the
+query, types of the extracted parameters (replacing literals),
+query execution counter and used memory.
+  
+
+ 
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index 4ec13f3..725e3a9 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -5432,6 +5432,39 @@ SELECT * FROM parent WHERE key = 2400;
   
  
 
+ 
+   autoprepare_limit (integer)
+   
+ autoprepare_limit configuration parameter
+   
+   
+   
+ 
+   Maximal number of autoprepared queries.
+   Zero value disables autoprepare, -1 means unlimited number of autoprepared queries.
+   Too large number of prepared queries can cause backend memory overflow and slowdown execution speed
+   (because of increased lookup time). Default value is 0.
+ 
+   
+ 
+
+ 
+   autoprepare_memory_limit (integer)
+   
+ autoprepare_memory_limit configuration parameter
+   
+   
+   
+ 
+   Maximal size of memory used by autoprepared queries.
+   Zero value disables autoprepare, -1 means means that there is no memory limit.
+   Default value is 0. Calculating memory used by prepared queries adds some extra overhead,
+   so positive value of this parameter may cause some slowdown.
+   autoprepare_limit is much faster way to limit number of autoprepared statements.
+ 
+   
+ 
+
  
 

diff --git a/doc/src/sgml/filelist.sgml b/doc/src/sgml/filelist.sgml
index 3da2365..4bd9f31 100644
--- a/doc/src/sgml/filelist.sgml
+++ b/doc/src/sgml/filelist.sgml
@@ -22,6 +22,7 @@
 
 
 
+
 
 
 
diff --git a/doc/src/sgml/postgres.sgml b/doc/src/sgml/postgres.sgml
index e59cba7..a479f36 100644
--- a/doc/src/sgml/postgres.sgml
+++ b/doc/src/sgml/postgres.sgml
@@ -109,6 +109,7 @@
   
   
   
+  
 
  
 
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index f7800f0..000cff3 100644
--- 

Re: [HACKERS] Cached plans and statement generalization

[Michael Paquier]

On Thu, Sep 26, 2019 at 10:23:38AM +0300, Konstantin Knizhnik wrote:
> Sorry,
> New version of the patch with corrected expected output for rules test is
> attached.

It looks like the documentation is failing to build.  Could you fix
that?  There may be other issues as well.  I have moved the patch to
next CF, waiting on author.
--
Michael


signature.asc
Description: PGP signature

Re: [HACKERS] Cached plans and statement generalization

[Konstantin Knizhnik]

On 25.09.2019 23:06, Alvaro Herrera wrote:

This patch fails the "rules" tests. Please fix.



Sorry,
New version of the patch with corrected expected output for rules test 
is attached.


--
Konstantin Knizhnik
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

diff --git a/doc/src/sgml/autoprepare.sgml b/doc/src/sgml/autoprepare.sgml
new file mode 100644
index 000..c4d96e6
--- /dev/null
+++ b/doc/src/sgml/autoprepare.sgml
@@ -0,0 +1,62 @@
+
+
+ 
+  Autoprepared statements
+
+  
+   autoprepared statements
+  
+
+  
+PostgreSQL makes it possible prepare
+frequently used statements to eliminate cost of their compilation
+and optimization on each execution of the query. On simple queries
+(like ones in pgbench -S) using prepared statements
+increase performance more than two times.
+  
+
+  
+Unfortunately not all database applications are using prepared statements
+and, moreover, it is not always possible. For example, in case of using
+pgbouncer or any other session pooler,
+there is no session state (transactions of one client may be executed at different
+backends) and so prepared statements can not be used.
+  
+
+  
+Autoprepare mode allows to overcome this limitation.
+In this mode Postgres stores plans for all queries pass using extended protocol.
+Speed of execution of autoprepared statements is almost the same as of explicitly prepared statements.
+  
+
+  
+By default autoprepare mode is switched off. To enable it, assign non-zero
+value to GUC variable autoprepare_limit or autoprepare_memory_limit.
+This variables specify limit for number of autoprepared statement or memory used by them.
+Autoprepare is enabled if one of thme is non zero. Value -1 means unlimited.
+Please notice that event autoprepare is anabled, Postgres makes a decision about using
+generalized plan vs. customized execution plans based on the results
+of comparison of average time of five customized plans with
+time of generalized plan.
+  
+
+  
+Too large number of autoprepared statements can cause memory overflow
+(especially if there are many active clients, because prepared statements cache
+is local to the backend). So using unlimited autoprepare hash is debgerous and not recommended.
+Postgres is using LRU policy to keep in memory most frequently used queries.
+  
+
+  
+Autoprepare hash is local to the backend. It is implicitely reseted on any change of database schema or
+session variables.
+  
+
+  
+It is possible to inspect autoprepared queries in the backend using
+pg_autoprepared_statements view. It shows original text of the
+query, types of the extracted parameters (replacing literals),
+query execution counter and used memory.
+  
+
+ 
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index 6612f95..a285f6d 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -5339,6 +5339,39 @@ SELECT * FROM parent WHERE key = 2400;
   
  
 
+ 
+   autoprepare_limit (integer/type>)
+   
+ autoprepare_limit configuration parameter
+   
+   
+   
+ 
+   Maximal number of autoprepared queries.
+   Zero value disables autoprepare, -1 means unlimited number of autoprepared queries.
+   Too large number of prepared queries can cause backend memory overflow and slowdown execution speed
+   (because of increased lookup time). Default value is 0.
+ 
+   
+ 
+
+ 
+   autoprepare_memory_limit (integer/type>)
+   
+ autoprepare_memory_limit configuration parameter
+   
+   
+   
+ 
+   Maximal size of memory used by autoprepared queries.
+   Zero value disables autoprepare, -1 means means that there is no memory limit.
+   Default value is 0. Calculating memory used by prepared queries adds some extra overhead,
+   so positive value of this parameter may cause some slowdown.
+   autoprepare_limit is much faster way to limit number of autoprepared statements.
+ 
+   
+ 
+
  
 

diff --git a/doc/src/sgml/filelist.sgml b/doc/src/sgml/filelist.sgml
index 3da2365..4bd9f31 100644
--- a/doc/src/sgml/filelist.sgml
+++ b/doc/src/sgml/filelist.sgml
@@ -22,6 +22,7 @@
 
 
 
+
 
 
 
diff --git a/doc/src/sgml/postgres.sgml b/doc/src/sgml/postgres.sgml
index e59cba7..a479f36 100644
--- a/doc/src/sgml/postgres.sgml
+++ b/doc/src/sgml/postgres.sgml
@@ -109,6 +109,7 @@
   
   
   
+  
 
  
 
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index 9fe4a47..7e06389 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -332,6 +332,9 @@ CREATE VIEW pg_prepared_xacts AS
 CREATE VIEW pg_prepared_statements AS
 SELECT * FROM pg_prepared_statement() AS P;
 
+CREATE 

Re: [HACKERS] Cached plans and statement generalization

[Alvaro Herrera]

This patch fails the "rules" tests. Please fix.


-- 
Álvaro Herrerahttps://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: [HACKERS] Cached plans and statement generalization

[Konstantin Knizhnik]

On 01.08.2019 19:56, Konstantin Knizhnik wrote:



On 31.07.2019 19:56, Heikki Linnakangas wrote:

On 09/07/2019 23:59, Konstantin Knizhnik wrote:

Fixed patch version of the path is attached.


Much of the patch and the discussion has been around the raw parsing, 
and guessing which literals are actually parameters that have been 
inlined into the SQL text. Do we really need to do that, though? The 
documentation mentions pgbouncer and other connection poolers, where 
you don't have session state, as a use case for this. But such 
connection poolers could and should still be using the extended query 
protocol, with Parse+Bind messages and query parameters, even if they 
don't use named prepared statements. I'd want to encourage 
applications and middleware to use out-of-band query parameters, to 
avoid SQL injection attacks, regardless of whether they use prepared 
statements or cache query plans. So how about dropping all the raw 
parse tree stuff, and doing the automatic caching of plans based on 
the SQL string, somewhere in the exec_parse_message? Check the 
autoprepare cache in exec_parse_message(), if it was an "unnamed" 
prepared statement, i.e. if the prepared statement name is an empty 
string.


(I'm actually not sure if exec_parse/bind_message is the right place 
for this, but I saw that your current patch did it in 
exec_simple_query, and exec_parse/bind_message are the equivalent of 
that for the extended query protocol).


- Heikki


I decided to implement your proposal. Much simple version of 
autoprepare patch is attached.

At my computer I got the following results:

 pgbench -M simple -S 22495 TPS
 pgbench -M extended -S    47633 TPS
 pgbench -M prepared -S    47683 TPS


So autoprepare speedup execution of queries sent using extended 
protocol more than twice and it is almost the same as with explicitly 
prepared statements.
I failed to create regression test for it because I do not know how to 
force psql to use extended protocol. Any advice is welcome.




Slightly improved and rebased version of the patch.


--
Konstantin Knizhnik
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

diff --git a/doc/src/sgml/autoprepare.sgml b/doc/src/sgml/autoprepare.sgml
new file mode 100644
index 000..c4d96e6
--- /dev/null
+++ b/doc/src/sgml/autoprepare.sgml
@@ -0,0 +1,62 @@
+
+
+ 
+  Autoprepared statements
+
+  
+   autoprepared statements
+  
+
+  
+PostgreSQL makes it possible prepare
+frequently used statements to eliminate cost of their compilation
+and optimization on each execution of the query. On simple queries
+(like ones in pgbench -S) using prepared statements
+increase performance more than two times.
+  
+
+  
+Unfortunately not all database applications are using prepared statements
+and, moreover, it is not always possible. For example, in case of using
+pgbouncer or any other session pooler,
+there is no session state (transactions of one client may be executed at different
+backends) and so prepared statements can not be used.
+  
+
+  
+Autoprepare mode allows to overcome this limitation.
+In this mode Postgres stores plans for all queries pass using extended protocol.
+Speed of execution of autoprepared statements is almost the same as of explicitly prepared statements.
+  
+
+  
+By default autoprepare mode is switched off. To enable it, assign non-zero
+value to GUC variable autoprepare_limit or autoprepare_memory_limit.
+This variables specify limit for number of autoprepared statement or memory used by them.
+Autoprepare is enabled if one of thme is non zero. Value -1 means unlimited.
+Please notice that event autoprepare is anabled, Postgres makes a decision about using
+generalized plan vs. customized execution plans based on the results
+of comparison of average time of five customized plans with
+time of generalized plan.
+  
+
+  
+Too large number of autoprepared statements can cause memory overflow
+(especially if there are many active clients, because prepared statements cache
+is local to the backend). So using unlimited autoprepare hash is debgerous and not recommended.
+Postgres is using LRU policy to keep in memory most frequently used queries.
+  
+
+  
+Autoprepare hash is local to the backend. It is implicitely reseted on any change of database schema or
+session variables.
+  
+
+  
+It is possible to inspect autoprepared queries in the backend using
+pg_autoprepared_statements view. It shows original text of the
+query, types of the extracted parameters (replacing literals),
+query execution counter and used memory.
+  
+
+ 
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index cdc30fa..28c9343 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -5326,6 +5326,39 @@ SELECT * FROM parent WHERE key = 2400;
   
  
 
+ 
+   autoprepare_limit 

Re: [HACKERS] Cached plans and statement generalization

[Daniel Migowski]

Am 02.08.2019 um 10:57 schrieb Konstantin Knizhnik:

On 02.08.2019 11:25, Daniel Migowski wrote:


I have two suggestions however:

1. Please allow to gather information about the autoprepared 
statements by returning them in pg_prepared_statements view. I would 
love to monitor usage of them as well as the memory consumption that 
occurs. I suggested a patch to display that in 
https://www.postgresql.org/message-id/41ed3f5450c90f4d8381bc4d8df6bbdcf02e1...@exchangeserver.ikoffice.de 



Sorry, but there is pg_autoprepared_statements view. I think that it 
will be better to distinguish explicitly and implicitly prepared 
statements, will not it?
Do you want to add more information to this view? Right now it shows 
query string, types of parameters and number of this query execution.
My sorry, I didn't notice it in the patch. If there is another view for 
those that's perfectly fine.
2. Please not only use a LRU list, but maybe something which would 
prefer statements that get reused at all. We often create ad hoc 
statements with parameters which don't really need to be kept. Maybe 
I can suggest an implementation of an LRU list where a reusal of a 
statement not only pulls it to the top of the list but also increases 
a reuse counter. When then a statement would drop off the end of the 
list one checks if the reusal count is non-zero, and only really 
drops it if the resual count is zero. Else the reusal count is 
decremented (or halved) and the element is also placed at the start 
of the LRU list, so it is kept a bit longer.


There are many variations of LRU and alternatives: clock, segmented 
LRU, ...
I agree that classical LRU may be not the best replacement policy for 
autoprepare.
Application is either use static queries, either constructing them 
dynamically. It will be better to distinguish queries executed at 
least twice from one-shot queries.
So may be SLRU will be the best choice. But actually situation you 
have described (We often create ad hoc statements with parameters 
which don't really need to be kept)
is not applicable to atutoprepare. Autoprepare deals only with 
statements which are actually executed.


We have another patch which limits number of stored prepared plans to 
avoid memory overflow (in case of using stored procedures which 
implicitly prepare all issued queries).

Here choice of replacement policy is more critical.


Alright, just wanted to have something better than a LRU, so it is not a 
stepback from the implementation in the JDBC driver for us.


Kind regards,
Daniel Migowski

Re: [HACKERS] Cached plans and statement generalization

[Konstantin Knizhnik]

On 02.08.2019 11:25, Daniel Migowski wrote:

Am 01.08.2019 um 18:56 schrieb Konstantin Knizhnik:
I decided to implement your proposal. Much simple version of 
autoprepare patch is attached.

At my computer I got the following results:

 pgbench -M simple -S 22495 TPS
 pgbench -M extended -S    47633 TPS
 pgbench -M prepared -S    47683 TPS


So autoprepare speedup execution of queries sent using extended 
protocol more than twice and it is almost the same as with explicitly 
prepared statements.
I failed to create regression test for it because I do not know how 
to force psql to use extended protocol. Any advice is welcome.


I am very interested in such a patch, because currently I use the same 
functionality within my JDBC driver and having this directly in 
PostgreSQL would surely speed things up.


I have two suggestions however:

1. Please allow to gather information about the autoprepared 
statements by returning them in pg_prepared_statements view. I would 
love to monitor usage of them as well as the memory consumption that 
occurs. I suggested a patch to display that in 
https://www.postgresql.org/message-id/41ed3f5450c90f4d8381bc4d8df6bbdcf02e1...@exchangeserver.ikoffice.de


Sorry, but there is pg_autoprepared_statements view. I think that it 
will be better to distinguish explicitly and implicitly prepared 
statements, will not it?
Do you want to add more information to this view? Right now it shows 
query string, types of parameters and number of this query execution.


2. Please not only use a LRU list, but maybe something which would 
prefer statements that get reused at all. We often create ad hoc 
statements with parameters which don't really need to be kept. Maybe I 
can suggest an implementation of an LRU list where a reusal of a 
statement not only pulls it to the top of the list but also increases 
a reuse counter. When then a statement would drop off the end of the 
list one checks if the reusal count is non-zero, and only really drops 
it if the resual count is zero. Else the reusal count is decremented 
(or halved) and the element is also placed at the start of the LRU 
list, so it is kept a bit longer.



There are many variations of LRU and alternatives: clock, segmented LRU, ...
I agree that classical LRU may be not the best replacement policy for 
autoprepare.
Application is either use static queries, either constructing them 
dynamically. It will be better to distinguish queries executed at least 
twice from one-shot queries.
So may be SLRU will be the best choice. But actually situation you have 
described (We often create ad hoc statements with parameters which don't 
really need to be kept)
is not applicable to atutoprepare. Autoprepare deals only with 
statements which are actually executed.


We have another patch which limits number of stored prepared plans to 
avoid memory overflow (in case of using stored procedures which 
implicitly prepare all issued queries).

Here choice of replacement policy is more critical.




Thanks,

Daniel Migowski






--
Konstantin Knizhnik
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

Re: [HACKERS] Cached plans and statement generalization

[Daniel Migowski]

Am 01.08.2019 um 18:56 schrieb Konstantin Knizhnik:
I decided to implement your proposal. Much simple version of 
autoprepare patch is attached.

At my computer I got the following results:

 pgbench -M simple -S 22495 TPS
 pgbench -M extended -S    47633 TPS
 pgbench -M prepared -S    47683 TPS


So autoprepare speedup execution of queries sent using extended 
protocol more than twice and it is almost the same as with explicitly 
prepared statements.
I failed to create regression test for it because I do not know how to 
force psql to use extended protocol. Any advice is welcome.


I am very interested in such a patch, because currently I use the same 
functionality within my JDBC driver and having this directly in 
PostgreSQL would surely speed things up.


I have two suggestions however:

1. Please allow to gather information about the autoprepared statements 
by returning them in pg_prepared_statements view. I would love to 
monitor usage of them as well as the memory consumption that occurs. I 
suggested a patch to display that in 
https://www.postgresql.org/message-id/41ed3f5450c90f4d8381bc4d8df6bbdcf02e1...@exchangeserver.ikoffice.de


2. Please not only use a LRU list, but maybe something which would 
prefer statements that get reused at all. We often create ad hoc 
statements with parameters which don't really need to be kept. Maybe I 
can suggest an implementation of an LRU list where a reusal of a 
statement not only pulls it to the top of the list but also increases a 
reuse counter. When then a statement would drop off the end of the list 
one checks if the reusal count is non-zero, and only really drops it if 
the resual count is zero. Else the reusal count is decremented (or 
halved) and the element is also placed at the start of the LRU list, so 
it is kept a bit longer.


Thanks,

Daniel Migowski

Re: [HACKERS] Cached plans and statement generalization

[Konstantin Knizhnik]

On 31.07.2019 19:56, Heikki Linnakangas wrote:

On 09/07/2019 23:59, Konstantin Knizhnik wrote:

Fixed patch version of the path is attached.


Much of the patch and the discussion has been around the raw parsing, 
and guessing which literals are actually parameters that have been 
inlined into the SQL text. Do we really need to do that, though? The 
documentation mentions pgbouncer and other connection poolers, where 
you don't have session state, as a use case for this. But such 
connection poolers could and should still be using the extended query 
protocol, with Parse+Bind messages and query parameters, even if they 
don't use named prepared statements. I'd want to encourage 
applications and middleware to use out-of-band query parameters, to 
avoid SQL injection attacks, regardless of whether they use prepared 
statements or cache query plans. So how about dropping all the raw 
parse tree stuff, and doing the automatic caching of plans based on 
the SQL string, somewhere in the exec_parse_message? Check the 
autoprepare cache in exec_parse_message(), if it was an "unnamed" 
prepared statement, i.e. if the prepared statement name is an empty 
string.


(I'm actually not sure if exec_parse/bind_message is the right place 
for this, but I saw that your current patch did it in 
exec_simple_query, and exec_parse/bind_message are the equivalent of 
that for the extended query protocol).


- Heikki


I decided to implement your proposal. Much simple version of autoprepare 
patch is attached.

At my computer I got the following results:

 pgbench -M simple -S 22495 TPS
 pgbench -M extended -S    47633 TPS
 pgbench -M prepared -S    47683 TPS


So autoprepare speedup execution of queries sent using extended protocol 
more than twice and it is almost the same as with explicitly prepared 
statements.
I failed to create regression test for it because I do not know how to 
force psql to use extended protocol. Any advice is welcome.


--
Konstantin Knizhnik
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

diff --git a/doc/src/sgml/autoprepare.sgml b/doc/src/sgml/autoprepare.sgml
new file mode 100644
index 000..ee29e36
--- /dev/null
+++ b/doc/src/sgml/autoprepare.sgml
@@ -0,0 +1,62 @@
+
+
+ 
+  Autoprepared statements
+
+  
+   autoprepared statements
+  
+
+  
+PostgreSQL makes it possible prepare
+frequently used statements to eliminate cost of their compilation
+and optimization on each execution of the query. On simple queries
+(like ones in pgbench -S) using prepared statements
+increase performance more than two times.
+  
+
+  
+Unfortunately not all database applications are using prepared statements
+and, moreover, it is not always possible. For example, in case of using
+pgbouncer or any other session pooler,
+there is no session state (transactions of one client may be executed at different
+backends) and so prepared statements can not be used.
+  
+
+  
+Autoprepare mode allows to overcome this limitation.
+In this mode Postgres stores plans for all queries pass using extended protocol.
+Speed of execution of autoprepared statements is almost the same as of explicitly prepared statements.
+  
+
+  
+By default autoprepare mode is switched off. To enable it, assign non-zero
+value to GUC variable autoprepare_limit or autoprepare_memory_limit.
+This variables specify limit for number of autoprepared statement or memory used by them.
+Autoprepare is enabled if one of thme is non zero. Value -1 means unlimited.
+Please notice that event autoprepare is anabled, Postgres makes a decision about using
+generalized plan vs. customized execution plans based on the results
+of comparison of average time of five customized plans with
+time of generalized plan.
+  
+
+  
+Too large number of autoprepared statements can cause memory overflow
+(especially if there are many active clients, because prepared statements cache
+is local to the backend). So using unlimited autoprepare hash is debgerous and not recommended.
+Postgres is using LRU policy to keep in memory most frequently used queries.
+  
+
+  
+Autoprepare hash is local to the backend. It is implicitely reseted on any change of database schema or
+session variables.
+  
+
+  
+It is possible to inspect autoprepared queries in the backend using
+pg_autoprepared_statements view. It shows original text of the
+query, types of the extracted parameters (replacing literals) and
+query execution counter.
+  
+
+ 
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index c91e3e1..73c07e7 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -5326,6 +5326,39 @@ SELECT * FROM parent WHERE key = 2400;
   
  
 
+ 
+   autoprepare_limit (integer/type>)
+   
+ autoprepare_limit configuration parameter
+   
+   
+   
+   

Re: [HACKERS] Cached plans and statement generalization

[Konstantin Knizhnik]

On 31.07.2019 19:56, Heikki Linnakangas wrote:

On 09/07/2019 23:59, Konstantin Knizhnik wrote:

Fixed patch version of the path is attached.


Much of the patch and the discussion has been around the raw parsing, 
and guessing which literals are actually parameters that have been 
inlined into the SQL text. Do we really need to do that, though? The 
documentation mentions pgbouncer and other connection poolers, where 
you don't have session state, as a use case for this. But such 
connection poolers could and should still be using the extended query 
protocol, with Parse+Bind messages and query parameters, even if they 
don't use named prepared statements. I'd want to encourage 
applications and middleware to use out-of-band query parameters, to 
avoid SQL injection attacks, regardless of whether they use prepared 
statements or cache query plans. So how about dropping all the raw 
parse tree stuff, and doing the automatic caching of plans based on 
the SQL string, somewhere in the exec_parse_message? Check the 
autoprepare cache in exec_parse_message(), if it was an "unnamed" 
prepared statement, i.e. if the prepared statement name is an empty 
string.


(I'm actually not sure if exec_parse/bind_message is the right place 
for this, but I saw that your current patch did it in 
exec_simple_query, and exec_parse/bind_message are the equivalent of 
that for the extended query protocol).


It will significantly simplify this patch and eliminate all complexity 
and troubles  caused by replacing string literals with parameters
if assumption that all client applications are using extended query 
protocol is true.
But I am not sure that we can expect it. At least I myself see many 
applications which are constructing queries by embedding literal values.
May be it is not so good and safe (SQL injection attack), but many 
applications are doing it. And the idea was to improve execution speed

of existed application without changing them.


Also please notice that extended protocol requires passing more message 
which has negative effect on performance.
At my  notebook I get about 21k TPS on "pgbench -S" and 18k TPS on 
"pgbench -M extended -S".
And it is with unix socket connection! I think that in case of remote 
connections difference will be even larger.


So may be committing simple version of this patch which do not need to 
solve any challenged problems is good idea.
But I afraid that it will significantly reduce positive effect of this 
patch.

Re: [HACKERS] Cached plans and statement generalization

[Heikki Linnakangas]

On 09/07/2019 23:59, Konstantin Knizhnik wrote:

Fixed patch version of the path is attached.


Much of the patch and the discussion has been around the raw parsing, 
and guessing which literals are actually parameters that have been 
inlined into the SQL text. Do we really need to do that, though? The 
documentation mentions pgbouncer and other connection poolers, where you 
don't have session state, as a use case for this. But such connection 
poolers could and should still be using the extended query protocol, 
with Parse+Bind messages and query parameters, even if they don't use 
named prepared statements. I'd want to encourage applications and 
middleware to use out-of-band query parameters, to avoid SQL injection 
attacks, regardless of whether they use prepared statements or cache 
query plans. So how about dropping all the raw parse tree stuff, and 
doing the automatic caching of plans based on the SQL string, somewhere 
in the exec_parse_message? Check the autoprepare cache in 
exec_parse_message(), if it was an "unnamed" prepared statement, i.e. if 
the prepared statement name is an empty string.


(I'm actually not sure if exec_parse/bind_message is the right place for 
this, but I saw that your current patch did it in exec_simple_query, and 
exec_parse/bind_message are the equivalent of that for the extended 
query protocol).


- Heikki

Re: [HACKERS] Cached plans and statement generalization

[Konstantin Knizhnik]

On 09.07.2019 15:16, Thomas Munro wrote:

On Tue, Jul 9, 2019 at 7:32 AM Konstantin Knizhnik
 wrote:

Sorry, are you tests autoprepare-16.patch I have sent in the last e-mail?
I can not reproduce the problem with building documentation:

+  autoprepare_threshold (integer/type>)

The problem is that "integer/type>".  (Missing "<").  There more
than one of those.  Not sure why that doesn't fail for you.
or the noise.  I did something stupid in my apply script.



Sorry, there were really several syntax problems.
I didn't understand why I have not noticed them before.
Fixed patch version of the path is attached.
diff --git a/doc/src/sgml/autoprepare.sgml b/doc/src/sgml/autoprepare.sgml
new file mode 100644
index 00..d20a4d0098
--- /dev/null
+++ b/doc/src/sgml/autoprepare.sgml
@@ -0,0 +1,62 @@
+
+
+ 
+  Autoprepared statements
+
+  
+   autoprepared statements
+  
+
+  
+PostgreSQL makes it possible prepare
+frequently used statements to eliminate cost of their compilation
+and optimization on each execution of the query. On simple queries
+(like ones in pgbench -S) using prepared statements
+increase performance more than two times.
+  
+
+  
+Unfortunately not all database applications are using prepared statements
+and, moreover, it is not always possible. For example, in case of using
+pgbouncer or any other session pooler,
+there is no session state (transactions of one client may be executed at different
+backends) and so prepared statements can not be used.
+  
+
+  
+Autoprepare mode allows to overcome this limitation.
+In this mode Postgres tries to generalize executed statements
+and build parameterized plan for them. Speed of execution of
+autoprepared statements is almost the same as of explicitly
+prepared statements.
+  
+
+  
+By default autoprepare mode is switched off. To enable it, assign non-zero
+value to GUC variable autoprepare_threshold.
+This variable specified minimal number of times the statement should be
+executed before it is autoprepared. Please notice that, despite to the
+value of this parameter, Postgres makes a decision about using
+generalized plan vs. customized execution plans based on the results
+of comparison of average time of five customized plans with
+time of generalized plan.
+  
+
+  
+If number of different statements issued by application is large enough,
+then autopreparing all of them can cause memory overflow
+(especially if there are many active clients, because prepared statements cache
+is local to the backend). To prevent growth of backend's memory because of
+autoprepared cache, it is possible to limit number of autoprepared statements
+by setting autoprepare_limit GUC variable. LRU strategy will be used
+to keep in memory most frequently used queries.
+  
+
+  
+It is possible to inspect autoprepared queries in the backend using
+pg_autoprepared_statements view. It shows original text of the
+query, types of the extracted parameters (replacing literals) and
+query execution counter.
+  
+
+ 
diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index f2b9d404cb..cb703f2084 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -8313,6 +8313,11 @@ SCRAM-SHA-256$iteration count:
   prepared statements
  
 
+ 
+  pg_autoprepared_statements
+  autoprepared statements
+ 
+
  
   pg_prepared_xacts
   prepared transactions
@@ -9630,6 +9635,68 @@ SELECT * FROM pg_locks pl LEFT JOIN pg_prepared_xacts ppx
   
  
 
+
+ 
+  pg_autoprepared_statements
+
+  
+   pg_autoprepared_statements
+  
+
+  
+   The pg_autoprepared_statements view displays
+   all the autoprepared statements that are available in the current
+   session. See  for more information about autoprepared
+   statements.
+  
+
+  
+   pg_autoprepared_statements Columns
+
+   
+
+ 
+  Name
+  Type
+  Description
+ 
+
+
+ 
+  statement
+  text
+  
+The query string submitted by the client from which this prepared statement
+was created. Please notice that literals in this statement are not
+replaced with prepared statement placeholders.
+  
+ 
+ 
+  parameter_types
+  regtype[]
+  
+   The expected parameter types for the autoprepared statement in the
+   form of an array of regtype. The OID corresponding
+   to an element of this array can be obtained by casting the
+   regtype value to oid.
+  
+ 
+ 
+  exec_count
+  int8
+  
+Number of times this statement was executed.
+  
+ 
+
+   
+  
+
+  
+   The pg_autoprepared_statements view is read only.
+  
+ 
+
  
   pg_prepared_xacts
 
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index 84341a30e5..8a9fff3756 100644
--- a/doc/src/sgml/config.sgml
+++ 

Re: [HACKERS] Cached plans and statement generalization

[Thomas Munro]

On Tue, Jul 9, 2019 at 7:32 AM Konstantin Knizhnik
 wrote:
> Sorry, are you tests autoprepare-16.patch I have sent in the last e-mail?
> I can not reproduce the problem with building documentation:

+  autoprepare_threshold (integer/type>)

The problem is that "integer/type>".  (Missing "<").  There more
than one of those.  Not sure why that doesn't fail for you.

> Also autoporepare-16.patch doesn't include any junk
>
> src/include/catalog/pg_proc.dat.~1~

Oops, yeah, sorry for the noise.  I did something stupid in my apply script.

-- 
Thomas Munro
https://enterprisedb.com

Re: [HACKERS] Cached plans and statement generalization

[Konstantin Knizhnik]

On 08.07.2019 2:23, Thomas Munro wrote:

On Tue, Jul 2, 2019 at 3:29 AM Konstantin Knizhnik
 wrote:

Attached please find rebased version of the patch.
Also this version can be found in autoprepare branch of this repository
https://github.com/postgrespro/postgresql.builtin_pool.git
on github.

Thanks.  I haven't looked at the code but this seems like interesting
work and I hope it will get some review.  I guess this is bound to use
a lot of memory.  I guess we'd eventually want to figure out how to
share the autoprepared plan cache between sessions, which is obviously
a whole can of worms.

A couple of trivial comments with my CF manager hat on:

1.  Can you please fix the documentation?  It doesn't build.
Obviously reviewing the goals, design and implementation are more
important than the documentation at this point, but if that is fixed
then the CF bot will be able to run check-world every day and we might
learn something about the code.
2.  Accidental editor junk included:  src/include/catalog/pg_proc.dat.~1~



Sorry, are you tests autoprepare-16.patch I have sent in the last e-mail?
I can not reproduce the problem with building documentation:

knizhnik@xps:~/postgresql/doc$ make
make -C ../src/backend generated-headers
make[1]: Entering directory '/home/knizhnik/postgresql/src/backend'
make -C catalog distprep generated-header-symlinks
make[2]: Entering directory '/home/knizhnik/postgresql/src/backend/catalog'
make[2]: Nothing to be done for 'distprep'.
make[2]: Nothing to be done for 'generated-header-symlinks'.
make[2]: Leaving directory '/home/knizhnik/postgresql/src/backend/catalog'
make -C utils distprep generated-header-symlinks
make[2]: Entering directory '/home/knizhnik/postgresql/src/backend/utils'
make[2]: Nothing to be done for 'distprep'.
make[2]: Nothing to be done for 'generated-header-symlinks'.
make[2]: Leaving directory '/home/knizhnik/postgresql/src/backend/utils'
make[1]: Leaving directory '/home/knizhnik/postgresql/src/backend'
make -C src all
make[1]: Entering directory '/home/knizhnik/postgresql/doc/src'
make -C sgml all
make[2]: Entering directory '/home/knizhnik/postgresql/doc/src/sgml'
/usr/bin/xmllint --path . --noout --valid postgres.sgml
/usr/bin/xsltproc --path . --stringparam pg.version '12devel' 
stylesheet.xsl postgres.sgml

cp ./stylesheet.css html/
touch html-stamp
/usr/bin/xmllint --path . --noout --valid postgres.sgml
/usr/bin/xsltproc --path . --stringparam pg.version '12devel' 
stylesheet-man.xsl postgres.sgml

touch man-stamp
make[2]: Leaving directory '/home/knizhnik/postgresql/doc/src/sgml'
make[1]: Leaving directory '/home/knizhnik/postgresql/doc/src'


Also autoporepare-16.patch doesn't include any junk

src/include/catalog/pg_proc.dat.~1~

Re: [HACKERS] Cached plans and statement generalization

[Thomas Munro]

On Tue, Jul 2, 2019 at 3:29 AM Konstantin Knizhnik
 wrote:
> Attached please find rebased version of the patch.
> Also this version can be found in autoprepare branch of this repository
> https://github.com/postgrespro/postgresql.builtin_pool.git
> on github.

Thanks.  I haven't looked at the code but this seems like interesting
work and I hope it will get some review.  I guess this is bound to use
a lot of memory.  I guess we'd eventually want to figure out how to
share the autoprepared plan cache between sessions, which is obviously
a whole can of worms.

A couple of trivial comments with my CF manager hat on:

1.  Can you please fix the documentation?  It doesn't build.
Obviously reviewing the goals, design and implementation are more
important than the documentation at this point, but if that is fixed
then the CF bot will be able to run check-world every day and we might
learn something about the code.
2.  Accidental editor junk included:  src/include/catalog/pg_proc.dat.~1~


--
Thomas Munro
https://enterprisedb.com

Re: [HACKERS] Cached plans and statement generalization

[Konstantin Knizhnik]

On 01.07.2019 12:51, Thomas Munro wrote:

On Wed, Apr 10, 2019 at 12:52 AM Konstantin Knizhnik
 wrote:

New version of the patching disabling autoprepare for rules and handling
planner error.

Hi Konstantin,

This doesn't apply. Could we please have a fresh rebase for the new Commitfest?

Thanks,


Attached please find rebased version of the patch.
Also this version can be found in autoprepare branch of this repository 
https://github.com/postgrespro/postgresql.builtin_pool.git

on github.
diff --git a/doc/src/sgml/autoprepare.sgml b/doc/src/sgml/autoprepare.sgml
new file mode 100644
index 000..b3309bd
--- /dev/null
+++ b/doc/src/sgml/autoprepare.sgml
@@ -0,0 +1,62 @@
+
+
+ 
+  Autoprepared statements
+
+  
+   autoprepared statements
+  
+
+  
+PostgreSQL makes it possible prepare
+frequently used statements to eliminate cost of their compilation
+and optimization on each execution of the query. On simple queries
+(like ones in pgbench -S) using prepared statements
+increase performance more than two times.
+  
+
+  
+Unfortunately not all database applications are using prepared statements
+and, moreover, it is not always possible. For example, in case of using
+pgbouncer or any other session pooler,
+there is no session state (transactions of one client may be executed at different
+backends) and so prepared statements can not be used.
+  
+
+  
+Autoprepare mode allows to overcome this limitation.
+In this mode Postgres tries to generalize executed statements
+and build parameterized plan for them. Speed of execution of
+autoprepared statements is almost the same as of explicitly
+prepared statements.
+  
+
+  
+By default autoprepare mode is switched off. To enable it, assign non-zero
+value to GUC variable autoprepare_tthreshold.
+This variable specified minimal number of times the statement should be
+executed before it is autoprepared. Please notice that, despite to the
+value of this parameter, Postgres makes a decision about using
+generalized plan vs. customized execution plans based on the results
+of comparison of average time of five customized plans with
+time of generalized plan.
+  
+
+  
+If number of different statements issued by application is large enough,
+then autopreparing all of them can cause memory overflow
+(especially if there are many active clients, because prepared statements cache
+is local to the backend). To prevent growth of backend's memory because of
+autoprepared cache, it is possible to limit number of autoprepared statements
+by setting autoprepare_limit GUC variable. LRU strategy will be used
+to keep in memory most frequently used queries.
+  
+
+  
+It is possible to inspect autoprepared queries in the backend using
+pg_autoprepared_statements view. It shows original text of the
+query, types of the extracted parameters (replacing literals) and
+query execution counter.
+  
+
+ 
diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index f2b9d40..cb703f2 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -8314,6 +8314,11 @@ SCRAM-SHA-256$iteration count:
  
 
  
+  pg_autoprepared_statements
+  autoprepared statements
+ 
+
+ 
   pg_prepared_xacts
   prepared transactions
  
@@ -9630,6 +9635,68 @@ SELECT * FROM pg_locks pl LEFT JOIN pg_prepared_xacts ppx
   
  
 
+
+ 
+  pg_autoprepared_statements
+
+  
+   pg_autoprepared_statements
+  
+
+  
+   The pg_autoprepared_statements view displays
+   all the autoprepared statements that are available in the current
+   session. See  for more information about autoprepared
+   statements.
+  
+
+  
+   pg_autoprepared_statements Columns
+
+   
+
+ 
+  Name
+  Type
+  Description
+ 
+
+
+ 
+  statement
+  text
+  
+The query string submitted by the client from which this prepared statement
+was created. Please notice that literals in this statement are not
+replaced with prepared statement placeholders.
+  
+ 
+ 
+  parameter_types
+  regtype[]
+  
+   The expected parameter types for the autoprepared statement in the
+   form of an array of regtype. The OID corresponding
+   to an element of this array can be obtained by casting the
+   regtype value to oid.
+  
+ 
+ 
+  exec_count
+  int8
+  
+Number of times this statement was executed.
+  
+ 
+
+   
+  
+
+  
+   The pg_autoprepared_statements view is read only.
+  
+ 
+
  
   pg_prepared_xacts
 
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index 84341a3..fcbb68b 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -5327,9 +5327,57 @@ SELECT * FROM parent WHERE key = 2400;
  
 
  
+
+ 
+  autoprepare_threshold (integer/type>)
+  
+   

Re: [HACKERS] Cached plans and statement generalization

[Thomas Munro]

On Wed, Apr 10, 2019 at 12:52 AM Konstantin Knizhnik
 wrote:
> New version of the patching disabling autoprepare for rules and handling
> planner error.

Hi Konstantin,

This doesn't apply. Could we please have a fresh rebase for the new Commitfest?

Thanks,

-- 
Thomas Munro
https://enterprisedb.com

Re: [HACKERS] Cached plans and statement generalization

[Konstantin Knizhnik]

New version of the patching disabling autoprepare for rules and handling 
planner error.



--
Konstantin Knizhnik
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

diff --git a/doc/src/sgml/autoprepare.sgml b/doc/src/sgml/autoprepare.sgml
new file mode 100644
index 000..b3309bd
--- /dev/null
+++ b/doc/src/sgml/autoprepare.sgml
@@ -0,0 +1,62 @@
+
+
+ 
+  Autoprepared statements
+
+  
+   autoprepared statements
+  
+
+  
+PostgreSQL makes it possible prepare
+frequently used statements to eliminate cost of their compilation
+and optimization on each execution of the query. On simple queries
+(like ones in pgbench -S) using prepared statements
+increase performance more than two times.
+  
+
+  
+Unfortunately not all database applications are using prepared statements
+and, moreover, it is not always possible. For example, in case of using
+pgbouncer or any other session pooler,
+there is no session state (transactions of one client may be executed at different
+backends) and so prepared statements can not be used.
+  
+
+  
+Autoprepare mode allows to overcome this limitation.
+In this mode Postgres tries to generalize executed statements
+and build parameterized plan for them. Speed of execution of
+autoprepared statements is almost the same as of explicitly
+prepared statements.
+  
+
+  
+By default autoprepare mode is switched off. To enable it, assign non-zero
+value to GUC variable autoprepare_tthreshold.
+This variable specified minimal number of times the statement should be
+executed before it is autoprepared. Please notice that, despite to the
+value of this parameter, Postgres makes a decision about using
+generalized plan vs. customized execution plans based on the results
+of comparison of average time of five customized plans with
+time of generalized plan.
+  
+
+  
+If number of different statements issued by application is large enough,
+then autopreparing all of them can cause memory overflow
+(especially if there are many active clients, because prepared statements cache
+is local to the backend). To prevent growth of backend's memory because of
+autoprepared cache, it is possible to limit number of autoprepared statements
+by setting autoprepare_limit GUC variable. LRU strategy will be used
+to keep in memory most frequently used queries.
+  
+
+  
+It is possible to inspect autoprepared queries in the backend using
+pg_autoprepared_statements view. It shows original text of the
+query, types of the extracted parameters (replacing literals) and
+query execution counter.
+  
+
+ 
diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index f4aabf5..1443a34 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -8240,6 +8240,11 @@ SCRAM-SHA-256$iteration count:
  
 
  
+  pg_autoprepared_statements
+  autoprepared statements
+ 
+
+ 
   pg_prepared_xacts
   prepared transactions
  
@@ -9551,6 +9556,68 @@ SELECT * FROM pg_locks pl LEFT JOIN pg_prepared_xacts ppx
   
  
 
+
+ 
+  pg_autoprepared_statements
+
+  
+   pg_autoprepared_statements
+  
+
+  
+   The pg_autoprepared_statements view displays
+   all the autoprepared statements that are available in the current
+   session. See  for more information about autoprepared
+   statements.
+  
+
+  
+   pg_autoprepared_statements Columns
+
+   
+
+ 
+  Name
+  Type
+  Description
+ 
+
+
+ 
+  statement
+  text
+  
+The query string submitted by the client from which this prepared statement
+was created. Please notice that literals in this statement are not
+replaced with prepared statement placeholders.
+  
+ 
+ 
+  parameter_types
+  regtype[]
+  
+   The expected parameter types for the autoprepared statement in the
+   form of an array of regtype. The OID corresponding
+   to an element of this array can be obtained by casting the
+   regtype value to oid.
+  
+ 
+ 
+  exec_count
+  int8
+  
+Number of times this statement was executed.
+  
+ 
+
+   
+  
+
+  
+   The pg_autoprepared_statements view is read only.
+  
+ 
+
  
   pg_prepared_xacts
 
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index 2166b99..9476710 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -5297,9 +5297,57 @@ SELECT * FROM parent WHERE key = 2400;
  
 
  
+
+ 
+  autoprepare_threshold (integer/type>)
+  
+   autoprepare_threshold configuration parameter
+  
+  
+  
+   
+ Threshold for autopreparing query. The autoprepare_threshold parameter
+ specifies how much times the statement should be executed before generic plan for this statement is generated.
+ Zero value 

Re: [HACKERS] Cached plans and statement generalization

[Konstantin Knizhnik]

New version of the patch with fixed error of autopreparing CTE queries.

--
Konstantin Knizhnik
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

diff --git a/doc/src/sgml/autoprepare.sgml b/doc/src/sgml/autoprepare.sgml
new file mode 100644
index 000..b3309bd
--- /dev/null
+++ b/doc/src/sgml/autoprepare.sgml
@@ -0,0 +1,62 @@
+
+
+ 
+  Autoprepared statements
+
+  
+   autoprepared statements
+  
+
+  
+PostgreSQL makes it possible prepare
+frequently used statements to eliminate cost of their compilation
+and optimization on each execution of the query. On simple queries
+(like ones in pgbench -S) using prepared statements
+increase performance more than two times.
+  
+
+  
+Unfortunately not all database applications are using prepared statements
+and, moreover, it is not always possible. For example, in case of using
+pgbouncer or any other session pooler,
+there is no session state (transactions of one client may be executed at different
+backends) and so prepared statements can not be used.
+  
+
+  
+Autoprepare mode allows to overcome this limitation.
+In this mode Postgres tries to generalize executed statements
+and build parameterized plan for them. Speed of execution of
+autoprepared statements is almost the same as of explicitly
+prepared statements.
+  
+
+  
+By default autoprepare mode is switched off. To enable it, assign non-zero
+value to GUC variable autoprepare_tthreshold.
+This variable specified minimal number of times the statement should be
+executed before it is autoprepared. Please notice that, despite to the
+value of this parameter, Postgres makes a decision about using
+generalized plan vs. customized execution plans based on the results
+of comparison of average time of five customized plans with
+time of generalized plan.
+  
+
+  
+If number of different statements issued by application is large enough,
+then autopreparing all of them can cause memory overflow
+(especially if there are many active clients, because prepared statements cache
+is local to the backend). To prevent growth of backend's memory because of
+autoprepared cache, it is possible to limit number of autoprepared statements
+by setting autoprepare_limit GUC variable. LRU strategy will be used
+to keep in memory most frequently used queries.
+  
+
+  
+It is possible to inspect autoprepared queries in the backend using
+pg_autoprepared_statements view. It shows original text of the
+query, types of the extracted parameters (replacing literals) and
+query execution counter.
+  
+
+ 
diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index f4aabf5..1443a34 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -8240,6 +8240,11 @@ SCRAM-SHA-256$iteration count:
  
 
  
+  pg_autoprepared_statements
+  autoprepared statements
+ 
+
+ 
   pg_prepared_xacts
   prepared transactions
  
@@ -9551,6 +9556,68 @@ SELECT * FROM pg_locks pl LEFT JOIN pg_prepared_xacts ppx
   
  
 
+
+ 
+  pg_autoprepared_statements
+
+  
+   pg_autoprepared_statements
+  
+
+  
+   The pg_autoprepared_statements view displays
+   all the autoprepared statements that are available in the current
+   session. See  for more information about autoprepared
+   statements.
+  
+
+  
+   pg_autoprepared_statements Columns
+
+   
+
+ 
+  Name
+  Type
+  Description
+ 
+
+
+ 
+  statement
+  text
+  
+The query string submitted by the client from which this prepared statement
+was created. Please notice that literals in this statement are not
+replaced with prepared statement placeholders.
+  
+ 
+ 
+  parameter_types
+  regtype[]
+  
+   The expected parameter types for the autoprepared statement in the
+   form of an array of regtype. The OID corresponding
+   to an element of this array can be obtained by casting the
+   regtype value to oid.
+  
+ 
+ 
+  exec_count
+  int8
+  
+Number of times this statement was executed.
+  
+ 
+
+   
+  
+
+  
+   The pg_autoprepared_statements view is read only.
+  
+ 
+
  
   pg_prepared_xacts
 
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index 2166b99..9476710 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -5297,9 +5297,57 @@ SELECT * FROM parent WHERE key = 2400;
  
 
  
+
+ 
+  autoprepare_threshold (integer/type>)
+  
+   autoprepare_threshold configuration parameter
+  
+  
+  
+   
+ Threshold for autopreparing query. The autoprepare_threshold parameter
+ specifies how much times the statement should be executed before generic plan for this statement is generated.
+ Zero value (default) disables 

Re: [HACKERS] Cached plans and statement generalization

[Konstantin Knizhnik]

Thank you very much for the review!

On 19.03.2019 5:56, Yamaji, Ryo wrote:

On Tue, Jan 29, 2019 at 10:46 AM, Konstantin Knizhnik wrote:

Rebased version of the patch is attached.

I'm sorry for the late review.
I confirmed behavior of autoprepare-12.patch. It is summarized below.

・parameter
Expected behavior was shown according to the set value.
However, I think that it is be more kind to describe that autoprepare
hold infinite statements when the setting value of
autoprepare_ (memory_) limit is 0 in the manual.
There is no problem as operation.


Sorry, I do not completely understand your concern.
Description of autoprepare_ (memory_) limit includes explanation of zero 
value:


autoprepare_limit:
0 means unlimited number of autoprepared queries. Too large number of 
prepared queries can cause backend memory overflow and slowdown 
execution speed (because of increased lookup time.


autoprepare_memory_limit:
0 means that there is no memory limit. Calculating memory used by 
prepared queries adds some extra overhead,
so non-zero value of this parameter may cause some slowdown. 
autoprepare_limit is much faster way to limit number of autoprepared 
statements



Do you think that this descriptions are unclear and should be rewritten?


・pg_autoprepared_statements
I confirmed that I could refer properly.

・autoprepare cache retention period
I confirmed that autoprepared statements were deleted when the set
statement or the DDL statement was executed. Although it differs from
the explicit prepare statements, it does not matter as a autoprepare.

・performance
This patch does not confirm the basic performance of autoprepare because
it confirmed that there was no performance problem with the previous
patch (autoprepare-11.patch). However, because it was argued that
performance degradation would occur when prepared statements execute to
a partition table, I expected that autoprepare might exhibit similar
behavior, and measured the performance. I also predicted that the
plan_cache_mode setting does not apply to autoprepare, and we also
measured the plan_cache_mode by conditions.
Below results (this result is TPS)

 plan_cache_mode simple  simple(autoprepare) prepare
 auto130 121 121.5
 force_custom_plan   132.5   90.7122.7
 force_generic_plan  126.7   14.724.7

Performance degradation was observed when plan_cache_mode was specified
for autoprepare. Is this behavior correct? I do not know why this is the
results.

Below performance test procedure

drop table if exists rt;
create table rt (a int, b int, c int) partition by range (a);
\o /dev/null
select 'create table rt' || x::text || ' partition of rt for values from (' ||
  (x)::text || ') to (' || (x+1)::text || ');' from generate_series(0, 1024) x;
\gexec
\o

pgbench -p port -T 60 -c 1 -n -f test.sql (-M prepared) postgres

test.sql
\set a random (0, 1023)
select * from rt where a = :a;
Autoprepare is using the same functions from plancache.c so 
plan_cache_mode settings affect

autoprepare as well as explicitly preprepared statements.

Below are my results of select-only pgbench:

plan_cache_mode simple  simple(autoprepare) prepare
auto23k 42k 50k
force_custom_plan   23k 24k 26k
force_generic_plan  23k 44k 50k


As you can see force_custom_plan slowdowns both explicitly and 
autoprepared statements.
Unfortunately generic plans are not working well with partitioned table 
because disabling partition pruning.

At my system result of your query execution is the following:

plan_cache_mode simple  simple(autoprepare) prepare
auto232 220 219
force_custom_plan   234 175 211
    force_generic_plan  230 48  48


The conclusion is that forcing generic plan can cause slowdown of 
queries on partitioned tables.
If plan cache mode is not enforced, then standard Postgres strategy of 
comparing efficiency of generic and custom plans

works well.

Attached please find rebased version of the patch.


--
Konstantin Knizhnik
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

diff --git a/doc/src/sgml/autoprepare.sgml b/doc/src/sgml/autoprepare.sgml
new file mode 100644
index 000..b3309bd
--- /dev/null
+++ b/doc/src/sgml/autoprepare.sgml
@@ -0,0 +1,62 @@
+
+
+ 
+  Autoprepared statements
+
+  
+   autoprepared statements
+  
+
+  
+PostgreSQL makes it possible prepare
+frequently used statements to eliminate cost of their compilation
+and optimization on each execution of the query. On simple queries
+(like ones in pgbench -S) using prepared statements
+increase performance more than two times.
+  
+
+  
+Unfortunately not all database applications are using prepared statements
+and, moreover, it is not always possible. For example, in case of 

RE: [HACKERS] Cached plans and statement generalization

[Yamaji, Ryo]

On Tue, Jan 29, 2019 at 10:46 AM, Konstantin Knizhnik wrote:
> Rebased version of the patch is attached.

I'm sorry for the late review.
I confirmed behavior of autoprepare-12.patch. It is summarized below.

・parameter
Expected behavior was shown according to the set value.
However, I think that it is be more kind to describe that autoprepare
hold infinite statements when the setting value of
autoprepare_ (memory_) limit is 0 in the manual.
There is no problem as operation.

・pg_autoprepared_statements
I confirmed that I could refer properly.

・autoprepare cache retention period
I confirmed that autoprepared statements were deleted when the set
statement or the DDL statement was executed. Although it differs from
the explicit prepare statements, it does not matter as a autoprepare.

・performance
This patch does not confirm the basic performance of autoprepare because
it confirmed that there was no performance problem with the previous
patch (autoprepare-11.patch). However, because it was argued that
performance degradation would occur when prepared statements execute to
a partition table, I expected that autoprepare might exhibit similar
behavior, and measured the performance. I also predicted that the
plan_cache_mode setting does not apply to autoprepare, and we also
measured the plan_cache_mode by conditions.
Below results (this result is TPS)

plan_cache_mode simple  simple(autoprepare) prepare
auto130 121 121.5
force_custom_plan   132.5   90.7122.7
force_generic_plan  126.7   14.724.7

Performance degradation was observed when plan_cache_mode was specified
for autoprepare. Is this behavior correct? I do not know why this is the
results.

Below performance test procedure

drop table if exists rt;
create table rt (a int, b int, c int) partition by range (a);
\o /dev/null
select 'create table rt' || x::text || ' partition of rt for values from (' ||
 (x)::text || ') to (' || (x+1)::text || ');' from generate_series(0, 1024) x;
\gexec
\o

pgbench -p port -T 60 -c 1 -n -f test.sql (-M prepared) postgres

test.sql
\set a random (0, 1023)
select * from rt where a = :a;

RE: [HACKERS] Cached plans and statement generalization

[Nagaura, Ryohei]

Hi,

On Mon, Jan 28, 2019 at 10:46 PM, Konstantin Knizhnik wrote:
> Rebased version of the patch is attached.
Thank you for your quick rebase.

> This files are just output of test execution and it is not possible to expect 
> lack of
> trailing spaces in output of test scripts execution.
I understand this is because regression tests use exact matching.
I learned a lot. Thanks.

Best regards,
-
Ryohei Nagaura

Re: [HACKERS] Cached plans and statement generalization

[Konstantin Knizhnik]

On 29.01.2019 4:38, Nagaura, Ryohei wrote:

Hi,

Although I became your reviewer, it seems to be difficult to feedback in this 
CF.
I continue to review, so would you update your patch please?
Until then I review your current patch.

There is one question.
date_1.out which maybe is copy of date.out includes trailing space and gaps of 
indent
e.g., line 3368 and 3380 in your current patch have
space in each end of line
different indent.
This is also seen in date.out.
I'm not sure whether it is ok to add new file including the above features just 
because a existing file includes.
Is it ok?

Best regards,
-
Ryohei Nagaura

Rebased version of the patch is attached.
Concerning spaces in date.out and date_1.out - it is ok.
This files are just output of test execution and it is not possible to 
expect lack of trailing spaces in output of test scripts execution.




--
Konstantin Knizhnik
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

diff --git a/doc/src/sgml/autoprepare.sgml b/doc/src/sgml/autoprepare.sgml
new file mode 100644
index 000..b3309bd
--- /dev/null
+++ b/doc/src/sgml/autoprepare.sgml
@@ -0,0 +1,62 @@
+
+
+ 
+  Autoprepared statements
+
+  
+   autoprepared statements
+  
+
+  
+PostgreSQL makes it possible prepare
+frequently used statements to eliminate cost of their compilation
+and optimization on each execution of the query. On simple queries
+(like ones in pgbench -S) using prepared statements
+increase performance more than two times.
+  
+
+  
+Unfortunately not all database applications are using prepared statements
+and, moreover, it is not always possible. For example, in case of using
+pgbouncer or any other session pooler,
+there is no session state (transactions of one client may be executed at different
+backends) and so prepared statements can not be used.
+  
+
+  
+Autoprepare mode allows to overcome this limitation.
+In this mode Postgres tries to generalize executed statements
+and build parameterized plan for them. Speed of execution of
+autoprepared statements is almost the same as of explicitly
+prepared statements.
+  
+
+  
+By default autoprepare mode is switched off. To enable it, assign non-zero
+value to GUC variable autoprepare_tthreshold.
+This variable specified minimal number of times the statement should be
+executed before it is autoprepared. Please notice that, despite to the
+value of this parameter, Postgres makes a decision about using
+generalized plan vs. customized execution plans based on the results
+of comparison of average time of five customized plans with
+time of generalized plan.
+  
+
+  
+If number of different statements issued by application is large enough,
+then autopreparing all of them can cause memory overflow
+(especially if there are many active clients, because prepared statements cache
+is local to the backend). To prevent growth of backend's memory because of
+autoprepared cache, it is possible to limit number of autoprepared statements
+by setting autoprepare_limit GUC variable. LRU strategy will be used
+to keep in memory most frequently used queries.
+  
+
+  
+It is possible to inspect autoprepared queries in the backend using
+pg_autoprepared_statements view. It shows original text of the
+query, types of the extracted parameters (replacing literals) and
+query execution counter.
+  
+
+ 
diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index af4d062..def8dce 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -8169,6 +8169,11 @@ SCRAM-SHA-256$iteration count:
  
 
  
+  pg_autoprepared_statements
+  autoprepared statements
+ 
+
+ 
   pg_prepared_xacts
   prepared transactions
  
@@ -9480,6 +9485,68 @@ SELECT * FROM pg_locks pl LEFT JOIN pg_prepared_xacts ppx
   
  
 
+
+ 
+  pg_autoprepared_statements
+
+  
+   pg_autoprepared_statements
+  
+
+  
+   The pg_autoprepared_statements view displays
+   all the autoprepared statements that are available in the current
+   session. See  for more information about autoprepared
+   statements.
+  
+
+  
+   pg_autoprepared_statements Columns
+
+   
+
+ 
+  Name
+  Type
+  Description
+ 
+
+
+ 
+  statement
+  text
+  
+The query string submitted by the client from which this prepared statement
+was created. Please notice that literals in this statement are not
+replaced with prepared statement placeholders.
+  
+ 
+ 
+  parameter_types
+  regtype[]
+  
+   The expected parameter types for the autoprepared statement in the
+   form of an array of regtype. The OID corresponding
+   to an element of this array can be obtained by casting the
+   regtype value to oid.
+  
+ 
+ 
+  

RE: [HACKERS] Cached plans and statement generalization

[Nagaura, Ryohei]

Hi,

Although I became your reviewer, it seems to be difficult to feedback in this 
CF.
I continue to review, so would you update your patch please?
Until then I review your current patch.

There is one question.
date_1.out which maybe is copy of date.out includes trailing space and gaps of 
indent
e.g., line 3368 and 3380 in your current patch have 
space in each end of line
different indent.
This is also seen in date.out.
I'm not sure whether it is ok to add new file including the above features just 
because a existing file includes.
Is it ok?

Best regards,
-
Ryohei Nagaura

Re: [HACKERS] Cached plans and statement generalization

[Dmitry Dolgov]

> On Fri, Nov 30, 2018 at 3:06 AM Yamaji, Ryo  wrote:
>
> On Fri, Nov 30, 2018 at 3:48 AM, Dmitry Dolgov wrote:
>
> > Hi,
> >
> > Thanks for reviewing. Since another CF is about to end, maybe you can
> > post the full review feedback?
>
> Since I had been busy with my private work, I couldn't review.
> I want to review next commit fest. I am sorry I postponed many times.

No worries, just don't forget to post a review when it will be ready.

RE: [HACKERS] Cached plans and statement generalization

[Yamaji, Ryo]

On Fri, Nov 30, 2018 at 3:48 AM, Dmitry Dolgov wrote:

> Hi,
> 
> Thanks for reviewing. Since another CF is about to end, maybe you can
> post the full review feedback?

Since I had been busy with my private work, I couldn't review.
I want to review next commit fest. I am sorry I postponed many times.

Re: [HACKERS] Cached plans and statement generalization

[Dmitry Dolgov]

> On Fri, Sep 28, 2018 at 10:46 AM Yamaji, Ryo  
> wrote:
>
> On Tuesday, August 7, 2018 at 0:36 AM, Konstantin Knizhnik wrote:
>
> > I have registered the patch for next commitfest.
> > For some reasons it doesn't find the latest autoprepare-10.patch and still
> > refer to autoprepare-6.patch as the latest attachement.
>
> I'm sorry for the late reply. I'm currently reviewing autoprepare.
> I could not make it in time for the commit fests in September,
> but I will continue to review for the next commitfest.
>
> Performance tests are good results. The results are shown below.
> pgbench -s 100 -c 8 -t 1 -S (average of 3 trials)
> - all autoprepare statements use same memory context.
> 18052.22706 TPS
> - each autoprepare statement use separate memory context.
> 18607.95889 TPS
> - calculate memory usage (autoprepare_memory_limit)
> 19171.60457 TPS
>
> From the above results, I think that adding/changing functions
> will not affect performance. I am currently checking the behavior
> when autoprepare_memory_limit is specified.

Hi,

Thanks for reviewing. Since another CF is about to end, maybe you can post the
full review feedback?

> On Tue, Jul 31, 2018 at 11:30 PM Konstantin Knizhnik 
>  wrote:
>
> Concerning next commit fest - I am not sure.
> At previous commitfest it was returned with feedback that it "has
> received no review or comments since last May".
> May be your review will help to change this situation.

Well, I think that wasn't the only reason, and having some balance here would
be nice. Anyway, thanks for working on this patch!

Unfortunately, the patch needs to be rebased one more time. Also, I see there
were concerns about how reliable this patch is. Just out of curiosity, can you
tell now that from v4 to v11 reliability is not a concern anymore?

(If you don't mind, I'll also CC some of the reviewers who saw previous
versions).

RE: [HACKERS] Cached plans and statement generalization

[Yamaji, Ryo]

On Tuesday, August 7, 2018 at 0:36 AM, Konstantin Knizhnik wrote:

> I have registered the patch for next commitfest.
> For some reasons it doesn't find the latest autoprepare-10.patch and still
> refer to autoprepare-6.patch as the latest attachement.

I'm sorry for the late reply. I'm currently reviewing autoprepare.
I could not make it in time for the commit fests in September, 
but I will continue to review for the next commitfest.

Performance tests are good results. The results are shown below.
pgbench -s 100 -c 8 -t 1 -S (average of 3 trials)
- all autoprepare statements use same memory context.
18052.22706 TPS
- each autoprepare statement use separate memory context.
18607.95889 TPS
- calculate memory usage (autoprepare_memory_limit)
19171.60457 TPS

From the above results, I think that adding/changing functions
will not affect performance. I am currently checking the behavior
when autoprepare_memory_limit is specified.

Re: [HACKERS] Cached plans and statement generalization

[Konstantin Knizhnik]

On 22.08.2018 07:54, Yamaji, Ryo wrote:

On Tuesday, August 7, 2018 at 0:36 AM, Konstantin Knizhnik wrote:


I have registered the patch for next commitfest.
For some reasons it doesn't find the latest autoprepare-10.patch and still
refer to autoprepare-6.patch as the latest attachement.

I am sorry for the long delay in my response.
I confirmed it and become reviewer the patch.
I am going to review.



Right now each prepared statement has two memory contexts: one for raw
parse tree used as hash table key and another for cached plan itself.
May be it will be possible to combine them. To calculate memory consumed
by cached plans, it will be necessary to calculate memory usage statistic
for all this contexts (which requires traversal of all context's chunks)
and sum them. It is much more complex and expensive than current check:
(++autoprepare_cached_plans > autoprepare_limit) although I so not think
that it will have measurable impact on performance...
May be there should be some faster way to estimate memory consumed by
prepared statements.

So, the current autoprepare_limit allows to limit number of autoprepared
statements and prevent memory overflow caused by execution of larger
number of different statements.
The question is whether we need more precise mechanism which will take
in account difference between small and large queries. Definitely simple
query can require 10-100 times less memory than complex query. But memory
contexts themselves (even with small block size) somehow minimize
difference in memory footprint of different queries, because of chunked
allocation.

Thank you for telling me how to implement and its problem.
In many cases when actually designing a system, we estimate the amount of memory
to use and prepare a memory accordingly. Therefore, if we need to estimate 
memory
usage in both patterns that are limit amount of memory used by prepare queries 
or
number of prepared statements, I think that there is no problem with the current
implementation.

Apart from the patch, if it can implement an application that outputs estimates 
of
memory usage when we enter a query, you may be able to use autoprepare more 
comfortably.
But it sounds difficult..


I have added autoprepare_memory_limit parameter which allows to limit 
memory used by  autoprepared statements rather than number of such 
statements.
If value of this parameter is non zero, then  total size of all memory 
contexts used for autoprepared statement is maintained (can be 
innsoected in debugger in autoprepare_used_memory variable).
As I already noticed, calculating memory context size adds extra 
overhead but I do not notice any influence in performance. New version 
of the patch is attached.




--
Konstantin Knizhnik
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

diff --git a/doc/src/sgml/autoprepare.sgml b/doc/src/sgml/autoprepare.sgml
new file mode 100644
index 000..b3309bd
--- /dev/null
+++ b/doc/src/sgml/autoprepare.sgml
@@ -0,0 +1,62 @@
+
+
+ 
+  Autoprepared statements
+
+  
+   autoprepared statements
+  
+
+  
+PostgreSQL makes it possible prepare
+frequently used statements to eliminate cost of their compilation
+and optimization on each execution of the query. On simple queries
+(like ones in pgbench -S) using prepared statements
+increase performance more than two times.
+  
+
+  
+Unfortunately not all database applications are using prepared statements
+and, moreover, it is not always possible. For example, in case of using
+pgbouncer or any other session pooler,
+there is no session state (transactions of one client may be executed at different
+backends) and so prepared statements can not be used.
+  
+
+  
+Autoprepare mode allows to overcome this limitation.
+In this mode Postgres tries to generalize executed statements
+and build parameterized plan for them. Speed of execution of
+autoprepared statements is almost the same as of explicitly
+prepared statements.
+  
+
+  
+By default autoprepare mode is switched off. To enable it, assign non-zero
+value to GUC variable autoprepare_tthreshold.
+This variable specified minimal number of times the statement should be
+executed before it is autoprepared. Please notice that, despite to the
+value of this parameter, Postgres makes a decision about using
+generalized plan vs. customized execution plans based on the results
+of comparison of average time of five customized plans with
+time of generalized plan.
+  
+
+  
+If number of different statements issued by application is large enough,
+then autopreparing all of them can cause memory overflow
+(especially if there are many active clients, because prepared statements cache
+is local to the backend). To prevent growth of backend's memory because of
+autoprepared cache, it is possible to limit number of autoprepared statements
+by setting autoprepare_limit 

RE: [HACKERS] Cached plans and statement generalization

[Yamaji, Ryo]

On Tuesday, August 7, 2018 at 0:36 AM, Konstantin Knizhnik wrote: 

> I have registered the patch for next commitfest.
> For some reasons it doesn't find the latest autoprepare-10.patch and still
> refer to autoprepare-6.patch as the latest attachement.

I am sorry for the long delay in my response.
I confirmed it and become reviewer the patch.
I am going to review.


> Right now each prepared statement has two memory contexts: one for raw
> parse tree used as hash table key and another for cached plan itself.
> May be it will be possible to combine them. To calculate memory consumed
> by cached plans, it will be necessary to calculate memory usage statistic
> for all this contexts (which requires traversal of all context's chunks)
> and sum them. It is much more complex and expensive than current check:
> (++autoprepare_cached_plans > autoprepare_limit) although I so not think
> that it will have measurable impact on performance...
> May be there should be some faster way to estimate memory consumed by
> prepared statements.
> 
> So, the current autoprepare_limit allows to limit number of autoprepared
> statements and prevent memory overflow caused by execution of larger
> number of different statements.
> The question is whether we need more precise mechanism which will take
> in account difference between small and large queries. Definitely simple
> query can require 10-100 times less memory than complex query. But memory
> contexts themselves (even with small block size) somehow minimize
> difference in memory footprint of different queries, because of chunked
> allocation.

Thank you for telling me how to implement and its problem.
In many cases when actually designing a system, we estimate the amount of memory
to use and prepare a memory accordingly. Therefore, if we need to estimate 
memory
usage in both patterns that are limit amount of memory used by prepare queries 
or
number of prepared statements, I think that there is no problem with the current
implementation.

Apart from the patch, if it can implement an application that outputs estimates 
of
memory usage when we enter a query, you may be able to use autoprepare more 
comfortably.
But it sounds difficult..

Re: [HACKERS] Cached plans and statement generalization

[Konstantin Knizhnik]

On 07.08.2018 13:02, Yamaji, Ryo wrote:


I want to confirm one point.
If I will have reviewed the autoprepare patch, then are you ready to register
the patch at commit fest in the near future? I fear that autoprepare patch do
not registered at commit fest in the future (for example, you are so busy), and
do not applied to PostgreSQL. If you are not ready to register the patch, I 
think
I want to register at commit fest instead of you.


I have registered the patch for next commitfest.
For some reasons it doesn't find the latest autoprepare-10.patch and 
still refer to autoprepare-6.patch as the latest attachement.






I agree it may be more useful to limit amount of memory used by prepare
queries, rather than number of prepared statements.
But it is just more difficult to calculate and maintain (I am not sure
that just looking at CacheMemoryContext is enough for it).
Also, if working set of queries (frequently repeated queries) doesn't
fir in memory, then autoprepare will be almost useless (because with
high probability
prepared query will be thrown away from the cache before it can be
reused). So limiting queries from "application side" seems to be more
practical.

I see. But I fear that autoprepare process uses irregularity amount of memory
when autoprepare_limit is specified number of prepared statements. I think
that there is scene that autoprepare process use a lot of memory (ex. it
need to prepare a lot of long queries), then other processes (ex. other
backend process in PostgreSQL or process other than PostgreSQL) cannot use
memory. I hope to specify limit amount of memory in the future.


Right now each prepared statement has two memory contexts: one for raw 
parse tree used as hash table key and another for cached plan itself.
May be it will be possible to combine them. To calculate memory consumed 
by cached plans, it will be necessary to calculate memory usage 
statistic for all this contexts (which requires traversal of all 
context's chunks) and sum them. It is much more complex and expensive 
than current check: (++autoprepare_cached_plans > autoprepare_limit)
although I so not think that it will have measurable impact on 
performance...
May be there should be some faster way to estimate memory consumed by 
prepared statements.


So, the current autoprepare_limit allows to limit number of autoprepared 
statements and prevent memory overflow caused by execution of larger 
number of different statements.
The question is whether we need more precise mechanism which will take 
in account difference between small and large queries. Definitely simple 
query can require 10-100 times less memory than complex query. But 
memory contexts themselves (even with small block size) somehow minimize 
difference in memory footprint of different queries, because of chunked 
allocation.




--
Konstantin Knizhnik
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

RE: [HACKERS] Cached plans and statement generalization

[Yamaji, Ryo]

> -Original Message-
> From: Konstantin Knizhnik [mailto:k.knizh...@postgrespro.ru]
> Sent: Friday, August 3, 2018 7:02 AM
> To: Yamaji, Ryo/山地 亮 
> Cc: PostgreSQL mailing lists 
> Subject: Re: [HACKERS] Cached plans and statement generalization
>
> Thank you.
> Unfortunately expression_tree_walker is not consistent with copyObject:
> I tried to use this walker to destroy raw parse tree of autoprepared
> statement, but looks like some nodes are not visited by
> expression_tree_walker. I have to create separate memory context for each
> autoprepared statement.
> New version  of the patch is attached.

Thank you for attaching the patch.
The improvement to plan cache context by the new patch was confirmed.


> This patch will be included in next release of PgPro EE.
> Concerning next commit fest - I am not sure.
> At previous commitfest it was returned with feedback that it "has received no 
> review or comments since last May".
> May be your review will help to change this situation.

I want to confirm one point.
If I will have reviewed the autoprepare patch, then are you ready to register
the patch at commit fest in the near future? I fear that autoprepare patch do
not registered at commit fest in the future (for example, you are so busy), and
do not applied to PostgreSQL. If you are not ready to register the patch, I 
think
I want to register at commit fest instead of you.


> I agree it may be more useful to limit amount of memory used by prepare 
> queries, rather than number of prepared statements.
> But it is just more difficult to calculate and maintain (I am not sure 
> that just looking at CacheMemoryContext is enough for it).
> Also, if working set of queries (frequently repeated queries) doesn't 
> fir in memory, then autoprepare will be almost useless (because with 
> high probability
> prepared query will be thrown away from the cache before it can be 
> reused). So limiting queries from "application side" seems to be more 
> practical.

I see. But I fear that autoprepare process uses irregularity amount of memory
when autoprepare_limit is specified number of prepared statements. I think
that there is scene that autoprepare process use a lot of memory (ex. it
need to prepare a lot of long queries), then other processes (ex. other
backend process in PostgreSQL or process other than PostgreSQL) cannot use
memory. I hope to specify limit amount of memory in the future.

Re: [HACKERS] Cached plans and statement generalization

[Konstantin Knizhnik]

On 02.08.2018 08:25, Yamaji, Ryo wrote:

-Original Message-
From: Konstantin Knizhnik [mailto:k.knizh...@postgrespro.ru]
Sent: Wednesday, August 1, 2018 4:53 PM
To: Yamaji, Ryo/山地 亮 
Cc: PostgreSQL mailing lists 
Subject: Re: [HACKERS] Cached plans and statement generalization

I failed to reproduce the problem.
I used the following non-default configuration parameters:

autoprepare_limit=1
autoprepare_threshold=1

create dummy database:

create table foo(x integer primary key, y integer); insert into foo values
(generate_series(1,1), 0);

and run different queries,  like:

postgres=# select *  from foo where x=1; postgres=# select *  from foo
where x+x=1; postgres=# select *  from foo where x+x+x=1; postgres=#
select *  from foo where x+x+x+x=1; ...

and check size of CacheMemoryContext using gdb - it is not increased.

Can you please send me your test?


I checked not CacheMemoryContext but "plan cache context".
Because I think that the memory context that autoprepare mainly uses is "plan cache 
context".

Non-default configuration parameter was used as well as Konstantin.
autoprepare_limit=1
autoprepare_threshold=1

The procedure of the test that I did is shown below.

create dummy database
create table test (key1 integer, key2 integer, ... , key100 integer);
insert into test values (1, 2, ... , 100);

And, different queries are executed.
select key1 from test where key1=1 and key2=2 and ... and key100=100;
select key2 from test where key1=1 and key2=2 and ... and key100=100;
select key3 from test where key1=1 and key2=2 and ... and key100=100;...

And, "plan cache context" was confirmed by using gdb.





Thank you.
Unfortunately expression_tree_walker is not consistent with copyObject: 
I tried to use this walker to destroy raw parse tree of autoprepared 
statement, but looks
like some nodes are not visited by expression_tree_walker. I have to 
create separate memory context for each autoprepared statement.

New version  of the patch is attached.
diff --git a/doc/src/sgml/autoprepare.sgml b/doc/src/sgml/autoprepare.sgml
new file mode 100644
index 000..b3309bd
--- /dev/null
+++ b/doc/src/sgml/autoprepare.sgml
@@ -0,0 +1,62 @@
+
+
+ 
+  Autoprepared statements
+
+  
+   autoprepared statements
+  
+
+  
+PostgreSQL makes it possible prepare
+frequently used statements to eliminate cost of their compilation
+and optimization on each execution of the query. On simple queries
+(like ones in pgbench -S) using prepared statements
+increase performance more than two times.
+  
+
+  
+Unfortunately not all database applications are using prepared statements
+and, moreover, it is not always possible. For example, in case of using
+pgbouncer or any other session pooler,
+there is no session state (transactions of one client may be executed at different
+backends) and so prepared statements can not be used.
+  
+
+  
+Autoprepare mode allows to overcome this limitation.
+In this mode Postgres tries to generalize executed statements
+and build parameterized plan for them. Speed of execution of
+autoprepared statements is almost the same as of explicitly
+prepared statements.
+  
+
+  
+By default autoprepare mode is switched off. To enable it, assign non-zero
+value to GUC variable autoprepare_tthreshold.
+This variable specified minimal number of times the statement should be
+executed before it is autoprepared. Please notice that, despite to the
+value of this parameter, Postgres makes a decision about using
+generalized plan vs. customized execution plans based on the results
+of comparison of average time of five customized plans with
+time of generalized plan.
+  
+
+  
+If number of different statements issued by application is large enough,
+then autopreparing all of them can cause memory overflow
+(especially if there are many active clients, because prepared statements cache
+is local to the backend). To prevent growth of backend's memory because of
+autoprepared cache, it is possible to limit number of autoprepared statements
+by setting autoprepare_limit GUC variable. LRU strategy will be used
+to keep in memory most frequently used queries.
+  
+
+  
+It is possible to inspect autoprepared queries in the backend using
+pg_autoprepared_statements view. It shows original text of the
+query, types of the extracted parameters (replacing literals) and
+query execution counter.
+  
+
+ 
diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index fffb79f..26f561d 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -8230,6 +8230,11 @@ SCRAM-SHA-256$iteration count:
  
 
  
+  pg_autoprepared_statements
+  autoprepared statements
+ 
+
+ 
   pg_prepared_xacts
   prepared transactions
  
@@ -9541,6 +9546,68 @@ SELECT * FROM pg_locks pl L

RE: [HACKERS] Cached plans and statement generalization

[Yamaji, Ryo]

> -Original Message-
> From: Konstantin Knizhnik [mailto:k.knizh...@postgrespro.ru]
> Sent: Wednesday, August 1, 2018 4:53 PM
> To: Yamaji, Ryo/山地 亮 
> Cc: PostgreSQL mailing lists 
> Subject: Re: [HACKERS] Cached plans and statement generalization
> 
> I failed to reproduce the problem.
> I used the following non-default configuration parameters:
> 
> autoprepare_limit=1
> autoprepare_threshold=1
> 
> create dummy database:
> 
> create table foo(x integer primary key, y integer); insert into foo values
> (generate_series(1,1), 0);
> 
> and run different queries,  like:
> 
> postgres=# select *  from foo where x=1; postgres=# select *  from foo
> where x+x=1; postgres=# select *  from foo where x+x+x=1; postgres=#
> select *  from foo where x+x+x+x=1; ...
> 
> and check size of CacheMemoryContext using gdb - it is not increased.
> 
> Can you please send me your test?


I checked not CacheMemoryContext but "plan cache context". 
Because I think that the memory context that autoprepare mainly uses is "plan 
cache context". 

Non-default configuration parameter was used as well as Konstantin. 
autoprepare_limit=1
autoprepare_threshold=1

The procedure of the test that I did is shown below. 

create dummy database
create table test (key1 integer, key2 integer, ... , key100 integer);
insert into test values (1, 2, ... , 100);

And, different queries are executed. 
select key1 from test where key1=1 and key2=2 and ... and key100=100;
select key2 from test where key1=1 and key2=2 and ... and key100=100;
select key3 from test where key1=1 and key2=2 and ... and key100=100;...

And, "plan cache context" was confirmed by using gdb.

Re: [HACKERS] Cached plans and statement generalization

[Konstantin Knizhnik]

On 31.07.2018 12:12, Yamaji, Ryo wrote:


3. I confirmed the transition of the amount of the memory when it tried to 
prepare query
of the number that exceeded the value specified for autoprepare_limit.
[autoprepare_limit=1 and execute 10 different queries]
plan cache context: 1032 used
plan cache context: 39832 used
plan cache context: 78552 used
plan cache context: 117272 used
plan cache context: 155952 used
plan cache context: 194632 used
plan cache context: 233312 used
plan cache context: 272032 used
plan cache context: 310712 used
plan cache context: 349392 used
plan cache context: 388072 used

I feel the doubt in an increase of the memory utilization when I execute a lot 
of
query though cached query is one (autoprepare_limit=1).
This behavior is correct?


I failed to reproduce the problem.
I used the following non-default configuration parameters:

autoprepare_limit=1
autoprepare_threshold=1

create dummy database:

create table foo(x integer primary key, y integer);
insert into foo values (generate_series(1,1), 0);

and run different queries,  like:

postgres=# select *  from foo where x=1;
postgres=# select *  from foo where x+x=1;
postgres=# select *  from foo where x+x+x=1;
postgres=# select *  from foo where x+x+x+x=1;
...

and check size of CacheMemoryContext using gdb - it is not increased.

Can you please send me your test?

Re: [HACKERS] Cached plans and statement generalization

[Konstantin Knizhnik]

On 01.08.2018 00:30, Konstantin Knizhnik wrote:

Hi Yamaji,


On 31.07.2018 12:12, Yamaji, Ryo wrote:

-Original Message-
From: Konstantin Knizhnik [mailto:k.knizh...@postgrespro.ru]
Sent: Friday, January 12, 2018 9:53 PM
To: Thomas Munro ; Stephen Frost

Cc: Michael Paquier ; PostgreSQL mailing
lists ; Tsunakawa, Takayuki/綱川 貴之

Subject: Re: [HACKERS] Cached plans and statement generalization

Thank you very much for reporting the problem.
Rebased version of the patch is attached.

Hi Konstantin.

I think that this patch excel very much. Because the customer of our
company has the demand that migrates from other DB to PostgreSQL, and
the problem to have to modify the application program to do prepare in
that case occurs. It is possible to solve it by the problem's using this
patch. I want to be helping this patch to be committed. Will you 
participate

in the following CF?


This patch will be included in next release of PgPro EE.
Concerning next commit fest - I am not sure.
At previous commitfest it was returned with feedback that it "has 
received no review or comments since last May".

May be your review will help to change this situation.




To review this patch, I verified it. The verified environment is
PostgreSQL 11beta2. It is necessary to add "executor/spi.h" and 
"jit/jit.h"

to postgres.c of the patch by the updating of PostgreSQL. Please rebase.


New version of the patch is attached.
diff --git a/doc/src/sgml/autoprepare.sgml b/doc/src/sgml/autoprepare.sgml
new file mode 100644
index 000..b3309bd
--- /dev/null
+++ b/doc/src/sgml/autoprepare.sgml
@@ -0,0 +1,62 @@
+
+
+ 
+  Autoprepared statements
+
+  
+   autoprepared statements
+  
+
+  
+PostgreSQL makes it possible prepare
+frequently used statements to eliminate cost of their compilation
+and optimization on each execution of the query. On simple queries
+(like ones in pgbench -S) using prepared statements
+increase performance more than two times.
+  
+
+  
+Unfortunately not all database applications are using prepared statements
+and, moreover, it is not always possible. For example, in case of using
+pgbouncer or any other session pooler,
+there is no session state (transactions of one client may be executed at different
+backends) and so prepared statements can not be used.
+  
+
+  
+Autoprepare mode allows to overcome this limitation.
+In this mode Postgres tries to generalize executed statements
+and build parameterized plan for them. Speed of execution of
+autoprepared statements is almost the same as of explicitly
+prepared statements.
+  
+
+  
+By default autoprepare mode is switched off. To enable it, assign non-zero
+value to GUC variable autoprepare_tthreshold.
+This variable specified minimal number of times the statement should be
+executed before it is autoprepared. Please notice that, despite to the
+value of this parameter, Postgres makes a decision about using
+generalized plan vs. customized execution plans based on the results
+of comparison of average time of five customized plans with
+time of generalized plan.
+  
+
+  
+If number of different statements issued by application is large enough,
+then autopreparing all of them can cause memory overflow
+(especially if there are many active clients, because prepared statements cache
+is local to the backend). To prevent growth of backend's memory because of
+autoprepared cache, it is possible to limit number of autoprepared statements
+by setting autoprepare_limit GUC variable. LRU strategy will be used
+to keep in memory most frequently used queries.
+  
+
+  
+It is possible to inspect autoprepared queries in the backend using
+pg_autoprepared_statements view. It shows original text of the
+query, types of the extracted parameters (replacing literals) and
+query execution counter.
+  
+
+ 
diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index 3ed9021..fdf8d52 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -8226,6 +8226,11 @@ SCRAM-SHA-256$iteration count:
  
 
  
+  pg_autoprepared_statements
+  autoprepared statements
+ 
+
+ 
   pg_prepared_xacts
   prepared transactions
  
@@ -9537,6 +9542,68 @@ SELECT * FROM pg_locks pl LEFT JOIN pg_prepared_xacts ppx
   
  
 
+
+ 
+  pg_autoprepared_statements
+
+  
+   pg_autoprepared_statements
+  
+
+  
+   The pg_autoprepared_statements view displays
+   all the autoprepared statements that are available in the current
+   session. See  for more information about autoprepared
+   statements.
+  
+
+  
+   pg_autoprepared_statements Columns
+
+   
+
+ 
+  Name
+  Type
+  Description
+ 
+
+
+ 
+  statement
+  text
+  
+The query string submitted by the client from which this prepared statement
+

Re: [HACKERS] Cached plans and statement generalization

[Konstantin Knizhnik]

Hi Yamaji,


On 31.07.2018 12:12, Yamaji, Ryo wrote:

-Original Message-
From: Konstantin Knizhnik [mailto:k.knizh...@postgrespro.ru]
Sent: Friday, January 12, 2018 9:53 PM
To: Thomas Munro ; Stephen Frost

Cc: Michael Paquier ; PostgreSQL mailing
lists ; Tsunakawa, Takayuki/綱川 貴之

Subject: Re: [HACKERS] Cached plans and statement generalization

Thank you very much for reporting the problem.
Rebased version of the patch is attached.

Hi Konstantin.

I think that this patch excel very much. Because the customer of our
company has the demand that migrates from other DB to PostgreSQL, and
the problem to have to modify the application program to do prepare in
that case occurs. It is possible to solve it by the problem's using this
patch. I want to be helping this patch to be committed. Will you participate
in the following CF?


This patch will be included in next release of PgPro EE.
Concerning next commit fest - I am not sure.
At previous commitfest it was returned with feedback that it "has 
received no review or comments since last May".

May be your review will help to change this situation.




To review this patch, I verified it. The verified environment is
PostgreSQL 11beta2. It is necessary to add "executor/spi.h" and "jit/jit.h"
to postgres.c of the patch by the updating of PostgreSQL. Please rebase.

1. I confirmed the influence on the performance by having applied this patch.
The result showed the tendency similar to Konstantin.
-s:100  -c:8-t: 1   read-only
simple: 20251 TPS
prepare:29502 TPS
simple(autoprepare):28001 TPS

2. I confirmed the influence on the memory utilization by the length of query 
that did
autoprepare. Short queries have 1 constant. Long queries have 100 constants.
This result was shown that preparing long query used the memory more.
before prepare：plan cache context: 1032 used
prepare 10 short query statement：plan cache context: 15664 used
prepare 10 long query statement：plan cache context: 558032 used

In this patch, the maximum number of query that can do prepare can be set to 
autoprepare_limit.
However, is it good in this? I think that I can assume the scene in the 
following.
- Application side user: To elicit the performance, they want to specify the 
number of prepared
query.
- Operation side user: To prevent the memory from overflowing, they want to set 
the maximum value
of the memory utilization.
Therefore, I propose to add the parameter to specify the maximum memory 
utilization.


I agree it may be more useful to limit amount of memory used by prepare 
queries, rather than number of prepared statements.
But it is just more difficult to calculate and maintain (I am not sure 
that just looking at CacheMemoryContext is enough for it).
Also, if working set of queries (frequently repeated queries) doesn't 
fir in memory, then autoprepare will be almost useless (because with 
high probability
prepared query will be thrown away from the cache before it can be 
reused). So limiting queries from "application side" seems to be more 
practical.



3. I confirmed the transition of the amount of the memory when it tried to 
prepare query
of the number that exceeded the value specified for autoprepare_limit.
[autoprepare_limit=1 and execute 10 different queries]
plan cache context: 1032 used
plan cache context: 39832 used
plan cache context: 78552 used
plan cache context: 117272 used
plan cache context: 155952 used
plan cache context: 194632 used
plan cache context: 233312 used
plan cache context: 272032 used
plan cache context: 310712 used
plan cache context: 349392 used
plan cache context: 388072 used

I feel the doubt in an increase of the memory utilization when I execute a lot 
of
query though cached query is one (autoprepare_limit=1).
This behavior is correct?


I will check it.

RE: [HACKERS] Cached plans and statement generalization

[Yamaji, Ryo]

> -Original Message-
> From: Konstantin Knizhnik [mailto:k.knizh...@postgrespro.ru]
> Sent: Friday, January 12, 2018 9:53 PM
> To: Thomas Munro ; Stephen Frost
> 
> Cc: Michael Paquier ; PostgreSQL mailing
> lists ; Tsunakawa, Takayuki/綱川 貴之
> 
> Subject: Re: [HACKERS] Cached plans and statement generalization
>
> Thank you very much for reporting the problem.
> Rebased version of the patch is attached.

Hi Konstantin.

I think that this patch excel very much. Because the customer of our
company has the demand that migrates from other DB to PostgreSQL, and
the problem to have to modify the application program to do prepare in
that case occurs. It is possible to solve it by the problem's using this
patch. I want to be helping this patch to be committed. Will you participate
in the following CF? 

To review this patch, I verified it. The verified environment is
PostgreSQL 11beta2. It is necessary to add "executor/spi.h" and "jit/jit.h"
to postgres.c of the patch by the updating of PostgreSQL. Please rebase.

1. I confirmed the influence on the performance by having applied this patch.
The result showed the tendency similar to Konstantin.
-s:100  -c:8-t: 1   read-only
simple: 20251 TPS
prepare:29502 TPS
simple(autoprepare):28001 TPS

2. I confirmed the influence on the memory utilization by the length of query 
that did
autoprepare. Short queries have 1 constant. Long queries have 100 constants.
This result was shown that preparing long query used the memory more.
before prepare：plan cache context: 1032 used
prepare 10 short query statement：plan cache context: 15664 used
prepare 10 long query statement：plan cache context: 558032 used

In this patch, the maximum number of query that can do prepare can be set to 
autoprepare_limit.
However, is it good in this? I think that I can assume the scene in the 
following.
- Application side user: To elicit the performance, they want to specify the 
number of prepared
query.
- Operation side user: To prevent the memory from overflowing, they want to set 
the maximum value
of the memory utilization.
Therefore, I propose to add the parameter to specify the maximum memory 
utilization.

3. I confirmed the transition of the amount of the memory when it tried to 
prepare query
of the number that exceeded the value specified for autoprepare_limit.
[autoprepare_limit=1 and execute 10 different queries]
plan cache context: 1032 used
plan cache context: 39832 used
plan cache context: 78552 used
plan cache context: 117272 used
plan cache context: 155952 used
plan cache context: 194632 used
plan cache context: 233312 used
plan cache context: 272032 used
plan cache context: 310712 used
plan cache context: 349392 used
plan cache context: 388072 used

I feel the doubt in an increase of the memory utilization when I execute a lot 
of
query though cached query is one (autoprepare_limit=1).
This behavior is correct?

Best regards, Yamaji

Re: Re: Re: [HACKERS] Cached plans and statement generalization

[David Steele]

On 3/2/18 9:26 AM, David Steele wrote:
> On 1/12/18 7:53 AM, Konstantin Knizhnik wrote:
>>
>>
>> On 12.01.2018 03:40, Thomas Munro wrote:
>>> On Sun, Jan 7, 2018 at 11:51 AM, Stephen Frost 
>>> wrote:
 * Konstantin Knizhnik (k.knizh...@postgrespro.ru) wrote:
> Updated version of the patch is attached.
 This patch appears to apply with just a bit of fuzz and make check
 passes, so I'm not sure why this is currently marked as 'Waiting for
 author'.

 I've updated it to be 'Needs review'.  If that's incorrect, feel free to
 change it back with an explanation.
>>> Hi Konstantin,
>>>
>>> /home/travis/build/postgresql-cfbot/postgresql/src/backend/tcop/postgres.c:5249:
>>>
>>> undefined reference to `PortalGetHeapMemory'
>>>
>>> That's because commit 0f7c49e85518dd846ccd0a044d49a922b9132983 killed
>>> PortalGetHeapMemory.  Looks like it needs to be replaced with
>>> portal->portalContext.
>>>
>> Hi  Thomas,
>>
>> Thank you very much for reporting the problem.
>> Rebased version of the patch is attached.
> 
> This patch has received no review or comments since last May and appears
> too complex and invasive for the final CF of PG11.
> 
> I don't think it makes sense to keep pushing a patch through CFs when it
> is not getting reviewed.  I'm planning to mark this as Returned with
> Feedback unless there are solid arguments to the contrary.

Marked as Returned with Feedback.

Regards,
-- 
-David
da...@pgmasters.net

Re: Re: [HACKERS] Cached plans and statement generalization

[David Steele]

Hi Konstantin,

On 1/12/18 7:53 AM, Konstantin Knizhnik wrote:
> 
> 
> On 12.01.2018 03:40, Thomas Munro wrote:
>> On Sun, Jan 7, 2018 at 11:51 AM, Stephen Frost 
>> wrote:
>>> * Konstantin Knizhnik (k.knizh...@postgrespro.ru) wrote:
 Updated version of the patch is attached.
>>> This patch appears to apply with just a bit of fuzz and make check
>>> passes, so I'm not sure why this is currently marked as 'Waiting for
>>> author'.
>>>
>>> I've updated it to be 'Needs review'.  If that's incorrect, feel free to
>>> change it back with an explanation.
>> Hi Konstantin,
>>
>> /home/travis/build/postgresql-cfbot/postgresql/src/backend/tcop/postgres.c:5249:
>>
>> undefined reference to `PortalGetHeapMemory'
>>
>> That's because commit 0f7c49e85518dd846ccd0a044d49a922b9132983 killed
>> PortalGetHeapMemory.  Looks like it needs to be replaced with
>> portal->portalContext.
>>
> Hi  Thomas,
> 
> Thank you very much for reporting the problem.
> Rebased version of the patch is attached.

This patch has received no review or comments since last May and appears
too complex and invasive for the final CF of PG11.

I don't think it makes sense to keep pushing a patch through CFs when it
is not getting reviewed.  I'm planning to mark this as Returned with
Feedback unless there are solid arguments to the contrary.

Thanks,
-- 
-David
da...@pgmasters.net

Re: [HACKERS] Cached plans and statement generalization

[Konstantin Knizhnik]

On 12.01.2018 03:40, Thomas Munro wrote:

On Sun, Jan 7, 2018 at 11:51 AM, Stephen Frost  wrote:

* Konstantin Knizhnik (k.knizh...@postgrespro.ru) wrote:

Updated version of the patch is attached.

This patch appears to apply with just a bit of fuzz and make check
passes, so I'm not sure why this is currently marked as 'Waiting for
author'.

I've updated it to be 'Needs review'.  If that's incorrect, feel free to
change it back with an explanation.

Hi Konstantin,

/home/travis/build/postgresql-cfbot/postgresql/src/backend/tcop/postgres.c:5249:
undefined reference to `PortalGetHeapMemory'

That's because commit 0f7c49e85518dd846ccd0a044d49a922b9132983 killed
PortalGetHeapMemory.  Looks like it needs to be replaced with
portal->portalContext.


Hi  Thomas,

Thank you very much for reporting the problem.
Rebased version of the patch is attached.

--
Konstantin Knizhnik
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

diff --git a/src/backend/nodes/nodeFuncs.c b/src/backend/nodes/nodeFuncs.c
index 6c76c41..8922950 100644
--- a/src/backend/nodes/nodeFuncs.c
+++ b/src/backend/nodes/nodeFuncs.c
@@ -3688,6 +3688,454 @@ raw_expression_tree_walker(Node *node,
 }
 
 /*
+ * raw_expression_tree_mutator --- transform raw parse tree.
+ *
+ * This function is implementing slightly different approach for tree update than expression_tree_mutator().
+ * Callback is given pointer to pointer to the current node and can update this field instead of returning reference to new node.
+ * It makes it possible to remember changes and easily revert them without extra traversal of the tree.
+ *
+ * This function do not need QTW_DONT_COPY_QUERY flag: it never implicitly copy tree nodes, doing in-place update.
+ *
+ * Like raw_expression_tree_walker, there is no special rule about query
+ * boundaries: we descend to everything that's possibly interesting.
+ *
+ * Currently, the node type coverage here extends only to DML statements
+ * (SELECT/INSERT/UPDATE/DELETE) and nodes that can appear in them, because
+ * this is used mainly during analysis of CTEs, and only DML statements can
+ * appear in CTEs. If some other node is visited, iteration is immediately stopped and true is returned.
+ */
+bool
+raw_expression_tree_mutator(Node *node,
+			bool (*mutator) (),
+			void *context)
+{
+	ListCell   *temp;
+
+	/*
+	 * The walker has already visited the current node, and so we need only
+	 * recurse into any sub-nodes it has.
+	 */
+	if (node == NULL)
+		return false;
+
+	/* Guard against stack overflow due to overly complex expressions */
+	check_stack_depth();
+
+	switch (nodeTag(node))
+	{
+		case T_SetToDefault:
+		case T_CurrentOfExpr:
+		case T_Integer:
+		case T_Float:
+		case T_String:
+		case T_BitString:
+		case T_Null:
+		case T_ParamRef:
+		case T_A_Const:
+		case T_A_Star:
+			/* primitive node types with no subnodes */
+			break;
+		case T_Alias:
+			/* we assume the colnames list isn't interesting */
+			break;
+		case T_RangeVar:
+			return mutator(&((RangeVar *) node)->alias, context);
+		case T_GroupingFunc:
+			return mutator(&((GroupingFunc *) node)->args, context);
+		case T_SubLink:
+			{
+SubLink	   *sublink = (SubLink *) node;
+
+if (mutator(>testexpr, context))
+	return true;
+/* we assume the operName is not interesting */
+if (mutator(>subselect, context))
+	return true;
+			}
+			break;
+		case T_CaseExpr:
+			{
+CaseExpr   *caseexpr = (CaseExpr *) node;
+
+if (mutator(>arg, context))
+	return true;
+/* we assume mutator(& doesn't care about CaseWhens, either */
+foreach(temp, caseexpr->args)
+{
+	CaseWhen   *when = (CaseWhen *) lfirst(temp);
+
+	Assert(IsA(when, CaseWhen));
+	if (mutator(>expr, context))
+		return true;
+	if (mutator(>result, context))
+		return true;
+}
+if (mutator(>defresult, context))
+	return true;
+			}
+			break;
+		case T_RowExpr:
+			/* Assume colnames isn't interesting */
+			return mutator(&((RowExpr *) node)->args, context);
+		case T_CoalesceExpr:
+			return mutator(&((CoalesceExpr *) node)->args, context);
+		case T_MinMaxExpr:
+			return mutator(&((MinMaxExpr *) node)->args, context);
+		case T_XmlExpr:
+			{
+XmlExpr	   *xexpr = (XmlExpr *) node;
+
+if (mutator(>named_args, context))
+	return true;
+/* we assume mutator(& doesn't care about arg_names */
+if (mutator(>args, context))
+	return true;
+			}
+			break;
+		case T_NullTest:
+			return mutator(&((NullTest *) node)->arg, context);
+		case T_BooleanTest:
+			return mutator(&((BooleanTest *) node)->arg, context);
+		case T_JoinExpr:
+			{
+JoinExpr   *join = (JoinExpr *) node;
+
+if (mutator(>larg, context))
+	return true;
+if (mutator(>rarg, context))
+	return true;
+if (mutator(>quals, context))
+	return true;
+if (mutator(>alias, context))
+	return true;
+/* using list is deemed uninteresting */
+			}
+			break;

Re: [HACKERS] Cached plans and statement generalization

[Stephen Frost]

Greetings,

* Konstantin Knizhnik (k.knizh...@postgrespro.ru) wrote:
> On 30.11.2017 04:59, Michael Paquier wrote:
> >On Wed, Sep 13, 2017 at 2:11 AM, Konstantin Knizhnik
> > wrote:
> >>One more patch passing all regression tests with autoprepare_threshold=1.
> >>I still do not think that it should be switch on by default...
> >This patch does not apply, and did not get any reviews. So I am moving
> >it to next CF with waiting on author as status. Please provide a
> >rebased version. Tsunakawa-san, you are listed as a reviewer of this
> >patch. If you are not planning to look at it anymore, you may want to
> >remove your name from the related CF entry
> >https://commitfest.postgresql.org/16/1150/.
> 
> Updated version of the patch is attached.

This patch appears to apply with just a bit of fuzz and make check
passes, so I'm not sure why this is currently marked as 'Waiting for
author'.

I've updated it to be 'Needs review'.  If that's incorrect, feel free to
change it back with an explanation.

Thanks!

Stephen


signature.asc
Description: PGP signature

Re: [HACKERS] Cached plans and statement generalization

[Konstantin Knizhnik]

On 30.11.2017 04:59, Michael Paquier wrote:

On Wed, Sep 13, 2017 at 2:11 AM, Konstantin Knizhnik
 wrote:

One more patch passing all regression tests with autoprepare_threshold=1.
I still do not think that it should be switch on by default...

This patch does not apply, and did not get any reviews. So I am moving
it to next CF with waiting on author as status. Please provide a
rebased version. Tsunakawa-san, you are listed as a reviewer of this
patch. If you are not planning to look at it anymore, you may want to
remove your name from the related CF entry
https://commitfest.postgresql.org/16/1150/.


Updated version of the patch is attached.

--
Konstantin Knizhnik
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

diff --git a/src/backend/nodes/nodeFuncs.c b/src/backend/nodes/nodeFuncs.c
index c2a93b2..0e6cc89 100644
--- a/src/backend/nodes/nodeFuncs.c
+++ b/src/backend/nodes/nodeFuncs.c
@@ -3688,6 +3688,454 @@ raw_expression_tree_walker(Node *node,
 }
 
 /*
+ * raw_expression_tree_mutator --- transform raw parse tree.
+ *
+ * This function is implementing slightly different approach for tree update than expression_tree_mutator().
+ * Callback is given pointer to pointer to the current node and can update this field instead of returning reference to new node.
+ * It makes it possible to remember changes and easily revert them without extra traversal of the tree.
+ *
+ * This function do not need QTW_DONT_COPY_QUERY flag: it never implicitly copy tree nodes, doing in-place update.
+ *
+ * Like raw_expression_tree_walker, there is no special rule about query
+ * boundaries: we descend to everything that's possibly interesting.
+ *
+ * Currently, the node type coverage here extends only to DML statements
+ * (SELECT/INSERT/UPDATE/DELETE) and nodes that can appear in them, because
+ * this is used mainly during analysis of CTEs, and only DML statements can
+ * appear in CTEs. If some other node is visited, iteration is immediately stopped and true is returned.
+ */
+bool
+raw_expression_tree_mutator(Node *node,
+			bool (*mutator) (),
+			void *context)
+{
+	ListCell   *temp;
+
+	/*
+	 * The walker has already visited the current node, and so we need only
+	 * recurse into any sub-nodes it has.
+	 */
+	if (node == NULL)
+		return false;
+
+	/* Guard against stack overflow due to overly complex expressions */
+	check_stack_depth();
+
+	switch (nodeTag(node))
+	{
+		case T_SetToDefault:
+		case T_CurrentOfExpr:
+		case T_Integer:
+		case T_Float:
+		case T_String:
+		case T_BitString:
+		case T_Null:
+		case T_ParamRef:
+		case T_A_Const:
+		case T_A_Star:
+			/* primitive node types with no subnodes */
+			break;
+		case T_Alias:
+			/* we assume the colnames list isn't interesting */
+			break;
+		case T_RangeVar:
+			return mutator(&((RangeVar *) node)->alias, context);
+		case T_GroupingFunc:
+			return mutator(&((GroupingFunc *) node)->args, context);
+		case T_SubLink:
+			{
+SubLink	   *sublink = (SubLink *) node;
+
+if (mutator(>testexpr, context))
+	return true;
+/* we assume the operName is not interesting */
+if (mutator(>subselect, context))
+	return true;
+			}
+			break;
+		case T_CaseExpr:
+			{
+CaseExpr   *caseexpr = (CaseExpr *) node;
+
+if (mutator(>arg, context))
+	return true;
+/* we assume mutator(& doesn't care about CaseWhens, either */
+foreach(temp, caseexpr->args)
+{
+	CaseWhen   *when = (CaseWhen *) lfirst(temp);
+
+	Assert(IsA(when, CaseWhen));
+	if (mutator(>expr, context))
+		return true;
+	if (mutator(>result, context))
+		return true;
+}
+if (mutator(>defresult, context))
+	return true;
+			}
+			break;
+		case T_RowExpr:
+			/* Assume colnames isn't interesting */
+			return mutator(&((RowExpr *) node)->args, context);
+		case T_CoalesceExpr:
+			return mutator(&((CoalesceExpr *) node)->args, context);
+		case T_MinMaxExpr:
+			return mutator(&((MinMaxExpr *) node)->args, context);
+		case T_XmlExpr:
+			{
+XmlExpr	   *xexpr = (XmlExpr *) node;
+
+if (mutator(>named_args, context))
+	return true;
+/* we assume mutator(& doesn't care about arg_names */
+if (mutator(>args, context))
+	return true;
+			}
+			break;
+		case T_NullTest:
+			return mutator(&((NullTest *) node)->arg, context);
+		case T_BooleanTest:
+			return mutator(&((BooleanTest *) node)->arg, context);
+		case T_JoinExpr:
+			{
+JoinExpr   *join = (JoinExpr *) node;
+
+if (mutator(>larg, context))
+	return true;
+if (mutator(>rarg, context))
+	return true;
+if (mutator(>quals, context))
+	return true;
+if (mutator(>alias, context))
+	return true;
+/* using list is deemed uninteresting */
+			}
+			break;
+		case T_IntoClause:
+			{
+IntoClause *into = (IntoClause *) node;
+
+if (mutator(>rel, context))
+	return true;
+/* colNames, options are deemed uninteresting */
+/* viewQuery 

RE: [HACKERS] Cached plans and statement generalization

[Tsunakawa, Takayuki]

From: Michael Paquier [mailto:michael.paqu...@gmail.com]
> This patch does not apply, and did not get any reviews. So I am moving it
> to next CF with waiting on author as status. Please provide a rebased version.
> Tsunakawa-san, you are listed as a reviewer of this patch. If you are not
> planning to look at it anymore, you may want to remove your name from the
> related CF entry https://commitfest.postgresql.org/16/1150/.

Sorry, but I'm planning to review this next month because this proposal could 
alleviate some problem we faced.

Regards
Takayuki Tsunakawa

Re: [HACKERS] Cached plans and statement generalization

[Michael Paquier]

On Wed, Sep 13, 2017 at 2:11 AM, Konstantin Knizhnik
 wrote:
> One more patch passing all regression tests with autoprepare_threshold=1.
> I still do not think that it should be switch on by default...

This patch does not apply, and did not get any reviews. So I am moving
it to next CF with waiting on author as status. Please provide a
rebased version. Tsunakawa-san, you are listed as a reviewer of this
patch. If you are not planning to look at it anymore, you may want to
remove your name from the related CF entry
https://commitfest.postgresql.org/16/1150/.
-- 
Michael