对大的空间选择进行排序不使用GiST索引(Postgres11.5)

Question

我有一个表(demo(，它的主键是序列(seqno(，JSONB列(doc(中包含一个geometry属性。我已经为序列列配置了主键约束，并为几何体配置了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

这表明主键约束索引用于扫描的所有行

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

但是，在省略ORDER BY子句时，可以正确使用几何体属性的GiST索引，这要高效得多。

但它回答了一个简单得多的问题。考虑"；从芝加哥随机找5个人"；以及"；给我找芝加哥最高的五个人"；。

至于让查询更快，我会尝试ORDER BY seqno+0技巧。我不认为它会更快，但我可能错了。

我也会在(seqno, doc)上尝试btree索引，这样你就可以获得只索引的扫描，尽管如果你的几何体在它自己的列中，而不是嵌入在JSONB中，这会更好，所以你可以只索引seqno和几何体，而不是整个JSONB。理论上，PostgreSQL可以为您提供只扫描(seqno, ST_GeometryFromText(doc->>'geometry'))上的索引的索引，但它还不够聪明，无法实现这一点。

您也可以尝试在(seqno, ST_GeometryFromText(doc->>'geometry'))上使用btree_GiST扩展名创建多列GiST索引，以允许包含seqno。

最后，您可以尝试在seqno上对表进行范围分区。这需要对数据集进行重组，所以这并不像构建索引那么简单。

Answer 2

您可以尝试在查询中包括边界框重叠运算符~，正如文档所说的

此操作数将使用几何图形。

SELECT seqno, doc
FROM demo
WHERE 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))')
AND 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子句且偏移量为0的情况下运行查询，以防止内联子查询，然后应用限制。

SELECT * FROM (
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))')
OFFSET 0
) sub
ORDER BY seqno
LIMIT 10