Re: Creating foreign key on partitioned table is too slow

On 2019-Oct-25, Tomas Vondra wrote:

> The attached patch trivially fixes that by adding a memory context
> tracking all the temporary data, and then just deletes it as a whole at
> the end of the function. This significantly reduces the memory usage for
> me, not sure it's 100% correct.

FWIW we already had this code (added by commit 2455ab48844c), but it was
removed by commit d3f48dfae42f. I think we should put it back. (I
think it may be useful to use a static MemoryContext that we can just
reset each time, instead of creating and deleting each time, to save on
memcxt churn. That'd make the function non-reentrant, but I don't see
that we'd make the catalogs partitioned any time soon. This may be
premature optimization though -- not really wedded to it.)

With Amit's patch to make RelationBuildPartitionDesc called lazily, the
time to plan the RI_InitialCheck query (using Kato Sho's test case) goes
from 30 seconds to 14 seconds on my laptop. Obviously there's more that
we'd need to fix to make the scenario fully supported, but it seems a
decent step forward.

--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services