Query Performance

Examples on this page use this demo table:

CREATE TABLE orders (id BIGINT PRIMARY KEY, customer_id BIGINT, amount DOUBLE PRECISION,
                     status TEXT, created_at TIMESTAMP);

By the end of this page you will know how to use JIT compilation, statistics, indexes, partitioning, and automatic zone/segment skipping, and how to verify each one is actually helping your query.

Every technique on this page reduces to the same verification approach in the end: run the query, look at EXPLAIN, compare timing before and after. This page tells you which of those two tools actually shows the effect for each technique, because not every technique is visible in EXPLAIN.

JIT compilation

[general]
jit_enabled = true

[jit]
compiled_cache_memory = "512MB"
compiled_cache_disk = "1GB"

jit_enabled is true by default. ScramDB compiles hot regions of query bytecode to native machine code in the background. A query starts running on the bytecode interpreter immediately and later calls transition to compiled code once the JIT finishes, a query never blocks waiting for compilation.

compiled_cache_memory and compiled_cache_disk bound the compiled-artifact cache. Raise both together on a workload with enough distinct query shapes (many different query text or plan shapes) to thrash the defaults, evicting and recompiling code that would otherwise stay cached. As a concrete reference point, ScramDB's own 22-query TPC-H benchmark raises these to "4GB" and "8GB" specifically to stop eviction churn during a full benchmark run.

Verifying JIT engaged: EXPLAIN does not show which execution tier ran a query, there is no JIT/VM/GPU indicator in the plan output. Use the /metrics scrape-delta pattern instead:

curl -s http://127.0.0.1:9090/metrics | grep -E 'scramdb_chunks_(jit|vm)_total' and note the two counter values.
Run your query several times (the first run compiles; later runs use the compiled code).
Scrape again and diff.

Expected: scramdb_chunks_jit_total grows across the later runs while scramdb_chunks_vm_total stops growing (or grows much more slowly), meaning execution shifted from the interpreter to compiled code.

Statistics

ANALYZE TABLE orders;

[statistics]
auto_analyze_check_interval = "120s"
auto_analyze_staleness_ratio = 0.10
auto_analyze_sample_pages = 64
mcv_entries = 100
column_pairs = 50
analyze_on_load = true

An auto-analyze background worker keeps statistics fresh automatically:

Key	Default	What it controls
`auto_analyze_check_interval`	`"120s"`	How often the background worker checks whether a table's statistics are stale.
`auto_analyze_staleness_ratio`	`0.10`	A table re-analyzes once its row count has drifted by this fraction (10%) since the last analysis.
`auto_analyze_sample_pages`	`64`	Pages sampled per analysis pass.
`mcv_entries`	`100`	Most-common-value entries retained per column.
`column_pairs`	`50`	Number of column pairs tracked for functional-dependency correlation.
`analyze_on_load`	`true`	Fires an immediate full analysis right after `COPY` or a large `INSERT` completes, without waiting for the next staleness check.

If a plan looks wrong immediately after a large load that has not yet tripped the staleness check, run ANALYZE TABLE <name> directly rather than waiting.

Verifying it worked: EXPLAIN reports estimated row counts per plan node. Run it before and after ANALYZE TABLE on a table whose row count changed significantly, and compare the estimate to the real row count (cross-check with EXPLAIN ANALYZE, which reports actual rows alongside the estimate).

EXPLAIN SELECT * FROM orders WHERE status = 'shipped';
ANALYZE TABLE orders;
EXPLAIN SELECT * FROM orders WHERE status = 'shipped';

Expected: the estimated row count in the second EXPLAIN is closer to reality than the first.

Indexes

CREATE INDEX idx_orders_customer ON orders (customer_id);

An index converts a full table scan into a lookup for selective predicates on the indexed column. Verify with EXPLAIN before and after creating the index:

EXPLAIN SELECT * FROM orders WHERE status = 'pending';
CREATE INDEX idx_orders_status ON orders (status);
EXPLAIN SELECT * FROM orders WHERE status = 'pending';

Expected: the plan shape or estimated cost changes to reflect an index lookup rather than a full scan. Cross-check with \timing for the actual wall-clock difference.

Partitioning

CREATE TABLE events (
  id bigint,
  event_time timestamp,
  payload text
) PARTITION BY RANGE (event_time);

CREATE TABLE events_2026_01 PARTITION OF events
  FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');

PARTITION BY RANGE, PARTITION BY LIST, and PARTITION BY HASH are all supported, along with PARTITION OF ... FOR VALUES FROM ... TO ..., ATTACH PARTITION, and DETACH PARTITION. See SQL Reference: Statements for the full DDL grammar.

Verifying partition pruning worked: unlike JIT tier and zone-map pruning, this one is directly visible in EXPLAIN. A pruned-out child partition's name never appears in the plan tree at all, only partitions that could actually contain matching rows show up.

EXPLAIN SELECT * FROM events WHERE event_time >= '2026-01-15' AND event_time < '2026-01-20';

Expected: only events_2026_01 (and any other partition whose range overlaps the predicate) appears in the plan. A partition for a different month is absent entirely, not present-but-marked-skipped.

Zone and segment skipping

ScramDB automatically maintains min/max range metadata per storage page (a zone map) and a page-level Bloom filter, both used to skip pages that cannot contain matching rows for a predicate. This is always on and requires no configuration to benefit from. The Bloom filter's size is tunable:

[storage]
bloom_max_bytes = "1KB"

Verifying it worked: there is no pruned-pages counter in EXPLAIN output or on /metrics, this is genuinely invisible as a direct measurement today. The only way to observe the effect is indirect, via timing: a selective range or equality predicate should scan measurably faster than a full table scan of the same table, roughly proportional to the fraction of pages pruned. Treat this as a latency-based inference, not a direct measurement.

psql -h 127.0.0.1 -p 5432 -c '\timing on' -c "SELECT count(*) FROM events;"
psql -h 127.0.0.1 -p 5432 -c '\timing on' -c "SELECT count(*) FROM events WHERE event_time >= '2026-01-15' AND event_time < '2026-01-16';"

Expected: the second query, over a narrow time slice, is faster than the first in proportion to how much of the table it actually excludes, not just proportional to the row count difference (some of that speedup comes from page skipping, not just reading less matching data).

General measurement recipe

For any of the above, the reliable "did this help" recipe is warmup runs followed by timed runs, taking the median:

Run the query 3-5 times to let JIT compile and caches warm (latency should stabilize).
Run it 3+ more times with \timing on.
Take the median of the timed runs, not the first one.

This is the same pattern ScramDB's own benchmark tooling uses internally.

See GPU Acceleration for offloading large batch operations, or Observability for the full /metrics reference used throughout this page.

JIT compilation​

Statistics​

Indexes​

Partitioning​

Zone and segment skipping​

General measurement recipe​

Next​