Page MenuHomePhabricator

PostgreSQL query planner bug on labsdb1006
Closed, ResolvedPublic

Description

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).