How to properly index hstore tags column to faster search for keys

Поиск
Список
Период
Сортировка
От Radu-Stefan Zugravu
Тема How to properly index hstore tags column to faster search for keys
Дата
Msg-id CAD4BGM1JbMiT8jYXSjg+zP+fN0kwgyZOKq46xzR=h9=GDpVAiA@mail.gmail.com
обсуждение исходный текст
Ответы Re: How to properly index hstore tags column to faster search for keys
Список pgsql-performance

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 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);

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,

Radu-Stefan

В списке pgsql-performance по дате отправления:

Предыдущее
От: Scott Barney
Дата:
Сообщение: Re: Dynamic queries in stored procedure
Следующее
От: Richard Huxton
Дата:
Сообщение: Re: How to properly index hstore tags column to faster search for keys