forked from mtusi2026/mtusi2026lab2
61 lines
2.8 KiB
Python
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()
|