Hi All,
In try_partitionwise_join() we create SpecialJoinInfo structures for
child joins by translating SpecialJoinInfo structures applicable to
the parent join. These SpecialJoinInfos are not used outside
try_partitionwise_join() but we do not free memory allocated to those.
In try_partitionwise_join() we create as many SpecialJoinInfos as the
number of partitions. But try_partitionwise_join() itself is called as
many times as the number of join orders for a given join. Thus the
number of child SpecialJoinInfos that are created increases
exponentially with the number of partitioned tables being joined.
The attached patch (0002) fixes this issue by
1. declaring child SpecialJoinInfo as a local variable, thus
allocating memory on the stack instead of CurrentMemoryContext. The
memory on the stack is freed automatically.
2. Freeing the Relids in SpecialJoinInfo explicitly after
SpecialJoinInfo has been used.
We can not free the object trees in SpecialJoinInfo since those may be
referenced in the paths.
Similar to my previous emails [1], the memory consumption for given
queries is measured using attached patch (0001). The table definitions
and helper functions can be found in setup.sql and queries.sql.
Following table shows the reduction in the memory using the attached
patch (0002).
Number of | without | | | Absolute |
joining tables | patch | with patch | % diff | diff |
--------------------------------------------------------------------
2 | 40.9 MiB | 39.9 MiB | 2.27% | 925.7 KiB |
3 | 151.7 MiB | 142.6 MiB | 5.97% | 9.0 MiB |
4 | 464.0 MiB | 418.4 MiB | 9.83% | 45.6 MiB |
5 | 1663.9 MiB | 1419.4 MiB | 14.69% | 244.5 MiB |
Since the memory consumed by SpecialJoinInfos is exponentially
proportional to the number of tables being joined, we see that the
patch is able to save more memory at higher number of tables joined.
The attached patch (0002) frees members of individual
SpecialJoinInfos. It might have some impact on the planning time. We
may improve that by allocating the members in a separate memory
context. The memory context will be allocated and deleted in each
invocation of try_partitionwise_join(). I am assuming that deleting a
memory context is more efficient than freeing bits of memory multiple
times. But I have not tried that approach.
Another approach would be to track the SpecialJoinInfo translations
(similar to RestrictListInfo translations proposed in other email
thread [2]) and avoid repeatedly translating the same SpecialJoinInfo.
The approach will have similar disadvantages that it may increase size
of SpecialJoinInfo structure even when planning the queries which do
not need partitionwise join. So I haven't tried that approach yet.
At this stage, I am looking for opinions on
1. whether it's worth reducing this memory
2. any suggestions/comments on which approach from above is more
suitable or if there are other approaches
References
[1] https://www.postgresql.org/message-id/CAExHW5stmOUobE55pMt83r8UxvfCph+Pvo5dNpdrVCsBgXEzDQ@mail.gmail.com
[2] https://www.postgresql.org/message-id/CAExHW5s=bCLMMq8n_bN6iU+Pjau0DS3z_6Dn6iLE69ESmsPMJQ@mail.gmail.com
--
Best Wishes,
Ashutosh Bapat