[[server]]
name = "Primary"
[server.style.Automatic]

[server.setup]
sql = """
DROP TABLE IF EXISTS documents CASCADE;
DROP TABLE IF EXISTS tenants CASCADE;
CREATE EXTENSION IF NOT EXISTS pg_search CASCADE;

CREATE TABLE tenants (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE documents (
    id BIGINT NOT NULL,
    tenant_id INTEGER NOT NULL,
    body TEXT NOT NULL,
    payload TEXT,
    PRIMARY KEY (id, tenant_id)
) PARTITION BY RANGE (tenant_id);

CREATE TABLE documents_low PARTITION OF documents FOR VALUES FROM (1) TO (51);
CREATE TABLE documents_high PARTITION OF documents FOR VALUES FROM (51) TO (101);

INSERT INTO tenants SELECT i, 'tenant ' || i FROM generate_series(1, 100) s(i);
INSERT INTO documents
SELECT i, 1 + (i % 100),
       CASE WHEN i % 2 = 0 THEN 'beer wine ' ELSE 'cheese bread ' END || i,
       'payload ' || i
FROM generate_series(1, 10000) s(i);

CREATE INDEX documents_idx ON documents USING paradedb (id, tenant_id, body) WITH (
    numeric_fields = '{"tenant_id": {"fast": true}}'
);
CREATE INDEX tenants_idx ON tenants USING paradedb (id, name);
ANALYZE documents;
ANALYZE tenants;

CREATE OR REPLACE FUNCTION assert_plan_contains(p_query text, p_expected_text text)
RETURNS boolean AS $$
DECLARE
    plan_line text;
    full_plan text := '';
BEGIN
    FOR plan_line IN EXECUTE 'EXPLAIN (VERBOSE) ' || p_query LOOP
        full_plan := full_plan || plan_line || chr(10);
        IF plan_line ILIKE '%' || p_expected_text || '%' THEN RETURN true; END IF;
    END LOOP;
    RAISE EXCEPTION 'Plan assertion failed: expected "%" not found. Actual plan:%',
        p_expected_text, chr(10) || full_plan;
END;
$$ LANGUAGE plpgsql;
"""

[server.teardown]
sql = """
DROP TABLE documents CASCADE;
DROP TABLE tenants CASCADE;
DROP EXTENSION pg_search CASCADE;
"""

[server.monitor]
refresh_ms = 100
title = "Partition Index Sizes"
log_columns = ["partition_index_size:MB"]
sql = """
SELECT sum(pg_relation_size(i.indexrelid)) AS partition_index_size
FROM pg_index i
JOIN pg_inherits p ON p.inhrelid = i.indrelid
WHERE p.inhparent = 'documents'::regclass;
"""

[[jobs]]
refresh_ms = 5
title = "Partitioned Top K Base Scan"
on_connect = """
SELECT assert_plan_contains(
    $q$ SELECT id, tenant_id, body FROM documents WHERE body ||| 'beer' ORDER BY id LIMIT 100 $q$,
    'Append'
);
SELECT assert_plan_contains(
    $q$ SELECT id, tenant_id, body FROM documents WHERE body ||| 'beer' ORDER BY id LIMIT 100 $q$,
    'TopKScanExecState'
);
"""
sql = "SELECT id, tenant_id, body FROM documents WHERE body ||| 'beer' ORDER BY id LIMIT 100;"

[[jobs]]
refresh_ms = 5
title = "Partition-pruned Base Scan"
on_connect = """
SELECT assert_plan_contains(
    $q$ SELECT id, body, payload FROM documents WHERE tenant_id < 20 AND body ||| 'wine' $q$,
    'NormalScanExecState'
);
"""
sql = "SELECT id, body, payload FROM documents WHERE tenant_id < 20 AND body ||| 'wine';"

[[jobs]]
refresh_ms = 5
title = "Postgres Aggregate over Partitioned Base Scans"
on_connect = """
SELECT assert_plan_contains(
    $q$ SELECT tenant_id, count(*) FROM documents WHERE body ||| 'beer' GROUP BY tenant_id $q$,
    'HashAggregate'
);
SELECT assert_plan_contains(
    $q$ SELECT tenant_id, count(*) FROM documents WHERE body ||| 'beer' GROUP BY tenant_id $q$,
    'ColumnarExecState'
);
"""
sql = "SELECT tenant_id, count(*) FROM documents WHERE body ||| 'beer' GROUP BY tenant_id;"

[[jobs]]
refresh_ms = 5
title = "Postgres Join over Partitioned Base Scans"
on_connect = """
SELECT assert_plan_contains(
    $q$ SELECT d.id, t.name FROM documents d JOIN tenants t ON d.tenant_id = t.id WHERE d.body ||| 'beer' LIMIT 50 $q$,
    'Nested Loop'
);
SELECT assert_plan_contains(
    $q$ SELECT d.id, t.name FROM documents d JOIN tenants t ON d.tenant_id = t.id WHERE d.body ||| 'beer' LIMIT 50 $q$,
    'TopKScanExecState'
);
"""
sql = "SELECT d.id, t.name FROM documents d JOIN tenants t ON d.tenant_id = t.id WHERE d.body ||| 'beer' LIMIT 50;"

[[jobs]]
refresh_ms = 25
title = "Partitioned Writes"
sql = """
UPDATE documents SET body = body || ' updated' WHERE id BETWEEN 1 AND 100;
"""