From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: tbutz@optitool.de
Subject: BUG #16241: Degraded hash join performance
Date: Mon, 03 Feb 2020 15:33:02 +0000
Message-ID: <16241-5af1a947ef860367@postgresql.org> (raw)
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-bugs@postgresql.org
Cc: noreply@postgresql.org, tbutz@optitool.de
Subject: Re: BUG #16241: Degraded hash join performance
In-Reply-To: <16241-5af1a947ef860367@postgresql.org>
References: <16241-5af1a947ef860367@postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox