#!/bin/sh set -eu ROOT="$(CDPATH= cd -- "$(dirname "$0")/.." && pwd)" cd "$ROOT" OUT="${1:-evidence/explain.txt}" mkdir -p "$(dirname "$OUT")" echo "=== seed >=10000 messages ===" docker compose --env-file .env exec -T postgres psql -U messenger -d messenger <<'SQL' do $$ declare uid uuid := gen_random_uuid(); cid uuid := gen_random_uuid(); begin insert into users(id, username, display_name, password_hash, created_at) values (uid, 'explain_seed_' || substr(replace(uid::text,'-',''),1,8), 'Explain', 'x', now()) on conflict do nothing; insert into chats(id, title, created_at) values (cid, 'explain chat', now()); insert into chat_members(chat_id, user_id) values (cid, uid); insert into messages(id, chat_id, sender_id, text, created_at) select gen_random_uuid(), cid, uid, 'msg-' || g, now() - ((10001 - g) * interval '1 millisecond') from generate_series(1, 10001) g; raise notice 'chat_id=%', cid; end $$; SQL CHAT=$(docker compose --env-file .env exec -T postgres psql -U messenger -d messenger -Atc \ "select id from chats where title='explain chat' order by created_at desc limit 1;") echo "chat_id=$CHAT" { echo "date=$(date -u +%Y-%m-%dT%H:%M:%SZ)" echo "chat_id=$CHAT" echo "count=$(docker compose --env-file .env exec -T postgres psql -U messenger -d messenger -Atc "select count(*) from messages where chat_id='$CHAT';")" echo echo "=== keyset with index messages_history_idx ===" docker compose --env-file .env exec -T postgres psql -U messenger -d messenger < ('1970-01-01'::timestamptz, '00000000-0000-4000-8000-000000000000'::uuid) ORDER BY created_at, id LIMIT 50; SQL echo echo "=== OFFSET page ~200 (full scan risk) ===" docker compose --env-file .env exec -T postgres psql -U messenger -d messenger < "$OUT" echo "wrote $OUT" cat "$OUT" echo "PASS: explain captured"