From: | Andy Fan <zhihui(dot)fan1213(at)gmail(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | Amit Langote <amitlangote09(at)gmail(dot)com>, Kyotaro Horiguchi <horikyota(dot)ntt(at)gmail(dot)com>, keisuke kuroda <keisuke(dot)kuroda(dot)3862(at)gmail(dot)com>, Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org>, Tatsuro Yamada <tatsuro(dot)yamada(dot)tf(at)nttcom(dot)co(dot)jp>, PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>, tatsuhito(dot)kasahara(dot)rd(at)hco(dot)ntt(dot)co(dot)jp |
Subject: | Re: Huge memory consumption on partitioned table with FKs |
Date: | 2021-03-08 11:39:28 |
Message-ID: | CAKU4AWqaZ6WBnwGCSjt0ATnaON7Oyg4e3RJcfrsGnX+SKYJcSA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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/)
From | Date | Subject | |
---|---|---|---|
Next Message | Tatsuo Ishii | 2021-03-08 11:52:41 | Re: Using COPY FREEZE in pgbench |
Previous Message | Tatsuo Ishii | 2021-03-08 11:23:07 | Re: Using COPY FREEZE in pgbench |