主页 / dba / 问题 / 39589
Accepted
Stephane Rolland
Asked: 2013-04-10 11:07:03 +0800 CST

优化对一系列时间戳（两列）的查询

  • 772

我在 Ubuntu 12.04 上使用 PostgreSQL 9.1。

我需要在一段时间内选择记录：我的表time_limits有两个timestamp字段和一个integer属性。我的实际表中有其他列与此查询无关。

create table (
   start_date_time timestamp,
   end_date_time timestamp, 
   id_phi integer, 
   primary key(start_date_time, end_date_time,id_phi);

该表包含大约 2M 条记录。

像下面这样的查询花费了大量的时间：

select * from time_limits as t 
where t.id_phi=0 
and t.start_date_time <= timestamp'2010-08-08 00:00:00'
and t.end_date_time   >= timestamp'2010-08-08 00:05:00';

所以我尝试添加另一个索引 - PK的倒数：

create index idx_inversed on time_limits(id_phi, start_date_time, end_date_time);

我的印象是性能提高了：访问表中间记录的时间似乎更合理：大约在 40 到 90 秒之间。

但是对于时间范围中间的值，它仍然是几十秒。当目标是表的末尾时（按时间顺序），还有两倍。

我explain analyze第一次尝试得到这个查询计划：

 Bitmap Heap Scan on time_limits  (cost=4730.38..22465.32 rows=62682 width=36) (actual time=44.446..44.446 rows=0 loops=1)
   Recheck Cond: ((id_phi = 0) AND (start_date_time <= '2011-08-08 00:00:00'::timestamp without time zone) AND (end_date_time >= '2011-08-08 00:05:00'::timestamp without time zone))
   ->  Bitmap Index Scan on idx_time_limits_phi_start_end  (cost=0.00..4714.71 rows=62682 width=0) (actual time=44.437..44.437 rows=0 loops=1)
         Index Cond: ((id_phi = 0) AND (start_date_time <= '2011-08-08 00:00:00'::timestamp without time zone) AND (end_date_time >= '2011-08-08 00:05:00'::timestamp without time zone))
 Total runtime: 44.507 ms

在 depesz.com 上查看结果。

我可以做些什么来优化搜索？id_phi您可以看到一旦设置为 ，扫描两个时间戳列所花费的所有时间0。而且我不理解时间戳上的大扫描（60K 行！）。他们不是由主键索引并且idx_inversed我添加的吗？

我应该从时间戳类型更改为其他类型吗？

我已经阅读了一些关于 GIST 和 GIN 索引的内容。我收集它们可以在自定义类型的某些条件下更有效。对于我的用例来说，这是一个可行的选择吗？

postgresql index

4 个回答

  1. Best Answer

    对于 Postgres 9.1 或更高版本：

    CREATE INDEX idx_time_limits_ts_inverse
ON time_limits (id_phi, start_date_time, end_date_time DESC);

    在大多数情况下，索引的排序顺序几乎不相关。Postgres 几乎可以以同样快的速度向后扫描。但是对于多列的范围查询，它可以产生巨大的差异。密切相关：

    考虑您的查询：

    SELECT *
FROM   time_limits
WHERE  id_phi = 0
AND    start_date_time <= '2010-08-08 00:00'
AND    end_date_time   >= '2010-08-08 00:05';

    id_phi索引中第一列的排序顺序无关紧要。由于已检查是否相等( =)，因此它应该排在第一位。你说对了。更多相关答案：

    Postgres 可以立即跳到id_phi = 0并考虑匹配索引的以下两列。这些是使用反向排序顺序（，）的范围条件查询的。在我的索引中，符合条件的行排在第一位。应该是使用 B-Tree 索引1的最快方法：<=>=

    • 您想要start_date_time <= something：索引首先具有最早的时间戳。
    • 如果符合条件，还要检查第 3 列。
      递归直到第一行不符合条件（超快）。
    • 您想要end_date_time >= something：索引首先具有最新的时间戳。
    • 如果符合条件，请继续获取行，直到第一个没有（超快）。
      继续第 2 列的下一个值 ..

    Postgres 可以向前或向后扫描。你有索引的方式，它必须读取前两列匹配的所有行，然后过滤第三列。请务必阅读索引章节和ORDER BY手册中的内容。它非常适合您的问题。

    前两列有多少行匹配？
    只有少数具有start_date_time接近表的开始时间范围。但几乎所有行都id_phi = 0在表的时间顺序末尾！因此，性能会随着开始时间的延迟而下降。

    规划师估计

    规划器估计rows=62682您的示例查询。其中，没有一个符合（rows=0）。如果您增加表的统计目标，您可能会得到更好的估计。对于 2.000.000 行...

    ALTER TABLE time_limits ALTER start_date_time SET STATISTICS 1000;
ALTER TABLE time_limits ALTER end_date_time   SET STATISTICS 1000;

    ......可能会付出。甚至更高。更多相关答案：

    我猜你不需要它id_phi（只有几个不同的值，均匀分布），而是时间戳（很多不同的值，分布不均匀）。
    我也认为改进后的索引并不重要。

    CLUSTER/ pg_repack / pg_squeeze

    但是，如果您希望它更快，您可以简化表中行的物理顺序。如果您有能力专门锁定您的表（例如在下班时间），请重写您的表并根据索引对行进行排序CLUSTER：

    CLUSTER time_limits USING idx_time_limits_inversed;

    或者考虑pg_repack或后来的pg_squeeze，它可以在没有排他锁的情况下做同样的事情。

    无论哪种方式，效果都是需要从表中读取的块更少，并且所有内容都是预先排序的。这是一种一次性效应，随着时间的推移而恶化，表上的写入会使物理排序顺序碎片化。

    Postgres 9.2+ 中的 GiST 索引

    1对于 pg 9.2+，还有另一个可能更快的选项：范围列的 GiST 索引。

    • timestamp和timestamp with time zone: tsrange,tstzrange有内置的范围类型。对于integer像id_phi. 更小，维护成本也更低。但是使用组合索引，查询总体上可能仍然会更快。

    • 更改表定义或使用表达式索引。

    • 对于手头的多列 GiST 索引，您还需要btree_gist安装附加模块（每个数据库一次），该模块提供运算符类以包含integer.

    三连冠！多列功能 GiST 索引：

    CREATE EXTENSION IF NOT EXISTS btree_gist;  -- if not installed, yet

CREATE INDEX idx_time_limits_funky ON time_limits USING gist
(id_phi, tsrange(start_date_time, end_date_time, '[]'));

    现在在查询中使用“包含范围”运算符：@>

    SELECT *
FROM   time_limits
WHERE  id_phi = 0
AND    tsrange(start_date_time, end_date_time, '[]')
    @> tsrange('2010-08-08 00:00', '2010-08-08 00:05', '[]')

    Postgres 9.3+ 中的 SP-GiST 索引

    对于这种查询，SP-GiST 索引可能会更快-除了引用手册：

    目前，只有 B-tree、GiST、GIN 和 BRIN 索引类型支持多列索引。

    在 Postgres 12 中仍然如此。
    您必须将一个spgist索引 on just(tsrange(...))与第二个btree索引 on结合起来(id_phi)。由于增加了开销，我不确定这是否可以竞争。
    仅针对tsrange列的基准的相关答案：

    • 241

  2. 然而，欧文的回答已经很全面了：

    时间戳的范围类型在 PostgreSQL 9.1 中可用，并带有来自 Jeff Davis 的 Temporal 扩展：https ://github.com/jeff-davis/PostgreSQL-Temporal

    注意：具有有限的功能（使用 Timestamptz，并且您只能让 '[)' 样式重叠 afaik）。此外，还有很多其他重要的理由升级到 PostgreSQL 9.2。

    • 5

  3. 您可以尝试以不同的顺序创建多列索引：

    primary key(id_phi, start_date_time,end_date_time);

    我曾经发布过一个类似的问题，也与多列索引上的索引排序有关。关键是首先尝试使用最严格的条件来减少搜索空间。

    编辑：我的错误。现在我看到你已经定义了这个索引。

    • 3

  4. 我设法迅速增加（从 1 秒到 70 毫秒）

    我有一个包含许多测量值和许多级别（l列）（30s、1m、1h 等）的聚合表，有两个范围限制列：$s开始和$e结束。

    我创建了两个多列索引：一个用于开始，一个用于结束。

    我调整了选择查询：选择它们的起始边界在给定范围内的范围。另外选择其末端边界在给定范围内的范围。

    解释显示两个行流有效地使用我们的索引。

    索引：

    drop index if exists agg_search_a;
CREATE INDEX agg_search_a
ON agg (measurement_id, l, "$s");

drop index if exists agg_search_b;
CREATE INDEX agg_search_b
ON agg (measurement_id, l, "$e");

    选择查询：

    select "$s", "$e", a, t, b, c from agg
where 
    measurement_id=0 
    and l =  '30s'
    and (
        (
            "$s" > '2013-05-01 02:05:05'
            and "$s" < '2013-05-01 02:18:15'
        )
        or 
        (
             "$e" > '2013-05-01 02:00:05'
            and "$e" < '2013-05-01 02:18:05'
        )
    )

;

    解释：

    [
  {
    "Execution Time": 0.058,
    "Planning Time": 0.112,
    "Plan": {
      "Startup Cost": 10.18,
      "Rows Removed by Index Recheck": 0,
      "Actual Rows": 37,
      "Plans": [
    {
      "Startup Cost": 10.18,
      "Actual Rows": 0,
      "Plans": [
        {
          "Startup Cost": 0,
          "Plan Width": 0,
          "Actual Rows": 26,
          "Node Type": "Bitmap Index Scan",
          "Index Cond": "((measurement_id = 0) AND ((l)::text = '30s'::text) AND (\"$s\" > '2013-05-01 02:05:05'::timestamp without time zone) AND (\"$s\" < '2013-05-01 02:18:15'::timestamp without time zone))",
          "Plan Rows": 29,
          "Parallel Aware": false,
          "Actual Total Time": 0.016,
          "Parent Relationship": "Member",
          "Actual Startup Time": 0.016,
          "Total Cost": 5,
          "Actual Loops": 1,
          "Index Name": "agg_search_a"
        },
        {
          "Startup Cost": 0,
          "Plan Width": 0,
          "Actual Rows": 36,
          "Node Type": "Bitmap Index Scan",
          "Index Cond": "((measurement_id = 0) AND ((l)::text = '30s'::text) AND (\"$e\" > '2013-05-01 02:00:05'::timestamp without time zone) AND (\"$e\" < '2013-05-01 02:18:05'::timestamp without time zone))",
          "Plan Rows": 39,
          "Parallel Aware": false,
          "Actual Total Time": 0.011,
          "Parent Relationship": "Member",
          "Actual Startup Time": 0.011,
          "Total Cost": 5.15,
          "Actual Loops": 1,
          "Index Name": "agg_search_b"
        }
      ],
      "Node Type": "BitmapOr",
      "Plan Rows": 68,
      "Parallel Aware": false,
      "Actual Total Time": 0.027,
      "Parent Relationship": "Outer",
      "Actual Startup Time": 0.027,
      "Plan Width": 0,
      "Actual Loops": 1,
      "Total Cost": 10.18
    }
      ],
      "Exact Heap Blocks": 1,
      "Node Type": "Bitmap Heap Scan",
      "Plan Rows": 68,
      "Relation Name": "agg",
      "Alias": "agg",
      "Parallel Aware": false,
      "Actual Total Time": 0.037,
      "Recheck Cond": "(((measurement_id = 0) AND ((l)::text = '30s'::text) AND (\"$s\" > '2013-05-01 02:05:05'::timestamp without time zone) AND (\"$s\" < '2013-05-01 02:18:15'::timestamp without time zone)) OR ((measurement_id = 0) AND ((l)::text = '30s'::text) AND (\"$e\" > '2013-05-01 02:00:05'::timestamp without time zone) AND (\"$e\" < '2013-05-01 02:18:05'::timestamp without time zone)))",
      "Lossy Heap Blocks": 0,
      "Actual Startup Time": 0.033,
      "Plan Width": 44,
      "Actual Loops": 1,
      "Total Cost": 280.95
    },
    "Triggers": []
  }
]

    诀窍是您的计划节点只包含想要的行。以前我们在计划节点中获得了数千行，因为它选择了all points from some point in time to the very end，然后下一个节点删除了不必要的行。

    • 1

相关问题