Files

196 lines
15 KiB
JavaScript

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.`);