Re: Huge memory consumption on partitioned table with FKs

At Thu, 3 Dec 2020 21:40:29 +0900, Amit Langote <amitlangote09(at)gmail(dot)com> wrote in
> 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. :)

Yes. I think that that confusion doen't hurt the correctness of the
discussion:)

> > > > > 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.

That's only to show how that looks like.

regards.

--
Kyotaro Horiguchi
NTT Open Source Software Center