Horton

Query is over 2x slower with jit=on

Andreas Joseph Krogh <andreas@visena.com>

View thread View on postgr.es
From: Andreas Joseph Krogh <andreas@visena.com>
To: pgsql-hackers@lists.postgresql.org
Date: 2018-04-18T15:35:31Z
Lists: pgsql-hackers 
Hi all.
 
I don't know whether this is expected or not but I'm experiencing over 2x 
slowdown on a large query in PG-11 with JIT=on.
 
(query is a prepared statement executed with "explain analyze execute 
myprepared(arg1, arg2, ..., argn)")
 
After 10 executions these are the results (the first 5 executed in > 16s, then 
the plan changed)
 
With jit=on:
https://explain.depesz.com/s/vYB
Planning Time: 0.336 ms 
 JIT:
  Functions: 716
  Generation Time: 78.404 ms
  Inlining: false
  Inlining Time: 0.000 ms
  Optimization: false
  Optimization Time: 43.916 ms
  Emission Time: 600.031 ms
 Execution Time: 2035.150 ms
 (385 rows)
  
With jit=off:
https://explain.depesz.com/s/X6mA
Planning Time: 0.371 ms 
 Execution Time: 833.941 ms
 (377 rows)
  
Both are master as of 55d26ff638f063fbccf57843f2c27f9795895a5c
 
The query largely consists of CTEs with aggregation which are FULL OUTER 
JOIN'ed.
 
On v10 the query executes in:
Execution time: 1159.628 ms
  
So v11 (with jit=off) is about 25% faster (due to parallel hash-join I think), 
which is nice!
 
What's the deal with jit making it slower?
 
--
Andreas Joseph Krogh

Commits

  1. Fix issues around EXPLAIN with JIT.

  2. Collect JIT instrumentation from workers.

  3. Make EXPLAIN output for JIT compilation more dense.

  4. Save/restore SPI's global variables in SPI_connect() and SPI_finish().

  5. Rationalize handling of single and double quotes in bootstrap data.