sh student-tests/explain.sh
=== Стенд ===
api mtusi-messenger:lab2 Up About a minute (healthy)
postgres postgres:17.7-alpine@sha256:bb377b7239d2774ac8cc76f481596ce96c5a6b5e9d141f6d0a0ee371a6e7c0f2 Up About a minute (healthy)
valkey valkey/valkey:8.1-alpine@sha256:081c2f5cb575efc901aa80ff9cdbd1ec6a301682fd35e1ebb4b0990a4a4a8507 Up Less than a second (health: starting)

=== Засев 10000 сообщений с неравномерными чатами ===
  seed_duration  | seeded_rows | target_total | messages_total 
-----------------+-------------+--------------+----------------
 00:00:00.001099 |           0 |        10000 |          53293
(1 row)


=== План запроса истории ===
                                       postgres_version                                        
-----------------------------------------------------------------------------------------------
 PostgreSQL 17.7 on aarch64-unknown-linux-musl, compiled by gcc (Alpine 15.2.0) 15.2.0, 64-bit
(1 row)

 messages_total | chats_with_messages | table_size | indexes_size 
----------------+---------------------+------------+--------------
          53293 |                  14 | 5320 kB    | 7152 kB
(1 row)

--- распределение истории по чатам: 5 самых больших и 5 самых маленьких ---
               chat_id                | rows  
--------------------------------------+-------
 69ee7d45-68b4-4606-befe-efaeecdedd32 | 38293
 e95dc135-101e-442f-baf7-b21f0ed7edaf | 14955
 20948a76-f794-4765-9fcc-7a9ebccafd2e |    25
 b2bd268f-f9b6-40fb-8039-1bde44c0f9ee |     5
 f4d3517a-4afa-4461-a4fc-16c6302f3d7c |     3
(5 rows)

               chat_id                | rows 
--------------------------------------+------
 6dbc6664-e048-4858-9ce5-f507afb9fd7c |    1
 ac1c8e3d-4e29-44fc-b770-d3ef6f2351ae |    1
 5a65b658-2555-4ac9-b10a-9a081f81f37b |    1
 17c53c09-19a0-4c0c-87f8-2f8c66468119 |    1
 451812c7-05dc-4b1d-9ddb-3679c4ccd15f |    1
(5 rows)

--- A. keyset, первая страница самого большого чата ---
                                                                  QUERY PLAN                                                                  
----------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.41..5.43 rows=50 width=67) (actual time=0.005..0.013 rows=50 loops=1)
   Buffers: shared hit=24
   ->  Index Scan using messages_history_idx on messages  (cost=0.41..3813.50 rows=38014 width=67) (actual time=0.005..0.010 rows=50 loops=1)
         Index Cond: (chat_id = '69ee7d45-68b4-4606-befe-efaeecdedd32'::uuid)
         Buffers: shared hit=24
 Planning:
   Buffers: shared hit=8
 Planning Time: 0.036 ms
 Execution Time: 0.021 ms
(9 rows)

--- B. keyset, страница на глубине 90% истории (так работает cursor API) ---
                                                                                                       QUERY PLAN                                                                                                        
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.41..8.31 rows=50 width=67) (actual time=0.006..0.013 rows=50 loops=1)
   Buffers: shared hit=53
   ->  Index Scan using messages_history_idx on messages  (cost=0.41..2141.23 rows=13555 width=67) (actual time=0.006..0.011 rows=50 loops=1)
         Index Cond: ((chat_id = '69ee7d45-68b4-4606-befe-efaeecdedd32'::uuid) AND (ROW(created_at, id) > ROW('2026-10-23 20:11:34.584521+00'::timestamp with time zone, 'aa6010bb-02c6-4d17-986b-0f5103547576'::uuid)))
         Buffers: shared hit=53
 Planning Time: 0.023 ms
 Execution Time: 0.032 ms
(7 rows)

--- C. OFFSET на той же глубине (так API не делает: цена растёт со страницей) ---
                                                                   QUERY PLAN                                                                    
-------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=3457.31..3462.32 rows=50 width=67) (actual time=4.463..4.470 rows=50 loops=1)
   Buffers: shared hit=34920
   ->  Index Scan using messages_history_idx on messages  (cost=0.41..3813.50 rows=38014 width=67) (actual time=0.005..3.799 rows=34513 loops=1)
         Index Cond: (chat_id = '69ee7d45-68b4-4606-befe-efaeecdedd32'::uuid)
         Buffers: shared hit=34920
 Planning Time: 0.016 ms
 Execution Time: 4.476 ms
(7 rows)

--- индексы таблицы messages ---
        index         |  size   | scans 
----------------------+---------+-------
 messages_history_idx | 4832 kB |    12
 messages_pkey        | 2320 kB |     0
(2 rows)

