Planning time of Generic plan for a table partitioned into a lot

From: "Kato, Sho" <kato-sho(at)jp(dot)fujitsu(dot)com>
To: "pgsql-hackers(at)lists(dot)postgresql(dot)org" <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: Planning time of Generic plan for a table partitioned into a lot
Date: 2018-11-27 10:05:29
Message-ID: 25C1C6B2E7BE044889E4FE8643A58BA963D89214@G01JPEXMBKW03
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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 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-cf1ee6862a1d@lab.ntt.co.jp

Regards,
Sho Kato

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message amul sul 2018-11-27 10:24:43 Re: vacuum and autovacuum - is it good to configure the threshold at TABLE LEVEL?
Previous Message Sergei Kornilov 2018-11-27 09:10:27 Re: pgsql: Integrate recovery.conf into postgresql.conf