Re: Huge memory consumption on partitioned table with FKs

On Fri, Mar 5, 2021 at 5:00 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Amit Langote <amitlangote09(at)gmail(dot)com> writes:
> > Updated patch attached.
>
> 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.
>
> What if the child tables don't have the same physical column numbers
> as the parent?

My point below is a bit off-topic, but I want to share it here. Since
we implement a partitioned table in PG with the inherited class, it has much
more flexibility than other databases. Like in PG, we allow different
partitions
have different physical order, different indexes, maybe different index
states.
that would cause our development work hard in many places and cause some
runtime issues as well (like catalog memory usage), have we discussed
limiting some flexibility so that we can have better coding/running
experience?
I want to do some research in this direction, but it would be better that I
can
listen to any advice from others. More specifically, I want to reduce the
memory
usage of Partitioned table/index as the first step. In my testing, each
IndexOptInfo
will use 2kB memory in each backend.

> The comment claiming that it's okay if riinfo->fk_attnums
> doesn't match seems quite off point, because the query plan is still
> going to need to use the correct column numbers. Even if column numbers
> are the same, the plan would also contain table and index OIDs that could
> only be right for one partition.
>
> I could imagine translating a parent plan to apply to a child instead of
> building it from scratch, but that would take a lot of code we don't have
> (there's no rewriteHandler infrastructure for plan nodes).
>
> Maybe I'm missing something, because I see that the cfbot claims
> this is passing, and I'd sure like to think that our test coverage
> is not so thin that it'd fail to detect probing the wrong partition
> for foreign key matches. But that's what it looks like this patch
> will do.
>
> regards, tom lane
>
>
>

--
Best Regards
Andy Fan (https://www.aliyun.com/)