BUG #15486: PG11 jit on 50x slower than jit off

The following bug has been logged on the website:

Bug reference: 15486
Logged by: Jakub Janeček
Email address: jakub(dot)janecek(at)comgate(dot)cz
PostgreSQL version: 11.0
Operating system: Debian 9
Description:

Hi team,

we have a problem with PG11 and JIT. Execution time with JIT ON is slower
than JIT OFF. PostgreSQL is running on Debian in LXC Container on Proxmox
VE.

Here is some example:

create table t(a int);
insert into t select generate_series from generate_series(1, 100000);
analyze t;
set jit to off;

explain analyze SELECT
ARRAY( SELECT DISTINCT unnest(array[]::integer[] || t.a) AS unnest) AS
c1,
ARRAY( SELECT DISTINCT unnest(((((array[]::integer[]))) || t.a)) AS
unnest) AS c2
FROM t
;

set jit to on;

explain analyze SELECT
ARRAY( SELECT DISTINCT unnest(array[]::integer[] || t.a) AS unnest) AS
c1,
ARRAY( SELECT DISTINCT unnest(((((array[]::integer[]))) || t.a)) AS
unnest) AS c2
FROM t
;

show jit_provider;

Here are results:

JIT OFF
QUERY PLAN
--------------------------------------------------------------------------------------------------------------
Seq Scan on t (cost=0.00..455443.00 rows=100000 width=64) (actual
time=0.032..1047.012 rows=100000 loops=1)
SubPlan 1
-> HashAggregate (cost=0.77..2.27 rows=100 width=4) (actual
time=0.001..0.002 rows=1 loops=100000)
Group Key: unnest(('{}'::integer[] || t.a))
-> ProjectSet (cost=0.00..0.52 rows=100 width=4) (actual
time=0.001..0.001 rows=1 loops=100000)
-> Result (cost=0.00..0.01 rows=1 width=0) (actual
time=0.000..0.000 rows=1 loops=100000)
SubPlan 2
-> HashAggregate (cost=0.77..2.27 rows=100 width=4) (actual
time=0.001..0.002 rows=1 loops=100000)
Group Key: unnest(('{}'::integer[] || t.a))
-> ProjectSet (cost=0.00..0.52 rows=100 width=4) (actual
time=0.001..0.001 rows=1 loops=100000)
-> Result (cost=0.00..0.01 rows=1 width=0) (actual
time=0.000..0.000 rows=1 loops=100000)
Planning Time: 0.162 ms
Execution Time: 1099.676 ms
(13 rows)
--------------------

JIT ON
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
Seq Scan on t (cost=0.00..455443.00 rows=100000 width=64) (actual
time=12.979..57391.188 rows=100000 loops=1)
SubPlan 1
-> HashAggregate (cost=0.77..2.27 rows=100 width=4) (actual
time=0.004..0.004 rows=1 loops=100000)
Group Key: unnest(('{}'::integer[] || t.a))
-> ProjectSet (cost=0.00..0.52 rows=100 width=4) (actual
time=0.002..0.002 rows=1 loops=100000)
-> Result (cost=0.00..0.01 rows=1 width=0) (actual
time=0.000..0.000 rows=1 loops=100000)
SubPlan 2
-> HashAggregate (cost=0.77..2.27 rows=100 width=4) (actual
time=0.004..0.004 rows=1 loops=100000)
Group Key: unnest(('{}'::integer[] || t.a))
-> ProjectSet (cost=0.00..0.52 rows=100 width=4) (actual
time=0.002..0.002 rows=1 loops=100000)
-> Result (cost=0.00..0.01 rows=1 width=0) (actual
time=0.000..0.000 rows=1 loops=100000)
Planning Time: 0.092 ms
JIT:
Functions: 600016
Options: Inlining false, Optimization false, Expressions true, Deforming
true
Timing: Generation 55262.932 ms, Inlining 0.000 ms, Optimization 0.481
ms, Emission 12.218 ms, Total 55275.631 ms
Execution Time: 70522.785 ms
(17 rows)
------------------

If you need more details, do not hesitate to contact me.

Thank you,
best regards,

Jakub Janecek