Query Processing — SQL text থেকে result rows

লক্ষ্য: EXPLAIN (FORMAT JSON) যা দেয় তার সরল রূপ consume করে terminal-এ মানবপাঠ্য tree — ঠিক যেভাবে আপনি psql-এ text output পড়েন, কিন্তু estimate-drift warning সহ।

import json

WARN_X = 10.0

def _pad(prefix, last):
    return prefix + ('   ' if last else '│  ')

def fmt_head(n):
    label = n['Node Type']
    if 'Relation Name' in n:
        label += f" on {n['Relation Name']}"
        if n.get('Alias') != n.get('Relation Name') and n.get('Alias'):
            label += f" as {n['Alias']}"
    if 'Join Type' in n:
        label += f" ({n['Join Type']})"
    su, tc = n['Startup Cost'], n['Total Cost']
    head = f"{label}  (cost={su:.2f}..{tc:.2f} rows={n['Plan Rows']} width={n['Plan Width']})"
    if 'Actual Rows' in n:
        head += f" (actual rows={n['Actual Rows']} loops={n.get('Actual Loops', 1)})"
    return head

def fmt_extras(n):
    out = []
    for k in ('Sort Key', 'Group Key', 'Hash Cond', 'Join Filter',
              'Index Cond', 'Filter', 'Strategy'):
        if k in n:
            out.append(f"{k}: {n[k]}")
    return out

def render(n, prefix='', last=True, lines=None):
    lines = lines if lines is not None else []
    branch = '└─ ' if last else '├─ '
    lines.append(f"{prefix}{branch}{fmt_head(n)}")
    pad = _pad(prefix, last)
    for e in fmt_extras(n):
        lines.append(f"{pad}   {e}")
    if 'Actual Rows' in n:
        loops = n.get('Actual Loops', 1)
        est_t, act_t = n['Plan Rows'] * loops, n['Actual Rows'] * loops
        hi, lo = max(est_t, act_t), max(min(est_t, act_t), 1)
        if hi / lo >= WARN_X:
            lines.append(f"{pad}   ⚠ estimate off by {hi/lo:.0f}x "
                         f"(planned {n['Plan Rows']}, actual {n['Actual Rows']}, loops={loops})")
    kids = n.get('Plans', [])
    for i, k in enumerate(kids):
        render(k, pad, i == len(kids) - 1, lines)
    return lines

def print_plan(doc):
    root = doc['Plan'] if isinstance(doc, dict) and 'Plan' in doc else doc
    for line in render(root, '', True):
        print(line)

# ---- sample plans (EXPLAIN FORMAT JSON-এর সরলীকৃত রূপ) ----
HASH_AGG_PLAN = {
  'Node Type': 'Finalize GroupAggregate', 'Startup Cost': 9000, 'Total Cost': 9500,
  'Plan Rows': 40, 'Plan Width': 24, 'Actual Rows': 2000, 'Actual Loops': 1,
  'Group Key': ['c.name'],
  'Plans': [{
    'Node Type': 'Gather Merge', 'Startup Cost': 8500, 'Total Cost': 9400,
    'Plan Rows': 60, 'Plan Width': 24, 'Actual Rows': 4000, 'Actual Loops': 1,
    'Plans': [{
      'Node Type': 'Partial HashAggregate', 'Startup Cost': 7400, 'Total Cost': 7600,
      'Plan Rows': 30, 'Plan Width': 24, 'Actual Rows': 2000, 'Actual Loops': 2,
      'Group Key': ['c.name'],
      'Plans': [{
        'Node Type': 'Hash Join', 'Startup Cost': 300, 'Total Cost': 6800,
        'Plan Rows': 98000, 'Plan Width': 32, 'Actual Rows': 45000, 'Actual Loops': 2,
        'Hash Cond': '(o.customer_id = c.id)',
        'Plans': [
          {'Node Type': 'Seq Scan', 'Relation Name': 'orders', 'Alias': 'o',
           'Startup Cost': 0, 'Total Cost': 3200, 'Plan Rows': 30000, 'Plan Width': 16,
           'Actual Rows': 44877, 'Actual Loops': 2, 'Filter': "(amount > '500'::numeric)"},
          {'Node Type': 'Hash', 'Startup Cost': 250, 'Total Cost': 280,
           'Plan Rows': 2000, 'Plan Width': 16, 'Actual Rows': 2000, 'Actual Loops': 2,
           'Plans': [
             {'Node Type': 'Seq Scan', 'Relation Name': 'customers', 'Alias': 'c',
              'Startup Cost': 0, 'Total Cost': 25, 'Plan Rows': 2000, 'Plan Width': 16,
              'Actual Rows': 2000, 'Actual Loops': 2}]}]}]}]}]}

if __name__ == '__main__':
    print_plan(HASH_AGG_PLAN)

প্রত্যাশিত output (renderer-এর হিসাব অনুযায়ী):

└─ Finalize GroupAggregate  (cost=9000.00..9500.00 rows=40 width=24) (actual rows=2000 loops=1)
      Group Key: ['c.name']
      ⚠ estimate off by 50x (planned 40, actual 2000, loops=1)
   └─ Gather Merge  (cost=8500.00..9400.00 rows=60 width=24) (actual rows=4000 loops=1)
         ⚠ estimate off by 67x (planned 60, actual 4000, loops=1)
      └─ Partial HashAggregate  (cost=7400.00..7600.00 rows=30 width=24) (actual rows=2000 loops=2)
            Group Key: ['c.name']
            ⚠ estimate off by 67x (planned 30, actual 2000, loops=2)
         └─ Hash Join  (cost=300.00..6800.00 rows=98000 width=32) (actual rows=45000 loops=2)
               Hash Cond: (o.customer_id = c.id)
            ├─ Seq Scan on orders as o  (cost=0.00..3200.00 rows=30000 width=16) (actual rows=44877 loops=2)
            │     Filter: (amount > '500'::numeric)
            └─ Hash  (cost=250.00..280.00 rows=2000 width=16) (actual rows=2000 loops=2)
               └─ Seq Scan on customers as c  (cost=0.00..25.00 rows=2000 width=16) (actual rows=2000 loops=2)

⚠ লাইনগুলোই এই tool-এর প্রাণ — estimate drift চেনে না মানুষ, চেনে সংখ্যার অনুপাত।

নিজে বাড়ান:

1. EXPLAIN (FORMAT JSON)-এর আসল output paste করে সরাসরি চালান — json.load-এর পরে একই renderer কাজ করবে
2. Nested Loop + Index Scan-সহ second plan sample যোগ করে দুই plan পাশাপাশি diff করুন
3. ANSI color যোগ করুন — warning লাল, scans নীল, joins হলুদ