-- Attribute the unique-ification error to a node.
--
-- Two plans compute the same semijoin over the same data:
--   H  Merge Join(Sort(probe), Sort(HashAggregate(rhs)))   -- planner's choice
--   U  Merge Join(Sort(probe), Unique(Sort(rhs)))          -- the faster plan
--
-- Exclusive cost and time are a node's totals less its children's; dividing
-- gives cost units per millisecond, so a node far below the plan's median rate
-- is undercharged.  TIMING ON inflates row-heavy nodes, so the TIMING OFF
-- totals are printed alongside.

\set ECHO none
SET max_parallel_workers_per_gather = 0;
SET work_mem = '256MB';
SET jit = off;


SELECT setseed(0.42);
CREATE TABLE probe AS
  SELECT (random() * 999999)::int AS id FROM generate_series(1, 4000000);
ANALYZE probe;
CREATE TABLE rhs AS
  SELECT (random() * 999999)::int AS k FROM generate_series(1, 2000000);
ANALYZE rhs;

CREATE TABLE attrib (
    plan_label  text,
    cache_kb    int,
    path        text,
    depth       int,
    node        text,
    excl_cost   float8,
    excl_ms     float8,
    rows        float8,
    total_cost  float8,
    total_ms    float8,
    untimed_ms  float8
);

DO $$
DECLARE
    plan_label  text;
    cache_kb    int;
    js          jsonb;
    untimed     float8;
    q text := 'SELECT count(*) FROM probe p WHERE p.id IN (SELECT k FROM rhs)';
BEGIN
    FOREACH cache_kb IN ARRAY ARRAY[0, 8192] LOOP
        EXECUTE format('SET effective_cpu_cache_size = %s', cache_kb);
        EXECUTE 'SET enable_hashjoin = off';

        FOREACH plan_label IN ARRAY ARRAY['H_hashagg_dedup', 'U_sort_dedup',
                                          'H_forced_hashagg'] LOOP
            EXECUTE 'SET enable_hashagg = on';
            EXECUTE 'SET enable_groupagg = on';
            IF plan_label = 'U_sort_dedup' THEN
                EXECUTE 'SET enable_hashagg = off';
            ELSIF plan_label = 'H_forced_hashagg' THEN
                -- enable_groupagg also gates the sort-based Unique dedup path,
                -- so this forces hash dedup and exposes its penalised cost
                EXECUTE 'SET enable_groupagg = off';
            END IF;

            -- untimed total, for the instrumentation-overhead check
            EXECUTE format('EXPLAIN (ANALYZE, TIMING OFF, FORMAT JSON) %s', q) INTO js;
            untimed := (js -> 0 ->> 'Execution Time')::float8;

            EXECUTE format('EXPLAIN (ANALYZE, TIMING ON, FORMAT JSON) %s', q) INTO js;

            INSERT INTO attrib
            WITH RECURSIVE walk(node, depth, path) AS (
                SELECT js -> 0 -> 'Plan', 0, '0'
                UNION ALL
                SELECT c.value, w.depth + 1, w.path || '.' || c.ordinality
                FROM walk w,
                     jsonb_array_elements(w.node -> 'Plans')
                         WITH ORDINALITY AS c(value, ordinality)
                WHERE w.node ? 'Plans'
            )
            SELECT plan_label,
                   cache_kb,
                   path,
                   depth,
                   (node ->> 'Node Type')
                       || coalesce(' (' || (node ->> 'Strategy') || ')', ''),
                   (node ->> 'Total Cost')::float8
                       - coalesce((SELECT sum((c ->> 'Total Cost')::float8)
                                   FROM jsonb_array_elements(node -> 'Plans') c), 0),
                   (node ->> 'Actual Total Time')::float8
                       - coalesce((SELECT sum((c ->> 'Actual Total Time')::float8)
                                   FROM jsonb_array_elements(node -> 'Plans') c), 0),
                   (node ->> 'Actual Rows')::float8,
                   (node ->> 'Total Cost')::float8,
                   (js -> 0 ->> 'Execution Time')::float8,
                   untimed
            FROM walk;
        END LOOP;
    END LOOP;
END $$;

RESET enable_hashagg;
RESET enable_hashjoin;

\echo ===== instrumentation overhead: timed vs untimed totals =====
SELECT plan_label, cache_kb,
       round(max(untimed_ms)::numeric, 1) AS untimed_ms,
       round(max(total_ms)::numeric, 1)   AS timed_ms,
       round(max(total_cost)::numeric, 1) AS plan_cost
FROM attrib GROUP BY plan_label, cache_kb ORDER BY cache_kb, plan_label;

\echo
\echo ===== per-node attribution, cost units per millisecond =====
SELECT plan_label, cache_kb, repeat('  ', depth) || node AS node,
       round(excl_cost::numeric, 0) AS excl_cost,
       round(excl_ms::numeric, 1)   AS excl_ms,
       CASE WHEN excl_ms > 0.5
            THEN round((excl_cost / excl_ms)::numeric, 1) END AS cost_per_ms
FROM attrib
ORDER BY cache_kb, plan_label, path;

\echo
\echo ===== the dedup subtree only: what each strategy costs and takes =====
SELECT plan_label, cache_kb,
       round(sum(excl_cost)::numeric, 0) AS dedup_cost,
       round(sum(excl_ms)::numeric, 1)   AS dedup_ms,
       round((sum(excl_cost) / nullif(sum(excl_ms), 0))::numeric, 1) AS cost_per_ms
FROM attrib
WHERE node LIKE 'Aggregate%' OR node LIKE 'Unique%'
   OR (node LIKE 'Sort%' AND rows < 3000000)
   OR node LIKE 'Seq Scan%'
GROUP BY plan_label, cache_kb
ORDER BY cache_kb, plan_label;
