-- Обоснование индекса истории: EXPLAIN (ANALYZE, BUFFERS) на >= 10 000 сообщений. -- Всё выполняется в ОДНОЙ транзакции и откатывается (ROLLBACK): синтетические -- данные и временное удаление индекса не остаются в БД. -- Запуск (compose): -- docker compose exec -T postgres psql -U messenger -d messenger -v ON_ERROR_STOP=1 \ -- < scripts/sql/explain_history.sql > evidence/explain_history.txt -- Внимание: DROP INDEX внутри транзакции берёт эксклюзивную блокировку messages -- до ROLLBACK (несколько секунд) — запускать на учебном стенде. \set ON_ERROR_STOP 1 \timing off SET TimeZone = 'UTC'; -- Убираем мёртвые версии строк от прошлых запусков (VACUUM нельзя внутри транзакции). VACUUM (ANALYZE) messages, chat_members, chats, users; BEGIN; INSERT INTO users (id, username, display_name, password_hash) VALUES ('00000000-0000-4000-8000-00000000e001', 'explain_alice', 'Explain', 'x'), ('00000000-0000-4000-8000-00000000e002', 'explain_bob', 'Explain', 'x'); -- 1 большой чат (12 000 сообщений) + 40 чатов по 1 000 = 52 000 сообщений. INSERT INTO chats (id, title, created_at) SELECT CASE WHEN g = 0 THEN '00000000-0000-4000-8000-0000000c0000'::uuid ELSE gen_random_uuid() END, 'explain-' || g, timestamptz '2026-01-01 00:00:00+00' + g * interval '1 second' FROM generate_series(0, 40) AS g; INSERT INTO chat_members (chat_id, user_id, position) SELECT c.id, u.id, u.pos FROM chats c CROSS JOIN (VALUES ('00000000-0000-4000-8000-00000000e001'::uuid, 0), ('00000000-0000-4000-8000-00000000e002'::uuid, 1)) AS u(id, pos) WHERE c.title LIKE 'explain-%'; INSERT INTO messages (id, chat_id, sender_id, text, created_at) SELECT gen_random_uuid(), c.id, CASE WHEN g % 2 = 0 THEN '00000000-0000-4000-8000-00000000e001'::uuid ELSE '00000000-0000-4000-8000-00000000e002'::uuid END, 'synthetic message ' || g, timestamptz '2026-01-02 00:00:00+00' + g * interval '1 millisecond' + (c.created_at - timestamptz '2026-01-01 00:00:00+00') FROM chats c CROSS JOIN LATERAL generate_series(1, CASE WHEN c.title = 'explain-0' THEN 12000 ELSE 1000 END) AS g WHERE c.title LIKE 'explain-%'; ANALYZE users, chats, chat_members, messages; \echo '=== dataset' SELECT count(*) AS total_messages, count(*) FILTER (WHERE chat_id = '00000000-0000-4000-8000-0000000c0000') AS big_chat_messages FROM messages; -- Позиция курсора после 10 000-го сообщения большого чата (как в next_cursor). SELECT created_at AS cur_ts, id AS cur_id FROM messages WHERE chat_id = '00000000-0000-4000-8000-0000000c0000' ORDER BY created_at, id OFFSET 9999 LIMIT 1 \gset \echo '=== 1. first page (no cursor), LIMIT 51 = limit+1' EXPLAIN (ANALYZE, BUFFERS, COSTS, TIMING) SELECT id, chat_id, sender_id, text, created_at FROM messages WHERE chat_id = '00000000-0000-4000-8000-0000000c0000' ORDER BY created_at, id LIMIT 51; \echo '=== 2. deep page: keyset cursor after message #10000 (query used by API)' EXPLAIN (ANALYZE, BUFFERS, COSTS, TIMING) SELECT id, chat_id, sender_id, text, created_at FROM messages WHERE chat_id = '00000000-0000-4000-8000-0000000c0000' AND (created_at, id) > (:'cur_ts'::timestamptz, :'cur_id'::uuid) ORDER BY created_at, id LIMIT 51; \echo '=== 3. for comparison: OFFSET pagination to the same page (NOT used by API)' EXPLAIN (ANALYZE, BUFFERS, COSTS, TIMING) SELECT id, chat_id, sender_id, text, created_at FROM messages WHERE chat_id = '00000000-0000-4000-8000-0000000c0000' ORDER BY created_at, id OFFSET 10000 LIMIT 51; \echo '=== 4. same keyset query WITHOUT messages_chat_created_id_idx (index dropped inside the transaction)' DROP INDEX messages_chat_created_id_idx; EXPLAIN (ANALYZE, BUFFERS, COSTS, TIMING) SELECT id, chat_id, sender_id, text, created_at FROM messages WHERE chat_id = '00000000-0000-4000-8000-0000000c0000' AND (created_at, id) > (:'cur_ts'::timestamptz, :'cur_id'::uuid) ORDER BY created_at, id LIMIT 51; ROLLBACK; \echo '=== rolled back: synthetic data removed, index restored'