Re: simplifying foreign key/RI checks

On Mon, Apr 11, 2022 at 4:47 PM Amit Langote <amitlangote09(at)gmail(dot)com> wrote:
> This one has been marked Returned with Feedback in the CF app, which
> makes sense given the discussion on -committers [1].
>
> Agree with the feedback given that it would be better to address *all*
> RI trigger check/action functions in the project of sidestepping SPI
> when doing those checks/actions, not only RI_FKey_check_ins / upd() as
> the current patch does. I guess that will require thinking a little
> bit harder about how to modularize the new implementation so that the
> various trigger functions don't end up with their own bespoke
> check/action implementations.
>
> I'll think about that, also consider what Corey proposed in [2], and
> try to reformulate this for v16.

I've been thinking about this and wondering if the SPI overhead is too
big in the other cases (cases where it is the FK table that is to be
scanned) that it makes sense to replace the actual planner (invoked
via SPI) by a hard-coded mini-planner for the task of figuring out the
best way to scan the FK table for a given PK row affected by the main
query. Planner's involvement seems necessary in those cases, because
the choice of how to scan the FK table is not as clear cut as how to
scan the PK table.

ISTM, the SPI overhead consists mainly of performing GetCachedPlan()
and executor setup/shutdown, which can seem substantial when compared
to the core task of scanning the PK/FK table, and does add up over
many rows affected by the main query, as seen by the over 2x speedup
for the PK table case gained by shaving it off with the proposed patch
[1]. In the other cases, the mini-planner will need some cycles of
its own, even though maybe not as many as by the use of SPI, so the
speedup might be less impressive.

Other than coming up with an acceptable implementation for the
mini-planner (maybe we have an example in plan_cluster_use_sort() to
ape), one more challenge is to figure out a way to implement the
CASCADE/SET trigger routines. For those, we might need to introduce
restricted forms of ExecUpdate(), ExecDelete() that can be called
directly, that is, without a full-fledged plan. Not having to worry
about those things does seem like a benefit of just continuing to use
the SPI in those cases.

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

[1]
drop table pk, fk;
create table pk (a int primary key);
create table fk (a int references pk);
insert into pk select generate_series(1, 1000000);
insert into fk select i%1000000+1 from generate_series(1, 10000000) i;

Time for the last statement:

HEAD: 67566.845 ms (01:07.567)

Patched: 26759.627 ms (00:26.760)