Files
mtusi2026lab2/scripts/explain_history.py

61 lines
2.8 KiB
Python

"""EXPLAIN on 10,000 synthetic messages; transaction rolls fixtures back."""
import sys
from pathlib import Path
from uuid import uuid4
import psycopg
from argon2 import PasswordHasher
sys.path.insert(0, str(Path(__file__).resolve().parents[1]))
from src.db import connection_kwargs
from src.main import HISTORY_AFTER_SQL, HISTORY_FIRST_SQL
def explain(conn, query, params):
plan = conn.execute("EXPLAIN (ANALYZE, BUFFERS) " + query, params).fetchall()
text = "\n".join(row["QUERY PLAN"] for row in plan)
print(text)
if "messages_history_idx" not in text or "Seq Scan on messages" in text:
raise AssertionError("Expected history index without full messages scan")
def main():
user_id, chat_id = uuid4(), uuid4()
with psycopg.connect(**connection_kwargs()) as conn:
print(conn.execute("SELECT version() AS version").fetchone()["version"])
with conn.transaction(force_rollback=True):
conn.execute(
"INSERT INTO users (id,username,display_name,password_hash) VALUES (%s,%s,%s,%s)",
(user_id, "plan_" + uuid4().hex[:20], "EXPLAIN fixture",
PasswordHasher().hash("Synthetic-password-123")),
)
conn.execute("INSERT INTO chats (id,title) VALUES (%s,%s)", (chat_id, "EXPLAIN fixture"))
conn.execute("INSERT INTO chat_members (chat_id,user_id) 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, 'EXPLAIN fixture ' || n,
TIMESTAMPTZ '2026-01-01T00:00:00Z' + n * INTERVAL '1 microsecond'
FROM generate_series(1,10000) AS n
""", (chat_id, user_id))
conn.execute("ANALYZE messages")
count = conn.execute("SELECT count(*) AS n FROM messages WHERE chat_id=%s",
(chat_id,)).fetchone()["n"]
assert count >= 10000
print("Fixture messages:", count)
print("FIRST PAGE: LIMIT 51 (50 items + lookahead)")
explain(conn, HISTORY_FIRST_SQL, (chat_id, 51))
# OFFSET only selects a reproducible fixture boundary, outside the page query.
boundary = conn.execute(
"SELECT created_at,id FROM messages WHERE chat_id=%s "
"ORDER BY created_at,id OFFSET 8999 LIMIT 1", (chat_id,),
).fetchone()
print("LATER PAGE: after message 9000, LIMIT 51")
explain(conn, HISTORY_AFTER_SQL,
(chat_id, boundary["created_at"], boundary["id"], 51))
conn.execute("ANALYZE messages")
print("PASS: 10000 messages; first/later pages use messages_history_idx; fixtures rolled back")
if __name__ == "__main__":
main()