import 'dotenv/config'; import assert from 'node:assert/strict'; import { createHash, randomUUID } from 'node:crypto'; import { mkdir, writeFile } from 'node:fs/promises'; import { createRequire } from 'node:module'; import { resolve } from 'node:path'; import { spawnSync } from 'node:child_process'; const require = createRequire(import.meta.url); const { Client } = require('pg'); assert.ok(process.env.DATABASE_URL, 'Set DATABASE_URL for the project PostgreSQL'); const output = resolve('evidence'); const schema = `explain_history_${randomUUID().replaceAll('-', '')}`; const table = `"${schema}"."messages"`; const client = new Client({ connectionString: process.env.DATABASE_URL, connectionTimeoutMillis: 5000, query_timeout: 60000, options: '-c statement_timeout=60000 -c lock_timeout=2000', }); const seed = ` WITH base AS ( SELECT n, CASE WHEN (n - 1) % 6 < 2 THEN 0 ELSE (n - 1) % 6 - 1 END AS chat_no FROM generate_series(1, 60000) AS n ), ranked AS ( SELECT n, chat_no, row_number() OVER (PARTITION BY chat_no ORDER BY n) AS chat_seq FROM base ) INSERT INTO ${table} (id, chat_id, sender_id, text, created_at) SELECT md5('message:' || n)::uuid, md5('chat:' || chat_no)::uuid, md5('sender:' || (n % 20))::uuid, rpad('Synthetic message ' || n || ' ', 256, '.'), '2026-01-01T00:00:00Z'::timestamptz + ((chat_seq - 1) / 10) * interval '1 second' FROM ranked ORDER BY n`; const columns = 'id, chat_id, sender_id, text, created_at'; const firstSql = `SELECT ${columns} FROM ${table} WHERE chat_id = $1::uuid ORDER BY created_at ASC, id ASC LIMIT $2`; const orSql = `SELECT ${columns} FROM ${table} WHERE chat_id = $1::uuid AND (created_at > $2::timestamptz OR (created_at = $2::timestamptz AND id > $3::uuid)) ORDER BY created_at ASC, id ASC LIMIT $4`; const tupleSql = `SELECT ${columns} FROM ${table} WHERE chat_id = $1::uuid AND (created_at, id) > ($2::timestamptz, $3::uuid) ORDER BY created_at ASC, id ASC LIMIT $4`; function nodes(plan) { return [plan, ...(plan.Plans ?? []).flatMap(nodes)]; } function median(values) { const sorted = [...values].sort((a, b) => a - b); return sorted[Math.floor(sorted.length / 2)]; } function inspectDocker() { const template = '{"cpus":{{.NCPU}},"memory_bytes":{{.MemTotal}},"server_version":{{json .ServerVersion}},"os":{{json .OperatingSystem}},"arch":{{json .Architecture}}}'; const result = spawnSync('docker', ['info', '--format', template], { encoding: 'utf8', timeout: 10000, windowsHide: true, }); try { if (result.status !== 0) return null; const info = JSON.parse(result.stdout); const ids = spawnSync('docker', ['compose', 'ps', '--quiet', 'postgres'], { encoding: 'utf8', timeout: 10000, windowsHide: true, }); if (ids.status === 0 && ids.stdout.trim()) { const limitsTemplate = '{"image":{{json .Config.Image}},"memory_limit_bytes":{{.HostConfig.Memory}},"nano_cpus":{{.HostConfig.NanoCpus}},"cpu_quota":{{.HostConfig.CpuQuota}},"cpu_period":{{.HostConfig.CpuPeriod}},"cpuset_cpus":{{json .HostConfig.CpusetCpus}}}'; const limits = spawnSync('docker', ['inspect', '--format', limitsTemplate, ids.stdout.trim()], { encoding: 'utf8', timeout: 10000, windowsHide: true, }); if (limits.status === 0) info.postgres_container = JSON.parse(limits.stdout); } return info; } catch { return null; } } await client.connect(); const runs = []; const raw = []; const signatures = new Map(); let metadata; let queries; let cleanup = false; let sourceIndexes; try { sourceIndexes = (await client.query("SELECT indexname, indexdef FROM pg_indexes WHERE schemaname = 'public' AND tablename = 'messages' ORDER BY indexname")).rows; await client.query('BEGIN'); await client.query(`CREATE SCHEMA "${schema}"`); await client.query(`CREATE TABLE ${table} (LIKE public.messages INCLUDING DEFAULTS INCLUDING CONSTRAINTS INCLUDING STORAGE)`); await client.query(seed); await client.query(`ANALYZE ${table}`); const distribution = (await client.query(`SELECT chat_id, count(*)::integer AS messages FROM ${table} GROUP BY chat_id ORDER BY messages DESC, chat_id`)).rows; assert.equal(distribution.reduce((sum, row) => sum + row.messages, 0), 60000); const chatId = distribution.find((row) => row.messages === 20000).chat_id; const cursor = async (offset) => (await client.query( `SELECT id, created_at FROM ${table} WHERE chat_id = $1::uuid ORDER BY created_at, id OFFSET $2 LIMIT 1`, [chatId, offset], )).rows[0]; const middle = await cursor(9999); const late = await cursor(17999); queries = [ { name: 'first_page', sql: firstSql, parameters: [chatId, 51] }, { name: 'middle_page_or', sql: orSql, parameters: [chatId, middle.created_at.toISOString(), middle.id, 51] }, { name: 'late_page_or', sql: orSql, parameters: [chatId, late.created_at.toISOString(), late.id, 51] }, { name: 'late_page_tuple', sql: tupleSql, parameters: [chatId, late.created_at.toISOString(), late.id, 51] }, ]; const settings = (await client.query( 'SELECT name, setting, unit FROM pg_settings WHERE name = ANY($1::text[]) ORDER BY name', [['server_version', 'shared_buffers', 'work_mem', 'random_page_cost', 'effective_cache_size', 'max_parallel_workers_per_gather', 'jit', 'block_size']], )).rows; metadata = { recorded_at: new Date().toISOString(), command: 'node test/explain-history.mjs', node_version: process.version, docker: inspectDocker(), settings, source_table_indexes_before: sourceIndexes, dataset: { total_messages: 60000, target_chat_messages: 20000, chats: 5, distribution, text_length: 256, timestamp_ties: 10, insertion_order: 'interleaved chats' }, method: { warmup_runs_per_query_per_phase: 1, measured_runs: 5, page_size: 50, fetched_rows: 51, cursor_positions: [10000, 18000], cache: 'warm PostgreSQL buffers; Redis is bypassed', cleanup: 'ROLLBACK the experiment schema; live tables and indexes unchanged', planner: 'default scan settings; no forced index or disabled sequential scans', schema_difference: 'copied columns/checks/defaults; foreign keys omitted equally in all phases', tuple_variant: 'current cursor SQL in ChatService.listMessages; OR is the pre-fix baseline' }, }; const phases = [ { name: 'no_indexes', ddl: [] }, { name: 'without_history_index', ddl: [`CREATE UNIQUE INDEX messages_pkey ON ${table} (id)`, `CREATE INDEX messages_sender_id_idx ON ${table} (sender_id)`] }, { name: 'with_history_index', ddl: [`CREATE INDEX messages_chat_id_created_at_id_idx ON ${table} (chat_id, created_at, id)`] }, ]; for (const phase of phases) { for (const ddl of phase.ddl) await client.query(ddl); await client.query(`ANALYZE ${table}`); const indexes = (await client.query('SELECT indexname, indexdef FROM pg_indexes WHERE schemaname = $1 ORDER BY indexname', [schema])).rows; const phaseRuns = new Map(queries.map((query) => [query.name, []])); for (const query of queries) { const rows = (await client.query(query.sql, query.parameters)).rows; assert.equal(rows.length, 51); const signature = createHash('sha256').update(JSON.stringify(rows)).digest('hex'); if (signatures.has(query.name)) assert.equal(signature, signatures.get(query.name)); else signatures.set(query.name, signature); if (query.name === 'late_page_tuple') assert.equal(signature, signatures.get('late_page_or')); await client.query(`EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) ${query.sql}`, query.parameters); } for (let repetition = 1; repetition <= 5; repetition++) { for (const query of queries) { const plan = (await client.query(`EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) ${query.sql}`, query.parameters)).rows[0]['QUERY PLAN'][0]; phaseRuns.get(query.name).push(plan); } } for (const query of queries) { const plans = phaseRuns.get(query.name); const medianMs = median(plans.map((plan) => plan['Execution Time'])); const representative = plans.find((plan) => plan['Execution Time'] === medianMs); const flat = nodes(representative.Plan); const scan = flat.find((node) => node['Node Type'].includes('Scan')); runs.push({ phase: phase.name, query: query.name, indexes, sql: query.sql, parameters: query.parameters, result_sha256: signatures.get(query.name), execution_ms: plans.map((plan) => plan['Execution Time']), median_execution_ms: medianMs, representative_plan: representative, scan_type: scan?.['Node Type'], index_name: scan?.['Index Name'] ?? null, rows_removed_by_filter: scan?.['Rows Removed by Filter'] ?? 0, scanned_rows: ((scan?.['Actual Rows'] ?? 0) + (scan?.['Rows Removed by Filter'] ?? 0)) * (scan?.['Actual Loops'] ?? 1), sort: flat.some((node) => node['Node Type'] === 'Sort'), shared_buffer_hits: representative.Plan['Shared Hit Blocks'] ?? 0, shared_buffer_reads: representative.Plan['Shared Read Blocks'] ?? 0, raw_plans: plans, }); const textPlan = (await client.query(`EXPLAIN (ANALYZE, BUFFERS) ${query.sql}`, query.parameters)).rows.map((row) => row['QUERY PLAN']).join('\n'); raw.push(`=== ${phase.name} / ${query.name} ===\nIndexes: ${indexes.map((index) => index.indexname).join(', ') || '(none)'}\nSQL:\n${query.sql}\nParameters: ${JSON.stringify(query.parameters)}\n\n${textPlan}\n`); console.log(`${phase.name} / ${query.name}: median ${medianMs.toFixed(3)} ms; ${scan?.['Node Type']}; scanned ${runs.at(-1).scanned_rows}; sort=${runs.at(-1).sort}`); } } } finally { try { await client.query('ROLLBACK'); if (metadata) { metadata.source_table_indexes_after = (await client.query("SELECT indexname, indexdef FROM pg_indexes WHERE schemaname = 'public' AND tablename = 'messages' ORDER BY indexname")).rows; assert.deepEqual(metadata.source_table_indexes_after, sourceIndexes); const leftovers = (await client.query('SELECT count(*)::integer AS count FROM pg_namespace WHERE nspname = $1', [schema])).rows[0].count; assert.equal(leftovers, 0); metadata.experiment_schema_absent = true; } cleanup = true; } finally { await client.end(); } } assert.equal(cleanup, true); await mkdir(output, { recursive: true }); await writeFile(resolve(output, 'history-index-explain.json'), JSON.stringify({ ...metadata, cleanup_completed: cleanup, runs }, null, 2) + '\n'); await writeFile(resolve(output, 'history-index-plans.txt'), raw.join('\n')); const labels = { no_indexes: 'Без индексов', without_history_index: 'PK + sender_id, без индекса истории', with_history_index: 'С индексом истории' }; const tableRows = runs.map((row) => `| ${labels[row.phase]} | ${row.query} | ${row.median_execution_ms.toFixed(3)} | ${row.scan_type} | ${row.scanned_rows} | ${row.sort ? 'да' : 'нет'} | ${row.shared_buffer_hits} / ${row.shared_buffer_reads} |`).join('\n'); const speedup = (query) => runs.find((row) => row.query === query && row.phase === 'no_indexes').median_execution_ms / runs.find((row) => row.query === query && row.phase === 'with_history_index').median_execution_ms; const summary = `# EXPLAIN истории сообщений: с индексами и без\n\nДата UTC: ${metadata.recorded_at}. PostgreSQL ${metadata.settings.find((setting) => setting.name === 'server_version').setting}.\n\nКоманда: \`node test/explain-history.mjs\` (нужен \`DATABASE_URL\`; PostgreSQL проекта с применёнными миграциями).\n\n60 000 синтетических сообщений в пяти чатах; в целевом чате 20 000. Чаты перемежаются при вставке, текст 256 символов, по 10 сообщений имеют одинаковый created_at. Страница API: 50 сообщений + 1 для next_cursor. Курсоры после 10 000-го и 18 000-го сообщений. Запросы первой страницы и с OR соответствуют прежнему \`ChatService.listMessages\`; tuple — исправленное условие курсора, используемое в текущем API. Скрипт сравнивает прежний и исправленный запросы; сам код API не меняет.\n\n| Индексы | Запрос | Медиана, мс | Scan | Проверено строк | Sort | Buffers hit/read |\n| --- | --- | ---: | --- | ---: | --- | ---: |\n${tableRows}\n\nПервая страница с индексом быстрее варианта без индексов в ${speedup('first_page').toFixed(1)} раза на этом наборе и стенде. Само наличие PK и индекса sender_id не заменяет индекс истории. Составной B-tree \`(chat_id, created_at, id)\` соответствует равенству chat_id и порядку истории; наличие Sort и границы Index Cond видны в сохранённых планах. ORDER BY нужен и при наличии индекса.\n\nПоздняя страница с OR: ${runs.find((row) => row.phase === 'with_history_index' && row.query === 'late_page_or').scanned_rows} проверенных строк. Эквивалентное сравнение пары: ${runs.find((row) => row.phase === 'with_history_index' && row.query === 'late_page_tuple').scanned_rows} строк. Это показывает, удалось ли передать границу курсора в Index Cond или она осталась Filter. Совпадение результатов всех фаз и OR/tuple проверено SHA-256 от полных 51 строк.\n\nМетодика: отдельная таблица с колонками текущей public.messages в случайной схеме; одинаковые данные во всех фазах; ANALYZE после загрузки и изменений индексов; 1 прогрев + 5 измерений каждого запроса в каждой фазе, медиана PostgreSQL Execution Time. Планы не принуждались настройками enable_seqscan/enable_indexscan. Фаза с индексом сохраняет PK и sender_id. Все создание таблиц, данных и индексов выполнены внутри транзакции; ROLLBACK удалил экспериментальную схему. Рабочие таблицы и индексы не изменялись.\n\nОграничения: это прогретый SQL-тест без HTTP, Redis, аутентификации и конкуренции; не cold-cache benchmark, не измерение RPS и не доказательство latency всего API. FK не копировались, поскольку сравнивается чтение; это одинаково во всех фазах. Измерения зависят от ресурсов и нагрузки Docker-host. Лимиты и настройки PostgreSQL записаны в JSON; текстовые планы сняты отдельным запуском, поэтому их времена могут отличаться от медиан JSON.\n\nФайлы: [полные текстовые планы](history-index-plans.txt), [JSON, пять повторов, настройки и результаты](history-index-explain.json).\n`; await writeFile(resolve(output, 'history-index-summary.md'), summary); console.log(`Saved EXPLAIN evidence to ${output}; experiment schema rolled back.`);