"""Real query-plan experiment with 12,000 messages; transaction rolls back fixtures.""" import json import sys import uuid from pathlib import Path sys.path.insert(0, str(Path(__file__).resolve().parents[1])) from src.db import make_pool pool = make_pool() with pool.connection() as conn: user_id, chat_id = uuid.uuid4(), uuid.uuid4() conn.execute('INSERT INTO users(id,username,display_name,password_hash) VALUES (%s,%s,%s,%s)', (user_id, 'plan_' + uuid.uuid4().hex[:20], 'Synthetic EXPLAIN', 'no-login')) conn.execute('INSERT INTO chats(id,title) VALUES (%s,%s)', (chat_id, 'EXPLAIN dataset')) conn.execute('INSERT INTO members 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,'message ' || n, " "'2026-01-01'::timestamptz + n * interval '1 microsecond' " "FROM generate_series(1,12000) n", (chat_id, user_id)) conn.execute('ANALYZE messages') pivot = conn.execute('SELECT created_at,id FROM messages WHERE chat_id=%s ' 'ORDER BY created_at,id OFFSET 10000 LIMIT 1', (chat_id,)).fetchone() plan = conn.execute('EXPLAIN (ANALYZE,BUFFERS,FORMAT JSON) SELECT * FROM messages ' 'WHERE chat_id=%s AND (created_at,id)>(%s,%s) ' 'ORDER BY created_at,id LIMIT 51', (chat_id, pivot['created_at'], pivot['id'])).fetchone()['QUERY PLAN'] print(json.dumps({'dataset_messages': 12000, 'cursor_position': 10001, 'page_size_with_lookahead': 51, 'plan': plan}, indent=2, default=str)) conn.rollback() pool.close()