What's new in pg_clickhouse v0.10.0: Subqueries, TPC-H Speedups, C Driver, and Aggregates

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. Along the way, we also rebuilt the binary driver on a new plain-C client library, more than doubled the surface area of functions and aggregates that push down, hardened the binary driver against a couple of concurrency bugs, detailed below. 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): QueryPostgreSQLpg_clickhouse 0.3pg_clickhouse 0.10PushdownQ2588 ms3,446 ms24 ms✔Q172107 ms32,709 ms37 ms✔Q22270 ms1,415 ms45 ms✼ ( ✔ = 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: EXPLAIN (VERBOSE, COSTS OFF) SELECT s.sale_id, s.amount FROM sales s WHERE s.amount > (SELECT 1.5 * avg(s2.amount) FROM sales s2 WHERE s2.item_id = s.item_id) ORDER BY s.sale_id; Foreign Scan on subplan_test.sales s Output: s.sale_id, s.amount Remote SQL: SELECT sale_id, amount FROM subplan_test.sales r1 WHERE ((r1.amount > (SELECT (1.5 * avg(q1_1.amount)) FROM subplan_test.sales q1_1 WHERE ((q1_1.item_id = (r1.item_id)))))) ORDER BY r1.sale_id ASC NULLS LAST SubPlan expr_1 -> Foreign Scan Output: ((1.5 * avg(s2.amount))) Relations: Aggregate on (sales s2) Remote SQL: SELECT (1.5 * avg(amount)) FROM subplan_test.sales WHERE ((item_id = {p1:Int32})) (8 rows) 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.