Re: Memory consumption during partitionwise join planning

On Thu, Jul 27, 2023 at 7:28 PM Ashutosh Bapat
<ashutosh(dot)bapat(dot)oss(at)gmail(dot)com> wrote:

> The memory consumption is broken by the objects that consume memory
> during planning. The second attached patch is used to measure breakup
> by functionality . Here's a brief explanation of the rows in the
> table.
>
> 1. Restrictlist translations: Like other expressions the Restrictinfo
> lists of parent are translated to obtain Restrictinfo lists to be
> applied to child partitions (base as well as join). The first row
> shows the memory consumed by the translated RestrictInfos. We can't
> avoid these translations but closer examination reveals that a given
> RestrictInfo gets translated multiple times proportional to the join
> orders. These repeated translations can be avoided. I will start a
> separate thread to discuss this topic.
>
> 2. Paths: this is the memory consumed when creating child join paths
> and the Append paths in parent joins. It includes memory consumed by
> the paths as well as translated expressions. I don't think we can
> avoid creating these paths. But once the best paths are chosen for the
> lower level relations, the unused paths can be freed. I will start a
> separate thread to discuss this topic.
>
> 3. targetlist translation: child join relations' targetlists are
> created by translating parent relations' targetlist. This row shows
> the memory consumed by the translated targetlists. This translation
> can't be avoided.
>
> 4. child SpecialJoinInfo: This is memory consumed in child joins'
> SpecialJoinInfos translated from SpecialJoinInfo applicable to parent
> joins. The child SpecialJoinInfos are translated on the fly when
> computing child joins but are never freed. May be we can free them on
> the fly as well or even better save them somewhere and fetch as and
> when required. I will start a separate thread to discuss this topic.
>
> 5. Child join RelOptInfos: memory consumed by child join relations.
> This is unavoidable as we need the RelOptInfos representing the child
> joins.
>
> Table 3: Partitionwise join planning memory breakup
> Num joins | 2 | 3 | 4 | 5 |
> ------------------------------------------------------------------------
> 1. translated | 1.8 MiB | 13.1 MiB | 58.0 MiB | 236.5 MiB |
> restrictlists | | | | |
> ------------------------------------------------------------------------
> 2. creating child | 11.6 MiB | 59.4 MiB | 207.6 MiB | 768.2 MiB |
> join paths | | | | |
> ------------------------------------------------------------------------
> 3. translated | 723.5 KiB | 3.3 MiB | 10.6 MiB | 28.5 MiB |
> targetlists | | | | |
> ------------------------------------------------------------------------
> 4. child | 926.8 KiB | 9.0 MiB | 45.7 MiB | 245.5 MiB |
> SpecialJoinInfo | | | | |
> ------------------------------------------------------------------------
> 5. Child join rels | 1.6 MiB | 7.9 MiB | 23.8 MiB | 67.5 MiB |
> ------------------------------------------------------------------------

>
> While subproblems and their solutions will be discussed in separate
> email threads, this thread is to discuss

I posted these patches long back but forgot to mention those in this
thread. Listing them here at one place.

[1] Memory reduction in SpecialJoinInfo -
https://www.postgresql.org/message-id/flat/CAExHW5tHqEf3ASVqvFFcghYGPfpy7o3xnvhHwBGbJFMRH8KjNw(at)mail(dot)gmail(dot)com
[2] Memory consumption reduction in RestrictInfos -
https://www.postgresql.org/message-id/flat/CAExHW5s=bCLMMq8n_bN6iU+Pjau0DS3z_6Dn6iLE69ESmsPMJQ(at)mail(dot)gmail(dot)com
[3] Memory consumption reduction in paths -
https://www.postgresql.org/message-id/flat/CAExHW5tUcVsBkq9qT%3DL5vYz4e-cwQNw%3DKAGJrtSyzOp3F%3DXacA%40mail.gmail.com
[4] Small change to reuse child bitmapsets in try_partitionwise_join()
- https://www.postgresql.org/message-id/CAExHW5snUW7pD2RdtaBa1T_TqJYaY6W_YPVjWDrgSf33i-0uqA%40mail.gmail.com

--
Best Wishes,
Ashutosh Bapat