RE: [HACKERS] Cached plans and statement generalization - Mailing list pgsql-hackers

> -----Original Message-----
> From: Konstantin Knizhnik [mailto:k.knizhnik@postgrespro.ru]
> Sent: Friday, January 12, 2018 9:53 PM
> To: Thomas Munro <thomas.munro@enterprisedb.com>; Stephen Frost
> <sfrost@snowman.net>
> Cc: Michael Paquier <michael.paquier@gmail.com>; PostgreSQL mailing
> lists <pgsql-hackers@postgresql.org>; Tsunakawa, Takayuki/綱川 貴之
> <tsunakawa.takay@jp.fujitsu.com>
> 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: 10000    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