"""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()