DataDrivenAlex's picture
Publish interactive portfolio demo
b204dec verified
Raw History Blame Contribute Delete
9.61 kB
<!doctype html><html lang="en"><head><meta charset="utf-8"><meta name="viewport" content="width=device-width,initial-scale=1"><meta name="description" content="Compare EXPLAIN JSON plans against explicit thresholds. Inspect cost shifts, plan-node changes and newly introduced large sequential scans."><title>Postgres Query Plan Regression Lab Β· Alexandru Cojocari</title><style>
:root{color-scheme:dark;--bg:#0b1020;--panel:#121a2b;--line:#27344b;--ink:#eef3fb;--muted:#a9b6c9;--mint:#78e0c0;--blue:#88aaff;--gold:#f2c879;--red:#ff8d8d}*{box-sizing:border-box}body{margin:0;background:radial-gradient(ellipse at 12% 0%,#172844 0,transparent 40%),var(--bg);color:var(--ink);font:15px/1.55 Inter,ui-sans-serif,system-ui,-apple-system,Segoe UI,sans-serif}main{max-width:1120px;margin:auto;padding:42px 24px 64px}.eyebrow{text-transform:uppercase;letter-spacing:.16em;font-size:11px;color:var(--mint);font-weight:800}.hero{display:flex;justify-content:space-between;gap:24px;align-items:flex-start;margin-bottom:26px}.hero h1{font-size:clamp(30px,5vw,48px);line-height:1.06;letter-spacing:-.04em;margin:10px 0 12px}.hero p{max-width:720px;color:var(--muted);margin:0}.pill{border:1px solid #38516c;background:#16253a;color:#c8e2ff;border-radius:999px;padding:7px 12px;font-size:12px;white-space:nowrap}.grid{display:grid;grid-template-columns:repeat(12,1fr);gap:14px}.card{background:linear-gradient(145deg,rgba(23,34,54,.96),rgba(15,22,37,.98));border:1px solid var(--line);border-radius:16px;padding:20px;box-shadow:0 12px 36px #0002}.span12{grid-column:span 12}.span8{grid-column:span 8}.span7{grid-column:span 7}.span6{grid-column:span 6}.span5{grid-column:span 5}.span4{grid-column:span 4}.label{font-size:12px;color:var(--muted);text-transform:uppercase;letter-spacing:.1em}.value{font-size:25px;font-weight:750;margin-top:5px}.muted{color:var(--muted)}.row{display:flex;gap:10px;flex-wrap:wrap;align-items:center}.stats{display:grid;grid-template-columns:repeat(4,1fr);gap:10px}.stat{padding:14px;border:1px solid var(--line);border-radius:12px;background:#0e1727}.stat b{font-size:22px;display:block}.stat span{font-size:12px;color:var(--muted)}button,select,.btn{font:inherit;color:var(--ink);border:1px solid #3b516f;border-radius:10px;background:#192841;padding:10px 14px;cursor:pointer}button:hover,.btn:hover{border-color:var(--mint);transform:translateY(-1px)}button.primary{background:linear-gradient(110deg,#268f83,#3468b8);border-color:transparent;font-weight:700}button.warn{border-color:#8f6654;color:#ffd0a3}.status{display:inline-flex;align-items:center;gap:7px;font-size:13px;font-weight:700}.dot{width:8px;height:8px;border-radius:50%;background:var(--mint);box-shadow:0 0 12px currentColor}.status.warn .dot{background:var(--gold)}.status.bad .dot{background:var(--red)}.flow{display:grid;grid-template-columns:repeat(4,1fr);gap:10px;margin-top:16px}.step{border:1px solid var(--line);border-radius:12px;padding:14px;background:#0c1423;min-height:88px}.step strong{display:block;margin-bottom:4px}.log{display:grid;gap:9px;margin-top:14px}.event{display:flex;justify-content:space-between;gap:14px;border-bottom:1px solid #243149;padding:8px 0;color:var(--muted);font-size:13px}.event b{color:var(--ink);font-weight:600}.two{display:grid;grid-template-columns:1fr 1fr;gap:12px}textarea{width:100%;min-height:210px;resize:vertical;color:#d9e7f8;background:#0a1220;border:1px solid #34445f;border-radius:10px;padding:12px;font:12px/1.5 ui-monospace,SFMono-Regular,monospace}input[type=number]{width:100%;background:#0a1220;color:var(--ink);border:1px solid #34445f;border-radius:9px;padding:10px}.meter{height:8px;background:#26354d;border-radius:9px;overflow:hidden;margin:8px 0}.meter i{display:block;height:100%;background:linear-gradient(90deg,#5dd5af,#6f9cff);width:50%}.table{width:100%;border-collapse:collapse}.table th,.table td{text-align:left;padding:10px 8px;border-bottom:1px solid #29364d;vertical-align:top}.table th{color:var(--muted);font-size:12px;text-transform:uppercase;letter-spacing:.08em}.tag{display:inline-block;border:1px solid #3c526f;border-radius:999px;padding:3px 8px;font-size:11px;color:#c7d8ec}.note{border-left:3px solid var(--gold);padding:10px 14px;background:#241e19;color:#f4dcb8;border-radius:0 10px 10px 0}.repo{color:#a6c1ff;text-decoration:none}.repo:hover{text-decoration:underline}.small{font-size:12px}.divider{height:1px;background:var(--line);margin:16px 0}.choice{display:flex;gap:8px;align-items:center}.choice input{accent-color:var(--mint)}@media(max-width:760px){main{padding:28px 16px}.hero{display:block}.hero .pill{display:inline-block;margin-top:16px}.span8,.span7,.span6,.span5,.span4{grid-column:span 12}.stats{grid-template-columns:repeat(2,1fr)}.flow{grid-template-columns:1fr 1fr}.two{grid-template-columns:1fr}}
</style></head><body><main><header class="hero"><div><div class="eyebrow">AI engineering Β· interactive portfolio work</div><h1>Postgres Query Plan Regression Lab</h1><p>Compare EXPLAIN JSON plans against explicit thresholds. Inspect cost shifts, plan-node changes and newly introduced large sequential scans.</p></div><span class="pill">Browser-only demonstration</span></header><section class="grid"><article class="card span12"><div class="row" style="justify-content:space-between"><div><div class="label">Plan comparison lab</div><div class="value">Spot structural and budget regressions</div></div><button id="compare" class="primary">Compare plans</button></div><p class="muted">Paste PostgreSQL <code>EXPLAIN (FORMAT JSON)</code> outputs. The starter pair reproduces the repository's index-scan β†’ sequential-scan fixture.</p></article><article class="card span6"><div class="label">Baseline plan JSON</div><textarea id="base"></textarea></article><article class="card span6"><div class="label">Candidate plan JSON</div><textarea id="cand"></textarea></article><article class="card span4"><label class="label" for="cost">Max estimated-cost growth (%)</label><input id="cost" type="number" value="25" min="0"><p class="muted small">Planner cost is not wall-clock time.</p></article><article class="card span4"><label class="label" for="rows">Max rows on a new sequential scan</label><input id="rows" type="number" value="10000" min="0"><p class="muted small">Threshold applied to Plan Rows.</p></article><article class="card span4"><div class="label">Result</div><div id="verdict" class="value">Ready</div><p id="summary" class="muted small">Run the comparison to see a report.</p></article><article class="card span12"><div class="stats"><div class="stat"><span>Estimated cost change</span><b id="delta">β€”</b></div><div class="stat"><span>Node shape</span><b id="shape">β€”</b></div><div class="stat"><span>New seq scan rows</span><b id="scan">β€”</b></div><div class="stat"><span>Gate</span><b id="gate">β€”</b></div></div><p id="detail" class="muted small" style="margin-top:16px"></p><div class="note">This tool compares plan JSON; it never connects to PostgreSQL or executes SQL. Estimated cost depends on database statistics and settings. EXPLAIN ANALYZE executes the query.</div></article></section><div class="divider"></div><p class="muted small">Companion demo for <a class="repo" href="https://github.com/DatadrivenAlex/query-plan-regression-lab" target="_blank" rel="noreferrer">the source repository</a>. Starter plan JSON comes from the source repository; no database is queried.</p></main><script>const B=[{"Plan": {"Node Type": "Index Scan", "Relation Name": "orders", "Index Name": "orders_customer_id_idx", "Plan Rows": 20, "Startup Cost": 0.29, "Total Cost": 12.4, "Plans": []}}],C=[{"Plan": {"Node Type": "Seq Scan", "Relation Name": "orders", "Plan Rows": 140000, "Startup Cost": 0.0, "Total Cost": 2420.0, "Plans": []}}];const $=x=>document.getElementById(x);const pretty=x=>JSON.stringify(x,null,2);function plan(x){let v=typeof x==='string'?JSON.parse(x):x;let p=Array.isArray(v)?v[0]?.Plan:v.Plan||v;return p}function nodes(n,a=[]){if(!n)return a;a.push(n);for(const c of n.Plans||[])nodes(c,a);return a}function sample(){$('base').value=pretty(B);$('cand').value=pretty(C)}sample();$('compare').onclick=()=>{try{let b=plan($('base').value),c=plan($('cand').value);let bn=nodes(b),cn=nodes(c),cost=b['Total Cost'],cc=c['Total Cost'];if(!Number.isFinite(cost)||!Number.isFinite(cc))throw Error('Root Total Cost is required.');let pct=cost===0?null:(cc-cost)/cost*100;let btypes=bn.map(n=>n['Node Type']),ctypes=cn.map(n=>n['Node Type']);let news=cn.filter(n=>n['Node Type']==='Seq Scan'&&!bn.some(x=>x['Node Type']==='Seq Scan'&&x['Relation Name']===n['Relation Name']));let rows=Math.max(0,...news.map(n=>Number(n['Plan Rows']||0)));let lim=Number($('cost').value),rlim=Number($('rows').value);let flags=[];if(pct!==null&&pct>lim)flags.push('cost growth exceeds budget');if(rows>rlim)flags.push('new sequential scan exceeds row budget');$('delta').textContent=pct===null?'skipped':pct.toFixed(1)+'%';$('shape').textContent=btypes.join(' β†’ ')+' / '+ctypes.join(' β†’ ');$('scan').textContent=rows.toLocaleString();$('gate').textContent=flags.length?'FAIL':'PASS';$('verdict').textContent=flags.length?'Regression found':'Within budgets';$('summary').textContent=flags.join('; ')||'No configured threshold was exceeded.';$('detail').textContent=`Root estimated cost: ${cost} β†’ ${cc}. Root relation: ${b['Relation Name']||'β€”'} β†’ ${c['Relation Name']||'β€”'}. A percentage is skipped for a zero baseline; runtime is reported only when both plans contain actual timing.`}catch(e){$('verdict').textContent='Input error';$('summary').textContent=e.message}};</script></body></html>