jit_benchmark.sql

-- Benchmark: JIT slower than no-JIT even with jit_tuple_deforming = off
-- Many partitions, few rows per partition -> many JIT functions compiled,
-- each executed briefly. Partition costs sum up to exceed JIT thresholds.

\timing on

---------------------------------------------------------------
-- Setup: 500 partitions, 10k rows each = 5M rows
-- Per-partition seq scan cost ~284 -> total ~142k (above jit_above_cost,
-- below jit_optimize_above_cost -> O0)
---------------------------------------------------------------
DROP TABLE IF EXISTS bench_jit;
CREATE TABLE bench_jit (
    id int,
    part_id int,
    a int, b int, c int, d int,
    t1 text, t2 text
) PARTITION BY LIST (part_id);

DO $$
BEGIN
    FOR i IN 0..499 LOOP
        EXECUTE format(
            'CREATE TABLE bench_jit_p%s PARTITION OF bench_jit FOR VALUES IN (%s)',
            i, i
        );
    END LOOP;
END;
$$;

INSERT INTO bench_jit
SELECT i, i % 500, i % 10000, i % 5000, i % 3000, i % 2000,
       'val_' || (i % 1000), 'str_' || (i % 100)
FROM generate_series(1, 5000000) i;

ANALYZE bench_jit;

SET max_parallel_workers_per_gather = 0;

---------------------------------------------------------------
-- Test 1: 500 partitions, few rows returned per partition
---------------------------------------------------------------
\echo '=== Test 1: 500 partitions - No JIT ==='
SET jit = off;

EXPLAIN (ANALYZE, SUMMARY, TIMING OFF) SELECT
    CASE WHEN a % 7 = 0 THEN b + c WHEN a % 7 = 1 THEN c + d ELSE d + a END,
    CASE WHEN b % 5 = 0 THEN 'zero' WHEN b % 5 = 1 THEN 'one' ELSE 'other' END,
    a + b + c + d,
    t1 || ' ' || t2
FROM bench_jit
WHERE a BETWEEN 0 AND 2 AND c BETWEEN 0 AND 2;

\echo '=== Test 1: 500 partitions - JIT on, tuple_deforming off ==='
SET jit = on;
SET jit_tuple_deforming = off;

EXPLAIN (ANALYZE, SUMMARY, TIMING OFF) SELECT
    CASE WHEN a % 7 = 0 THEN b + c WHEN a % 7 = 1 THEN c + d ELSE d + a END,
    CASE WHEN b % 5 = 0 THEN 'zero' WHEN b % 5 = 1 THEN 'one' ELSE 'other' END,
    a + b + c + d,
    t1 || ' ' || t2
FROM bench_jit
WHERE a BETWEEN 0 AND 2 AND c BETWEEN 0 AND 2;

---------------------------------------------------------------
-- Now scale up: 2000 partitions to cross jit_optimize_above_cost
---------------------------------------------------------------
DROP TABLE IF EXISTS bench_jit;
CREATE TABLE bench_jit (
    id int,
    part_id int,
    a int, b int, c int, d int,
    t1 text, t2 text
) PARTITION BY LIST (part_id);

DO $$
BEGIN
    FOR i IN 0..1999 LOOP
        EXECUTE format(
            'CREATE TABLE bench_jit_p%s PARTITION OF bench_jit FOR VALUES IN (%s)',
            i, i
        );
    END LOOP;
END;
$$;

INSERT INTO bench_jit
SELECT i, i % 2000, i % 10000, i % 5000, i % 3000, i % 2000,
       'val_' || (i % 1000), 'str_' || (i % 100)
FROM generate_series(1, 20000000) i;

ANALYZE bench_jit;

SET max_parallel_workers_per_gather = 0;

---------------------------------------------------------------
-- Test 2: 2000 partitions, O2 triggered
---------------------------------------------------------------
\echo '=== Test 2: 2000 partitions - No JIT ==='
SET jit = off;

EXPLAIN (ANALYZE, SUMMARY, TIMING OFF) SELECT
    CASE WHEN a % 7 = 0 THEN b + c WHEN a % 7 = 1 THEN c + d ELSE d + a END,
    CASE WHEN b % 5 = 0 THEN 'zero' WHEN b % 5 = 1 THEN 'one' ELSE 'other' END,
    a + b + c + d,
    t1 || ' ' || t2
FROM bench_jit
WHERE a BETWEEN 0 AND 2 AND c BETWEEN 0 AND 2;

\echo '=== Test 2: 2000 partitions - JIT on, tuple_deforming off ==='
SET jit = on;
SET jit_tuple_deforming = off;

EXPLAIN (ANALYZE, SUMMARY, TIMING OFF) SELECT
    CASE WHEN a % 7 = 0 THEN b + c WHEN a % 7 = 1 THEN c + d ELSE d + a END,
    CASE WHEN b % 5 = 0 THEN 'zero' WHEN b % 5 = 1 THEN 'one' ELSE 'other' END,
    a + b + c + d,
    t1 || ' ' || t2
FROM bench_jit
WHERE a BETWEEN 0 AND 2 AND c BETWEEN 0 AND 2;

---------------------------------------------------------------
-- Cleanup
---------------------------------------------------------------
-- DROP TABLE bench_jit;