setup.sql
application/octet-stream
Filename: setup.sql
Type: application/octet-stream
Part: 5
-- ================================================================
-- UPDATE BENCHMARK SUITE cf-5556
-- ================================================================
-- ================================================================
-- CLEANUP
-- ================================================================
SET client_min_messages = WARNING;
DROP TABLE IF EXISTS phase1_btree_simple CASCADE;
DROP TABLE IF EXISTS phase1_hash_simple CASCADE;
DROP TABLE IF EXISTS phase1_gin_simple CASCADE;
DROP TABLE IF EXISTS phase1_brin_simple CASCADE;
DROP TABLE IF EXISTS phase2_expr_btree CASCADE;
DROP TABLE IF EXISTS phase2_partial_btree CASCADE;
DROP TABLE IF EXISTS phase2_expr_partial_combined CASCADE;
DROP TABLE IF EXISTS phase3_multi_index_light CASCADE;
DROP TABLE IF EXISTS phase3_multi_index_heavy CASCADE;
DROP TABLE IF EXISTS phase3_jsonb_gin_mixed CASCADE;
DROP TABLE IF EXISTS phase4_bulk_wide_table CASCADE;
DROP TABLE IF EXISTS phase4_bulk_narrow_table CASCADE;
DROP TABLE IF EXISTS phase5_vacuum_stress_high_churn CASCADE;
DROP TABLE IF EXISTS phase5_vacuum_stress_selective CASCADE;
DROP TABLE IF EXISTS phase6_realistic_ecommerce CASCADE;
DROP TABLE IF EXISTS phase6_realistic_timeseries CASCADE;
DROP FUNCTION IF EXISTS show_hot_stats_detailed() CASCADE;
DROP FUNCTION IF EXISTS show_table_size_stats() CASCADE;
SET client_min_messages = DEFAULT;
-- ================================================================
-- PHASE 1: INDEX TYPE FUNDAMENTALS (1M rows each)
-- ================================================================
\timing on
-- === Phase 1.1: BTREE Index ===
CREATE TABLE phase1_btree_simple (
id BIGINT PRIMARY KEY,
user_id INT NOT NULL,
status TEXT NOT NULL,
email TEXT NOT NULL,
description TEXT,
last_activity TIMESTAMP,
counter BIGINT DEFAULT 0,
data_blob TEXT
) WITH (fillfactor = 60);
CREATE INDEX idx_p1_btree_userid ON phase1_btree_simple(user_id);
CREATE INDEX idx_p1_btree_status ON phase1_btree_simple(status);
INSERT INTO phase1_btree_simple (id, user_id, status, email,
description, last_activity, data_blob)
SELECT
i,
(i % 50000) + 1,
CASE (i % 5)
WHEN 0 THEN 'active'
WHEN 1 THEN 'inactive'
WHEN 2 THEN 'pending'
WHEN 3 THEN 'suspended'
ELSE 'deleted'
END,
'user' || (i % 100000) || '@example.com',
'Description for record ' || i,
'2024-01-01'::timestamp + ((i % 30) || ' days')::interval,
repeat('x', (i % 100) + 100)
FROM generate_series(1, 1000000) i;
ANALYZE phase1_btree_simple;
-- === Phase 1.2: HASH Index ===
CREATE TABLE phase1_hash_simple (
id BIGINT PRIMARY KEY,
product_code TEXT NOT NULL,
product_name TEXT NOT NULL,
quantity INT,
last_modified TIMESTAMP,
notes TEXT,
counter BIGINT DEFAULT 0
) WITH (fillfactor = 60);
CREATE INDEX idx_p1_hash_code ON phase1_hash_simple USING hash(product_code);
INSERT INTO phase1_hash_simple (id, product_code, product_name,
quantity, last_modified, notes)
SELECT
i,
'PROD' || lpad((i % 10000)::text, 5, '0'),
'Product Name ' || i,
(i % 1000) + 1,
'2024-01-01'::timestamp + ((i % 90) || ' days')::interval,
'Notes for product ' || i
FROM generate_series(1, 1000000) i;
ANALYZE phase1_hash_simple;
-- === Phase 1.3: GIN Index ===
CREATE TABLE phase1_gin_simple (
id BIGINT PRIMARY KEY,
tags TEXT[] NOT NULL,
metadata JSONB NOT NULL,
config JSONB NOT NULL,
description TEXT,
counter BIGINT DEFAULT 0
) WITH (fillfactor = 60);
CREATE INDEX idx_p1_gin_tags ON phase1_gin_simple USING gin(tags);
CREATE INDEX idx_p1_gin_metadata ON phase1_gin_simple USING gin(metadata);
INSERT INTO phase1_gin_simple (id, tags, metadata, config, description)
SELECT
i,
ARRAY['tag' || (i % 50), 'tag' || (i % 100), 'tag' || (i % 200)],
jsonb_build_object(
'category', CASE (i % 10) WHEN 0 THEN 'A' WHEN 1 THEN 'B' ELSE 'C' END,
'priority', i % 100,
'active', (i % 2 = 0)
),
jsonb_build_object(
'setting1', 'value' || (i % 50),
'setting2', i % 1000
),
'Description ' || i
FROM generate_series(1, 1000000) i;
ANALYZE phase1_gin_simple;
-- === Phase 1.4: BRIN Index ===
CREATE TABLE phase1_brin_simple (
id BIGINT PRIMARY KEY,
ts TIMESTAMP NOT NULL,
sensor_id INT NOT NULL,
measurement NUMERIC(10,2),
raw_data TEXT,
counter BIGINT DEFAULT 0
) WITH (fillfactor = 60);
CREATE INDEX idx_p1_brin_ts ON phase1_brin_simple USING brin(ts);
CREATE INDEX idx_p1_brin_sensor ON phase1_brin_simple USING brin(sensor_id);
INSERT INTO phase1_brin_simple (id, ts, sensor_id, measurement, raw_data)
SELECT
i,
'2024-01-01'::timestamp + (i || ' seconds')::interval,
1 + (i % 1000),
(50.0 + random() * 50)::numeric(10,2),
'Sensor data ' || i
FROM generate_series(1, 1000000) i;
ANALYZE phase1_brin_simple;
-- ================================================================
-- PHASE 2: INDEX FEATURES (Expression + Partial)
-- ================================================================
-- === Phase 2.1: Expression Index ===
CREATE TABLE phase2_expr_btree (
id BIGINT PRIMARY KEY,
email TEXT NOT NULL,
username TEXT NOT NULL,
status TEXT NOT NULL,
counter BIGINT DEFAULT 0
) WITH (fillfactor = 60);
CREATE INDEX idx_p2_expr_email_lower ON phase2_expr_btree(lower(email));
CREATE INDEX idx_p2_expr_username_lower ON phase2_expr_btree(lower(username));
INSERT INTO phase2_expr_btree (id, email, username, status)
SELECT
i,
'user' || i || '@EXAMPLE.COM',
'User' || i,
CASE (i % 3) WHEN 0 THEN 'active' WHEN 1 THEN 'inactive' ELSE 'pending' END
FROM generate_series(1, 500000) i;
ANALYZE phase2_expr_btree;
-- === Phase 2.2: Partial Index ===
CREATE TABLE phase2_partial_btree (
id BIGINT PRIMARY KEY,
status TEXT NOT NULL,
priority INT NOT NULL,
value NUMERIC(10,2),
updated_at TIMESTAMP,
counter BIGINT DEFAULT 0
) WITH (fillfactor = 60);
CREATE INDEX idx_p2_partial_active_high ON phase2_partial_btree(priority)
WHERE status = 'active' AND priority > 5;
CREATE INDEX idx_p2_partial_recent ON phase2_partial_btree(updated_at)
WHERE updated_at > '2024-01-01'::timestamp + interval '30 days';
INSERT INTO phase2_partial_btree (id, status, priority, value, updated_at)
SELECT
i,
CASE WHEN i % 10 < 7 THEN 'active' ELSE 'inactive' END,
1 + (i % 10),
(100.0 + random() * 1000)::numeric(10,2),
'2024-01-01'::timestamp + ((i % 60) || ' days')::interval
FROM generate_series(1, 500000) i;
ANALYZE phase2_partial_btree;
-- === Phase 2.3: Expression + Partial Combined ===
CREATE TABLE phase2_expr_partial_combined (
id BIGINT PRIMARY KEY,
account_name TEXT NOT NULL,
account_status TEXT NOT NULL,
tier TEXT NOT NULL,
last_login TIMESTAMP,
metadata JSONB,
counter BIGINT DEFAULT 0
) WITH (fillfactor = 60);
CREATE INDEX idx_p2_expr_partial_name ON phase2_expr_partial_combined(lower(account_name))
WHERE account_status = 'active';
CREATE INDEX idx_p2_expr_partial_tier ON phase2_expr_partial_combined(lower(tier))
WHERE account_status != 'suspended';
INSERT INTO phase2_expr_partial_combined (id, account_name, account_status, tier, last_login, metadata)
SELECT
i,
'Account' || i,
CASE (i % 100)
WHEN 0 THEN 'suspended'
WHEN 1 THEN 'inactive'
ELSE 'active'
END,
CASE (i % 4) WHEN 0 THEN 'FREE' WHEN 1 THEN 'BASIC' WHEN 2 THEN 'PRO' ELSE 'ENTERPRISE' END,
'2024-01-01'::timestamp + ((i % 90) || ' days')::interval,
jsonb_build_object('created', '2024-01-01', 'version', 1)
FROM generate_series(1, 500000) i;
ANALYZE phase2_expr_partial_combined;
-- ================================================================
-- PHASE 3: MULTI-INDEX COMPLEXITY
-- ================================================================
-- === Phase 3.1: Light Multi-Index ===
CREATE TABLE phase3_multi_index_light (
id BIGINT PRIMARY KEY,
customer_id INT NOT NULL,
order_type TEXT NOT NULL,
amount NUMERIC(10,2) NOT NULL,
status TEXT NOT NULL,
created_at TIMESTAMP NOT NULL,
notes TEXT,
counter BIGINT DEFAULT 0
) WITH (fillfactor = 60);
CREATE INDEX idx_p3_light_customer ON phase3_multi_index_light(customer_id);
CREATE INDEX idx_p3_light_status ON phase3_multi_index_light(status);
CREATE INDEX idx_p3_light_created ON phase3_multi_index_light(created_at);
INSERT INTO phase3_multi_index_light (id, customer_id, order_type, amount, status, created_at, notes)
SELECT
i,
(i % 100000) + 1,
CASE (i % 3) WHEN 0 THEN 'online' WHEN 1 THEN 'instore' ELSE 'phone' END,
(10.0 + random() * 5000)::numeric(10,2),
CASE (i % 4) WHEN 0 THEN 'pending' WHEN 1 THEN 'processing' WHEN 2 THEN 'complete' ELSE 'failed' END,
'2024-01-01'::timestamp + ((i % 365) || ' days')::interval + ((i % 86400) || ' seconds')::interval,
'Order notes ' || i
FROM generate_series(1, 1000000) i;
ANALYZE phase3_multi_index_light;
-- === Phase 3.2: Heavy Multi-Index ===
CREATE TABLE phase3_multi_index_heavy (
id BIGINT PRIMARY KEY,
user_id INT NOT NULL,
email TEXT NOT NULL,
username TEXT NOT NULL,
status TEXT NOT NULL,
tier TEXT NOT NULL,
last_login TIMESTAMP,
metadata JSONB,
preferences JSONB,
counter BIGINT DEFAULT 0
) WITH (fillfactor = 60);
CREATE INDEX idx_p3_heavy_user ON phase3_multi_index_heavy(user_id);
CREATE INDEX idx_p3_heavy_email ON phase3_multi_index_heavy(email);
CREATE INDEX idx_p3_heavy_status ON phase3_multi_index_heavy(status);
CREATE INDEX idx_p3_heavy_tier ON phase3_multi_index_heavy(tier);
CREATE INDEX idx_p3_heavy_email_lower ON phase3_multi_index_heavy(lower(email));
CREATE INDEX idx_p3_heavy_username_lower ON phase3_multi_index_heavy(lower(username));
CREATE INDEX idx_p3_heavy_metadata ON phase3_multi_index_heavy USING gin(metadata);
CREATE INDEX idx_p3_heavy_preferences ON phase3_multi_index_heavy USING gin(preferences);
INSERT INTO phase3_multi_index_heavy (id, user_id, email, username, status, tier, last_login, metadata, preferences)
SELECT
i,
(i % 100000) + 1,
'user' || i || '@domain' || (i % 1000) || '.com',
'user' || i,
CASE (i % 5) WHEN 0 THEN 'active' WHEN 1 THEN 'inactive' ELSE 'pending' END,
CASE (i % 4) WHEN 0 THEN 'free' WHEN 1 THEN 'basic' WHEN 2 THEN 'pro' ELSE 'enterprise' END,
'2024-01-01'::timestamp + ((i % 90) || ' days')::interval,
jsonb_build_object('created', '2024-01-01', 'region', CASE (i % 5) WHEN 0 THEN 'US' WHEN 1 THEN 'EU' WHEN 2 THEN 'APAC' ELSE 'LATAM' END),
jsonb_build_object('theme', CASE (i % 2) WHEN 0 THEN 'dark' ELSE 'light' END, 'lang', 'en')
FROM generate_series(1, 500000) i;
ANALYZE phase3_multi_index_heavy;
-- === Phase 3.3: JSONB-Heavy GIN ===
CREATE TABLE phase3_jsonb_gin_mixed (
id BIGINT PRIMARY KEY,
config JSONB NOT NULL,
settings JSONB NOT NULL,
metadata JSONB NOT NULL,
tags TEXT[] NOT NULL,
description TEXT,
counter BIGINT DEFAULT 0
) WITH (fillfactor = 60);
CREATE INDEX idx_p3_gin_config ON phase3_jsonb_gin_mixed USING gin(config);
CREATE INDEX idx_p3_gin_settings ON phase3_jsonb_gin_mixed USING gin(settings);
CREATE INDEX idx_p3_gin_metadata ON phase3_jsonb_gin_mixed USING gin(metadata);
CREATE INDEX idx_p3_gin_tags ON phase3_jsonb_gin_mixed USING gin(tags);
INSERT INTO phase3_jsonb_gin_mixed (id, config, settings, metadata, tags, description)
SELECT
i,
jsonb_build_object(
'option1', i % 100,
'option2', 'value' || (i % 50),
'enabled', (i % 2 = 0),
'level', i % 10
),
jsonb_build_object(
'timeout', 30 + (i % 60),
'retries', i % 5,
'mode', CASE (i % 3) WHEN 0 THEN 'fast' WHEN 1 THEN 'balanced' ELSE 'safe' END
),
jsonb_build_object(
'created', '2024-01-01',
'type', CASE (i % 5) WHEN 0 THEN 'A' WHEN 1 THEN 'B' WHEN 2 THEN 'C' WHEN 3 THEN 'D' ELSE 'E' END,
'version', 1
),
ARRAY['tag' || (i % 20), 'tag' || (i % 30)],
'Description ' || i
FROM generate_series(1, 500000) i;
ANALYZE phase3_jsonb_gin_mixed;
-- ================================================================
-- PHASE 4: BULK OPERATIONS
-- ================================================================
-- === Phase 4.1: Wide Table ===
CREATE TABLE phase4_bulk_wide_table (
id BIGINT PRIMARY KEY,
col1 TEXT, col2 TEXT, col3 TEXT, col4 TEXT, col5 TEXT,
col6 INT, col7 INT, col8 INT, col9 INT, col10 INT,
col11 NUMERIC(10,2), col12 NUMERIC(10,2), col13 NUMERIC(10,2), col14 NUMERIC(10,2), col15 NUMERIC(10,2),
indexed_col TEXT NOT NULL,
counter BIGINT DEFAULT 0
) WITH (fillfactor = 50);
CREATE INDEX idx_p4_wide_indexed ON phase4_bulk_wide_table(indexed_col);
INSERT INTO phase4_bulk_wide_table (id, col1, col2, col3, col4, col5, col6, col7, col8, col9, col10,
col11, col12, col13, col14, col15, indexed_col)
SELECT
i,
'val' || i, 'val' || i, 'val' || i, 'val' || i, 'val' || i,
i % 100, i % 200, i % 300, i % 400, i % 500,
(100 + random() * 1000)::numeric(10,2),
(100 + random() * 1000)::numeric(10,2),
(100 + random() * 1000)::numeric(10,2),
(100 + random() * 1000)::numeric(10,2),
(100 + random() * 1000)::numeric(10,2),
'key_' || (i % 10000)
FROM generate_series(1, 500000) i;
ANALYZE phase4_bulk_wide_table;
-- === Phase 4.2: Narrow Table ===
CREATE TABLE phase4_bulk_narrow_table (
id BIGINT PRIMARY KEY,
indexed_id INT NOT NULL,
value NUMERIC(10,2),
counter BIGINT DEFAULT 0
) WITH (fillfactor = 60);
CREATE INDEX idx_p4_narrow_indexed_id ON phase4_bulk_narrow_table(indexed_id);
INSERT INTO phase4_bulk_narrow_table (id, indexed_id, value)
SELECT
i,
(i % 100000) + 1,
(100 + random() * 1000)::numeric(10,2)
FROM generate_series(1, 2000000) i;
ANALYZE phase4_bulk_narrow_table;
-- ================================================================
-- PHASE 5: VACUUM STRESS TESTING
-- ================================================================
-- === Phase 5.1: High Churn ===
CREATE TABLE phase5_vacuum_stress_high_churn (
id BIGINT PRIMARY KEY,
session_id UUID NOT NULL,
user_id INT NOT NULL,
event_type TEXT NOT NULL,
event_data JSONB,
created_at TIMESTAMP NOT NULL,
counter BIGINT DEFAULT 0
) WITH (fillfactor = 40);
CREATE INDEX idx_p5_churn_session ON phase5_vacuum_stress_high_churn(session_id);
CREATE INDEX idx_p5_churn_user ON phase5_vacuum_stress_high_churn(user_id);
CREATE INDEX idx_p5_churn_created ON phase5_vacuum_stress_high_churn(created_at);
INSERT INTO phase5_vacuum_stress_high_churn (id, session_id, user_id, event_type, event_data, created_at)
SELECT
i,
md5(((i / 1000)::text) || 'session')::uuid,
(i % 50000) + 1,
CASE (i % 10) WHEN 0 THEN 'click' WHEN 1 THEN 'view' WHEN 2 THEN 'scroll' ELSE 'interaction' END,
jsonb_build_object('x', i % 1000, 'y', i % 768, 'duration', random() * 10),
'2024-01-01'::timestamp + ((i % 86400) || ' seconds')::interval
FROM generate_series(1, 1000000) i;
ANALYZE phase5_vacuum_stress_high_churn;
-- === Phase 5.2: Selective Updates ===
CREATE TABLE phase5_vacuum_stress_selective (
id BIGINT PRIMARY KEY,
category TEXT NOT NULL,
status TEXT NOT NULL,
value NUMERIC(10,2),
attempts INT DEFAULT 0,
last_attempt TIMESTAMP,
counter BIGINT DEFAULT 0
) WITH (fillfactor = 60);
CREATE INDEX idx_p5_selective_category ON phase5_vacuum_stress_selective(category);
CREATE INDEX idx_p5_selective_status ON phase5_vacuum_stress_selective(status);
INSERT INTO phase5_vacuum_stress_selective (id, category, status, value)
SELECT
i,
CASE (i % 10) WHEN 0 THEN 'hot' ELSE 'cold' END,
CASE (i % 3) WHEN 0 THEN 'pending' WHEN 1 THEN 'processing' ELSE 'complete' END,
(100 + random() * 1000)::numeric(10,2)
FROM generate_series(1, 500000) i;
ANALYZE phase5_vacuum_stress_selective;
-- ================================================================
-- PHASE 6: REALISTIC WORKLOADS
-- ================================================================
-- === Phase 6.1: E-Commerce ===
CREATE TABLE phase6_realistic_ecommerce (
id BIGINT PRIMARY KEY,
order_id TEXT NOT NULL,
customer_id INT NOT NULL,
product_id INT NOT NULL,
quantity INT NOT NULL,
unit_price NUMERIC(10,2) NOT NULL,
total_price NUMERIC(10,2) NOT NULL,
status TEXT NOT NULL,
payment_method TEXT,
shipping_address JSONB,
order_metadata JSONB,
created_at TIMESTAMP NOT NULL,
updated_at TIMESTAMP,
counter BIGINT DEFAULT 0
) WITH (fillfactor = 60);
CREATE INDEX idx_p6_ecom_order_id ON phase6_realistic_ecommerce(order_id);
CREATE INDEX idx_p6_ecom_customer ON phase6_realistic_ecommerce(customer_id);
CREATE INDEX idx_p6_ecom_status ON phase6_realistic_ecommerce(status);
CREATE INDEX idx_p6_ecom_created ON phase6_realistic_ecommerce(created_at);
CREATE INDEX idx_p6_ecom_metadata ON phase6_realistic_ecommerce USING gin(order_metadata);
CREATE INDEX idx_p6_ecom_expr_status ON phase6_realistic_ecommerce(lower(status))
WHERE status NOT IN ('archived', 'deleted');
INSERT INTO phase6_realistic_ecommerce (id, order_id, customer_id, product_id, quantity, unit_price,
total_price, status, payment_method, shipping_address, order_metadata, created_at, updated_at)
SELECT
i,
'ORD' || lpad((i % 1000000)::text, 7, '0'),
(i % 100000) + 1,
(i % 50000) + 1,
1 + (i % 10),
(9.99 + random() * 990)::numeric(10,2),
((9.99 + random() * 990) * (1 + i % 10))::numeric(10,2),
CASE (i % 6) WHEN 0 THEN 'pending' WHEN 1 THEN 'processing' WHEN 2 THEN 'shipped'
WHEN 3 THEN 'delivered' WHEN 4 THEN 'returned' ELSE 'archived' END,
CASE (i % 3) WHEN 0 THEN 'credit_card' WHEN 1 THEN 'paypal' ELSE 'apple_pay' END,
jsonb_build_object(
'street', i || ' Main St',
'city', CASE (i % 5) WHEN 0 THEN 'NYC' WHEN 1 THEN 'LA' WHEN 2 THEN 'Chicago' ELSE 'Houston' END,
'zip', lpad((i % 99999)::text, 5, '0')
),
jsonb_build_object(
'source', CASE (i % 2) WHEN 0 THEN 'web' ELSE 'mobile' END,
'campaign', 'camp_' || (i % 100),
'promo_code', CASE WHEN i % 10 = 0 THEN 'PROMO' || (i % 100) ELSE NULL END
),
'2024-01-01'::timestamp + ((i % 365) || ' days')::interval,
'2024-01-01'::timestamp + ((i % 365) || ' days')::interval + ((i % 1000) || ' seconds')::interval
FROM generate_series(1, 1000000) i;
ANALYZE phase6_realistic_ecommerce;
-- === Phase 6.2: Time-Series ===
CREATE TABLE phase6_realistic_timeseries (
id BIGINT PRIMARY KEY,
sensor_id INT NOT NULL,
measurement_time TIMESTAMP NOT NULL,
value NUMERIC(10,2) NOT NULL,
status TEXT NOT NULL,
quality_flags INT DEFAULT 0,
derived_values JSONB,
counter BIGINT DEFAULT 0
) WITH (fillfactor = 60);
CREATE INDEX idx_p6_ts_sensor ON phase6_realistic_timeseries(sensor_id);
CREATE INDEX idx_p6_ts_time ON phase6_realistic_timeseries(measurement_time);
CREATE INDEX idx_p6_ts_sensor_time ON phase6_realistic_timeseries(sensor_id, measurement_time);
CREATE INDEX idx_p6_ts_brin ON phase6_realistic_timeseries USING brin(measurement_time);
INSERT INTO phase6_realistic_timeseries (id, sensor_id, measurement_time, value, status, quality_flags, derived_values)
SELECT
i,
(i % 1000) + 1,
'2024-01-01'::timestamp + (i || ' seconds')::interval,
(20 + random() * 30)::numeric(10,2),
CASE WHEN random() < 0.9 THEN 'ok' WHEN random() < 0.95 THEN 'warning' ELSE 'error' END,
CASE WHEN random() < 0.95 THEN 0 ELSE 1 END,
jsonb_build_object(
'min', (20 + random() * 10)::numeric(10,2),
'max', (40 + random() * 10)::numeric(10,2),
'avg', (25 + random() * 20)::numeric(10,2)
)
FROM generate_series(1, 2000000) i;
ANALYZE phase6_realistic_timeseries;
-- ================================================================
-- UTILITY FUNCTIONS (Used by report.sql)
-- ================================================================
CREATE OR REPLACE FUNCTION show_hot_stats_detailed()
RETURNS TABLE (
table_name TEXT,
total_rows BIGINT,
total_updates BIGINT,
hot_updates BIGINT,
hot_percentage NUMERIC(5,2),
total_deletes BIGINT,
table_size_mb NUMERIC(10,2)
) AS $$
BEGIN
RETURN QUERY
SELECT
t.relname::TEXT,
t.n_live_tup,
t.n_tup_upd,
t.n_tup_hot_upd,
CASE
WHEN t.n_tup_upd > 0 THEN
ROUND((t.n_tup_hot_upd::numeric / t.n_tup_upd::numeric) * 100, 2)
ELSE 0.00
END,
t.n_tup_del,
ROUND(pg_total_relation_size(t.relid)::numeric / 1024 / 1024, 2)
FROM pg_stat_user_tables t
WHERE schemaname = 'public'
AND relname LIKE 'phase%'
ORDER BY t.relname;
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION show_table_size_stats()
RETURNS TABLE (
table_name TEXT,
heap_size_mb NUMERIC(10,2),
indexes_size_mb NUMERIC(10,2),
total_size_mb NUMERIC(10,2)
) AS $$
BEGIN
RETURN QUERY
SELECT
t.relname::TEXT,
ROUND(pg_relation_size(t.oid)::numeric / 1024 / 1024, 2),
ROUND(pg_indexes_size(t.oid)::numeric / 1024 / 1024, 2),
ROUND(pg_total_relation_size(t.oid)::numeric / 1024 / 1024, 2)
FROM pg_class t
WHERE t.relkind = 'r'
AND t.relnamespace = 'public'::regnamespace
AND t.relname LIKE 'phase%'
ORDER BY t.relname;
END;
$$ LANGUAGE plpgsql;
-- ================================================================
-- FINAL SETUP
-- ================================================================
VACUUM ANALYZE;
\echo ''
\echo '════════════════════════════════════════════════════════════'
\echo ' HOT Update Benchmark Suite v2.0 - Setup Complete'
\echo '════════════════════════════════════════════════════════════'
\echo ''
\echo ' Phase 1: Index Type Fundamentals (4 tables, ~4M rows)'
\echo ' Phase 2: Index Features (3 tables, ~1.5M rows)'
\echo ' Phase 3: Multi-Index Complexity (3 tables, ~2M rows)'
\echo ' Phase 4: Bulk Operations (2 tables, ~2.5M rows)'
\echo ' Phase 5: Vacuum Stress Testing (2 tables, ~1.5M rows)'
\echo ' Phase 6: Realistic Workloads (2 tables, ~3M rows)'
\echo ''
\echo ' Total: 22 tables with ~18M rows'
\echo ''
\echo ' Next: Run pgbench with hot_test.sql'
\echo ' Then: Run report.sql for analysis'
\echo ''
\echo '════════════════════════════════════════════════════════════'
\echo ''