Using Just-In-Time (JIT) compilation

PostgreSQL’s Just-In-Time (JIT) compilation, introduced by default in PostgreSQL 11, translates SQL expressions into native machine code at runtime and gives performance improvements, especially for complex operations or large datasets. However, its benefits are most noticeable for long-running, CPU-intensive queries, typically analytical ones. On the other hand, the overhead for JIT might negate its advantages for shorter queries.

Key JIT optimizations in PostgreSQL include:

• Expression evaluation: Primarily for WHERE clauses, aggregates, and projections. This optimization generates code for specific scenarios.
• Tuple deforming: Converts on-disk tuples into in-memory formats, accelerated using functions suited for specific table layouts.

Getting ready

JIT is triggered based on the estimated query cost. If this surpasses the jit_above_cost setting, JIT is engaged. Depending on the cost, specific optimizations...