Re: Huge memory consumption on partitioned table with FKs

Amit Langote <amitlangote09(at)gmail(dot)com> writes:
> On Fri, Mar 5, 2021 at 6:00 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> This claim seems false on its face:
>>> All child constraints of a given foreign key constraint can use the
>>> same RI query and the resulting plan, that is, no need to create as
>>> many copies of the query and the plan as there are partitions, as
>>> happens now due to the child constraint OID being used in the key
>>> for ri_query_cache.

> The quoted comment could have been written to be clearer about this,
> but it's not talking about the table that is to be queried, but the
> table whose RI trigger is being executed. In all the cases except one
> (mentioned below), the table that is queried is the same irrespective
> of which partition's trigger is being executed, so we're basically
> creating the same plan separately for each partition.

Hmm. So, the key point is that the values coming from the partitioned
child table are injected into the test query as parameters, not as
column references, thus it doesn't matter *to the test query* what
numbers the referencing columns have in that child. We just have to
be sure we pass the right parameter values. But ... doesn't the code
use riinfo->fk_attnums[] to pull out the values to be passed?

IOW, I now get the point about being able to share the SPI plans,
but I'm still dubious about sharing the RI_ConstraintInfo cache entries.

It looks to me like the v4 patch is now actually not sharing the
cache entries, ie their hash key is just the child constraint OID
same as before; but the comments are pretty confused about this.

It might be simpler if you add just one new field which is the OID of
the constraint that we're building the SPI query from, which might be
either equal to constraint_id, or the OID of some parent constraint.
In particular it's not clear to me why we need both constraint_parent
and constraint_root_id.

regards, tom lane