gis=> EXPLAIN SELECT osm_id, name, tags->'wikipedia' wikipedia, tags->'wikidata' wikidata FROM planet_osm_polygon WHERE ( tags ? 'wikipedia' OR tags ? 'wikidata' ) AND osm_id > 1000 ORDER BY osm_id LIMIT 1000; QUERY PLAN ---------------------------------------------------------------------------------------------------------------------- Limit (cost=0.00..897216.11 rows=1000 width=54) -> Index Scan using planet_osm_polygon_pkey on planet_osm_polygon (cost=0.00..586654620.86 rows=653861 width=54) Index Cond: (osm_id > 1000) Filter: ((tags ? 'wikipedia'::text) OR (tags ? 'wikidata'::text)) (4 rows) gis=> EXPLAIN SELECT osm_id, name, tags->'wikipedia' wikipedia, tags->'wikidata' wikidata FROM planet_osm_point WHERE ( tags ? 'wikipedia' OR tags ? 'wikidata' ) AND osm_id > 1000 ORDER BY osm_id LIMIT 1000; QUERY PLAN ----------------------------------------------------------------------------------------------------------------- Limit (cost=0.00..69225.62 rows=1000 width=99) -> Index Scan using planet_osm_point_pkey on planet_osm_point (cost=0.00..11418005.18 rows=164939 width=99) Index Cond: (osm_id > 1000) Filter: ((tags ? 'wikipedia'::text) OR (tags ? 'wikidata'::text)) (4 rows)
These two look sane and execute fast.
gis=> EXPLAIN SELECT osm_id, name, tags->'wikipedia' wikipedia, tags->'wikidata' wikidata FROM planet_osm_line WHERE ( tags ? 'wikipedia' OR tags ? 'wikidata' ) AND osm_id > 1000 ORDER BY osm_id LIMIT 1000; QUERY PLAN -------------------------------------------------------------------------------------------------------------- Limit (cost=1005472.07..1005474.57 rows=1000 width=75) -> Sort (cost=1005472.07..1006091.17 rows=247639 width=75) Sort Key: osm_id -> Bitmap Heap Scan on planet_osm_line (cost=110937.36..991894.29 rows=247639 width=75) Recheck Cond: ((tags ? 'wikipedia'::text) OR (tags ? 'wikidata'::text)) Filter: (osm_id > 1000) -> BitmapOr (cost=110937.36..110937.36 rows=254606 width=0) -> Bitmap Index Scan on planet_osm_line_tags (cost=0.00..55406.77 rows=127303 width=0) Index Cond: (tags ? 'wikipedia'::text) -> Bitmap Index Scan on planet_osm_line_tags (cost=0.00..55406.77 rows=127303 width=0) Index Cond: (tags ? 'wikidata'::text) (11 rows)
This one is atrociously slow, however. This does not happen on production (maps*) databases (that have newer PG anyway).