[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