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
