Re: making update/delete of inheritance trees scale better - Mailing list pgsql-hackers

On Wed, Mar 31, 2021 at 11:56 AM Tom Lane <tgl@sss.pgh.pa.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