Re: BUG #16241: Degraded hash join performance

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