Re: Huge memory consumption on partitioned table with FKs

At Thu, 26 Nov 2020 09:59:28 +0900, Keisuke Kuroda <keisuke(dot)kuroda(dot)3862(at)gmail(dot)com> wrote in
> Hi Hackers,
>
> Analyzed the problem and created a patch to resolve it.
>
> # Problem 1
>
> When you create a foreign key to a partitioned table, referential
> integrity function is created for the number of partitions.
> Internally, SPI_prepare() creates a plan and SPI_keepplan()
> caches the plan.
>
> The more partitions in the referencing table, the more plans will
> be cached.
>
> # Problem 2
>
> When referenced table is partitioned table, the larger the number
> of partitions, the larger the plan size to be cached.
>
> The actual plan processed is simple and small if pruning is
> enabled. However, the cached plan will include all partition
> information.
>
> The more partitions in the referenced table, the larger the plan
> size to be cached.
>
> # Idea for solution
>
> Constraints with the same pg_constraint.parentid can be combined
> into one plan with the same comparentid if we can guarantee that
> all their contents are the same.

The memory reduction this patch gives seems quite high with a small
footprint.

This shares RI_ConstraintInfo cache by constraints that shares the
same parent constraints. But you forgot that the cache contains some
members that can differ among partitions.

Consider the case of attaching a partition that have experienced a
column deletion.

create table t (a int primary key);
create table p (a int, r int references t(a)) partition by range(a);
create table c1 partition of p for values from (0) to (10);
create table c2 (a int, r int);
alter table c2 drop column r;
alter table c2 add column r int;
alter table p attach partition c2 for values from (10) to (20);

In that case riinfo->fk_attnums has different values from other
partitions.

=# select oid, conrelid::regclass, confrelid::regclass, conparentid, conname, conkey from pg_constraint where confrelid = 't'::regclass;

oid | conrelid | confrelid | conparentid | conname | conkey
-------+----------+-----------+-------------+----------+--------
16620 | p | t | 0 | p_r_fkey | {2}
16626 | c1 | t | 16620 | p_r_fkey | {2}
16632 | c2 | t | 16620 | p_r_fkey | {3}
(3 rows)

conkey is copied onto riinfo->fk_attnums.

> Problem 1 can be solved
> and significant memory bloat can be avoided.
> CachedPlan: 710MB -> 1466kB
>
> Solving Problem 2 could also reduce memory,
> but I don't have a good idea.
>
> Currently, DISCARD ALL does not discard CachedPlan by SPI as in
> this case. It may be possible to modify DISCARD ALL to discard
> CachedPlan and run it periodically. However, we think the burden
> on the user is high.

regards.

--
Kyotaro Horiguchi
NTT Open Source Software Center