Re: Huge memory consumption on partitioned table with FKs

On Mon, Mar 8, 2021 at 3:43 PM Andy Fan <zhihui(dot)fan1213(at)gmail(dot)com> wrote:

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

As for the compatible issue, will it be ok to introduce a new concept
like "
CREATE TABLE p (a int) partitioned by list(a) RESTRICTED". We can add these
limitation to restricted partitioned relation only.

>
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/)
>

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