Обсуждение: Speeding up creating UPDATE/DELETE generic plan for partitionedtable into a lot

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

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

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
> 
> 

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

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

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