Pushdown walkthrough, model answer

psql "$TARGET_DSN" -c "SELECT extname, extversion FROM pg_extension WHERE extname = 'pg_clickhouse';"

The one question that sorts every plan

For each panel query, run as shop_analytics:

export ANALYTICS_DSN="postgresql://shop_analytics:${ANALYTICS_PASSWORD}@${TARGET_HOST}:5432/shop?sslmode=require"
for q in bench/queries/*.sql; do
  echo "=== $(basename "$q")"
  { echo 'EXPLAIN (VERBOSE)'; cat "$q"; echo ';'; } | psql "$ANALYTICS_DSN" -f -
done 2>&1 | tee /tmp/explain-before.txt

Then ask one question per plan: is the aggregation inside the remote query, or above the Foreign Scan? Everything else is detail.

Three things to read, in this order:

1. A Foreign Scan node exists, on a relation in schema ch. If there is none, you are not routed at all — that is a search_path or session-recycling problem, not a pushdown one, and the fix is in module 06 Step 3.
2. Under VERBOSE, the remote query the wrapper will send. This is the whole answer. If it carries the sum(...), the count(*) and the GROUP BY, ClickHouse is doing the work. If it is a bare column list, ClickHouse is being used as a very fast disk and Postgres is doing the maths over a network.
3. No Seq Scan or Index Scan on a public. relation. One of those means part of the query is still reading the local heap, which is a different failure from a missing pushdown and has a different cause.

The shape that pushed down

Sort
  Output: hour, revenue
  Sort Key: hour
  ->  Foreign Scan
        Output: hour, revenue
        Relations: (ch.order_items)
        Remote SQL: SELECT toStartOfHour(placed_at), sum(line_total) FROM shop.order_items
                    WHERE placed_at >= ... GROUP BY 1

The aggregate and the GROUP BY are inside Remote SQL, and the only local node is a cheap Sort over the tens of rows that come back. Node names and the exact remote text vary by version; the property to recognize is the aggregate appearing on that line.

The shape that did not

WindowAgg
  Output: day, revenue, avg(revenue) OVER (...)
  ->  Sort
        Sort Key: day
        ->  HashAggregate
              Output: date_trunc('day', placed_at), sum(line_total)
              Group Key: date_trunc('day', placed_at)
              ->  Foreign Scan
                    Output: placed_at, line_total
                    Relations: (ch.order_items)
                    Remote SQL: SELECT placed_at, line_total FROM shop.order_items
                                WHERE placed_at >= ...

Remote SQL selects two bare columns. The HashAggregate above the Foreign Scan is Postgres aggregating rows it pulled across the wire — tens of millions of them for a 90-day window. This panel is now slower than it was on RDS, where the same aggregate at least ran over a local index with no network in the middle.

Corroborate from the far end

A plan says what the planner intends. system.query_log says what arrived. Run this in ClickHouse after a sweep:

SELECT event_time, read_rows, formatReadableSize(read_bytes) AS read, query
FROM system.query_log
WHERE type = 'QueryFinish' AND user = 'workshop' AND event_time > now() - INTERVAL 10 MINUTE
ORDER BY event_time DESC
LIMIT 20;

A pushed-down panel appears as one query containing sum( and GROUP BY, with a small result. A panel that did not push down appears as a query selecting bare columns with read_rows in the millions. read_rows is the tell and it is not a matter of interpretation — it is why this corroboration is worth doing even when the plan looks obvious.

The eight panels, and what to look for in each

Shapes, not verdicts. What each panel is made of determines which part of the plan to read first.

q4 and q6 are the two worth reading closely after q7. Nested aggregation and a correlated-looking scalar subquery are both shapes where the planner has a choice, so they are the likeliest to differ between extension versions and between your run and this page.

The designed case: q7_revenue_run_rate

The panel, as it ships in bench/queries/q7_revenue_run_rate.sql:

SELECT day, revenue, avg(revenue) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS rolling_7d
FROM (
  SELECT date_trunc('day', placed_at) AS day, sum(line_total) AS revenue
  FROM order_items
  WHERE placed_at >= now() - interval '90 days'
  GROUP BY 1
) daily
ORDER BY day

Why it should be one of the fastest panels on the dashboard. The inner aggregate reduces 90 days of order_items to 90 rows. The window function then runs over 90 rows, which is free. If the subquery is planned as its own remote aggregate, the whole panel is one small ClickHouse query plus trivial local work.

Why it can instead be the slowest. When window-function handling prevents the subquery from being planned as a standalone remote aggregate, the planner fuses the levels and the only thing it can push is the scan and the filter. You get the second shape above: WindowAgg over Sort over HashAggregate over a Foreign Scan returning placed_at and line_total for every matching row. The panel becomes worse than it was on RDS in module 01 — that regression is the lesson, and it is the reason EXPLAIN is mandatory here rather than advisory.

The repair

Force the daily aggregate to be evaluated as its own step:

WITH daily AS MATERIALIZED (
  SELECT date_trunc('day', placed_at) AS day, sum(line_total) AS revenue
  FROM order_items
  WHERE placed_at >= now() - interval '90 days'
  GROUP BY 1
)
SELECT day, revenue, avg(revenue) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS rolling_7d
FROM daily
ORDER BY day

AS MATERIALIZED is an explicit optimization fence: evaluate the CTE once, on its own, rather than inlining it into the outer query. Normally that is a thing to avoid — you are taking a decision away from the planner — and here it is precisely the point, because the fence is what lets the aggregate be planned as a foreign scan with nothing above it.

What you want to see:

Sort
  ->  WindowAgg
        ->  CTE Scan on daily
  CTE daily
    ->  Foreign Scan
          Relations: (ch.order_items)
          Remote SQL: SELECT toStartOfDay(placed_at), sum(line_total) FROM shop.order_items
                      WHERE placed_at >= ... GROUP BY 1

CTE Scan on daily with the WindowAgg above it, and inside the CTE a Foreign Scan whose remote query carries sum(line_total) and a GROUP BY. Then confirm from system.query_log that read_rows for that query collapsed from millions of shipped rows to a scan returning 90.

If the CTE form still does not push

CTE pushdown sits on the same expanding frontier as window functions, so the fence may not be enough. The fallback removes the window function entirely, expressing the rolling average as a self-join over the same materialized aggregate:

WITH daily AS MATERIALIZED (
  SELECT date_trunc('day', placed_at) AS day, sum(line_total) AS revenue
  FROM order_items
  WHERE placed_at >= now() - interval '90 days'
  GROUP BY 1
)
SELECT d.day, d.revenue, round(avg(w.revenue), 2) AS rolling_7d
FROM daily d
JOIN daily w ON w.day BETWEEN d.day - interval '6 days' AND d.day
GROUP BY d.day, d.revenue
ORDER BY d.day

The join is over 90 rows against 90 rows, so its cost is irrelevant; what matters is that the only remote work left is the aggregate inside the CTE.

Verify the two forms return the same numbers before you keep one. A repair that changes the answer is not a repair, and the round() in the fallback makes them easy to compare only if you compare them deliberately:

cat > /tmp/q7_repaired.sql   # paste whichever repaired form you are keeping, then Ctrl-D
psql "$ANALYTICS_DSN" -f bench/queries/q7_revenue_run_rate.sql > /tmp/q7_before.txt
psql "$ANALYTICS_DSN" -f /tmp/q7_repaired.sql > /tmp/q7_after.txt
diff /tmp/q7_before.txt /tmp/q7_after.txt

Small floating-point differences in the rolling average are acceptable and should be understood rather than waved through; a different number of rows, or a different revenue per day, is a broken repair.

Apply it to both files, or the check catches you

${EDITOR:-nano} bench/queries/q7_revenue_run_rate.sql
${EDITOR:-nano} grafana/dashboards/shop-ops.json
./scripts/preflight.sh && echo "panel SQL matches bench/queries"
git diff --stat -- grafana/dashboards/shop-ops.json bench/queries/
cd grafana && docker compose up -d --force-recreate && cd ..

In the dashboard JSON the panel is Revenue run rate, rolling 7 day (90 days) and the string to replace is its targets[0].rawSql. preflight.sh compares both directions, so editing one side and not the other fails — which is the check doing exactly what it exists for. After a correct repair, git diff --stat names exactly two files.

If q7 pushes down cleanly for you

Then the exercise stands and the target moves: find whichever of your eight queries has an aggregate above its Foreign Scan, and repair that one instead. There is very likely one, and q4, q6 and q8 are where to look — nested aggregation, a scalar subquery, and a conditional aggregate respectively.

If every one of the eight pushes down in your version, that is a real and reportable observation. Record the extension version beside it, and then do the harder exercise: construct a query shape that does not push down and explain from the plan why not. The transferable skill was never "know that q7 is the broken one" — it is reading the remote query and knowing what a plan is telling you.

What not to conclude

• Not "pg_clickhouse is magic". It is a planner, and planners have coverage. ClickHouse's published TPC-H SF1 result is 14 of 22 queries fully pushed down; fourteen of twenty-two is not twenty-two of twenty-two, and the gap is where EXPLAIN earns its place.
• Not "pg_clickhouse is broken". Filters, joins, semi-joins, aggregations and a wide function set — including ordered-set aggregates such as percentile_cont() — push down today. The panel that does not is the exception you have to be able to find, not the rule.
• Not "a fast panel proves the routing worked". It proves something got faster. The three proofs in module 06 Step 4 are what establish that the analytical role reaches ClickHouse and the writer role does not.
• Not "this page's plans are what you should see". They are a snapshot against one version. Yours are the measurement.