时间戳

首页 > 解决方案 > Postgresql 12：重叠运算符的性能问题并在同一张表上加入

postgresql - Postgresql 12：重叠运算符的性能问题并在同一张表上加入

问题描述

我在“非常简单”的请求性能方面遇到了麻烦：

数据库架构：

CREATE TABLE bigdata3.data_1_2021
(
   p_value      float8      NOT NULL,
   p_timestamp  tsrange     NOT NULL
);

CREATE INDEX IF NOT EXISTS idx_data_1_2021_ts ON bigdata3.data_1_2021 USING gist (p_timestamp);
CREATE INDEX IF NOT EXISTS idx_data_1_2021_ts2 ON bigdata3.data_1_2021 USING btree (p_timestamp);

仅供参考，我正在使用 btree_gist 扩展

CREATE EXTENSION IF NOT EXISTS btree_gist;

此外，我的表中有 19037 行。所以现在，请求：

WITH data_1 AS
(
  SELECT t1.p_value AS value,
         t1.p_timestamp AS TS
  FROM "bigdata3".data_1_2021 AS t1
  WHERE TSRANGE( '2021-02-01 00:00:00.000'::TIMESTAMP,'2021-02-17 09:51:54.000'::TIMESTAMP) && t1.p_timestamp
)
SELECT t1.ts AS ts,
       t2.ts AS ts,
       t1.value,
       t2.value
      FROM data_1 as t1
        INNER JOIN data_1 as t2 ON t1.ts && t2.ts

此请求需要 1 分钟。 当我进行解释时，很多事情对我来说似乎很奇怪：

QUERY PLAN
Nested Loop  (cost=508.96..8108195.71 rows=1801582 width=80)
  Join Filter: (t1.ts && t2.ts)
  CTE data_1
    ->  Seq Scan on data_1_2021 t1_1  (cost=0.00..508.96 rows=18982 width=29)
          Filter: ('["2021-02-01 00:00:00","2021-02-17 09:51:54")'::tsrange && p_timestamp)
  ->  CTE Scan on data_1 t1  (cost=0.00..379.64 rows=18982 width=40)
  ->  CTE Scan on data_1 t2  (cost=0.00..379.64 rows=18982 width=40)

1）我希望对 ts 范围的序列扫描使用“idx_data_1_2021_ts”索引

2）我希望连接使用相同的索引进行哈希或合并连接

奇怪的事情来了：

WITH data_1 AS
(
  SELECT t1.p_value AS value,
         t1.p_timestamp AS TS
  FROM "bigdata3".data_1_2021 AS t1
  WHERE TSRANGE( '2021-02-01 00:00:00.000'::TIMESTAMP,'2021-02-17 09:51:54.000'::TIMESTAMP) && t1.p_timestamp
),
data_2 AS
(
  SELECT t1.p_value AS value,
         t1.p_timestamp AS TS
  FROM "bigdata3".data_1_2021 AS t1
  WHERE TSRANGE( '2021-02-01 00:00:00.000'::TIMESTAMP,'2021-02-17 09:51:54.000'::TIMESTAMP) && t1.p_timestamp
)
SELECT t1.ts AS ts,
       t2.ts AS ts,
       t1.value,
       t2.value
      FROM data_1 as t1
        INNER JOIN data_2 as t2 ON t1.ts && t2.ts

我只将我的 data_1 复制为 data_2 并将我的联接更改为将 data_1 与 data_2 联接：

Nested Loop  (cost=0.28..116154.41 rows=1801582 width=58)
  ->  Seq Scan on data_1_2021 t1  (cost=0.00..508.96 rows=18982 width=29)
        Filter: ('["2021-02-01 00:00:00","2021-02-17 09:51:54")'::tsrange && p_timestamp)
  ->  Index Scan using idx_data_1_2021_ts on data_1_2021 t1_1  (cost=0.28..4.19 rows=190 width=29)
        Index Cond: ((p_timestamp && t1.p_timestamp) AND (p_timestamp && '["2021-02-01 00:00:00","2021-02-17 09:51:54")'::tsrange))

请求需要 1 秒，现在使用索引！ 但是......由于 seq 扫描和嵌套循环，它仍然不完美。

另一条信息：在连接上切换到 = 运算符使第一种情况更快，但第二种情况更慢......

有没有人解释为什么在加入同一个表时没有正确使用索引？我也接受任何建议以使这个请求更快。

非常感谢，克莱门特

PS：我知道这个请求看起来很愚蠢，我把我的真实案例简单地指出了我的问题。

编辑1：根据要求，第一个请求的分析+缓冲区说明：

QUERY PLAN
Nested Loop  (cost=509.04..8122335.52 rows=1802721 width=40) (actual time=0.025..216996.205 rows=19680 loops=1)
  Join Filter: (t1.ts && t2.ts)
  Rows Removed by Join Filter: 359841220
  Buffers: shared hit=271
  CTE data_1
    ->  Seq Scan on data_1_2021 t1_1  (cost=0.00..509.04 rows=18988 width=29) (actual time=0.013..38.263 rows=18970 loops=1)
          Filter: ('["2021-02-01 00:00:00","2021-02-17 09:51:54")'::tsrange && p_timestamp)
          Rows Removed by Filter: 73
          Buffers: shared hit=271
  ->  CTE Scan on data_1 t1  (cost=0.00..379.76 rows=18988 width=40) (actual time=0.016..8.083 rows=18970 loops=1)
        Buffers: shared hit=1
  ->  CTE Scan on data_1 t2  (cost=0.00..379.76 rows=18988 width=40) (actual time=0.000..4.723 rows=18970 loops=18970)
        Buffers: shared hit=270
Planning Time: 0.176 ms
Execution Time: 217208.300 ms

第二个：

QUERY PLAN
Nested Loop  (cost=0.28..116190.34 rows=1802721 width=58) (actual time=280.133..817.611 rows=19680 loops=1)
  Buffers: shared hit=76361
  ->  Seq Scan on data_1_2021 t1  (cost=0.00..509.04 rows=18988 width=29) (actual time=0.030..7.909 rows=18970 loops=1)
        Filter: ('["2021-02-01 00:00:00","2021-02-17 09:51:54")'::tsrange && p_timestamp)
        Rows Removed by Filter: 73
        Buffers: shared hit=271
  ->  Index Scan using idx_data_1_2021_ts on data_1_2021 t1_1  (cost=0.28..4.19 rows=190 width=29) (actual time=0.041..0.042 rows=1 loops=18970)
        Index Cond: ((p_timestamp && t1.p_timestamp) AND (p_timestamp && '["2021-02-01 00:00:00","2021-02-17 09:51:54")'::tsrange))
        Buffers: shared hit=76090
Planning Time: 709.820 ms
Execution Time: 981.659 ms

标签： postgresqlperformancequery-optimizationgist

解决方案

因为您的 CTE 在查询中被引用了两次，所以规划器会自动实现它。一旦具体化，它就不能再使用基础表上的索引了。（也就是说，它不能用于高选择性条件t1.ts && t2.ts。它仍然可以用于“二月上半月”条件，因为它发生在具体化之前，但由于它是如此非选择性，它选择不使用它）

您可以强制它不实现它：

WITH data_1 AS NOT MATERIALIZED (...

在我的手中，这样做会产生与编写两个单独的 CTE 相同的执行计划，每个 CTE 只被引用一次。

推荐阅读