Postgres缓慢地按最大查询分组

snorock 发表于 Dev

西诺洛克

我正在使用postgres 9.1，并且我有一个表，其中包含约350万行的事件类型（varchar）和事件时间（时间戳）-以及其他一些字段。只有大约20种不同的事件类型，事件时间跨度约为4年。

我想获取每个事件类型的最后一个时间戳。如果我运行类似的查询：

select eventtype, max(eventtime)
from allevents
group by eventtype

大约需要20秒。选择不同的事件类型同样很慢。查询计划显示了对表的完整顺序扫描-这并不奇怪，它很慢。

解释分析以上查询给出：

HashAggregate  (cost=84591.47..84591.68 rows=21 width=21) (actual time=20918.131..20918.141 rows=21 loops=1)
  ->  Seq Scan on allevents  (cost=0.00..66117.98 rows=3694698 width=21) (actual time=0.021..4831.793 rows=3694392 loops=1)
Total runtime: 20918.204 ms

如果我添加where子句以选择特定的事件类型，则它花费的时间从40毫秒到150毫秒不等，这至少还不错。

选择特定事件类型时的查询计划：

GroupAggregate  (cost=343.87..24942.71 rows=1 width=21) (actual time=98.397..98.397 rows=1 loops=1)
  ->  Bitmap Heap Scan on allevents  (cost=343.87..24871.07 rows=14325 width=21) (actual time=6.820..89.610 rows=19736 loops=1)
        Recheck Cond: ((eventtype)::text = 'TEST_EVENT'::text)
        ->  Bitmap Index Scan on allevents_idx2  (cost=0.00..340.28 rows=14325 width=0) (actual time=6.121..6.121 rows=19736 loops=1)
              Index Cond: ((eventtype)::text = 'TEST_EVENT'::text)
Total runtime: 98.482 ms

主键是（事件类型，事件时间）。我也有以下索引：

allevents_idx (event time desc, eventtype)
allevents_idx2 (eventtype).

如何加快查询速度？

以下@denis建议的具有14个手动输入值的相关子查询的查询播放结果给出：

Function Scan on unnest val  (cost=0.00..185.40 rows=100 width=32) (actual time=0.121..8983.134 rows=14 loops=1)
   SubPlan 2
     ->  Result  (cost=1.83..1.84 rows=1 width=0) (actual time=641.644..641.645 rows=1 loops=14)
          InitPlan 1 (returns $1)
             ->  Limit  (cost=0.00..1.83 rows=1 width=8) (actual time=641.640..641.641 rows=1 loops=14)
                  ->  Index Scan using allevents_idx on allevents  (cost=0.00..322672.36 rows=175938 width=8) (actual time=641.638..641.638 rows=1 loops=14)
                         Index Cond: ((eventtime IS NOT NULL) AND ((eventtype)::text = val.val))
Total runtime: 8983.203 ms

使用@jjanes建议的递归查询，查询将按照以下计划在4到5秒之间运行：

CTE Scan on t  (cost=260.32..448.63 rows=101 width=32) (actual time=0.146..4325.598 rows=22 loops=1)
  CTE t
    ->  Recursive Union  (cost=2.52..260.32 rows=101 width=32) (actual time=0.075..1.449 rows=22 loops=1)
          ->  Result  (cost=2.52..2.53 rows=1 width=0) (actual time=0.074..0.074 rows=1 loops=1)
            InitPlan 1 (returns $1)
                  ->  Limit  (cost=0.00..2.52 rows=1 width=13) (actual time=0.070..0.071 rows=1 loops=1)
                        ->  Index Scan using allevents_idx2 on allevents  (cost=0.00..9315751.37 rows=3696851 width=13) (actual time=0.070..0.070 rows=1 loops=1)
                              Index Cond: ((eventtype)::text IS NOT NULL)
          ->  WorkTable Scan on t  (cost=0.00..25.58 rows=10 width=32) (actual time=0.059..0.060 rows=1 loops=22)
                Filter: (eventtype IS NOT NULL)
                SubPlan 3
                  ->  Result  (cost=2.53..2.54 rows=1 width=0) (actual time=0.059..0.059 rows=1 loops=21)
                        InitPlan 2 (returns $3)
                          ->  Limit  (cost=0.00..2.53 rows=1 width=13) (actual time=0.057..0.057 rows=1 loops=21)
                                ->  Index Scan using allevents_idx2 on allevents  (cost=0.00..3114852.66 rows=1232284 width=13) (actual time=0.055..0.055 rows=1 loops=21)
                                      Index Cond: (((eventtype)::text IS NOT NULL) AND ((eventtype)::text > t.eventtype))
  SubPlan 6
    ->  Result  (cost=1.83..1.84 rows=1 width=0) (actual time=196.549..196.549 rows=1 loops=22)
          InitPlan 5 (returns $6)
            ->  Limit  (cost=0.00..1.83 rows=1 width=8) (actual time=196.546..196.546 rows=1 loops=22)
                  ->  Index Scan using allevents_idx on allevents  (cost=0.00..322946.21 rows=176041 width=8) (actual time=196.544..196.544 rows=1 loops=22)
                        Index Cond: ((eventtime IS NOT NULL) AND ((eventtype)::text = t.eventtype))
Total runtime: 4325.694 ms

詹妮丝

您需要的是“跳过扫描”或“松散索引扫描”。PostgreSQL的计划器尚未自动实现这些功能，但是您可以通过递归查询诱使它使用一个。

WITH RECURSIVE  t AS (
SELECT min(eventtype) AS eventtype FROM allevents
           UNION ALL
SELECT (SELECT min(eventtype) as eventtype FROM allevents WHERE eventtype > t.eventtype)
   FROM t where t.eventtype is not null
)
select eventtype, (select max(eventtime) from allevents where eventtype=t.eventtype) from t;

可能有一种方法可以将max（eventtime）折叠到递归查询中，而不是在该查询之外进行，但是如果是这样，我还没有遇到。

为了高效，这需要一个索引（事件类型，事件时间）。您可以在事件发生时将其设置为DESC，但这不是必需的。只有事件类型只有几个不同的值（在您的情况下为21个）中，这才有效。

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。