Collected from the PG bugs email list.
|Reported by||Yuri Astrakhan|
Body of first available message related to this bug follows.
The following bug has been logged on the website: Bug reference: 16076 Logged by: Yuri Astrakhan Email address: (redacted) PostgreSQL version: 12.0 Operating system: Ubuntu 19.10 Description: (huge thanks to @Komzpa - Darafei Praliaskouski who figured out the culprit) PostgreSQL 12.0 (Ubuntu 12.0-2.pgdg19.10+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.2.1-9ubuntu1) 9.2.1 20191008, 64-bit POSTGIS="3.0.0 r17983" [EXTENSION] PGSQL="120" GEOS="3.8.0-CAPI-1.13.1 " PROJ="Rel. 5.2.0, September 15th, 2018" LIBXML="2.9.4" LIBJSON="0.13.1" LIBPROTOBUF="1.3.1" WAGYU="0.4.3 (Internal)" I have a full OpenStreetMap loaded using OpenMapTiles configuration into multiple machine configurations. A single query (wrapped as a UDF) generates the whole MVT tile blob, and uses many layers, sub-queries, and UDFs. When JIT is enabled, the query takes 5-10 seconds to run. Disabling JIT makes the query run in milliseconds. Same queries on PostgreSQL 10 + Postgis 2.5.2/GEOS 3.6 have no issues (as there was no JIT). Details: The query is a concatenation of multiple map layers (binary blobs in MVT format from ST_AsMVT()). All layers are "UNION ALL-ed" from subqueries, one query per layer. The "explain analyze" shows huge cost of the first layer query, regardless of the order of those layers. Every layer added about 200-300ms to the execution (not planning!) time, totalling about 5-7 seconds. Yet, I noticed that when I was testing a portion of layers, removing layer_waterway made query run in milliseconds (I suspect there are more than one layer that causes this bug). I will try to reproduce this error using OpenMapTiles's quickstart setup, but that will involve some heavy customization. Is this a known bug, or should I try to get an easy to set up repo? Thanks! * "perf top" results - https://gist.github.com/nyurik/fa0cb0dd935d50e796cd5d58d8d41067 * A few explore analyze runs with different layer orderings: * https://explain.depesz.com/s/fecC * https://explain.depesz.com/s/MpMo * https://explain.depesz.com/s/d7RU * main query - https://gist.github.com/nyurik/ed80c5e6d20f67fd14d37ea2503a9cbd * helpers - https://gist.github.com/nyurik/00c4c9d69b7d4fa67c86159b9ce83027
|2019-10-24 15:12:16+00||PG Bug reporting form||BUG #16076: JIT causes huge delays in a complex query. jit=off solves it.|
|2019-10-25 01:04:56+00||Andres Freund||Re: BUG #16076: JIT causes huge delays in a complex query. jit=off solves it.|
|2019-10-25 01:58:25+00||Yuri Astrakhan||Re: BUG #16076: JIT causes huge delays in a complex query. jit=off solves it.|