-- Воспроизводимый эксперимент: план запроса страницы истории (EXPLAIN (ANALYZE, BUFFERS)) на ~67 000 сообщений. -- Запускать в ОТДЕЛЬНОЙ базе со схемой приложения (миграции применены), см. evidence/lab2-level4-explain.md: -- docker compose exec -T db psql -U messenger -d explain_lab -f - < scripts/explain-history.sql -- Данные синтетические. Порядок вставки перемешан (ORDER BY random()), чтобы строки одного чата не лежали в куче подряд, -- как при реальной записи в много чатов одновременно. \set ON_ERROR_STOP on \pset pager off \timing off TRUNCATE messages, chat_members, chats, sessions, users CASCADE; INSERT INTO users (id, username, display_name, password_hash, created_at) SELECT gen_random_uuid(), 'seed_user_' || g, 'Seed', 'not-a-real-hash', now() FROM generate_series(1, 3) g; -- 1 большой чат (60 000), 1 средний (5 000) и 2 000 маленьких (по 5 сообщений): неравномерные размеры. CREATE TEMP TABLE seed_chats AS SELECT gen_random_uuid() AS id, n, CASE WHEN n = 1 THEN 60000 WHEN n = 2 THEN 5000 ELSE 5 END AS msgs FROM generate_series(1, 2002) n; INSERT INTO chats (id, title, created_at, history_version) SELECT id, 'seed ' || n, now() + n * interval '1 millisecond', msgs FROM seed_chats; INSERT INTO chat_members (chat_id, user_id) SELECT c.id, u.id FROM seed_chats c CROSS JOIN users u; INSERT INTO messages (id, chat_id, sender_id, text, created_at) SELECT gen_random_uuid(), c.id, s.id, 'message ' || g, timestamptz '2026-01-01 00:00:00+00' + g * interval '1 second' FROM seed_chats c CROSS JOIN LATERAL generate_series(1, c.msgs) g CROSS JOIN (SELECT id FROM users WHERE username = 'seed_user_1') s ORDER BY random(); ANALYZE; SELECT count(*) AS messages_total, (SELECT count(*) FROM messages m JOIN seed_chats c ON c.id = m.chat_id AND c.n = 1) AS big_chat, (SELECT count(*) FROM chats) AS chats FROM messages; SELECT id AS big FROM seed_chats WHERE n = 1 \gset SELECT id AS small FROM seed_chats WHERE n = 3 \gset -- Курсор «в середине» истории большого чата (30 000-е сообщение) и «в конце» (59 949-е). SELECT created_at AS mid_t, id AS mid_id FROM messages WHERE chat_id = :'big' ORDER BY created_at, id OFFSET 30000 LIMIT 1 \gset SELECT created_at AS end_t, id AS end_id FROM messages WHERE chat_id = :'big' ORDER BY created_at, id OFFSET 59949 LIMIT 1 \gset \echo \echo '=== A. Первая страница большого чата (с индексом ix_messages_chat_history) ===' EXPLAIN (ANALYZE, BUFFERS, COSTS OFF, TIMING OFF) SELECT m.id, m.chat_id, m.created_at, m.sender_id, m.text FROM (SELECT * FROM messages WHERE chat_id = :'big') AS m ORDER BY m.created_at, m.id LIMIT 51; \echo \echo '=== B. Страница из середины истории (30 000 сообщений до курсора), keyset, с индексом ===' EXPLAIN (ANALYZE, BUFFERS, COSTS OFF, TIMING OFF) SELECT m.id, m.chat_id, m.created_at, m.sender_id, m.text FROM (SELECT * FROM messages WHERE chat_id = :'big' AND (created_at, id) > (:'mid_t', :'mid_id')) AS m ORDER BY m.created_at, m.id LIMIT 51; \echo \echo '=== C. Последняя страница большого чата, keyset, с индексом ===' EXPLAIN (ANALYZE, BUFFERS, COSTS OFF, TIMING OFF) SELECT m.id, m.chat_id, m.created_at, m.sender_id, m.text FROM (SELECT * FROM messages WHERE chat_id = :'big' AND (created_at, id) > (:'end_t', :'end_id')) AS m ORDER BY m.created_at, m.id LIMIT 51; \echo \echo '=== D. Маленький чат (5 сообщений) на той же таблице, с индексом ===' EXPLAIN (ANALYZE, BUFFERS, COSTS OFF, TIMING OFF) SELECT m.id, m.chat_id, m.created_at, m.sender_id, m.text FROM (SELECT * FROM messages WHERE chat_id = :'small') AS m ORDER BY m.created_at, m.id LIMIT 51; \echo \echo '=== E. Для сравнения: OFFSET-пагинация, та же страница из середины (индекс есть) ===' EXPLAIN (ANALYZE, BUFFERS, COSTS OFF, TIMING OFF) SELECT m.id FROM messages m WHERE m.chat_id = :'big' ORDER BY m.created_at, m.id OFFSET 30000 LIMIT 51; \echo \echo '=== F. Тот же запрос B БЕЗ индекса (DROP INDEX ix_messages_chat_history) ===' BEGIN; DROP INDEX ix_messages_chat_history; EXPLAIN (ANALYZE, BUFFERS, COSTS OFF, TIMING OFF) SELECT m.id, m.chat_id, m.created_at, m.sender_id, m.text FROM (SELECT * FROM messages WHERE chat_id = :'big' AND (created_at, id) > (:'mid_t', :'mid_id')) AS m ORDER BY m.created_at, m.id LIMIT 51; \echo \echo '=== G. Запрос D (маленький чат) БЕЗ индекса ===' EXPLAIN (ANALYZE, BUFFERS, COSTS OFF, TIMING OFF) SELECT m.id, m.chat_id, m.created_at, m.sender_id, m.text FROM (SELECT * FROM messages WHERE chat_id = :'small') AS m ORDER BY m.created_at, m.id LIMIT 51; ROLLBACK; \echo \echo '=== H. Размеры: таблица и индекс ===' SELECT pg_size_pretty(pg_relation_size('messages')) AS table_size, pg_size_pretty(pg_relation_size('ix_messages_chat_history')) AS history_index_size;