Обсуждение: 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