// HACKER NEWS — CYBERSECURITY
pg_clickhouse v0.10: Subquery pushdown and 1000x faster TPC-H queries
Continuing our investment in pg_clickhouse, improving pushdown coverage for analytic workloads has remained our top focus, with full pushdown across the TPC-H benchmark suite as our immediate metric. We've had a lot of progress since our last update in June, including on the TPC-H scoreboard, which we haven't really talked about since our introductory post back in December, so that's where we'll start. With the release of v0.10.0, our scoreboard has moved from 12 of 22 TPC-H queries fully pushed down to 16, leaving only 6 to go to finish off the set.
Three more TPC-H queries now fully push down. All three were exceedingly inefficient before because, due to the shape of the query, pg_clickhouse had to fetch every row from ClickHouse individually and then evaluate the subquery on it locally (full chart):
( ✔ = whole query is a single foreign scan )
( ✼ = pushed down, but as more than one remote query; typically an outer scan plus one InitPlan scan.)
Q17 is the trophy: a correlated subquery averaging l_quantity per part that, back when it evaluated once per outer row against 6M line items at scale factor 1, took 32.7 seconds. Fully pushed down, it's 37 milliseconds. That's three orders of magnitude difference, and shows off a clear case where pg_clickhouse outperforms native PostgreSQL's own plan for the same query (2.1s).
Six queries remain unpushed: Q13, Q15, Q16, Q18, Q20, Q21. Q16 and Q18 show us the way forward; pg_clickhouse already pushes down the SQL shape they need (IN and NOT IN deparsed as anti/semi-joins, as in Q2 and Q17); what blocks them is that PostgreSQL flattens their subqueries into anti/semi-joins whose inputs are themselves joins, and the deparser doesn't yet walk a join tree on both sides of a join. Q15 and Q20 hit variants of the same issue. That's the next cohesive piece of subquery pushdown.
December's headline feature was teaching the planner to push a whole correlated EXISTS subquery down as a single LEFT SEMI JOIN instead of a nested loop with one ClickHouse round trip per outer row. This moved the needle from 3 of 22 TPC-H queries all the way to 12. The ten remaining queries shared one problem: the planner couldn't fold subqueries into a join at all, so it left a SubPlan behind. This is a piece of a query plan that describes a complete plan for a separate query that runs as part of executing the full query, usually once per row. Pushing that down was the fifth item on our roadmap, and we knocked it out (#289) as of this latest release (0.10.0). Now, subqueries in Postgres become subqueries in ClickHouse:
The EXPLAIN still shows the SubPlan node (that's just PostgreSQL's bookkeeping for the correlation), but you can see the top Remote SQL contains the whole comparison, including the subquery, in one statement we ship to ClickHouse. The same mechanism enables pg_clickhouse to push down the whole of TPC-H Q2: one Foreign Scan and one remote query. NOT IN gets the same treatment via a LEFT ANTI JOIN (the negated cousin of v0.1.0's semi-join) whenever the planner can prove the transformation safe.
Note that none of this works below ClickHouse 25.8, which doesn't support the correlated-subquery SQL shape; pg_clickhouse checks the server version at plan time and falls back to local evaluation on older servers, the same as it always does for unsupported shapes.
Pushing down the SQL was the easy part. The harder part was making sure it computed the same answer PostgreSQL would (#315, #317), and that was its own rabbit hole. ClickHouse's IN operates on two-valued logic, PostgreSQL's on three-valued. That means x NOT IN (1, NULL) can be FALSE (x=1) or NULL in PostgreSQL, but never TRUE. Pushed down naively, these expressions can silently invert results wherever a NULL is involved in a comparison, WHERE NOT IN returning rows PostgreSQL would filter out, GROUP BY merging a NULL group into FALSE, etc. None of it shows up in ordinary testing, which is exactly why it's dangerous in a pushdown extension; the p