# EXPLAIN (ANALYZE, BUFFERS) истории сообщений
# всего сообщений: 25000; в исследуемом чате: 15000
# keyset-курсор указывает на 10 000-е сообщение чата (глубокая страница)

## С индексом messages_chat_created_idx (chat_id, created_at, id)

### 1. Первая страница
SELECT id, chat_id, sender_id, text, created_at
FROM messages
WHERE chat_id = %(chat)s
ORDER BY created_at, id
LIMIT 51

Limit  (cost=0.29..7.67 rows=51 width=78) (actual time=0.016..0.055 rows=51 loops=1)
  Buffers: shared hit=54
  ->  Index Scan using messages_chat_created_idx on messages  (cost=0.29..2170.70 rows=15000 width=78) (actual time=0.016..0.052 rows=51 loops=1)
        Index Cond: (chat_id = '9a29690f-123d-4123-9ca9-41776e552bc7'::uuid)
        Buffers: shared hit=54
Planning Time: 0.040 ms
Execution Time: 0.078 ms

### 2. Глубокая страница, keyset (как в приложении)
SELECT id, chat_id, sender_id, text, created_at
FROM messages
WHERE chat_id = %(chat)s AND (created_at, id) > (%(t)s, %(i)s)
ORDER BY created_at, id
LIMIT 51

Limit  (cost=0.29..16.94 rows=51 width=78) (actual time=0.023..0.066 rows=51 loops=1)
  Buffers: shared hit=51
  ->  Index Scan using messages_chat_created_idx on messages  (cost=0.29..1627.91 rows=4986 width=78) (actual time=0.023..0.062 rows=51 loops=1)
        Index Cond: ((chat_id = '9a29690f-123d-4123-9ca9-41776e552bc7'::uuid) AND (ROW(created_at, id) > ROW('2026-01-21 01:57:43.327018+00'::timestamp with time zone, '42bea608-77e6-48e1-b1ab-68d0c66a93a1'::uuid)))
        Buffers: shared hit=51
Planning Time: 0.073 ms
Execution Time: 0.086 ms

### 3. Глубокая страница через OFFSET 10000 (для сравнения)
SELECT id, chat_id, sender_id, text, created_at
FROM messages
WHERE chat_id = %(chat)s
ORDER BY created_at, id
OFFSET 10000 LIMIT 51

Limit  (cost=1447.23..1454.61 rows=51 width=78) (actual time=3.272..3.288 rows=51 loops=1)
  Buffers: shared hit=10095
  ->  Index Scan using messages_chat_created_idx on messages  (cost=0.29..2170.70 rows=15000 width=78) (actual time=0.029..2.991 rows=10051 loops=1)
        Index Cond: (chat_id = '9a29690f-123d-4123-9ca9-41776e552bc7'::uuid)
        Buffers: shared hit=10095
Planning Time: 0.166 ms
Execution Time: 3.325 ms

## Без индекса (DROP INDEX в транзакции, затем ROLLBACK)

### 1. Первая страница
SELECT id, chat_id, sender_id, text, created_at
FROM messages
WHERE chat_id = %(chat)s
ORDER BY created_at, id
LIMIT 51

Limit  (cost=1146.93..1147.06 rows=51 width=78) (actual time=2.658..2.665 rows=51 loops=1)
  Buffers: shared hit=340
  ->  Sort  (cost=1146.93..1184.43 rows=15000 width=78) (actual time=2.656..2.659 rows=51 loops=1)
        Sort Key: created_at, id
        Sort Method: top-N heapsort  Memory: 36kB
        Buffers: shared hit=340
        ->  Seq Scan on messages  (cost=0.00..646.50 rows=15000 width=78) (actual time=0.013..1.449 rows=15000 loops=1)
              Filter: (chat_id = '9a29690f-123d-4123-9ca9-41776e552bc7'::uuid)
              Rows Removed by Filter: 10000
              Buffers: shared hit=334
Planning:
  Buffers: shared hit=4
Planning Time: 0.165 ms
Execution Time: 2.705 ms

### 2. Глубокая страница, keyset (как в приложении)
SELECT id, chat_id, sender_id, text, created_at
FROM messages
WHERE chat_id = %(chat)s AND (created_at, id) > (%(t)s, %(i)s)
ORDER BY created_at, id
LIMIT 51

Limit  (cost=937.84..937.97 rows=51 width=78) (actual time=1.914..1.922 rows=51 loops=1)
  Buffers: shared hit=334
  ->  Sort  (cost=937.84..950.31 rows=4986 width=78) (actual time=1.913..1.917 rows=51 loops=1)
        Sort Key: created_at, id
        Sort Method: top-N heapsort  Memory: 36kB
        Buffers: shared hit=334
        ->  Seq Scan on messages  (cost=0.00..771.50 rows=4986 width=78) (actual time=0.009..1.542 rows=5000 loops=1)
              Filter: ((chat_id = '9a29690f-123d-4123-9ca9-41776e552bc7'::uuid) AND (ROW(created_at, id) > ROW('2026-01-21 01:57:43.327018+00'::timestamp with time zone, '42bea608-77e6-48e1-b1ab-68d0c66a93a1'::uuid)))
              Rows Removed by Filter: 20000
              Buffers: shared hit=334
Planning Time: 0.146 ms
Execution Time: 1.952 ms
