Re: Should pg 11 use a lot more memory building an spgist index?

On 2018/10/30 21:27, Amit Langote wrote:
> On Tue, Oct 30, 2018 at 7:11 PM Amit Langote
>> I've tried to fix that with the attached patches.
>>
>> 0001 adds the ability for the callers of index_beginscan to specify a
>> memory context. index_beginscan_internals switches to that context before
>> calling ambeginscan and stores into a new field xs_scan_cxt of
>> IndexScanData, but maybe storing it is unnecessary.
>>
>> 0002 builds on that and adds the ability for the callers of
>> check_exclusion_or_unique_constraints to specify a memory context. Using
>> that, it fixes the leak by teaching IndexCheckExclusion and
>> ExecIndexInsertTuples to pass a memory context that's reset before calling
>> check_exclusion_or_unique_constraints() for the next tuple.
>>
>> The following example shows that the patch works.
>>
>> With HEAD:
>>
>> create table foo (a int4range);
>> alter table foo add exclude using spgist (a with &&);
>> -- this consumes about 1GB
>> insert into foo select int4range(i,i+1) from generate_series(1, 100000) i;
>> alter table foo drop constraint foo_a_excl;
>> -- this too
>> alter table foo add exclude using spgist (a with &&);
>>
>> Patched:
>>
>> create table foo (a int4range);
>> alter table foo add exclude using spgist (a with &&);
>> -- memory consumption doesn't grow above 37MB
>> insert into foo select int4range(i,i+1) from generate_series(1, 100000) i;
>> alter table foo drop constraint foo_a_excl;
>> -- ditto
>> alter table foo add exclude using spgist (a with &&);
>
> Sorry I forgot to try the example with your patch. Maybe, it will
> have more or less the same behavior as mine, although I didn't realize
> that when I started writing my patch.

Yep, I checked that fix-spgist-memory-leaks-1.patch posted upthread gives
almost the same numbers, i.e., the maximum amount of memory consumed.

Maybe, we don't need to spoil the interface of index_beginscan with the
new memory context argument like my patch does if the simple following of
its contract by amendscan would suffice.

Thanks,
Amit