Files
mtusi2026lab2/scripts/explain.py

31 lines
1.7 KiB
Python

"""Real query-plan experiment with 12,000 messages; transaction rolls back fixtures."""
import json
import sys
import uuid
from pathlib import Path
sys.path.insert(0, str(Path(__file__).resolve().parents[1]))
from src.db import make_pool
pool = make_pool()
with pool.connection() as conn:
user_id, chat_id = uuid.uuid4(), uuid.uuid4()
conn.execute('INSERT INTO users(id,username,display_name,password_hash) VALUES (%s,%s,%s,%s)',
(user_id, 'plan_' + uuid.uuid4().hex[:20], 'Synthetic EXPLAIN', 'no-login'))
conn.execute('INSERT INTO chats(id,title) VALUES (%s,%s)', (chat_id, 'EXPLAIN dataset'))
conn.execute('INSERT INTO members VALUES (%s,%s)', (chat_id, user_id))
conn.execute("INSERT INTO messages(id,chat_id,sender_id,text,created_at) "
"SELECT gen_random_uuid(),%s,%s,'message ' || n, "
"'2026-01-01'::timestamptz + n * interval '1 microsecond' "
"FROM generate_series(1,12000) n", (chat_id, user_id))
conn.execute('ANALYZE messages')
pivot = conn.execute('SELECT created_at,id FROM messages WHERE chat_id=%s '
'ORDER BY created_at,id OFFSET 10000 LIMIT 1', (chat_id,)).fetchone()
plan = conn.execute('EXPLAIN (ANALYZE,BUFFERS,FORMAT JSON) SELECT * FROM messages '
'WHERE chat_id=%s AND (created_at,id)>(%s,%s) '
'ORDER BY created_at,id LIMIT 51',
(chat_id, pivot['created_at'], pivot['id'])).fetchone()['QUERY PLAN']
print(json.dumps({'dataset_messages': 12000, 'cursor_position': 10001,
'page_size_with_lookahead': 51, 'plan': plan}, indent=2, default=str))
conn.rollback()
pool.close()