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

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