PostgreSQL Bugs

Collected from the PG bugs email list.

Bug ID16076
PG Version12.0
OSUbuntu 19.10
Opened2019-10-24 15:12:16+00
Reported byYuri Astrakhan
StatusNew

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

Messages

DateAuthorSubject
2019-10-24 15:12:16+00PG Bug reporting formBUG #16076: JIT causes huge delays in a complex query. jit=off solves it.
2019-10-25 01:04:56+00Andres FreundRe: BUG #16076: JIT causes huge delays in a complex query. jit=off solves it.
2019-10-25 01:58:25+00Yuri AstrakhanRe: BUG #16076: JIT causes huge delays in a complex query. jit=off solves it.