RE: Speeding up creating UPDATE/DELETE generic plan for partitioned table into a lot

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 probably access 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(at)lab(dot)ntt(dot)co(dot)jp]
> Sent: Friday, December 21, 2018 5:45 PM
> To: Kato, Sho/加藤 翔 <kato-sho(at)jp(dot)fujitsu(dot)com>;
> pgsql-hackers(at)lists(dot)postgresql(dot)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
>
>