forked from mtusi2026/mtusi2026lab2
43 lines
1.5 KiB
Bash
Executable File
43 lines
1.5 KiB
Bash
Executable File
#!/bin/sh
|
|
set -eu
|
|
cd "$(dirname "$0")/.."
|
|
mkdir -p evidence
|
|
tmp_file=$(mktemp)
|
|
trap 'rm -f "$tmp_file"' EXIT
|
|
docker compose --env-file .env exec -T postgres psql -U messenger -d messenger -v ON_ERROR_STOP=1 >"$tmp_file" <<'SQL'
|
|
INSERT INTO users(id, username, display_name, password_hash)
|
|
VALUES ('00000000-0000-4000-8000-000000000001', 'explain_user', 'EXPLAIN fixture', 'fixture-not-for-login')
|
|
ON CONFLICT (id) DO NOTHING;
|
|
|
|
INSERT INTO chats(id, title)
|
|
VALUES ('00000000-0000-4000-8000-000000000002', 'EXPLAIN history fixture')
|
|
ON CONFLICT (id) DO NOTHING;
|
|
|
|
INSERT INTO chat_members(chat_id, user_id)
|
|
VALUES ('00000000-0000-4000-8000-000000000002', '00000000-0000-4000-8000-000000000001')
|
|
ON CONFLICT DO NOTHING;
|
|
|
|
DELETE FROM messages WHERE chat_id = '00000000-0000-4000-8000-000000000002';
|
|
INSERT INTO messages(id, chat_id, sender_id, text, created_at)
|
|
SELECT md5('history-message-' || n::text)::uuid,
|
|
'00000000-0000-4000-8000-000000000002',
|
|
'00000000-0000-4000-8000-000000000001',
|
|
'message ' || n::text,
|
|
TIMESTAMPTZ '2026-01-01 00:00:00+00' + n * INTERVAL '1 microsecond'
|
|
FROM generate_series(1, 10000) AS n;
|
|
ANALYZE messages;
|
|
|
|
SELECT count(*) AS fixture_messages
|
|
FROM messages
|
|
WHERE chat_id = '00000000-0000-4000-8000-000000000002';
|
|
|
|
EXPLAIN (ANALYZE, BUFFERS)
|
|
SELECT id, chat_id, sender_id, text, created_at
|
|
FROM messages
|
|
WHERE chat_id = '00000000-0000-4000-8000-000000000002'
|
|
ORDER BY created_at, id
|
|
LIMIT 50;
|
|
SQL
|
|
cp "$tmp_file" evidence/explain-history.txt
|
|
cat "$tmp_file"
|