Files
mtusi2026lab5/scripts/explain_history.sql

112 lines
4.6 KiB
PL/PgSQL

-- Обоснование индекса истории: EXPLAIN (ANALYZE, BUFFERS) на ≥10 000 сообщений.
-- Запуск: make explain → evidence/explain_history.txt
-- Сид синтетический и идемпотентный: повторный запуск данных не дублирует.
-- Пользователь explain_seed не может войти: password_hash не является hash Argon2.
\pset pager off
\timing off
DO $$
DECLARE
seed_user uuid;
big_chat uuid;
noise uuid;
BEGIN
IF EXISTS (SELECT 1 FROM chats WHERE title = 'explain-big') THEN
RETURN;
END IF;
INSERT INTO users (username, display_name, password_hash)
VALUES ('explain_seed', 'EXPLAIN seed', 'not-an-argon2-hash')
ON CONFLICT (username) DO UPDATE SET display_name = EXCLUDED.display_name
RETURNING id INTO seed_user;
-- Большой чат: 20 000 сообщений
INSERT INTO chats (title, created_by) VALUES ('explain-big', seed_user)
RETURNING id INTO big_chat;
INSERT INTO chat_members (chat_id, user_id, position) VALUES (big_chat, seed_user, 0);
INSERT INTO messages (chat_id, sender_id, text, created_at)
SELECT big_chat, seed_user, 'synthetic message #' || n,
timestamptz '2026-01-01 00:00:00+00' + n * interval '1 second'
FROM generate_series(1, 20000) AS n;
-- Шум: 50 чатов по 1 000 сообщений, чтобы таблица была больше одного чата
FOR i IN 1..50 LOOP
INSERT INTO chats (title, created_by) VALUES ('explain-noise-' || i, seed_user)
RETURNING id INTO noise;
INSERT INTO chat_members (chat_id, user_id, position) VALUES (noise, seed_user, 0);
INSERT INTO messages (chat_id, sender_id, text, created_at)
SELECT noise, seed_user, 'noise #' || n,
timestamptz '2026-01-01 00:00:00+00' + n * interval '1 second'
FROM generate_series(1, 1000) AS n;
END LOOP;
END $$;
ANALYZE users, chats, chat_members, messages;
SELECT id AS big_chat FROM chats WHERE title = 'explain-big' \gset
-- Cursor из середины истории: ключ 10 000-го сообщения
SELECT created_at AS cursor_created_at, id AS cursor_id
FROM messages WHERE chat_id = :'big_chat'
ORDER BY created_at, id OFFSET 9999 LIMIT 1 \gset
\echo '== Объём данных'
SELECT
(SELECT count(*) FROM messages) AS messages_total,
(SELECT count(*) FROM messages WHERE chat_id = :'big_chat') AS messages_in_big_chat,
pg_size_pretty(pg_relation_size('messages')) AS messages_heap,
pg_size_pretty(pg_relation_size('messages_chat_id_created_at_id_idx')) AS history_index;
\echo ''
\echo '== 1. Первая страница истории (limit 50 + 1), как list_for_chat'
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT id, chat_id, sender_id, text, created_at
FROM messages
WHERE chat_id = :'big_chat'
ORDER BY created_at, id
LIMIT 51;
\echo ''
\echo '== 2. Страница из середины по cursor (после 10 000-го), как list_for_chat_after'
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT id, chat_id, sender_id, text, created_at
FROM messages
WHERE chat_id = :'big_chat'
AND (created_at, id) > (:'cursor_created_at'::timestamptz, :'cursor_id'::uuid)
ORDER BY created_at, id
LIMIT 51;
\echo ''
\echo '== 3. Тот же запрос как generic plan prepared statement (так его исполняет asyncpg)'
SET plan_cache_mode = force_generic_plan;
PREPARE history_page(uuid, timestamptz, uuid, int) AS
SELECT id, chat_id, sender_id, text, created_at
FROM messages
WHERE chat_id = $1 AND (created_at, id) > ($2, $3)
ORDER BY created_at, id
LIMIT $4;
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
EXECUTE history_page(:'big_chat', :'cursor_created_at', :'cursor_id', 51);
DEALLOCATE history_page;
RESET plan_cache_mode;
\echo ''
\echo '== 4. MAX(created_at) при вставке сообщения (монотонное время чата)'
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT max(created_at) FROM messages WHERE chat_id = :'big_chat';
\echo ''
\echo '== 5. Для сравнения: тот же запрос из середины БЕЗ индекса истории'
\echo ' (DROP INDEX внутри транзакции с ROLLBACK — индекс не теряется)'
BEGIN;
DROP INDEX messages_chat_id_created_at_id_idx;
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT id, chat_id, sender_id, text, created_at
FROM messages
WHERE chat_id = :'big_chat'
AND (created_at, id) > (:'cursor_created_at'::timestamptz, :'cursor_id'::uuid)
ORDER BY created_at, id
LIMIT 51;
ROLLBACK;