-- Планы запросов страницы истории для текущего варианта индекса (EXPLAIN (ANALYZE, BUFFERS)). -- Переменные psql: big, small, mid_t, mid_id, end_t, end_id (задаёт run.sh). Запрос — тот, что генерирует EF. \set ON_ERROR_STOP on \pset pager off \echo '--- Q1 первая страница большого чата (80 000), keyset' EXPLAIN (ANALYZE, BUFFERS, COSTS OFF, TIMING OFF) SELECT m.id, m.chat_id, m.created_at, m.sender_id, m.text FROM (SELECT * FROM messages WHERE chat_id = :'big') AS m ORDER BY m.created_at, m.id LIMIT 51; \echo '--- Q2 страница из середины большого чата, keyset' EXPLAIN (ANALYZE, BUFFERS, COSTS OFF, TIMING OFF) SELECT m.id, m.chat_id, m.created_at, m.sender_id, m.text FROM (SELECT * FROM messages WHERE chat_id = :'big' AND (created_at, id) > (:'mid_t', :'mid_id')) AS m ORDER BY m.created_at, m.id LIMIT 51; \echo '--- Q3 последняя страница большого чата, keyset' EXPLAIN (ANALYZE, BUFFERS, COSTS OFF, TIMING OFF) SELECT m.id, m.chat_id, m.created_at, m.sender_id, m.text FROM (SELECT * FROM messages WHERE chat_id = :'big' AND (created_at, id) > (:'end_t', :'end_id')) AS m ORDER BY m.created_at, m.id LIMIT 51; \echo '--- Q4 маленький чат (5 сообщений)' EXPLAIN (ANALYZE, BUFFERS, COSTS OFF, TIMING OFF) SELECT m.id, m.chat_id, m.created_at, m.sender_id, m.text FROM (SELECT * FROM messages WHERE chat_id = :'small') AS m ORDER BY m.created_at, m.id LIMIT 51; \echo '--- Q5 середина большого чата, OFFSET 40000 (для сравнения с keyset)' EXPLAIN (ANALYZE, BUFFERS, COSTS OFF, TIMING OFF) SELECT m.id, m.chat_id, m.created_at, m.sender_id, m.text FROM messages m WHERE m.chat_id = :'big' ORDER BY m.created_at, m.id OFFSET 40000 LIMIT 51;