From: | Richard Huxton <dev(at)archonet(dot)com> |
---|---|
To: | Radu-Stefan Zugravu <raduzugravu90(at)gmail(dot)com> |
Cc: | pgsql-performance(at)postgresql(dot)org |
Subject: | Re: How to properly index hstore tags column to faster search for keys |
Date: | 2013-07-08 07:44:41 |
Message-ID: | 51DA6DE9.4030309@archonet.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
On 07/07/13 08:28, Radu-Stefan Zugravu wrote:
> 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}';
> 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?
Your index definition looks OK. Try showing the output of EXPLAIN
ANALYSE for your query - that way we'll see if the index is being used.
You can always paste explain output to: http://explain.depesz.com/ if
it's too long for the email.
--
Richard Huxton
Archonet Ltd
From | Date | Subject | |
---|---|---|---|
Next Message | Radu-Stefan Zugravu | 2013-07-08 08:31:02 | Re: How to properly index hstore tags column to faster search for keys |
Previous Message | Radu-Stefan Zugravu | 2013-07-07 07:28:11 | How to properly index hstore tags column to faster search for keys |