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 speeding up 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-cf1ee6862...@lab.ntt.co.jp
Regards,
Sho Kato
