# Индекс истории: EXPLAIN (ANALYZE, BUFFERS) на 152 048 сообщениях (сид 60 000 плюс записи нагрузочных прогонов), 10 000 в исследуемом чате
# дата: 2026-10-30T18:49Z, commit: 269b449
# стенд: Apple M3 Pro, 11 CPU, 18 GB RAM, macOS 26.1, Docker 29.3.0, Compose 5.1.0

$ make explain
./scripts/explain_history.sh
== Объём данных
 messages_total | messages_in_chat | table_size | index_size 
----------------+------------------+------------+------------
         152048 |            10000 | 16 MB      | 15 MB
(1 row)


== 1. Первая страница (limit 50, запрос берёт 51 строку)
                                                                    QUERY PLAN                                                                     
---------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.42..39.54 rows=51 width=79) (actual time=0.010..0.026 rows=51 loops=1)
   Buffers: shared hit=10
   ->  Index Scan using messages_chat_history_idx on messages  (cost=0.42..7708.63 rows=10050 width=79) (actual time=0.009..0.023 rows=51 loops=1)
         Index Cond: (chat_id = '00000000-0000-4000-8000-000000000100'::uuid)
         Buffers: shared hit=10
 Planning:
   Buffers: shared hit=33
 Planning Time: 0.113 ms
 Execution Time: 0.037 ms
(9 rows)


== 2. Страница из середины истории: keyset после 5000-го сообщения
                                                                                                       QUERY PLAN                                                                                                        
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.42..48.43 rows=51 width=79) (actual time=0.006..0.015 rows=51 loops=1)
   Buffers: shared hit=8
   ->  Index Scan using messages_chat_history_idx on messages  (cost=0.42..7585.63 rows=8058 width=79) (actual time=0.006..0.013 rows=51 loops=1)
         Index Cond: ((chat_id = '00000000-0000-4000-8000-000000000100'::uuid) AND (ROW(created_at, id) > ROW('2026-10-30 10:29:04.094702+00'::timestamp with time zone, 'c58c75ac-5b55-4d4d-b27e-370b05239644'::uuid)))
         Buffers: shared hit=8
 Planning Time: 0.022 ms
 Execution Time: 0.036 ms
(7 rows)


== 3. Та же страница через OFFSET (так делать не стали)
                                                                       QUERY PLAN                                                                       
--------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=3481.50..3481.63 rows=51 width=79) (actual time=2.228..2.232 rows=51 loops=1)
   Buffers: shared hit=992
   ->  Sort  (cost=3469.00..3494.13 rows=10050 width=79) (actual time=1.984..2.116 rows=5051 loops=1)
         Sort Key: created_at, id
         Sort Method: quicksort  Memory: 1713kB
         Buffers: shared hit=992
         ->  Bitmap Heap Scan on messages  (cost=598.31..2800.93 rows=10050 width=79) (actual time=0.339..1.164 rows=10000 loops=1)
               Recheck Cond: (chat_id = '00000000-0000-4000-8000-000000000100'::uuid)
               Heap Blocks: exact=856
               Buffers: shared hit=986
               ->  Bitmap Index Scan on messages_chat_history_idx  (cost=0.00..595.79 rows=10050 width=0) (actual time=0.281..0.281 rows=10000 loops=1)
                     Index Cond: (chat_id = '00000000-0000-4000-8000-000000000100'::uuid)
                     Buffers: shared hit=130
 Planning Time: 0.028 ms
 Execution Time: 2.277 ms
(15 rows)


== 4. Запрос из пункта 2 без индекса messages_chat_history_idx (DROP INDEX внутри транзакции с ROLLBACK)
                                                                                                           QUERY PLAN                                                                                                            
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=4800.35..4806.21 rows=51 width=79) (actual time=3.835..4.779 rows=51 loops=1)
   Buffers: shared hit=2092
   ->  Gather Merge  (cost=4800.35..5345.45 rows=4740 width=79) (actual time=3.834..4.776 rows=51 loops=1)
         Workers Planned: 1
         Workers Launched: 1
         Buffers: shared hit=2092
         ->  Sort  (cost=3800.34..3812.19 rows=4740 width=79) (actual time=2.952..2.953 rows=39 loops=2)
               Sort Key: created_at, id
               Sort Method: top-N heapsort  Memory: 31kB
               Buffers: shared hit=2092
               Worker 0:  Sort Method: top-N heapsort  Memory: 31kB
               ->  Parallel Seq Scan on messages  (cost=0.00..3642.20 rows=4740 width=79) (actual time=2.091..2.813 rows=2500 loops=2)
                     Filter: ((chat_id = '00000000-0000-4000-8000-000000000100'::uuid) AND (ROW(created_at, id) > ROW('2026-10-30 10:29:04.094702+00'::timestamp with time zone, 'c58c75ac-5b55-4d4d-b27e-370b05239644'::uuid)))
                     Rows Removed by Filter: 73524
                     Buffers: shared hit=2077
 Planning:
   Buffers: shared hit=4
 Planning Time: 0.042 ms
 Execution Time: 4.793 ms
(19 rows)


