Re: [HACKERS] Cached plans and statement generalization

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
> auto 130 121 121.5
> force_custom_plan 132.5 90.7 122.7
> force_generic_plan 126.7 14.7 24.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
auto 23k 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
auto 232 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