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

On Wed, Mar 19, 2025 at 3:31 PM Ashutosh Bapat
<ashutosh(dot)bapat(dot)oss(at)gmail(dot)com> wrote:
> On Wed, Mar 19, 2025 at 8:22 AM Amit Langote <amitlangote09(at)gmail(dot)com> wrote:
> > Yes, those were with pwj=off. FTR, numbers I get with pwj=on.
> >
> > 3-way:
> >
> > nparts master patched %change
> > 0 38.407 34.675 -9.717
> > 16 69.357 64.312 -7.274
> > 32 70.027 67.079 -4.210
> > 64 73.807 70.725 -4.176
> > 128 83.875 81.945 -2.301
> > 256 102.858 106.19 3.239
> > 512 181.95 180.891 -0.582
> > 1024 464.503 440.355 -5.199
> >
> > 6-way:
> >
> > nparts master patched %change
> > 0 64.411 67.175 4.291
> > 16 203.344 209.75 3.150
> > 32 296.952 300.966 1.352
> > 64 445.536 449.917 0.983
> > 128 805.103 781.892 -2.883
> > 256 1695.746 1574.346 -7.159
> > 512 4743.16 4010.196 -15.453
> > 1024 16772.454 12284.706 -26.757
> >
> > So a bit less impressive than the improvements for pwj=off. Also,
> > patch seems to make things worse for low partition counts (0-16) for
> > 6-way joins, which I am not quite sure is within the noise range.
> > Have you noticed that too and, if yes, do you know what might be
> > causing it?
>
> I have observed similar things with lower numbers of partitions. In my
> observation, such results have coincided with relatively large
> variance. With 0 partitions there is no difference in the code
> behaviour irrespective of pwj ON or Off. Hence we don't expect any
> variation in the numbers you posted yesterday and today when nparts =
> 0. But there it is. I have always observed that much variation in
> planning time for one combination or the other.
>
> With 6 -way join there will be 6 * 5 - 5 derived clauses in one
> equivalence class, That's close to 32, which is the threshold to start
> using a hash table. So some slight perturbation is expected around
> that threshold. But given that it's lower than 32, that shouldn't
> apply here.

Ok, thanks for that analysis. I don't think there's anything about
the patch that makes it particularly less suitable for pwj=on.

I read patch 0002 in detail last week and wrote a follow-up patch
(0003), mostly for cosmetic improvements, which I plan to squash into
0002.

I’ve also revised the commit messages for 0001 and 0002. Let me know
if that looks reasonable or if I’ve missed any credits.

--
Thanks, Amit Langote