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

[Paul Norman]

Bad plan: https://explain.depesz.com/s/S6gG
Good plan: https://explain.depesz.com/s/jm3H

On the server getting the bad plan, can you force it to the good plan by overwriting `SET enable_*` variables?

Can you get an EXPLAIN ANALYZE for just the good plan?

The bad plan is materializing the CTE. The documentation for 14 and 15 states

> However, if a WITH query is non-recursive and side-effect-free (that is, it is a SELECT containing no volatile functions) then it can be folded into the parent query, allowing joint optimization of the two query levels. By default, this happens if the parent query references the WITH query just once, but not if it references the WITH query more than once.

I guess it can still materialize a CTE just like it can materialize a query plan node if it's required for the plan.

Both plans are estimating 20 million rows. This sounds very off, as we're doing batches of 1000, and indicates the problem is likely statistics-related.

The HashAggregate node statistics look reasonable as it is <1000. Reality is probably higher than 215 if places are randomly selected.

The estimate of 9.6 million rows for the good plan's bitmap index scan seems high.

I would expect PostgreSQL is already tracking the univariate statistics for `planet_osm_index_bucket(nodes)`. In case it's not, can you try `CREATE STATISTICS planet_osm_ways_bucket_stats ( planet_osm_index_bucket(nodes) ) FROM planet_osm_ways` and see if that changes the behavior?

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

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