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: | Whole Thread | Raw Message | 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
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 |