(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