Files
mtusi2026lab2/scripts/explain_history.py
ChernikovandClaude Opus 5.5 921ae002d6 ЛР 2 — БВТ2304 — Черников
PostgreSQL с версионируемыми миграциями, вход по Basic и Bearer-сессии в Valkey
(AOF), Argon2id, compose-стенд с секретами в файлах. Собственные тесты в
student-tests/, проверки инфраструктуры и EXPLAIN в scripts/, отчёт в REPORT.md,
результаты в evidence/, команды для показа в DEMO.md.

Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>
2026-10-15 12:58:54 +03:00

140 lines
6.4 KiB
Python
Executable File

#!/usr/bin/env python3
"""EXPLAIN (ANALYZE, BUFFERS) of the history page query on 10 000+ messages.
python scripts/explain_history.py --output evidence/explain.txt
The stand must be running. The script registers two synthetic users through the API,
creates a target chat and a noise chat, bulk-inserts messages with SQL, runs ANALYZE and
prints the plans of the same query the API runs (src/messenger/db.py, list_messages):
first page, a page in the middle, the last page, the generic plan of the parameterized
query, and for comparison the middle page without the index (DROP INDEX inside a
transaction that is rolled back, so the database is left unchanged).
Standard library only.
"""
import argparse
import base64
import json
from pathlib import Path
import subprocess
import sys
import time
import urllib.request
import uuid
ROOT = Path(__file__).resolve().parents[1]
BASE = 'http://127.0.0.1:8080'
PSQL = ['docker', 'compose', 'exec', '-T', 'postgres', 'psql', '-U', 'messenger', '-d', 'messenger',
'-X', '-v', 'ON_ERROR_STOP=1']
PAGE = 51
lines = []
def out(text=''):
lines.append(text)
print(text, flush=True)
def psql(sql, echo=True):
"""Run SQL in one psql session; with echo the transcript shows every statement before its result."""
args = PSQL + (['--echo-all'] if echo else ['-At']) + ['-f', '-']
done = subprocess.run(args, cwd=str(ROOT), input=sql.encode('utf-8'), stdout=subprocess.PIPE, stderr=subprocess.STDOUT)
text = done.stdout.decode('utf-8', 'replace').strip()
if done.returncode != 0:
out(text)
raise SystemExit('psql failed')
return text
def api(method, path, body=None, headers=None):
headers = dict(headers or {})
data = None
if body is not None:
data = json.dumps(body).encode('utf-8')
headers['Content-Type'] = 'application/json'
request = urllib.request.Request(BASE + path, data=data, headers=headers, method=method)
with urllib.request.urlopen(request, timeout=10) as response:
return json.loads(response.read())
def user(label):
username = label + '_' + uuid.uuid4().hex[:10]
password = 'Explain-' + uuid.uuid4().hex
created = api('POST', '/api/v1/users', {'username': username, 'display_name': label, 'password': password})
credential = base64.b64encode((username + ':' + password).encode()).decode()
session = api('POST', '/api/v1/auth/sessions', headers={'Authorization': 'Basic ' + credential})
return created['id'], session['token']
def page_sql(chat_id, after=None):
"""The same SQL text as Repository.list_messages, with literal values."""
sql = "SELECT id, chat_id, sender_id, text, created_at FROM messages WHERE chat_id = '%s' " % chat_id
if after:
sql += "AND (created_at, id) > ('%s', '%s') " % after
return sql + 'ORDER BY created_at, id LIMIT %d' % PAGE
def main():
parser = argparse.ArgumentParser(description='EXPLAIN of the history query; inserts synthetic messages')
parser.add_argument('--messages', type=int, default=10000, help='messages in the target chat (>= 10000)')
parser.add_argument('--noise', type=int, default=20000, help='messages in another chat')
parser.add_argument('--output', default=None)
args = parser.parse_args()
sys.stdout.reconfigure(errors='replace')
out('# %s UTC, %s' % (time.strftime('%Y-%m-%d %H:%M:%S', time.gmtime()), psql('SELECT version()', echo=False)))
alice, alice_token = user('explain_a')
bob, _ = user('explain_b')
target = api('POST', '/api/v1/chats', {'title': 'EXPLAIN target', 'member_ids': [alice, bob]},
{'Authorization': 'Bearer ' + alice_token})['id']
noise = api('POST', '/api/v1/chats', {'title': 'EXPLAIN noise', 'member_ids': [alice]},
{'Authorization': 'Bearer ' + alice_token})['id']
out('# target chat %s: %d messages; noise chat %s: %d messages' % (target, args.messages, noise, args.noise))
started = time.monotonic()
psql('''
INSERT INTO messages (id, chat_id, sender_id, text, created_at)
SELECT gen_random_uuid(), '%(target)s', CASE WHEN i %% 2 = 0 THEN '%(alice)s'::uuid ELSE '%(bob)s'::uuid END,
'seed message ' || i, clock_timestamp() + i * interval '1 millisecond'
FROM generate_series(1, %(messages)d) AS i;
INSERT INTO messages (id, chat_id, sender_id, text, created_at)
SELECT gen_random_uuid(), '%(noise)s', '%(alice)s', 'noise message ' || i, clock_timestamp() + i * interval '1 millisecond'
FROM generate_series(1, %(noise_count)d) AS i;
ANALYZE messages;
''' % {'target': target, 'noise': noise, 'alice': alice, 'bob': bob, 'messages': args.messages, 'noise_count': args.noise},
echo=False)
out('# inserted and analyzed in %.1f s' % (time.monotonic() - started))
out(psql("SELECT 'messages total: ' || count(*) || ', in target chat: ' || count(*) FILTER (WHERE chat_id = '%s') FROM messages" % target, echo=False))
def key_at(position):
row = psql("SELECT created_at || '|' || id FROM messages WHERE chat_id = '%s' ORDER BY created_at, id OFFSET %d LIMIT 1"
% (target, position - 1), echo=False)
return tuple(row.split('|'))
middle, last = key_at(args.messages // 2), key_at(args.messages - 20)
explain = 'EXPLAIN (ANALYZE, BUFFERS, COSTS OFF) '
out()
out('== 1. First page, middle page and last page: index range scan, no sort ==')
out(psql('\n'.join(explain + page_sql(target, after) + ';' for after in (None, middle, last))))
out()
out('== 2. Generic plan of the parameterized query (how a prepared statement with $1..$4 runs) ==')
out(psql('''SET plan_cache_mode = force_generic_plan;
PREPARE history_page(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;
%sEXECUTE history_page('%s', '%s', '%s', %d);''' % (explain, target, middle[0], middle[1], PAGE)))
out()
out('== 3. For comparison: the same middle page without the index (rolled back) ==')
out(psql('''BEGIN;
DROP INDEX messages_chat_history_idx;
%s%s;
ROLLBACK;''' % (explain, page_sql(target, middle))))
out(psql("SELECT 'index still exists: ' || (to_regclass('messages_chat_history_idx') IS NOT NULL)", echo=False))
if args.output:
Path(args.output).parent.mkdir(parents=True, exist_ok=True)
Path(args.output).write_text('\n'.join(lines) + '\n', encoding='utf-8')
if __name__ == '__main__':
main()