sql - 用于范围查找的快速表索引

Question

我有一个包含大约 450 万行的 Postgres 表。这些列基本上只是

low BIGINT,
high BIGINT,
data1,
data2, 
...

当你查询这个表的时候，你有一个长整型，想要找到包含那个值的low和high之间的范围对应的数据。为快速查找索引此表的最佳方法是什么？

Answer 1

A multi-column index反向排序:

CREATE INDEX tbl_low_high_idx on tbl(low, high DESC);

这样，索引可以向前扫描到low足够高，然后取所有行直到 high太低了 - 全部在一次扫描中。这就是为什么 sort order is implemented for indexes 的主要原因首先:将不同的排序顺序组合在具有不同顺序的多列索引中。基本上，b 树索引可以以几乎相同的速度在两个方向上遍历，所以 ASC/DESC单列索引几乎不需要。

---

您可能还对新的 range types 感兴趣PostgreSQL 9.2。可以indexed with a GiST index像这样:

CREATE INDEX tbl_idx ON tbl USING gist (low_high);

应该使这种形式的查询执行得非常快:

SELECT * FROM tbl WHERE my_value <@ low_high;

<@正在 "element is contained by" operator .