# 2026-10-07 16:04:34 UTC, PostgreSQL 18.6 (Debian 18.6-1.pgdg13+2) on x86_64-pc-linux-gnu, compiled by gcc (Debian 14.2.0-19) 14.2.0, 64-bit
# target chat 5c31bb3d-a9e4-4c7c-a268-d15c483a2d25: 10000 messages; noise chat 0d65d937-959b-44c6-a012-df0c5a5ad525: 20000 messages
# inserted and analyzed in 0.6 s
messages total: 30302, in target chat: 10000

== 1. First page, middle page and last page: index range scan, no sort ==
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF) SELECT id, chat_id, sender_id, text, created_at FROM messages WHERE chat_id = '5c31bb3d-a9e4-4c7c-a268-d15c483a2d25' ORDER BY created_at, id LIMIT 51;
                                                 QUERY PLAN                                                 
------------------------------------------------------------------------------------------------------------
 Limit (actual time=0.036..0.048 rows=51.00 loops=1)
   Buffers: shared hit=4
   ->  Index Scan using messages_chat_history_idx on messages (actual time=0.035..0.041 rows=51.00 loops=1)
         Index Cond: (chat_id = '5c31bb3d-a9e4-4c7c-a268-d15c483a2d25'::uuid)
         Index Searches: 1
         Buffers: shared hit=4
 Planning:
   Buffers: shared hit=112
 Planning Time: 0.414 ms
 Execution Time: 0.069 ms
(10 rows)

EXPLAIN (ANALYZE, BUFFERS, COSTS OFF) SELECT id, chat_id, sender_id, text, created_at FROM messages WHERE chat_id = '5c31bb3d-a9e4-4c7c-a268-d15c483a2d25' AND (created_at, id) > ('2026-10-07 16:04:39.873383+00', 'fa6a0ba6-382c-459f-bab6-feab2ba3c2d3') ORDER BY created_at, id LIMIT 51;
                                                                                                       QUERY PLAN                                                                                                        
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit (actual time=0.014..0.021 rows=51.00 loops=1)
   Buffers: shared hit=5
   ->  Index Scan using messages_chat_history_idx on messages (actual time=0.013..0.018 rows=51.00 loops=1)
         Index Cond: ((chat_id = '5c31bb3d-a9e4-4c7c-a268-d15c483a2d25'::uuid) AND (ROW(created_at, id) > ROW('2026-10-07 16:04:39.873383+00'::timestamp with time zone, 'fa6a0ba6-382c-459f-bab6-feab2ba3c2d3'::uuid)))
         Index Searches: 1
         Buffers: shared hit=5
 Planning Time: 0.048 ms
 Execution Time: 0.041 ms
(8 rows)

EXPLAIN (ANALYZE, BUFFERS, COSTS OFF) SELECT id, chat_id, sender_id, text, created_at FROM messages WHERE chat_id = '5c31bb3d-a9e4-4c7c-a268-d15c483a2d25' AND (created_at, id) > ('2026-10-07 16:04:44.872196+00', '2db6f051-d2f0-4608-a584-347e2b610391') ORDER BY created_at, id LIMIT 51;
                                                                                                       QUERY PLAN                                                                                                        
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit (actual time=0.021..0.026 rows=20.00 loops=1)
   Buffers: shared hit=4
   ->  Index Scan using messages_chat_history_idx on messages (actual time=0.020..0.023 rows=20.00 loops=1)
         Index Cond: ((chat_id = '5c31bb3d-a9e4-4c7c-a268-d15c483a2d25'::uuid) AND (ROW(created_at, id) > ROW('2026-10-07 16:04:44.872196+00'::timestamp with time zone, '2db6f051-d2f0-4608-a584-347e2b610391'::uuid)))
         Index Searches: 1
         Buffers: shared hit=4
 Planning Time: 0.061 ms
 Execution Time: 0.038 ms
(8 rows)

== 2. Generic plan of the parameterized query (how a prepared statement with $1..$4 runs) ==
SET plan_cache_mode = force_generic_plan;
SET
PREPARE history_page(uuid, timestamptz, uuid, int) AS
SELECT id, chat_id, sender_id, text, created_at FROM messages
WHERE chat_id = $1 AND (created_at, id) > ($2, $3) ORDER BY created_at, id LIMIT $4;
PREPARE
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF) EXECUTE history_page('5c31bb3d-a9e4-4c7c-a268-d15c483a2d25', '2026-10-07 16:04:39.873383+00', 'fa6a0ba6-382c-459f-bab6-feab2ba3c2d3', 51);
                                                 QUERY PLAN                                                 
------------------------------------------------------------------------------------------------------------
 Limit (actual time=0.021..0.030 rows=51.00 loops=1)
   Buffers: shared hit=5
   ->  Index Scan using messages_chat_history_idx on messages (actual time=0.020..0.025 rows=51.00 loops=1)
         Index Cond: ((chat_id = $1) AND (ROW(created_at, id) > ROW($2, $3)))
         Index Searches: 1
         Buffers: shared hit=5
 Planning:
   Buffers: shared hit=111
 Planning Time: 0.356 ms
 Execution Time: 0.064 ms
(10 rows)

== 3. For comparison: the same middle page without the index (rolled back) ==
BEGIN;
BEGIN
DROP INDEX messages_chat_history_idx;
DROP INDEX
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF) SELECT id, chat_id, sender_id, text, created_at FROM messages WHERE chat_id = '5c31bb3d-a9e4-4c7c-a268-d15c483a2d25' AND (created_at, id) > ('2026-10-07 16:04:39.873383+00', 'fa6a0ba6-382c-459f-bab6-feab2ba3c2d3') ORDER BY created_at, id LIMIT 51;
                                                                                                        QUERY PLAN                                                                                                         
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit (actual time=1.944..1.949 rows=51.00 loops=1)
   Buffers: shared hit=416
   ->  Sort (actual time=1.942..1.944 rows=51.00 loops=1)
         Sort Key: created_at, id
         Sort Method: top-N heapsort  Memory: 31kB
         Buffers: shared hit=416
         ->  Seq Scan on messages (actual time=0.315..1.677 rows=5000.00 loops=1)
               Filter: ((chat_id = '5c31bb3d-a9e4-4c7c-a268-d15c483a2d25'::uuid) AND (ROW(created_at, id) > ROW('2026-10-07 16:04:39.873383+00'::timestamp with time zone, 'fa6a0ba6-382c-459f-bab6-feab2ba3c2d3'::uuid)))
               Rows Removed by Filter: 25302
               Buffers: shared hit=410
 Planning:
   Buffers: shared hit=83
 Planning Time: 0.271 ms
 Execution Time: 1.976 ms
(14 rows)

ROLLBACK;
ROLLBACK
index still exists: true
