#!/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 # limit 50 + 1 row to learn whether the next page exists, as the API does 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()