Planning time of Generic plan for a table partitioned into a lot - Mailing list pgsql-hackers

Hi,
I found that making a generic plan of SELECT/UPDATE/DELETE for a table partitioned into thousands is slow.
Especially, UPDATE/DELETE statement is too slow.

I'm afraid that I could not come up with a good idea, but how can I shorten the creation time of a generic plan?

The results are as follows.

*setup*

postgres=# create table t(id int) partition by range(id);
CREATE TABLE
postgres=# \o /dev/null
postgres=# select 'create table t_' || x || ' partition of t for values from (' || x || ') to (' || x+1 || ')'from
generate_series(1,8192) x;
 
postgres=# \gexec
postgres-# analyze;
ANALYZE

*explain analyze results*

postgres=# set plan_cache_mode = force_generic_plan;
SET
postgres=# prepare select_stmt(int) as select * from t where id = $1;
PREPARE
postgres=# explain analyze execute select_stmt(8192);
                                               QUERY PLAN                                               
--------------------------------------------------------------------------------------------------------
 Append  (cost=0.00..343572.48 rows=106496 width=4) (actual time=0.015..0.015 rows=0 loops=1)
   Subplans Removed: 8191
   ->  Seq Scan on t_8192  (cost=0.00..41.88 rows=13 width=4) (actual time=0.013..0.013 rows=0 loops=1)
         Filter: (id = $1)
 Planning Time: 206.415 ms
 Execution Time: 0.742 ms
(6 rows)

postgres=# prepare update_stmt(int) as update t set id = id + 1 where id = $1;
PREPARE
postgres=# explain analyze execute update_stmt(8192);
                                               QUERY PLAN
---------------------------------------------------------------------------------------------------------
 Update on t  (cost=0.00..343306.24 rows=106496 width=10) (actual time=39.502..39.503 rows=0 loops=1)
   Update on t_1
   Update on t_2
   ...
   ->  Seq Scan on t_1  (cost=0.00..41.91 rows=13 width=10) (actual time=0.025..0.026 rows=0 loops=1)
         Filter: (id = $1)
   ->  Seq Scan on t_2  (cost=0.00..41.91 rows=13 width=10) (actual time=0.004..0.005 rows=0 loops=1)
         Filter: (id = $1)
   ...
 Planning Time: 14357.504 ms
 Execution Time: 397.652 ms
(24579 rows)

postgres=# prepare delete_stmt(int) as delete from t where id = $1;
PREPARE
postgres=# explain analyze execute delete_stmt(8192);
                                               QUERY PLAN
--------------------------------------------------------------------------------------------------------
 Delete on t  (cost=0.00..343040.00 rows=106496 width=6) (actual time=51.628..51.628 rows=0 loops=1)
   Delete on t_1
   Delete on t_2
   ...
   ->  Seq Scan on t_1  (cost=0.00..41.88 rows=13 width=6) (actual time=0.025..0.026 rows=0 loops=1)
         Filter: (id = $1)
   ->  Seq Scan on t_2  (cost=0.00..41.88 rows=13 width=6) (actual time=0.014..0.015 rows=0 loops=1)
         Filter: (id = $1)
   ...
 Planning Time: 14225.908 ms
 Execution Time: 405.605 ms
(24579 rows)

Of course, in case of plan_cache_mode = force_custom_plan, it is not problem because unnecessary paths are pruned by
speedingup planning with partitions patch[1].
 

However, if plan_cachemode is force_generic_plan, generic plan is made at the first execution of prepared statement.
If plan_cache_mode is auto(default), generic plan is made at the sixth execution.
So, with default setting, performance get lower at the sixth execution.
Even if you do not improve creation of generic plan, if the number of partition is large, it is better to recommend
force_custom_plan.
Thoughts?

[1]: https://www.postgresql.org/message-id/9d7c5112-cb99-6a47-d3be-cf1ee6862a1d@lab.ntt.co.jp

Regards,
Sho Kato