时间戳

首页 > 解决方案 > 对 GROUP BY 的查询非常慢

postgresql - 对 GROUP BY 的查询非常慢

问题描述

我的查询非常慢（约 100 分钟）。我通过用后缀表示它省略了很多内部子节点...

HashAggregate  (cost=6449635645.84..6449635742.59 rows=1290 width=112) (actual time=5853093.882..5853095.159 rows=785 loops=1)
   Group Key: p.processid
   ->  Nested Loop  (cost=10851145.36..6449523319.09 rows=832050 width=112) (actual time=166573.289..5853043.076 rows=3904 loops=1)
         Join Filter: (SubPlan 2)
         Rows Removed by Join Filter: 617040
         ->  Merge Left Join  (cost=5425572.68..5439530.95 rows=1290 width=799) (actual time=80092.782..80114.828 rows=788 loops=1) ...
         ->  Materialize  (cost=5425572.68..5439550.30 rows=1290 width=112) (actual time=109.689..109.934 rows=788 loops=788) ...
         SubPlan 2
           ->  Limit  (cost=3869.12..3869.13 rows=5 width=8) (actual time=9.155..9.156 rows=5 loops=620944) ...
Planning time: 1796.764 ms
Execution time: 5853316.418 ms
(2836 rows)

上面的查询计划是对视图执行的查询，下面的schema（简化）

create or replace view foo_bar_view(processid, column_1, run_count) as
SELECT
    q.processid,
    q.column_1,
    q.run_count
FROM
    (
    SELECT
        r.processid,
        avg(h.some_column) AS column_1,
        -- many more aggregate function on many more columns
        count(1) AS run_count
    FROM
        foo_bar_table r,
        foo_bar_table h
    WHERE (h.processid IN (SELECT p.processid
                             FROM process p
                                      LEFT JOIN bar i ON p.barid = i.id
                                      LEFT JOIN foo ii ON i.fooid = ii.fooid
                                      JOIN foofoobar pt ON p.typeid = pt.typeid AND pt.displayname ~~
                                                                                      ((SELECT ('%'::text || property.value) || '%'::text
                                                                                        FROM property
                                                                                        WHERE property.name = 'something'::text))
                             WHERE p.processid < r.processid
                               AND (ii.name = r.foo_name OR ii.name IS NULL AND r.foo_name IS NULL)
                             ORDER BY p.processid DESC
                             LIMIT 5))
    GROUP BY r.processid
    ) q;

我只是想明白，这是否意味着大部分时间都花在了执行上GROUP BY processid？

如果不是，是什么导致了问题？我想不出这个查询这么慢的原因。

使用的聚合函数是 avg、min、max、stddev。总共使用了 52 个，13 个列中的每个列上 4 个。

更新：在 SubPlan 2 的子节点上展开。我们可以看到 process_pkey 部分的Bitmap Index Scan是瓶颈。

->  Bitmap Heap Scan on process p_30  (cost=1825.89..3786.00 rows=715 width=24) (actual time=8.642..8.833 rows=394 loops=620944)
      Recheck Cond: ((typeid = pt_30.typeid) AND (processid < p.processid))
      Heap Blocks: exact=185476288
      ->  BitmapAnd  (cost=1825.89..1825.89 rows=715 width=0) (actual time=8.611..8.611 rows=0 loops=620944)
            ->  Bitmap Index Scan on ix_process_typeid  (cost=0.00..40.50 rows=2144 width=0) (actual time=0.077..0.077 rows=788 loops=620944)
                  Index Cond: (typeid = pt_30.typeid)
            ->  Bitmap Index Scan on process_pkey  (cost=0.00..1761.20 rows=95037 width=0) (actual time=8.481..8.481 rows=145093 loops=620944)
                  Index Cond: (processid < p.processid)

我无法弄清楚为什么它使用位图索引扫描而不是索引扫描。从表面上看，应该只有 788 行需要比较？那不是更快吗？如果不是，我该如何优化这个查询？

processid是 bigint 类型并且有一个索引

完整的执行计划在这里。

标签： postgresql

解决方案

您方便地在执行计划中省略了表的名称，但我假设嵌套循环连接在foo_bar_table rand之间foo_bar_table h，并且子计划是IN条件。

高执行时间是由子计划引起的，该子计划针对每个潜在的连接结果执行，即 788 * 788 = 620944 次。620944 * 9.156 占 5685363 毫秒。

创建此索引：

CREATE INDEX ON process (typeid, processid, installationid);

并运行VACUUM：

VACUUM process;

这应该会给你一个快速的仅索引扫描。

推荐阅读