Re: More efficient RI checks - take 2

Hi,

On 2020-04-22 13:18:06 -0400, Alvaro Herrera wrote:
> > But honestly, my gut feeling is that for a lot of cases it'd be best
> > just bypass parser, planner *and* executor. And just do manual
> > systable_beginscan() style checks. For most cases we exactly know what
> > plan shape we expect, and going through the overhead of creating a query
> > string, parsing, planning, caching the previous steps, and creating an
> > executor tree for every check is a lot. Even just the amount of memory
> > for caching the plans can be substantial.
>
> Avoiding the executor altogether scares me, but I can't say exactly why.
> Foe example, you couldn't use foreign tables at either side of the FK --
> but we don't allow FKs on those tables and we'd have to use some
> specific executor node for such a thing anyway. So this not a real
> argument against going that route.

I think it'd also not that hard to call a specific routine for doing
fkey checks on the remote side. Probably easier to handle things that
way than through "generic" FDW code.

> > Side note: I for one would appreciate a setting that just made all RI
> > actions requiring a seqscan error out...
>
> Hmm, interesting thought. I guess there are actual cases where it's
> not strictly necessary, for example where the referencing table is
> really tiny -- not the *referenced* table, note, since you need the
> UNIQUE index on that side in any case. I suppose that's not a really
> interesting case.

Yea, the index is pretty much free there. Except I guess for the case of
a tiny table thats super heavily updated.

> I don't think this is implementable when going through SPI.

It'd probably be not too hard to approximate by just erroring out when
there's no index on the relevant column, before even doing the planning.

Greetings,

Andres Freund