Re: Optimize planner memory consumption for huge arrays

From: Alena Rybakina <lena(dot)ribackina(at)yandex(dot)ru>
To: Andrei Lepikhov <a(dot)lepikhov(at)postgrespro(dot)ru>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Tomas Vondra <tomas(dot)vondra(at)enterprisedb(dot)com>
Cc: Ashutosh Bapat <ashutosh(dot)bapat(dot)oss(at)gmail(dot)com>, pgsql-hackers(at)lists(dot)postgresql(dot)org, Евгений Бредня <e(dot)brednya(at)postgrespro(dot)ru>
Subject: Re: Optimize planner memory consumption for huge arrays
Date: 2024-02-22 18:50:03
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers


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,
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

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.

Alena Rybakina
Postgres Professional:
The Russian Postgres Company

Attachment Content-Type Size
0002-Utilize-memory-in-scalararraysel-in-more-optimal-man.diff.txt text/plain 2.4 KB
0001-Utilize-memory-in-scalararraysel-in-more-optimal-man.diff.txt text/plain 1.6 KB

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Ranier Vilela 2024-02-22 19:42:37 re: Speeding up COPY TO for uuids and arrays
Previous Message Alvaro Herrera 2024-02-22 18:43:00 Re: SLRU optimization - configurable buffer pool and partitioning the SLRU lock