BUG #16241: Degraded hash join performance

From: PG Bug reporting form <noreply(at)postgresql(dot)org>
To: pgsql-bugs(at)lists(dot)postgresql(dot)org
Cc: tbutz(at)optitool(dot)de
Subject: BUG #16241: Degraded hash join performance
Date: 2020-02-03 15:33:02
Message-ID: 16241-5af1a947ef860367@postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 16241
Logged by: Thomas Butz
Email address: tbutz(at)optitool(dot)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.

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Tom Lane 2020-02-03 15:56:09 Re: BUG #16171: Potential malformed JSON in explain output
Previous Message Jack Plasterer 2020-02-03 15:25:14 Re: Unable to trigger createdb