Received: from malur.postgresql.org ([217.196.149.56])
 by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from
 <pgsql-performance-owner+M50157=pgsql-performance-ar@postgresql.org>)
 id 1Uw8fU-0005ZC-LI
 for pgsql-performance@arkaria.postgresql.org; Mon, 08 Jul 2013 10:27:40 +0000
Received: from localhost ([127.0.0.1] helo=postgresql.org)
 by malur.postgresql.org with smtp (Exim 4.80) (envelope-from
 <pgsql-performance-owner+M50157=pgsql-performance-ar@postgresql.org>)
 id 1Uw8fT-0002cq-UQ
 for pgsql-performance@arkaria.postgresql.org; Mon, 08 Jul 2013 10:27:39 +0000
Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29])
 by malur.postgresql.org with esmtp (Exim 4.80)
 (envelope-from <dev@archonet.com>) id 1Uw8fS-0002cd-TV
 for pgsql-performance@postgresql.org; Mon, 08 Jul 2013 10:27:38 +0000
Received: from archon2012.plus.com ([80.229.41.160] helo=mail.archonet.com)
 by magus.postgresql.org with esmtp (Exim 4.80)
 (envelope-from <dev@archonet.com>) id 1Uw8fP-0003Y5-T2
 for pgsql-performance@postgresql.org; Mon, 08 Jul 2013 10:27:38 +0000
Received: from [192.168.1.13] (ariel.office.archonet.com [192.168.1.13])
 by mail.archonet.com (Postfix) with ESMTP id DDD562C44DB;
 Mon,  8 Jul 2013 11:27:34 +0100 (BST)
Message-ID: <51DA9416.70200@archonet.com>
Date: Mon, 08 Jul 2013 11:27:34 +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>
 <51DA6DE9.4030309@archonet.com>
 <CAD4BGM0GYXZp0Vr9m=+MKssE0=M-6kVBTYUrbTyz3m0cM+HmAg@mail.gmail.com>
 <51DA7E08.6090007@archonet.com>
 <CAD4BGM0Z8uBvg4ypD7YaxBkSONRKjreiAbOE7cAw7nGMbKbOmA@mail.gmail.com>
In-Reply-To: 
 <CAD4BGM0Z8uBvg4ypD7YaxBkSONRKjreiAbOE7cAw7nGMbKbOmA@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Pg-Spam-Score: -1.9 (-)
List-Archive: <http://archives.postgresql.org/pgsql-performance>
List-Help: <mailto:majordomo@postgresql.org?body=help>
List-ID: <pgsql-performance.postgresql.org>
List-Owner: <mailto:pgsql-performance-owner@postgresql.org>
List-Post: <mailto:pgsql-performance@postgresql.org>
List-Subscribe: <mailto:majordomo@postgresql.org?body=sub%20pgsql-performance>
List-Unsubscribe: 
 <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
X-Mailing-List: pgsql-performance
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

On 08/07/13 10:20, Radu-Stefan Zugravu wrote:
> Any improvement is welcomed. The overall performance of the application
> is not very good. It takes about 200 seconds to compute a path for not
> so far star and end points.

So you have to call this query 1000 times with different start and end 
points?

 > I want to improve this query as much as I can.

There's only two ways I can see to get this much below 20ms. This will 
only work if you want a very restricted range of tags.

Drop the tag index and create multiple geometry indexes instead:

CREATE INDEX node_geo_tourist_idx <index details> WHERE tags ? 'tourist';
CREATE INDEX node_geo_tourist_idx <index details> WHERE tags ? 'history';
etc.

This will only work if you have a literal WHERE clause that checks the 
tag. It should be fast though.


The second way would be to delete all the nodes that aren't tagged 
tourist or history. That assumes you are never interested in them of course.

> How exactly should I post the explain without the index? Do I have to
> drop all created indexes for the tags column? It takes some time to
> create them back.

Not important - I was just curious.

-- 
   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