Collected from the PG bugs email list.
|OS||Modified postgres:12 docker image (Debian Buster)|
|Reported by||Thomas Butz|
Body of first available message related to this bug follows.
The following bug has been logged on the website: Bug reference: 16241 Logged by: Thomas Butz Email address: (redacted) 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.
|2020-02-03 15:33:02+00||PG Bug reporting form||BUG #16241: Degraded hash join performance|
|2020-02-03 16:23:03+00||Andres Freund||Re: BUG #16241: Degraded hash join performance|
|2020-02-04 09:02:45+00||Thomas Butz||Re: BUG #16241: Degraded hash join performance|
|2020-02-04 13:44:08+00||Andres Freund||Re: BUG #16241: Degraded hash join performance|
|2020-02-04 14:49:10+00||Thomas Butz||Re: BUG #16241: Degraded hash join performance|
|2020-02-04 15:01:52+00||Andres Freund||Re: BUG #16241: Degraded hash join performance|
|2020-02-04 16:00:29+00||Tom Lane||Re: BUG #16241: Degraded hash join performance|
|2020-02-04 16:29:01+00||Andres Freund||Re: BUG #16241: Degraded hash join performance|
|2020-02-05 11:06:41+00||Thomas Butz||Re: BUG #16241: Degraded hash join performance|