postgresql - 如何优化 PostgreSQL 中大表的 BETWEEN 条件

Question

我有一个大表(大约一千万行)，我需要用执行查询？在 columnA 和 columnB 之间。

使用表和示例数据创建数据库的脚本:

CREATE DATABASE test;
\c test
-- Create test table
CREATE TABLE test (id INT PRIMARY KEY, range_start NUMERIC(12, 0), range_end NUMERIC(12, 0));
-- Fill the table with sample data
INSERT INTO test (SELECT value, value, value FROM (SELECT generate_series(1, 10000000) AS value) source);
-- Query I want to be optimized
SELECT * FROM test WHERE 5000000 BETWEEN range_start AND range_end;

我想创建 INDEX 以便 PostgreSQL 可以执行快速 INDEX SCAN 而不是 SEQ SCAN。然而，我最初的(也是显而易见的)尝试失败了:

CREATE INDEX test1 ON test (range_start, range_end);
CREATE INDEX test2 ON test (range_start DESC, range_end);
CREATE INDEX test3 ON test (range_end, range_start);

另请注意，查询中的数字专门选择在生成值的中间(否则 PostgreSQL 能够识别该值接近范围边界并执行一些优化)。

任何想法或想法将不胜感激。

---

更新 1 基于 official documentation似乎 PostgreSQL 无法正确使用索引来处理多列不等式条件。我不确定为什么会有这样的限制，如果我可以做些什么来显着加快查询速度。

---

更新 2 一种可能的方法是通过了解我拥有的最大范围来限制 INDEX SCAN，假设它是 100000:

SELECT * FROM test WHERE range_start BETWEEN 4900000 AND 5000000 AND range_end > 5000000;

Answer 1

您为什么不尝试使用要点索引的范围？

alter table test add  numr numrange;

update test set numr = numrange(range_start,range_end,'[]');

CREATE INDEX test_idx ON test USING gist (numr);

EXPLAIN ANALYZE  SELECT * FROM test WHERE 5000000.0  <@ numr;

Bitmap Heap Scan on public.test  (cost=2367.92..130112.36 rows=50000 width=48) (actual time=0.150..0.151 rows=1 loops=1)
  Output: id, range_start, range_end, numr
  Recheck Cond: (5000000.0 <@ test.numr)
  ->  Bitmap Index Scan on test_idx  (cost=0.00..2355.42 rows=50000 width=0) (actual time=0.142..0.142 rows=1 loops=1)
        Index Cond: (5000000.0 <@ test.numr)
Total runtime: 0.189 ms