Re: making update/delete of inheritance trees scale better

On Wed, Mar 31, 2021 at 11:56 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> I noticed something else interesting. If you try an actually-useful
> UPDATE, ie one that has to do some computation in the target list,
> you can get a plan like this if it's a partitioned table:
>
> EXPLAIN (verbose, costs off) UPDATE parent SET f2 = f2 + 1;
> QUERY PLAN
> ---------------------------------------------------------------------------
> Update on public.parent
> Update on public.child1 parent_1
> Update on public.child2 parent_2
> Update on public.child3 parent_3
> -> Append
> -> Seq Scan on public.child1 parent_1
> Output: (parent_1.f2 + 1), parent_1.tableoid, parent_1.ctid
> -> Seq Scan on public.child2 parent_2
> Output: (parent_2.f2 + 1), parent_2.tableoid, parent_2.ctid
> -> Seq Scan on public.child3 parent_3
> Output: (parent_3.f2 + 1), parent_3.tableoid, parent_3.ctid
>
> But when using traditional inheritance, it looks more like:
>
> EXPLAIN (verbose, costs off) UPDATE parent SET f2 = f2 + 1;
> QUERY PLAN
> ---------------------------------------------------------------------------
> Update on public.parent
> Update on public.parent parent_1
> Update on public.child1 parent_2
> Update on public.child2 parent_3
> Update on public.child3 parent_4
> -> Result
> Output: (parent.f2 + 1), parent.tableoid, parent.ctid
> -> Append
> -> Seq Scan on public.parent parent_1
> Output: parent_1.f2, parent_1.tableoid, parent_1.ctid
> -> Seq Scan on public.child1 parent_2
> Output: parent_2.f2, parent_2.tableoid, parent_2.ctid
> -> Seq Scan on public.child2 parent_3
> Output: parent_3.f2, parent_3.tableoid, parent_3.ctid
> -> Seq Scan on public.child3 parent_4
> Output: parent_4.f2, parent_4.tableoid, parent_4.ctid
>
> That is, instead of shoving the "f2 + 1" computation down to the table
> scans, it gets done in a separate Result node, implying yet another
> extra node in the plan with resultant slowdown. The reason for this
> seems to be that apply_scanjoin_target_to_paths has special logic
> to push the target down to members of a partitioned table, but it
> doesn't do that for other sorts of appendrels. That isn't new
> with this patch, you can see the same behavior in SELECT.

I've noticed this too when investigating why
find_modifytable_subplan() needed to deal with a Result node in some
cases.

> Given the distinct whiff of second-class citizenship that traditional
> inheritance has today, I'm not sure how excited people will be about
> fixing this. I've complained before that apply_scanjoin_target_to_paths
> is brute-force and needs to be rewritten, but I don't really want to
> undertake that task right now.

I remember having *unsuccessfully* tried to make
apply_scanjoin_target_to_paths() do the targetlist pushdown for the
traditional inheritance cases as well. I agree that rethinking the
whole apply_scanjoin_target_to_paths() approach might be a better use
of our time. It has a looping-over-the-whole-partition-array
bottleneck for simple lookup queries that I have long wanted to
propose doing something about.

--
Amit Langote
EDB: http://www.enterprisedb.com