postgresql - 在处理文本搜索和地理空间数据时如何影响 Postgres 查询分析器

Question

我无法解决以下语句的严重性能问题。

给定情况

• 我有一个安装了 Postgis 1.4 的 postgres 8.4 数据库
• 我有一个包含大约 900 万个条目的地理空间表。该表有一个(postgis)几何列和一个 tsvector 列
• 我在几何体上有一个 GIST 索引，在 vname 列上有一个 VNAME 索引
• 表是ANALYZE

我想在这些几何图形的子集中执行 to_tsquery 文本搜索，这应该会返回所有受影响的 ID。

要搜索的区域会将 900 万个数据集限制在大约 100.000 个，并且此区域内 ts_query 的结果集很可能会给出 0..1000 个条目的输出。

问题

查询分析器决定他想首先对 vname 进行位图索引扫描，然后聚合并在几何上放置过滤器(以及我在该语句中的其他条件)

查询分析器输出:

Aggregate  (cost=12.35..12.62 rows=1 width=510) (actual time=5.616..5.616 rows=1 loops=1)
->  Bitmap Heap Scan on mxgeom g  (cost=8.33..12.35 rows=1 width=510) (actual time=5.567..5.567 rows=0 loops=1)
     Recheck Cond: (vname @@ '''hemer'' & ''hauptstrasse'':*'::tsquery)
     Filter: (active AND (geom && '0107000020E6100000010000000103000000010000000B0000002AFFFF5FD15B1E404AE254774BA8494096FBFF3F4CC11E40F37563BAA9A74940490200206BEC1E40466F209648A949404DF6FF1F53311F400C9623C206B2494024EBFF1F4F711F404C87835954BD4940C00000B0E7CA1E4071551679E0BD4940AD02004038991E40D35CC68418BE49408EF9FF5F297C1E404F8CFFCB5BBB4940A600006015541E40FAE6468054B8494015040060A33E1E4032E568902DAE49402AFFFF5FD15B1E404AE254774BA84940'::geometry) AND (mandator_id = ANY ('{257,1}'::bigint[])))
     ->  Bitmap Index Scan on gis_vname_idx  (cost=0.00..8.33 rows=1 width=0) (actual time=5.566..5.566 rows=0 loops=1)
           Index Cond: (vname @@ '''hemer'' & ''hauptstrasse'':*'::tsquery)

这会导致大量 I/O - AFAIK 首先限制几何形状，然后再进行 vname 搜索会更聪明。

尝试的解决方案

为了实现我想要的行为

1. 我将 geom @@ AREA 放入子选择中 -> 没有更改执行计划
2. 我创建了一个具有所需区域子集的临时 View -> 没有更改执行计划
3. 我创建了所需区域的临时表 -> 创建需要 4 到 6 秒，这让情况变得更糟。

顺便说一句，很抱歉没有发布实际查询:我想如果我发布了，我的老板真的会生我的气，而且我正在寻找更多的理论指导，以便有人解决我的实际查询。请询问您是否需要进一步说明

---

编辑

Richard 有一个很好的观点:您可以使用 width 语句实现 Query Planner 的预期行为。坏处是这个临时表(或 CTE)弄乱了 vname 索引，从而使查询在某些情况下不返回任何内容。

我能够通过使用 to_tsvector() 即时创建一个新的 vname 来解决这个问题，但这(太)昂贵了 - 每个查询大约 300 - 500 毫秒。

我的解决方案

我放弃了 vname 搜索并使用简单的 LIKE('%query_string%')(10-20 毫秒/查询)，但这在我给定的环境中速度很快。 YMMV.

Answer 1

tsvector 的统计处理有一些改进(我认为 PostGIS 也是如此，但我不使用它)。如果您有时间，可能值得再次尝试 9.1 版本，看看它对您有什么帮助。

但是，对于这个单个查询，您可能需要查看 WITH 构造。

http://www.postgresql.org/docs/8.4/static/queries-with.html

如果您将几何部分作为 WITH 子句，它将首先被评估(保证)，然后该结果集将被以下 SELECT 过滤。不过它最终可能会变慢，直到您尝试才会知道。

这可能是对 work_mem 的调整也会有所帮助 - 您可以在每个 session 中执行此操作(“SET work_mem = ...”)，但要小心将其设置得太高 - 并发查询会很快耗尽所有 RAM。

http://www.postgresql.org/docs/8.4/static/runtime-config-resource.html#RUNTIME-CONFIG-RESOURCE-MEMORY