Speeding up creating UPDATE/DELETE generic plan for partitionedtable into a lot - Mailing list pgsql-hackers

From Kato, Sho
Subject Speeding up creating UPDATE/DELETE generic plan for partitionedtable into a lot
Date
Msg-id 25C1C6B2E7BE044889E4FE8643A58BA963D9E2A8@G01JPEXMBKW03
Whole thread Raw
Responses Re: Speeding up creating UPDATE/DELETE generic plan for partitionedtable into a lot
List 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
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



pgsql-hackers by date:

Previous
From: David Steele
Date:
Subject: Re: Remove Deprecated Exclusive Backup Mode
Next
From: "Nagaura, Ryohei"
Date:
Subject: RE: Timeout parameters