On 05/15/2010 10:52 PM, Peter Körner wrote:
>...
>
> 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.
There does seem to be another option that is much faster:
osm_mapnik=> EXPLAIN ANALYZE select count(*) from planet_point where
(tags @> 'amenity=>baby_hatch');
QUERY
PLAN
--------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=72509.57..72509.58 rows=1 width=0) (actual
time=163.902..163.902 rows=1 loops=1)
-> Bitmap Heap Scan on planet_point (cost=1666.01..72458.31
rows=20501 width=0) (actual time=163.286..163.872 rows=55 loops=1)
Filter: (tags @> '"amenity"=>"baby_hatch"'::hstore)
-> Bitmap Index Scan on planet_point_tags
(cost=0.00..1660.89 rows=20501 width=0) (actual time=163.250..163.250
rows=55 loops=1)
Index Cond: (tags @> '"amenity"=>"baby_hatch"'::hstore)
Total runtime: 163.986 ms
compared to
osm_mapnik=> EXPLAIN ANALYZE select count(*) from planet_point where
amenity='baby_hatch';
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=957866.58..957866.59 rows=1 width=0) (actual
time=11162.156..11162.156 rows=1 loops=1)
-> Seq Scan on planet_point (cost=0.00..957754.05 rows=45012
width=0) (actual time=78.598..11161.997 rows=55 loops=1)
Filter: (amenity = 'baby_hatch'::text)
Total runtime: 11162.236 ms
(4 rows)
They both seem to give the same result of 55 entries, although I haven't
looked at how the @> operator behaves in other cases.
>
> 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.
Can you not use the the osm_id to link to objects?
> 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
Kai
>
> _______________________________________________
> Maps-l mailing list
> [email protected]
> https://lists.wikimedia.org/mailman/listinfo/maps-l
_______________________________________________
Maps-l mailing list
[email protected]
https://lists.wikimedia.org/mailman/listinfo/maps-l
