问题描述
我有一个大约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.
您可能还对新的。可以中。
这篇关于用于范围查找的快速表索引的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!