PostgreSQL 9.3 - ELEMENT IS CONTAINED BY vs. BETWEEN PostgreSQL 9.3 - 元素包含与区间比较

Question

我有一个大的（30M行）简单表格...

CREATE TABLE "Foo"."Bar" (
    "BarID" BIGSERIAL PRIMARY KEY,
    "DateTime" TIMESTAMP NOT NULL,
    "Bar" TEXT NOT NULL
);

一个简单的索引：

CREATE INDEX ON "Foo"."Bar"("DateTime");

...还有一个简单的问题：

在2015年的第一小时，哪些"BarID"值具有"DateTime"值？

---

所以我创建了这个查询 #1：

SELECT
    "Bar"."BarID"
FROM
    "Foo"."Bar"
WHERE
    "Bar"."DateTime" <@ TSRANGE('2015-01-01 00:00:00', '2015-01-01 01:00:00');

"...和这个查询 #2："

SELECT
    "Bar"."BarID"
FROM
    "Foo"."Bar"
WHERE
    "Bar"."DateTime" BETWEEN '2015-01-01 00:00:00' AND '2015-01-01 01:00:00';

结果

查询 #1 使用序列扫描需要 60 秒。

查询 #2 使用索引扫描只需要 0.02 秒。

我尝试使用 USING GiST 创建了另一个索引，但没有改善。

为什么会这样呢？

- losthorse

---

我的猜测是BETWEEN是sargable，而TSRANGE()则不是。 - Dour High Arch

Answer 1

范围表达式是可搜索的。您只需要一个可以使用范围表达式的索引。您在“时间戳”列上有一个B树索引和一个GiST索引。时间戳范围表达式无法利用这些索引。

在时间戳范围表达式上创建一个GiST索引，并更新统计信息。

create index on "Foo"."Bar" 
using gist(tsrange("DateTime"::timestamp, "DateTime"::timestamp, '[]'));

analyze "Foo"."Bar";

你的“DateTime”列代表一个时间点，因此时间戳范围表达式应该具有包括下限和上限（使用'[]'）。

重新编写 WHERE 子句以使用相同的表达式。

explain analyze
select "BarID"
from "Foo"."Bar"
where tsrange("DateTime"::timestamp, "DateTime"::timestamp, '[]') <@ tsrange('2015-01-01 00:00:00', '2015-01-01 01:00:00');

这个查询可以使用索引，在一个大约有一百万行的表上，它在这里运行时间约为半毫秒。

"Bitmap Heap Scan on "Bar"  (cost=10.19..859.53 rows=246 width=8) (actual time=0.195..0.551 rows=219 loops=1)"
"  Recheck Cond: (tsrange("DateTime", "DateTime", '[]'::text) <@ '["2015-01-01 00:00:00","2015-01-01 01:00:00")'::tsrange)"
"  ->  Bitmap Index Scan on "Bar_tsrange_idx"  (cost=0.00..10.13 rows=246 width=0) (actual time=0.160..0.160 rows=219 loops=1)"
"        Index Cond: (tsrange("DateTime", "DateTime", '[]'::text) <@ '["2015-01-01 00:00:00","2015-01-01 01:00:00")'::tsrange)"
"总运行时间：0.589 毫秒"

- Mike Sherrill 'Cat Recall'