=== Data volume
 messages_total | messages_in_big_chat 
----------------+----------------------
         141938 |                15000


=== History index
                                        indexdef                                        
----------------------------------------------------------------------------------------
 CREATE INDEX messages_history ON public.messages USING btree (chat_id, created_at, id)


=== 1. First page: limit=50, the app asks for 51 rows to know if there is a next page
                                           QUERY PLAN                                           
------------------------------------------------------------------------------------------------
 Limit (actual time=0.007..0.018 rows=51 loops=1)
   Buffers: shared hit=54
   ->  Index Scan using messages_history on messages (actual time=0.007..0.015 rows=51 loops=1)
         Index Cond: (chat_id = '1efc622c-6386-4403-a0dc-6d7e17977038'::uuid)
         Buffers: shared hit=54
 Planning:
   Buffers: shared hit=7
 Planning Time: 0.066 ms
 Execution Time: 0.026 ms


=== 2. Page after the 10000th message by keyset cursor (this is what the app does)
                                                                                                    QUERY PLAN                                                                                                    
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit (actual time=0.027..0.041 rows=51 loops=1)
   Buffers: shared hit=4
   ->  Index Scan using messages_history on messages (actual time=0.026..0.036 rows=51 loops=1)
         Index Cond: ((chat_id = '1efc622c-6386-4403-a0dc-6d7e17977038'::uuid) AND (ROW(created_at, id) > ROW('2026-09-01 02:46:40+00'::timestamp with time zone, 'cc573555-8177-419f-a1b0-2b402c9b73d3'::uuid)))
         Buffers: shared hit=4
 Planning Time: 0.084 ms
 Execution Time: 0.098 ms


=== 2b. The same page exactly as the app sends it since lab4: with attachment_ids of every message
                                                                                                    QUERY PLAN                                                                                                    
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit (actual time=0.021..0.177 rows=51 loops=1)
   Buffers: shared hit=58
   ->  Index Scan using messages_history on messages m (actual time=0.021..0.174 rows=51 loops=1)
         Index Cond: ((chat_id = '1efc622c-6386-4403-a0dc-6d7e17977038'::uuid) AND (ROW(created_at, id) > ROW('2026-09-01 02:46:40+00'::timestamp with time zone, 'cc573555-8177-419f-a1b0-2b402c9b73d3'::uuid)))
         Buffers: shared hit=58
         SubPlan 1
           ->  Sort (actual time=0.002..0.002 rows=0 loops=51)
                 Sort Key: a."position"
                 Sort Method: quicksort  Memory: 25kB
                 Buffers: shared hit=54
                 ->  Bitmap Heap Scan on message_attachments a (actual time=0.000..0.000 rows=0 loops=51)
                       Recheck Cond: (message_id = m.id)
                       Buffers: shared hit=51
                       ->  Bitmap Index Scan on message_attachments_message_id_attachment_id_key (actual time=0.000..0.000 rows=0 loops=51)
                             Index Cond: (message_id = m.id)
                             Buffers: shared hit=51
 Planning:
   Buffers: shared hit=63
 Planning Time: 0.164 ms
 Execution Time: 0.219 ms


=== 3. The same page with OFFSET 10000 (the app does NOT do this)
                                              QUERY PLAN                                               
-------------------------------------------------------------------------------------------------------
 Limit (actual time=3.398..3.401 rows=51 loops=1)
   Buffers: shared hit=2553
   ->  Sort (actual time=3.012..3.217 rows=10051 loops=1)
         Sort Key: created_at, id
         Sort Method: quicksort  Memory: 2377kB
         Buffers: shared hit=2553
         ->  Bitmap Heap Scan on messages (actual time=0.608..1.680 rows=15000 loops=1)
               Recheck Cond: (chat_id = '1efc622c-6386-4403-a0dc-6d7e17977038'::uuid)
               Heap Blocks: exact=2359
               Buffers: shared hit=2553
               ->  Bitmap Index Scan on messages_history (actual time=0.458..0.458 rows=15000 loops=1)
                     Index Cond: (chat_id = '1efc622c-6386-4403-a0dc-6d7e17977038'::uuid)
                     Buffers: shared hit=194
 Planning Time: 0.036 ms
 Execution Time: 3.484 ms


=== 4. Query 2 without the index (index is dropped only inside this transaction)
                                                                     QUERY PLAN                                                                      
-----------------------------------------------------------------------------------------------------------------------------------------------------
 Limit (actual time=1.636..1.639 rows=51 loops=1)
   Buffers: shared hit=2375
   ->  Sort (actual time=1.635..1.637 rows=51 loops=1)
         Sort Key: created_at, id
         Sort Method: top-N heapsort  Memory: 31kB
         Buffers: shared hit=2375
         ->  Bitmap Heap Scan on messages (actual time=1.034..1.289 rows=5000 loops=1)
               Recheck Cond: (chat_id = '1efc622c-6386-4403-a0dc-6d7e17977038'::uuid)
               Filter: (ROW(created_at, id) > ROW('2026-09-01 02:46:40+00'::timestamp with time zone, 'cc573555-8177-419f-a1b0-2b402c9b73d3'::uuid))
               Rows Removed by Filter: 10000
               Heap Blocks: exact=2359
               Buffers: shared hit=2375
               ->  Bitmap Index Scan on messages_idempotency_key (actual time=0.303..0.303 rows=15000 loops=1)
                     Index Cond: (chat_id = '1efc622c-6386-4403-a0dc-6d7e17977038'::uuid)
                     Buffers: shared hit=16
 Planning:
   Buffers: shared hit=4
 Planning Time: 0.099 ms
 Execution Time: 1.665 ms


=== ROLLBACK: test data removed, index is back
 explain_users_left 
--------------------
                  0

