Re: BUG #16241: Degraded hash join performance

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