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: | Speeding up creating UPDATE/DELETE generic plan for partitioned table into a lot |
Date: | 2018-12-21 06:36:27 |
Message-ID: | 25C1C6B2E7BE044889E4FE8643A58BA963D9E2A8@G01JPEXMBKW03 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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 every partition.
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
From | Date | Subject | |
---|---|---|---|
Next Message | Nagaura, Ryohei | 2018-12-21 06:36:45 | RE: Timeout parameters |
Previous Message | David Steele | 2018-12-21 06:18:33 | Re: Remove Deprecated Exclusive Backup Mode |