6

Yet another intersection issue. I'm working with OSM database in PostGIS. Now trying to count all buildings that fall into a countries bounding box.

This is my query (bbox for Germany).

SELECT
  COUNT(osm_id) AS num
FROM
  planet_osm_polygon
WHERE
  tags->'building' IS NOT NULL AND
  way && ST_Transform(ST_MakeEnvelope(12.8719,52.3700,13.9382,52.6646, 4326), 3857)

No matter whether I filter for buildings or not, the query is slow. My expectation would be below 10s but reality is about 100 seconds.

This is my query plan:

Aggregate  (cost=500537.88..500537.89 rows=1 width=8)
 -> Bitmap Heap Scan on planet_osm_polygon  (cost=7691.95..500254.94 rows=113173 width=8)
    Recheck Cond: (way && '0103000020110F0000010000000500000076B9815A3DDD354137FCF32B5A325A4176B9815A3DDD3541133B4058FB665A410E689A53E9AC3741133B4058FB665A410E689A53E9AC374137FCF32B5A325A4176B9815A3DDD354137FCF32B5A325A41'::geometry)
    Filter: ((tags -> 'building'::text) IS NOT NULL)
     -> Bitmap Index Scan on planet_osm_polygon_index  (cost=0.00..7663.66 rows=133748 width=0)
        Index Cond: (way && '0103000020110F0000010000000500000076B9815A3DDD354137FCF32B5A325A4176B9815A3DDD3541133B4058FB665A410E689A53E9AC3741133B4058FB665A410E689A53E9AC374137FCF32B5A325A4176B9815A3DDD354137FCF32B5A325A41'::geometry)

Eventually this should run against another table with 200+ country polygons.

  • 1
    do you have indexes on way and tags? – Ian Turton Mar 15 '18 at 10:26
  • Would it make difference to build the envelope directly from EPSG:3857 coordinates? @IanTurton, tags seem to be in a hstore field so they are automatically indexed. – user30184 Mar 15 '18 at 10:32
  • 3
    try running VACUUM ANALYZE on the table; updating the table stats might get the planner to avoid bitmap scans. check the plan afterwards and see if it uses index (only) scan. – ThingumaBob Mar 15 '18 at 11:54
  • 1
    Someone asked a similar question, see this answer. You probably need a GIN index on tags. – John Powell Mar 15 '18 at 12:15
  • 2
    glad it helped! for reference: the VACUUM cleans the table space/cluster of dead data tuples, effectively reclaiming that space (for that table, see the docs for options like VACUUM FULL) and is initially not that important. the ANALYZE however creates/refreshes the internal table statistics, mainly for the planner to work it's magic properly, and hence is crucial for performance, also on new tables. you can run both commands together (VACUUM ANALYZE table) and should when the table gets updates/inserts/deletes. – ThingumaBob Mar 15 '18 at 19:09
1

The comments already seemed to help, meaning vacuum analyze should do in the most situations. Nevertheless,here is a method that reduced the runtime of the query by factor 2:

create table buildings as 
select osm_id,building,way from planet_osm_polygon 
where building is not null;
create index on buildings using gist(way);
\timing
Timing is on.
select count(osm_id) as num from planet_osm_polygon 
where way && st_makeenvelope(680000.0,6400000.0,710000.0,6490000.0,3857)
and building is not null;
 num  
------
 8102
(1 line)
Time: 12,594 ms
select count(osm_id) from buildings where 
way && st_makeenvelope(680000.0,6400000.0,710000.0,6490000.0,3857);
 num  
------
 8102
(1 line)
Time: 6,102 ms

Here is a query plan for the first query:

 Aggregate  (cost=6660.68..6660.69 rows=1 width=8)
   ->  Bitmap Heap Scan on planet_osm_polygon  (cost=1086.09..6636.39 rows=9715 width=8)
         Recheck Cond: (way && '0103000020110F000001000000050000000000000080C0244100000000006A58410000000080C0244100000000E4C1584100000000E0AA254100000000E4C1584100000000E0AA254100000000006A58410000000080C0244100000000006A5841'::geometry)
         Filter: (building IS NOT NULL)
         ->  Bitmap Index Scan on planet_osm_polygon_index  (cost=0.00..1083.66 rows=13784 width=0)
               Index Cond: (way && '0103000020110F000001000000050000000000000080C0244100000000006A58410000000080C0244100000000E4C1584100000000E0AA254100000000E4C1584100000000E0AA254100000000006A58410000000080C0244100000000006A5841'::geometry)

Here is a query plan for the second query:

 Aggregate  (cost=3037.33..3037.34 rows=1 width=8)
   ->  Bitmap Heap Scan on building  (cost=287.66..3016.88 rows=8178 width=8)
         Recheck Cond: (way && '0103000020110F000001000000050000000000000080C0244100000000006A58410000000080C0244100000000E4C1584100000000E0AA254100000000E4C1584100000000E0AA254100000000006A58410000000080C0244100000000006A5841'::geometry)
         ->  Bitmap Index Scan on building_way_idx  (cost=0.00..285.61 rows=8178 width=0)
               Index Cond: (way && '0103000020110F000001000000050000000000000080C0244100000000006A58410000000080C0244100000000E4C1584100000000E0AA254100000000E4C1584100000000E0AA254100000000006A58410000000080C0244100000000006A5841'::geometry)

As you can see, the trick is to preprocess the data as much as possible to gain query performance and force the query planner to do exactly what you want. In this case, this skipped the 'building is not null' filter condition. Furthermore, the estimated costs for the aggregate and bitmap heap scans halved.

For testing, I just used data from Luxembourg, whereas my bbox was smaller than the extents of Luxembourg, therefore, the results for larger datasets might differ.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.