diff options
Diffstat (limited to 'contrib/postgres_fdw/sql/postgres_fdw.sql')
| -rw-r--r-- | contrib/postgres_fdw/sql/postgres_fdw.sql | 126 |
1 files changed, 125 insertions, 1 deletions
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; @@ -1306,6 +1306,130 @@ 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 -- =================================================================== EXPLAIN (verbose, costs off) |