(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.d...@gmail.com]
Sent: Wednesday, December 05, 2012 10:39 AM
To: Martijn van Exel
Cc: Paul Norman; Richard Welty; the Old Topo Depot;
talk...@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...@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 penor...@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...@openstreetmap.org
http://lists.openstreetmap.org/listinfo/talk-us
___
dev mailing list
dev@openstreetmap.org
http://lists.openstreetmap.org/listinfo/dev
