Re: Assert failure on 'list_member_ptr(rel->joininfo, restrictinfo)'

Hi!

On Mon, Dec 11, 2023 at 3:25 PM Ashutosh Bapat <ashutosh(dot)bapat(dot)oss(at)gmail(dot)com>
wrote:

> On Fri, Dec 8, 2023 at 11:24 PM Alexander Korotkov <aekorotkov(at)gmail(dot)com>
> wrote:
> > On Fri, Dec 8, 2023 at 3:28 PM Ashutosh Bapat
> > <ashutosh(dot)bapat(dot)oss(at)gmail(dot)com> wrote:
> > > I did some analysis of memory consumption by bitmapsets in such cases.
> > > [1] contains slides with the result of this analysis. The slides are
> > > crude and quite WIP. But they will give some idea.
> > >
> > > [1]
> https://docs.google.com/presentation/d/1S9BiAADhX-Fv9tDbx5R5Izq4blAofhZMhHcO1c-wzfI/edit?usp=sharing
> >
> > Thank you for sharing your analysis. I understand that usage of a
> > plain bitmap becomes a problem with a large number of partitions. But
> > I wonder what does "post proposed fixes" mean? Is it the fixes posted
> > in [1]. If so it's very surprising for me they are reducing the
> > memory footprint size.
>
> No. These are fixes in various threads all listed together in [1]. I
> had started investigating memory consumption by Bitmapsets around the
> same time. The slides are result of that investigation. I have updated
> slides with this reference.
>
> [1]
> https://www.postgresql.org/message-id/CAExHW5s_KwB0Rb9L3TuRJxsvO5UCtEpdskkAeMb5X1EtssMjgg@mail.gmail.com
>
> They reduce the memory footprint by Bitmapset because they reduce the
> objects that contain the bitmapsets, thus reducing the total number of
> bitmapsets produced.
>

Thank you Ashutosh for your work on this matter. With a large number of
partitions, it definitely makes sense to reduce both Bitmapset's size as
well as the number of Bitmapsets.

I've checked the patchset [1] with your test suite to check the memory
consumption. The results are in the table below.

query | no patch | patch | no self-join
removal
----------------------------------------------------------------------------------
2-way join, non partitioned | 14792 | 15208 | 29152
2-way join, no partitionwise join | 19519576 | 19519576 | 19519576
2-way join, partitionwise join | 40851968 | 40851968 | 40851968
3-way join, non partitioned | 20632 | 21784 | 79376
3-way join, no partitionwise join | 45227224 | 45227224 | 45227224
3-way join, partitionwise join | 151655144 | 151655144 | 151655144
4-way join, non partitioned | 25816 | 27736 | 209128
4-way join, no partitionwise join | 83540712 | 83540712 | 83540712
4-way join, partitionwise join | 463960088 | 463960088 | 463960088
5-way join, non partitioned | 31000 | 33720 | 562552
5-way join, no partitionwise join | 149284376 | 149284376 | 149284376
5-way join, partitionwise join | 1663896608 | 1663896608 | 1663896608

The most noticeable thing for me is that self-join removal doesn't work
with partitioned tables. I think this is the direction for future work on
this subject. In non-partitioned cases, patchset gives a small memory
overhead. However, the memory consumption is still much less than it is
without the self-join removal. So, removing the join still lowers memory
consumption even if it copies some Bitmapsets. Given that patchset [1] is
required for the correctness of memory manipulations in Bitmapsets during
join removals, I'm going to push it if there are no objections.

Links.
1.
https://www.postgresql.org/message-id/CAPpHfdtLgCryACcrmLv%3DKoq9rAB3%3Dtr5y9D84dGgvUhSCvjzjg%40mail.gmail.com

------
Regards,
Alexander Korotkov