Re: Optimize planner memory consumption for huge arrays

Hi!

On 20.02.2024 07:41, Andrei Lepikhov wrote:
> On 20/2/2024 04:51, Tom Lane wrote:
>> Tomas Vondra <tomas.vondra@enterprisedb.com> writes:
>>> On 2/19/24 16:45, Tom Lane wrote:
>>>> Tomas Vondra <tomas.vondra@enterprisedb.com> writes:
>>>>> For example, I don't think we expect selectivity functions to 
>>>>> allocate
>>>>> long-lived objects, right? So maybe we could run them in a dedicated
>>>>> memory context, and reset it aggressively (after each call).
>> Here's a quick and probably-incomplete implementation of that idea.
>> I've not tried to study its effects on memory consumption, just made
>> sure it passes check-world.
> Thanks for the sketch. The trick with the planner_tmp_cxt_depth 
> especially looks interesting.
> I think we should design small memory contexts - one per scalable 
> direction of memory utilization, like selectivity or partitioning 
> (appending ?).
> My coding experience shows that short-lived GEQO memory context forces 
> people to learn on Postgres internals more precisely :).
>
I think there was a problem in your patch when you freed up the memory 
of a variable in the eqsel_internal function, because we have a case 
where the variable was deleted by reference in the 
eval_const_expressions_mutator function (it is only for T_SubPlan and 
T_AlternativeSubPlan type of nodes.

This query just causes an error in your case:

create table a (id bigint, c1 bigint, primary key(id));
create table b (a_id bigint, b_id bigint, b2 bigint, primary key(a_id, 
b_id));
explain select id
     from a, b
     where id = a_id
       and b2 = (select  min(b2)
                 from    b
                 where   id = a_id);
drop table a;
drop table b;

We can return a copy of the variable or not release the memory of this 
variable.

I attached two patch: the first one is removing your memory cleanup and 
another one returns the copy of variable.

The author of the corrections is not only me, but also Daniil Anisimov.

-- 
Regards,
Alena Rybakina
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company