[Tile-serving] [openstreetmap/osm2pgsql] Problem with query to get changed parent objects (Issue #2056)

[Sarah Hoffmann]

An EXPLAIN ANALYSE is naturally not possible short of waiting for a couple of hours.

Bad plan:
```
nominatim=# EXPLAIN                
WITH changed_buckets AS (
      SELECT array_agg(id) AS node_ids, id >> 5 AS bucket
      FROM (SELECT osm_id as id FROM place LIMIT 1000) as x GROUP BY id >> 5)
      SELECT DISTINCT w.id
      FROM "public"."planet_osm_ways" w, changed_buckets b
      WHERE w.nodes && b.node_ids
      AND planet_osm_index_bucket(w.nodes) && (ARRAY[b.bucket])::bigint[];
                                                           QUERY PLAN                                                            
---------------------------------------------------------------------------------------------------------------------------------
 Unique  (cost=46.34..55985610375.59 rows=20752577 width=8)
   ->  Nested Loop  (cost=46.34..55985558494.15 rows=20752577 width=8)
         Join Filter: ((w.nodes && (array_agg(x.id))) AND (planet_osm_index_bucket(w.nodes) && ARRAY[((x.id >> 5))]))
         ->  Index Scan using planet_osm_ways_pkey on planet_osm_ways w  (cost=0.57..472413790.47 rows=965236160 width=108)
         ->  Materialize  (cost=45.77..52.22 rows=215 width=40)
               ->  HashAggregate  (cost=45.77..48.99 rows=215 width=40)
                     Group Key: (x.id >> 5)
                     ->  Subquery Scan on x  (cost=0.57..40.77 rows=1000 width=16)
                           ->  Limit  (cost=0.57..28.27 rows=1000 width=8)
                                 ->  Index Only Scan using place_id_idx on place  (cost=0.57..9085193.42 rows=328039168 width=8)
(10 rows)
```

Good plan:
```
nominatim=# EXPLAIN                                            
WITH changed_buckets AS (
      SELECT array_agg(id) AS node_ids, id >> 5 AS bucket
      FROM (SELECT osm_id as id FROM place LIMIT 1000) as x GROUP BY id >> 5)
      SELECT DISTINCT w.id
      FROM "public"."planet_osm_ways" w, changed_buckets b
      WHERE w.nodes && b.node_ids
      AND planet_osm_index_bucket(w.nodes) && (ARRAY[b.bucket])::bigint[];
                                                           QUERY PLAN                                                            
---------------------------------------------------------------------------------------------------------------------------------
 Unique  (cost=59521649340.32..59521753103.21 rows=20752577 width=8)
   ->  Sort  (cost=59521649340.32..59521701221.77 rows=20752577 width=8)
         Sort Key: w.id
         ->  Nested Loop  (cost=72489.62..59518154417.96 rows=20752577 width=8)
               ->  HashAggregate  (cost=45.77..48.99 rows=215 width=40)
                     Group Key: (x.id >> 5)
                     ->  Subquery Scan on x  (cost=0.57..40.77 rows=1000 width=16)
                           ->  Limit  (cost=0.57..28.27 rows=1000 width=8)
                                 ->  Index Only Scan using place_id_idx on place  (cost=0.57..9085193.42 rows=328039168 width=8)
               ->  Bitmap Heap Scan on planet_osm_ways w  (cost=72443.86..276827659.72 rows=96524 width=108)
                     Recheck Cond: (planet_osm_index_bucket(nodes) && ARRAY[((x.id >> 5))])
                     Filter: (nodes && (array_agg(x.id)))
                     ->  Bitmap Index Scan on planet_osm_ways_nodes_bucket_idx  (cost=0.00..72419.73 rows=9652362 width=0)
                           Index Cond: (planet_osm_index_bucket(nodes) && ARRAY[((x.id >> 5))])
(14 rows)
```

-- 
Reply to this email directly or view it on GitHub:
https://github.com/openstreetmap/osm2pgsql/issues/2056#issuecomment-1693889204
You are receiving this because you are subscribed to this thread.

Message ID: <openstreetmap/osm2pgsql/issues/2056/1693889204 at github.com>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.openstreetmap.org/pipermail/tile-serving/attachments/20230825/8cf8d7bf/attachment.htm>