Horton

Re: Reducing memory consumed by RestrictInfo list translations in partitionwise join planning

Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>

View thread
From: Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>
To: Amit Langote <amitlangote09@gmail.com>
Cc: Alvaro Herrera <alvherre@alvh.no-ip.org>, Dmitry Dolgov <9erthalion6@gmail.com>, tomas@vondra.me, vignesh C <vignesh21@gmail.com>, pgsql-hackers <pgsql-hackers@postgresql.org>, Richard Guo <guofenglinux@gmail.com>, David Rowley <dgrowleyml@gmail.com>
Date: 2025-03-27T10:27:35Z
Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Add assertion to verify derived clause has constant RHS

  2. Make derived clause lookup in EquivalenceClass more efficient

  3. Doc: improve documentation for jsonpath behavior.

  4. Work around implementation restriction in adjust_appendrel_attrs.

Attachments

Hi,

On Wed, Mar 26, 2025 at 4:59 PM Ashutosh Bapat
<ashutosh.bapat.oss@gmail.com> wrote:
>
>
> The averages are now more stable than the previous exercise. Some regressions seen with the first exercise are not seen with the other and some improvements seens with the first exercise are not seen with the second and vice versa. The regressions present in both the exercises will not be seen, if I repeat the exercise a few more times. So I think those regressions or improvements seen with lower number of joins and lower number of partitions aren't real and they are mostly within noise level. However, the improvements seen with higher numbers of joins and partitions are always there irrespective of the number of times I repeat the exercise. Please note that we have only tried partitions upto 256. Previous measurements have seen stable improvements in case of higher number of partitions.
>

Further, I experimented with hash table size. Attached images have
four graphs for planning time and planner's memory consumption
measured for a 3-way join for initial has table sizes of 64, 128 and
256 respectively.
1. Planning time vs number of partitions with PWJ enabled
2. planning time vs number of partitions with PWJ disabled
3. Memory consumed vs number of partitions with PWJ enabled
4. Memory consumed vs number of partitions with PWJ disabled

Also find attached the spreadsheet containing the measurements and
also the charts.

In the graphs, one can observe that the lines corresponding to all
three hash table sizes are inseparable. That leads to a conclusion
that the planning time or memory consumption doesn't change with hash
table size.

As a result I am keeping the initial hash table size = 256L, same as
the previously attached patches.

-- 
Best Wishes,
Ashutosh Bapat