如何正确索引hstore标记列以更快地搜索密钥 [英] How to properly index hstore tags column to faster search for keys

问题描述

我将一大块OpenStreetMap的planet.osm文件导入到postgresql数据库中。该数据库包含一个名为nodes的表。每个节点都有一个名为geom的几何列和一个名为tags的hstore列。我需要沿着标签列中具有某些键的行提取节点。为此，我使用以下查询：

Hi,

I imported a large area of OpenStreetMap's planet.osm file into a postgresql database. The database contains a table called nodes. Each node has a geometry column called geom and a hstore column called tags. I need to extract nodes along a line that have certain keys in the tags column. To do that I use the following query:

SELECT id, tags  
FROM nodes  
WHERE ST_DWithin(nodes.geom, ST_MakeLine('{$geom1}', '{$geom2}'), 0.001)  
AND tags ? '{$type}';

$ geom1和$ geom2是我行的起点和终点的几何。

$ type变量包含我想要搜索的键。现在，它可以具有以下值之一：'历史'或'旅游'。



上面给出的查询有效，但速度太慢。我想在标签列中搜索键需要花费太多时间。我读了GIN和GIST索引，并使用以下查询生成了GIN索引：

$geom1 and $geom2 are geometries for start and end points of my line.
The $type variable contains the key I want to search for. Now, it can have one of the following values: 'historic' or 'tourist'.

The query given above works but it is too slow. I guess searching for a key in tags column takes too much time. I read about GIN and GIST indexes and I generated a GIN index using the following query:

CREATE INDEX nodes_tags_idx ON nodes USING GIN(tags);

创建索引后，我使用相同的第一个查询再次搜索节点，但性能没有变化。



如何正确使用GIN和GIST索引标签列，以便我可以更快地搜索标签列中具有特定键的节点？



谢谢

After creating the index I searched again for nodes using the same first query but there is no change in performance.

How can I properly use GIN and GIST to index tags column so I can faster search for nodes that have a certain key in tags column?

Thank you

推荐答案

geom1}'，' {

geom1}', '{

geom2}'）， 0 。 001 ）
AND 标签？ ' {

geom2}'), 0.001) AND tags ? '{

type}';

type}';

这篇关于如何正确索引hstore标记列以更快地搜索密钥的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！