perf(postgres): group catalog rows once in loadTables() - #12746

// node bench.mjs — extracts the current filter structure and the grouped version,
// checks their output is identical at every size, then times both.
const key2 = (o) => `${o["table_schema"]} ${o["table_name"]}`
const key3 = (o) => `${o["table_schema"]} ${o["table_name"]} ${o["column_name"]}`

function makeData(tables, colsPerTable, consPerTable, fksPerTable, idxPerTable) {
    const dbTables = [], dbColumns = [], dbConstraints = [], dbForeignKeys = [], dbIndices = []
    for (let t = 0; t < tables; t++) {
        const table_schema = `s${t % 3}`, table_name = `t${t}`
        dbTables.push({ table_schema, table_name })
        for (let c = 0; c < colsPerTable; c++)
            dbColumns.push({ table_schema, table_name, column_name: `c${c}`, data_type: "text" })
        for (let k = 0; k < consPerTable; k++)
            dbConstraints.push({
                table_schema, table_name, column_name: `c${k % colsPerTable}`,
                constraint_name: `k${t}_${k}`,
                constraint_type: k % 3 === 0 ? "UNIQUE" : k % 3 === 1 ? "CHECK" : "PRIMARY KEY",
            })
        for (let f = 0; f < fksPerTable; f++)
            dbForeignKeys.push({ table_schema, table_name, constraint_name: `f${t}_${f}` })
        for (let i = 0; i < idxPerTable; i++)
            dbIndices.push({ table_schema, table_name, constraint_name: `i${t}_${i}` })
    }
    return { dbTables, dbColumns, dbConstraints, dbForeignKeys, dbIndices }
}

function before({ dbTables, dbColumns, dbConstraints, dbForeignKeys, dbIndices }) {
    const out = []
    for (const dbTable of dbTables) {
        const cols = dbColumns.filter(
            (dbColumn) =>
                dbColumn["table_name"] === dbTable["table_name"] &&
                dbColumn["table_schema"] === dbTable["table_schema"])
        const colOut = cols.map((dbColumn) => {
            const columnConstraints = dbConstraints.filter(
                (dbConstraint) =>
                    dbConstraint["table_name"] === dbColumn["table_name"] &&
                    dbConstraint["table_schema"] === dbColumn["table_schema"] &&
                    dbConstraint["column_name"] === dbColumn["column_name"])
            return { name: dbColumn["column_name"], n: columnConstraints.length }
        })
        const uniques = dbConstraints.filter(
            (c) => c["table_name"] === dbTable["table_name"] &&
                   c["table_schema"] === dbTable["table_schema"] &&
                   c["constraint_type"] === "UNIQUE")
        const checks = dbConstraints.filter(
            (c) => c["table_name"] === dbTable["table_name"] &&
                   c["table_schema"] === dbTable["table_schema"] &&
                   c["constraint_type"] === "CHECK")
        const fks = dbForeignKeys.filter(
            (c) => c["table_name"] === dbTable["table_name"] &&
                   c["table_schema"] === dbTable["table_schema"])
        const idx = dbIndices.filter(
            (c) => c["table_name"] === dbTable["table_name"] &&
                   c["table_schema"] === dbTable["table_schema"])
        out.push({ t: dbTable["table_name"], colOut, u: uniques.length, k: checks.length, f: fks.length, i: idx.length })
    }
    return out
}

function group(arr, keyOf) {
    const m = new Map()
    for (const x of arr) {
        const k = keyOf(x)
        const cur = m.get(k)
        if (cur) cur.push(x); else m.set(k, [x])
    }
    return m
}

function after({ dbTables, dbColumns, dbConstraints, dbForeignKeys, dbIndices }) {
    const colsBy = group(dbColumns, key2)
    const consByCol = group(dbConstraints, key3)
    const consByTable = group(dbConstraints, key2)
    const fksBy = group(dbForeignKeys, key2)
    const idxBy = group(dbIndices, key2)
    const EMPTY = []
    const out = []
    for (const dbTable of dbTables) {
        const k2 = key2(dbTable)
        const cols = colsBy.get(k2) ?? EMPTY
        const colOut = cols.map((dbColumn) => ({
            name: dbColumn["column_name"],
            n: (consByCol.get(key3(dbColumn)) ?? EMPTY).length,
        }))
        const tableCons = consByTable.get(k2) ?? EMPTY
        out.push({
            t: dbTable["table_name"], colOut,
            u: tableCons.filter((c) => c["constraint_type"] === "UNIQUE").length,
            k: tableCons.filter((c) => c["constraint_type"] === "CHECK").length,
            f: (fksBy.get(k2) ?? EMPTY).length,
            i: (idxBy.get(k2) ?? EMPTY).length,
        })
    }
    return out
}

const median = (a) => { const s = [...a].sort((x, y) => x - y); return s[s.length >> 1] }
function time(fn, data, reps) {
    const t = []
    for (let i = 0; i < reps; i++) { const s = performance.now(); fn(data); t.push(performance.now() - s) }
    return median(t)
}

console.log("tables  columns  constraints   before(ms)   after(ms)   ratio")
for (const T of [5, 20, 50, 100, 300, 800]) {
    const data = makeData(T, 20, 8, 3, 4)
    if (JSON.stringify(before(data)) !== JSON.stringify(after(data)))
        throw new Error(`output mismatch at ${T} tables`)
    const reps = T <= 50 ? 51 : T <= 300 ? 21 : 9
    const b1 = time(before, data, reps), a1 = time(after, data, reps)
    const b2 = time(before, data, reps), a2 = time(after, data, reps)
    const bt = Math.min(b1, b2), at = Math.min(a1, a2)
    console.log(`${String(T).padStart(6)} ${String(T * 20).padStart(8)} ${String(T * 8).padStart(12)}` +
        `${bt.toFixed(3).padStart(13)}${at.toFixed(3).padStart(12)}${(bt / at).toFixed(2).padStart(8)}x`)
}