Files
mtusi2026lab5/scripts/sql/explain-history.sql
ArtyomandClaude Opus 5.5 540fcb35ba Carry over the lab 4 solution
Bring the application, own tests, Dockerfile, toolchain, compose stack,
HAProxy, k6 and RustFS configuration, migrations, hooks, evidence and
scenario scripts and the CI job from the lab 4 solution commit d2744bc,
merge the lab 4 Makefile targets into the lab 5 skeleton Makefile
without touching its targets, and append the lab 4 solution variables
to the skeleton .env.example. The package and the application follow
the contract version 1.5.0, and the image and the throwaway test
containers are tagged lab5 so they do not collide with a lab 4 stand on
the same host.

This is the starting point of the lab 5 cycle: lint, own tests and the
offline skeleton check pass before any lab 5 change.

Co-Authored-By: Claude Opus 5.5 (1M context) <noreply@anthropic.com>
2026-10-29 00:34:03 +03:00

60 lines
2.3 KiB
PL/PgSQL

\set ON_ERROR_STOP on
\set big '''00000000-0000-7000-8000-00000000c001'''
\set cursor_at '''2026-01-01T02:46:40Z'''
\set cursor_id '''00000000-0000-7000-8001-000000002710'''
PREPARE history_first (uuid, int) AS
SELECT id, chat_id, sender_id, text, created_at
FROM messages
WHERE chat_id = $1
ORDER BY created_at, id
LIMIT $2;
PREPARE history_after (uuid, timestamptz, uuid, int) AS
SELECT id, chat_id, sender_id, text, created_at
FROM messages
WHERE chat_id = $1 AND (created_at, id) > ($2, $3)
ORDER BY created_at, id
LIMIT $4;
PREPARE history_offset (uuid, int, int) AS
SELECT id, chat_id, sender_id, text, created_at
FROM messages
WHERE chat_id = $1
ORDER BY created_at, id
LIMIT $2 OFFSET $3;
SELECT count(*) AS chat_messages FROM messages WHERE chat_id = :big;
SELECT count(*) AS all_messages FROM messages;
SELECT pg_size_pretty(pg_relation_size('messages_history_idx')) AS history_index_size;
\echo '== Первая страница, limit=50 (запрос HISTORY_FIRST приложения, LIMIT limit+1)'
EXPLAIN (ANALYZE, BUFFERS) EXECUTE history_first(:big, 51);
\echo '== Глубокая страница: keyset после 10 000-го сообщения (запрос HISTORY_AFTER приложения)'
EXPLAIN (ANALYZE, BUFFERS) EXECUTE history_after(:big, :cursor_at, :cursor_id, 51);
\echo '== Та же глубокая страница через OFFSET 10000 (не используется приложением, для сравнения)'
EXPLAIN (ANALYZE, BUFFERS) EXECUTE history_offset(:big, 51, 10000);
\echo '== Проверка: keyset и OFFSET возвращают одну и ту же страницу'
SELECT (
SELECT array_agg(id ORDER BY created_at, id) FROM (
SELECT id, created_at FROM messages
WHERE chat_id = :big AND (created_at, id) > (:cursor_at, :cursor_id)
ORDER BY created_at, id LIMIT 51
) AS keyset
) = (
SELECT array_agg(id ORDER BY created_at, id) FROM (
SELECT id, created_at FROM messages
WHERE chat_id = :big
ORDER BY created_at, id LIMIT 51 OFFSET 10000
) AS skipped
) AS same_page;
\echo '== Контрольный план без индекса истории (DROP INDEX внутри транзакции, затем ROLLBACK)'
BEGIN;
DROP INDEX messages_history_idx;
EXPLAIN (ANALYZE, BUFFERS) EXECUTE history_after(:big, :cursor_at, :cursor_id, 51);
ROLLBACK;