Thread: Speeding up creating UPDATE/DELETE generic plan for partitionedtable into a lot
Speeding up creating UPDATE/DELETE generic plan for partitionedtable into a lot
From
"Kato, Sho"
Date:
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
Re: Speeding up creating UPDATE/DELETE generic plan for partitionedtable into a lot
From
Amit Langote
Date:
Kato-san, On 2018/12/21 15:36, Kato, Sho wrote: > 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. > > In most cases, since the partitions to access are partial, I think planner does not need to create a Scan path for everypartition. What do you mean by "since the partitions to access are partial"? > Is there any better way? For example, can planner create generic plans from the parameters specified for EXECUTE? Well, a generic plan is, by definition, *not* specific to the values of parameters, so it's not clear what you're suggesting here. Thanks, Amit
RE: Speeding up creating UPDATE/DELETE generic plan for partitionedtable into a lot
From
"Kato, Sho"
Date:
Hi, Amit
Thank you for your reply.
> What do you mean by "since the partitions to access are partial"?
I mean planner create scan nodes based on the parameters specified for EXECUTE and backend keep them in CachedPlan.
If CachedPlan does not have a scan node for accessing partition, planning is needed.
But if there are a lot of partitions and EXEUCTE is executed several times, planning will not be needed because EXECUTE
probablyaccess to some partitions in most case.
I'm sorry that I do not understand the mechanism so much, so I do not know if I can do it.
This is idea.
Before:
postgres=# explain execute update_stmt(8);
QUERY PLAN
-------------------------------------------------------------
Update on t (cost=0.00..382.78 rows=110 width=14)
Update on t_1
Update on t_2
Update on t_3
Update on t_4
Update on t_5
Update on t_6
Update on t_7
Update on t_8
Update on t_9
Update on t_10
-> Seq Scan on t_1 (cost=0.00..38.28 rows=11 width=14)
Filter: (aid = $1)
-> Seq Scan on t_2 (cost=0.00..38.28 rows=11 width=14)
Filter: (aid = $1)
-> Seq Scan on t_3 (cost=0.00..38.28 rows=11 width=14)
Filter: (aid = $1)
-> Seq Scan on t_4 (cost=0.00..38.28 rows=11 width=14)
Filter: (aid = $1)
-> Seq Scan on t_5 (cost=0.00..38.28 rows=11 width=14)
Filter: (aid = $1)
-> Seq Scan on t_6 (cost=0.00..38.28 rows=11 width=14)
Filter: (aid = $1)
-> Seq Scan on t_7 (cost=0.00..38.28 rows=11 width=14)
Filter: (aid = $1)
-> Seq Scan on t_8 (cost=0.00..38.28 rows=11 width=14)
Filter: (aid = $1)
-> Seq Scan on t_9 (cost=0.00..38.28 rows=11 width=14)
Filter: (aid = $1)
-> Seq Scan on t_10 (cost=0.00..38.28 rows=11 width=14)
Filter: (aid = $1)
After:
postgres=# explain execute update_stmt(8);
QUERY PLAN
-------------------------------------------------------------
Update on t (cost=0.00..382.78 rows=110 width=14)
Update on t_8
-> Seq Scan on t_8 (cost=0.00..38.28 rows=11 width=14)
Filter: (aid = $1)
regards,
> -----Original Message-----
> From: Amit Langote [mailto:Langote_Amit_f8@lab.ntt.co.jp]
> Sent: Friday, December 21, 2018 5:45 PM
> To: Kato, Sho/加藤 翔 <kato-sho@jp.fujitsu.com>;
> pgsql-hackers@lists.postgresql.org
> Subject: Re: Speeding up creating UPDATE/DELETE generic plan for
> partitioned table into a lot
>
> Kato-san,
>
> On 2018/12/21 15:36, Kato, Sho wrote:
> > 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.
> >
> > In most cases, since the partitions to access are partial, I think
> planner does not need to create a Scan path for every partition.
>
> What do you mean by "since the partitions to access are partial"?
>
> > Is there any better way? For example, can planner create generic plans
> from the parameters specified for EXECUTE?
>
> Well, a generic plan is, by definition, *not* specific to the values of
> parameters, so it's not clear what you're suggesting here.
>
> Thanks,
> Amit
>
>
Re: Speeding up creating UPDATE/DELETE generic plan for partitionedtable into a lot
From
Amit Langote
Date:
Kato-san, On 2018/12/27 16:53, Kato, Sho wrote: >> What do you mean by "since the partitions to access are partial"? > > I mean planner create scan nodes based on the parameters specified for EXECUTE and backend keep them in CachedPlan. > > Before: > > postgres=# explain execute update_stmt(8); > QUERY PLAN > ------------------------------------------------------------- > Update on t (cost=0.00..382.78 rows=110 width=14) > Update on t_1 > Update on t_2 > Update on t_3 > Update on t_4 > Update on t_5 > Update on t_6 > Update on t_7 > Update on t_8 > Update on t_9 > Update on t_10 > -> Seq Scan on t_1 (cost=0.00..38.28 rows=11 width=14) > Filter: (aid = $1) > -> Seq Scan on t_2 (cost=0.00..38.28 rows=11 width=14) > Filter: (aid = $1) > -> Seq Scan on t_3 (cost=0.00..38.28 rows=11 width=14) > Filter: (aid = $1) > -> Seq Scan on t_4 (cost=0.00..38.28 rows=11 width=14) > Filter: (aid = $1) > -> Seq Scan on t_5 (cost=0.00..38.28 rows=11 width=14) > Filter: (aid = $1) > -> Seq Scan on t_6 (cost=0.00..38.28 rows=11 width=14) > Filter: (aid = $1) > -> Seq Scan on t_7 (cost=0.00..38.28 rows=11 width=14) > Filter: (aid = $1) > -> Seq Scan on t_8 (cost=0.00..38.28 rows=11 width=14) > Filter: (aid = $1) > -> Seq Scan on t_9 (cost=0.00..38.28 rows=11 width=14) > Filter: (aid = $1) > -> Seq Scan on t_10 (cost=0.00..38.28 rows=11 width=14) > Filter: (aid = $1) > > After: > > postgres=# explain execute update_stmt(8); > QUERY PLAN > ------------------------------------------------------------- > Update on t (cost=0.00..382.78 rows=110 width=14) > Update on t_8 > -> Seq Scan on t_8 (cost=0.00..38.28 rows=11 width=14) > Filter: (aid = $1) As I said in my previous reply, this is no longer a generic plan, because it is based on a specific value of the parameter. The problem with generic planning for queries involving partitioned tables is that the time needed increases as the number of partitions increases, because pruning *cannot* be used when creating a generic plan. That wouldn't have been a problem if the planner didn't need to look at the partitions when constructing plans for a partitioned table. Maybe, we can invent new types of plans for queries on partitioned tables that can be constructed by only looking at the parent relation. We'd need new infrastructure before we can begin working on that though. For example, until we had partitioned tables and the new partition pruning module specialized for partitioned tables, we had to look at every child to use constraint exclusion to emulate partition pruning. Starting in PG 11, we now only look at the parent to perform pruning. To perform the *whole planning* by just looking at the parent relation would require us to build more infrastructure such that, for example, an appropriate scan method for underlying partitions can be selected without having to open the children. Thanks, Amit
RE: Speeding up creating UPDATE/DELETE generic plan for partitionedtable into a lot
From
"Tsunakawa, Takayuki"
Date:
From: Amit Langote [mailto:Langote_Amit_f8@lab.ntt.co.jp] > Maybe, we can invent new types of plans for queries on partitioned tables > that can be constructed by only looking at the parent relation. We'd need > new infrastructure before we can begin working on that though. For > example, until we had partitioned tables and the new partition pruning > module specialized for partitioned tables, we had to look at every child > to use constraint exclusion to emulate partition pruning. Starting in PG > 11, we now only look at the parent to perform pruning. To perform the > *whole planning* by just looking at the parent relation would require us > to build more infrastructure such that, for example, an appropriate scan > method for underlying partitions can be selected without having to open > the children. Although I may say the same thing as you, I think a natural idea would be to create a generic plan gradually. The startingsimple question is "why do we have to touch all partitions at first?" That is, can we behave like this: * PREPARE just creates an aggregation plan node (e.g. Append for SELECT, Update for UPDATE). It doesn't create any planfor particular partitions. Say, call this a parent generic plan node. * EXECUTE creates a generic plan for specific partitions if they don't exist yet, and attach them to the parent generic plannode. Regards Takayuki Tsunakawa
Re: Speeding up creating UPDATE/DELETE generic plan for partitionedtable into a lot
From
David Rowley
Date:
On Fri, 28 Dec 2018 at 20:36, Tsunakawa, Takayuki <tsunakawa.takay@jp.fujitsu.com> wrote: > Although I may say the same thing as you, I think a natural idea would be to create a generic plan gradually. The startingsimple question is "why do we have to touch all partitions at first?" That is, can we behave like this: > > * PREPARE just creates an aggregation plan node (e.g. Append for SELECT, Update for UPDATE). It doesn't create any planfor particular partitions. Say, call this a parent generic plan node. > * EXECUTE creates a generic plan for specific partitions if they don't exist yet, and attach them to the parent genericplan node. I imagine the place to start looking would be around why planning is so slow for that many partitions. There are many inefficient data structures used in the planner that perform linear searches over things like equivalence classes. Perhaps some profiling would highlight just where the problems lie. Tom recently re-posted a query [1] which involved a large number of joins which was taking about 14 seconds to plan on my laptop. After writing some test code to allow faster lookups of equivalence classes matching a set of relations I got this down to about 2.4 seconds [2]. Perhaps this also helps the partitioned table case a little too. Another possible interesting idea would be to, instead of creating large Append/MergeAppend plans for partition scanning, invent some "Partition Seq Scan" and "Partition Index Scan" nodes that are able to build plans more similar to scanning a normal table. Likely such nodes would need to be programmed with a list of Oids that they're to scan during their execution. They'd also need to take care of their own tuple mapping for when partitions had their columns in varying orders. At first thought, such a design does not seem so unrealistic, if so, it would likely solve the generic plan problem you describe completely. [1] https://www.postgresql.org/message-id/6970.1545327857%40sss.pgh.pa.us [2] https://www.postgresql.org/message-id/CAKJS1f_BQvjetGKsjT65gLAVWXQyRYRJpuXE2eBKrE0o0EcWwA@mail.gmail.com -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services