Files
mtusi2026lab2-implementation/scripts/explain.sql

67 lines
2.3 KiB
PL/PgSQL

\set ON_ERROR_STOP on
\set big_chat '''00000000-0000-4000-8000-00000000e001'''
\set seed_user '''00000000-0000-4000-8000-00000000e000'''
INSERT INTO users (id, username, display_name, password_hash, created_at)
VALUES (:seed_user, 'explain_seed', 'Explain seed', 'not-a-login', now())
ON CONFLICT DO NOTHING;
INSERT INTO chats (id, title, created_at)
SELECT CASE WHEN g = 0 THEN :big_chat::uuid
ELSE ('00000000-0000-4000-8000-' || lpad(to_hex(57345 + g), 12, '0'))::uuid END,
'explain chat ' || g, now()
FROM generate_series(0, 20) AS g
ON CONFLICT DO NOTHING;
INSERT INTO chat_members (chat_id, user_id, position)
SELECT id, :seed_user, 0 FROM chats WHERE title LIKE 'explain chat %'
ON CONFLICT DO NOTHING;
INSERT INTO messages (id, chat_id, sender_id, text, created_at)
SELECT gen_random_uuid(), c.id, :seed_user, 'synthetic message ' || n,
timestamptz '2026-01-01' + n * interval '1 second'
FROM chats c
CROSS JOIN LATERAL generate_series(1, CASE WHEN c.id = :big_chat THEN 20000 ELSE 2000 END) AS n
WHERE c.title LIKE 'explain chat %'
AND NOT EXISTS (SELECT 1 FROM messages m WHERE m.chat_id = c.id);
ANALYZE users;
ANALYZE chats;
ANALYZE chat_members;
ANALYZE messages;
SELECT count(*) AS messages_total,
count(*) FILTER (WHERE chat_id = :big_chat) AS messages_in_big_chat
FROM messages;
SELECT created_at AS cur_ts, id AS cur_id
FROM messages WHERE chat_id = :big_chat
ORDER BY created_at, id OFFSET 9999 LIMIT 1 \gset
\echo '=== 1. First page (limit=50 -> LIMIT 51), index messages_chat_created_id_idx'
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, chat_id, sender_id, text, created_at
FROM messages
WHERE chat_id = :big_chat
ORDER BY created_at, id
LIMIT 51;
\echo '=== 2. Page in the middle by keyset cursor (after 10 000 messages)'
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, chat_id, sender_id, text, created_at
FROM messages
WHERE chat_id = :big_chat AND (created_at, id) > (:'cur_ts', :'cur_id')
ORDER BY created_at, id
LIMIT 51;
\echo '=== 3. Same middle page WITHOUT the index (dropped inside a rolled-back transaction)'
BEGIN;
DROP INDEX messages_chat_created_id_idx;
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, chat_id, sender_id, text, created_at
FROM messages
WHERE chat_id = :big_chat AND (created_at, id) > (:'cur_ts', :'cur_id')
ORDER BY created_at, id
LIMIT 51;
ROLLBACK;