From: idc danny <idcdanny@yahoo.com>
To: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: How to properly index hstore tags column to faster search for keys
Date: Mon, 8 Jul 2013 05:16:19 -0700 (PDT)
Message-ID: <1373285779.16379.YahooMailNeo@web163105.mail.bf1.yahoo.com> (raw)
In-Reply-To: <CAD4BGM2=SL8+Z2fkgKqBMtKOnO=TxB6Frd=b=FVnfxXh5PNX1w@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>
<51DA9416.70200@archonet.com>
<CAD4BGM2=SL8+Z2fkgKqBMtKOnO=TxB6Frd=b=FVnfxXh5PNX1w@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
Hi Stefan
1 - If you have a fixed data that does not change a lot, like I assume is your fixed 'map' try implementing in your app the hashtrie method. This looks as better approach as your query is quite fast. Usually I am starting to query my queries (or the query planner) when they start to take more the 2 seconds. The fact that you continuously call it for your next node it might not be the best approach.
2 - As mentioned by Richard, try either to delete the nodes that does not belong to "historic" / "tourist" or simply split the table in 2. One that have only them and the rest to the other table. Assuming this will not change a lot the other already implemented queries in your app (because you'll have to make a 1-to-1 now) it might save your day.
Danny
________________________________
From: Radu-Stefan Zugravu <raduzugravu90@gmail.com>
To: Richard Huxton <dev@archonet.com>
Cc: pgsql-performance@postgresql.org
Sent: Monday, July 8, 2013 2:01 PM
Subject: Re: [PERFORM] How to properly index hstore tags column to faster search for keys
I do call the query for each neighbour node to find which one is better in building my path.I think I will try the first way you mentioned. I also found some references using BTREE indexes:
CREATE INDEX nodes_tags_btree_historic_idx on nodes USING BTREE ((tags ? 'historic'));
CREATE INDEX nodes_tags_btree_tourist_idx on nodes USING BTREE ((tags ? 'tourist));
Do you think this could make a difference?
On Mon, Jul 8, 2013 at 1:27 PM, Richard Huxton <dev@archonet.com> wrote:
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
>
--
Radu-Stefan Zugravu0755 950 145
0760 903 464
raduzugravu90@gmail.com
radu.zugravu@yahoo.com
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: idcdanny@yahoo.com
Subject: Re: How to properly index hstore tags column to faster search for keys
In-Reply-To: <1373285779.16379.YahooMailNeo@web163105.mail.bf1.yahoo.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