sql - CROSS JOIN 查询性能问题

Question

我对 PostgreSQL 数据库版本 10.4 有以下查询：

SELECT
    t.pcf ,
    t.pcid,
    t.description           AS "Description",
    SUM(t.buys)             AS "Buys"
FROM
    (
        SELECT
            t.pcf,
            CASE WHEN t.row_index <= 9 THEN t.pcid ELSE NULL END AS pcid,
            CASE WHEN t.row_index <= 9 THEN t.description ELSE 'Others' END AS description,
            t.buys
        FROM
            (
                SELECT
                    t.pcf,
                    t.pcid,
                    t.description,
                    t.buys,
                    row_number() OVER (PARTITION BY t.pcf ORDER BY t.buys DESC) AS row_index
                FROM
                    (
                        SELECT
                            cbr.pcid AS pcid,
                            CONCAT(cbr.title, ' - ', cbr.description) AS description,
                            pcf,
                            COUNT(1) AS buys
                        FROM table1 cbr
                                CROSS JOIN UNNEST(
                                    ARRAY [${input_list}]) AS pcf
                        WHERE
                                cbr.tid IN (
                                SELECT
                                    cb.tid
                                FROM table1 cb
                                WHERE
                                    cb.pcid = pcf               
                                    AND cb.startdate >= DATE(${start_date})
                                    AND cb.startdate <= DATE(${end_date})
                            )
                        AND cbr.pcid != (pcf)
                        AND UPPER(cbr.country) = 'ES'
                        GROUP BY
                            1, 2, 3
                    ) t
                ORDER BY
                    1, 4 DESC
            ) AS t
    ) AS t
GROUP BY
    1, 2, 3
ORDER BY
    1, 4 DESC

Table1 是一个表，其中每行是某个事件的记录，包含大约 125 万行。

该查询以简化的方式执行以下操作：首先，它查找列pcid在传递的数组中的条目。然后，它查找共享列tid但具有不同pcid的条目（以便具有具有相同tid和多个不同pcid的条目）。然后，对结果进行分组（按某个值排序的前 9 个条目按原样选择，其余的分组为“其他”）

${variable} 表示一个参数。在这种情况下，传递了两个日期和一个整数数组。在传递的列表少于大约 20 个条目的情况下，查询运行正常，但在某些情况下，此列表可能长达 80 个条目。结合较大的日期范围，查询会耗尽资源并给出 DiskFull 错误（在这种情况下，它是 python 异常，但即使直接使用数据库控制台也会出现错误）：

OperationalError('(psycopg2.errors.DiskFull) could not write to file "base/pgsql_tmp/pgsql_tmp17143.11561": No space left on device\n')

表 1 看起来像这样（简化）：

国家	pcid	描述	时间	开始日期
ES	770	文本1	21d34983-bc4f-4749-a5e8-72b952acf938	2021-04-08
ES	770	文本2	21d37761-bab7-4058-abad-374b54acef6e	2021-04-12
ES	771	其他文本	21d54563-81b1-4eb7-9585-0956e03b7fd3	2021-04-12
ES	772	另一个文本	21d54563-81b1-4eb7-9585-0956e03b7fd3	2021-04-12
ES	753	啊啊啊	21d54563-81b1-4eb7-9585-0956e03b7fd3	2021-04-12

结果应该是这样的：

个人电脑	pcid	描述	购买
770	1368	标题 1 - 文本 1	100
771	772	标题 2 - 文本 2	75
770	753	其他标题 - 其他文本	23
770	772	yeet - 还有另一个文本	4
770	其他	bbb - aaaaa	2

如果可能的话，我该如何简化这个查询？我认为问题在于 CROSS JOIN，因为将数千或数十万行与大型数组交叉连接可能会填满所有可用内存。目前，仅增加可用内存不是一种选择。

标签： sqlpostgresqlquery-optimizationpostgresql-performancecross-join