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

On Wed, Mar 19, 2025 at 3:31 PM Ashutosh Bapat
<ashutosh.bapat.oss@gmail.com> wrote:
> On Wed, Mar 19, 2025 at 8:22 AM Amit Langote <amitlangote09@gmail.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