#!/usr/bin/env python3 """Обоснование индекса истории: EXPLAIN (ANALYZE, BUFFERS) на синтетических данных. Использует ОТДЕЛЬНУЮ БД (миграции применяются к ней же): python scripts/explain_history.py Переменные: DATABASE_URL_FILE/DATABASE_URL указывают на БД для эксперимента (не на рабочую!). Данные: 1 «целевой» чат (TARGET_N сообщений) + 4 чата-шума по NOISE_N — таблица ≈ 5×, как в реальной системе. """ import os import sys import uuid from pathlib import Path sys.path.insert(0, str(Path(__file__).resolve().parents[1])) import psycopg # noqa: E402 from app import config, migrate # noqa: E402 TARGET_N = int(os.environ.get("TARGET_N", 20000)) NOISE_N = int(os.environ.get("NOISE_N", 20000)) PAGE = 50 def main(): migrate.apply_all(config.DATABASE_URL) with psycopg.connect(config.DATABASE_URL, autocommit=True) as conn: conn.execute("TRUNCATE messages, chat_members, chats, sessions, users CASCADE") user = uuid.uuid4() conn.execute("INSERT INTO users (id, username, display_name, password_hash) VALUES (%s,'explain_user','x','x')", (user,)) chats = [uuid.uuid4() for _ in range(5)] for cid in chats: conn.execute("INSERT INTO chats (id, title, created_by) VALUES (%s,'t',%s)", (cid, user)) conn.execute("INSERT INTO chat_members (chat_id, user_id) VALUES (%s,%s)", (cid, user)) # Вставка вперемешку по времени (как у живых чатов): created_at — равномерная шкала + микросекунды. for i, cid in enumerate(chats): n = TARGET_N if i == 0 else NOISE_N conn.execute("""INSERT INTO messages (id, chat_id, sender_id, text, created_at) SELECT gen_random_uuid(), %s, %s, 'message ' || g, timestamptz '2026-01-01 00:00:00+00' + (g * 5 + %s) * interval '1 second' + (random()*1000) * interval '1 microsecond' FROM generate_series(1, %s) g""", (cid, user, i, n)) conn.execute("VACUUM (ANALYZE) messages") target = chats[0] total = conn.execute("SELECT count(*) FROM messages").fetchone()[0] in_chat = conn.execute("SELECT count(*) FROM messages WHERE chat_id=%s", (target,)).fetchone()[0] # Курсор «глубоко в истории»: элемент №15000 по (created_at, id). ts, mid = conn.execute("""SELECT created_at, id FROM messages WHERE chat_id=%s ORDER BY created_at, id OFFSET %s LIMIT 1""", (target, min(15000, in_chat - PAGE - 1))).fetchone() def explain(title, sql, params, pre=None, post=None): print("\n" + "=" * 100 + "\n" + title + "\n" + "=" * 100) with conn.transaction(force_rollback=True): # DDL в PostgreSQL транзакционен: индекс вернётся if pre: conn.execute(pre) for line in conn.execute("EXPLAIN (ANALYZE, BUFFERS) " + sql, params).fetchall(): print(line[0]) print("rows total=%d, rows in target chat=%d, page=%d" % (total, in_chat, PAGE)) print(conn.execute("SELECT version()").fetchone()[0]) first = "SELECT id, chat_id, sender_id, text, created_at FROM messages WHERE chat_id=%s ORDER BY created_at, id LIMIT %s" keyset = ("SELECT id, chat_id, sender_id, text, created_at FROM messages WHERE chat_id=%s " "AND (created_at, id) > (%s, %s) ORDER BY created_at, id LIMIT %s") offset = "SELECT id, chat_id, sender_id, text, created_at FROM messages WHERE chat_id=%s ORDER BY created_at, id OFFSET 15000 LIMIT %s" drop = "DROP INDEX messages_chat_created_id_idx" explain("A. Первая страница, keyset, С индексом (chat_id, created_at, id)", first, (target, PAGE + 1)) explain("B. Страница на глубине ~15 000, keyset, С индексом", keyset, (target, ts, mid, PAGE + 1)) explain("C. Та же страница, keyset, БЕЗ индекса (индекс удалён в откатываемой транзакции)", keyset, (target, ts, mid, PAGE + 1), pre=drop) explain("D. Та же глубина через OFFSET 15000, С индексом (антипример)", offset, (target, PAGE + 1)) explain("E. Первая страница, БЕЗ индекса", first, (target, PAGE + 1), pre=drop) print("\nИндексы messages:") for row in conn.execute("SELECT indexname, indexdef FROM pg_indexes WHERE tablename='messages' ORDER BY 1").fetchall(): print(" ", row[0], "->", row[1]) print("Размер индекса: ", conn.execute("SELECT pg_size_pretty(pg_relation_size('messages_chat_created_id_idx'))").fetchone()[0], "; таблицы:", conn.execute("SELECT pg_size_pretty(pg_relation_size('messages'))").fetchone()[0]) if __name__ == "__main__": main()