1

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.

WITH DADOS as (
SELECT   
             CAST( 37959.0 as NUMERIC(9, 0)) id ,
             CAST( 1.0 as NUMERIC(9, 0)) emp,
             CAST( 12884.0 as NUMERIC(9, 0)) pro ,
             CAST( 7.891097087431E12 as NUMERIC(18, 0)) gtin,
             to_timestamp( '2023-03-07 12:48:00' , 'YYYY-MM-DD HH24:MI:SS') data3,
             CAST( 0.79 as NUMERIC(18, 8)) preco ,
             CAST( 10.0 as NUMERIC(18, 8)) valor1 ,
             CAST( 0.0 as NUMERIC(18, 8)) preco,
             CAST( 0.0 as NUMERIC(18, 8)) valor3,
             CAST( '1' as NUMERIC(1, 0)) flag ,
             0,
             current_timestamp,
             current_timestamp,
             CAST( 'USER' as varchar(255)) usr,
             to_timestamp( '2023-03-07 23:59:00' , 'YYYY-MM-DD HH24:MI:SS') data1,
             CAST( 9.0 as NUMERIC(18, 8)) codigo2,
             CAST( 5.0 as NUMERIC(1)) opt,
             to_timestamp( '2023-03-07 00:00:00' , 'YYYY-MM-DD HH24:MI:SS') data2,
             CAST( '2' as NUMERIC(1)) codigo3
UNION ALL

... over 2k  unions all..
 
SELECT   
             CAST( 37975.0 as NUMERIC(9, 0)) id ,
             CAST( 1.0 as NUMERIC(9, 0)) emp,
             CAST( 29121.0 as NUMERIC(9, 0)) pro ,
             CAST( 7.896011105178E12 as NUMERIC(18, 0)) gtin,
             to_timestamp( '2023-03-07 12:48:00' , 'YYYY-MM-DD HH24:MI:SS') data3,
             CAST( 1.98 as NUMERIC(18, 8)) preco ,
             CAST( 10.0 as NUMERIC(18, 8)) valor1 ,
             CAST( 0.0 as NUMERIC(18, 8)) preco,
             CAST( 0.0 as NUMERIC(18, 8)) valor3,
             CAST( '1' as NUMERIC(1, 0)) flag ,
             0,
             current_timestamp,
             current_timestamp,
             CAST( 'USER' as varchar(255)) usr,
             to_timestamp( '2023-03-07 23:59:00' , 'YYYY-MM-DD HH24:MI:SS') data1,
             CAST( 9.0 as NUMERIC(18, 8)) codigo2,
             CAST( 5.0 as NUMERIC(1)) opt,
             to_timestamp( '2023-03-07 00:00:00' , 'YYYY-MM-DD HH24:MI:SS') data2,
             CAST( '2' as NUMERIC(1)) codigo3 
             )
        select
    *
from
    dados
Improve this question
2
  • We use different configurations finally we are in a container with default settings. through the following container docker run -d --name container-db --restart=unless-stopped --publish 5432:5432 -e POSTGRES_USER=user -e POSTGRES_PASSWORD='123456' -e ENCODING='UTF8' --volume=/var/postgresql_home/db/postgresql/pg14/data:/var/lib/postgresql/data:rw --volume=/etc/localtime:/etc/localtime:ro --volume=/tmp/arquivos:/tmp/arquivos:rw --log-opt max-size=200m --log-opt max-file=5 postgres:14.2
    – Rafael Chielle
    Mar 7 at 20:23
  • Dear friend in the second comment it was mentioned that we used several configurations and finally we tested with the default PostgresSQL configurations. The problem that this query when running with version 11 of PostgresSQL we managed to perform the query and in version 14 of PostgresSQL in the same hardware environment and same settings it restarts.
    – Rafael Chielle
    Mar 8 at 17:27

1 Answer 1

Reset to default
3

Explanation about what happened https://www.postgresql.org/message-id/211479.1678300966%40sss.pgh.pa.us

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(at)sss(dot)pgh(dot)pa(dot)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(at)sss(dot)pgh(dot)pa(dot)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

Improve this answer
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.