From: Richard Huxton <dev@archonet.com>
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
Date: Mon, 08 Jul 2013 11:27:34 +0100
Message-ID: <51DA9416.70200@archonet.com> (raw)
In-Reply-To: <CAD4BGM0Z8uBvg4ypD7YaxBkSONRKjreiAbOE7cAw7nGMbKbOmA@mail.gmail.com>
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>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: dev@archonet.com, raduzugravu90@gmail.com
Subject: Re: How to properly index hstore tags column to faster search for keys
In-Reply-To: <51DA9416.70200@archonet.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox