# 2026-10-07 22:30:37 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 0026e6c1-47d7-4b72-ae1b-a7d3c5a493ae: 10000 messages; noise chat 8efe2cac-fee0-4974-b5a0-197ee563140a: 20000 messages
# inserted and analyzed in 1.0 s
messages total: 55175, 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 = '0026e6c1-47d7-4b72-ae1b-a7d3c5a493ae' ORDER BY created_at, id LIMIT 51;
                                                 QUERY PLAN                                                 
------------------------------------------------------------------------------------------------------------
 Limit (actual time=0.045..0.183 rows=51.00 loops=1)
   Buffers: shared hit=54
   ->  Index Scan using messages_chat_history_idx on messages (actual time=0.042..0.174 rows=51.00 loops=1)
         Index Cond: (chat_id = '0026e6c1-47d7-4b72-ae1b-a7d3c5a493ae'::uuid)
         Index Searches: 1
         Buffers: shared hit=54
 Planning:
   Buffers: shared hit=144
 Planning Time: 0.722 ms
 Execution Time: 0.220 ms
(10 rows)

EXPLAIN (ANALYZE, BUFFERS, COSTS OFF) SELECT id, chat_id, sender_id, text, created_at FROM messages WHERE chat_id = '0026e6c1-47d7-4b72-ae1b-a7d3c5a493ae' AND (created_at, id) > ('2026-10-07 22:30:43.623934+00', 'c51eae37-23c0-412f-8b9e-233311a6374d') ORDER BY created_at, id LIMIT 51;
                                                                                                       QUERY PLAN                                                                                                        
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit (actual time=0.020..0.033 rows=51.00 loops=1)
   Buffers: shared hit=5
   ->  Index Scan using messages_chat_history_idx on messages (actual time=0.019..0.027 rows=51.00 loops=1)
         Index Cond: ((chat_id = '0026e6c1-47d7-4b72-ae1b-a7d3c5a493ae'::uuid) AND (ROW(created_at, id) > ROW('2026-10-07 22:30:43.623934+00'::timestamp with time zone, 'c51eae37-23c0-412f-8b9e-233311a6374d'::uuid)))
         Index Searches: 1
         Buffers: shared hit=5
 Planning Time: 0.068 ms
 Execution Time: 0.059 ms
(8 rows)

EXPLAIN (ANALYZE, BUFFERS, COSTS OFF) SELECT id, chat_id, sender_id, text, created_at FROM messages WHERE chat_id = '0026e6c1-47d7-4b72-ae1b-a7d3c5a493ae' AND (created_at, id) > ('2026-10-07 22:30:48.635476+00', '61827eb5-875e-47be-b232-4b344ab1b5df') ORDER BY created_at, id LIMIT 51;
                                                                                                       QUERY PLAN                                                                                                        
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit (actual time=0.031..0.039 rows=20.00 loops=1)
   Buffers: shared hit=5
   ->  Index Scan using messages_chat_history_idx on messages (actual time=0.030..0.036 rows=20.00 loops=1)
         Index Cond: ((chat_id = '0026e6c1-47d7-4b72-ae1b-a7d3c5a493ae'::uuid) AND (ROW(created_at, id) > ROW('2026-10-07 22:30:48.635476+00'::timestamp with time zone, '61827eb5-875e-47be-b232-4b344ab1b5df'::uuid)))
         Index Searches: 1
         Buffers: shared hit=5
 Planning Time: 0.077 ms
 Execution Time: 0.054 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('0026e6c1-47d7-4b72-ae1b-a7d3c5a493ae', '2026-10-07 22:30:43.623934+00', 'c51eae37-23c0-412f-8b9e-233311a6374d', 51);
                                                 QUERY PLAN                                                 
------------------------------------------------------------------------------------------------------------
 Limit (actual time=0.038..0.051 rows=51.00 loops=1)
   Buffers: shared hit=5
   ->  Index Scan using messages_chat_history_idx on messages (actual time=0.032..0.040 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=141
 Planning Time: 0.836 ms
 Execution Time: 0.107 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 = '0026e6c1-47d7-4b72-ae1b-a7d3c5a493ae' AND (created_at, id) > ('2026-10-07 22:30:43.623934+00', 'c51eae37-23c0-412f-8b9e-233311a6374d') ORDER BY created_at, id LIMIT 51;
                                                                                                        QUERY PLAN                                                                                                         
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit (actual time=6.627..6.638 rows=51.00 loops=1)
   Buffers: shared hit=809
   ->  Sort (actual time=6.624..6.629 rows=51.00 loops=1)
         Sort Key: created_at, id
         Sort Method: top-N heapsort  Memory: 31kB
         Buffers: shared hit=809
         ->  Seq Scan on messages (actual time=3.200..6.151 rows=5000.00 loops=1)
               Filter: ((chat_id = '0026e6c1-47d7-4b72-ae1b-a7d3c5a493ae'::uuid) AND (ROW(created_at, id) > ROW('2026-10-07 22:30:43.623934+00'::timestamp with time zone, 'c51eae37-23c0-412f-8b9e-233311a6374d'::uuid)))
               Rows Removed by Filter: 50175
               Buffers: shared hit=803
 Planning:
   Buffers: shared hit=108
 Planning Time: 0.468 ms
 Execution Time: 6.680 ms
(14 rows)

ROLLBACK;
ROLLBACK
index still exists: true
