Message-ID: <51DA6DE9.4030309@archonet.com>
Date: Mon, 08 Jul 2013 08:44:41 +0100
From: Richard Huxton <dev@archonet.com>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
 rv:10.0.12) Gecko/20130116 Icedove/10.0.12
MIME-Version: 1.0
To: Radu-Stefan Zugravu <raduzugravu90@gmail.com>
CC: pgsql-performance@postgresql.org
Subject: Re: How to properly index hstore tags column to faster search for
 keys
References: 
 <CAD4BGM1JbMiT8jYXSjg+zP+fN0kwgyZOKq46xzR=h9=GDpVAiA@mail.gmail.com>
In-Reply-To: 
 <CAD4BGM1JbMiT8jYXSjg+zP+fN0kwgyZOKq46xzR=h9=GDpVAiA@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

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


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance