postgresql - 如何使用条件创建索引并使用它

Question

我有一个只有三列的表:id、time、value

我已经在 (id, time) 上有了一个索引。

现在我需要为最近的一个数据查询提速，所以我想我需要为每一天创建一个新的索引。

create index heat_20181001_20181003 on mytable using
brin ("id", "time") where time between '2018-10-01 00:00:00' and 
'2018-10-03 23:59:59';

但是我查询的时候，显示使用旧索引，没有使用新索引。

查询:

EXPLAIN  ANALYZE ( select * from mytable where time between 
'2018-10-01 00:00:00' and '2018-10-03 23:59:59' and "id" = '453615414');

结果:

Index Scan using "mytable_id_579df36b7ac8aa8e_idx" on mytable 
  (cost=0.57..3931.98 rows=1133 width=87) (actual time=3.045..12294.511 rows=3762 loops=1)
  Index Cond: ((("id")::text = '453615414'::text) AND ("time" >= '2018-10-01 00:00:00+08'::timestamp with time zone) AND ("time" <= '2018-10-03 23:59:59+08'::timestamp with time zone))
Planning time: 0.320 ms
Execution time: 12295.688 ms
(4 rows)

如何使用索引进行查询？或者我该如何改进它？

Answer 1

如果时间间隔是可变的，那么您的原始索引是完美的(这就是 PostgreSQL 使用它的原因)。

如果时间间隔是常数，那么完美的索引就是

CREATE INDEX ON mytable (id)
   WHERE time BETWEEN '2018-10-01 00:00:00' AND '2018-10-03 23:59:59';

BRIN 索引可能没有用，因为表中元组的物理顺序与索引顺序不相同。

您肯定不每天创建一个索引，您的原始索引就足够了。