C. (chat_id, id)
research=# EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM messages WHERE chat_id = (SELECT id FROM chats WHERE title = 'big') AND (created_at, id) > ('2026-01-15 00:00+00', '00000000-0000-0000-0000-000000000000') ORDER BY created_at, id LIMIT 50;
                                                                       QUERY PLAN                                                                       
--------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=810.77..810.89 rows=50 width=151) (actual time=11.161..11.167 rows=50.00 loops=1)
   Buffers: shared hit=1730
   InitPlan 1
     ->  Seq Scan on chats  (cost=0.00..4.58 rows=1 width=16) (actual time=0.012..0.024 rows=1.00 loops=1)
           Filter: (title = 'big'::text)
           Rows Removed by Filter: 205
           Buffers: shared hit=2
   ->  Sort  (cost=806.19..806.68 rows=193 width=151) (actual time=11.159..11.162 rows=50.00 loops=1)
         Sort Key: messages.created_at, messages.id
         Sort Method: top-N heapsort  Memory: 36kB
         Buffers: shared hit=1730
         ->  Index Scan using idx_messages_chat_id on messages  (cost=0.42..799.78 rows=193 width=151) (actual time=2.648..8.533 rows=39841.00 loops=1)
               Index Cond: (chat_id = (InitPlan 1).col1)
               Filter: (ROW(created_at, id) > ROW('2026-01-15 00:00:00+00'::timestamp with time zone, '00000000-0000-0000-0000-000000000000'::uuid))
               Rows Removed by Filter: 20159
               Index Searches: 1
               Buffers: shared hit=1730
 Planning Time: 0.115 ms
 Execution Time: 11.189 ms
(19 rows)

Time: 11.614 ms
research=# EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM messages WHERE chat_id = (SELECT id FROM chats WHERE title = 'big') AND (created_at, id) > ('2026-01-15 00:00+00', '00000000-0000-0000-0000-000000000000') ORDER BY created_at, id LIMIT 50;
                                                                       QUERY PLAN                                                                        
---------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=810.77..810.89 rows=50 width=151) (actual time=13.602..13.609 rows=50.00 loops=1)
   Buffers: shared hit=1730
   InitPlan 1
     ->  Seq Scan on chats  (cost=0.00..4.58 rows=1 width=16) (actual time=0.034..0.044 rows=1.00 loops=1)
           Filter: (title = 'big'::text)
           Rows Removed by Filter: 205
           Buffers: shared hit=2
   ->  Sort  (cost=806.19..806.68 rows=193 width=151) (actual time=13.601..13.604 rows=50.00 loops=1)
         Sort Key: messages.created_at, messages.id
         Sort Method: top-N heapsort  Memory: 36kB
         Buffers: shared hit=1730
         ->  Index Scan using idx_messages_chat_id on messages  (cost=0.42..799.78 rows=193 width=151) (actual time=3.012..10.954 rows=39841.00 loops=1)
               Index Cond: (chat_id = (InitPlan 1).col1)
               Filter: (ROW(created_at, id) > ROW('2026-01-15 00:00:00+00'::timestamp with time zone, '00000000-0000-0000-0000-000000000000'::uuid))
               Rows Removed by Filter: 20159
               Index Searches: 1
               Buffers: shared hit=1730
 Planning Time: 0.207 ms
 Execution Time: 13.645 ms
(19 rows)

Time: 14.423 ms
research=# EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM messages WHERE chat_id = (SELECT id FROM chats WHERE title = 'small-1') AND (created_at, id) > ('2026-01-01 00:30+00', '00000000-0000-0000-0000-000000000000') ORDER BY created_at, id LIMIT 50;
                                                                     QUERY PLAN                                                                      
