基于地理类型列问题的 GIST 索引表达式

Question

我对postgresql如何使用索引有疑问。我在启用 Postgis 的数据库中基于 Postgresql 中的地理类型列的 Gist 索引表达式有问题。

我有下表：

CREATE TABLE place
(
  id serial NOT NULL,
  name character varying(40) NOT NULL,
  location geography(Point,4326),
  CONSTRAINT place_pkey PRIMARY KEY (id )
)

然后我根据列"位置"创建了 Gist 索引表达式

CREATE INDEX place_buffer_5000m ON place
USING GIST (ST_BUFFER(location, 5000));

现在假设在表路由中，我有带有线串对象的列形状，我想检查线穿过哪些 5000m 多边形（围绕位置）。

在我看来，下面的查询应该使用"place_buffer_5000m"索引，但不使用它。

SELECT place.name
FROM place, route
WHERE
  route.id=1 AND
  ST_CROSSES(route.shape::geometry, ST_BUFFER(place.location, 5000)::geometry))

表位大约有76000行。在此表上运行了分析和真空，并重新创建了"place_buffer_5000m"索引，但在上述查询期间未使用该索引。

当我在表格位置创建另一列名为"area_5000m"（geograpthy 类型）并更新表格时，有趣的是：

UPDATE place SET area_5000m=ST_BUFFER(location, 5000)

然后像这样为本列创建 gist 索引：

CREATE INDEX place_area_5000m ON place USING GIST (area_5000m)

然后使用查询：

SELECT place.name
FROM place, route
WHERE
  route.id=1 AND
  ST_CROSSES(route.shape::geometry, place.area_5000m::geometry))

使用索引"place_area_5000m"。问题是为什么不使用基于位置列计算的索引表达式？

Answer 1

您是否尝试将强制转换添加到"功能索引"中？这有助于确定数据类型。它应该适用于几何，可能也适用于地理，如下所示：

CREATE INDEX place_buffer_5000m ON place
USING GIST(ST_BUFFER(location, 5000)::geometry);

Answer 2

最终，您想知道距离地点 5 公里范围内有哪些路线，这是一种非常简单且常见的查询类型。但是，您落入了一个常见的陷阱：不要使用ST_Buffer来过滤！太贵了！

使用

ST_DWithin，它将使用常规 GiST 索引（如果可用）：

SELECT place.name
FROM place, route
WHERE route.id = 1 AND ST_DWithin(route.shape::geography, place.location, 5000);