Postgres子查询在连接时运行极慢

Question

Postgres子查询在连接时运行极慢

sqlpostgresqlpostgresql-9.4

9

我有一个Postgres查询问题，已经尝试了很多方法来解决，但都没有成功。

我已经写了一些可行的查询，但关键是性能-可用的查询速度太慢了。

我有一个名为"events_hub"的表，它链接到包含不同事件信息的单独表。不同的事件是通过不同的"event_types"进行区分的。这些事件也被分组到聚合中，而聚合是通过"aggregate_id"进行区分的。

我的基本问题是，我想找到每个聚合组与事件1相关联的最早时间，然后在接近那个时间的时间窗口内计算事件2发生的次数（例如，在最早的聚合组出现之前的24小时内计算事件2发生的次数）。

事件中心表如下所示：

| aggregate_id | event_id |  event_type  | event_time |
-------------------------------------------------------
|      1       |     1    |       1      |  1st Jan   |
|      1       |     2    |       1      |  2nd Jan   |
|      2       |     3    |       1      |  2nd Jan   |
|      2       |     4    |       1      |  3rd Jan   |
|      null    |     5    |       2      |  30th Dec  |
|      null    |     6    |       2      |  31st Dec  |
|      null    |     7    |       2      |  1st Jan   |
|      null    |     8    |       2      |  1st Jan   |
-------------------------------------------------------

在上述玩具例子中，我希望返回以下内容：

| aggregate_id | count_of_event2 |
----------------------------------
|      1       |        3        |
|      2       |        2        |
----------------------------------

因为在前一天，aggregate_id 1 的最早出现有 3 次事件类型为 2 的发生次数，而 aggregate_id 2 只有 2 次。 方法1 我的第一次尝试涉及使用连接包围的 group by。以下查询运行非常快，但并不完全返回我想要的内容：

SELECT
    aggregate_id,
    count(aggregate_id)
FROM
    (SELECT
        aggregate_id,
        min(event_time) as time_of_event1
     FROM events_hub WHERE event_type = 1
     GROUP BY aggregate_id) as t1
     LEFT JOIN
    (SELECT event_time as time_of_event2
     FROM events_hub WHERE event_type = 2) as t2
     ON t2.time_of_event2 BETWEEN t1.time_of_event1 - INTERVAL '24 hours'
                          AND t1.time_of_event1
GROUP BY aggregate_id

运行EXPLAIN ANALYZE会返回以下内容（请注意，本问题中的SQL查询是我想要运行的实际查询的缩减版本-因此在解释计划中出现了一些额外的表限制）：

HashAggregate  (cost=1262545.21..1262547.21 rows=200 width=15) (actual time=536.206..539.222 rows=2824 loops=1)
  Group Key: events_hub_1.aggregate_id
  ->  Nested Loop Left Join  (cost=9137.36..1191912.59 rows=14126523 width=15) (actual time=15.419..395.895 rows=111948 loops=1)
        ->  HashAggregate  (cost=9136.80..9141.42 rows=462 width=23) (actual time=15.387..19.316 rows=2824 loops=1)
              Group Key: events_hub_1.aggregate_id
              ->  Index Only Scan using comp_index1 on events_hub events_hub_1  (cost=0.56..9110.87 rows=5186 width=23) (actual time=2.669..9.750 rows=4412 loops=1)
                    Index Cond: ((event_type_code = 5) AND (event_datetime >= '2013-01-01 00:00:00'::timestamp without time zone) AND (event_datetime <= '2013-01-02 00:00:00'::timestamp without time zone) AND (aggregate_id IS NOT NULL))
                    Heap Fetches: 4412
        ->  Index Only Scan using comp_index on events_hub  (cost=0.56..2254.33 rows=30577 width=8) (actual time=0.005..0.049 rows=40 loops=2824)
              Index Cond: ((event_type_code = 3) AND (event_datetime <= (min(events_hub_1.event_datetime))) AND (event_datetime >= ((min(events_hub_1.event_datetime)) - '12:00:00'::interval)))
              Heap Fetches: 0
Planning time: 0.326 ms
Execution time: 542.020 ms

