Re: IndexJoin memory problem using spgist and boxes

Hi Alexander,

thanks for the feedback.

> I can reproduce the high memory consumption with your queries.
>
> Looking at the patch, I see that you changed the lifetime of the temporary
> context from per-tuple to per-index-scan. It is not obvious that this change
> is correct. Could you explain, what memory context are involved in the scan,
> and what their lifetimes are, before and after your changes? What are these
> memory allocations that are causing the high consumption, and what code
> makes them? This will make it easier to understand your changes.
>
Yes, you are right, I changed the temporary context for calling the
user-defined inner consistent method from per-tuple to per-index-scan.
For boxes this function is spg_box_quad_inner_consistent in
geo_spgist.c that allocates nodes, RangeBoxes, and RectBoxes.

The problem before this patch was that the traversalMemoryContext in
this function was set to per-query lifetime.
The memory allocations in the per-query lifetime caused this high
memory consumption.

I changed the temporary context to per-index-scan so that it can also
be used for traversalMemoryContext.
Calling the function in per-index-scan lifetime did cause a high
memory consumption.

The better alternative may be to have two temporary memory contexts,
one per-tuple for calling the inner consistent method and one
per-index-scan for the traversal memory.

What do you think?

>
>>
>>
https://www.postgresql.org/message-id/flat/CAPqRbE5vTGWCGrOc91Bmu-0o7CwsU0UCnAshOtpDR8cSpSjy0g%40mail.gmail.com#CAPqRbE5vTGWCGrOc91Bmu-0o7CwsU0UCnAshOtpDR8cSpSjy0g@mail.gmail.com
>
>
> Also, this link doesn't open for me.
>
The link works perfectly fine for me.

Best regards,
Anton