Hi,
I want to speed up the creation of UPDATE/DELETE generic plan for tables partitioned into a lot.
Currently, creating a generic plan of UPDATE/DELTE for such table, planner creates a plan to scan all partitions.
So it takes a very long time.
I tried with a table partitioned into 8192, it took 12 seconds.
*setup*
postgres=# create table t(aid int, abalance int) partition by range(aid);
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
*explan analyze*
I use master(commit 71a05b2232 Wed Dec 5) + v8 patch[1] + v1 patch[2]
postgres=# explain analyze execute update_stmt(999);
QUERY PLAN
---------------------------------------------------------------------------------------------------------
Update on t (cost=0.00..313569.28 rows=90112 width=14) (actual time=42.805..42.805 rows=0 loops=1)
Update on t_1
Update on t_2
Update on t_3
...
-> Seq Scan on t_1 (cost=0.00..38.28 rows=11 width=14) (actual time=0.021..0.022 rows=0 loops=1)
Filter: (aid = $1)
-> Seq Scan on t_2 (cost=0.00..38.28 rows=11 width=14) (actual time=0.004..0.005 rows=0 loops=1)
Filter: (aid = $1)
-> Seq Scan on t_3 (cost=0.00..38.28 rows=11 width=14) (actual time=0.004..0.004 rows=0 loops=1)
Filter: (aid = $1)
-> Seq Scan on t_4 (cost=0.00..38.28 rows=11 width=14) (actual time=0.004..0.005 rows=0 loops=1)
...
Planning Time: 12367.833 ms
Execution Time: 490.082 ms
(24579 rows)
In most cases, since the partitions to access are partial, I think planner does not need to create a Scan path for
everypartition.
Is there any better way? For example, can planner create generic plans from the parameters specified for EXECUTE?
[1]:https://www.postgresql.org/message-id/9d7c5112-cb99-6a47-d3be-cf1ee6862a1d@lab.ntt.co.jp
[2]:https://www.postgresql.org/message-id/CAKJS1f-=FnMqmQP6qitkD+xEddxw22ySLP-0xFk3JAqUX2yfMw@mail.gmail.com
regards,
Sho Kato