Hello, We found problem that a huge amount of memory was consumed when we created a foreign key on a partitioned table including a lots partitions and accessed them, as discussed in [1]. Kuroda-san's idea proposed in this thread is reducing cached SPI plans by combining several plans into one. But we are also considering another option to solve this problem, which makes users to release cached SPI plans for referential integrity as well as plain cached plans with DISCARD ALL. To do that, we added a new function, RI_DropAllPreparedPlan() which deletes all plans from ri_query_cache and modified DISCARD ALL to execute that function.
I tested using a test case yamada-san attached in [2] as follows:
[Before DISCARD ALL]
=# SELECT name, sum(used_bytes) as bytes,
pg_size_pretty(sum(used_bytes)) FROM pg_backend_memory_contexts WHERE
name LIKE 'Cached%' GROUP BY name;
name | bytes | pg_size_pretty
------------------+-----------+----------------
CachedPlanQuery | 1326280 | 1295 kB
CachedPlanSource | 1474616 | 1440 kB
CachedPlan | 744009168 | 710 MB
(3 rows)
[After DISCARD ALL]
=# DISCARD ALL;
DISCARD ALL
=# SELECT name, sum(used_bytes) as bytes,
pg_size_pretty(sum(used_bytes)) FROM pg_backend_memory_contexts WHERE
name LIKE 'Cached%' GROUP BY name;
name | bytes | pg_size_pretty
------------------+-------+----------------
CachedPlanQuery | 10280 | 10 kB
CachedPlanSource | 14616 | 14 kB
CachedPlan | 13168 | 13 kB
(3 rows)
In addition to that, a following case would be solved with this approach:
When many processes are referencing many tables defined foreign key
constraints thoroughly, a huge amount of memory will be consumed
regardless of whether referenced tables are partitioned or not.
Attached the patch. Any thoughts?
[1]
https://www.postgresql.org/message-id/flat/cab4b85d-9292-967d-adf2-be0d803c3e23%40nttcom.co.jp_1
[2]https://www.postgresql.org/message-id/cab4b85d-9292-967d-adf2-be0d803c3e23%40nttcom.co.jp_1
--
Best regards,
Yuzuko Hosoya
NTT Open Source Software Center
v1_discard_ri_spi_plans.patch
Description: Binary data
