Re: ModifyTable overheads in generic plans

On Thu, Apr 8, 2021 at 1:54 PM David Rowley <dgrowleyml(at)gmail(dot)com> wrote:
> On Thu, 8 Apr 2021 at 15:32, Amit Langote <amitlangote09(at)gmail(dot)com> wrote:
> > There's 10-20% improvement in this case too for various partition
> > counts, which really has more to do with 86dc90056 than the work done
> > here.
>
> I'm not sure of the exact query you're running,

The query is basically this:

\set a random(1, 1000000)
update test_table set b = :a where a = :a;

> but I imagine the
> reason that it wasn't that slow with custom plans was down to
> 428b260f87.

Right, 428b260f87 is certainly why we are seeing numbers this big at
all. However, I was saying that 86dc90056 is what makes v14 HEAD run
about 10-20% faster than *v13.2* in this benchmark. Note that
inheritance_planner() in v13, which, although not as bad as it used to
be in v11, is still more expensive than a single grouping_planner()
call for a given query that we now get thanks to 86dc90056.

> So the remaining slowness for the generic plan case with large numbers
> of partitions in the plan vs custom plans plan-time pruning is a)
> locking run-time pruned partitions; and; b) permission checks during
> executor startup?

Actually, we didn't move ahead with making the ResulRelInfos
themselves lazily as I had proposed in the original patch, so
ExecInitModifyTable() still builds ResultRelInfos for all partitions.
Although we did move initializations of some fields of it out of
ExecInitModifyTable() --- commits a1115fa0, c5b7ba4e, saving a decent
amount of time spent there. We need to study closely whether
initializing foreign partition's updates (direct or otherwise) lazily
doesn't produce wrong semantics before we can do that and we need the
ResultRelInfos to pass to those APIs.

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