-- EXPLAIN (ANALYZE, BUFFERS) of the chat history query on synthetic data. -- Everything runs in one transaction that is rolled back: the database is left untouched. -- The queries are the ones ExposedMessageRepository.findPageByChatId generates (page size 50 + 1 look-ahead row). \set ON_ERROR_STOP on \pset pager off BEGIN; INSERT INTO users (id, username, display_name, password_hash, created_at) VALUES ('00000000-0000-0000-0000-000000000001', 'explain_alice', 'Alice', 'not-a-hash', now()), ('00000000-0000-0000-0000-000000000002', 'explain_bob', 'Bob', 'not-a-hash', now()); -- One large chat (20 000 messages) and 50 neighbours with 1 000 messages each: 70 000 rows in total, -- so the plan has to skip foreign chats instead of reading a table that only holds the target chat. INSERT INTO chats (id, title, created_at) SELECT ('00000000-0000-0000-0001-' || lpad(n::text, 12, '0'))::uuid, 'explain chat ' || n, now() FROM generate_series(0, 50) AS n; INSERT INTO chat_members (chat_id, user_id, position) SELECT c.id, u.id, u.position FROM chats c CROSS JOIN (VALUES ('00000000-0000-0000-0000-000000000001'::uuid, 0), ('00000000-0000-0000-0000-000000000002'::uuid, 1)) AS u (id, position) WHERE c.title LIKE 'explain chat %'; INSERT INTO messages (id, chat_id, sender_id, text, created_at) SELECT gen_random_uuid(), '00000000-0000-0000-0001-000000000000', CASE WHEN n % 2 = 0 THEN '00000000-0000-0000-0000-000000000001'::uuid ELSE '00000000-0000-0000-0000-000000000002'::uuid END, 'message #' || n, timestamptz '2026-01-01 00:00:00+00' + n * interval '1 second' FROM generate_series(1, 20000) AS n; INSERT INTO messages (id, chat_id, sender_id, text, created_at) SELECT gen_random_uuid(), ('00000000-0000-0000-0001-' || lpad(c::text, 12, '0'))::uuid, '00000000-0000-0000-0000-000000000001', 'neighbour message #' || n, timestamptz '2026-01-01 00:00:00+00' + n * interval '1 second' FROM generate_series(1, 50) AS c, generate_series(1, 1000) AS n; ANALYZE users, chats, chat_members, messages; \echo \echo '== Data volume' SELECT count(*) AS total_messages, count(*) FILTER (WHERE chat_id = '00000000-0000-0000-0001-000000000000') AS target_chat_messages FROM messages; -- Cursor of the 15 000th message: a deep page, where OFFSET pagination would have to skip 15 000 rows. SELECT created_at AS cursor_created_at, id AS cursor_id FROM messages WHERE chat_id = '00000000-0000-0000-0001-000000000000' ORDER BY created_at, id OFFSET 14999 LIMIT 1 \gset \echo \echo '== 1. First page: WHERE chat_id = ? ORDER BY created_at, id LIMIT 51' EXPLAIN (ANALYZE, BUFFERS, COSTS OFF) SELECT messages.id, messages.chat_id, messages.sender_id, messages.text, messages.created_at FROM messages WHERE messages.chat_id = '00000000-0000-0000-0001-000000000000' ORDER BY messages.created_at ASC, messages.id ASC LIMIT 51; \echo \echo '== 2. Deep page after the 15 000th message: (created_at, id) > (?, ?)' EXPLAIN (ANALYZE, BUFFERS, COSTS OFF) SELECT messages.id, messages.chat_id, messages.sender_id, messages.text, messages.created_at FROM messages WHERE (messages.chat_id = '00000000-0000-0000-0001-000000000000') AND ((messages.created_at, messages.id) > (:'cursor_created_at', :'cursor_id')) ORDER BY messages.created_at ASC, messages.id ASC LIMIT 51; \echo \echo '== 3. Same deep page with OFFSET instead of a cursor (for comparison, not used by the API)' EXPLAIN (ANALYZE, BUFFERS, COSTS OFF) SELECT messages.id, messages.chat_id, messages.sender_id, messages.text, messages.created_at FROM messages WHERE messages.chat_id = '00000000-0000-0000-0001-000000000000' ORDER BY messages.created_at ASC, messages.id ASC LIMIT 51 OFFSET 15000; \echo \echo '== 4. Deep page without messages_chat_id_created_at_id_idx (index scans disabled for this query only)' SET LOCAL enable_indexscan = off; SET LOCAL enable_bitmapscan = off; SET LOCAL enable_indexonlyscan = off; EXPLAIN (ANALYZE, BUFFERS, COSTS OFF) SELECT messages.id, messages.chat_id, messages.sender_id, messages.text, messages.created_at FROM messages WHERE (messages.chat_id = '00000000-0000-0000-0001-000000000000') AND ((messages.created_at, messages.id) > (:'cursor_created_at', :'cursor_id')) ORDER BY messages.created_at ASC, messages.id ASC LIMIT 51; ROLLBACK;