Files
mtusi2026lab2/scripts/track2_benchmark.py

125 lines
4.3 KiB
Python

#!/usr/bin/env python3
"""Compare index plans and INSERT cost; write evidence/track2/benchmark.json."""
import json
import os
import sys
import time
import uuid
from datetime import datetime, timezone
from pathlib import Path
import psycopg
ROOT = Path(__file__).resolve().parents[1]
OUT = Path(os.environ.get('TRACK2_EVIDENCE_DIR', str(ROOT / 'evidence' / 'track2')))
DATABASE_URL = os.environ.get('DATABASE_URL', '')
HOT_CHAT_TITLE = os.environ.get('TRACK2_HOT_CHAT_TITLE', 'track2_hot')
def _find_index(plan):
if plan.get('Index Name'):
return plan.get('Index Name'), plan.get('Node Type')
for child in plan.get('Plans') or []:
found = _find_index(child)
if found:
return found
return None, plan.get('Node Type')
def explain(conn, sql, params):
rows = conn.execute('EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) ' + sql, params).fetchone()[0]
plan = rows[0]['Plan']
index_name, node_type = _find_index(plan)
return {
'node_type': node_type,
'index_name': index_name,
'execution_ms': rows[0]['Execution Time'],
'plan_rows': plan.get('Plan Rows'),
}
def insert_batch(conn, chat_id, user_id, n):
base = datetime.now(timezone.utc)
start = time.perf_counter()
with conn.cursor() as cur:
cur.executemany(
'INSERT INTO messages (id, chat_id, sender_id, text, created_at) '
'VALUES (%s, %s, %s, %s, %s)',
[
(str(uuid.uuid4()), chat_id, user_id, 'w', base)
for _ in range(n)
],
)
conn.commit()
elapsed = time.perf_counter() - start
return n / elapsed
def main():
if not DATABASE_URL:
print('DATABASE_URL required', file=sys.stderr)
raise SystemExit(2)
OUT.mkdir(parents=True, exist_ok=True)
with psycopg.connect(DATABASE_URL) as conn:
hot = conn.execute(
'SELECT c.id FROM chats c WHERE c.title = %s',
(HOT_CHAT_TITLE,),
).fetchone()
if not hot:
print('Run track2_seed.py first', file=sys.stderr)
raise SystemExit(2)
hot_id = str(hot[0])
user = conn.execute(
"SELECT id FROM users WHERE username = 'track2_bench'"
).fetchone()
user_id = str(user[0])
keyset_sql = (
'SELECT id FROM messages WHERE chat_id = %s '
'ORDER BY created_at ASC, id ASC LIMIT 50'
)
offset_sql = (
'SELECT id FROM messages WHERE chat_id = %s '
'ORDER BY created_at ASC, id ASC OFFSET 50000 LIMIT 50'
)
plan_keyset = explain(conn, keyset_sql, (hot_id,))
plan_offset = explain(conn, offset_sql, (hot_id,))
conn.execute('DROP INDEX IF EXISTS messages_chat_id_only_idx')
conn.execute('CREATE INDEX messages_chat_id_only_idx ON messages (chat_id)')
conn.commit()
indexes = conn.execute(
"SELECT indexname, indexdef FROM pg_indexes "
"WHERE tablename = 'messages' ORDER BY indexname"
).fetchall()
print(indexes)
plan_keyset_with_extra = explain(conn, keyset_sql, (hot_id,))
write_before = insert_batch(conn, hot_id, user_id, 500)
conn.execute('DROP INDEX IF EXISTS messages_chat_id_only_idx')
conn.commit()
write_after = insert_batch(conn, hot_id, user_id, 500)
result = {
'hypothesis': (
'Keyset pagination on (chat_id, created_at, id) avoids large offsets; '
'redundant (chat_id)-only index does not improve reads but slows writes.'
),
'hot_chat_id': hot_id,
'read_plans': {
'keyset_limit_50': plan_keyset,
'offset_50000_limit_50': plan_offset,
'keyset_with_extra_chat_id_index': plan_keyset_with_extra,
},
'write_messages_per_second': {
'with_extra_chat_id_index': round(write_before, 1),
'production_indexes_only': round(write_after, 1),
},
}
out_path = OUT / 'benchmark.json'
out_path.write_text(json.dumps(result, indent=2, ensure_ascii=False), encoding='utf-8')
print(json.dumps(result, indent=2, ensure_ascii=False))
print('wrote', out_path)
if __name__ == '__main__':
main()