这并不奇怪，因为我在事件集线器上有一个复合索引 (event_type, event_time)，所以基于两个事件的相对时间的相对复杂的连接条件可以快速运行。然而，当我尝试添加另一个查询条件，基于事件2的某些属性（以获取所需的结果），查询速度显著变慢（即上述查询瞬间完成，而下面的查询要运行几分钟）：

SELECT
    aggregate_id,
    count(aggregate_id)
FROM
    (SELECT
        aggregate_id,
        min(event_time) as time_of_event1
     FROM events_hub WHERE event_type = 1
     GROUP BY aggregate_id) as t1
     LEFT JOIN
    (SELECT event_id, event_time as time_of_event2
     FROM events_hub WHERE event_type = 2) as t2
     ON t2.time_of_event2 BETWEEN t1.time_of_event1 - INTERVAL '24 hours'
                          AND t1.time_of_event1
     INNER JOIN
     (SELECT event_id FROM event_2_attributes WHERE some_flag = TRUE) as t3
     ON t2.event_id = t3.event_id
GROUP BY aggregate_id

针对这个查询，EXPLAIN ANALYZE查询返回以下结果：

HashAggregate  (cost=33781.17..33783.17 rows=200 width=15) (actual time=479888.736..479891.819 rows=2824 loops=1)
  Group Key: events_hub_1.aggregate_id
  ->  Nested Loop  (cost=9625.94..33502.10 rows=55815 width=15) (actual time=346721.414..479857.494 rows=26164 loops=1)
        Join Filter: ((events_hub.event_datetime <= (min(events_hub_1.event_datetime))) AND (events_hub.event_datetime >= ((min(events_hub_1.event_datetime)) - '12:00:00'::interval)))
        Rows Removed by Join Filter: 209062796
        ->  Merge Join  (cost=489.14..14311.03 rows=1087 width=8) (actual time=1.360..1571.387 rows=74040 loops=1)
              Merge Cond: (events_hub.event_id = arrests.event_id)
              ->  Index Scan using comp_index4 on events_hub  (cost=0.44..290158.71 rows=275192 width=12) (actual time=1.344..512.787 rows=282766 loops=1)
                    Index Cond: (event_type_code = 3)
              ->  Index Scan using arrests_events_id_index on arrests  (cost=0.42..11186.59 rows=73799 width=4) (actual time=0.008..456.550 rows=74040 loops=1)
                    Filter: felony_flag
                    Rows Removed by Filter: 210238
        ->  Materialize  (cost=9136.80..9148.35 rows=462 width=23) (actual time=0.001..3.002 rows=2824 loops=74040)
              ->  HashAggregate  (cost=9136.80..9141.42 rows=462 width=23) (actual time=10.963..14.006 rows=2824 loops=1)
                    Group Key: events_hub_1.aggregate_id
                    ->  Index Only Scan using comp_index1 on events_hub events_hub_1  (cost=0.56..9110.87 rows=5186 width=23) (actual time=0.018..5.405 rows=4412 loops=1)
                          Index Cond: ((event_type_code = 5) AND (event_datetime >= '2013-01-01 00:00:00'::timestamp without time zone) AND (event_datetime <= '2013-01-02 00:00:00'::timestamp without time zone) AND (aggregate_id IS NOT NULL))
                          Heap Fetches: 4412
Planning time: 12.548 ms
Execution time: 479894.888 ms

请注意，当包含内连接时，返回的数据实际上更少。然而它仍然运行得非常慢。

我尝试嵌套这些连接并将它们调换，使其使用 RIGHT JOIN 而不是 LEFT JOIN，但这没有任何区别。

我还尝试对每个子查询使用 CTE 表达式来尝试强制执行顺序，但也没有成功。 方法二 作为第二种方法，我尝试使用一个子查询来返回事件 2 的计数：

SELECT
    t1.aggregate_id,
    (SELECT count(t3.event_id)
    FROM (SELECT event_id FROM events_hub AS t2 WHERE t2.event_type = 2
          AND t2.event_time BETWEEN t1.time_of_event1 - INTERVAL '24 hours'
                            AND t1.time_of_event1) as t3
          INNER JOIN event_2_attributes as t4
          ON t3.event_id = t4.event_id
          WHERE t4.some_flag = TRUE) as count_column
