# EXPLAIN evidence for message history pagination
# date: 2026-10-09T09:35:29Z  commit: e914e38
# postgres: 17.6
# messages total: 70006, in measured chat: 20000

PREPARE
PREPARE
=== 1. WITH index messages_chat_history_idx: first page (limit 50 + 1) ===
                                                                    QUERY PLAN                                                                     
---------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.42..7.37 rows=51 width=78) (actual time=0.017..0.022 rows=51 loops=1)
   Buffers: shared hit=4
   ->  Index Scan using messages_chat_history_idx on messages  (cost=0.42..2712.54 rows=19884 width=78) (actual time=0.016..0.019 rows=51 loops=1)
         Index Cond: (chat_id = '00000000-0000-4000-8000-000000000100'::uuid)
         Buffers: shared hit=4
 Planning:
   Buffers: shared hit=106
 Planning Time: 0.287 ms
 Execution Time: 0.035 ms
(9 rows)

=== 2. WITH index: deep page, cursor after message #15000 of 20000 ===
                                                                                                    QUERY PLAN                                                                                                    
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.42..54.50 rows=51 width=78) (actual time=0.016..0.021 rows=51 loops=1)
   Buffers: shared hit=4
   ->  Index Scan using messages_chat_history_idx on messages  (cost=0.42..1492.33 rows=1407 width=78) (actual time=0.016..0.019 rows=51 loops=1)
         Index Cond: ((chat_id = '00000000-0000-4000-8000-000000000100'::uuid) AND (ROW(created_at, id) > ROW('2026-01-01 04:10:00+00'::timestamp with time zone, '47ba2a9c-d238-4807-2008-6ea72cb0f95c'::uuid)))
         Buffers: shared hit=4
 Planning:
   Buffers: shared hit=42
 Planning Time: 0.286 ms
 Execution Time: 0.039 ms
(9 rows)

BEGIN
DROP INDEX
=== 3. WITHOUT index: first page ===
                                                        QUERY PLAN                                                        
--------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=2472.45..2472.58 rows=51 width=78) (actual time=4.141..4.146 rows=51 loops=1)
   Buffers: shared hit=940
   ->  Sort  (cost=2472.45..2522.16 rows=19884 width=78) (actual time=4.141..4.143 rows=51 loops=1)
         Sort Key: created_at, id
         Sort Method: top-N heapsort  Memory: 31kB
         Buffers: shared hit=940
         ->  Seq Scan on messages  (cost=0.00..1809.08 rows=19884 width=78) (actual time=0.004..3.157 rows=20000 loops=1)
               Filter: (chat_id = '00000000-0000-4000-8000-000000000100'::uuid)
               Rows Removed by Filter: 50006
               Buffers: shared hit=934
 Planning:
   Buffers: shared hit=16
 Planning Time: 0.091 ms
 Execution Time: 4.167 ms
(14 rows)

=== 4. WITHOUT index: deep page ===
                                                                                                     QUERY PLAN                                                                                                     
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=2206.05..2206.17 rows=51 width=78) (actual time=2.882..2.886 rows=51 loops=1)
   Buffers: shared hit=934
   ->  Sort  (cost=2206.05..2209.56 rows=1407 width=78) (actual time=2.881..2.883 rows=51 loops=1)
         Sort Key: created_at, id
         Sort Method: top-N heapsort  Memory: 31kB
         Buffers: shared hit=934
         ->  Seq Scan on messages  (cost=0.00..2159.11 rows=1407 width=78) (actual time=0.628..2.654 rows=5000 loops=1)
               Filter: ((chat_id = '00000000-0000-4000-8000-000000000100'::uuid) AND (ROW(created_at, id) > ROW('2026-01-01 04:10:00+00'::timestamp with time zone, '47ba2a9c-d238-4807-2008-6ea72cb0f95c'::uuid)))
               Rows Removed by Filter: 65006
               Buffers: shared hit=934
 Planning Time: 0.059 ms
 Execution Time: 2.898 ms
(12 rows)

ROLLBACK
