Re: Memory consumed by child SpecialJoinInfo in partitionwise join planning

On Mon, Mar 18, 2024 at 5:40 PM Amit Langote <amitlangote09(at)gmail(dot)com>
wrote:

>
> >>
> >> Sorry, I should’ve mentioned that I was interested in seeing cpu times
> to compare the two approaches. Specifically, to see if the palloc / frees
> add noticeable overhead.
> >
> > No problem. Here you go
> >
> > tables | master | patched | perc_change
> > --------+----------+----------+-------------
> > 2 | 477.87 | 492.32 | -3.02
> > 3 | 1970.83 | 1989.52 | -0.95
> > 4 | 6305.01 | 6268.81 | 0.57
> > 5 | 19261.56 | 18684.86 | 2.99
> >
> > +ve change indicates reduced planning time. It seems that the planning
> time improves as the number of tables increases. But all the numbers are
> well within noise levels and thus may not show any improvement or
> deterioration. If you want, I can repeat the experiment.
>
> Thanks. I also wanted to see cpu time difference between the two
> approaches of SpecialJoinInfo allocation -- on stack and on heap. So
> comparing times with the previous version of the patch using stack
> allocation and the new one with palloc. I'm hoping that the new
> approach is only worse in the noise range.
>

Ah, sorry, I didn't read it carefully. Alvaro made me realise that I have
been gathering numbers using assert enabled builds, so they are not that
reliable. Here they are with non-assert enabled builds.

planning time (in ms) reported by EXPLAIN.
tables | master | stack_alloc | perc_change_1 | palloc | perc_change_2
| total_perc_change
--------+----------+-------------+---------------+----------+---------------+-------------------
2 | 338.1 | 333.92 | 1.24 | 332.16 | 0.53
| 1.76
3 | 1507.93 | 1475.76 | 2.13 | 1472.79 | 0.20
| 2.33
4 | 5099.45 | 4980.35 | 2.34 | 4947.3 | 0.66
| 2.98
5 | 15442.64 | 15531.94 | -0.58 | 15393.41 | 0.89
| 0.32

stack_alloc = timings with SpecialJoinInfo on stack
perc_change_1 = percentage change of above wrt master
palloc = timings with palloc'ed SpecialJoinInfo
perc_change_2 = percentage change of above wrt timings with stack_alloc
total_perc_change = percentage change between master and all patches

total change is within noise. Timing with palloc is better than that with
SpecialJoinInfo on stack but only marginally. I don't think that means
palloc based allocation is better or worse than stack based.

You requested CPU time difference between stack based SpecialJoinInfo and
palloc'ed SpecialJoinInfo. Here it is
tables | stack_alloc | palloc | perc_change
--------+-------------+----------+-------------
2 | 0.438204 | 0.438986 | -0.18
3 | 1.224672 | 1.238781 | -1.15
4 | 3.511317 | 3.663334 | -4.33
5 | 9.283856 | 9.340516 | -0.61

Yes, there's a consistent degradation albeit within noise levels.

The memory measurements
tables | master | with_patch
--------+--------+------------
2 | 26 MB | 26 MB
3 | 93 MB | 84 MB
4 | 277 MB | 235 MB
5 | 954 MB | 724 MB

The first row shows the same value because of rounding. The actual values
there are 27740432 and 26854704 resp.

Please let me know if you need anything.

--
Best Wishes,
Ashutosh Bapat