Am 16.05.2010 12:15, schrieb Tim Alder:
> As far I know the hstore support no "like" command.
Well, you can do WHERE (tags->'name' LIKE 'A%') but I'm not sure if this
can take use of any index.
> So a column index
> would be nice to
> get e.g. all streets beginning "A..." in an area: "where name like 'A%'"
> or looking for something like
> "Amselweg" or "Amselstraße"...
> So perhaps line table would be better than the point table for testing.
We are updated with the main db now, so I added the two indexes on the
line table:
CREATE INDEX planet_line_name ON planet_line (name);
CREATE INDEX planet_line_ref ON planet_line (ref);
and did some test-queries (see textfile). of course the BitmapAnd is
much faster then the hstore like. As far as I understood we'll have to
add those indexes to the other tables as well, right?
Peter
osm_mapnik=> explain analyze select * from planet_line where (tags @>
'place=>city') and name like 'a%';
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on planet_line (cost=3521.71..3561.80 rows=10 width=2183)
(actual time=178.296..178.296 rows=0 loops=1)
Filter: ((tags @> '"place"=>"city"'::hstore) AND (name ~~ 'a%'::text))
-> BitmapAnd (cost=3521.71..3521.71 rows=10 width=0) (actual
time=178.294..178.294 rows=0 loops=1)
-> Bitmap Index Scan on planet_line_name (cost=0.00..246.21
rows=10132 width=0) (actual time=0.933..0.933 rows=3458 loops=1)
Index Cond: ((name >= 'a'::text) AND (name < 'b'::text))
-> Bitmap Index Scan on planet_line_tags (cost=0.00..3275.24
rows=34285 width=0) (actual time=176.983..176.983 rows=14234 loops=1)
Index Cond: (tags @> '"place"=>"city"'::hstore)
Total runtime: 178.349 ms
(8 rows)
osm_mapnik=> explain analyze select * from planet_line where (tags @>
'place=>city') and (tags->'name' like 'a%');
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on planet_line (cost=3275.28..127271.34 rows=171 width=2183)
(actual time=12685.296..12685.296 rows=0 loops=1)
Filter: ((tags @> '"place"=>"city"'::hstore) AND ((tags -> 'name'::text) ~~
'a%'::text))
-> Bitmap Index Scan on planet_line_tags (cost=0.00..3275.24 rows=34285
width=0) (actual time=5.961..5.961 rows=14234 loops=1)
Index Cond: (tags @> '"place"=>"city"'::hstore)
Total runtime: 12685.367 ms
(5 rows)
_______________________________________________
Maps-l mailing list
[email protected]
https://lists.wikimedia.org/mailman/listinfo/maps-l
