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