-----------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=819.50..819.63 rows=50 width=151) (actual time=0.140..0.148 rows=46.00 loops=1)
   Buffers: shared hit=10
   InitPlan 1
     ->  Seq Scan on chats  (cost=0.00..4.58 rows=1 width=16) (actual time=0.011..0.020 rows=1.00 loops=1)
           Filter: (title = 'small-1'::text)
           Rows Removed by Filter: 205
           Buffers: shared hit=2
   ->  Sort  (cost=814.93..816.07 rows=456 width=151) (actual time=0.139..0.142 rows=46.00 loops=1)
         Sort Key: messages.created_at, messages.id
         Sort Method: quicksort  Memory: 35kB
         Buffers: shared hit=10
         ->  Index Scan using idx_messages_chat_id on messages  (cost=0.42..799.78 rows=456 width=151) (actual time=0.039..0.047 rows=46.00 loops=1)
               Index Cond: (chat_id = (InitPlan 1).col1)
               Filter: (ROW(created_at, id) > ROW('2026-01-01 00:30:00+00'::timestamp with time zone, '00000000-0000-0000-0000-000000000000'::uuid))
               Rows Removed by Filter: 29
               Index Searches: 1
               Buffers: shared hit=10
 Planning Time: 0.084 ms
 Execution Time: 0.167 ms
(19 rows)

Time: 0.532 ms
research=# EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM messages WHERE chat_id = (SELECT id FROM chats WHERE title = 'small-1') AND (created_at, id) > ('2026-01-01 00:30+00', '00000000-0000-0000-0000-000000000000') ORDER BY created_at, id LIMIT 50;
                                                                     QUERY PLAN                                                                      
-----------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=819.50..819.63 rows=50 width=151) (actual time=0.126..0.131 rows=46.00 loops=1)
   Buffers: shared hit=10
   InitPlan 1
     ->  Seq Scan on chats  (cost=0.00..4.58 rows=1 width=16) (actual time=0.032..0.042 rows=1.00 loops=1)
           Filter: (title = 'small-1'::text)
           Rows Removed by Filter: 205
           Buffers: shared hit=2
   ->  Sort  (cost=814.93..816.07 rows=456 width=151) (actual time=0.125..0.127 rows=46.00 loops=1)
         Sort Key: messages.created_at, messages.id
         Sort Method: quicksort  Memory: 35kB
         Buffers: shared hit=10
         ->  Index Scan using idx_messages_chat_id on messages  (cost=0.42..799.78 rows=456 width=151) (actual time=0.075..0.086 rows=46.00 loops=1)
               Index Cond: (chat_id = (InitPlan 1).col1)
               Filter: (ROW(created_at, id) > ROW('2026-01-01 00:30:00+00'::timestamp with time zone, '00000000-0000-0000-0000-000000000000'::uuid))
               Rows Removed by Filter: 29
               Index Searches: 1
               Buffers: shared hit=10
 Planning Time: 0.250 ms
 Execution Time: 0.166 ms
(19 rows)

Time: 1.156 ms


B. (chat_id, created_at, id)
research=# DROP INDEX IF EXISTS idx_messages_chat_id;
DROP INDEX
Time: 3.644 ms
research=# CREATE INDEX idx_messages_chat_history ON messages (chat_id, created_at, id);
CREATE INDEX
Time: 69.022 ms
research=# ANALYZE messages;
ANALYZE
Time: 103.435 ms
research=# EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM messages WHERE chat_id = (SELECT id FROM chats WHERE title = 'big') AND (created_at, id) > ('2026-01-15 00:00+00', '00000000-0000-0000-0000-000000000000') ORDER BY created_at, id LIMIT 50;
                                                                                      QUERY PLAN                                                                                       
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=4.99..93.65 rows=50 width=151) (actual time=0.055..0.069 rows=50.00 loops=1)
   Buffers: shared hit=4 read=4
   InitPlan 1
     ->  Seq Scan on chats  (cost=0.00..4.58 rows=1 width=16) (actual time=0.009..0.019 rows=1.00 loops=1)
           Filter: (title = 'big'::text)
           Rows Removed by Filter: 205
           Buffers: shared hit=2
   ->  Index Scan using idx_messages_chat_history on messages  (cost=0.42..340.85 rows=192 width=151) (actual time=0.054..0.065 rows=50.00 loops=1)
         Index Cond: ((chat_id = (InitPlan 1).col1) AND (ROW(created_at, id) > ROW('2026-01-15 00:00:00+00'::timestamp with time zone, '00000000-0000-0000-0000-000000000000'::uuid)))
         Index Searches: 1
         Buffers: shared hit=4 read=4
 Planning:
   Buffers: shared hit=21 read=1
 Planning Time: 0.333 ms
 Execution Time: 0.092 ms
