Thread: BUG #17650: For the sixth time, the clipping function in the 120 partition table planning stage fails

The following bug has been logged on the website:

Bug reference:      17650
Logged by:          dafoer
Email address:      dafoer_x@163.com
PostgreSQL version: 14.0
Operating system:   centos7.6
Description:

The clipping function of partition table cannot be carried out normally in
the planning stage. The extension protocol cannot be clipped in the sixth
execution. When concurrency is high, lock contention is serious

postgres=# prepare fun3(int, int) as  select * from t_range where f1=$1 and
f2 = $2 + 2;
PREPARE
Time: 0.439 ms
postgres=# execute fun3(3486,201703);
  f1  |   f2   |  f3  |                f4                
------+--------+------+----------------------------------
 3486 | 201705 | 3486 | ab4f2b5fd96ca65349119909c1eada2d
(1 row)

Time: 1.431 ms
postgres=# 
postgres=# execute fun3(3486,201703);
  f1  |   f2   |  f3  |                f4                
------+--------+------+----------------------------------
 3486 | 201705 | 3486 | ab4f2b5fd96ca65349119909c1eada2d
(1 row)

Time: 0.451 ms
postgres=# execute fun3(3486,201703);
  f1  |   f2   |  f3  |                f4                
------+--------+------+----------------------------------
 3486 | 201705 | 3486 | ab4f2b5fd96ca65349119909c1eada2d
(1 row)

Time: 0.430 ms
postgres=# execute fun3(3486,201703);
  f1  |   f2   |  f3  |                f4                
------+--------+------+----------------------------------
 3486 | 201705 | 3486 | ab4f2b5fd96ca65349119909c1eada2d
(1 row)

Time: 0.421 ms
postgres=# execute fun3(3486,201703);
  f1  |   f2   |  f3  |                f4                
------+--------+------+----------------------------------
 3486 | 201705 | 3486 | ab4f2b5fd96ca65349119909c1eada2d
(1 row)

Time: 0.416 ms
postgres=# execute fun3(3486,201703);
  f1  |   f2   |  f3  |                f4                
------+--------+------+----------------------------------
 3486 | 201705 | 3486 | ab4f2b5fd96ca65349119909c1eada2d
(1 row)

Time: 7.568 ms （-- It took too much time <<<<<<<<=======================）
postgres=# execute fun3(3486,201703);
  f1  |   f2   |  f3  |                f4                
------+--------+------+----------------------------------
 3486 | 201705 | 3486 | ab4f2b5fd96ca65349119909c1eada2d
(1 row)

Time: 0.450 ms
postgres=# execute fun3(3486,201703);
  f1  |   f2   |  f3  |                f4                
------+--------+------+----------------------------------
 3486 | 201705 | 3486 | ab4f2b5fd96ca65349119909c1eada2d
(1 row)

use cases:
prepare fun3(int, int) as  select * from t_range where f1=$1 and f2 = $2 +
2;
execute fun3(3486,201703);

