"""Reproducible 100k-row history-index and insert-cost experiment.""" import csv import json import secrets import sys from datetime import datetime, timezone from time import perf_counter from uuid import uuid4 import psycopg from psycopg.rows import dict_row from src.db import database_url from src.store import Store HISTORY = "CREATE INDEX messages_history_idx ON messages (chat_id, created_at, id)" COVERING = ( "CREATE INDEX messages_history_covering_idx ON messages " "(chat_id, created_at, id) INCLUDE (sender_id, text)" ) PROJECTION = "SELECT id,chat_id,sender_id,text,created_at FROM messages WHERE chat_id=%s" def explain(conn, query, params): result = conn.execute("EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) " + query, params).fetchone()["QUERY PLAN"][0] plan = result["Plan"] names = [] def visit(node): names.append(node.get("Index Name", node["Node Type"])) for child in node.get("Plans", []): visit(child) visit(plan) return result, " > ".join(names) def main(): store = Store() user = store.register("track2_" + uuid4().hex[:16], "Synthetic benchmark", secrets.token_urlsafe(18)) sizes = (("hot", 80_000), ("medium", 15_000), ("cold", 5_000)) chats = [(name, size, store.create_chat(user["id"], "Track 2 " + name, [user["id"]])["id"]) for name, size in sizes] write_chat = store.create_chat(user["id"], "Track 2 writes", [user["id"]])["id"] with psycopg.connect(database_url(), autocommit=True, row_factory=dict_row) as conn: conn.execute("SET statement_timeout=0") base = datetime.now(timezone.utc) for name, size, chat_id in chats: conn.execute( "INSERT INTO messages (id,chat_id,sender_id,text,created_at) " "SELECT gen_random_uuid(),%s,%s,convert_to(%s || n, 'UTF8'),%s::timestamptz + n * interval '1 microsecond' " "FROM generate_series(1,%s) n", (chat_id, user["id"], name + "-synthetic-", base, size), ) conn.execute("VACUUM (ANALYZE) messages") total = sum(conn.execute("SELECT count(*) AS n FROM messages WHERE chat_id=%s", (chat_id,)) .fetchone()["n"] for _, _, chat_id in chats) assert total == 100_000 hot_id = chats[0][2] cursor = conn.execute( "SELECT created_at,id FROM messages WHERE chat_id=%s " "ORDER BY created_at,id OFFSET 71999 LIMIT 1", (hot_id,) ).fetchone() queries = ( ("first", PROJECTION + " ORDER BY created_at,id LIMIT 51", (hot_id,)), ("deep", PROJECTION + " AND (created_at,id)>(%s,%s) " "ORDER BY created_at,id LIMIT 51", (hot_id, cursor["created_at"], cursor["id"])), ) writer = csv.writer(sys.stdout, lineterminator="\n") writer.writerow(("variant", "index", "operation", "trial", "dataset_rows", "execution_ms", "shared_hit", "shared_read", "shared_dirtied", "plan", "rows_inserted")) print(f"UTC: {datetime.now(timezone.utc).isoformat()}", file=sys.stderr) print(f"PostgreSQL: {conn.execute('SHOW server_version').fetchone()['server_version']}", file=sys.stderr) print("Synthetic chat sizes: hot=80000, medium=15000, cold=5000; fixed 51-row pages; deep cursor=72000", file=sys.stderr) try: conn.execute("DROP INDEX IF EXISTS messages_history_covering_idx") conn.execute(HISTORY.replace("CREATE INDEX", "CREATE INDEX IF NOT EXISTS", 1)) for variant, index in (("A", "messages_history_idx"), ("B", "messages_history_covering_idx")): if variant == "B": conn.execute("DROP INDEX messages_history_idx") conn.execute(COVERING) conn.execute("VACUUM (ANALYZE) messages") print(f"Variant {variant}: {index}", file=sys.stderr) for label, query, params in queries: expected = [row["id"] for row in conn.execute(query, params).fetchall()] assert len(expected) == 51 for _ in range(3): explain(conn, query, params) for trial in range(1, 6): result, plan = explain(conn, query, params) actual = [row["id"] for row in conn.execute(query, params).fetchall()] assert actual == expected node = result["Plan"] writer.writerow((variant, index, label, trial, total, result["Execution Time"], node.get("Shared Hit Blocks", 0), node.get("Shared Read Blocks", 0), node.get("Shared Dirtied Blocks", 0), plan, 0)) if trial == 1: print(json.dumps({"variant": variant, "operation": label, "plan": node}, default=str), file=sys.stderr) # Same 500-row write statement on a separate synthetic chat under each index. # Five committed repetitions include index maintenance and commit time. for trial in range(1, 6): start = perf_counter() with conn.transaction(): conn.execute( "INSERT INTO messages (id,chat_id,sender_id,text,created_at) " "SELECT gen_random_uuid(),%s,%s,convert_to('write-synthetic-' || n, 'UTF8')," "clock_timestamp() + n * interval '1 microsecond' " "FROM generate_series(1,500) n", (write_chat, user["id"]), ) elapsed = (perf_counter() - start) * 1000 writer.writerow((variant, index, "write500", trial, total, round(elapsed, 3), "", "", "", "INSERT 500 + COMMIT", 500)) finally: conn.execute("DROP INDEX IF EXISTS messages_history_covering_idx") conn.execute(HISTORY.replace("CREATE INDEX", "CREATE INDEX IF NOT EXISTS", 1)) assert conn.execute( "SELECT count(*) AS n FROM messages WHERE chat_id=%s", (write_chat,) ).fetchone()["n"] == 5000 print("Original history index restored; committed write rows=5000", file=sys.stderr) if __name__ == "__main__": main()