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

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