[Maps-l] hstore on Query-to-map project

[Peter Körner]

> I moved with my project Query-to-map[1] to hstore-DB which was really easy, so I can now
> also show lit=yes[2]. ;-) As far as I know is no project active on the old tables, so we could shut off them?
Nice to see that this new database is of use.

> "drink:club-mate=yes" (rare key with commons value) works fine, also in a huge area, so I'm very happy ;-)
> But "amenity=baby_hatch" (commons key with rare value) and others doesn't work fine in a huge area, that is frustrating.
> Peter explains the reasons in his mail.
The query is twice fast when you use the regular column. This should be 
as fast as it was without the hstore. I'd suggest using the regular 
columns where possible and only use the hstore if there is no column for 
the key you are looking for.


osm_mapnik=> explain analyze select tags from planet_point where (tags ? 
'amenity') and (tags->'amenity' = 'baby_hatch');
                                                                   QUERY 
PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------
  Bitmap Heap Scan on planet_point  (cost=1660.92..72555.72 rows=103 
width=198) (actual time=21259.021..100168.469 rows=55 loops=1)
    Recheck Cond: (tags ? 'amenity'::text)
    Filter: ((tags -> 'amenity'::text) = 'baby_hatch'::text)
    ->  Bitmap Index Scan on planet_point_tags  (cost=0.00..1660.89 
rows=20501 width=0) (actual time=14650.118..14650.118 rows=1920725 loops=1)
          Index Cond: (tags ? 'amenity'::text)
  Total runtime: 100168.620 ms
(6 rows)



osm_mapnik=> explain analyze select tags from planet_point where amenity 
= 'baby_hatch';
                                                       QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
  Seq Scan on planet_point  (cost=0.00..957754.05 rows=45012 width=198) 
(actual time=62.098..58970.003 rows=55 loops=1)
    Filter: (amenity = 'baby_hatch'::text)
  Total runtime: 58970.149 ms
(3 rows)

> Or could we use a second alternated hstore?  This should work and there are a lot of use-cases.
I dont think you can the indexes on both hstores in one query, so either 
the key-lookup is slow or the value-lookup is.

Another thing is, that so, in the audience on our lightning talk at 
fossgis said, that the index efficiency drops noticeable with an 
inhomogeneous value distribution and you'd get as lot of "yes"es or 
"no"s for a value-index.

> Independent from that I want to ask if I can get an index on "name" and "ref", because I use this often to link on objects from
> featurelist.
I think we can do this:
CREATE INDEX planet_point_name ON planet_point (name);
CREATE INDEX planet_point_ref ON planet_point (ref);

CREATE INDEX planet_line_name ON planet_line (name);
CREATE INDEX planet_line_ref ON planet_line (ref);

CREATE INDEX planet_polygon_name ON planet_polygon (name);
CREATE INDEX planet_polygon_ref ON planet_polygon (ref);

CREATE INDEX planet_roads_name ON planet_roads (name);
CREATE INDEX planet_roads_ref ON planet_roads (ref);

I'd like to wait until the diff import has catched up. I think this will 
take not more than one day.

Peter