Re: Memory consumption during partitionwise join planning

On Thu, Jul 27, 2023 at 7:28 PM Ashutosh Bapat
<ashutosh.bapat.oss@gmail.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@mail.gmail.com
[2] Memory consumption reduction in RestrictInfos -
https://www.postgresql.org/message-id/flat/CAExHW5s=bCLMMq8n_bN6iU+Pjau0DS3z_6Dn6iLE69ESmsPMJQ@mail.gmail.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