Re: Huge memory consumption on partitioned table with FKs

On Thu, Dec 3, 2020 at 5:13 PM Kyotaro Horiguchi
<horikyota(dot)ntt(at)gmail(dot)com> wrote:
> At Thu, 3 Dec 2020 16:41:45 +0900, Amit Langote <amitlangote09(at)gmail(dot)com> wrote in
> > Maybe I misread but I think you did in your email dated Dec 1 where you said:
> >
> > "After an off-list discussion, we confirmed that even in that case the
> > patch works as is because fk_attnum (or contuple.conkey) always stores
> > key attnums compatible to the topmost parent when conparent has a
> > valid value (assuming the current usage of fk_attnum), but I still
> > feel uneasy to rely on that unclear behavior."
>
> fk_attnums *doesn't* refers to to top parent talbe of the referencing
> side. it refers to attributes of the partition that is compatible with
> the same element of fk_attnums of the topmost parent. Maybe I'm
> misreading.

Yeah, no I am confused. Reading what I wrote, it seems I implied that
the referenced (PK relation's) partitions have RI_ConstraintInfo which
makes no sense, although there indeed is one pg_constraint entry that
is defined on the FK root table for every PK partition with its OID as
confrelid, which is in addition to an entry containing the root PK
table's OID as confrelid. I confused those PK-partition-referencing
entries as belonging to the partitions themselves. Although in my
defence, all of those entries' conkey contains the FK root table's
attributes, so at least that much holds. :)

> > > > On the topic of how we'd be able to share even the RI_ConstraintInfos
> > > > among partitions, that would indeed look a bit more elaborate than the
> > > > patch we have right now.
> > >
> > > Maybe just letting the hash entry for the child riinfo point to the
> > > parent riinfo if all members (other than constraint_id, of course)
> > > share the exactly the same values. No need to count references since
> > > we don't going to remove riinfos.
> >
> > Ah, something maybe worth trying. Although the memory we'd save by
> > sharing the RI_ConstraintInfos would not add that much to the savings
> > we're having by sharing the plan, because it's the plans that are a
> > memory hog AFAIK.
>
> I agree that plans are rather large but the sharable part of the
> RI_ConstraintInfos is 536 bytes, I'm not sure it is small enough
> comparing to the plans. But that has somewhat large footprint.. (See
> the attached)

Thanks for the patch.

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