forked from mtusi2026/mtusi2026lab2
14 lines
602 B
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;
|