(15 rows)

Time: 1.018 ms
research=# EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM messages WHERE chat_id = (SELECT id FROM chats WHERE title = 'big') AND (created_at, id) > ('2026-01-15 00:00+00', '00000000-0000-0000-0000-000000000000') ORDER BY created_at, id LIMIT 50;
                                                                                      QUERY PLAN                                                                                       
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=4.99..93.65 rows=50 width=151) (actual time=0.033..0.045 rows=50.00 loops=1)
   Buffers: shared hit=8
   InitPlan 1
     ->  Seq Scan on chats  (cost=0.00..4.58 rows=1 width=16) (actual time=0.011..0.020 rows=1.00 loops=1)
           Filter: (title = 'big'::text)
           Rows Removed by Filter: 205
           Buffers: shared hit=2
   ->  Index Scan using idx_messages_chat_history on messages  (cost=0.42..340.85 rows=192 width=151) (actual time=0.032..0.041 rows=50.00 loops=1)
         Index Cond: ((chat_id = (InitPlan 1).col1) AND (ROW(created_at, id) > ROW('2026-01-15 00:00:00+00'::timestamp with time zone, '00000000-0000-0000-0000-000000000000'::uuid)))
         Index Searches: 1
         Buffers: shared hit=8
 Planning Time: 0.106 ms
 Execution Time: 0.061 ms
(13 rows)

Time: 0.464 ms
research=# EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM messages WHERE chat_id = (SELECT id FROM chats WHERE title = 'small-1') AND (created_at, id) > ('2026-01-01 00:30+00', '00000000-0000-0000-0000-000000000000') ORDER BY created_at, id LIMIT 50;
                                                                                      QUERY PLAN                                                                                       
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=4.99..88.31 rows=50 width=151) (actual time=0.139..0.152 rows=46.00 loops=1)
   Buffers: shared hit=7 read=2
   InitPlan 1
     ->  Seq Scan on chats  (cost=0.00..4.58 rows=1 width=16) (actual time=0.015..0.026 rows=1.00 loops=1)
           Filter: (title = 'small-1'::text)
           Rows Removed by Filter: 205
           Buffers: shared hit=2
   ->  Index Scan using idx_messages_chat_history on messages  (cost=0.42..758.65 rows=455 width=151) (actual time=0.137..0.147 rows=46.00 loops=1)
         Index Cond: ((chat_id = (InitPlan 1).col1) AND (ROW(created_at, id) > ROW('2026-01-01 00:30:00+00'::timestamp with time zone, '00000000-0000-0000-0000-000000000000'::uuid)))
         Index Searches: 1
         Buffers: shared hit=7 read=2
 Planning Time: 0.130 ms
 Execution Time: 0.174 ms
(13 rows)

