# Индекс истории: EXPLAIN (ANALYZE, BUFFERS), объём данных в первой таблице вывода
# дата: 2026-10-31T01:05Z, чистый clone
# стенд: 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 
----------------+------------------+------------+------------
          91163 |            10000 | 10120 kB   | 8968 kB
(1 row)


== 1. Первая страница (limit 50, запрос берёт 51 строку)
                                                                         QUERY PLAN                                                                         
------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.42..726.39 rows=51 width=112) (actual time=0.031..0.253 rows=51 loops=1)
   Buffers: shared hit=64
   ->  Index Scan using messages_chat_history_idx on messages  (cost=0.42..145123.89 rows=10195 width=112) (actual time=0.031..0.249 rows=51 loops=1)
         Index Cond: (chat_id = '00000000-0000-4000-8000-000000000100'::uuid)
         Buffers: shared hit=64
         SubPlan 1
           ->  Aggregate  (cost=13.78..13.79 rows=1 width=32) (actual time=0.004..0.004 rows=1 loops=51)
                 Buffers: shared hit=54
                 ->  Sort  (cost=13.74..13.76 rows=6 width=20) (actual time=0.003..0.003 rows=0 loops=51)
                       Sort Key: a."position"
                       Sort Method: quicksort  Memory: 25kB
                       Buffers: shared hit=54
                       ->  Bitmap Heap Scan on message_attachments a  (cost=4.20..13.67 rows=6 width=20) (actual time=0.000..0.000 rows=0 loops=51)
                             Recheck Cond: (message_id = messages.id)
                             Buffers: shared hit=51
                             ->  Bitmap Index Scan on message_attachments_pkey  (cost=0.00..4.20 rows=6 width=0) (actual time=0.000..0.000 rows=0 loops=51)
                                   Index Cond: (message_id = messages.id)
                                   Buffers: shared hit=51
 Planning:
   Buffers: shared hit=78
 Planning Time: 0.235 ms
 Execution Time: 0.286 ms
(22 rows)


== 2. Страница из середины истории: keyset после 5000-го сообщения
                                                                                                       QUERY PLAN                                                                                                        
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.42..735.98 rows=51 width=112) (actual time=0.017..0.251 rows=51 loops=1)
   Buffers: shared hit=59
   ->  Index Scan using messages_chat_history_idx on messages  (cost=0.42..98594.86 rows=6836 width=112) (actual time=0.017..0.248 rows=51 loops=1)
         Index Cond: ((chat_id = '00000000-0000-4000-8000-000000000100'::uuid) AND (ROW(created_at, id) > ROW('2026-10-30 16:45:29.612108+00'::timestamp with time zone, '6dacd396-f0bd-431e-8871-9e3bb7792539'::uuid)))
         Buffers: shared hit=59
         SubPlan 1
           ->  Aggregate  (cost=13.78..13.79 rows=1 width=32) (actual time=0.004..0.004 rows=1 loops=51)
                 Buffers: shared hit=51
                 ->  Sort  (cost=13.74..13.76 rows=6 width=20) (actual time=0.003..0.003 rows=0 loops=51)
                       Sort Key: a."position"
                       Sort Method: quicksort  Memory: 25kB
                       Buffers: shared hit=51
                       ->  Bitmap Heap Scan on message_attachments a  (cost=4.20..13.67 rows=6 width=20) (actual time=0.000..0.000 rows=0 loops=51)
                             Recheck Cond: (message_id = messages.id)
                             Buffers: shared hit=51
                             ->  Bitmap Index Scan on message_attachments_pkey  (cost=0.00..4.20 rows=6 width=0) (actual time=0.000..0.000 rows=0 loops=51)
                                   Index Cond: (message_id = messages.id)
                                   Buffers: shared hit=51
 Planning Time: 0.052 ms
 Execution Time: 0.291 ms
(20 rows)


== 3. Та же страница через OFFSET (так делать не стали)
                                                                          QUERY PLAN                                                                          
