forked from mtusi2026/mtusi2026lab2
32 lines
1.8 KiB
SQL
32 lines
1.8 KiB
SQL
-- Планы запросов страницы истории для текущего варианта индекса (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;
|