在一系列时间戳上优化查询（两列）

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

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';

#1 楼

对于Postgres 9.1或更高版本：

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

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

PostgreSQL索引不用于范围查询

考虑您的查询：

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树索引的最快方法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;

...可能需要付款。甚至更高。有关此答案的更多信息：

检查PostgreSQL中的统计目标

我想您不需要id_phi（只有几个不同的值，均匀分布），但是时间戳（很多不同的值，分布不均匀）。
我也不认为改进索引有什么意义。但是，您可以更快地简化表中行的物理顺序。如果您有能力单独锁定表（例如在非工作时间锁定表），请使用CLUSTER重写表并根据索引对行进行排序：

CLUSTER time_limits USING idx_time_limits_inversed;

，或者考虑使用pg_repack或更高版本的pg_squeeze不论哪种方式，结果都是需要从表中读取更少的块，并且所有内容都进行了预排序。随着时间的推移，表上的写入会破坏物理排序顺序，这是一种一次性的影响。用于范围列。


CLUSTER和timestamp有内置的范围类型：timestamp with time zone，tsrange。对于其他tstzrange列（例如integer），btree索引通常更快。维护更小，更便宜。但是使用组合索引，整体查询可能仍然会更快。


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


对于您还需要安装多列GiST索引，您还需要安装附加模块id_phi（每个数据库一次），该模块为操作员类提供一个btree_gist。


三连胜！多列功能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, '[]'));

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

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树，GiST，GIN和BRIN索引类型支持多列索引。 br />
在Postgres 12中仍然适用。
您必须将@>上的spgist索引与(tsrange(...))上的第二个btree索引结合起来。随着额外的开销，我不确定这是否可以竞争。
仅提供(id_phi)列的基准测试相关答案：

在PostgreSQL中执行此小时的操作查询

#2 楼

但是，Erwin的答案已经很全面了：

时间戳的范围类型可在PostgreSQL 9.1中获得，而Jeff Davis的Temporal扩展名是这样的： />
注意：功能有限（使用Timestamptz，并且您只能使'[）'样式重叠afaik）。另外，还有很多其他重要原因可以升级到PostgreSQL 9.2。

#3 楼

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

primary key(id_phi, start_date_time,end_date_time);

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

编辑：我的错。现在我看到您已经定义了该索引。

#4 楼

我设法快速增加（从1秒到70ms）

我有一个表，其中包含许多测量值和许多级别的汇总（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'
        )
    )

;

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