# EXPLAIN (ANALYZE, BUFFERS) истории сообщений
date_utc: 2026-10-29T06:44:11Z
postgres: 17.6
messages total: 54682
target chat (explain_seed_0) messages: 12000
indexes on messages: CREATE UNIQUE INDEX messages_pkey ON public.messages USING btree (id); CREATE INDEX messages_chat_id_created_at_id_idx ON public.messages USING btree (chat_id, created_at, id)

Pager usage is off.
PREPARE
PREPARE
PREPARE
=== 1. Проверка членства (PK chat_members), generic plan
SET
                                                  QUERY PLAN                                                  
--------------------------------------------------------------------------------------------------------------
 Result  (cost=1.72..1.73 rows=1 width=1) (actual time=0.024..0.025 rows=1 loops=1)
   Buffers: shared hit=1
   InitPlan 1
     ->  Seq Scan on chat_members  (cost=0.00..1.72 rows=1 width=0) (actual time=0.021..0.022 rows=1 loops=1)
           Filter: ((chat_id = $1) AND (user_id = $2))
           Rows Removed by Filter: 39
           Buffers: shared hit=1
 Planning:
   Buffers: shared hit=40
 Planning Time: 1.240 ms
 Execution Time: 0.079 ms
(11 rows)

=== 2. Первая страница, limit 51 (50 + 1 для next_cursor), custom plan
SET
                                                                          QUERY PLAN                                                                           
---------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.41..21.35 rows=51 width=181) (actual time=0.072..0.136 rows=51 loops=1)
   Buffers: shared hit=10
   ->  Index Scan using messages_chat_id_created_at_id_idx on messages m  (cost=0.41..4907.70 rows=11955 width=181) (actual time=0.071..0.128 rows=51 loops=1)
         Index Cond: (chat_id = '7123ea42-c7c8-4598-b6af-37cec6e4e6b0'::uuid)
         Buffers: shared hit=10
 Planning:
   Buffers: shared hit=12
 Planning Time: 0.508 ms
 Execution Time: 0.184 ms
(9 rows)

=== 2g. Первая страница, generic plan (так её выполняет pgx после 5 повторов)
SET
                                                                          QUERY PLAN                                                                          
--------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.41..419.97 rows=342 width=181) (actual time=0.044..0.086 rows=51 loops=1)
   Buffers: shared hit=10
   ->  Index Scan using messages_chat_id_created_at_id_idx on messages m  (cost=0.41..4193.54 rows=3418 width=181) (actual time=0.042..0.077 rows=51 loops=1)
         Index Cond: (chat_id = $1)
         Buffers: shared hit=10
 Planning Time: 0.155 ms
 Execution Time: 0.138 ms
(7 rows)

=== 3. Глубокая страница после 10 000 сообщений, custom plan
SET
                                                                                                       QUERY PLAN                                                                                                       
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.41..136.69 rows=51 width=181) (actual time=0.066..0.106 rows=51 loops=1)
   Buffers: shared hit=5
   ->  Index Scan using messages_chat_id_created_at_id_idx on messages m  (cost=0.41..2840.87 rows=1063 width=181) (actual time=0.065..0.098 rows=51 loops=1)
         Index Cond: ((chat_id = '7123ea42-c7c8-4598-b6af-37cec6e4e6b0'::uuid) AND (ROW(created_at, id) > ROW('2026-10-28 06:42:20.06342+00'::timestamp with time zone, '522a8c8d-5115-4b9a-b0c9-0038ba42a7f9'::uuid)))
         Buffers: shared hit=5
 Planning Time: 0.175 ms
 Execution Time: 0.311 ms
(7 rows)

=== 3g. Глубокая страница, generic plan: граница курсора остаётся в Index Cond
SET
                                                                          QUERY PLAN                                                                          
--------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.41..297.73 rows=114 width=181) (actual time=0.064..0.102 rows=51 loops=1)
   Buffers: shared hit=5
   ->  Index Scan using messages_chat_id_created_at_id_idx on messages m  (cost=0.41..2970.95 rows=1139 width=181) (actual time=0.062..0.093 rows=51 loops=1)
         Index Cond: ((chat_id = $1) AND (ROW(created_at, id) > ROW($2, $3)))
         Buffers: shared hit=5
 Planning Time: 0.159 ms
 Execution Time: 0.202 ms
(7 rows)

=== 3x. Было до исправления: один SQL с «$2 IS NULL OR ...», generic plan — курсор в Filter
PREPARE
                                                                          QUERY PLAN                                                                          
--------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.41..417.67 rows=115 width=181) (actual time=2.282..2.322 rows=51 loops=1)
   Buffers: shared hit=324
   ->  Index Scan using messages_chat_id_created_at_id_idx on messages m  (cost=0.41..4176.62 rows=1151 width=181) (actual time=2.279..2.313 rows=51 loops=1)
         Index Cond: (chat_id = $1)
         Filter: (($2 IS NULL) OR (ROW(created_at, id) > ROW($2, $3)))
         Rows Removed by Filter: 10000
         Buffers: shared hit=324
 Planning Time: 0.187 ms
 Execution Time: 2.393 ms
(9 rows)

=== 4. Контроль: та же глубокая страница через OFFSET 10000 (так делать нельзя)
                                                                             QUERY PLAN                                                                              
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=707.35..710.95 rows=51 width=24) (actual time=2.656..2.669 rows=51 loops=1)
   Buffers: shared hit=135
   ->  Index Only Scan using messages_chat_id_created_at_id_idx on messages m  (cost=0.41..845.55 rows=11955 width=24) (actual time=0.050..1.982 rows=10051 loops=1)
         Index Cond: (chat_id = '7123ea42-c7c8-4598-b6af-37cec6e4e6b0'::uuid)
         Heap Fetches: 40
         Buffers: shared hit=135
 Planning Time: 0.200 ms
 Execution Time: 2.720 ms
(8 rows)

=== 5. Контроль: без индекса (enable_indexscan/bitmapscan off) — полный scan и сортировка
SET
SET
SET
SET
                                                                                                        QUERY PLAN                                                                                                        
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=1988.34..1988.47 rows=51 width=181) (actual time=11.196..11.208 rows=51 loops=1)
   Buffers: shared hit=986
   ->  Sort  (cost=1988.34..1991.00 rows=1063 width=181) (actual time=11.195..11.199 rows=51 loops=1)
         Sort Key: created_at, id
         Sort Method: top-N heapsort  Memory: 38kB
         Buffers: shared hit=986
         ->  Seq Scan on messages m  (cost=0.00..1952.88 rows=1063 width=181) (actual time=1.877..10.115 rows=2000 loops=1)
               Filter: ((chat_id = '7123ea42-c7c8-4598-b6af-37cec6e4e6b0'::uuid) AND (ROW(created_at, id) > ROW('2026-10-28 06:42:20.06342+00'::timestamp with time zone, '522a8c8d-5115-4b9a-b0c9-0038ba42a7f9'::uuid)))
               Rows Removed by Filter: 52682
               Buffers: shared hit=980
 Planning Time: 0.181 ms
 Execution Time: 11.292 ms
(12 rows)

