[OSM-dev] [Talk-us] tools for analysis of road networks?

Paul Norman penorman at mac.com
Wed Dec 5 22:07:49 GMT 2012

(cc'ing dev@ instead)


hstore supports GiST and GIN indexes and these support only the @>, ?, ?&
and ?| operators. ?& and ?| are essentially equivalent to hstore ? 'a' AND
hstore ? 'b' or the same with OR.


If there is some hstore value that you use a lot for complicated queries
where you need to use the -> operator it's possible to index that


I have some more notes in pgsnapshot indexes that I should do up into a blog
post or an email  but it is frequently worth doing EXPLAIN on queries to see
if it's using a sane plan.


From: Ian Dees [mailto:ian.dees at gmail.com] 
Sent: Wednesday, December 05, 2012 10:39 AM
To: Martijn van Exel
Cc: Paul Norman; Richard Welty; the Old Topo Depot;
talk-us at openstreetmap.org Openstreetmap
Subject: Re: [Talk-us] tools for analysis of road networks?


These are the sorts of queries that jxapi does all the time.


hstore's @> and ? operators use index, others don't.


As far as metrics... an xapi query like
[highway=*][ref|name=*][bbox=a,b,c,d] on jxapi was taking ~5-10 hours when
it was using "exists(tags, 'highway')" (which doesn't use the index on
hstore) while the exact same xapi query using the "tags ? 'highway'" takes
about 20 minutes.


On Wed, Dec 5, 2012 at 12:23 PM, Martijn van Exel <m at rtijn.org> wrote:

Interesting, I have been looking for a way to speed up those types of
queries for a while, thanks!


Do you have metrics on this?


On Wed, Dec 5, 2012 at 10:53 AM, Paul Norman <penorman at mac.com> wrote:

Something else is that tags->'highway' in ( 'motorway', 'primary',
'secondary' ) will NOT use indexes on tags.


tags @> hstore('highway', 'motorway') OR tags @> hstore('highway',
'primary') OR tags @> hstore('highway', 'secondary') should be significantly
faster with proper indexes.


Martijn van Exel

Talk-us mailing list
Talk-us at openstreetmap.org


-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.openstreetmap.org/pipermail/dev/attachments/20121205/60dc4942/attachment.html>

More information about the dev mailing list