根据串行是否在多范围内从表中选择行

Question

问题

给定一个具有BIGSERIAL的表，我想查询在作为参数传递的多范围中包含索引的行。

这张桌子经过修剪，看起来像：

CREATE TABLE event
(
id         UUID PRIMARY KEY,
index      BIGSERIAL   NOT NULL,
data       JSONB
);
CREATE UNIQUE INDEX ON event (index);

我天真地想选择那些在我传递的多范围内有索引的事件作为参数：

SELECT
*
FROM
event
WHERE
INDEX <@ $1;

该表相当大(30mil行)，并且查询经常运行，因此它必须快速运行。上述查询导致seq scan，其至少与我们的旧"；破解"；如下所述。

通常，多范围不包含多个范围，并且通常对于最后一个范围(即{[100, 110], [160,]})具有无限末端。此外，我们通常会查询到最后，因此值通常在3000万的范围内。查询通常只返回几百行，很少会返回更多行。

我尝试过的

我们的旧解决方案通过在后端代码中将其拆分为多范围并对每个范围进行查询来处理这个问题。

SELECT
*
FROM
event
WHERE
INDEX >= LOWER($1) AND(UPPER_INF($1) OR INDEX < UPPER($1));

这将导致高效的索引扫描，并且需要几毫秒才能完成。然而，这还远远不够理想，特别是因为我们还希望LIMIT和OFFSET适用于整个多范围。

查询和分析

使用只获取几个事件的多范围对生产运行上面的天真查询。

EXPLAIN (ANALYZE, buffers)
SELECT
id, index, data
FROM
event
WHERE
index <@ '{[30000000,30000005],[30000010,30000025)}'::int8multirange;

给出以下内容：

Gather (cost=1000.00..1476747.50 rows=154593 width=157) (actual time=4326.117..5610.042 rows=21 loops=1)
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=1204253 read=95107
I/O Timings: read=9552.560
-> Parallel Seq Scan on event (cost=0.00..1460288.20 rows=64414 width=157) (actual time=5178.488..5605.465 rows=7 loops=3)
Filter: (index <@ '{[30000000,30000006),[30000010,30000025)}'::int8multirange)
Rows Removed by Filter: 10064869
Buffers: shared hit=1204253 read=95107
I/O Timings: read=9552.560
Planning Time: 0.066 ms
Execution Time: 5614.964 ms

举个例子，使用<@和int8range获取几乎相同的数据，只需要多出几行：

EXPLAIN (ANALYZE, buffers)
SELECT
id, index, data
FROM
event
WHERE
index <@ '[30000000,30000025)'::int8range;

给出以下内容：

Gather (cost=1000.00..1476751.99 rows=154593 width=157) (actual time=2510.701..4249.822 rows=25 loops=1)
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=1236553 read=62777
I/O Timings: read=5273.182
-> Parallel Seq Scan on event (cost=0.00..1460292.69 rows=64414 width=157) (actual time=3658.410..4235.849 rows=8 loops=3)
Filter: (index <@ '[30000000,30000025)'::int8range)
Rows Removed by Filter: 10064877
Buffers: shared hit=1236553 read=62777
I/O Timings: read=5273.182
Planning Time: 0.067 ms
Execution Time: 4249.849 ms

最后，我们使用upper和lower的旧破解，并将其拆分为每个范围的多个查询：

EXPLAIN (ANALYZE, buffers)
SELECT
id, index, data
FROM
event
WHERE
index >= lower('[30000000,30000025)'::int8range) and(upper_inf('[30000000,30000025)'::int8range) OR index < upper('[30000000,30000025)'::int8range));

具有以下功能：

Index Scan using idx_event_index_unique on event (cost=0.44..9.37 rows=7 width=157) (actual time=0.016..0.037 rows=25 loops=1)
Index Cond: ((index >= '30000000'::bigint) AND (index < '30000025'::bigint))
Buffers: shared hit=28
Planning:
Buffers: shared hit=8
Planning Time: 0.305 ms
Execution Time: 0.054 ms

也就是说，与使用范围进行比较的前两个相比，它快了几个量级(0054ms比4s)。

Answer 1

对于以下内容，我使用index_seq来避免由于使用index作为标识符而不是关键字而导致的潜在混淆。

不幸的是，PostgreSQL没有使用x <@ '[l,u)'::int8range在语义上等同于l <= x < u的事实，因此没有利用可用的索引。一种选择是添加排除约束并修改查询以将index_seq表示为检查是否包含在多范围中的单个值范围：

CREATE EXTENSION IF NOT EXISTS btree_gist;
ALTER TABLE events
ADD CONSTRAINT event_index_seq_exc EXCLUDE USING gist (int8range(index_seq, index_seq, '[]') WITH &&);
EXPLAIN (ANALYZE, buffers)
SELECT
id, index_seq, data
FROM
event
WHERE
int8range(index_seq, index_seq, '[]') <@ '{[30000000,30000005],[30000010,30000025)}'::int8multirange;