-- Обоснование индекса истории: 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;