主页 / dba / 问题 / 324487
Accepted
Rafael Chielle
Asked: 2023-03-08 04:12:54 +0800 CST

PostgreSQL 使用大量内存

  • 772

当执行包含 WITH 和超过一千个 UNION ALL 的查询时，PostgreSQL 版本 14 或更高版本使用所有服务器内存，直到它进入恢复模式。在版本 11 的测试中，不会发生此行为。

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
linux

1 个回答

  1. Best Answer

    关于发生的事情的解释https://www.postgresql.org/message-id/211479.1678300966%40sss.pgh.pa.us

    我稍微研究了一下。像这样的查询

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

    可以看到 v11 和 v12 之间的内存使用量跳跃。另一方面，如果 UNION 臂不是那么微不足道，比如说

    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 ;

    两个分支都同样糟糕:-(，在规划过程中消耗了大约 O(N^2) 内存和时间。我将行为更改一分为二

    commit 4be058fe9ec5e630239b656af21fc083371f30ed 作者：Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> 日期：2019 年 1 月 28 日星期一 17:54:10 -0500

    在规划器中，用虚拟 RTE 替换空的 FROM 子句。

    这是一项有意的更改，以允许空 FROM 子句 SELECT 在与具有普通 FROM 子句的 SELECT 相同的基础上进行优化。问题是子查询扁平化（v11 中的第一种查询根本不会发生）在有很多 UNION 臂时会占用大量资源。

    好消息是，在 HEAD 中，两种形式的查询都很快，这发生在

    commit e42e312430279dcd8947846fdfeb4885e3754eac 作者：Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> 日期：2022 年 12 月 22 日星期四 11:02:03 -0500

    提取大量 UNION ALL 子查询时避免 O(N^2) 成本。

    我怀疑我们会冒着回补丁的风险，但至少一个解决方案即将到来。

    问候，汤姆莱恩

    • 2

相关问题