Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1UwAMr-0003iL-V9 for pgsql-performance@arkaria.postgresql.org; Mon, 08 Jul 2013 12:16:34 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1UwAMr-0003wA-CV for pgsql-performance@arkaria.postgresql.org; Mon, 08 Jul 2013 12:16:33 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1UwAMq-0003vn-Dz for pgsql-performance@postgresql.org; Mon, 08 Jul 2013 12:16:32 +0000 Received: from nm6.bullet.mail.bf1.yahoo.com ([98.139.212.165]) by magus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1UwAMg-0005XC-0U for pgsql-performance@postgresql.org; Mon, 08 Jul 2013 12:16:29 +0000 Received: from [98.139.212.152] by nm6.bullet.mail.bf1.yahoo.com with NNFMP; 08 Jul 2013 12:16:20 -0000 Received: from [98.139.212.206] by tm9.bullet.mail.bf1.yahoo.com with NNFMP; 08 Jul 2013 12:16:20 -0000 Received: from [127.0.0.1] by omp1015.mail.bf1.yahoo.com with NNFMP; 08 Jul 2013 12:16:20 -0000 X-Yahoo-Newman-Property: ymail-3 X-Yahoo-Newman-Id: 39172.59210.bm@omp1015.mail.bf1.yahoo.com Received: (qmail 17404 invoked by uid 60001); 8 Jul 2013 12:16:19 -0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s1024; t=1373285779; bh=ZNpABP7UtbJpk/WO9qDdMFyKK8AY3vZ7GMFE4Um1MDQ=; h=X-YMail-OSG:Received:X-Rocket-MIMEInfo:X-Mailer:References:Message-ID:Date:From:Reply-To:Subject:To:In-Reply-To:MIME-Version:Content-Type; b=X8pHlJZL+nFDvKULfp8ddY4SeeSVQdvm5YQyIon7zw+qStIaPVaadpOhi7yIRqKQUTJDQcAj8W5IITOcC9sDSe26N02cfz7iZhfQG1dZPOLi4j/g5f1ocgOg1pOFVUmS8E44pbXnvQ5Hg5r4F6eJ92fkF3vWAxq3gOyTbD9mzBo= DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=X-YMail-OSG:Received:X-Rocket-MIMEInfo:X-Mailer:References:Message-ID:Date:From:Reply-To:Subject:To:In-Reply-To:MIME-Version:Content-Type; b=xK0paHmub9/VhKUNZqjlAbYn+/b4if2B4+WVxi1UoO7gvUk4IYTVGAz+M/JbAncNL+0tor+/BQQqEJ4mDecpOwCThTiNkHyJBZ4zXKDodX5hBlKMm/aiYuxgAwLb/cXeHFzbSq/P9vfTxJBpMzVM+i8vmM4N876BXM+em2aM+CI= ; X-YMail-OSG: NadI7q0VM1mUGjc1Hx1IiDRqzIY9AEr4uHwiDFmU9An.1Eq SNVGRXSr4tvQVxft8V1vZaMo36aZhB0vdenN050cXim9_d254oiDSlbNWfnP 7sz_kbQhsHM0PWkAhMwoOoP_8V65OgmsAbyT2GtJLFt3b7g3w_meneaafnFJ 5BYtNXU9vjglYcFZxSWZM61JOJTc1DdrK_6F0bVHZu5fLcKE2h2vcjzXaxsA wJjWE4kXhDddebp1Oi6.nh5l._UIKJShE5tCmxMlfL3Pe7P2.tWvk5Z2UBiI nHNQolargbMnda7iJ3eMPxA4N.2ykw69cKh4e1F.fVbGbT95YpYiU.Loy_av 5h6ZGlq5VBYckKvAay0BroY02w9Qkm3zf7x9_NDEsZftegimmPBl63seVURj AZLQTiigqODa8qhMYUWUUJ52Ea.znttRI9lCKD86D0sz_9FSHJ4iY4dWHyXz VQEdaVNpe3VhMxE4rYL0_mZMzIKUz0GzezV1ewU8WT5S.OnaUZGLATm9wbr3 bi_kqxYFi8YoZ5R.f1OgAZZIA_J87x.SDkjdo6G4sKD7lhnpRDpWu9LdTMo. 1JrgO8AKQsqBlJa_lFP6TqO0lfxM- Received: from [46.214.227.93] by web163105.mail.bf1.yahoo.com via HTTP; Mon, 08 Jul 2013 05:16:19 PDT X-Rocket-MIMEInfo: 002.001, SGkgU3RlZmFuCjEgLSBJZiB5b3UgaGF2ZSBhIGZpeGVkIGRhdGEgdGhhdCBkb2VzIG5vdCBjaGFuZ2UgYSBsb3QsIGxpa2UgSSBhc3N1bWUgaXMgeW91ciBmaXhlZCAnbWFwJyB0cnkgaW1wbGVtZW50aW5nIGluIHlvdXIgYXBwIHRoZSBoYXNodHJpZSBtZXRob2QuIFRoaXMgbG9va3MgYXMgYmV0dGVyIGFwcHJvYWNoIGFzIHlvdXIgcXVlcnkgaXMgcXVpdGUgZmFzdC4gVXN1YWxseSBJIGFtIHN0YXJ0aW5nIHRvIHF1ZXJ5IG15IHF1ZXJpZXMgKG9yIHRoZSBxdWVyeSBwbGFubmVyKSB3aGVuIHRoZXkgc3RhcnQBMAEBAQE- X-Mailer: YahooMailWebService/0.8.148.557 References: <51DA6DE9.4030309@archonet.com> <51DA7E08.6090007@archonet.com> <51DA9416.70200@archonet.com> Message-ID: <1373285779.16379.YahooMailNeo@web163105.mail.bf1.yahoo.com> Date: Mon, 8 Jul 2013 05:16:19 -0700 (PDT) From: idc danny Reply-To: idc danny Subject: Re: How to properly index hstore tags column to faster search for keys To: "pgsql-performance@postgresql.org" In-Reply-To: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="196471698-1917610862-1373285779=:16379" X-Pg-Spam-Score: -1.3 (-) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org --196471698-1917610862-1373285779=:16379 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable Hi Stefan=0A1 - 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 metho= d. 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 n= ode it might not be the best approach.=0A2 - As mentioned by Richard, try e= ither 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 o= ther table. Assuming this will not change a lot the other already implement= ed queries in your app (because you'll have to make a 1-to-1 now) it might = save your day.=0ADanny=0A=0A=0A________________________________=0A From: Ra= du-Stefan Zugravu =0ATo: Richard Huxton =0ACc: pgsql-performance@postgresql.org =0ASent: Monday, July 8, 2= 013 2:01 PM=0ASubject: Re: [PERFORM] How to properly index hstore tags colu= mn to faster search for keys=0A =0A=0A=0AI do call the query for each neigh= bour node to find which one is better in building my path.I think I will tr= y the first way you mentioned. I also found some references using BTREE ind= exes:=0A=0ACREATE INDEX nodes_tags_btree_historic_idx on nodes USING BTREE = ((tags ? 'historic'));=0ACREATE INDEX nodes_tags_btree_tourist_idx on nodes= USING BTREE ((tags ? 'tourist));=0A=0A=0ADo you think this could make a di= fference?=0A=0A=0A=0AOn Mon, Jul 8, 2013 at 1:27 PM, Richard Huxton wrote:=0A=0AOn 08/07/13 10:20, Radu-Stefan Zugravu wrote:=0A>= =0A>Any improvement is welcomed. The overall performance of the application= =0A>>is not very good. It takes about 200 seconds to compute a path for not= =0A>>so far star and end points.=0A>>=0A>=0ASo you have to call this query = 1000 times with different start and end points?=0A>=0A>=0A>> I want to impr= ove this query as much as I can.=0A>=0A>=0AThere's only two ways I can see = to get this much below 20ms. This will only work if you want a very restric= ted range of tags.=0A>=0A>Drop the tag index and create multiple geometry i= ndexes instead:=0A>=0A>CREATE INDEX node_geo_tourist_idx WH= ERE tags ? 'tourist';=0A>CREATE INDEX node_geo_tourist_idx = WHERE tags ? 'history';=0A>etc.=0A>=0A>This will only work if you have a li= teral WHERE clause that checks the tag. It should be fast though.=0A>=0A>= =0A>The second way would be to delete all the nodes that aren't tagged tour= ist or history. That assumes you are never interested in them of course.=0A= >=0A>=0A>=0A>How exactly should I post the explain without the index? Do I = have to=0A>>drop all created indexes for the tags column? It takes some tim= e to=0A>>create them back.=0A>>=0A>=0ANot important - I was just curious.= =0A>=0A>=0A>-- =0A>=A0 Richard Huxton=0A>=A0 Archonet Ltd=0A>=0A=0A=0A-- = =0A=0ARadu-Stefan Zugravu0755 950 145=0A0760 903 464=0Araduzugravu90@gmail.= com=0Aradu.zugravu@yahoo.com --196471698-1917610862-1373285779=:16379 Content-Type: text/html; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable
Hi Stefan<= /span>
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 meth= od. 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 tak= e more the 2 seconds. The fact that you continuously call it for your next = node it might not be the best approach.
2 - A= s 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 <raduzugrav= u90@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 inde= x hstore tags column to faster=0A search for keys

