我需要在一段时间内选择记录：我的表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索引的内容.我认为他们在自定义类型的某些条件下可以更有效率.这是我用例的可行选择吗？
CREATE INDEX idx_time_limits_ts_inverse ON time_limits (id_phi,end_date_time DESC);
在大多数情况下,索引的排序顺序几乎不相关. Postgres几乎可以快速向后扫描.但对于多列的范围查询,它可以产生巨大的差异.密切相关：
> PostgreSQL index not used for query on range
考虑您的查询：
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的排序顺序无关紧要.由于它检查了相等性(=),它应该首先出现.你说对了.更多相关答案：
> 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_limits
WHERE id_phi = 0
AND 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列的基准测试的相关答案：
