postgresql表中间加列_postgresql – 优化一系列时间戳(两列)的查询

对于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 index1的最快方式：

>你想要start_date_time< = something：index首先有最早的时间戳.

>如果符合条件,也请查看第3栏.

递归,直到第一行无法符合条件(超快).

>你想要end_date_time> =某事：index首先有最新的时间戳.

>如果符合条件,请继续获取行,直到第一行不行(超快).

继续第2列的下一个值..

Postgres可以向前或向后扫描.你有索引的方式,它必须读取前两列匹配的所有行,然后过滤第三列.请务必阅读手册中的第Indexes and ORDER BY章.它很适合你的问题.

前两列有多少行匹配？

只有少数start_date_time接近表的时间范围的开始.但几乎所有行的id_phi = 0在表的时间顺序结束！因此,性能随着启动时间的延长而恶化.

规划师估计

规划器为您的示例查询估计rows = 62682.其中,没有限定(行= 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

如果您希望它更快,您可以简化表格中行的物理顺序.如果您能够在短时间内(例如在非工作时间)专门锁定表,则根据索引重写表并订购行：

ALTER TABLE time_limits CLUSTER ON idx_time_limits_inversed;

使用并发访问时,请考虑pg_repack,它可以在没有独占锁定的情况下执行相同操作.

无论哪种方式,效果是需要从表中读取更少的块,并且所有内容都是预先排序的.如果你在桌子上写字,这是一次性的影响随着时间的推移而恶化.

Postgres 9.2中的GiST指数

>时间戳和带时区的时间戳有内置范围类型：tsrange, tstzrange.对于额外的整数列id_phi,btree索引通常更快,维护更小,更便宜.但是使用组合索引,查询整体可能仍会更快.

>更改表格定义或将其设为functional index.

>对于手头的多列索引,还需要附加模块btree_gist(每个数据库一次)以在GiST索引中包含整数.

三连胜！多列函数GiST索引：

CREATE EXTENSION IF NOT EXISTS btree_gist; -- only 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指数

Currently, only the B-tree, GiST, GIN, and BRIN index types support multicolumn indexes.

在第11页仍然如此.

您必须将(tsrange(…))上的spgist索引与(id_phi)上的第二个(btree)索引组合在一起.由于额外的开销,我不确定这可以竞争.

仅针对tsrange列的基准测试的相关答案：