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(dot)vondra(at)enterprisedb(dot)com> writes:
>>> On 2/19/24 16:45, Tom Lane wrote:
>>>> Tomas Vondra <tomas(dot)vondra(at)enterprisedb(dot)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