Обсуждение: PostgreSQL uses huge amount of memory

Поиск
Список
Период
Сортировка

PostgreSQL uses huge amount of memory

От
Mauricio Martini
Дата:

When executing a query containing WITH and more than a thousand UNION ALL, PostgreSQL version 14 or higher uses all server memory until it enters recovery mode. In tests with version 11, this behavior does not occur. shouldn't he respect the work_mem?
*I know I should refactor, but what caught my attention was the fact that a query uses the entire resource without restriction, which does not occur in version 11.4, I did a test in version 15, which also occurs.
Has anything related to memory usage in this sense been changed?


Re: PostgreSQL uses huge amount of memory

От
Neimar Sierota
Дата:
I did a test as you mentioned and in postgresql version 11 I didn't observe any change in memory consumption while the query is executed. With version 14, running the same query, it is possible to observe a considerable increase in memory usage during execution.
Could it be a bug in postgresql's memory management?

WITH DADOS  as (
    select 1  
 UNION ALL
SELECT 1
 UNION ALL
SELECT 1
 UNION ALL
...
 )
select    * from   dados;

Em qua, 8 de mar de 2023 10:17, Mauricio Martini <martini.mauricio@hotmail.com> escreveu:

When executing a query containing WITH and more than a thousand UNION ALL, PostgreSQL version 14 or higher uses all server memory until it enters recovery mode. In tests with version 11, this behavior does not occur. shouldn't he respect the work_mem?
*I know I should refactor, but what caught my attention was the fact that a query uses the entire resource without restriction, which does not occur in version 11.4, I did a test in version 15, which also occurs.
Has anything related to memory usage in this sense been changed?


Re: PostgreSQL uses huge amount of memory

От
Scott Ribe
Дата:
Could it be a difference in parallel queries? (I don't remember when those were added.) Certainly, a union is an
exampleof something that will clearly get multiple workers. 

--
Scott Ribe
scott_ribe@elevated-dev.com
https://www.linkedin.com/in/scottribe/



> On Mar 8, 2023, at 6:29 AM, Neimar Sierota <neimarsmo@gmail.com> wrote:
>
> I did a test as you mentioned and in postgresql version 11 I didn't observe any change in memory consumption while
thequery is executed. With version 14, running the same query, it is possible to observe a considerable increase in
memoryusage during execution. 
> Could it be a bug in postgresql's memory management?




Re: PostgreSQL uses huge amount of memory

От
Fernando Hevia
Дата:
Parallel queries do exist in v11.  
Rather trivial but I wonder if the OP has the same settings for max_parallel_worker_per_gather and work_mem in both database versions.

Try setting max_parallel_workers_per_gather = 1 in v14 before running the query. Is the memory usage kept in check with it?


El mié, 8 mar 2023 a la(s) 13:13, Scott Ribe (scott_ribe@elevated-dev.com) escribió:
Could it be a difference in parallel queries? (I don't remember when those were added.) Certainly, a union is an example of something that will clearly get multiple workers.

--
Scott Ribe
scott_ribe@elevated-dev.com
https://www.linkedin.com/in/scottribe/



> On Mar 8, 2023, at 6:29 AM, Neimar Sierota <neimarsmo@gmail.com> wrote:
>
> I did a test as you mentioned and in postgresql version 11 I didn't observe any change in memory consumption while the query is executed. With version 14, running the same query, it is possible to observe a considerable increase in memory usage during execution.
> Could it be a bug in postgresql's memory management?



Re: PostgreSQL uses huge amount of memory

От
Tom Lane
Дата:
Neimar Sierota <neimarsmo@gmail.com> writes:
> I did a test as you mentioned and in postgresql version 11 I didn't observe
> any change in memory consumption while the query is executed. With version
> 14, running the same query, it is possible to observe a considerable
> increase in memory usage during execution.

I poked into this a little bit.  With a query like

SELECT 0 AS x
UNION ALL SELECT 1
UNION ALL SELECT 2
UNION ALL SELECT 3
...
UNION ALL SELECT 9999
UNION ALL SELECT 10000
;

it is possible to see a jump in memory usage between v11 and v12.
On the other hand, if the UNION arms aren't quite so trivial, say

CREATE TABLE dual AS SELECT 1 AS y;

SELECT 0 AS x FROM dual
UNION ALL SELECT 1 FROM dual
UNION ALL SELECT 2 FROM dual
UNION ALL SELECT 3 FROM dual
...
UNION ALL SELECT 9999 FROM dual
UNION ALL SELECT 10000 FROM dual
;

both branches are equally bad :-(, consuming about O(N^2) memory
and time during planning.  I bisected the behavior change to

commit 4be058fe9ec5e630239b656af21fc083371f30ed
Author: Tom Lane <tgl@sss.pgh.pa.us>
Date:   Mon Jan 28 17:54:10 2019 -0500

    In the planner, replace an empty FROM clause with a dummy RTE.

which was an intentional change to allow empty-FROM-clause SELECTs
to be optimized on the same basis as SELECTs with normal FROM clauses.
The problem is that subquery flattening, which didn't happen at all
with the first type of query in v11, is eating a lot of resources
when there are a lot of UNION arms.

The good news is that in HEAD, both shapes of query are fast,
which happened at

commit e42e312430279dcd8947846fdfeb4885e3754eac
Author: Tom Lane <tgl@sss.pgh.pa.us>
Date:   Thu Dec 22 11:02:03 2022 -0500

    Avoid O(N^2) cost when pulling up lots of UNION ALL subqueries.

I doubt we'd risk back-patching that, but at least a solution is
in the offing.

            regards, tom lane