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

下面的语句有一个非常严重的性能问题，我无法自己修复。

特定情况下

我有postgres 8.4数据库与Postgis 1.4安装
我有一个包含约900万个条目的地理空间表。这个表有一个(postgis) geometry列和一个tsvector列
我有一个几何上的GIST索引和VNAME列上的VNAME索引
表为ANALYZE 'd

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

要搜索的区域将把900万数据集限制在大约10万，并且该区域内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搜索。

<<p> 试图解决方案/strong>
为了实现期望的行为，我尝试
I Put the geom @@ AREA into a subselect ->没有更改执行计划
我创建了一个具有所需区域子集的临时视图->没有更改执行计划
我创建了一个所需区域的临时表->需要4~6秒来创建，所以这使得它更糟。
顺便说一句，抱歉没有发布实际的查询:我想我的老板真的会对我生气，如果我这样做了，我也在寻找更多的理论指针，有人来修复我的实际查询。请询问是否需要进一步澄清
编辑
Richard有一个非常好的观点:您可以使用width语句实现查询计划器的预期行为。不好的是，这个临时表(或CTE)混淆了vname索引，因此在某些情况下使查询什么也不返回。
我能够通过使用to_tsvector()动态创建一个新的vname来解决这个问题，但是这(太)昂贵了-每个查询大约300 - 500ms。
我的解决方案
我放弃了vname搜索，使用了一个简单的LIKE('%query_string%')(10-20毫秒/查询)，但这只在我给定的环境中是快速的。YMMV .

在tsvector的统计处理方面有一些改进(我认为PostGIS也是，但我不使用它)。如果你有时间，也许值得在9.1版本中再试一次，看看它能为你做些什么。

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

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

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

对work_mem进行调整可能也会有所帮助-您可以在每个会话中进行此设置("SET work_mem =…")，但要注意设置得太高-并发查询会很快耗尽所有内存。

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

相关内容

最新更新

热门标签：