When PostgreSQL JIT Costs More Than the Query: Measuring Compile Time on Short Queries
A PostgreSQL query that runs in 11 ms spent 119 ms end to end, 108 of them compiling it with LLVM JIT. A reproducible experiment on PostgreSQL 16, 17 and 18: where the cost thresholds sit, what each tier costs, and how to switch it off at the right scope.
A list query over 10,000 rows, indexed, returning 10 rows, runs in 11 ms on PostgreSQL 18. With default settings the same query takes 119 ms, and 108 of those milliseconds are spent compiling it to machine code. The query never got slower; PostgreSQL decided it was expensive enough to be worth compiling, and the compile cost ten times what the query itself needed.
This post reproduces that effect from scratch on PostgreSQL 16, 17 and 18, measures what each JIT tier costs, shows exactly where the planner's cost thresholds switch them on, and ends with the options for turning JIT off at the narrowest scope that fixes the problem. Everything in it comes from the harness at the bottom, which needs only Docker and Python. It also covers what did not reproduce: on the analytic query I tried, JIT never paid off.
What JIT does and when the planner turns it on
PostgreSQL can compile parts of a query to native code with LLVM. The documentation names two things it accelerates: expression evaluation (WHERE clauses, target lists, aggregates) and tuple deforming. It is aimed at long-running, CPU-bound queries, usually analytical ones, and the same page warns that for short queries the overhead of compiling is often higher than the time it saves.
The decision is made from the planner's total estimated cost, at plan time, against three settings. Their defaults are the same in versions 16, 17 and 18:
| setting | default | what it switches on |
|---|---|---|
jit_above_cost | 100,000 | JIT compilation at all |
jit_inline_above_cost | 500,000 | inlining of short functions and operators |
jit_optimize_above_cost | 500,000 | expensive LLVM optimization passes |
flowchart TD
P["Planner total cost for the query"] --> A{"jit on and cost above jit_above_cost (100,000)?"}
A -- no --> N["Interpreted expressions, no compile step"]
A -- yes --> B["Compile expressions and tuple deforming"]
B --> C{"cost above jit_inline_above_cost (500,000)?"}
C -- yes --> I["Also inline small functions"]
B --> D{"cost above jit_optimize_above_cost (500,000)?"}
D -- yes --> O["Also run expensive optimization"]
Two consequences matter below. The decision uses the planner's cost, not a time, so an inflated estimate pays for compilation the query will never earn back. And the compile happens per execution, in the backend that runs the query. In the measurements below nothing was reused between executions: the 21st run in a session paid for compilation as much as the first.
The experiment
The schema is a synthetic task tracker: tasks(id, parent_id, status, title) where about one row in ten is a parent, and task_deps(task_id, blocker_id) for "this task is blocked by that one". Seeding is deterministic (setseed plus fixed formulas), at two sizes, 10,000 and 100,000 tasks, followed by ANALYZE. Every query returns identical rows on all three versions; the harness prints a row count and checksum for Q1 to Q3 to prove it.
Four queries, all in the harness:
- Q1, low cost. Three correlated
NOT EXISTSchecks over the indexed columns. Planner cost 6,755 at 10,000 rows. - Q2, middle. "Ready" tasks: nothing blocks the task, its parent or any of its children, written as correlated
EXISTS/NOT EXISTSsubqueries. Planner cost 198,986, betweenjit_above_costand the 500,000 tier. - Q3, high. Q2 plus two more levels of nested
EXISTS(siblings, and tasks that depend on this one). Planner cost 577,376, above all three thresholds, although the optimizer expects about 173 rows and the query returns 10. - Q4, analytic. A long aggregation over a separate 3-million-row table, with a dozen arithmetic aggregates. It is there to look for a case where JIT wins.
Each query runs under five configurations, interleaved inside one session (A, B, C, A, B, C and so on, so that background load on a busy laptop hits every configuration alike): the defaults; jit = off; the inline and optimize thresholds raised to 1,000,000,000 so only the cheap tier remains; all three thresholds raised; and all three set to 0 to force the full tier. Each cell is one discarded warm-up round plus 21 timed runs, and the tables report the median with the 25th to 75th percentile in brackets. The time is psql's \timing for the plain query with rows discarded, so it includes planning, JIT and execution as a client sees them; a second set of runs uses EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) to read the JIT block and the cost.
Environment: Docker 29 in a Linux VM (4 vCPU, 4 GB, Ubuntu 24.04, aarch64) on an Apple M2 Pro laptop, official postgres:16, postgres:17 and postgres:18 images (16.15, 17.11 and 18.6, the current minors at the time of writing) with default configuration. In all three, SELECT pg_jit_available() returned true, jit was on and jit_provider was llvmjit. These are aarch64 Debian builds, so the absolute numbers will differ on x86 or with another LLVM; the shape of the results is what carries over.
What the defaults cost
PostgreSQL 18.6, 10,000 tasks, median of 21 runs, milliseconds:
| query | planner cost | JIT tier at defaults | jit = off | defaults | of which JIT | slowdown |
|---|---|---|---|---|---|---|
| Q1 | 6,755 | none | 6.3 | 6.4 | 0 | 1.0x |
| Q2 | 198,986 | compile only | 7.0 | 15.8 | 8.4 | 2.3x |
| Q3 | 577,376 | compile, inline, optimize | 10.9 | 119.1 | 108.0 | 10.9x |
Q3 is the one to look at. The JIT block that EXPLAIN ANALYZE prints for it:
1
2
3
4
5
6
JIT:
Functions: 80
Options: Inlining true, Optimization true, Expressions true, Deforming true
Timing: Generation 1.131 ms (Deform 0.360 ms), Inlining 7.474 ms,
Optimization 55.857 ms, Emission 56.864 ms, Total 121.327 ms
Execution Time: 133.989 ms
(A single EXPLAIN ANALYZE from a separate session, its second execution; the medians in the table come from 21 runs.) Generation is cheap. Optimization and emission, the LLVM work, are almost all of it. The Execution Time reported by EXPLAIN ANALYZE includes that JIT time, so the real work of the query was roughly the 11 ms the jit = off run shows.
The threshold effect
Same three queries, PostgreSQL 18.6, 10,000 tasks, median wall time in milliseconds:
| configuration | Q1 (6,755) | Q2 (198,986) | Q3 (577,376) |
|---|---|---|---|
| defaults | 6.4 | 15.8 | 119.1 |
jit = off | 6.3 | 7.0 | 10.9 |
| inline and optimize thresholds raised | 6.3 | 15.6 | 24.6 |
| all three thresholds raised | 6.5 | 7.1 | 10.9 |
| all three thresholds set to 0 (forced) | 128.8 | 77.4 | 118.8 |
Read it row by row. Raising only the two expensive thresholds takes Q3 from 119 ms to 24.6 ms: the cheap tier still costs about 13 ms there, more than the query itself, but inlining and optimization were the bulk of the bill. Raising all three matches jit = off. The forced row shows what the full tier costs in isolation: even Q1, a 6 ms query the planner correctly left alone, takes 129 ms when forced through it. That fixed price of roughly 110 to 130 ms follows the size of the expression tree, not the number of rows the query touches, as the next table shows.
The overhead does not grow with the data
Same queries, 100,000 tasks, PostgreSQL 18.6:
| query | planner cost | jit = off | defaults | of which JIT |
|---|---|---|---|---|
| Q1 | 87,417 | 67.3 | 70.1 | 0 |
| Q2 | 2,995,566 | 82.9 | 157.8 | 77.5 |
| Q3 | 7,339,736 | 146.7 | 273.7 | 127.9 |
With ten times the rows the queries get slower and the planner's cost goes up, so Q2 now crosses 500,000 and gets the full tier too: its JIT bill grows from 8 ms to 78 ms. Q3's bill barely moves (108 ms, then 128 ms), so with more data it matters less in relative terms (its penalty falls from 10.9x to 1.9x), and it is still a pure loss: compiled expressions did not repay it. Note Q1 at 87,417: it sits 13 percent under jit_above_cost. That is the cliff the PostgreSQL commit message below describes, a few more rows or one more condition away from a 120 ms tax.
Same behavior on 16, 17 and 18
Q3 at the defaults against jit = off, medians in milliseconds, with the JIT time that EXPLAIN attributes:
| version | 10,000 rows, jit = off | defaults | JIT | 100,000 rows, jit = off | defaults | JIT |
|---|---|---|---|---|---|---|
| 16.15 | 11.1 | 132.2 | 116.7 | 138.5 | 259.8 | 123.1 |
| 17.11 | 12.6 | 149.2 | 137.4 | 160.4 | 308.9 | 112.4 |
| 18.6 | 10.9 | 119.1 | 108.0 | 146.7 | 273.7 | 127.9 |
The planner cost for Q3 at 10,000 rows is 577,376 on all three, the thresholds are the same, and so is the outcome. The 100,000-row figures on 16 and 17 had a wide spread between runs on a busy laptop (for 16.15 defaults the 25th to 75th percentile was 256 to 549 ms), which is why the JIT column, taken from the plan, is the steadier comparison there.
A new connection pays more on its first JIT query. In the session's first run of Q3 at defaults, the wall time was 158 ms on 18.6 against a median of 119 ms, 200 ms against 149 on 17.11 and 175 against 132 on 16.15. The EXPLAIN of one such first run showed Inlining at 47.8 ms against about 7.5 ms warm. A pool that keeps connections for a long time pays that once per connection; short-lived connections pay it every time.
Why the estimate is so high
The cost is in the planner's own units, not milliseconds, and the harness lets us divide one by the other, using the jit = off times (18.6):
| query | rows | planner cost | actual ms | cost per ms |
|---|---|---|---|---|
| Q1 | 10,000 | 6,755 | 6.3 | 1,072 |
| Q2 | 10,000 | 198,986 | 7.0 | 28,400 |
| Q3 | 10,000 | 577,376 | 10.9 | 53,000 |
| Q3 | 100,000 | 7,339,736 | 146.7 | 50,000 |
| Q4 | 3,000,000 | 79,945 | 421.6 | 190 |
Queries built from correlated EXISTS / NOT EXISTS subqueries come out 26 to 50 times more expensive per millisecond than the plainer Q1, and Q3 was expected to return 173 rows and returned 10. I did not dig into the planner source to say exactly which estimates compound; what the measurement shows is the ratio. The parallel analytic query is at the other end: 190 cost units per millisecond, below the 100,000 threshold in total (a parallel plan's cost is spread across workers), so JIT never started by itself.
The PostgreSQL project reached a similar view. A commit on 2 April 2026 changed the default of jit to off, explaining that compiling and optimizing can cost more than JIT saves on shorter queries, that the choice is driven by a simple cost comparison without a reliable estimate of the LLVM cost, that small unrelated changes can push the cost just over a threshold, and that most large-scale deployments, the hyperscalers included, already disable it. The PostgreSQL 19 release notes list that change, and the 19 documentation gives off as the default; at the time of writing 19 was at release candidate 1, so 16, 17 and 18 keep on.
Where JIT did not help
Q4 was meant to be the counterexample: a long aggregation with a dozen arithmetic aggregates over 3 million rows. At the defaults its plan cost was about 79,900, below jit_above_cost, so JIT stayed off and it ran in 421 ms on 18.6, 457 ms on 16.15 and 538 ms on 17.11. Forcing JIT on with the thresholds at 0 made it slower on every version, by 12 percent on 18.6 (473 vs 422 ms), 4 percent on 16.15 (475 vs 456 ms) and, on a noisier run, 23 percent on 17.11 (673 vs 547 ms). While building the harness I also tried, ad hoc and outside it, a 5-million-row table with a dozen aggregates and a 2-million-row, 30-column table read at its last columns, and neither favored JIT in this environment either. So the honest result of this experiment is that it did not reproduce a JIT win, not that none exists: the documentation's claim about long CPU-bound analytic queries stands, and my queries were not that on this hardware and LLVM build. If JIT matters for a workload, measure that workload.
Options and their scope
All the cost settings and jit itself can be changed per session (in pg_settings their context is user on 18.6; jit_provider is postmaster). They differ in how far the change reaches:
| option | scope | note |
|---|---|---|
SET jit = off | the current session | gone at reconnect, so a pool must run it on every new connection |
options=-c jit=off in the connection string, or PGOPTIONS='-c jit=off' | every connection made with that string | check with SHOW jit that a pooler in the middle passes it through |
ALTER ROLE app SET jit = off, optionally IN DATABASE | every new session of that role | applied at login; SET ROLE does not apply it |
ALTER DATABASE db SET jit = off | every new session in that database | all roles in it |
jit = off in postgresql.conf | the whole server | also switches off JIT for the analytic work that benefits |
raise jit_inline_above_cost and jit_optimize_above_cost | as above | keeps the cheap tier; took Q3 from 119 to 24.6 ms here |
raise jit_above_cost | as above | keeps JIT for genuinely large plans; pick a value from your own cost distribution |
A practical order: find the queries whose JIT Total in EXPLAIN (ANALYZE) is a large share of Execution Time; set jit = off for the application role that runs short transactional queries, so a reporting role or an analyst's session keeps the default; and go server-wide only once nothing on the server benefits. Settings applied at plan time also matter for prepared statements with a generic plan: the documentation says the values in effect at prepare time control the decision, not those at execution time.
Managed platforms differ in what they ship, so run SELECT pg_jit_available(); and SHOW jit; on the real database rather than assuming. The first returns true only when a JIT provider is available and jit is on, per the function reference.
What this does and does not show
- The effect is real and large for short queries with an inflated cost estimate: ten times slower for an 11 ms query, and a fixed 80 to 130 ms tax on every execution of a query that crosses the thresholds, in these measurements.
- It is not a claim that JIT is bad. It did not win on my analytic query, but that is one query on one aarch64 build.
- The
EXPLAINruns add their own overhead (see theexeccolumn in the raw output), which is why the tables use the plain-query wall time. - The laptop was busy and noisy. Medians and interquartile ranges are reported for that reason; the 100,000-row cells on 16 and 17 are the least steady.
Reproduce it
Needs Docker and Python 3 (standard library only). It starts one container per version, named jit-lab-16, jit-lab-17 and jit-lab-18, removes each when it finishes, and takes about twelve minutes.
1
2
python3 jit_lab.py # prints one line per cell, writes jit_lab.json
python3 jit_report.py # Markdown tables with percentile spreads
The harness, jit_lab.py:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
#!/usr/bin/env python3
"""Measure PostgreSQL JIT cost on short queries. Needs Docker and python3 only."""
import json, re, statistics, subprocess, sys, time
VERSIONS = ["16", "17", "18"]
SIZES = [10_000, 100_000]
RUNS = 21 # timed runs per cell, after one discarded warm-up round
SEED = """
DROP TABLE IF EXISTS task_deps, tasks;
CREATE TABLE tasks (
id integer PRIMARY KEY,
parent_id integer REFERENCES tasks(id),
status text NOT NULL,
title text NOT NULL);
CREATE TABLE task_deps (
task_id integer NOT NULL REFERENCES tasks(id),
blocker_id integer NOT NULL REFERENCES tasks(id),
PRIMARY KEY (task_id, blocker_id));
SELECT setseed(0.42);
INSERT INTO tasks
SELECT i,
CASE WHEN i % 10 = 0 THEN NULL
ELSE (floor(random() * (:n / 10)) + 1)::int * 10 END,
(ARRAY['todo','doing','done','done','done'])[1 + floor(random() * 5)::int],
'task ' || i
FROM generate_series(1, :n) i;
INSERT INTO task_deps
SELECT t.id, 1 + floor(random() * :n)::int
FROM tasks t, generate_series(1, 2) g
WHERE random() < 0.6
ON CONFLICT DO NOTHING;
DELETE FROM task_deps WHERE task_id = blocker_id;
CREATE INDEX ON tasks (parent_id);
CREATE INDEX ON tasks (status);
CREATE INDEX ON task_deps (blocker_id);
ANALYZE;
"""
BLOCKED = ("EXISTS (SELECT 1 FROM task_deps d JOIN tasks b ON b.id = d.blocker_id "
"WHERE d.task_id = {t} AND b.status <> 'done')")
QUERIES = {
# a few correlated subqueries, cheap plan
"Q1 low": """SELECT t.id FROM tasks t WHERE t.status <> 'done'
AND NOT EXISTS (SELECT 1 FROM tasks c WHERE c.parent_id = t.id AND NOT EXISTS
(SELECT 1 FROM task_deps d JOIN tasks b ON b.id = d.blocker_id
WHERE d.task_id = c.id AND b.status = 'done'))
AND NOT EXISTS (SELECT 1 FROM tasks s WHERE s.parent_id = t.parent_id AND s.id <> t.id
AND s.status = 'doing' AND NOT EXISTS
(SELECT 1 FROM task_deps d WHERE d.task_id = s.id AND d.blocker_id = t.id))
AND NOT EXISTS (SELECT 1 FROM task_deps d WHERE d.blocker_id = t.id AND NOT EXISTS
(SELECT 1 FROM tasks x WHERE x.id = d.task_id AND x.status = 'todo'))""",
# "ready" tasks: nothing blocks it, its parent, or any of its children
"Q2 mid": """SELECT t.id FROM tasks t WHERE t.status <> 'done'
AND NOT """ + BLOCKED.format(t="t.id") + """
AND (t.parent_id IS NULL OR NOT """ + BLOCKED.format(t="t.parent_id") + """)
AND NOT EXISTS (SELECT 1 FROM tasks c WHERE c.parent_id = t.id
AND (c.status <> 'done' OR """ + BLOCKED.format(t="c.id") + "))",
# the same plus two more levels of EXISTS
"Q3 high": """SELECT t.id FROM tasks t WHERE t.status <> 'done'
AND NOT """ + BLOCKED.format(t="t.id") + """
AND (t.parent_id IS NULL OR NOT """ + BLOCKED.format(t="t.parent_id") + """)
AND NOT EXISTS (SELECT 1 FROM tasks c WHERE c.parent_id = t.id
AND (c.status <> 'done' OR """ + BLOCKED.format(t="c.id") + """))
AND (t.parent_id IS NULL OR NOT EXISTS (SELECT 1 FROM tasks s
WHERE s.parent_id = t.parent_id AND s.id <> t.id
AND (s.status = 'doing' OR """ + BLOCKED.format(t="s.id") + """)))
AND NOT EXISTS (SELECT 1 FROM task_deps d JOIN tasks x ON x.id = d.task_id
WHERE d.blocker_id = t.id AND (x.status = 'doing'
OR EXISTS (SELECT 1 FROM tasks y WHERE y.parent_id = x.id AND y.status = 'doing')))""",
# a long analytic scan, where JIT is meant to pay off (reads its own 3M-row table)
"Q4 analytic": """SELECT a % 4, count(*), sum(a::float8), sum(a * (1 - c / 20)),
sum(a * (1 - c / 20) * (1 + b / 1000.0)), avg(a), avg(b), avg(c), sum(b * c),
sum(a * b * c), sum(a * a + b * b), max(a * c - b)
FROM facts WHERE a + b > 100 GROUP BY a % 4""",
}
FACTS = """
DROP TABLE IF EXISTS facts;
CREATE TABLE facts AS
SELECT g AS id, ((g::bigint * 7919) % 1000)::int AS a, ((g::bigint * 104729) % 977)::int AS b,
(g % 13)::float8 AS c
FROM generate_series(1, 3000000) g;
ANALYZE facts;
"""
BIG = "1000000000"
CONFIGS = {
"defaults": "",
"jit=off": "SET jit = off;",
"tier1 only": f"SET jit_inline_above_cost = {BIG}; SET jit_optimize_above_cost = {BIG};",
"forced": "SET jit_above_cost = 0; SET jit_inline_above_cost = 0; "
"SET jit_optimize_above_cost = 0;",
"all raised": f"SET jit_above_cost = {BIG}; SET jit_inline_above_cost = {BIG}; "
f"SET jit_optimize_above_cost = {BIG};",
}
def sh(*a, stdin=None):
r = subprocess.run(a, input=stdin, capture_output=True, text=True)
if r.returncode:
sys.exit(f"{' '.join(a)}\n{r.stderr}")
return r.stdout
def psql(c, script, *flags):
return sh("docker", "exec", "-i", c, "psql", "-U", "postgres", "-X", "-q", "-A", "-t",
"-v", "ON_ERROR_STOP=1", *flags, stdin=script)
def med(xs):
return statistics.median(xs)
def wait_ready(c):
# the official image starts a temporary server for init, then restarts: wait for the second
for _ in range(90):
time.sleep(1)
log = subprocess.run(["docker", "logs", c], capture_output=True, text=True)
if (log.stdout + log.stderr).count("ready to accept connections") >= 2:
return
sys.exit("server did not come up")
def run_query(c, sql, configs):
"""Time one query under every config. Runs are interleaved inside ONE session
(config A, B, C, A, B, C, ...), so a noisy neighbour hits every config alike."""
q = sql.strip()
names = list(configs)
def step(name): # one run of one config
return f"RESET ALL;\n{configs[name]}\n"
# 1) wall-clock as the client sees it: psql \timing, result rows discarded
body = "".join(step(n) + "\\timing on\n" + q + ";\n\\timing off\n"
for _ in range(RUNS + 1) for n in names)
times = [float(x) for x in re.findall(r"Time: ([\d.]+) ms", psql(c, "\\o /dev/null\n" + body))]
# 2) the plan: EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON), the JIT block and the cost
body = "".join(step(n) + f"EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) {q};\n\\echo @@@\n"
for _ in range(RUNS + 1) for n in names)
plans = [json.loads(p)[0] for p in psql(c, body).split("@@@") if p.strip()]
cells = {}
for i, n in enumerate(names):
mine = plans[i::len(names)]
jit = [p.get("JIT", {}).get("Timing", {}) for p in mine]
cells[n] = {
"cost": mine[0]["Plan"]["Total Cost"],
"jit_functions": mine[0].get("JIT", {}).get("Functions", 0),
"jit_options": mine[0].get("JIT", {}).get("Options"),
"cold_ms": times[i], # first run of this config in the session
"wall_ms": times[len(names) + i::len(names)],
"plan_ms": [p["Planning Time"] for p in mine[1:]],
"exec_ms": [p["Execution Time"] for p in mine[1:]], # includes JIT time
"jit": {k: [t.get(k, 0.0) for t in jit[1:]]
for k in ("Generation", "Inlining", "Optimization", "Emission", "Total")},
}
return cells
def main():
out = {}
for v in VERSIONS:
c = f"jit-lab-{v}"
subprocess.run(["docker", "rm", "-f", c], capture_output=True)
sh("docker", "run", "-d", "--name", c, "-e", "POSTGRES_PASSWORD=lab", f"postgres:{v}")
try:
wait_ready(c)
info = psql(c, "SELECT version(), pg_jit_available(), "
"current_setting('jit'), current_setting('jit_above_cost'), "
"current_setting('jit_inline_above_cost'), "
"current_setting('jit_optimize_above_cost'), "
"current_setting('jit_provider');").strip().split("|")
print(f"\n== {info[0]}\n pg_jit_available={info[1]} jit={info[2]} above={info[3]} "
f"inline={info[4]} optimize={info[5]} provider={info[6]}", flush=True)
out[v] = {"info": info, "cells": {}}
for n in SIZES:
psql(c, SEED, "-v", f"n={n}")
for name, sql in QUERIES.items():
if name.startswith("Q4"):
if n != SIZES[0]:
continue # does not depend on the seeded size
psql(c, FACTS)
chk = psql(c, f"SELECT count(*), md5(string_agg(id::text, ',' ORDER BY id)) "
f"FROM ({sql}) s;" if not name.startswith("Q4") else "SELECT 1;")
cfgs = {k: v for k, v in CONFIGS.items()
if not name.startswith("Q4") or k != "tier1 only"}
for cname, cell in run_query(c, sql, cfgs).items():
cell["result"] = chk.strip()
out[v]["cells"][f"{n}|{name}|{cname}"] = cell
print(f"{v} n={n:<6} {name:<12} {cname:<11} cost={cell['cost']:>11.0f} "
f"wall={med(cell['wall_ms']):8.1f} exec={med(cell['exec_ms']):8.1f} "
f"jit={med(cell['jit']['Total']):7.1f} {cell['result'][:18]}", flush=True)
finally:
sh("docker", "rm", "-f", c)
json.dump(out, open("jit_lab.json", "w"), indent=1)
if __name__ == "__main__":
main()
The table generator, jit_report.py:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
#!/usr/bin/env python3
"""Turn jit_lab.json into Markdown tables: median and (p25-p75) in milliseconds."""
import json, statistics
data = json.load(open("jit_lab.json"))
m = statistics.median
def span(xs):
q = statistics.quantiles(xs, n=4)
return f"{m(xs):.1f} ({q[0]:.1f}-{q[2]:.1f})"
def phase(c, k):
# PostgreSQL 17+ nests Generation as {"Deform": .., "Total": ..}
xs = [x["Total"] if isinstance(x, dict) else x for x in c["jit"][k]]
return m(xs)
for v, d in data.items():
print(f"\n### {d['info'][0].split(' on ')[0]} (pg_jit_available={d['info'][1]})\n")
for size in sorted({k.split("|")[0] for k in d["cells"]}, key=int):
print(f"rows={size}\n")
print("| query | config | plan cost | wall ms | JIT total | gen | inline | optimize | emit |")
print("|---|---|---:|---:|---:|---:|---:|---:|---:|")
for key, c in d["cells"].items():
n, q, cfg = key.split("|")
if n != size:
continue
print(f"| {q} | {cfg} | {c['cost']:.0f} | {span(c['wall_ms'])} | {phase(c, 'Total'):.1f} | "
f"{phase(c, 'Generation'):.1f} | {phase(c, 'Inlining'):.1f} | "
f"{phase(c, 'Optimization'):.1f} | {phase(c, 'Emission'):.1f} |")
print()
Console output of that run for PostgreSQL 18.6 (wall and exec in milliseconds, exec being the EXPLAIN ANALYZE execution time including JIT; jit the JIT total; the last field is the row count and a checksum of the result, identical on all three versions):
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
PostgreSQL 18.6 (Debian 18.6-1.pgdg13+2) on aarch64-unknown-linux-gnu, compiled by gcc (Debian 14.2.0-19) 14.2.0, 64-bit
pg_jit_available=t jit=on above=100000 inline=500000 optimize=500000 provider=llvmjit
18 n=10000 Q1 low defaults cost= 6755 wall= 6.4 exec= 10.2 jit= 0.0 208|759cc7c49b31ac
18 n=10000 Q1 low jit=off cost= 6755 wall= 6.3 exec= 10.2 jit= 0.0 208|759cc7c49b31ac
18 n=10000 Q1 low tier1 only cost= 6755 wall= 6.3 exec= 10.2 jit= 0.0 208|759cc7c49b31ac
18 n=10000 Q1 low forced cost= 6755 wall= 128.8 exec= 129.3 jit= 119.3 208|759cc7c49b31ac
18 n=10000 Q1 low all raised cost= 6755 wall= 6.5 exec= 10.2 jit= 0.0 208|759cc7c49b31ac
18 n=10000 Q2 mid defaults cost= 198986 wall= 15.8 exec= 16.9 jit= 8.4 1210|8ce8ca534f89d
18 n=10000 Q2 mid jit=off cost= 198986 wall= 7.0 exec= 8.2 jit= 0.0 1210|8ce8ca534f89d
18 n=10000 Q2 mid tier1 only cost= 198986 wall= 15.6 exec= 16.8 jit= 8.3 1210|8ce8ca534f89d
18 n=10000 Q2 mid forced cost= 198986 wall= 77.4 exec= 78.3 jit= 70.0 1210|8ce8ca534f89d
18 n=10000 Q2 mid all raised cost= 198986 wall= 7.1 exec= 8.4 jit= 0.0 1210|8ce8ca534f89d
18 n=10000 Q3 high defaults cost= 577376 wall= 119.1 exec= 120.1 jit= 108.0 10|a350d26dc6f818f
18 n=10000 Q3 high jit=off cost= 577376 wall= 10.9 exec= 12.2 jit= 0.0 10|a350d26dc6f818f
18 n=10000 Q3 high tier1 only cost= 577376 wall= 24.6 exec= 25.8 jit= 13.3 10|a350d26dc6f818f
18 n=10000 Q3 high forced cost= 577376 wall= 118.8 exec= 120.0 jit= 107.8 10|a350d26dc6f818f
18 n=10000 Q3 high all raised cost= 577376 wall= 10.9 exec= 12.2 jit= 0.0 10|a350d26dc6f818f
18 n=10000 Q4 analytic defaults cost= 79945 wall= 421.4 exec= 468.9 jit= 0.0 1
18 n=10000 Q4 analytic jit=off cost= 79945 wall= 421.6 exec= 470.0 jit= 0.0 1
18 n=10000 Q4 analytic forced cost= 79945 wall= 472.7 exec= 538.5 jit= 350.9 1
18 n=10000 Q4 analytic all raised cost= 79945 wall= 419.8 exec= 468.5 jit= 0.0 1
18 n=100000 Q1 low defaults cost= 87417 wall= 70.1 exec= 119.6 jit= 0.0 2315|94370c8ae07a3
18 n=100000 Q1 low jit=off cost= 87417 wall= 67.3 exec= 117.0 jit= 0.0 2315|94370c8ae07a3
18 n=100000 Q1 low tier1 only cost= 87417 wall= 69.1 exec= 119.6 jit= 0.0 2315|94370c8ae07a3
18 n=100000 Q1 low forced cost= 87417 wall= 187.3 exec= 245.5 jit= 127.2 2315|94370c8ae07a3
18 n=100000 Q1 low all raised cost= 87417 wall= 70.3 exec= 118.6 jit= 0.0 2315|94370c8ae07a3
18 n=100000 Q2 mid defaults cost= 2995566 wall= 157.8 exec= 175.2 jit= 77.5 12115|677fa3f4ca56
18 n=100000 Q2 mid jit=off cost= 2995566 wall= 82.9 exec= 101.6 jit= 0.0 12115|677fa3f4ca56
18 n=100000 Q2 mid tier1 only cost= 2995566 wall= 96.4 exec= 111.9 jit= 9.5 12115|677fa3f4ca56
18 n=100000 Q2 mid forced cost= 2995566 wall= 158.7 exec= 178.1 jit= 79.3 12115|677fa3f4ca56
18 n=100000 Q2 mid all raised cost= 2995566 wall= 81.9 exec= 100.8 jit= 0.0 12115|677fa3f4ca56
18 n=100000 Q3 high defaults cost= 7339736 wall= 273.7 exec= 294.9 jit= 127.9 75|d7003c8d8731289
18 n=100000 Q3 high jit=off cost= 7339736 wall= 146.7 exec= 170.0 jit= 0.0 75|d7003c8d8731289
18 n=100000 Q3 high tier1 only cost= 7339736 wall= 168.3 exec= 187.5 jit= 16.2 75|d7003c8d8731289
18 n=100000 Q3 high forced cost= 7339736 wall= 273.9 exec= 294.9 jit= 128.8 75|d7003c8d8731289
18 n=100000 Q3 high all raised cost= 7339736 wall= 139.5 exec= 168.9 jit= 0.0 75|d7003c8d8731289
Sources
- PostgreSQL 18: When to JIT?
- PostgreSQL 18: What is JIT compilation?
- PostgreSQL 18: JIT configuration
- PostgreSQL 18: query planning settings (the same defaults in 16 and 17)
- PostgreSQL 18:
pg_jit_available() - PostgreSQL 18:
ALTER ROLEand libpq connection parameters - Commit "jit: Change the default to off" and the PostgreSQL 19 release notes