BUG #16241: Degraded hash join performance - Mailing list pgsql-bugs

The following bug has been logged on the website:

Bug reference:      16241
Logged by:          Thomas Butz
Email address:      tbutz@optitool.de
PostgreSQL version: 12.1
Operating system:   Modified postgres:12 docker image (Debian Buster)
Description:

I'm currently analyzing a slowdown in the performance of our OSM tile
rendering toolchain. While the following query finishes quite fast with
PostgreSQL 11.4/Postgis 2.5 it takes a lot longer using PostgreSQL
12.1/Postgis 3.0:

SELECT ST_AsBinary("way") AS geom,"construction","highway","name","tunnel"
FROM (SELECT
            way,
            CASE WHEN substr(highway, length(highway)-4, 5) = '_link' THEN
substr(highway, 0, length(highway)-4) ELSE highway END,
            CASE WHEN (tunnel = 'yes' OR tunnel = 'building_passage' OR
covered = 'yes') THEN 'yes' ELSE 'no' END AS tunnel,
            construction,
            localized_streetname as name,
            CASE
              WHEN oneway IN ('yes', '-1') THEN oneway
              WHEN junction IN ('roundabout') AND (oneway IS NULL OR NOT
oneway IN ('no', 'reversible')) THEN 'yes'
              ELSE NULL
            END AS oneway,
            horse, bicycle
          FROM planet_osm_line l
          JOIN (VALUES -- this join is also putting a condition on what is
selected. features not matching it do not make it into the results.
              ('motorway', 380),
              ('trunk', 370),
              ('primary', 360),
              ('secondary', 350),
              ('tertiary', 340),
              ('residential', 330),
              ('unclassified', 330),
              ('road', 330),
              ('living_street', 320),
              ('pedestrian', 310),
              ('raceway', 300),
              ('motorway_link', 240),
              ('trunk_link', 230),
              ('primary_link', 220),
              ('secondary_link', 210),
              ('tertiary_link', 200),
              ('service', 150),
              ('construction', 10)
            ) AS ordertable (highway, prio)
            USING (highway)
          WHERE highway IN ('motorway', 'motorway_link', 'trunk',
'trunk_link', 'primary', 'primary_link', 'secondary', 'secondary_link',
'tertiary',
                            'tertiary_link', 'residential', 'unclassified',
'road', 'service', 'pedestrian', 'raceway', 'living_street',
'construction')
            AND (name IS NOT NULL
              OR oneway IN ('yes', '-1')
              OR junction IN ('roundabout'))
          ORDER BY
            z_order DESC, -- put important roads first
            COALESCE(layer, 0), -- put top layered roads first
            length(name) DESC, -- Try to fit big labels in first
            name DESC, -- Force a consistent ordering between differently
named streets
            l.osm_id DESC -- Force an ordering for streets of the same name,
e.g. dualized roads
        ) AS roads_text_name WHERE "way" &&
ST_SetSRID('BOX3D(1222380.956336539 6339381.37785938,1233387.888409604
6350388.309932444)'::box3d, 3857)

I've uploaded detailed EXPLAINE ANALYZE output for both scenarios:

PostgreSQL 11.4 (execution time: 140.5ms)
https://explain.depesz.com/s/BsO7

PostgreSQL 12.1 (execution time: 3394.2ms)
https://explain.depesz.com/s/TMLO

The settings/hardware on both machines is nearly identical. The only
difference is the imported data volume which is higher for the 12.1
server(europe vs germany/austria/switzerland) but that shouldn't impact the
hash join performance as the number of rows at this point should be roughly
the same.

The only culprit seems to be that the hash join takes a lot longer to finish
and i can't figure out why.