pg_clickhouse v0.10: Subquery pushdown makes TPC-H queries 1000x faster
pg_clickhouse v0.10: Subquery pushdown and 1000x faster TPC-H queries
pg_clickhouse v0.10.0 pushes down correlated subqueries, enabling full pushdown for 16 of 22 TPC-H queries. A TPC-H Q17 query that took 32.7 seconds now runs in 37 ms. The release also introduces a new C client library, expands pushdown coverage for functions and aggregates, and fixes concurrency bugs in the binary driver.
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.
- nasretdinov
As someone who tried doing things like condition pushdown, transpiling from one language to another, etc, this is really impressive. Correctly pushing subqueries while preserving the semantics of the original database is something I can't imagine I could've ever achieved
- manjose2018
Please consider comparing pg_clickhouse to pgrust.
https://malisper.me/how-we-made-postgres-hundreds-of-times-f...
- manbash
> 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.
LLMs sure love throwing in archaic expressions such as "move the needle". The abrupt contrast in jargons is so unpleasant and keeps throwing me off right in the middle of reading.