\set ON_ERROR_STOP on DROP TABLE IF EXISTS benchmark_write_results; CREATE TABLE benchmark_write_results ( variant TEXT NOT NULL, run_number INTEGER NOT NULL, execution_ms DOUBLE PRECISION NOT NULL ); CREATE OR REPLACE PROCEDURE benchmark_write_repeat() LANGUAGE plpgsql AS $$ DECLARE i INTEGER; v TEXT; variants TEXT[]; started_at TIMESTAMPTZ; elapsed_ms DOUBLE PRECISION; BEGIN FOR i IN 1..5 LOOP -- Чередуем порядок вариантов. IF i % 2 = 1 THEN variants := ARRAY['simple', 'composite']; ELSE variants := ARRAY['composite', 'simple']; END IF; FOREACH v IN ARRAY variants LOOP -- Каждая вставка начинается с пустой таблицы. IF v = 'simple' THEN TRUNCATE TABLE bench_simple; started_at := clock_timestamp(); INSERT INTO bench_simple SELECT * FROM bench_insert_data; ELSE TRUNCATE TABLE bench_composite; started_at := clock_timestamp(); INSERT INTO bench_composite SELECT * FROM bench_insert_data; END IF; elapsed_ms := EXTRACT(EPOCH FROM (clock_timestamp() - started_at)) * 1000; INSERT INTO benchmark_write_results VALUES (v, i, elapsed_ms); END LOOP; END LOOP; END; $$; CALL benchmark_write_repeat(); SELECT variant, run_number, ROUND(execution_ms::numeric, 3) AS execution_ms FROM benchmark_write_results ORDER BY run_number, variant; SELECT variant, ROUND( percentile_cont(0.5) WITHIN GROUP (ORDER BY execution_ms)::numeric, 3 ) AS median_ms, ROUND(MIN(execution_ms)::numeric, 3) AS min_ms, ROUND(MAX(execution_ms)::numeric, 3) AS max_ms FROM benchmark_write_results GROUP BY variant ORDER BY variant; -- Контроль количества записей и размера индексов. SELECT 'simple' AS variant, COUNT(*) AS rows_count, pg_size_pretty(pg_indexes_size('bench_simple')) AS indexes_size FROM bench_simple UNION ALL SELECT 'composite', COUNT(*), pg_size_pretty(pg_indexes_size('bench_composite')) FROM bench_composite;