Re: BUG #16241: Degraded hash join performance

From: Thomas Butz <tbutz(at)optitool(dot)de>
To: pgsql-bugs <pgsql-bugs(at)lists(dot)postgresql(dot)org>
Cc: Andres Freund <andres(at)anarazel(dot)de>
Subject: Re: BUG #16241: Degraded hash join performance
Date: 2020-02-04 09:02:45
Message-ID: 438641783.252458.1580806965331.JavaMail.zimbra@optitool.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

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(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:
>
> 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

Attachment Content-Type Size
report-children.txt text/plain 2.0 MB
report-no-children.txt text/plain 96.9 KB

Browse pgsql-bugs by date

  From Date Subject
Next Message Andres Freund 2020-02-04 13:44:08 Re: BUG #16241: Degraded hash join performance
Previous Message Amit Kapila 2020-02-04 06:41:31 Re: ERROR: subtransaction logged without previous top-level txn record