Files
mtusi2026lab2/benchmark_read_repeat.sql
2026-10-13 15:27:39 +03:00

88 lines
2.1 KiB
PL/PgSQL

\set ON_ERROR_STOP on
DROP TABLE IF EXISTS benchmark_read_results;
CREATE TABLE benchmark_read_results (
variant TEXT NOT NULL,
query_type TEXT NOT NULL,
run_number INTEGER NOT NULL,
execution_ms DOUBLE PRECISION NOT NULL
);
CREATE OR REPLACE PROCEDURE benchmark_read(
p_variant TEXT,
p_query_type TEXT,
p_offset INTEGER
)
LANGUAGE plpgsql
AS $$
DECLARE
i INTEGER;
plan JSON;
elapsed_ms DOUBLE PRECISION;
BEGIN
FOR i IN 1..5 LOOP
EXECUTE format(
'EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
SELECT id, chat_id, sender_id, text, created_at
FROM messages
WHERE chat_id = %L::uuid
ORDER BY created_at DESC, id DESC
LIMIT 50 OFFSET %s',
'00000000-0000-4000-8000-000000000101',
p_offset
)
INTO plan;
elapsed_ms := (plan->0->>'Execution Time')::DOUBLE PRECISION;
INSERT INTO benchmark_read_results
VALUES (p_variant, p_query_type, i, elapsed_ms);
END LOOP;
END;
$$;
-- Вариант A: простой индекс.
DROP INDEX IF EXISTS idx_messages_history;
DROP INDEX IF EXISTS idx_messages_chat_only;
CREATE INDEX idx_messages_chat_only
ON messages(chat_id);
ANALYZE messages;
CALL benchmark_read('simple', 'first_page', 0);
CALL benchmark_read('simple', 'deep_page', 30000);
-- Вариант B: составной индекс.
DROP INDEX idx_messages_chat_only;
CREATE INDEX idx_messages_history
ON messages(chat_id, created_at, id);
ANALYZE messages;
CALL benchmark_read('composite', 'first_page', 0);
CALL benchmark_read('composite', 'deep_page', 30000);
-- Вывод всех измерений.
SELECT *
FROM benchmark_read_results
ORDER BY variant, query_type, run_number;
-- Медианы.
SELECT
variant,
query_type,
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_read_results
GROUP BY variant, query_type
ORDER BY query_type, variant;