[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
specifically.

 

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
http://oegeo.wordpress.com/
http://openstreetmap.us/


_______________________________________________
Talk-us mailing list
Talk-us at openstreetmap.org
http://lists.openstreetmap.org/listinfo/talk-us

 

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