Thread: PostgreSQL uses huge amount of memory
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?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?
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;
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?
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?
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?
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