\set ON_ERROR_STOP on \pset pager off \timing off INSERT INTO users ( id, username, display_name, password_hash ) VALUES ( '00000000-0000-4000-8000-000000000001', 'track2_hot_user', 'Track 2 hot chat user', 'synthetic-not-a-login-hash' ), ( '00000000-0000-4000-8000-000000000002', 'track2_medium_user', 'Track 2 medium chat user', 'synthetic-not-a-login-hash' ), ( '00000000-0000-4000-8000-000000000003', 'track2_small_user', 'Track 2 small chat user', 'synthetic-not-a-login-hash' ); INSERT INTO chats ( id, title ) VALUES ( '00000000-0000-4000-8000-000000000101', 'Track 2 hot chat' ), ( '00000000-0000-4000-8000-000000000102', 'Track 2 medium chat' ), ( '00000000-0000-4000-8000-000000000103', 'Track 2 small chat' ); INSERT INTO chat_members ( chat_id, user_id, position ) VALUES ( '00000000-0000-4000-8000-000000000101', '00000000-0000-4000-8000-000000000001', 0 ), ( '00000000-0000-4000-8000-000000000102', '00000000-0000-4000-8000-000000000002', 0 ), ( '00000000-0000-4000-8000-000000000103', '00000000-0000-4000-8000-000000000003', 0 ); \echo 'WRITE PLAN: current index (chat_id, created_at, id)' EXPLAIN (ANALYZE, BUFFERS, WAL) WITH generated AS ( SELECT number, md5('track2-message-' || number::text) AS digest, CASE WHEN number <= 90000 THEN '00000000-0000-4000-8000-000000000101'::uuid WHEN number <= 99000 THEN '00000000-0000-4000-8000-000000000102'::uuid ELSE '00000000-0000-4000-8000-000000000103'::uuid END AS chat_id, CASE WHEN number <= 90000 THEN '00000000-0000-4000-8000-000000000001'::uuid WHEN number <= 99000 THEN '00000000-0000-4000-8000-000000000002'::uuid ELSE '00000000-0000-4000-8000-000000000003'::uuid END AS sender_id FROM generate_series(1, 100000) AS series(number) ) INSERT INTO messages ( id, chat_id, sender_id, text, created_at ) SELECT ( substr(digest, 1, 8) || '-' || substr(digest, 9, 4) || '-4' || substr(digest, 14, 3) || '-a' || substr(digest, 18, 3) || '-' || substr(digest, 21, 12) )::uuid, chat_id, sender_id, 'Synthetic Track 2 message ' || number || ' with deterministic payload for index comparison', TIMESTAMPTZ '2026-01-01 00:00:00+00' + number * INTERVAL '1 microsecond' FROM generated; VACUUM (ANALYZE) messages; \echo 'UNEVEN DISTRIBUTION: current variant' SELECT chat_id, count(*) AS message_count FROM messages GROUP BY chat_id ORDER BY message_count DESC; \echo 'INDEX SIZE: current variant' SELECT pg_relation_size('messages_chat_created_at_id_idx') AS index_bytes, pg_size_pretty( pg_relation_size('messages_chat_created_at_id_idx') ) AS index_size; SELECT id AS cursor_id, created_at AS cursor_created_at FROM messages WHERE chat_id = '00000000-0000-4000-8000-000000000101' ORDER BY created_at, id OFFSET 49999 LIMIT 1 \gset \echo 'READ PLAN: current index plus keyset cursor' EXPLAIN (ANALYZE, BUFFERS) SELECT id, chat_id, sender_id, text, created_at FROM messages WHERE chat_id = '00000000-0000-4000-8000-000000000101' AND (created_at, id) > ( :'cursor_created_at'::timestamptz, :'cursor_id'::uuid ) ORDER BY created_at, id LIMIT 101; \echo 'READ PLAN: current index plus OFFSET 50000' EXPLAIN (ANALYZE, BUFFERS) SELECT id, chat_id, sender_id, text, created_at FROM messages WHERE chat_id = '00000000-0000-4000-8000-000000000101' ORDER BY created_at, id OFFSET 50000 LIMIT 101; DROP INDEX messages_chat_created_at_id_idx; TRUNCATE TABLE messages; CREATE INDEX messages_chat_created_at_id_covering_idx ON messages (chat_id, created_at, id) INCLUDE (sender_id, text); \echo 'WRITE PLAN: covering index with INCLUDE (sender_id, text)' EXPLAIN (ANALYZE, BUFFERS, WAL) WITH generated AS ( SELECT number, md5('track2-message-' || number::text) AS digest, CASE WHEN number <= 90000 THEN '00000000-0000-4000-8000-000000000101'::uuid WHEN number <= 99000 THEN '00000000-0000-4000-8000-000000000102'::uuid ELSE '00000000-0000-4000-8000-000000000103'::uuid END AS chat_id, CASE WHEN number <= 90000 THEN '00000000-0000-4000-8000-000000000001'::uuid WHEN number <= 99000 THEN '00000000-0000-4000-8000-000000000002'::uuid ELSE '00000000-0000-4000-8000-000000000003'::uuid END AS sender_id FROM generate_series(1, 100000) AS series(number) ) INSERT INTO messages ( id, chat_id, sender_id, text, created_at ) SELECT ( substr(digest, 1, 8) || '-' || substr(digest, 9, 4) || '-4' || substr(digest, 14, 3) || '-a' || substr(digest, 18, 3) || '-' || substr(digest, 21, 12) )::uuid, chat_id, sender_id, 'Synthetic Track 2 message ' || number || ' with deterministic payload for index comparison', TIMESTAMPTZ '2026-01-01 00:00:00+00' + number * INTERVAL '1 microsecond' FROM generated; VACUUM (ANALYZE) messages; \echo 'UNEVEN DISTRIBUTION: covering variant' SELECT chat_id, count(*) AS message_count FROM messages GROUP BY chat_id ORDER BY message_count DESC; \echo 'INDEX SIZE: covering variant' SELECT pg_relation_size( 'messages_chat_created_at_id_covering_idx' ) AS index_bytes, pg_size_pretty( pg_relation_size( 'messages_chat_created_at_id_covering_idx' ) ) AS index_size; SELECT id AS cursor_id, created_at AS cursor_created_at FROM messages WHERE chat_id = '00000000-0000-4000-8000-000000000101' ORDER BY created_at, id OFFSET 49999 LIMIT 1 \gset \echo 'READ PLAN: covering index plus keyset cursor' EXPLAIN (ANALYZE, BUFFERS) SELECT id, chat_id, sender_id, text, created_at FROM messages WHERE chat_id = '00000000-0000-4000-8000-000000000101' AND (created_at, id) > ( :'cursor_created_at'::timestamptz, :'cursor_id'::uuid ) ORDER BY created_at, id LIMIT 101;