# Индекс истории: EXPLAIN (ANALYZE, BUFFERS) на 60 000 сообщений, 10 000 в одном чате
# дата: 2026-10-08T15:56Z, commit: 6473fd0
# стенд: 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 
----------------+------------------+------------+------------
          60140 |            10000 | 6848 kB    | 6160 kB
(1 row)


== 1. Первая страница (limit 50, запрос берёт 51 строку)
                                                                    QUERY PLAN                                                                     
---------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.41..21.21 rows=51 width=80) (actual time=0.007..0.015 rows=51 loops=1)
   Buffers: shared hit=8
   ->  Index Scan using messages_chat_history_idx on messages  (cost=0.41..4114.85 rows=10089 width=80) (actual time=0.006..0.012 rows=51 loops=1)
         Index Cond: (chat_id = '00000000-0000-4000-8000-000000000100'::uuid)
         Buffers: shared hit=8
 Planning:
   Buffers: shared hit=33
 Planning Time: 0.079 ms
 Execution Time: 0.025 ms
(9 rows)


== 2. Страница из середины истории: keyset после 5000-го сообщения
                                                                                                       QUERY PLAN                                                                                                        
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.41..38.79 rows=51 width=80) (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.41..3778.58 rows=5021 width=80) (actual time=0.006..0.012 rows=51 loops=1)
         Index Cond: ((chat_id = '00000000-0000-4000-8000-000000000100'::uuid) AND (ROW(created_at, id) > ROW('2026-10-08 07:36:22.289031+00'::timestamp with time zone, '31ba47ee-c105-47bd-9bb1-79986700dbd4'::uuid)))
         Buffers: shared hit=8
 Planning Time: 0.031 ms
 Execution Time: 0.034 ms
(7 rows)


== 3. Та же страница через OFFSET (так делать не стали)
                                                                     QUERY PLAN                                                                      
-----------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=2039.48..2060.28 rows=51 width=80) (actual time=0.459..0.464 rows=51 loops=1)
   Buffers: shared hit=491
   ->  Index Scan using messages_chat_history_idx on messages  (cost=0.41..4114.85 rows=10089 width=80) (actual time=0.005..0.359 rows=5051 loops=1)
         Index Cond: (chat_id = '00000000-0000-4000-8000-000000000100'::uuid)
         Buffers: shared hit=491
 Planning Time: 0.016 ms
 Execution Time: 0.469 ms
(7 rows)


== 4. Запрос из пункта 2 без индекса messages_chat_history_idx (DROP INDEX внутри транзакции с ROLLBACK)
                                                                                                        QUERY PLAN                                                                                                         
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=2075.96..2076.09 rows=51 width=80) (actual time=2.252..2.256 rows=51 loops=1)
   Buffers: shared hit=862
   ->  Sort  (cost=2075.96..2088.51 rows=5021 width=80) (actual time=2.252..2.254 rows=51 loops=1)
         Sort Key: created_at, id
         Sort Method: top-N heapsort  Memory: 31kB
         Buffers: shared hit=862
         ->  Seq Scan on messages  (cost=0.00..1908.45 rows=5021 width=80) (actual time=0.939..2.012 rows=5000 loops=1)
               Filter: ((chat_id = '00000000-0000-4000-8000-000000000100'::uuid) AND (ROW(created_at, id) > ROW('2026-10-08 07:36:22.289031+00'::timestamp with time zone, '31ba47ee-c105-47bd-9bb1-79986700dbd4'::uuid)))
               Rows Removed by Filter: 55140
               Buffers: shared hit=856
 Planning:
   Buffers: shared hit=4
 Planning Time: 0.032 ms
 Execution Time: 2.266 ms
(14 rows)


