From 824dbea3e41efa3b35094163c834988dea7eb139 Mon Sep 17 00:00:00 2001 From: Alexander Korotkov Date: Tue, 5 Dec 2023 22:53:12 +0200 Subject: Add support for deparsing semi-joins to contrib/postgres_fdw SEMI-JOIN is deparsed as the EXISTS subquery. It references outer and inner relations, so it should be evaluated as the condition in the upper-level WHERE clause. The signatures of deparseFromExprForRel() and deparseRangeTblRef() are revised so that they can add conditions to the upper level. PgFdwRelationInfo now has a hidden_subquery_rels field, referencing the relids used in the inner parts of semi-join. They can't be referred to from upper relations and should be used internally for equivalence member searches. The planner can create semi-join, which refers to inner rel vars in its target list. However, we deparse semi-join as an exists() subquery. So we skip the case when the target list references to inner rel of semi-join. Author: Alexander Pyhalov Reviewed-by: Ashutosh Bapat, Ian Lawrence Barwick, Yuuki Fujii, Tomas Vondra Discussion: https://postgr.es/m/c9e2a757cf3ac2333714eaf83a9cc184@postgrespro.ru --- contrib/postgres_fdw/sql/postgres_fdw.sql | 126 +++++++++++++++++++++++++++++- 1 file changed, 125 insertions(+), 1 deletion(-) (limited to 'contrib/postgres_fdw/sql/postgres_fdw.sql') diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql index 075da4ff867..cb405407028 100644 --- a/contrib/postgres_fdw/sql/postgres_fdw.sql +++ b/contrib/postgres_fdw/sql/postgres_fdw.sql @@ -600,7 +600,7 @@ WITH t (c1_1, c1_3, c2_1) AS MATERIALIZED (SELECT t1.c1, t1.c3, t2.c1 FROM ft1 t -- ctid with whole-row reference EXPLAIN (VERBOSE, COSTS OFF) SELECT t1.ctid, t1, t2, t1.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10; --- SEMI JOIN, not pushed down +-- SEMI JOIN EXPLAIN (VERBOSE, COSTS OFF) SELECT t1.c1 FROM ft1 t1 WHERE EXISTS (SELECT 1 FROM ft2 t2 WHERE t1.c1 = t2.c1) ORDER BY t1.c1 OFFSET 100 LIMIT 10; SELECT t1.c1 FROM ft1 t1 WHERE EXISTS (SELECT 1 FROM ft2 t2 WHERE t1.c1 = t2.c1) ORDER BY t1.c1 OFFSET 100 LIMIT 10; @@ -1305,6 +1305,130 @@ explain (verbose, costs off) select * from ft3 where f2 = 'foo' COLLATE "C"; explain (verbose, costs off) select * from ft3 f, loct3 l where f.f3 = l.f3 COLLATE "POSIX" and l.f1 = 'foo'; +-- =================================================================== +-- test SEMI-JOIN pushdown +-- =================================================================== +EXPLAIN (verbose, costs off) +SELECT ft2.*, ft4.* FROM ft2 INNER JOIN ft4 ON ft2.c2 = ft4.c1 + WHERE ft2.c1 > 900 + AND EXISTS (SELECT 1 FROM ft5 WHERE ft4.c1 = ft5.c1) + ORDER BY ft2.c1; +SELECT ft2.*, ft4.* FROM ft2 INNER JOIN ft4 ON ft2.c2 = ft4.c1 + WHERE ft2.c1 > 900 + AND EXISTS (SELECT 1 FROM ft5 WHERE ft4.c1 = ft5.c1) + ORDER BY ft2.c1; + +-- The same query, different join order +EXPLAIN (verbose, costs off) +SELECT ft2.*, ft4.* FROM ft2 INNER JOIN + (SELECT * FROM ft4 WHERE + EXISTS (SELECT 1 FROM ft5 WHERE ft4.c1 = ft5.c1)) ft4 + ON ft2.c2 = ft4.c1 + WHERE ft2.c1 > 900 + ORDER BY ft2.c1; +SELECT ft2.*, ft4.* FROM ft2 INNER JOIN + (SELECT * FROM ft4 WHERE + EXISTS (SELECT 1 FROM ft5 WHERE ft4.c1 = ft5.c1)) ft4 + ON ft2.c2 = ft4.c1 + WHERE ft2.c1 > 900 + ORDER BY ft2.c1; + +-- Left join +EXPLAIN (verbose, costs off) +SELECT ft2.*, ft4.* FROM ft2 LEFT JOIN + (SELECT * FROM ft4 WHERE + EXISTS (SELECT 1 FROM ft5 WHERE ft4.c1 = ft5.c1)) ft4 + ON ft2.c2 = ft4.c1 + WHERE ft2.c1 > 900 + ORDER BY ft2.c1 LIMIT 10; +SELECT ft2.*, ft4.* FROM ft2 LEFT JOIN + (SELECT * FROM ft4 WHERE + EXISTS (SELECT 1 FROM ft5 WHERE ft4.c1 = ft5.c1)) ft4 + ON ft2.c2 = ft4.c1 + WHERE ft2.c1 > 900 + ORDER BY ft2.c1 LIMIT 10; + +-- Several semi-joins per upper level join +EXPLAIN (verbose, costs off) +SELECT ft2.*, ft4.* FROM ft2 INNER JOIN + (SELECT * FROM ft4 WHERE + EXISTS (SELECT 1 FROM ft5 WHERE ft4.c1 = ft5.c1)) ft4 + ON ft2.c2 = ft4.c1 + INNER JOIN (SELECT * FROM ft5 WHERE + EXISTS (SELECT 1 FROM ft4 WHERE ft4.c1 = ft5.c1)) ft5 + ON ft2.c2 <= ft5.c1 + WHERE ft2.c1 > 900 + ORDER BY ft2.c1 LIMIT 10; +SELECT ft2.*, ft4.* FROM ft2 INNER JOIN + (SELECT * FROM ft4 WHERE + EXISTS (SELECT 1 FROM ft5 WHERE ft4.c1 = ft5.c1)) ft4 + ON ft2.c2 = ft4.c1 + INNER JOIN (SELECT * FROM ft5 WHERE + EXISTS (SELECT 1 FROM ft4 WHERE ft4.c1 = ft5.c1)) ft5 + ON ft2.c2 <= ft5.c1 + WHERE ft2.c1 > 900 + ORDER BY ft2.c1 LIMIT 10; + +-- Semi-join below Semi-join +EXPLAIN (verbose, costs off) +SELECT ft2.* FROM ft2 WHERE + c1 = ANY ( + SELECT c1 FROM ft2 WHERE + EXISTS (SELECT 1 FROM ft4 WHERE ft4.c2 = ft2.c2)) + AND ft2.c1 > 900 + ORDER BY ft2.c1 LIMIT 10; +SELECT ft2.* FROM ft2 WHERE + c1 = ANY ( + SELECT c1 FROM ft2 WHERE + EXISTS (SELECT 1 FROM ft4 WHERE ft4.c2 = ft2.c2)) + AND ft2.c1 > 900 + ORDER BY ft2.c1 LIMIT 10; + +-- Upper level relations shouldn't refer EXISTS() subqueries +EXPLAIN (verbose, costs off) +SELECT * FROM ft2 ftupper WHERE + EXISTS ( + SELECT c1 FROM ft2 WHERE + EXISTS (SELECT 1 FROM ft4 WHERE ft4.c2 = ft2.c2) AND c1 = ftupper.c1 ) + AND ftupper.c1 > 900 + ORDER BY ftupper.c1 LIMIT 10; +SELECT * FROM ft2 ftupper WHERE + EXISTS ( + SELECT c1 FROM ft2 WHERE + EXISTS (SELECT 1 FROM ft4 WHERE ft4.c2 = ft2.c2) AND c1 = ftupper.c1 ) + AND ftupper.c1 > 900 + ORDER BY ftupper.c1 LIMIT 10; + +-- EXISTS should be propogated to the highest upper inner join +EXPLAIN (verbose, costs off) + SELECT ft2.*, ft4.* FROM ft2 INNER JOIN + (SELECT * FROM ft4 WHERE EXISTS ( + SELECT 1 FROM ft2 WHERE ft2.c2 = ft4.c2)) ft4 + ON ft2.c2 = ft4.c1 + INNER JOIN + (SELECT * FROM ft2 WHERE EXISTS ( + SELECT 1 FROM ft4 WHERE ft2.c2 = ft4.c2)) ft21 + ON ft2.c2 = ft21.c2 + WHERE ft2.c1 > 900 + ORDER BY ft2.c1 LIMIT 10; +SELECT ft2.*, ft4.* FROM ft2 INNER JOIN + (SELECT * FROM ft4 WHERE EXISTS ( + SELECT 1 FROM ft2 WHERE ft2.c2 = ft4.c2)) ft4 + ON ft2.c2 = ft4.c1 + INNER JOIN + (SELECT * FROM ft2 WHERE EXISTS ( + SELECT 1 FROM ft4 WHERE ft2.c2 = ft4.c2)) ft21 + ON ft2.c2 = ft21.c2 + WHERE ft2.c1 > 900 + ORDER BY ft2.c1 LIMIT 10; + +-- Can't push down semi-join with inner rel vars in targetlist +EXPLAIN (verbose, costs off) +SELECT ft1.c1 FROM ft1 JOIN ft2 on ft1.c1 = ft2.c1 WHERE + ft1.c1 IN ( + SELECT ft2.c1 FROM ft2 JOIN ft4 ON ft2.c1 = ft4.c1) + ORDER BY ft1.c1 LIMIT 5; + -- =================================================================== -- test writable foreign table stuff -- =================================================================== -- cgit v1.2.3