Time: 0.581 ms
research=# EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM messages WHERE chat_id = (SELECT id FROM chats WHERE title = 'small-1') AND (created_at, id) > ('2026-01-01 00:30+00', '00000000-0000-0000-0000-000000000000') ORDER BY created_at, id LIMIT 50;
                                                                                      QUERY PLAN                                                                                       
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=4.99..88.31 rows=50 width=151) (actual time=0.064..0.073 rows=46.00 loops=1)
   Buffers: shared hit=9
   InitPlan 1
     ->  Seq Scan on chats  (cost=0.00..4.58 rows=1 width=16) (actual time=0.030..0.039 rows=1.00 loops=1)
           Filter: (title = 'small-1'::text)
           Rows Removed by Filter: 205
           Buffers: shared hit=2
   ->  Index Scan using idx_messages_chat_history on messages  (cost=0.42..758.65 rows=455 width=151) (actual time=0.063..0.069 rows=46.00 loops=1)
         Index Cond: ((chat_id = (InitPlan 1).col1) AND (ROW(created_at, id) > ROW('2026-01-01 00:30:00+00'::timestamp with time zone, '00000000-0000-0000-0000-000000000000'::uuid)))
         Index Searches: 1
         Buffers: shared hit=9
 Planning Time: 0.208 ms
 Execution Time: 0.100 ms
(13 rows)

Time: 0.808 ms

A. Only PK
research=# DROP INDEX IF EXISTS idx_messages_chat_history;
DROP INDEX
Time: 5.715 ms
research=# DROP INDEX IF EXISTS idx_messages_chat_id;
NOTICE:  index "idx_messages_chat_id" does not exist, skipping
DROP INDEX
Time: 0.228 ms
research=# ANALYZE messages;
ANALYZE
Time: 102.311 ms
research=# EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM messages WHERE chat_id = (SELECT id FROM chats WHERE title = 'big') AND (created_at, id) > ('2026-01-15 00:00+00', '00000000-0000-0000-0000-000000000000') ORDER BY created_at, id LIMIT 50;
                                                                                       QUERY PLAN                                                                                        
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=4022.99..4023.11 rows=50 width=150) (actual time=10.619..10.625 rows=50.00 loops=1)
   Buffers: shared hit=2264
   InitPlan 1
     ->  Seq Scan on chats  (cost=0.00..4.58 rows=1 width=16) (actual time=0.007..0.019 rows=1.00 loops=1)
           Filter: (title = 'big'::text)
           Rows Removed by Filter: 205
           Buffers: shared hit=2
   ->  Sort  (cost=4018.41..4018.89 rows=193 width=150) (actual time=10.616..10.619 rows=50.00 loops=1)
         Sort Key: messages.created_at, messages.id
         Sort Method: top-N heapsort  Memory: 36kB
         Buffers: shared hit=2264
         ->  Seq Scan on messages  (cost=0.00..4012.00 rows=193 width=150) (actual time=1.644..8.293 rows=39841.00 loops=1)
               Filter: ((chat_id = (InitPlan 1).col1) AND (ROW(created_at, id) > ROW('2026-01-15 00:00:00+00'::timestamp with time zone, '00000000-0000-0000-0000-000000000000'::uuid)))
               Rows Removed by Filter: 60159
               Buffers: shared hit=2264
 Planning:
   Buffers: shared hit=20
 Planning Time: 0.264 ms
 Execution Time: 10.659 ms
(19 rows)

Time: 11.410 ms
research=# EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM messages WHERE chat_id = (SELECT id FROM chats WHERE title = 'big') AND (created_at, id) > ('2026-01-15 00:00+00', '00000000-0000-0000-0000-000000000000') ORDER BY created_at, id LIMIT 50;
                                                                                       QUERY PLAN                                                                                        
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=4022.99..4023.11 rows=50 width=150) (actual time=10.266..10.274 rows=50.00 loops=1)
   Buffers: shared hit=2264
   InitPlan 1
     ->  Seq Scan on chats  (cost=0.00..4.58 rows=1 width=16) (actual time=0.006..0.016 rows=1.00 loops=1)
           Filter: (title = 'big'::text)
           Rows Removed by Filter: 205
           Buffers: shared hit=2
   ->  Sort  (cost=4018.41..4018.89 rows=193 width=150) (actual time=10.265..10.269 rows=50.00 loops=1)
         Sort Key: messages.created_at, messages.id
         Sort Method: top-N heapsort  Memory: 36kB
         Buffers: shared hit=2264
         ->  Seq Scan on messages  (cost=0.00..4012.00 rows=193 width=150) (actual time=1.597..8.150 rows=39841.00 loops=1)
               Filter: ((chat_id = (InitPlan 1).col1) AND (ROW(created_at, id) > ROW('2026-01-15 00:00:00+00'::timestamp with time zone, '00000000-0000-0000-0000-000000000000'::uuid)))
               Rows Removed by Filter: 60159
               Buffers: shared hit=2264
 Planning Time: 0.082 ms
 Execution Time: 10.300 ms
