> docker compose exec postgres psql -U messenger -d messenger
psql (18.6 (Debian 18.6-1.pgdg13+2))
Type "help" for help.

messenger=# INSERT INTO users (id, username, display_name, password_hash)
VALUES ('00000000-0000-0000-0000-0000000000a1', 'explain_user', 'Explain', 'x');
INSERT 0 1
messenger=# INSERT INTO chats (id, title)
VALUES ('00000000-0000-0000-0000-0000000000b1', 'explain chat');
INSERT 0 1
messenger=# INSERT INTO messages (id, chat_id, sender_id, text, created_at)
SELECT gen_random_uuid(), '00000000-0000-0000-0000-0000000000b1',
       '00000000-0000-0000-0000-0000000000a1', 'msg ' || g,
       now() - (g || ' seconds')::interval
FROM generate_series(1, 20000) g;
INSERT 0 20000
messenger=# ANALYZE messages;
ANALYZE

messenger=# BEGIN;
BEGIN
messenger=*# DROP INDEX idx_messages_chat_history;
DROP INDEX
messenger=*# EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM messages
WHERE chat_id = '00000000-0000-0000-0000-0000000000b1'
ORDER BY created_at, id LIMIT 50;
                                                         QUERY PLAN                                                         
----------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=1405.39..1405.51 rows=50 width=65) (actual time=4.083..4.088 rows=50.00 loops=1)
   Buffers: shared hit=491
   ->  Sort  (cost=1405.39..1455.39 rows=20000 width=65) (actual time=4.082..4.084 rows=50.00 loops=1)
         Sort Key: created_at, id
         Sort Method: top-N heapsort  Memory: 38kB
         Buffers: shared hit=491
         ->  Seq Scan on messages  (cost=0.00..741.00 rows=20000 width=65) (actual time=0.154..1.624 rows=20000.00 loops=1)
               Filter: (chat_id = '00000000-0000-0000-0000-0000000000b1'::uuid)
               Buffers: shared hit=491
 Planning:
   Buffers: shared hit=30
 Planning Time: 0.218 ms
 Execution Time: 4.102 ms
(13 rows)

messenger=*# ROLLBACK;
ROLLBACK


messenger=# EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM messages
WHERE chat_id = '00000000-0000-0000-0000-0000000000b1'
ORDER BY created_at, id LIMIT 50;
                                                                      QUERY PLAN                                                                      
------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.41..10.76 rows=50 width=65) (actual time=0.008..0.018 rows=50.00 loops=1)
   Buffers: shared hit=6
   ->  Index Scan using idx_messages_chat_history on messages  (cost=0.41..4139.54 rows=20000 width=65) (actual time=0.006..0.014 rows=50.00 loops=1)
         Index Cond: (chat_id = '00000000-0000-0000-0000-0000000000b1'::uuid)
         Index Searches: 1
         Buffers: shared hit=6
 Planning:
   Buffers: shared hit=22
 Planning Time: 0.141 ms
 Execution Time: 0.029 ms
(10 rows)


messenger=# SELECT created_at AS cur_ts, id AS cur_id FROM messages
WHERE chat_id = '00000000-0000-0000-0000-0000000000b1'
ORDER BY created_at, id OFFSET 15000 LIMIT 1 \gset

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM messages
WHERE chat_id = '00000000-0000-0000-0000-0000000000b1'
  AND (created_at, id) > (:'cur_ts', :'cur_id')
ORDER BY created_at, id LIMIT 50;

                                                                                                      QUERY PLAN                                                                                                       
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.41..17.36 rows=50 width=65) (actual time=0.018..0.031 rows=50.00 loops=1)
   Buffers: shared hit=5
   ->  Index Scan using idx_messages_chat_history on messages  (cost=0.41..1694.52 rows=4999 width=65) (actual time=0.017..0.026 rows=50.00 loops=1)
         Index Cond: ((chat_id = '00000000-0000-0000-0000-0000000000b1'::uuid) AND (ROW(created_at, id) > ROW('2026-10-05 19:13:13.4346+00'::timestamp with time zone, '9bb7f5ae-1191-44f9-aaa9-fb1ebf4cc701'::uuid)))
         Index Searches: 1
         Buffers: shared hit=5
 Planning Time: 0.071 ms
 Execution Time: 0.044 ms
(8 rows)

messenger=# 
