Add semi-join pushdown to postgres_fdw
Alexander Pyhalov <a.pyhalov@postgrespro.ru>
From: Alexander Pyhalov <a.pyhalov@postgrespro.ru>
To: Pgsql Hackers <pgsql-hackers@postgresql.org>
Date: 2022-08-24T07:25:46Z
Lists: pgsql-hackers
Attachments
- 0001-postgres_fdw-add-support-for-deparsing-semi-joins.patch (text/x-diff) patch 0001
Hi.
It's possible to extend deparsing in postgres_fdw, so that we can push
down semi-joins, which doesn't refer to inner reltarget. This allows
us to push down joins in queries like
SELECT * FROM ft1 t1 WHERE t1.c1 < 10 AND t1.c3 IN (SELECT c3 FROM ft2
t2 WHERE date(c5) = '1970-01-17'::date);
EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE t1.c1 < 10 AND
t1.c3 IN (SELECT c3 FROM ft2 t2 WHERE date(c5) = '1970-01-17'::date);
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Foreign Scan
Output: t1.c1, t1.c2, t1.c3, t1.c4, t1.c5, t1.c6, t1.c7, t1.c8
Relations: (public.ft1 t1) SEMI JOIN (public.ft2 t2)
Remote SQL: SELECT r1."C 1", r1.c2, r1.c3, r1.c4, r1.c5, r1.c6,
r1.c7, r1.c8 FROM "S 1"."T 1" r1 WHERE ((r1."C 1" < 10)) AND (EXISTS
(SELECT NULL FROM "S 1"."T 1" r3 WHERE ((date(r3.c5) =
'1970-01-17'::date)) AND ((r1.c3 = r3.c3))))
Deparsing semi-joins leads to generating (text) conditions like 'EXISTS
(SELECT NULL FROM inner_rel WHERE join_conds) . Such conditions are
generated in deparseFromExprForRel() and distributed to nearest WHERE,
where they are added to the list of and clauses.
--
Best regards,
Alexander Pyhalov,
Postgres Professional
Commits
Same data as JSON:
GET /api/v1/messages/:b64id/commits
the thread's linked commits as JSON, with link sources.
API reference →
-
postgres_fdw: Avoid pulling up restrict infos from subqueries
- 729fe699e612 17.5 landed
- 023fb512755f 18.0 landed
-
Add support for deparsing semi-joins to contrib/postgres_fdw
- 824dbea3e41e 17.0 landed
-
Move PartitioPruneInfo out of plan nodes into PlannedStmt
- ec386948948c 16.0 cited