Files
mtusi2026lab2/scripts/explain_history.sql

14 lines
602 B
SQL

-- Run with:
-- docker compose exec -T postgres psql -U "$POSTGRES_USER" -d "$POSTGRES_DB" < scripts/explain_history.sql
-- This script selects an existing chat and explains the exact keyset history query.
-- For the required >=10,000-row experiment, seed the chosen chat first, then ANALYZE.
\pset pager off
SELECT count(*) AS total_messages FROM messages;
ANALYZE messages;
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, chat_id, sender_id, text, created_at
FROM messages
WHERE chat_id = (SELECT chat_id FROM messages GROUP BY chat_id ORDER BY count(*) DESC LIMIT 1)
ORDER BY created_at ASC, id ASC
LIMIT 51;