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

概述我在Ubuntu 12.04上使用PostgreSQL 9.1. 我需要在一段时间内选择记录：我的表time_limits有两个时间戳字段和一个整数属性.我的实际表中还有其他列与此查询无关. create table ( start_date_time timestamp, end_date_time timestamp, id_phi integer, primary 我在Ubuntu 12.04上使用Postgresql 9.1.

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

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秒之间.

但是在时间范围中间的值仍然是几十秒.当针对表格的末尾时(按时间顺序),再两次.

我第一次尝试解释分析以获得此查询计划：

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

See the results on depesz.com.

我该怎么做才能优化搜索？一旦ID_phi设置为0,您就可以看到扫描两个时间戳列的所有时间.我不理解时间戳上的大扫描(60K行！).它们不是由主键和我添加的IDx_inversed索引的吗？

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

我已经阅读了一些关于GIST和GIN索引的内容.我认为他们在自定义类型的某些条件下可以更有效率.这是我用例的可行选择吗？

对于Postgres 9.1或更高版本：

CREATE INDEX IDx_time_limits_ts_inverseON time_limits (ID_phi,end_date_time DESC);

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

> PostgreSQL index not used for query on range

考虑您的查询：

SELECT *FROM   time_limitsWHERE  ID_phi = 0AND    start_date_time <= '2010-08-08 00:00'AND    end_date_time   >= '2010-08-08 00:05';

索引中第一列ID_phi的排序顺序无关紧要.由于它检查了相等性(=),它应该首先出现.你说对了.更多相关答案：

> Multicolumn index and performance

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;

……可能会付钱.甚至更高.更多相关答案：

> Check statistics targets in PostgreSQL

我猜你不需要为ID_phi(只有少数不同的值,均匀分布),但对于时间戳(许多不同的值,不均匀分布).
我也认为改进的指数并不重要.

CLUSTER / pg_repack

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

ALTER table time_limits CLUSTER ON IDx_time_limits_inversed;

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

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

Postgres 9.2中的GiST指数

1对于第9.2页,还有另一种可能更快的选择：GiST index for a range column.

>时间戳和带时区的时间戳有内置范围类型：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,'[]'));

立即在您的查询中使用“contains range” operator @>：

SELECT *FROM   time_limitsWHERE  ID_phi = 0AND    tsrange(start_date_time,'[]')    @> tsrange('2010-08-08 00:00','2010-08-08 00:05','[]')

Postgres 9.3中的SP-GiST指数

对于这种查询,SP-GiST索引可能更快 – 除了quoting the manual：

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

在第11页仍然如此.
您必须将(tsrange(…))上的spgist索引与(ID_phi)上的第二个(btree)索引组合在一起.由于额外的开销,我不确定这可以竞争.
仅针对tsrange列的基准测试的相关答案：

> Perform this hours of operation query in PostgreSQL

总结

以上是内存溢出为你收集整理的postgresql – 优化一系列时间戳(两列)的查询全部内容，希望文章能够帮你解决postgresql – 优化一系列时间戳(两列)的查询所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。