9.1 From Row-at-a-Time Interpretation to Expression JIT

Query execution time can be spent on I/O, hash/sort, tuple decoding, or on evaluating expressions themselves. Only when interpretation and expression evaluation consume a significant portion of the runtime does bytecode or JIT compilation begin to justify the overhead of compilation. Attributing all slow queries to virtual function overhead often misidentifies the root cause.

Learning Objectives

• Identify the CPU overhead sources associated with iterator/expression evaluation;
• Distinguish between AST walk, bytecode VM, specialized functions, and JIT compilation;
• Understand the scope and limitations of PostgreSQL's JIT compiler;
• Calculate the break-even point between compilation cost and execution savings;
• Design an experiment that can differentiate between I/O-bound and CPU-bound workloads.

Two Levels of Interpretation

Operator dispatch

Partial row-at-a-time iterator interface; the executor supplies Row:

struct Operator {
    virtual ~Operator() = default;
    virtual bool next(Row& output) = 0;
};

A single row passes through Scan → Filter → Project, potentially triggering multiple indirect calls, state checks, and branches.

Expression evaluation

Predicates may also be interpreted via tree traversal:

AND
├─ greater_than
│  ├─ column(value_cents)
│  └─ constant(50000)
└─ equals
   ├─ column(type_id)
   └─ constant(1)

Each row traverses nodes, dispatches opcodes or functions, and checks for NULL or type mismatches. Even when operators are batched, the complex expression interpreter still incurs runtime cost.

Measure Indirect Calls in Context

The cost of an indirect branch depends on the CPU, prediction history, inlining, cache state, compiler optimizations, security mitigations, and surrounding work. Cache misses, string comparisons, hashing, and decompression in a query often cost far more than a single function call.

Correct measurement includes:

rows/batches processed
cycles, instructions, IPC
branch misses
L1/LLC misses
expression time vs operator time
I/O wait
JIT generation time

Use production-level profiling tools like perf with proper permissions and overhead considerations to evaluate performance. Do not rely on "row count × guessed nanoseconds" as a factual baseline.

Path 1: AST/tree interpreter

Advantages: Simple to implement, easy to add dynamic types or functions, and low startup overhead for planning. Disadvantages: Each value requires repeated node traversal and dispatch.

Best suited for short queries, small datasets, or scenarios with complex control flow or low compile cache hit rates.

Path Two: Bytecode VM

Lower the expression tree into linear instructions:

LOAD_COLUMN value_cents
LOAD_CONST  50000
GREATER_THAN_INT64
LOAD_COLUMN type_id
LOAD_CONST  1
EQUALS_INT32
AND_3VL
RETURN

The VM loop is more continuous than a pointer-rich AST, enabling switch-based or direct-threaded dispatch, with constant folding and opcode selection during lowering. It remains interpreted, but startup time is smaller than native JIT, making it a common intermediate layer.

SQL NULL requires three-valued logic; AND_3VL cannot be reduced to ordinary boolean AND.

Path 3: Specialization/Template

The engine can use precompiled templates or function pointers for common type/operator combinations. This is a declaration only; it requires <cstddef>, <cstdint>, and an implementation:

template <typename T>
std::size_t select_greater_than(
    const T* values,
    const std::uint64_t* validity,
    std::size_t size,
    T threshold,
    std::uint32_t* selected);

This avoids runtime type switching, but a large number of combinations can increase the binary and code cache size. Dynamic UDFs and collations still require a generic path.

Path 4: JIT Native Code

JIT compiles machine code based on bound types, constants, NULLability, and expression trees:

for each tuple/batch:
  decode known columns
  if valid(value) and value > 50000
     and valid(type) and type == 1:
       emit/probe/aggregate

Potential benefits:

• Eliminates expression-node dispatch;
• Enables inlining of operators and functions;
• Performs constant folding and dead branch elimination;
• Specializes NULL and no-NULL execution paths;
• Exposes loops to compiler-level optimizations.

Costs: IR construction, optimization, machine-code emission, memory protection, instruction-cache usage and code size, cache key management, and invalidation.

The Actual Scope of PostgreSQL JIT

PostgreSQL's LLVM JIT primarily accelerates:

• expression evaluation;
• tuple deformation (splitting on-page tuple into Datums/attributes);
• Function inlining and LLVM optimizations (triggered when cost thresholds are reached).

It doesn't fuse the entire physical plan and each node into a single native function. The scan/join/sort framework is still driven by the executor.

EXPLAIN (ANALYZE, BUFFERS, SETTINGS, SUMMARY ON)
SELECT SUM(value_cents * quantity)
FROM line_items
WHERE status = 'settled';

This PostgreSQL plan fragment requires an existing line_items(value_cents, quantity, status) table. JIT must be enabled, its implementation available, and the estimated cost above the relevant threshold before the plan reports its functions, options, and timing. jit=on alone does not guarantee use on this query. See PostgreSQL JIT decisions.

Don't casually SET jit_above_cost in a shared production session for demos. First, compare JIT on/off in an isolated environment, and document the current server defaults/settings.

Break-even

Let:

• compile/generation cost C;
• interpreted per-row cost I;
• compiled per-row cost J;
• processed rows N.

A necessary approximation for JIT to be beneficial is:

C + N×J < N×I
N > C / (I - J)

This derivation requires I > J. If compiled per-row work is no cheaper, more rows cannot recover a positive compile cost. With assumed C=10 ms and I-J=50 ns, net savings begin above 200000 rows. This is a calculation, not a hardware measurement.

However, I-J is influenced by expression complexity, caching behavior, and vectorization; the actual number of rows processed by the plan may also deviate from the estimate. The rule of thumb ("enable JIT for queries over 1 second, disable for those under 50 ms") lacks cross-system validity.

Only systems that actually reuse generated code can amortize C across executions. PostgreSQL prepared statements can cache plans without caching generated LLVM functions; its JIT implementation notes state that generated functions cannot yet be cached across executions. Record compilation and execution separately on repeated runs.

Scenarios Where JIT Might Be Slower

• OLTP point queries where execution time is shorter than compilation time;
• I/O, lock, or network-bound operations;
• Simple expressions that the compiler cannot optimize or eliminate;
• Very few rows or early predicate pruning;
• Frequent code cache churn and instruction cache pressure;
• Many concurrent compiles competing for CPU and memory resources;
• Generated code that is overly specialized with frequent parameter changes.

A Diagnostic Experiment

On replicated or isolated data:

1. Select a query that is CPU-intensive, has sufficient rows, and does not involve lock waits;
2. Warm up or explicitly report cold cache conditions;
3. Preserve EXPLAIN (ANALYZE, BUFFERS, SETTINGS);
4. Repeat JIT off and on multiple times, distinguishing first runs, later runs, and code reuse that has actually been demonstrated;
5. Record code-generation time, execution time, CPU counters, and result hashes;
6. Verify that results are consistent with floating-point and overflow semantics;
7. Test representative parameter values, rather than a single high-impact value alone.

Lesson Summary

Query compilation first addresses the CPU overhead of expression evaluation and tuple decoding. Bytecode, templates, and JIT each represent different trade-offs between startup time and steady-state performance. In the next lesson, we expand the scope to operator pipelines, explaining how push-based code generation enables operator fusion across stages, and why sort/hash-build operations still break the pipeline.