postgresql - 对大型空间选择进行排序不使用 GiST 索引(Postgres 11.5)

Question

我有一个表( demo )，其主键是一个序列( seqno )和一个 geometry包含在 JSONB 列 ( doc ) 中的属性。我已经为序列列配置了一个主键约束，并为几何体配置了一个 GiST 索引。我已经通过运行 VACUUM ANALYZE 收集了统计信息.这是一个相当大的表(42M 行)。

CREATE TABLE demo
(
    seqno bigint NOT NULL DEFAULT nextval('seqno'::regclass),
    doc jsonb NOT NULL DEFAULT '{}'::jsonb,
    CONSTRAINT demo_pkey PRIMARY KEY (seqno)
)

CREATE INDEX demo_doc_geometry_gist
ON demo USING gist (st_geometryfromtext(doc ->> 'geometry'::text))

我想在相当大的区域上执行空间过滤器并返回前 10 行，按其主键排序。因此，我尝试了以下查询:

SELECT seqno, doc
FROM demo
WHERE ST_Within(ST_GeometryFromText((doc->>'geometry')), ST_GeometryFromText('POLYGON((4.478054829251019 52.61266886732067,5.247097798001019 52.61266886732067,5.247097798001019 52.156694555984416,4.478054829251019 52.156694555984416,4.478054829251019 52.61266886732067))'))
ORDER BY seqno
LIMIT 10

这导致以下查询计划:

Limit  (cost=1000.59..15169.06 rows=10 width=633) (actual time=2479.372..2496.737 rows=10 loops=1)
  ->  Gather Merge  (cost=1000.59..19780184.81 rows=13960 width=633) (actual time=2479.370..2496.732 rows=10 loops=1)
        Workers Planned: 2
        Workers Launched: 2
        ->  Parallel Index Scan using demo_pkey on demo  (cost=0.56..19777573.45 rows=5817 width=633) (actual time=2440.310..2450.101 rows=5 loops=3)
              Filter: (('0103000020407100000100000005000000CFCA3EB32997F4402D3225A6F0D02041DDFD612B4A5F0141D66C69E40CCD20415E0E6F193D580141AE7BECF122511C412C99A20E8F48F440E6B3764403591C41CFCA3EB32997F4402D3225A6F0D02041'::geometry ~ st_geometryfromtext((doc ->> 'geometry'::text))) AND _st_contains('0103000020407100000100000005000000CFCA3EB32997F4402D3225A6F0D02041DDFD612B4A5F0141D66C69E40CCD20415E0E6F193D580141AE7BECF122511C412C99A20E8F48F440E6B3764403591C41CFCA3EB32997F4402D3225A6F0D02041'::geometry, st_geometryfromtext((doc ->> 'geometry'::text))))
              Rows Removed by Filter: 221313
Planning Time: 0.375 ms
Execution Time: 2496.786 ms

这说明使用主键约束索引扫描所有行，并对每一行进行空间过滤，显然效率很低。给定的空间谓词有超过 5M 的匹配项。根本不使用 GiST 索引。
然而，当省略 ORDER BY 子句时，几何属性的 GiST 索引被正确使用，效率更高。

Limit  (cost=0.42..128.90 rows=10 width=633) (actual time=0.381..0.745 rows=10 loops=1)
  ->  Index Scan using demo_doc_geometry_gist on demo  (cost=0.42..179352.99 rows=13960 width=633) (actual time=0.380..0.742 rows=10 loops=1)
        Index Cond: ('0103000020407100000100000005000000CFCA3EB32997F4402D3225A6F0D02041DDFD612B4A5F0141D66C69E40CCD20415E0E6F193D580141AE7BECF122511C412C99A20E8F48F440E6B3764403591C41CFCA3EB32997F4402D3225A6F0D02041'::geometry ~ st_geometryfromtext((doc ->> 'geometry'::text)))
        Filter: _st_contains('0103000020407100000100000005000000CFCA3EB32997F4402D3225A6F0D02041DDFD612B4A5F0141D66C69E40CCD20415E0E6F193D580141AE7BECF122511C412C99A20E8F48F440E6B3764403591C41CFCA3EB32997F4402D3225A6F0D02041'::geometry, st_geometryfromtext((doc ->> 'geometry'::text)))
Planning Time: 0.245 ms
Execution Time: 0.780 ms

有没有办法使这个查询快速？我们可以让查询计划器将 GiST 索引与 PK 索引结合起来得到一个排序结果吗？还有其他建议吗？

Answer 1

This shows that the primary key constraint index is used to scan all rows

它不会扫描所有行，它会在找到 10 行匹配后停止。这似乎是大约 221313 * 3 + 10 行，或大约总行数的 1.6%。这并不明显是错误的做法。您可以通过更改为 ORDER BY seqno+0 来抑制主键索引的使用。 .这应该使用 GiST 索引，但我不会指望这会更快。

However, when leaving out the ORDER BY clause, the GiST index for the geometry property is properly used, which is far more efficient.

但它回答了一个简单得多的问题。考虑一下“从芝加哥找 5 个随机的人”和“给我找 5 个芝加哥最高的人”之间的区别。
至于使查询更快，我会尝试 ORDER BY seqno+0诡计。我不认为它会更快，但我可能是错的。
我也会在 (seqno, doc) 上尝试一个 btree 索引因此您可以获得仅索引扫描，尽管如果您的几何图形位于其自己的列中，而不是嵌入在 JSONB 中，这会好得多，因此您可以仅索引 seqno 和几何图形，而不是整个 JSONB。理论上，PostgreSQL 可以给你一个索引，只扫描 (seqno, ST_GeometryFromText(doc->>'geometry')) 上的索引。 ，但它只是不够聪明来意识到这一点。
您也可以在 (seqno, ST_GeometryFromText(doc->>'geometry')) 上尝试多列 GiST 索引。使用 btree_gist 扩展来启用包含 seqno。
最后，您可以尝试在 seqno 上对表进行范围分区。这将需要重新组织您的数据集，因此并不像构建索引那么简单。