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

From: Radu-Stefan Zugravu <raduzugravu90(at)gmail(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: How to properly index hstore tags column to faster search for keys
Date: 2013-07-07 07:28:11
Message-ID: CAD4BGM1JbMiT8jYXSjg+zP+fN0kwgyZOKq46xzR=h9=GDpVAiA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Richard Huxton 2013-07-08 07:44:41 Re: How to properly index hstore tags column to faster search for keys
Previous Message Scott Barney 2013-07-05 14:50:47 Re: Dynamic queries in stored procedure