From: | Radu-Stefan Zugravu <raduzugravu90(at)gmail(dot)com> |
---|---|
To: | Richard Huxton <dev(at)archonet(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 08:31:02 |
Message-ID: | CAD4BGM0GYXZp0Vr9m=+MKssE0=M-6kVBTYUrbTyz3m0cM+HmAg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
Hi,
Thank you for your answer.
My EXPLAIN ANALYZE output can be found here: http://explain.depesz.com/s/Wbo
.
Also, there is a discution on this subject on dba.stackexchange.com:
http://dba.stackexchange.com/questions/45820/how-to-properly-index-hstore-tags-column-to-faster-search-for-keys
On Mon, Jul 8, 2013 at 10:44 AM, Richard Huxton <dev(at)archonet(dot)com> wrote:
> 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
>
--
Radu-Stefan Zugravu
0755 950 145
0760 903 464
raduzugravu90(at)gmail(dot)com
radu(dot)zugravu(at)yahoo(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | Richard Huxton | 2013-07-08 08:53:28 | Re: How to properly index hstore tags column to faster search for keys |
Previous Message | Richard Huxton | 2013-07-08 07:44:41 | Re: How to properly index hstore tags column to faster search for keys |