From: Thomas Butz <tbutz@optitool.de>
To: pgsql-bugs <pgsql-bugs@lists.postgresql.org>
Cc: Andres Freund <andres@anarazel.de>
Subject: Re: BUG #16241: Degraded hash join performance
Date: Tue, 4 Feb 2020 10:02:45 +0100 (CET)
Message-ID: <438641783.252458.1580806965331.JavaMail.zimbra@optitool.de> (raw)
I've executed the query twice and attached the generated reports.
> Hi,
>
> On 2020-02-03 15:33:02 +0000, PG Bug reporting form wrote:
>> 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:
>
> Hm. This sounds somehow familar.
>
> I first thought it might be a bug that I'm literally just now am trying
> to find again, because I made a note to fix it before the next set of
> minor releases. Without writing down the bug number :(.
>
> But the bug I am thinking of was around hash *aggregates* and subplans,
> not joins.
>
> It'd be useful if you could get a perf profile with callgraphs. You
> would have to install debugging symbols for both postgres and postgis,
> and then do something like:
>
> SELECT pg_backend_pid(); to get the backend pid
> perf record --call-graph dwarf -p $pid_of_session
> and then run the query a times in the session you got the pid for
> and then cancel the perf record run with ctrl-c
>
> You can then look at the perf profile interactively with
> perf report --no-children
> and send the created file of
> perf report --no-children > somefile-no-children
> perf report --children > somefile-children
>
> - Andres
Thomas Butz
Attachments:
report-children.txt (text/plain, 2.0M) download
report-no-children.txt (text/plain, 96.9K) download
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: tbutz@optitool.de, andres@anarazel.de
Subject: Re: BUG #16241: Degraded hash join performance
In-Reply-To: <438641783.252458.1580806965331.JavaMail.zimbra@optitool.de>
References: <438641783.252458.1580806965331.JavaMail.zimbra@optitool.de>
* 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