I do call the query for each neighbour node to find which one is better i= n building my path.
I think I will try the first way you mentioned. I = also found some references using BTREE indexes:

=0ACREATE INDEX node= s_tags_btree_historic_idx on nodes USING BTREE ((tags ? 'historic'));
C= REATE INDEX nodes_tags_btree_tourist_idx on nodes USING BTREE ((tags ? 'tou= rist));

Do you think this could make a difference?=0A


=0ASo you have to call this que= ry 1000 times with different start and end points?

=0A
=0A> I want to improve this query as much as I can.
= =0A
=0AThere'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.
=0A=0ADrop the tag index and create multiple geometry indexes instead:
= =0A
=0ACREATE INDEX node_geo_tourist_idx <index details> WHERE tag= s ? 'tourist';
=0ACREATE INDEX node_geo_tourist_idx <index details>= ; WHERE tags ? 'history';
=0Aetc.
=0A
=0AThis will only work if yo= u have a literal WHERE clause that checks the tag. It should be fast though= .
=0A
=0A
=0AThe 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.

=0A
=0A
=0AHow exactly should I post the explain with= out the index? Do I have to
=0Adrop all created indexes for the tags col= umn? It takes some time to
=0Acreate them back.
=0A
=0A
=0ANot important - I was just curious.

=0A
=0A--
=0A  Richard= Huxton
=0A  Archonet Ltd
=0A

=

--
Radu-Stefan Zugravu
0755 950 145
0760 903 464
=0Araduzugravu90@gmail.com
radu.zugravu@yahoo.com=0A


=
--196471698-1917610862-1373285779=:16379--