Speeding up creating UPDATE/DELETE generic plan for partitioned table 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: 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

Responses

Browse pgsql-hackers by date

  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