BUG #15486: PG11 jit on 50x slower than jit off - Mailing list pgsql-bugs

The following bug has been logged on the website:

Bug reference:      15486
Logged by:          Jakub Janeček
Email address:      jakub.janecek@comgate.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