pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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.



view thread (9+ messages)  latest in thread

Message-ID: <16241-5af1a947ef860367@postgresql.org>
Permalink:  ../16241-5af1a947ef860367@postgresql.org/
Also on:    postgresql.org/message-id/16241-5af1a947ef860367@postgresql.org

 · 

reply

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