FROM
    (SELECT
        aggregate_id,
        min(event_time) as time_of_event1
     FROM events_hub WHERE event_type = 1
     GROUP BY aggregate_id) as t1

这个方法效果还不错，大概需要15秒左右就能完成。但是当我尝试将结果插入到另一个表中（这是我接下来要做的事情所必需的），查询就会花费大量时间：

CREATE TABLE tbl AS
    < query above >

对我来说这很令人困惑！

我尝试在这个查询上运行EXPLAIN ANALYZE，但是在2000秒后就退出了。虽然没有使用EXPLAIN ANALYZE，但是这个查询在15秒内就能运行。

方法3

最后一种方法，我尝试使用如下的lateral join（这里没有group by）：

WITH t1 AS
(SELECT
    aggregate_id,
    min(event_time) as time_of_event1
FROM events_hub WHERE event_type = 1
GROUP BY aggregate_id)
SELECT
    t1.aggregate_id,
    t2.event_time
FROM t1
LEFT JOIN LATERAL
    (SELECT event_time FROM
        (SELECT event_id, event_time FROM events_hub WHERE event_type = 2) as t3
        INNER JOIN
        (SELECT event_id FROM event_2_attributes WHERE some_flag = TRUE) as t4
        ON t3.event_id = t4.event_id
    WHERE t3.event_time BETWEEN t1.time_of_event1 - INTERVAL '24 hours'
                        AND t1.time_of_event1
    ) as t2
ON TRUE

这个查询可以运行，但非常非常慢，即使没有group by操作也是如此。

如果你能就这些（可能无关？）问题提供任何帮助，我们将不胜感激。值得一提的是，事件中心中的每个单独列都有索引。

非常感谢！

- Ned Yoxall

你想要什么查询结果？因为如果方法1已经很快了，为什么还需要2和3呢？ - Juan Carlos Oropeza

1

已更新代码片段。我只是更新了问题以反映我在那里所做的更改，因为我认为这更清晰...还在等待解释计划。 - Ned Yoxall

1

仍然非常低质量。要准确说明您拥有什么，您想要什么。请阅读如何提问。这里是一个很好的地方开始学习如何提高问题质量并获得更好的答案。 - Juan Carlos Oropeza

这些“aggregate_id is NULL”记录的含义是什么？如果只有两条记录具有“aggregate_id = 1”，为什么这个计数“| 1 | 3 |”是三呢？ - joop

你不同的查询将不会返回相同的结果，方法1中的第2个查询由于Where条件实际上是一个Inner join而不是Outer join，当没有事件匹配时，第1个查询将返回1（而不是0）。 - dnoeth

显示剩余6条评论

2个回答

0

不确定这是否有帮助，因为您没有包括 EXPLAIN ANALIZE，但当您创建子查询然后连接时，通常会失去索引的使用。

尝试像这样重写

SELECT e.event_id, e.event_time, ea.event_id -- but dont think you need it repeat event_id
FROM events e
INNER JOIN event_2_attributes ea
        ON e.event_id = ea.event_id 
WHERE e.event_type = 2
  AND ea.some_flag = TRUE

- Juan Carlos Oropeza

你可以使用左连接来实现同样的功能：LEFT JOIN (SELECT ... FROM xyz WHERE [condition]) 等同于 LEFT JOIN xyz ON [condition]。 - oals

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Ned Yoxall · Accepted Answer

好的，我已经解决了这个问题。

虽然不是最简洁的解决方案，但最终的技巧是创建一个表格，其中包含初始 GROUP BY 操作的结果，该操作返回与 aggregate_id 关联的最早时间：

CREATE TABLE earliest_time AS
(SELECT
    aggregate_id,
    min(event_time) as time_of_event1
 FROM events_hub WHERE event_type = 1
 GROUP BY aggregate_id)

然后在aggregate_id和time_of_event1列上添加索引。

然后按照上述方法1使用该表。

已经将子查询实体化有助于优化器选择最有效的路径，执行时间降低了两个数量级。