We found a simple query which is much slower using the JIT Compilation. The Query Plan
kicktipp=# show jit;
jit
-----
on
(1 row)
kicktipp=# explain analyze select * from tippereignis where tippspieltag_id in ( select tippspieltag_id from tippspieltag where tippsaison_id = 3571821);
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=1.01..426899.54 rows=1835 width=65) (actual time=9.493..9.797 rows=310 loops=1)
-> Index Scan using ix_tippspieltag_tippsaison_id on tippspieltag (cost=0.44..423.05 rows=223 width=4) (actual time=0.132..0.141 rows=35 loops=1)
Index Cond: (tippsaison_id = 3571821)
-> Index Scan using ix_tippereignis_tippspieltag_ereignis on tippereignis (cost=0.57..1901.43 rows=1102 width=65) (actual time=0.004..0.007 rows=9 loops=35)
Index Cond: (tippspieltag_id = tippspieltag.tippspieltag_id)
Planning Time: 0.500 ms
JIT:
Functions: 7
Options: Inlining false, Optimization false, Expressions true, Deforming true
Timing: Generation 0.705 ms, Inlining 0.000 ms, Optimization 0.564 ms, Emission 8.744 ms, Total 10.013 ms
Execution Time: 10.620 ms
(11 rows)
kicktipp=# set jit=off;
SET
kicktipp=# explain analyze select * from tippereignis where tippspieltag_id in ( select tippspieltag_id from tippspieltag where tippsaison_id = 3571821);
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=1.01..426899.54 rows=1835 width=65) (actual time=0.030..0.365 rows=310 loops=1)
-> Index Scan using ix_tippspieltag_tippsaison_id on tippspieltag (cost=0.44..423.05 rows=223 width=4) (actual time=0.014..0.022 rows=35 loops=1)
Index Cond: (tippsaison_id = 3571821)
-> Index Scan using ix_tippereignis_tippspieltag_ereignis on tippereignis (cost=0.57..1901.43 rows=1102 width=65) (actual time=0.005..0.007 rows=9 loops=35)
Index Cond: (tippspieltag_id = tippspieltag.tippspieltag_id)
Planning Time: 0.480 ms
Execution Time: 0.416 ms
(7 rows)
It's a simple query with two index scans. On both tables a reindex and vacuum was executed, before running the explain analyze. The JIT parameters have default values: jit_above_cost=100000, jit_inline_above_cost=500000, jit_optimize_above_cost=500000.
The query has a result of 310 rows. The table tippereignis has 397.244.719 rows, tippspieltag 48.264.823 rows. The indices are both a btree index.
I would like to understand the reasons, why this query is so much slower with the jit compiler. The only solution for us ist to set jit to off. Probably there are other queries slower, too. But this example was conspicuous because it was so much slower.
