Hacktoberfest 2026: the issues maintainers tagged for October, open and beginner-friendly. Browse Hacktoberfest issues

Push down joins against an aggregated `IN (SELECT … GROUP BY … HAVING)` subquery (TPC-H Q18 shape)

Open
  • Is something wrong?
#378 0 comments 0 reactions 1 assignee View on GitHub

Maintainers usually reply within 1 day

@JoshDreamland is already working on this.

Since Sep 25, 2026.

Assessment

This issue has not been assessed yet.

Description

enhancement pushdown sql
Summary

When a query filters a foreign join with IN (SELECT … GROUP BY … HAVING …)
over foreign tables on the same server, pg_clickhouse sends the join and the
subquery as two separate remote queries. The whole join result is then
fetched into Postgres, and Postgres does the join between them. This is the
TPC-H Q18 shape; the v0.10.0 SubPlan work covers Q2/Q11/Q15/Q17/Q20/Q22 but not
this one.

Tested on pg_clickhouse v0.10.0 (also main at d1b59a0), PostgreSQL 18.6,
ClickHouse 26.9.

Reproduction

ClickHouse:

CREATE DATABASE repro;
CREATE TABLE repro.customer (c_custkey Int64, c_name String) ENGINE = MergeTree ORDER BY c_custkey;
CREATE TABLE repro.orders   (o_orderkey Int64, o_custkey Int64, o_totalprice Float64) ENGINE = MergeTree ORDER BY o_orderkey;
CREATE TABLE repro.lineitem (l_orderkey Int64, l_quantity Float64) ENGINE = MergeTree ORDER BY l_orderkey;
INSERT INTO repro.customer SELECT number, concat('c', toString(number)) FROM numbers(10000);
INSERT INTO repro.orders   SELECT number, number % 10000, number FROM numbers(100000);
INSERT INTO repro.lineitem SELECT intDiv(number, 4), (number * 7) % 100 FROM numbers(400000);

PostgreSQL:

CREATE EXTENSION pg_clickhouse;
CREATE SERVER ch FOREIGN DATA WRAPPER clickhouse_fdw
  OPTIONS (driver 'binary', host 'clickhouse', port '9000', dbname 'repro');
CREATE USER MAPPING FOR CURRENT_USER SERVER ch;
IMPORT FOREIGN SCHEMA repro FROM SERVER ch INTO public;

EXPLAIN (VERBOSE, COSTS OFF)
SELECT c_name, o_orderkey, o_totalprice, sum(l_quantity)
FROM customer
JOIN orders   ON c_custkey = o_custkey
JOIN lineitem ON o_orderkey = l_orderkey
WHERE o_orderkey IN (SELECT l_orderkey FROM lineitem
                     GROUP BY l_orderkey HAVING sum(l_quantity) > 300)
GROUP BY c_name, o_orderkey, o_totalprice
ORDER BY o_totalprice DESC
LIMIT 10;

Plan (Output lines trimmed):

 Limit
   ->  Sort
         ->  HashAggregate
               ->  Hash Join
                     Inner Unique: true
                     Hash Cond: (orders.o_orderkey = lineitem_1.l_orderkey)
                     ->  Foreign Scan
                           Relations: ((customer) INNER JOIN (orders)) INNER JOIN (lineitem)
                           Remote SQL: SELECT r1.c_name, r2.o_orderkey, r2.o_totalprice, r4.l_quantity, r4.l_orderkey
                                       FROM repro.customer r1 ALL INNER JOIN repro.orders r2 ON (((r1.c_custkey = r2.o_custkey)))
                                       ALL INNER JOIN repro.lineitem r4 ON (((r2.o_orderkey = r4.l_orderkey)))
                     ->  Hash
                           ->  Foreign Scan
                                 Relations: Aggregate on (lineitem)
                                 Remote SQL: SELECT l_orderkey FROM repro.lineitem GROUP BY l_orderkey HAVING ((sum(l_quantity) > 300))

EXPLAIN ANALYZE shows the first Foreign Scan returning 400,000 rows (the
entire unfiltered join) to Postgres. Only 48,000 of them survive the join, and
the query returns 10. Everything below the Limit could run as one ClickHouse
query.

Writing the filter as an explicit join to the aggregated derived table
(JOIN (SELECT l_orderkey … GROUP BY … HAVING …) big ON big.l_orderkey = o_orderkey)
gives the same plan.

Likely cause

The planner pulls the uncorrelated IN sublink up into a semi-join. Because
the grouped subquery is unique on l_orderkey, it then turns that into an
inner join against a subquery rel. No SubPlan is left, so the v0.10.0 SubPlan
pushdown does not apply. The subquery rel is fully pushed down as its own
ForeignPath, but it is not a foreign rel, so no foreign join path is built
for the outer join.

Related

#79 covers joins against an aggregated subquery where the other side is a
local table, so the query cannot run remotely as a whole. Here every
relation is on the same foreign server, so the whole query could be one
remote query.

Workaround

A ClickHouse view that contains the IN, imported as a foreign table, makes
the whole query push down as one.

Happy to test a fix against the repro above and TPC-H Q18.

Dominant language
C
Stars
284
Forks
21
Avg merge
1d 3h
Merged PRs (30d)
22

Getting set up

  • Ships a Dockerfile or Docker Compose file
  • No pull request template
  • No contributing guide

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

More from ClickHouse/pg_clickhouse

All issues in ClickHouse/pg_clickhouse

Similar issues

More C issues

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.