(17 rows)

Time: 11.458 ms
research=# EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM messages WHERE chat_id = (SELECT id FROM chats WHERE title = 'small-1') AND (created_at, id) > ('2026-01-01 00:30+00', '00000000-0000-0000-0000-000000000000') ORDER BY created_at, id LIMIT 50;
                                                                                       QUERY PLAN                                                                                        
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=4031.69..4031.81 rows=50 width=150) (actual time=6.337..6.344 rows=46.00 loops=1)
   Buffers: shared hit=2264
   InitPlan 1
     ->  Seq Scan on chats  (cost=0.00..4.58 rows=1 width=16) (actual time=0.007..0.019 rows=1.00 loops=1)
           Filter: (title = 'small-1'::text)
           Rows Removed by Filter: 205
           Buffers: shared hit=2
   ->  Sort  (cost=4027.11..4028.25 rows=455 width=150) (actual time=6.335..6.339 rows=46.00 loops=1)
         Sort Key: messages.created_at, messages.id
         Sort Method: quicksort  Memory: 35kB
         Buffers: shared hit=2264
         ->  Seq Scan on messages  (cost=0.00..4012.00 rows=455 width=150) (actual time=4.345..6.255 rows=46.00 loops=1)
               Filter: ((chat_id = (InitPlan 1).col1) AND (ROW(created_at, id) > ROW('2026-01-01 00:30:00+00'::timestamp with time zone, '00000000-0000-0000-0000-000000000000'::uuid)))
               Rows Removed by Filter: 99954
               Buffers: shared hit=2264
 Planning Time: 0.105 ms
 Execution Time: 6.366 ms
(17 rows)

Time: 6.763 ms
research=# EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM messages WHERE chat_id = (SELECT id FROM chats WHERE title = 'small-1') AND (created_at, id) > ('2026-01-01 00:30+00', '00000000-0000-0000-0000-000000000000') ORDER BY created_at, id LIMIT 50;
                                                                                       QUERY PLAN                                                                                        
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=4031.69..4031.81 rows=50 width=150) (actual time=6.219..6.227 rows=46.00 loops=1)
   Buffers: shared hit=2264
   InitPlan 1
     ->  Seq Scan on chats  (cost=0.00..4.58 rows=1 width=16) (actual time=0.009..0.021 rows=1.00 loops=1)
           Filter: (title = 'small-1'::text)
           Rows Removed by Filter: 205
           Buffers: shared hit=2
   ->  Sort  (cost=4027.11..4028.25 rows=455 width=150) (actual time=6.217..6.221 rows=46.00 loops=1)
         Sort Key: messages.created_at, messages.id
         Sort Method: quicksort  Memory: 35kB
         Buffers: shared hit=2264
         ->  Seq Scan on messages  (cost=0.00..4012.00 rows=455 width=150) (actual time=4.517..6.205 rows=46.00 loops=1)
               Filter: ((chat_id = (InitPlan 1).col1) AND (ROW(created_at, id) > ROW('2026-01-01 00:30:00+00'::timestamp with time zone, '00000000-0000-0000-0000-000000000000'::uuid)))
               Rows Removed by Filter: 99954
               Buffers: shared hit=2264
 Planning Time: 0.110 ms
 Execution Time: 6.248 ms
(17 rows)

Time: 6.727 ms