-- create table
create or replace function init_t_range_data(start_date int) returns void
as
$$
DECLARE
i int;
sql text;
pd int;
BEGIN
pd := start_date;
    for i in 1..11 loop
        sql:=format('create table if not exists t_range_%s PARTITION OF t_range
for values from (%s) to (%s);', pd, pd, pd+1);
        execute sql;
        raise notice '%',sql;

        sql:=format('insert into t_range(f1,f2,f3,f4) select t, %s, mod(t,10000),
md5(t::varchar) from generate_series(1,100000) t;', pd);
        execute sql;
        raise notice '%',sql;
        
        sql:=format('create index if not exists t_range_%s_f3_idx on
t_range_%s(f3);',pd,pd);
        execute sql;
        raise notice '%',sql;
        
        sql:=format('create index if not exists t_range_%s_f1_idx on
t_range_%s(f1);',pd,pd);
        execute sql;
        raise notice '%',sql; 
        
        pd:=pd+1;
    end loop;

    pd := start_date;
    sql:=format('create table if not exists t_range_%s PARTITION OF t_range for
values from (%s) to (%s);', pd+11, pd+11, pd+100);
    execute sql;
    raise notice '%',sql;
    sql:=format('insert into t_range(f1,f2,f3,f4) select t, %s, mod(t,10000),
md5(t::varchar) from generate_series(1,100000) t;', pd+11);
    execute sql;
    raise notice '%',sql;
    sql:=format('create index if not exists t_range_%s_f3_idx on
t_range_%s(f3);',pd+11,pd+11);
    execute sql;
    raise notice '%',sql;
    sql:=format('create index if not exists t_range_%s_f1_idx on
t_range_%s(f1);',pd+11,pd+11);
    execute sql;
    raise notice '%',sql; 
end;
$$
language plpgsql;

drop table if exists t_range cascade; 
create table t_range (f1 bigint,f2 int , f3 integer, f4 varchar(100) )
partition by range ( f2 );
select init_t_range_data(201701);

create or replace function init_t_range_data(start_date int) returns void
as
$$
DECLARE
i int;
sql text;
pd int;
BEGIN
pd := start_date;
    for i in 1..11 loop
        sql:=format('create table if not exists t_range_%s PARTITION OF t_range
for values from (%s) to (%s);', pd, pd, pd+1);
        execute sql;
        raise notice '%',sql;

        --sql:=format('insert into t_range(f1,f2,f3,f4) select t, %s,
mod(t,10000), md5(t::varchar) from generate_series(1,100000) t;', pd);
        --execute sql;
        --raise notice '%',sql;
        
        sql:=format('create index if not exists t_range_%s_f3_idx on
t_range_%s(f3);',pd,pd);
        execute sql;
        raise notice '%',sql;
        
        sql:=format('create index if not exists t_range_%s_f1_idx on
t_range_%s(f1);',pd,pd);
        execute sql;
        raise notice '%',sql; 
        
        pd:=pd+1;
    end loop;

    pd := start_date;
    sql:=format('create table if not exists t_range_%s PARTITION OF t_range for
values from (%s) to (%s);', pd+11, pd+11, pd+100);
    execute sql;
    raise notice '%',sql;
    --sql:=format('insert into t_range(f1,f2,f3,f4) select t, %s, mod(t,10000),
md5(t::varchar) from generate_series(1,100000) t;', pd+11);
    --execute sql;
    --raise notice '%',sql;
    sql:=format('create index if not exists t_range_%s_f3_idx on
t_range_%s(f3);',pd+11,pd+11);
    execute sql;
    raise notice '%',sql;
    sql:=format('create index if not exists t_range_%s_f1_idx on
t_range_%s(f1);',pd+11,pd+11);
    execute sql;
    raise notice '%',sql; 
end;
$$
language plpgsql;

select init_t_range_data(201601);
select init_t_range_data(201801);
select init_t_range_data(201901);
select init_t_range_data(202001);
select init_t_range_data(202101);
select init_t_range_data(202201);
select init_t_range_data(202301);
select init_t_range_data(202401);
select init_t_range_data(202501);
vacuum (analyze, verbose) t_range;

Hi,

On Tue, Oct 18, 2022 at 01:11:13PM +0000, PG Bug reporting form wrote:
> The following bug has been logged on the website:
>
> Bug reference:      17650
> Logged by:          dafoer
> Email address:      dafoer_x@163.com
> PostgreSQL version: 14.0

Unrelated but you should definitely update to the latest minor version,
currently 14.5

> Operating system:   centos7.6
> Description:
>
> The clipping function of partition table cannot be carried out normally in
> the planning stage. The extension protocol cannot be clipped in the sixth
> execution. When concurrency is high, lock contention is serious

I'm not sure I fully understand.  Are you saying that partition pruning (1)
doesn't happen after 6 execution of a prepared statement?  If yes, it's not a
bug but a known behavior of generic plans.  You can dynamically set
plan_cache_mode to force_custom_plan for the cases where generic plans are
known to behave poorly.

[1] https://www.postgresql.org/docs/current/ddl-partitioning.html#DDL-PARTITION-PRUNING

PG Bug reporting form <noreply@postgresql.org> writes:
> The clipping function of partition table cannot be carried out normally in
> the planning stage.

This looks like normal behavior to me: the sixth iteration is where
the plan cache will try to see if a generic plan would be smarter
to use than a custom one.  Unsurprisingly, for a table partitioned
as heavily as this one, it takes a little while even to construct
a generic plan.  Then it'll decide "nope, we'll stick to custom
planning" and the subsequent executions take the same amount of
time as before.

You could override that with "set plan_cache_mode = force_custom_plan".
But TBH my advice is to use fewer partitions.  There are always going
to be edge cases where the system has to consider all the partitions.
If you're allergic to spending even half a dozen msec in the planner,
you need to set up your tables in a way that doesn't require so much
time when that happens.

            regards, tom lane