# 2026-10-15T23:09:01Z  MacBook, Docker Desktop 28.0.4, без CPU/RAM-лимитов, postgres:16.4-alpine (настройки по умолчанию)
$ python3 student-tests/history_bench.py --big 100000 --small-chats 20 --small-size 500

====================================================================================================
1. Заливка данных
====================================================================================================
залито за 1.8 с
messages_total | chats_with_messages | in_big_chat | table_size | index_size 
----------------+---------------------+-------------+------------+------------
         110205 |                  26 |      100000 | 22 MB      | 11 MB
(1 row)

====================================================================================================
2a. С индексом: первая страница (как в приложении, limit=50 → LIMIT 51)
====================================================================================================
SELECT id, chat_id, sender_id, text, created_at FROM messages WHERE chat_id = '6cf4a78d-a633-497d-96ce-bd82abd08a11' ORDER BY created_at, id LIMIT 51
QUERY PLAN                                               
-------------------------------------------------------------------------------------------------------
 Limit (actual time=0.026..0.038 rows=51 loops=1)
   Buffers: shared hit=5
   ->  Index Scan using messages_chat_order_idx on messages (actual time=0.025..0.035 rows=51 loops=1)
         Index Cond: (chat_id = '6cf4a78d-a633-497d-96ce-bd82abd08a11'::uuid)
         Buffers: shared hit=5
 Planning:
   Buffers: shared hit=137
 Planning Time: 0.396 ms
 Execution Time: 0.064 ms
(9 rows)

====================================================================================================
2b. С индексом: страница после 90000-го сообщения, keyset-курсор (как в приложении)
====================================================================================================
SELECT id, chat_id, sender_id, text, created_at FROM messages WHERE chat_id = '6cf4a78d-a633-497d-96ce-bd82abd08a11' AND (created_at, id) > ('2026-01-01 00:01:30+00', '29f2fea4-8210-4561-9e6a-e0c3a1b513c0'::uuid) ORDER BY created_at, id LIMIT 51
QUERY PLAN                                                                                                    
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit (actual time=0.018..0.029 rows=51 loops=1)
   Buffers: shared hit=7
   ->  Index Scan using messages_chat_order_idx on messages (actual time=0.017..0.026 rows=51 loops=1)
         Index Cond: ((chat_id = '6cf4a78d-a633-497d-96ce-bd82abd08a11'::uuid) AND (ROW(created_at, id) > ROW('2026-01-01 00:01:30+00'::timestamp with time zone, '29f2fea4-8210-4561-9e6a-e0c3a1b513c0'::uuid)))
         Buffers: shared hit=7
 Planning:
   Buffers: shared hit=136
 Planning Time: 0.352 ms
 Execution Time: 0.058 ms
(9 rows)

====================================================================================================
2c. Для сравнения: та же глубина через OFFSET 90000 (так приложение НЕ делает)
====================================================================================================
SELECT id, chat_id, sender_id, text, created_at FROM messages WHERE chat_id = '6cf4a78d-a633-497d-96ce-bd82abd08a11' ORDER BY created_at, id OFFSET 90000 LIMIT 51
QUERY PLAN                                                 
-----------------------------------------------------------------------------------------------------------
 Limit (actual time=12.582..12.591 rows=51 loops=1)
   Buffers: shared hit=4726
   ->  Index Scan using messages_chat_order_idx on messages (actual time=0.019..10.068 rows=90051 loops=1)
         Index Cond: (chat_id = '6cf4a78d-a633-497d-96ce-bd82abd08a11'::uuid)
         Buffers: shared hit=4726
 Planning:
   Buffers: shared hit=137
 Planning Time: 0.377 ms
 Execution Time: 12.607 ms
(9 rows)

====================================================================================================
2d. БЕЗ индекса messages_chat_order_idx (DROP INDEX внутри BEGIN … ROLLBACK, данные не меняются)
====================================================================================================
--- первая страница без индекса
                                          QUERY PLAN                                           
-----------------------------------------------------------------------------------------------
 Limit (actual time=10.974..12.247 rows=51 loops=1)
   Buffers: shared hit=2836
   ->  Gather Merge (actual time=10.973..12.241 rows=51 loops=1)
         Workers Planned: 1
         Workers Launched: 1
         Buffers: shared hit=2836
         ->  Sort (actual time=9.575..9.579 rows=51 loops=2)
               Sort Key: created_at, id
               Sort Method: top-N heapsort  Memory: 39kB
               Buffers: shared hit=2836
               Worker 0:  Sort Method: top-N heapsort  Memory: 40kB
               ->  Parallel Seq Scan on messages (actual time=0.019..6.242 rows=50000 loops=2)
                     Filter: (chat_id = '6cf4a78d-a633-497d-96ce-bd82abd08a11'::uuid)
                     Rows Removed by Filter: 5102
                     Buffers: shared hit=2792
 Planning:
   Buffers: shared hit=104
 Planning Time: 0.367 ms
 Execution Time: 12.294 ms
(19 rows)

--- keyset-страница без индекса
                                                                                                     QUERY PLAN                                                                                                     
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit (actual time=7.631..7.636 rows=51 loops=1)
   Buffers: shared hit=2792
   ->  Sort (actual time=7.630..7.632 rows=51 loops=1)
         Sort Key: created_at, id
         Sort Method: top-N heapsort  Memory: 45kB
         Buffers: shared hit=2792
         ->  Seq Scan on messages (actual time=4.642..7.102 rows=10000 loops=1)
               Filter: ((chat_id = '6cf4a78d-a633-497d-96ce-bd82abd08a11'::uuid) AND (ROW(created_at, id) > ROW('2026-01-01 00:01:30+00'::timestamp with time zone, '29f2fea4-8210-4561-9e6a-e0c3a1b513c0'::uuid)))
               Rows Removed by Filter: 100205
               Buffers: shared hit=2792
 Planning Time: 0.060 ms
 Execution Time: 7.668 ms
(12 rows)
indexname        
-------------------------
 messages_chat_order_idx
 messages_pkey
(2 rows)

====================================================================================================
3. Полный обход истории большого чата через API (limit=100)
====================================================================================================
{
 "pages": 1000,
 "messages_read": 100000,
 "expected": 100000,
 "first_20_pages_median_ms": 3.14,
 "last_20_pages_median_ms": 2.83,
 "all_pages_p50_ms": 2.8,
 "all_pages_p95_ms": 4.33,
 "all_pages_max_ms": 10.96
}
PASS: все 100000 сообщений прочитаны без потерь и повторов