--------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=71174.26..71900.24 rows=51 width=112) (actual time=6.185..6.246 rows=51 loops=1)
   Buffers: shared hit=5551
   ->  Index Scan using messages_chat_history_idx on messages  (cost=0.42..145123.89 rows=10195 width=112) (actual time=0.012..6.130 rows=5051 loops=1)
         Index Cond: (chat_id = '00000000-0000-4000-8000-000000000100'::uuid)
         Buffers: shared hit=5551
         SubPlan 1
           ->  Aggregate  (cost=13.78..13.79 rows=1 width=32) (actual time=0.001..0.001 rows=1 loops=5051)
                 Buffers: shared hit=5051
                 ->  Sort  (cost=13.74..13.76 rows=6 width=20) (actual time=0.001..0.001 rows=0 loops=5051)
                       Sort Key: a."position"
                       Sort Method: quicksort  Memory: 25kB
                       Buffers: shared hit=5051
                       ->  Bitmap Heap Scan on message_attachments a  (cost=4.20..13.67 rows=6 width=20) (actual time=0.000..0.000 rows=0 loops=5051)
                             Recheck Cond: (message_id = messages.id)
                             Buffers: shared hit=5051
                             ->  Bitmap Index Scan on message_attachments_pkey  (cost=0.00..4.20 rows=6 width=0) (actual time=0.000..0.000 rows=0 loops=5051)
                                   Index Cond: (message_id = messages.id)
                                   Buffers: shared hit=5051
 Planning Time: 0.038 ms
 Execution Time: 6.266 ms
(20 rows)


== 4. Запрос из пункта 2 без индекса messages_chat_history_idx (DROP INDEX внутри транзакции с ROLLBACK)
                                                                                                           QUERY PLAN                                                                                                            
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=3088.42..3792.21 rows=51 width=112) (actual time=3.362..3.425 rows=51 loops=1)
   Buffers: shared hit=1322
   ->  Result  (cost=3088.42..97424.85 rows=6836 width=112) (actual time=3.362..3.422 rows=51 loops=1)
         Buffers: shared hit=1322
         ->  Sort  (cost=3088.42..3105.51 rows=6836 width=80) (actual time=3.343..3.345 rows=51 loops=1)
               Sort Key: messages.created_at, messages.id
               Sort Method: top-N heapsort  Memory: 31kB
               Buffers: shared hit=1271
               ->  Seq Scan on messages  (cost=0.00..2860.35 rows=6836 width=80) (actual time=1.866..3.076 rows=5000 loops=1)
                     Filter: ((chat_id = '00000000-0000-4000-8000-000000000100'::uuid) AND (ROW(created_at, id) > ROW('2026-10-30 16:45:29.612108+00'::timestamp with time zone, '6dacd396-f0bd-431e-8871-9e3bb7792539'::uuid)))
                     Rows Removed by Filter: 86163
                     Buffers: shared hit=1265
         SubPlan 1
           ->  Aggregate  (cost=13.78..13.79 rows=1 width=32) (actual time=0.001..0.001 rows=1 loops=51)
                 Buffers: shared hit=51
                 ->  Sort  (cost=13.74..13.76 rows=6 width=20) (actual time=0.001..0.001 rows=0 loops=51)
                       Sort Key: a."position"
                       Sort Method: quicksort  Memory: 25kB
                       Buffers: shared hit=51
                       ->  Bitmap Heap Scan on message_attachments a  (cost=4.20..13.67 rows=6 width=20) (actual time=0.000..0.000 rows=0 loops=51)
                             Recheck Cond: (message_id = messages.id)
                             Buffers: shared hit=51
                             ->  Bitmap Index Scan on message_attachments_pkey  (cost=0.00..4.20 rows=6 width=0) (actual time=0.000..0.000 rows=0 loops=51)
                                   Index Cond: (message_id = messages.id)
                                   Buffers: shared hit=51
 Planning:
   Buffers: shared hit=4
 Planning Time: 0.057 ms
 Execution Time: 3.455 ms
(29 rows)


