用于范围查找的快速表索引 [英] Fast table indexing for range lookup

问题描述

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

I have a Postgres table with about 4.5 million rows. The columns are basically just

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

查询此表时，您有一个长整数，并且想要找到包含该值的 low 和 high 之间范围的数据。索引此表进行快速查找的最佳方法是什么？

When you query this table, you have a long integer, and want to find the data corresponding to the range between low and high that includes that value. What would be the best way to index this table for fast lookups?

推荐答案

A 多列索引 ，排序顺序颠倒：

A multi-column index with reversed sort order:

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

这样，索引可以向前扫描到 low 足够高，然后占用所有行，直到 high 太低 - 全部在一次扫描中。这就是为什么为索引实施排序顺序开始的主要原因with：在不同顺序的多列索引中组合不同的排序顺序。基本上，b树索引可以以几乎相同的速度在两个方向上遍历，因此 ASC / DESC 将单列索引几乎不需要。

This way, the index can be scanned forward to where low is high enough, then take all rows until high is too low - all in one scan. That's the main reason why sort order is implemented for indexes to begin with: to combine different sort orders in a multi-column index with different order. Basically, a b-tree index can be traversed in both directions at practically the same speed, so ASC / DESC would hardly be needed for single-column indexes.

您可能还对新的 PostgreSQL 9.2 的//www.postgresql.org/docs/9.2/interactive/rangetypes.htmlrel =nofollow> 范围类型 。可以使用 GiST索引 <编制索引/ a>像这样：

You may also be interested in the new range types of PostgreSQL 9.2. Can be indexed with a GiST index like this:

CREATE INDEX tbl_idx ON tbl USING gist (low_high);

如果对此表单的查询执行速度非常快：

Should make a query of this form perform very fast:

SELECT * FROM tbl WHERE my_value <@ low_high;

< @ 成为元素包含在运算符中。

这篇关于用于范围查找的快速表索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！