Postgres_fdw join pushdown - INNER - FULL OUTER join combination generating wrong result
Hi,
I am testing postgres_fdw join pushdown feature for PostgreSQL 9.6 DB, and
I observed below issue.
*Observation:* Inner join and full outer join combination on a table
generating wrong result.
SELECT * FROM lt;
c1
----
1
2
(2 rows)
SELECT * FROM ft;
c1
----
1
2
(2 rows)
\d+ ft
Foreign table "public.ft"
Column | Type | Modifiers | FDW Options | Storage | Stats target |
Description
--------+---------+-----------+-------------+---------+--------------+-------------
c1 | integer | | | plain | |
Server: link_server
FDW Options: (table_name 'lt')
--inner join and full outer join on local tables
SELECT t1.c1,t2.c1,t3.c1 FROM lt t1 INNER JOIN lt t2 ON (t1.c1 = t2.c1)
FULL JOIN lt t3 ON (t2.c1 = t3.c1);
c1 | c1 | c1
----+----+----
1 | 1 | 1
2 | 2 | 2
(2 rows)
--inner join and full outer join on corresponding foreign tables
SELECT t1.c1,t2.c1,t3.c1 FROM ft t1 INNER JOIN ft t2 ON (t1.c1 = t2.c1)
FULL JOIN ft t3 ON (t2.c1 = t3.c1);
c1 | c1 | c1
----+----+----
1 | 1 | 1
1 | 2 |
2 | 1 |
2 | 2 | 2
(4 rows)
Thanks & Regards,
Rajkumar Raghuwanshi
QMG, EnterpriseDB Corporation
Show quoted text
On 2016/03/28 18:17, Rajkumar Raghuwanshi wrote:
I am testing postgres_fdw join pushdown feature for PostgreSQL 9.6 DB,
and I observed below issue._Observation:_ Inner join and full outer join combination on a table
generating wrong result.SELECT * FROM lt;
c1
----
1
2
(2 rows)SELECT * FROM ft;
c1
----
1
2
(2 rows)\d+ ft
Foreign table "public.ft"
Column | Type | Modifiers | FDW Options | Storage | Stats target |
Description
--------+---------+-----------+-------------+---------+--------------+-------------
c1 | integer | | | plain | |
Server: link_server
FDW Options: (table_name 'lt')--inner join and full outer join on local tables
SELECT t1.c1,t2.c1,t3.c1 FROM lt t1 INNER JOIN lt t2 ON (t1.c1 = t2.c1)
FULL JOIN lt t3 ON (t2.c1 = t3.c1);
c1 | c1 | c1
----+----+----
1 | 1 | 1
2 | 2 | 2
(2 rows)--inner join and full outer join on corresponding foreign tables
SELECT t1.c1,t2.c1,t3.c1 FROM ft t1 INNER JOIN ft t2 ON (t1.c1 = t2.c1)
FULL JOIN ft t3 ON (t2.c1 = t3.c1);
c1 | c1 | c1
----+----+----
1 | 1 | 1
1 | 2 |
2 | 1 |
2 | 2 | 2
(4 rows)
I think the reason for that is in foreign_join_ok. This in that function:
switch (jointype)
{
case JOIN_INNER:
fpinfo->remote_conds = list_concat(fpinfo->remote_conds,
list_copy(fpinfo_i->remote_conds));
fpinfo->remote_conds = list_concat(fpinfo->remote_conds,
list_copy(fpinfo_o->remote_conds));
break;
case JOIN_LEFT:
fpinfo->joinclauses = list_concat(fpinfo->joinclauses,
list_copy(fpinfo_i->remote_conds));
fpinfo->remote_conds = list_concat(fpinfo->remote_conds,
list_copy(fpinfo_o->remote_conds));
break;
case JOIN_RIGHT:
fpinfo->joinclauses = list_concat(fpinfo->joinclauses,
list_copy(fpinfo_o->remote_conds));
fpinfo->remote_conds = list_concat(fpinfo->remote_conds,
list_copy(fpinfo_i->remote_conds));
break;
case JOIN_FULL:
fpinfo->joinclauses = list_concat(fpinfo->joinclauses,
list_copy(fpinfo_i->remote_conds));
fpinfo->joinclauses = list_concat(fpinfo->joinclauses,
list_copy(fpinfo_o->remote_conds));
break;
default:
/* Should not happen, we have just check this above */
elog(ERROR, "unsupported join type %d", jointype);
}
wrongly pulls up remote_conds from joining relations in the FULL JOIN
case. I think we should not pull up such conditions in the FULL JOIN case.
Best regards,
Etsuro Fujita
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Observation:_ Inner join and full outer join combination on a table
generating wrong result.
SELECT * FROM lt;
c1
----
1
2
(2 rows)SELECT * FROM ft;
c1
----
1
2
(2 rows)\d+ ft
Foreign table "public.ft"
Column | Type | Modifiers | FDW Options | Storage | Stats target |
Description--------+---------+-----------+-------------+---------+--------------+-------------
c1 | integer | | | plain | |
Server: link_server
FDW Options: (table_name 'lt')--inner join and full outer join on local tables
SELECT t1.c1,t2.c1,t3.c1 FROM lt t1 INNER JOIN lt t2 ON (t1.c1 = t2.c1)
FULL JOIN lt t3 ON (t2.c1 = t3.c1);
c1 | c1 | c1
----+----+----
1 | 1 | 1
2 | 2 | 2
(2 rows)--inner join and full outer join on corresponding foreign tables
SELECT t1.c1,t2.c1,t3.c1 FROM ft t1 INNER JOIN ft t2 ON (t1.c1 = t2.c1)
FULL JOIN ft t3 ON (t2.c1 = t3.c1);
c1 | c1 | c1
----+----+----
1 | 1 | 1
1 | 2 |
2 | 1 |
2 | 2 | 2
(4 rows)
Thanks Rajkumar for the detailed report.
I think the reason for that is in foreign_join_ok. This in that function:
wrongly pulls up remote_conds from joining relations in the FULL JOIN
case. I think we should not pull up such conditions in the FULL JOIN case.
Right. For a full outer join, since each joining relation acts as outer for
the other, we can not pull up the quals to either join clauses or other
clauses. So, in such a case, we will need to encapsulate the joining
relation with conditions into a subquery. Unfortunately, the current
deparse logic does not handle this encapsulation. Adding that functionality
so close to the feature freeze might be risky given the amount of code
changes required.
PFA patch with a quick fix. A full outer join with either of the joining
relations having WHERE conditions (or other clauses) is not pushed down. In
the particular case that was reported, the bug triggered because of the way
conditions are handled for an inner join. For an inner join, all the
conditions in ON as well as WHERE clause are treated like they are part of
WHERE clause. This allows pushing down a join even if there are unpushable
join clauses. But the pushable conditions can be put back into the ON
clause. This avoids using subqueries while deparsing.
--
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company
Attachments:
pg_fj_cond.patchapplication/x-patch; name=pg_fj_cond.patchDownload
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index 50f1261..5c4ebb6 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -441,31 +441,31 @@ SELECT t1.c1, t2."C 1" FROM ft2 t1 LEFT JOIN "S 1"."T 1" t2 ON (t1.c1 = t2."C 1"
107 | 107
108 | 108
109 | 109
110 | 110
(10 rows)
-- A join between local table and foreign join. ORDER BY clause is added to the
-- foreign join so that the local table can be joined using merge join strategy.
EXPLAIN (COSTS false, VERBOSE)
SELECT t1."C 1" FROM "S 1"."T 1" t1 left join ft1 t2 join ft2 t3 on (t2.c1 = t3.c1) on (t3.c1 = t1."C 1") OFFSET 100 LIMIT 10;
- QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+---------------------------------------------------------------------------------------------------------------------------------------------------------
Limit
Output: t1."C 1"
-> Merge Right Join
Output: t1."C 1"
Merge Cond: (t3.c1 = t1."C 1")
-> Foreign Scan
Output: t3.c1
Relations: (public.ft1 t2) INNER JOIN (public.ft2 t3)
- Remote SQL: SELECT r3."C 1" FROM ("S 1"."T 1" r2 INNER JOIN "S 1"."T 1" r3 ON (TRUE)) WHERE ((r2."C 1" = r3."C 1")) ORDER BY r2."C 1" ASC NULLS LAST
+ Remote SQL: SELECT r3."C 1" FROM ("S 1"."T 1" r2 INNER JOIN "S 1"."T 1" r3 ON (((r2."C 1" = r3."C 1")))) ORDER BY r2."C 1" ASC NULLS LAST
-> Index Only Scan using t1_pkey on "S 1"."T 1" t1
Output: t1."C 1"
(11 rows)
SELECT t1."C 1" FROM "S 1"."T 1" t1 left join ft1 t2 join ft2 t3 on (t2.c1 = t3.c1) on (t3.c1 = t1."C 1") OFFSET 100 LIMIT 10;
C 1
-----
101
102
103
@@ -896,59 +896,59 @@ SELECT count(c3) FROM ft1 t1 WHERE t1.c1 === t1.c2;
-- ===================================================================
-- JOIN queries
-- ===================================================================
-- Analyze ft4 and ft5 so that we have better statistics. These tables do not
-- have use_remote_estimate set.
ANALYZE ft4;
ANALYZE ft5;
-- join two tables
EXPLAIN (COSTS false, VERBOSE)
SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10;
- QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit
Output: t1.c1, t2.c1, t1.c3
-> Foreign Scan
Output: t1.c1, t2.c1, t1.c3
Relations: (public.ft1 t1) INNER JOIN (public.ft2 t2)
- Remote SQL: SELECT r1."C 1", r1.c3, r2."C 1" FROM ("S 1"."T 1" r1 INNER JOIN "S 1"."T 1" r2 ON (TRUE)) WHERE ((r1."C 1" = r2."C 1")) ORDER BY r1.c3 ASC NULLS LAST, r1."C 1" ASC NULLS LAST
+ Remote SQL: SELECT r1."C 1", r1.c3, r2."C 1" FROM ("S 1"."T 1" r1 INNER JOIN "S 1"."T 1" r2 ON (((r1."C 1" = r2."C 1")))) ORDER BY r1.c3 ASC NULLS LAST, r1."C 1" ASC NULLS LAST
(6 rows)
SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10;
c1 | c1
-----+-----
101 | 101
102 | 102
103 | 103
104 | 104
105 | 105
106 | 106
107 | 107
108 | 108
109 | 109
110 | 110
(10 rows)
-- join three tables
EXPLAIN (COSTS false, VERBOSE)
SELECT t1.c1, t2.c2, t3.c3 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) JOIN ft4 t3 ON (t3.c1 = t1.c1) ORDER BY t1.c3, t1.c1 OFFSET 10 LIMIT 10;
- QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit
Output: t1.c1, t2.c2, t3.c3, t1.c3
-> Sort
Output: t1.c1, t2.c2, t3.c3, t1.c3
Sort Key: t1.c3, t1.c1
-> Foreign Scan
Output: t1.c1, t2.c2, t3.c3, t1.c3
Relations: ((public.ft1 t1) INNER JOIN (public.ft2 t2)) INNER JOIN (public.ft4 t3)
- Remote SQL: SELECT r1."C 1", r1.c3, r2.c2, r4.c3 FROM (("S 1"."T 1" r1 INNER JOIN "S 1"."T 1" r2 ON (TRUE)) INNER JOIN "S 1"."T 3" r4 ON (TRUE)) WHERE ((r1."C 1" = r4.c1)) AND ((r1."C 1" = r2."C 1"))
+ Remote SQL: SELECT r1."C 1", r1.c3, r2.c2, r4.c3 FROM (("S 1"."T 1" r1 INNER JOIN "S 1"."T 1" r2 ON (((r1."C 1" = r2."C 1")))) INNER JOIN "S 1"."T 3" r4 ON (((r1."C 1" = r4.c1))))
(9 rows)
SELECT t1.c1, t2.c2, t3.c3 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) JOIN ft4 t3 ON (t3.c1 = t1.c1) ORDER BY t1.c3, t1.c1 OFFSET 10 LIMIT 10;
c1 | c2 | c3
----+----+--------
22 | 2 | AAA022
24 | 4 | AAA024
26 | 6 | AAA026
28 | 8 | AAA028
30 | 0 | AAA030
@@ -1137,20 +1137,82 @@ SELECT t1.c1, t2.c1 FROM ft4 t1 FULL JOIN ft5 t2 ON (t1.c1 = t2.c1) ORDER BY t1.
96 | 96
98 |
100 |
| 3
| 9
| 15
| 21
| 27
(10 rows)
+-- full outer join with restrictions on the joining relations
+EXPLAIN (COSTS false, VERBOSE)
+SELECT t1.c1, t2.c1 FROM (SELECT c1 FROM ft4 WHERE c1 between 50 and 60) t1 FULL JOIN (SELECT c1 FROM ft5 WHERE c1 between 50 and 60) t2 ON (t1.c1 = t2.c1) ORDER BY t1.c1, t2.c1;
+ QUERY PLAN
+------------------------------------------------------------------------------------------------
+ Sort
+ Output: ft4.c1, ft5.c1
+ Sort Key: ft4.c1, ft5.c1
+ -> Hash Full Join
+ Output: ft4.c1, ft5.c1
+ Hash Cond: (ft4.c1 = ft5.c1)
+ -> Foreign Scan on public.ft4
+ Output: ft4.c1, ft4.c2, ft4.c3
+ Remote SQL: SELECT c1 FROM "S 1"."T 3" WHERE ((c1 >= 50)) AND ((c1 <= 60))
+ -> Hash
+ Output: ft5.c1
+ -> Foreign Scan on public.ft5
+ Output: ft5.c1
+ Remote SQL: SELECT c1 FROM "S 1"."T 4" WHERE ((c1 >= 50)) AND ((c1 <= 60))
+(14 rows)
+
+SELECT t1.c1, t2.c1 FROM (SELECT c1 FROM ft4 WHERE c1 between 50 and 60) t1 FULL JOIN (SELECT c1 FROM ft5 WHERE c1 between 50 and 60) t2 ON (t1.c1 = t2.c1) ORDER BY t1.c1, t2.c1;
+ c1 | c1
+----+----
+ 50 |
+ 52 |
+ 54 | 54
+ 56 |
+ 58 |
+ 60 | 60
+ | 51
+ | 57
+(8 rows)
+
+-- full outer join + inner join
+EXPLAIN (COSTS false, VERBOSE)
+SELECT t1.c1, t2.c1, t3.c1 FROM ft4 t1 INNER JOIN ft5 t2 ON (t1.c1 = t2.c1 + 1 and t1.c1 between 50 and 60) FULL JOIN ft4 t3 ON (t2.c1 = t3.c1) ORDER BY t1.c1, t2.c1, t3.c1 LIMIT 10;
+ QUERY PLAN
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Limit
+ Output: t1.c1, t2.c1, t3.c1
+ -> Foreign Scan
+ Output: t1.c1, t2.c1, t3.c1
+ Relations: ((public.ft4 t1) INNER JOIN (public.ft5 t2)) FULL JOIN (public.ft4 t3)
+ Remote SQL: SELECT r1.c1, r2.c1, r4.c1 FROM (("S 1"."T 3" r1 INNER JOIN "S 1"."T 4" r2 ON (((r1.c1 = (r2.c1 + 1))) AND ((r1.c1 >= 50)) AND ((r1.c1 <= 60)))) FULL JOIN "S 1"."T 3" r4 ON (((r2.c1 = r4.c1)))) ORDER BY r1.c1 ASC NULLS LAST, r2.c1 ASC NULLS LAST, r4.c1 ASC NULLS LAST
+(6 rows)
+
+SELECT t1.c1, t2.c1, t3.c1 FROM ft4 t1 INNER JOIN ft5 t2 ON (t1.c1 = t2.c1 + 1 and t1.c1 between 50 and 60) FULL JOIN ft4 t3 ON (t2.c1 = t3.c1) ORDER BY t1.c1, t2.c1, t3.c1 LIMIT 10;
+ c1 | c1 | c1
+----+----+----
+ 52 | 51 |
+ 58 | 57 |
+ | | 2
+ | | 4
+ | | 6
+ | | 8
+ | | 10
+ | | 12
+ | | 14
+ | | 16
+(10 rows)
+
-- full outer join three tables
EXPLAIN (COSTS false, VERBOSE)
SELECT t1.c1, t2.c2, t3.c3 FROM ft2 t1 FULL JOIN ft2 t2 ON (t1.c1 = t2.c1) FULL JOIN ft4 t3 ON (t2.c1 = t3.c1) OFFSET 10 LIMIT 10;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit
Output: t1.c1, t2.c2, t3.c3
-> Foreign Scan
Output: t1.c1, t2.c2, t3.c3
Relations: ((public.ft2 t1) FULL JOIN (public.ft2 t2)) FULL JOIN (public.ft4 t3)
@@ -1308,28 +1370,28 @@ SELECT t1.c1, t2.c2, t3.c3 FROM ft2 t1 RIGHT JOIN ft2 t2 ON (t1.c1 = t2.c1) LEFT
16 | 6 | AAA016
17 | 7 |
18 | 8 | AAA018
19 | 9 |
20 | 0 | AAA020
(10 rows)
-- left outer join + right outer join
EXPLAIN (COSTS false, VERBOSE)
SELECT t1.c1, t2.c2, t3.c3 FROM ft2 t1 LEFT JOIN ft2 t2 ON (t1.c1 = t2.c1) RIGHT JOIN ft4 t3 ON (t2.c1 = t3.c1) OFFSET 10 LIMIT 10;
- QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit
Output: t1.c1, t2.c2, t3.c3
-> Foreign Scan
Output: t1.c1, t2.c2, t3.c3
Relations: (public.ft4 t3) LEFT JOIN ((public.ft2 t1) INNER JOIN (public.ft2 t2))
- Remote SQL: SELECT r4.c3, r1."C 1", r2.c2 FROM ("S 1"."T 3" r4 LEFT JOIN ("S 1"."T 1" r1 INNER JOIN "S 1"."T 1" r2 ON (TRUE)) ON (((r2."C 1" = r4.c1)) AND ((r1."C 1" = r2."C 1"))))
+ Remote SQL: SELECT r4.c3, r1."C 1", r2.c2 FROM ("S 1"."T 3" r4 LEFT JOIN ("S 1"."T 1" r1 INNER JOIN "S 1"."T 1" r2 ON (((r1."C 1" = r2."C 1")))) ON (((r2."C 1" = r4.c1))))
(6 rows)
SELECT t1.c1, t2.c2, t3.c3 FROM ft2 t1 LEFT JOIN ft2 t2 ON (t1.c1 = t2.c1) RIGHT JOIN ft4 t3 ON (t2.c1 = t3.c1) OFFSET 10 LIMIT 10;
c1 | c2 | c3
----+----+--------
22 | 2 | AAA022
24 | 4 | AAA024
26 | 6 | AAA026
28 | 8 | AAA028
30 | 0 | AAA030
@@ -1368,30 +1430,30 @@ SELECT t1.c1, t2.c1 FROM ft4 t1 FULL JOIN ft5 t2 ON (t1.c1 = t2.c1) WHERE (t1.c1
| 3
| 9
| 15
| 21
(10 rows)
-- join two tables with FOR UPDATE clause
-- tests whole-row reference for row marks
EXPLAIN (COSTS false, VERBOSE)
SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10 FOR UPDATE OF t1;
- QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit
Output: t1.c1, t2.c1, t1.c3, t1.*, t2.*
-> LockRows
Output: t1.c1, t2.c1, t1.c3, t1.*, t2.*
-> Foreign Scan
Output: t1.c1, t2.c1, t1.c3, t1.*, t2.*
Relations: (public.ft1 t1) INNER JOIN (public.ft2 t2)
- Remote SQL: SELECT r1."C 1", r1.c3, ROW(r1."C 1", r1.c2, r1.c3, r1.c4, r1.c5, r1.c6, r1.c7, r1.c8), r2."C 1", ROW(r2."C 1", r2.c2, r2.c3, r2.c4, r2.c5, r2.c6, r2.c7, r2.c8) FROM ("S 1"."T 1" r1 INNER JOIN "S 1"."T 1" r2 ON (TRUE)) WHERE ((r1."C 1" = r2."C 1")) ORDER BY r1.c3 ASC NULLS LAST, r1."C 1" ASC NULLS LAST FOR UPDATE OF r1
+ Remote SQL: SELECT r1."C 1", r1.c3, ROW(r1."C 1", r1.c2, r1.c3, r1.c4, r1.c5, r1.c6, r1.c7, r1.c8), r2."C 1", ROW(r2."C 1", r2.c2, r2.c3, r2.c4, r2.c5, r2.c6, r2.c7, r2.c8) FROM ("S 1"."T 1" r1 INNER JOIN "S 1"."T 1" r2 ON (((r1."C 1" = r2."C 1")))) ORDER BY r1.c3 ASC NULLS LAST, r1."C 1" ASC NULLS LAST FOR UPDATE OF r1
-> Merge Join
Output: t1.c1, t1.c3, t1.*, t2.c1, t2.*
Merge Cond: (t1.c1 = t2.c1)
-> Sort
Output: t1.c1, t1.c3, t1.*
Sort Key: t1.c1
-> Foreign Scan on public.ft1 t1
Output: t1.c1, t1.c3, t1.*
Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" FOR UPDATE
-> Sort
@@ -1412,30 +1474,30 @@ SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c3, t
105 | 105
106 | 106
107 | 107
108 | 108
109 | 109
110 | 110
(10 rows)
EXPLAIN (COSTS false, VERBOSE)
SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10 FOR UPDATE;
- QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit
Output: t1.c1, t2.c1, t1.c3, t1.*, t2.*
-> LockRows
Output: t1.c1, t2.c1, t1.c3, t1.*, t2.*
-> Foreign Scan
Output: t1.c1, t2.c1, t1.c3, t1.*, t2.*
Relations: (public.ft1 t1) INNER JOIN (public.ft2 t2)
- Remote SQL: SELECT r1."C 1", r1.c3, ROW(r1."C 1", r1.c2, r1.c3, r1.c4, r1.c5, r1.c6, r1.c7, r1.c8), r2."C 1", ROW(r2."C 1", r2.c2, r2.c3, r2.c4, r2.c5, r2.c6, r2.c7, r2.c8) FROM ("S 1"."T 1" r1 INNER JOIN "S 1"."T 1" r2 ON (TRUE)) WHERE ((r1."C 1" = r2."C 1")) ORDER BY r1.c3 ASC NULLS LAST, r1."C 1" ASC NULLS LAST FOR UPDATE OF r1 FOR UPDATE OF r2
+ Remote SQL: SELECT r1."C 1", r1.c3, ROW(r1."C 1", r1.c2, r1.c3, r1.c4, r1.c5, r1.c6, r1.c7, r1.c8), r2."C 1", ROW(r2."C 1", r2.c2, r2.c3, r2.c4, r2.c5, r2.c6, r2.c7, r2.c8) FROM ("S 1"."T 1" r1 INNER JOIN "S 1"."T 1" r2 ON (((r1."C 1" = r2."C 1")))) ORDER BY r1.c3 ASC NULLS LAST, r1."C 1" ASC NULLS LAST FOR UPDATE OF r1 FOR UPDATE OF r2
-> Merge Join
Output: t1.c1, t1.c3, t1.*, t2.c1, t2.*
Merge Cond: (t1.c1 = t2.c1)
-> Sort
Output: t1.c1, t1.c3, t1.*
Sort Key: t1.c1
-> Foreign Scan on public.ft1 t1
Output: t1.c1, t1.c3, t1.*
Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" FOR UPDATE
-> Sort
@@ -1457,30 +1519,30 @@ SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c3, t
106 | 106
107 | 107
108 | 108
109 | 109
110 | 110
(10 rows)
-- join two tables with FOR SHARE clause
EXPLAIN (COSTS false, VERBOSE)
SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10 FOR SHARE OF t1;
- QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit
Output: t1.c1, t2.c1, t1.c3, t1.*, t2.*
-> LockRows
Output: t1.c1, t2.c1, t1.c3, t1.*, t2.*
-> Foreign Scan
Output: t1.c1, t2.c1, t1.c3, t1.*, t2.*
Relations: (public.ft1 t1) INNER JOIN (public.ft2 t2)
- Remote SQL: SELECT r1."C 1", r1.c3, ROW(r1."C 1", r1.c2, r1.c3, r1.c4, r1.c5, r1.c6, r1.c7, r1.c8), r2."C 1", ROW(r2."C 1", r2.c2, r2.c3, r2.c4, r2.c5, r2.c6, r2.c7, r2.c8) FROM ("S 1"."T 1" r1 INNER JOIN "S 1"."T 1" r2 ON (TRUE)) WHERE ((r1."C 1" = r2."C 1")) ORDER BY r1.c3 ASC NULLS LAST, r1."C 1" ASC NULLS LAST FOR SHARE OF r1
+ Remote SQL: SELECT r1."C 1", r1.c3, ROW(r1."C 1", r1.c2, r1.c3, r1.c4, r1.c5, r1.c6, r1.c7, r1.c8), r2."C 1", ROW(r2."C 1", r2.c2, r2.c3, r2.c4, r2.c5, r2.c6, r2.c7, r2.c8) FROM ("S 1"."T 1" r1 INNER JOIN "S 1"."T 1" r2 ON (((r1."C 1" = r2."C 1")))) ORDER BY r1.c3 ASC NULLS LAST, r1."C 1" ASC NULLS LAST FOR SHARE OF r1
-> Merge Join
Output: t1.c1, t1.c3, t1.*, t2.c1, t2.*
Merge Cond: (t1.c1 = t2.c1)
-> Sort
Output: t1.c1, t1.c3, t1.*
Sort Key: t1.c1
-> Foreign Scan on public.ft1 t1
Output: t1.c1, t1.c3, t1.*
Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" FOR SHARE
-> Sort
@@ -1501,30 +1563,30 @@ SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c3, t
105 | 105
106 | 106
107 | 107
108 | 108
109 | 109
110 | 110
(10 rows)
EXPLAIN (COSTS false, VERBOSE)
SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10 FOR SHARE;
- QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit
Output: t1.c1, t2.c1, t1.c3, t1.*, t2.*
-> LockRows
Output: t1.c1, t2.c1, t1.c3, t1.*, t2.*
-> Foreign Scan
Output: t1.c1, t2.c1, t1.c3, t1.*, t2.*
Relations: (public.ft1 t1) INNER JOIN (public.ft2 t2)
- Remote SQL: SELECT r1."C 1", r1.c3, ROW(r1."C 1", r1.c2, r1.c3, r1.c4, r1.c5, r1.c6, r1.c7, r1.c8), r2."C 1", ROW(r2."C 1", r2.c2, r2.c3, r2.c4, r2.c5, r2.c6, r2.c7, r2.c8) FROM ("S 1"."T 1" r1 INNER JOIN "S 1"."T 1" r2 ON (TRUE)) WHERE ((r1."C 1" = r2."C 1")) ORDER BY r1.c3 ASC NULLS LAST, r1."C 1" ASC NULLS LAST FOR SHARE OF r1 FOR SHARE OF r2
+ Remote SQL: SELECT r1."C 1", r1.c3, ROW(r1."C 1", r1.c2, r1.c3, r1.c4, r1.c5, r1.c6, r1.c7, r1.c8), r2."C 1", ROW(r2."C 1", r2.c2, r2.c3, r2.c4, r2.c5, r2.c6, r2.c7, r2.c8) FROM ("S 1"."T 1" r1 INNER JOIN "S 1"."T 1" r2 ON (((r1."C 1" = r2."C 1")))) ORDER BY r1.c3 ASC NULLS LAST, r1."C 1" ASC NULLS LAST FOR SHARE OF r1 FOR SHARE OF r2
-> Merge Join
Output: t1.c1, t1.c3, t1.*, t2.c1, t2.*
Merge Cond: (t1.c1 = t2.c1)
-> Sort
Output: t1.c1, t1.c3, t1.*
Sort Key: t1.c1
-> Foreign Scan on public.ft1 t1
Output: t1.c1, t1.c3, t1.*
Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" FOR SHARE
-> Sort
@@ -1546,29 +1608,29 @@ SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c3, t
106 | 106
107 | 107
108 | 108
109 | 109
110 | 110
(10 rows)
-- join in CTE
EXPLAIN (COSTS false, VERBOSE)
WITH t (c1_1, c1_3, c2_1) AS (SELECT t1.c1, t1.c3, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1)) SELECT c1_1, c2_1 FROM t ORDER BY c1_3, c1_1 OFFSET 100 LIMIT 10;
- QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------------------------------------------------------------------------------------------
Limit
Output: t.c1_1, t.c2_1, t.c1_3
CTE t
-> Foreign Scan
Output: t1.c1, t1.c3, t2.c1
Relations: (public.ft1 t1) INNER JOIN (public.ft2 t2)
- Remote SQL: SELECT r1."C 1", r1.c3, r2."C 1" FROM ("S 1"."T 1" r1 INNER JOIN "S 1"."T 1" r2 ON (TRUE)) WHERE ((r1."C 1" = r2."C 1"))
+ Remote SQL: SELECT r1."C 1", r1.c3, r2."C 1" FROM ("S 1"."T 1" r1 INNER JOIN "S 1"."T 1" r2 ON (((r1."C 1" = r2."C 1"))))
-> Sort
Output: t.c1_1, t.c2_1, t.c1_3
Sort Key: t.c1_3, t.c1_1
-> CTE Scan on t
Output: t.c1_1, t.c2_1, t.c1_3
(12 rows)
WITH t (c1_1, c1_3, c2_1) AS (SELECT t1.c1, t1.c3, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1)) SELECT c1_1, c2_1 FROM t ORDER BY c1_3, c1_1 OFFSET 100 LIMIT 10;
c1_1 | c2_1
------+------
@@ -1580,28 +1642,28 @@ WITH t (c1_1, c1_3, c2_1) AS (SELECT t1.c1, t1.c3, t2.c1 FROM ft1 t1 JOIN ft2 t2
106 | 106
107 | 107
108 | 108
109 | 109
110 | 110
(10 rows)
-- ctid with whole-row reference
EXPLAIN (COSTS false, VERBOSE)
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;
- QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit
Output: t1.ctid, t1.*, t2.*, t1.c1, t1.c3
-> Foreign Scan
Output: t1.ctid, t1.*, t2.*, t1.c1, t1.c3
Relations: (public.ft1 t1) INNER JOIN (public.ft2 t2)
- Remote SQL: SELECT r1.ctid, ROW(r1."C 1", r1.c2, r1.c3, r1.c4, r1.c5, r1.c6, r1.c7, r1.c8), r1."C 1", r1.c3, ROW(r2."C 1", r2.c2, r2.c3, r2.c4, r2.c5, r2.c6, r2.c7, r2.c8) FROM ("S 1"."T 1" r1 INNER JOIN "S 1"."T 1" r2 ON (TRUE)) WHERE ((r1."C 1" = r2."C 1")) ORDER BY r1.c3 ASC NULLS LAST, r1."C 1" ASC NULLS LAST
+ Remote SQL: SELECT r1.ctid, ROW(r1."C 1", r1.c2, r1.c3, r1.c4, r1.c5, r1.c6, r1.c7, r1.c8), r1."C 1", r1.c3, ROW(r2."C 1", r2.c2, r2.c3, r2.c4, r2.c5, r2.c6, r2.c7, r2.c8) FROM ("S 1"."T 1" r1 INNER JOIN "S 1"."T 1" r2 ON (((r1."C 1" = r2."C 1")))) ORDER BY r1.c3 ASC NULLS LAST, r1."C 1" ASC NULLS LAST
(6 rows)
-- SEMI JOIN, not pushed down
EXPLAIN (COSTS false, VERBOSE)
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;
QUERY PLAN
---------------------------------------------------------------------------------------------
Limit
Output: t1.c1
-> Merge Semi Join
@@ -1810,109 +1872,109 @@ SELECT t1.c1, t2.c1 FROM ft1 t1 LEFT JOIN ft2 t2 ON (t1.c1 = t2.c1) WHERE t1.c8
109 | 109
110 | 110
(10 rows)
-- join where unsafe to pushdown condition in WHERE clause has a column not
-- in the SELECT clause. In this test unsafe clause needs to have column
-- references from both joining sides so that the clause is not pushed down
-- into one of the joining sides.
EXPLAIN (COSTS false, VERBOSE)
SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) WHERE t1.c8 = t2.c8 ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10;
- QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------------------------------------------------------------------------------------------------------------
Limit
Output: t1.c1, t2.c1, t1.c3
-> Sort
Output: t1.c1, t2.c1, t1.c3
Sort Key: t1.c3, t1.c1
-> Foreign Scan
Output: t1.c1, t2.c1, t1.c3
Filter: (t1.c8 = t2.c8)
Relations: (public.ft1 t1) INNER JOIN (public.ft2 t2)
- Remote SQL: SELECT r1."C 1", r1.c3, r2."C 1", r1.c8, r2.c8 FROM ("S 1"."T 1" r1 INNER JOIN "S 1"."T 1" r2 ON (TRUE)) WHERE ((r1."C 1" = r2."C 1"))
+ Remote SQL: SELECT r1."C 1", r1.c3, r2."C 1", r1.c8, r2.c8 FROM ("S 1"."T 1" r1 INNER JOIN "S 1"."T 1" r2 ON (((r1."C 1" = r2."C 1"))))
(10 rows)
SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) WHERE t1.c8 = t2.c8 ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10;
c1 | c1
-----+-----
101 | 101
102 | 102
103 | 103
104 | 104
105 | 105
106 | 106
107 | 107
108 | 108
109 | 109
110 | 110
(10 rows)
-- Aggregate after UNION, for testing setrefs
EXPLAIN (COSTS false, VERBOSE)
SELECT t1c1, avg(t1c1 + t2c1) FROM (SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) UNION SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1)) AS t (t1c1, t2c1) GROUP BY t1c1 ORDER BY t1c1 OFFSET 100 LIMIT 10;
- QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------------------------------------------------------
Limit
Output: t1.c1, (avg((t1.c1 + t2.c1)))
-> Sort
Output: t1.c1, (avg((t1.c1 + t2.c1)))
Sort Key: t1.c1
-> HashAggregate
Output: t1.c1, avg((t1.c1 + t2.c1))
Group Key: t1.c1
-> HashAggregate
Output: t1.c1, t2.c1
Group Key: t1.c1, t2.c1
-> Append
-> Foreign Scan
Output: t1.c1, t2.c1
Relations: (public.ft1 t1) INNER JOIN (public.ft2 t2)
- Remote SQL: SELECT r1."C 1", r2."C 1" FROM ("S 1"."T 1" r1 INNER JOIN "S 1"."T 1" r2 ON (TRUE)) WHERE ((r1."C 1" = r2."C 1"))
+ Remote SQL: SELECT r1."C 1", r2."C 1" FROM ("S 1"."T 1" r1 INNER JOIN "S 1"."T 1" r2 ON (((r1."C 1" = r2."C 1"))))
-> Foreign Scan
Output: t1_1.c1, t2_1.c1
Relations: (public.ft1 t1) INNER JOIN (public.ft2 t2)
- Remote SQL: SELECT r1."C 1", r2."C 1" FROM ("S 1"."T 1" r1 INNER JOIN "S 1"."T 1" r2 ON (TRUE)) WHERE ((r1."C 1" = r2."C 1"))
+ Remote SQL: SELECT r1."C 1", r2."C 1" FROM ("S 1"."T 1" r1 INNER JOIN "S 1"."T 1" r2 ON (((r1."C 1" = r2."C 1"))))
(20 rows)
SELECT t1c1, avg(t1c1 + t2c1) FROM (SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) UNION SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1)) AS t (t1c1, t2c1) GROUP BY t1c1 ORDER BY t1c1 OFFSET 100 LIMIT 10;
t1c1 | avg
------+----------------------
101 | 202.0000000000000000
102 | 204.0000000000000000
103 | 206.0000000000000000
104 | 208.0000000000000000
105 | 210.0000000000000000
106 | 212.0000000000000000
107 | 214.0000000000000000
108 | 216.0000000000000000
109 | 218.0000000000000000
110 | 220.0000000000000000
(10 rows)
-- join with lateral reference
EXPLAIN (COSTS false, VERBOSE)
SELECT t1."C 1" FROM "S 1"."T 1" t1, LATERAL (SELECT DISTINCT t2.c1, t3.c1 FROM ft1 t2, ft2 t3 WHERE t2.c1 = t3.c1 AND t2.c2 = t1.c2) q ORDER BY t1."C 1" OFFSET 10 LIMIT 10;
- QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit
Output: t1."C 1"
-> Nested Loop
Output: t1."C 1"
-> Index Scan using t1_pkey on "S 1"."T 1" t1
Output: t1."C 1", t1.c2, t1.c3, t1.c4, t1.c5, t1.c6, t1.c7, t1.c8
-> HashAggregate
Output: t2.c1, t3.c1
Group Key: t2.c1, t3.c1
-> Foreign Scan
Output: t2.c1, t3.c1
Relations: (public.ft1 t2) INNER JOIN (public.ft2 t3)
- Remote SQL: SELECT r1."C 1", r2."C 1" FROM ("S 1"."T 1" r1 INNER JOIN "S 1"."T 1" r2 ON (TRUE)) WHERE ((r1."C 1" = r2."C 1")) AND ((r1.c2 = $1::integer))
+ Remote SQL: SELECT r1."C 1", r2."C 1" FROM ("S 1"."T 1" r1 INNER JOIN "S 1"."T 1" r2 ON (((r1."C 1" = r2."C 1")) AND ((r1.c2 = $1::integer))))
(13 rows)
SELECT t1."C 1" FROM "S 1"."T 1" t1, LATERAL (SELECT DISTINCT t2.c1, t3.c1 FROM ft1 t2, ft2 t3 WHERE t2.c1 = t3.c1 AND t2.c2 = t1.c2) q ORDER BY t1."C 1" OFFSET 10 LIMIT 10;
C 1
-----
1
1
1
1
1
@@ -1989,28 +2051,28 @@ CREATE VIEW v_ft5 AS SELECT * FROM ft5;
-- ft5 is view_owner and not the current user.
ALTER VIEW v_ft5 OWNER TO view_owner;
-- create a public user mapping for loopback server
-- drop user mapping for current_user.
DROP USER MAPPING FOR CURRENT_USER SERVER loopback;
CREATE USER MAPPING FOR PUBLIC SERVER loopback;
-- different effective user for permission check, but same user mapping for the
-- joining sides, join pushed down, no result expected.
PREPARE join_stmt AS SELECT t1.c1, t2.c1 FROM ft5 t1 JOIN v_ft5 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c1, t2.c1 OFFSET 100 LIMIT 10;
EXPLAIN (COSTS false, VERBOSE) EXECUTE join_stmt;
- QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------------------------------------------------
Limit
Output: t1.c1, ft5.c1
-> Foreign Scan
Output: t1.c1, ft5.c1
Relations: (public.ft5 t1) INNER JOIN (public.ft5)
- Remote SQL: SELECT r1.c1, r6.c1 FROM ("S 1"."T 4" r1 INNER JOIN "S 1"."T 4" r6 ON (TRUE)) WHERE ((r1.c1 = r6.c1)) ORDER BY r1.c1 ASC NULLS LAST
+ Remote SQL: SELECT r1.c1, r6.c1 FROM ("S 1"."T 4" r1 INNER JOIN "S 1"."T 4" r6 ON (((r1.c1 = r6.c1)))) ORDER BY r1.c1 ASC NULLS LAST
(6 rows)
EXECUTE join_stmt;
c1 | c1
----+----
(0 rows)
-- create user mapping for view_owner and execute the prepared statement
-- the join should not be pushed down since joining relations now use two
-- different user mappings
@@ -2074,26 +2136,26 @@ SELECT t1.c1, t2.c1 FROM (ft5 t1 JOIN v_ft5 t2 ON (t1.c1 = t2.c1)) left join (ft
-- recreate the dropped user mapping for further tests
CREATE USER MAPPING FOR CURRENT_USER SERVER loopback;
DROP USER MAPPING FOR PUBLIC SERVER loopback;
-- ===================================================================
-- parameterized queries
-- ===================================================================
-- simple join
PREPARE st1(int, int) AS SELECT t1.c3, t2.c3 FROM ft1 t1, ft2 t2 WHERE t1.c1 = $1 AND t2.c1 = $2;
EXPLAIN (VERBOSE, COSTS false) EXECUTE st1(1, 2);
- QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+------------------------------------------------------------------------------------------------------------------------------
Foreign Scan
Output: t1.c3, t2.c3
Relations: (public.ft1 t1) INNER JOIN (public.ft2 t2)
- Remote SQL: SELECT r1.c3, r2.c3 FROM ("S 1"."T 1" r1 INNER JOIN "S 1"."T 1" r2 ON (TRUE)) WHERE ((r2."C 1" = 2)) AND ((r1."C 1" = 1))
+ Remote SQL: SELECT r1.c3, r2.c3 FROM ("S 1"."T 1" r1 INNER JOIN "S 1"."T 1" r2 ON (((r2."C 1" = 2)) AND ((r1."C 1" = 1))))
(4 rows)
EXECUTE st1(1, 1);
c3 | c3
-------+-------
00001 | 00001
(1 row)
EXECUTE st1(101, 101);
c3 | c3
@@ -2685,28 +2747,28 @@ UPDATE ft2 SET c2 = c2 + 400, c3 = c3 || '_update7' WHERE c1 % 10 = 7 RETURNING
977 | 407 | 00977_update7 | Thu Mar 19 00:00:00 1970 PST | Thu Mar 19 00:00:00 1970 | 7 | 7 | foo
987 | 407 | 00987_update7 | Sun Mar 29 00:00:00 1970 PST | Sun Mar 29 00:00:00 1970 | 7 | 7 | foo
997 | 407 | 00997_update7 | Wed Apr 08 00:00:00 1970 PST | Wed Apr 08 00:00:00 1970 | 7 | 7 | foo
1007 | 507 | 0000700007_update7 | | | | ft2 |
1017 | 507 | 0001700017_update7 | | | | ft2 |
(102 rows)
EXPLAIN (verbose, costs off)
UPDATE ft2 SET c2 = ft2.c2 + 500, c3 = ft2.c3 || '_update9', c7 = DEFAULT
FROM ft1 WHERE ft1.c1 = ft2.c2 AND ft1.c1 % 10 = 9; -- can't be pushed down
- QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Update on public.ft2
Remote SQL: UPDATE "S 1"."T 1" SET c2 = $2, c3 = $3, c7 = $4 WHERE ctid = $1
-> Foreign Scan
Output: ft2.c1, (ft2.c2 + 500), NULL::integer, (ft2.c3 || '_update9'::text), ft2.c4, ft2.c5, ft2.c6, 'ft2 '::character(10), ft2.c8, ft2.ctid, ft1.*
Relations: (public.ft2) INNER JOIN (public.ft1)
- Remote SQL: SELECT r1."C 1", r1.c2, r1.c3, r1.c4, r1.c5, r1.c6, r1.c8, r1.ctid, ROW(r2."C 1", r2.c2, r2.c3, r2.c4, r2.c5, r2.c6, r2.c7, r2.c8) FROM ("S 1"."T 1" r1 INNER JOIN "S 1"."T 1" r2 ON (TRUE)) WHERE ((r1.c2 = r2."C 1")) AND (((r2."C 1" % 10) = 9)) FOR UPDATE OF r1
+ Remote SQL: SELECT r1."C 1", r1.c2, r1.c3, r1.c4, r1.c5, r1.c6, r1.c8, r1.ctid, ROW(r2."C 1", r2.c2, r2.c3, r2.c4, r2.c5, r2.c6, r2.c7, r2.c8) FROM ("S 1"."T 1" r1 INNER JOIN "S 1"."T 1" r2 ON (((r1.c2 = r2."C 1")) AND (((r2."C 1" % 10) = 9)))) FOR UPDATE OF r1
-> Hash Join
Output: ft2.c1, ft2.c2, ft2.c3, ft2.c4, ft2.c5, ft2.c6, ft2.c8, ft2.ctid, ft1.*
Hash Cond: (ft2.c2 = ft1.c1)
-> Foreign Scan on public.ft2
Output: ft2.c1, ft2.c2, ft2.c3, ft2.c4, ft2.c5, ft2.c6, ft2.c8, ft2.ctid
Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c8, ctid FROM "S 1"."T 1" FOR UPDATE
-> Hash
Output: ft1.*, ft1.c1
-> Foreign Scan on public.ft1
Output: ft1.*, ft1.c1
@@ -2828,28 +2890,28 @@ DELETE FROM ft2 WHERE c1 % 10 = 5 RETURNING c1, c4;
975 | Tue Mar 17 00:00:00 1970 PST
985 | Fri Mar 27 00:00:00 1970 PST
995 | Mon Apr 06 00:00:00 1970 PST
1005 |
1015 |
1105 |
(103 rows)
EXPLAIN (verbose, costs off)
DELETE FROM ft2 USING ft1 WHERE ft1.c1 = ft2.c2 AND ft1.c1 % 10 = 2; -- can't be pushed down
- QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Delete on public.ft2
Remote SQL: DELETE FROM "S 1"."T 1" WHERE ctid = $1
-> Foreign Scan
Output: ft2.ctid, ft1.*
Relations: (public.ft2) INNER JOIN (public.ft1)
- Remote SQL: SELECT r1.ctid, ROW(r2."C 1", r2.c2, r2.c3, r2.c4, r2.c5, r2.c6, r2.c7, r2.c8) FROM ("S 1"."T 1" r1 INNER JOIN "S 1"."T 1" r2 ON (TRUE)) WHERE ((r1.c2 = r2."C 1")) AND (((r2."C 1" % 10) = 2)) FOR UPDATE OF r1
+ Remote SQL: SELECT r1.ctid, ROW(r2."C 1", r2.c2, r2.c3, r2.c4, r2.c5, r2.c6, r2.c7, r2.c8) FROM ("S 1"."T 1" r1 INNER JOIN "S 1"."T 1" r2 ON (((r1.c2 = r2."C 1")) AND (((r2."C 1" % 10) = 2)))) FOR UPDATE OF r1
-> Hash Join
Output: ft2.ctid, ft1.*
Hash Cond: (ft2.c2 = ft1.c1)
-> Foreign Scan on public.ft2
Output: ft2.ctid, ft2.c2
Remote SQL: SELECT c2, ctid FROM "S 1"."T 1" FOR UPDATE
-> Hash
Output: ft1.*, ft1.c1
-> Foreign Scan on public.ft1
Output: ft1.*, ft1.c1
diff --git a/contrib/postgres_fdw/postgres_fdw.c b/contrib/postgres_fdw/postgres_fdw.c
index 4fbbde1..0e99421 100644
--- a/contrib/postgres_fdw/postgres_fdw.c
+++ b/contrib/postgres_fdw/postgres_fdw.c
@@ -3992,67 +3992,36 @@ foreign_join_ok(PlannerInfo *root, RelOptInfo *joinrel, JoinType jointype,
fpinfo->local_conds = lappend(fpinfo->local_conds, expr);
else
fpinfo->remote_conds = lappend(fpinfo->remote_conds, expr);
}
fpinfo->outerrel = outerrel;
fpinfo->innerrel = innerrel;
fpinfo->jointype = jointype;
/*
- * If user is willing to estimate cost for a scan of either of the joining
- * relations using EXPLAIN, he intends to estimate scans on that relation
- * more accurately. Then, it makes sense to estimate the cost the join
- * with that relation more accurately using EXPLAIN.
- */
- fpinfo->use_remote_estimate = fpinfo_o->use_remote_estimate ||
- fpinfo_i->use_remote_estimate;
-
- /*
- * Since both the joining relations come from the same server, the server
- * level options should have same value for both the relations. Pick from
- * any side.
- */
- fpinfo->fdw_startup_cost = fpinfo_o->fdw_startup_cost;
- fpinfo->fdw_tuple_cost = fpinfo_o->fdw_tuple_cost;
-
- /*
- * Set cached relation costs to some negative value, so that we can detect
- * when they are set to some sensible costs, during one (usually the
- * first) of the calls to estimate_path_cost_size().
- */
- fpinfo->rel_startup_cost = -1;
- fpinfo->rel_total_cost = -1;
-
- /* Mark that this join can be pushed down safely */
- fpinfo->pushdown_safe = true;
-
- /*
- * Set fetch size to maximum of the joining sides, since we are expecting
- * the rows returned by the join to be proportional to the relation sizes.
- */
- if (fpinfo_o->fetch_size > fpinfo_i->fetch_size)
- fpinfo->fetch_size = fpinfo_o->fetch_size;
- else
- fpinfo->fetch_size = fpinfo_i->fetch_size;
-
- /*
* Pull the other remote conditions from the joining relations into join
- * clauses or other remote clauses (remote_conds) of this relation. This
- * avoids building subqueries at every join step.
+ * clauses or other remote clauses (remote_conds) of this relation wherever
+ * possible. This avoids building subqueries at every join step, which is
+ * not currently supported by the deparser logic.
*
* For an inner join, clauses from both the relations are added to the
- * other remote clauses. For an OUTER join, the clauses from the outer
- * side are added to remote_conds since those can be evaluated after the
- * join is evaluated. The clauses from inner side are added to the
+ * other remote clauses. For LEFT and RIGHT OUTER join, the clauses from the
+ * outer side are added to remote_conds since those can be evaluated after
+ * the join is evaluated. The clauses from inner side are added to the
* joinclauses, since they need to evaluated while constructing the join.
*
+ * For a FULL OUTER JOIN, the other clauses from either relation can not be
+ * added to the joinclauses or remote_conds, since each relation acts as an
+ * outer relation for the other. Consider such full outer join as
+ * unshippable because of the reasons mentioned above in this comment.
+ *
* The joining sides can not have local conditions, thus no need to test
* shippability of the clauses being pulled up.
*/
switch (jointype)
{
case JOIN_INNER:
fpinfo->remote_conds = list_concat(fpinfo->remote_conds,
list_copy(fpinfo_i->remote_conds));
fpinfo->remote_conds = list_concat(fpinfo->remote_conds,
list_copy(fpinfo_o->remote_conds));
@@ -4066,32 +4035,79 @@ foreign_join_ok(PlannerInfo *root, RelOptInfo *joinrel, JoinType jointype,
break;
case JOIN_RIGHT:
fpinfo->joinclauses = list_concat(fpinfo->joinclauses,
list_copy(fpinfo_o->remote_conds));
fpinfo->remote_conds = list_concat(fpinfo->remote_conds,
list_copy(fpinfo_i->remote_conds));
break;
case JOIN_FULL:
- fpinfo->joinclauses = list_concat(fpinfo->joinclauses,
- list_copy(fpinfo_i->remote_conds));
- fpinfo->joinclauses = list_concat(fpinfo->joinclauses,
- list_copy(fpinfo_o->remote_conds));
+ if (fpinfo_i->remote_conds || fpinfo_o->remote_conds)
+ return false;
break;
default:
/* Should not happen, we have just check this above */
elog(ERROR, "unsupported join type %d", jointype);
}
/*
+ * For an inner join, as explained above all restrictions can be treated
+ * alike. Treating the pushed down conditions as join conditions allows a
+ * top level full outer join to be deparsed without requiring subqueries.
+ */
+ if (jointype == JOIN_INNER)
+ {
+ Assert(!fpinfo->joinclauses);
+ fpinfo->joinclauses = fpinfo->remote_conds;
+ fpinfo->remote_conds = NIL;
+ }
+
+ /* Mark that this join can be pushed down safely */
+ fpinfo->pushdown_safe = true;
+
+ /*
+ * If user is willing to estimate cost for a scan of either of the joining
+ * relations using EXPLAIN, he intends to estimate scans on that relation
+ * more accurately. Then, it makes sense to estimate the cost the join
+ * with that relation more accurately using EXPLAIN.
+ */
+ fpinfo->use_remote_estimate = fpinfo_o->use_remote_estimate ||
+ fpinfo_i->use_remote_estimate;
+
+ /*
+ * Since both the joining relations come from the same server, the server
+ * level options should have same value for both the relations. Pick from
+ * any side.
+ */
+ fpinfo->fdw_startup_cost = fpinfo_o->fdw_startup_cost;
+ fpinfo->fdw_tuple_cost = fpinfo_o->fdw_tuple_cost;
+
+ /*
+ * Set cached relation costs to some negative value, so that we can detect
+ * when they are set to some sensible costs, during one (usually the
+ * first) of the calls to estimate_path_cost_size().
+ */
+ fpinfo->rel_startup_cost = -1;
+ fpinfo->rel_total_cost = -1;
+
+ /*
+ * Set fetch size to maximum of the joining sides, since we are expecting
+ * the rows returned by the join to be proportional to the relation sizes.
+ */
+ if (fpinfo_o->fetch_size > fpinfo_i->fetch_size)
+ fpinfo->fetch_size = fpinfo_o->fetch_size;
+ else
+ fpinfo->fetch_size = fpinfo_i->fetch_size;
+
+ /*
* Set the string describing this join relation to be used in EXPLAIN
* output of corresponding ForeignScan.
*/
fpinfo->relation_name = makeStringInfo();
appendStringInfo(fpinfo->relation_name, "(%s) %s JOIN (%s)",
fpinfo_o->relation_name->data,
get_jointype_name(fpinfo->jointype),
fpinfo_i->relation_name->data);
return true;
diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql
index f420b23..d1f44d6 100644
--- a/contrib/postgres_fdw/sql/postgres_fdw.sql
+++ b/contrib/postgres_fdw/sql/postgres_fdw.sql
@@ -365,20 +365,28 @@ EXPLAIN (COSTS false, VERBOSE)
SELECT t1.c1, t2.c1 FROM ft5 t1 RIGHT JOIN ft4 t2 ON (t1.c1 = t2.c1) ORDER BY t2.c1, t1.c1 OFFSET 10 LIMIT 10;
SELECT t1.c1, t2.c1 FROM ft5 t1 RIGHT JOIN ft4 t2 ON (t1.c1 = t2.c1) ORDER BY t2.c1, t1.c1 OFFSET 10 LIMIT 10;
-- right outer join three tables
EXPLAIN (COSTS false, VERBOSE)
SELECT t1.c1, t2.c2, t3.c3 FROM ft2 t1 RIGHT JOIN ft2 t2 ON (t1.c1 = t2.c1) RIGHT JOIN ft4 t3 ON (t2.c1 = t3.c1) OFFSET 10 LIMIT 10;
SELECT t1.c1, t2.c2, t3.c3 FROM ft2 t1 RIGHT JOIN ft2 t2 ON (t1.c1 = t2.c1) RIGHT JOIN ft4 t3 ON (t2.c1 = t3.c1) OFFSET 10 LIMIT 10;
-- full outer join
EXPLAIN (COSTS false, VERBOSE)
SELECT t1.c1, t2.c1 FROM ft4 t1 FULL JOIN ft5 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c1, t2.c1 OFFSET 45 LIMIT 10;
SELECT t1.c1, t2.c1 FROM ft4 t1 FULL JOIN ft5 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c1, t2.c1 OFFSET 45 LIMIT 10;
+-- full outer join with restrictions on the joining relations
+EXPLAIN (COSTS false, VERBOSE)
+SELECT t1.c1, t2.c1 FROM (SELECT c1 FROM ft4 WHERE c1 between 50 and 60) t1 FULL JOIN (SELECT c1 FROM ft5 WHERE c1 between 50 and 60) t2 ON (t1.c1 = t2.c1) ORDER BY t1.c1, t2.c1;
+SELECT t1.c1, t2.c1 FROM (SELECT c1 FROM ft4 WHERE c1 between 50 and 60) t1 FULL JOIN (SELECT c1 FROM ft5 WHERE c1 between 50 and 60) t2 ON (t1.c1 = t2.c1) ORDER BY t1.c1, t2.c1;
+-- full outer join + inner join
+EXPLAIN (COSTS false, VERBOSE)
+SELECT t1.c1, t2.c1, t3.c1 FROM ft4 t1 INNER JOIN ft5 t2 ON (t1.c1 = t2.c1 + 1 and t1.c1 between 50 and 60) FULL JOIN ft4 t3 ON (t2.c1 = t3.c1) ORDER BY t1.c1, t2.c1, t3.c1 LIMIT 10;
+SELECT t1.c1, t2.c1, t3.c1 FROM ft4 t1 INNER JOIN ft5 t2 ON (t1.c1 = t2.c1 + 1 and t1.c1 between 50 and 60) FULL JOIN ft4 t3 ON (t2.c1 = t3.c1) ORDER BY t1.c1, t2.c1, t3.c1 LIMIT 10;
-- full outer join three tables
EXPLAIN (COSTS false, VERBOSE)
SELECT t1.c1, t2.c2, t3.c3 FROM ft2 t1 FULL JOIN ft2 t2 ON (t1.c1 = t2.c1) FULL JOIN ft4 t3 ON (t2.c1 = t3.c1) OFFSET 10 LIMIT 10;
SELECT t1.c1, t2.c2, t3.c3 FROM ft2 t1 FULL JOIN ft2 t2 ON (t1.c1 = t2.c1) FULL JOIN ft4 t3 ON (t2.c1 = t3.c1) OFFSET 10 LIMIT 10;
-- full outer join + right outer join
EXPLAIN (COSTS false, VERBOSE)
SELECT t1.c1, t2.c2, t3.c3 FROM ft2 t1 FULL JOIN ft2 t2 ON (t1.c1 = t2.c1) RIGHT JOIN ft4 t3 ON (t2.c1 = t3.c1) OFFSET 10 LIMIT 10;
SELECT t1.c1, t2.c2, t3.c3 FROM ft2 t1 FULL JOIN ft2 t2 ON (t1.c1 = t2.c1) RIGHT JOIN ft4 t3 ON (t2.c1 = t3.c1) OFFSET 10 LIMIT 10;
-- right outer join + full outer join
EXPLAIN (COSTS false, VERBOSE)
Thanks Ashutosh for the patch. I have applied and tested it. Now getting
proper result for reported issue.
Thanks & Regards,
Rajkumar Raghuwanshi
QMG, EnterpriseDB Corporation
On Tue, Mar 29, 2016 at 7:50 PM, Ashutosh Bapat <
ashutosh.bapat@enterprisedb.com> wrote:
Show quoted text
Observation:_ Inner join and full outer join combination on a table
generating wrong result.
SELECT * FROM lt;
c1
----
1
2
(2 rows)SELECT * FROM ft;
c1
----
1
2
(2 rows)\d+ ft
Foreign table "public.ft"
Column | Type | Modifiers | FDW Options | Storage | Stats target |
Description--------+---------+-----------+-------------+---------+--------------+-------------
c1 | integer | | | plain | |
Server: link_server
FDW Options: (table_name 'lt')--inner join and full outer join on local tables
SELECT t1.c1,t2.c1,t3.c1 FROM lt t1 INNER JOIN lt t2 ON (t1.c1 = t2.c1)
FULL JOIN lt t3 ON (t2.c1 = t3.c1);
c1 | c1 | c1
----+----+----
1 | 1 | 1
2 | 2 | 2
(2 rows)--inner join and full outer join on corresponding foreign tables
SELECT t1.c1,t2.c1,t3.c1 FROM ft t1 INNER JOIN ft t2 ON (t1.c1 = t2.c1)
FULL JOIN ft t3 ON (t2.c1 = t3.c1);
c1 | c1 | c1
----+----+----
1 | 1 | 1
1 | 2 |
2 | 1 |
2 | 2 | 2
(4 rows)Thanks Rajkumar for the detailed report.
I think the reason for that is in foreign_join_ok. This in that function:
wrongly pulls up remote_conds from joining relations in the FULL JOIN
case. I think we should not pull up such conditions in the FULL JOIN case.Right. For a full outer join, since each joining relation acts as outer
for the other, we can not pull up the quals to either join clauses or other
clauses. So, in such a case, we will need to encapsulate the joining
relation with conditions into a subquery. Unfortunately, the current
deparse logic does not handle this encapsulation. Adding that functionality
so close to the feature freeze might be risky given the amount of code
changes required.PFA patch with a quick fix. A full outer join with either of the joining
relations having WHERE conditions (or other clauses) is not pushed down. In
the particular case that was reported, the bug triggered because of the way
conditions are handled for an inner join. For an inner join, all the
conditions in ON as well as WHERE clause are treated like they are part of
WHERE clause. This allows pushing down a join even if there are unpushable
join clauses. But the pushable conditions can be put back into the ON
clause. This avoids using subqueries while deparsing.--
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company
On 2016/03/29 23:20, Ashutosh Bapat wrote:
I think the reason for that is in foreign_join_ok. This in that
function:wrongly pulls up remote_conds from joining relations in the FULL
JOIN case. I think we should not pull up such conditions in the
FULL JOIN case.
Right. For a full outer join, since each joining relation acts as outer
for the other, we can not pull up the quals to either join clauses or
other clauses. So, in such a case, we will need to encapsulate the
joining relation with conditions into a subquery. Unfortunately, the
current deparse logic does not handle this encapsulation. Adding that
functionality so close to the feature freeze might be risky given the
amount of code changes required.PFA patch with a quick fix. A full outer join with either of the joining
relations having WHERE conditions (or other clauses) is not pushed down.
In the particular case that was reported, the bug triggered because of
the way conditions are handled for an inner join. For an inner join, all
the conditions in ON as well as WHERE clause are treated like they are
part of WHERE clause. This allows pushing down a join even if there are
unpushable join clauses. But the pushable conditions can be put back
into the ON clause. This avoids using subqueries while deparsing.
I'm not sure that is a good idea. As you mentioned, we could support
FULL JOIN fully, by encapsulating a joining relation with conditions
into a subquery. And ISTM that it is relatively easy to do that, by
borrowing some ideas from Hanada-san's original join pushdown patch. If
it's OK, I'll create a patch for that in a few days.
Sorry for speaking up late.
Best regards,
Etsuro Fujita
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Thu, Apr 14, 2016 at 8:42 AM, Etsuro Fujita <fujita.etsuro@lab.ntt.co.jp>
wrote:
On 2016/03/29 23:20, Ashutosh Bapat wrote:
I think the reason for that is in foreign_join_ok. This in that
function:wrongly pulls up remote_conds from joining relations in the FULL
JOIN case. I think we should not pull up such conditions in the
FULL JOIN case.Right. For a full outer join, since each joining relation acts as outer
for the other, we can not pull up the quals to either join clauses or
other clauses. So, in such a case, we will need to encapsulate the
joining relation with conditions into a subquery. Unfortunately, the
current deparse logic does not handle this encapsulation. Adding that
functionality so close to the feature freeze might be risky given the
amount of code changes required.PFA patch with a quick fix. A full outer join with either of the joining
relations having WHERE conditions (or other clauses) is not pushed down.
In the particular case that was reported, the bug triggered because of
the way conditions are handled for an inner join. For an inner join, all
the conditions in ON as well as WHERE clause are treated like they are
part of WHERE clause. This allows pushing down a join even if there are
unpushable join clauses. But the pushable conditions can be put back
into the ON clause. This avoids using subqueries while deparsing.I'm not sure that is a good idea. As you mentioned, we could support FULL
JOIN fully, by encapsulating a joining relation with conditions into a
subquery. And ISTM that it is relatively easy to do that, by borrowing
some ideas from Hanada-san's original join pushdown patch. If it's OK,
I'll create a patch for that in a few days.
In his patch the deparsing targetlist and conditions required that the FROM
clause entries were ready with the columns from base relations and joins
realiased. That's no more true. We deparse every Var node as <relation
alias>.<column name> where relation alias is nothing but rN; N being index
of RangeTblEntry. So, Hanada-san's method to deparse recursively can not be
applied as such now.
Here's what needs to be done:
When we identify that certain relation (base or join) needs a subquery to
be deparsed (because the join relation above it could not pull the quals
up), we remember it in the upper join relation. Before deparsing 1. we walk
the join tree and collect targetlists of all such relations, 2. associate
column aliases with those targetlists (save the column alises in resname?)
and craft a relation alias 3. associate the relations alias, column
aliases and targetlists with the base relations involved in such relations
(may be creating a list similar to rtable). While deparsing a Var node, we
check if corresponding base relation is itself or part of a relation
deparsed as a subquery. If it is then we lookup that Var in the targetlist
associated with the base relation and use corresponding relation and column
alias for deparsing it. Otherwise, deparse it as <relation alias>.<column
name> usually.
That looks like a big code change to go after feature freeze. So, we will
leave it for 9.7, unless RMT allows us to introduce that change.
--
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company
On 2016/04/14 15:20, Ashutosh Bapat wrote:
On Thu, Apr 14, 2016 at 8:42 AM, Etsuro Fujita
<fujita.etsuro@lab.ntt.co.jp <mailto:fujita.etsuro@lab.ntt.co.jp>> wrote:
As you mentioned, we could
support FULL JOIN fully, by encapsulating a joining relation with
conditions into a subquery. And ISTM that it is relatively easy to
do that, by borrowing some ideas from Hanada-san's original join
pushdown patch. If it's OK, I'll create a patch for that in a few days.
In his patch the deparsing targetlist and conditions required that the
FROM clause entries were ready with the columns from base relations and
joins realiased. That's no more true. We deparse every Var node as
<relation alias>.<column name> where relation alias is nothing but rN; N
being index of RangeTblEntry. So, Hanada-san's method to deparse
recursively can not be applied as such now.
I think so, too. I don't think his ideas could be applied as is.
Here's what needs to be done:
When we identify that certain relation (base or join) needs a subquery
to be deparsed (because the join relation above it could not pull the
quals up), we remember it in the upper join relation. Before deparsing
1. we walk the join tree and collect targetlists of all such relations,
2. associate column aliases with those targetlists (save the column
alises in resname?) and craft a relation alias 3. associate the
relations alias, column aliases and targetlists with the base relations
involved in such relations (may be creating a list similar to rtable).
While deparsing a Var node, we check if corresponding base relation is
itself or part of a relation deparsed as a subquery. If it is then we
lookup that Var in the targetlist associated with the base relation and
use corresponding relation and column alias for deparsing it. Otherwise,
deparse it as <relation alias>.<column name> usually.
Good to know. That is what I have in mind, except for the way of
collecting subqueries' columns and associating those columns with
relation and column aliases, which I think can be done more easily.
Please find attached a WIP patch. That patch works well at least for
queries in your patch. Maybe I'm missing something, though.
That looks like a big code change to go after feature freeze. So, we
will leave it for 9.7, unless RMT allows us to introduce that change.
OK
Best regards,
Etsuro Fujita
Attachments:
pg_fj_cond_efujita.patchtext/x-patch; name=pg_fj_cond_efujita.patchDownload
*** a/contrib/postgres_fdw/deparse.c
--- b/contrib/postgres_fdw/deparse.c
***************
*** 88,93 **** typedef struct foreign_loc_cxt
--- 88,105 ----
} foreign_loc_cxt;
/*
+ * Structure for information on subqueries' column aliases
+ */
+ typedef struct ColumnAliases
+ {
+ List *exprs; /* subqueries' columns */
+ int max_exprs; /* maximum number of columns stored */
+ int *ssno; /* table alias numbers of columns */
+ int *sscolno; /* column alias numbers of columns */
+ int next_ssno; /* next subquery's table alias number */
+ } ColumnAliases;
+
+ /*
* Context for deparseExpr
*/
typedef struct deparse_expr_cxt
***************
*** 96,101 **** typedef struct deparse_expr_cxt
--- 108,114 ----
RelOptInfo *foreignrel; /* the foreign relation we are planning for */
StringInfo buf; /* output buffer to append to */
List **params_list; /* exprs that will become remote Params */
+ ColumnAliases *colaliases; /* subqueries' column aliases */
} deparse_expr_cxt;
#define REL_ALIAS_PREFIX "r"
***************
*** 103,108 **** typedef struct deparse_expr_cxt
--- 116,124 ----
#define ADD_REL_QUALIFIER(buf, varno) \
appendStringInfo((buf), "%s%d.", REL_ALIAS_PREFIX, (varno))
+ #define SS_ALIAS_PREFIX "ss"
+ #define SSCOL_ALIAS_PREFIX "c"
+
/*
* Functions to determine whether an expression can be evaluated safely on
* remote server.
***************
*** 152,164 **** static void printRemoteParam(int paramindex, Oid paramtype, int32 paramtypmod,
deparse_expr_cxt *context);
static void printRemotePlaceholder(Oid paramtype, int32 paramtypmod,
deparse_expr_cxt *context);
! static void deparseSelectSql(List *tlist, List **retrieved_attrs,
deparse_expr_cxt *context);
static void deparseLockingClause(deparse_expr_cxt *context);
static void appendOrderByClause(List *pathkeys, deparse_expr_cxt *context);
static void appendConditions(List *exprs, deparse_expr_cxt *context);
static void deparseFromExprForRel(StringInfo buf, PlannerInfo *root,
! RelOptInfo *joinrel, bool use_alias, List **params_list);
/*
--- 168,188 ----
deparse_expr_cxt *context);
static void printRemotePlaceholder(Oid paramtype, int32 paramtypmod,
deparse_expr_cxt *context);
! static void deparseSelectSql(List *tlist,
! List *remote_conds,
! List **retrieved_attrs,
deparse_expr_cxt *context);
static void deparseLockingClause(deparse_expr_cxt *context);
static void appendOrderByClause(List *pathkeys, deparse_expr_cxt *context);
static void appendConditions(List *exprs, deparse_expr_cxt *context);
static void deparseFromExprForRel(StringInfo buf, PlannerInfo *root,
! RelOptInfo *joinrel, bool use_alias,
! List **params_list, ColumnAliases *colaliases);
! static void appendSubselectAlias(List *exprs, deparse_expr_cxt *context);
! static void updateColumnAliases(ColumnAliases *colaliases, Expr *expr,
! int ssno, int sscolno);
! static bool hasColumnAlias(ColumnAliases *colaliases, Expr *node,
! int *ssno, int *sscolno);
/*
***************
*** 764,769 **** deparseSelectStmtForRel(StringInfo buf, PlannerInfo *root, RelOptInfo *rel,
--- 788,794 ----
List *tlist, List *remote_conds, List *pathkeys,
List **retrieved_attrs, List **params_list)
{
+ ColumnAliases colaliases;
deparse_expr_cxt context;
/* We handle relations for foreign tables and joins between those */
***************
*** 771,793 **** deparseSelectStmtForRel(StringInfo buf, PlannerInfo *root, RelOptInfo *rel,
rel->reloptkind == RELOPT_BASEREL ||
rel->reloptkind == RELOPT_OTHER_MEMBER_REL);
/* Fill portions of context common to join and base relation */
context.buf = buf;
context.root = root;
context.foreignrel = rel;
context.params_list = params_list;
! /* Construct SELECT clause and FROM clause */
! deparseSelectSql(tlist, retrieved_attrs, &context);
!
! /*
! * Construct WHERE clause
! */
! if (remote_conds)
! {
! appendStringInfo(buf, " WHERE ");
! appendConditions(remote_conds, &context);
! }
/* Add ORDER BY clause if we found any useful pathkeys */
if (pathkeys)
--- 796,817 ----
rel->reloptkind == RELOPT_BASEREL ||
rel->reloptkind == RELOPT_OTHER_MEMBER_REL);
+ /* Initialize subqueries' column aliases */
+ colaliases.exprs = NIL;
+ colaliases.max_exprs = 32;
+ colaliases.ssno = (int *) palloc(colaliases.max_exprs * sizeof(int));
+ colaliases.sscolno = (int *) palloc(colaliases.max_exprs * sizeof(int));
+ colaliases.next_ssno = 1;
+
/* Fill portions of context common to join and base relation */
context.buf = buf;
context.root = root;
context.foreignrel = rel;
context.params_list = params_list;
+ context.colaliases = &colaliases;
! /* Construct SELECT clause and FROM clause and WHERE clause */
! deparseSelectSql(tlist, remote_conds, retrieved_attrs, &context);
/* Add ORDER BY clause if we found any useful pathkeys */
if (pathkeys)
***************
*** 800,806 **** deparseSelectStmtForRel(StringInfo buf, PlannerInfo *root, RelOptInfo *rel,
/*
* Construct a simple SELECT statement that retrieves desired columns
* of the specified foreign table, and append it to "buf". The output
! * contains just "SELECT ... FROM ....".
*
* We also create an integer List of the columns being retrieved, which is
* returned to *retrieved_attrs.
--- 824,830 ----
/*
* Construct a simple SELECT statement that retrieves desired columns
* of the specified foreign table, and append it to "buf". The output
! * contains just "SELECT ... FROM ... WHERE ...".
*
* We also create an integer List of the columns being retrieved, which is
* returned to *retrieved_attrs.
***************
*** 809,820 **** deparseSelectStmtForRel(StringInfo buf, PlannerInfo *root, RelOptInfo *rel,
* deparseSelectStmtForRel() for details.
*/
static void
! deparseSelectSql(List *tlist, List **retrieved_attrs, deparse_expr_cxt *context)
{
StringInfo buf = context->buf;
RelOptInfo *foreignrel = context->foreignrel;
PlannerInfo *root = context->root;
PgFdwRelationInfo *fpinfo = (PgFdwRelationInfo *) foreignrel->fdw_private;
/*
* Construct SELECT list
--- 833,856 ----
* deparseSelectStmtForRel() for details.
*/
static void
! deparseSelectSql(List *tlist,
! List *remote_conds,
! List **retrieved_attrs,
! deparse_expr_cxt *context)
{
StringInfo buf = context->buf;
RelOptInfo *foreignrel = context->foreignrel;
PlannerInfo *root = context->root;
PgFdwRelationInfo *fpinfo = (PgFdwRelationInfo *) foreignrel->fdw_private;
+ StringInfoData jointree;
+
+ /*
+ * Deparse a join tree expression in FROM clause first.
+ */
+ initStringInfo(&jointree);
+ deparseFromExprForRel(&jointree, root, foreignrel,
+ (foreignrel->reloptkind == RELOPT_JOINREL),
+ context->params_list, context->colaliases);
/*
* Construct SELECT list
***************
*** 848,857 **** deparseSelectSql(List *tlist, List **retrieved_attrs, deparse_expr_cxt *context)
/*
* Construct FROM clause
*/
! appendStringInfoString(buf, " FROM ");
! deparseFromExprForRel(buf, root, foreignrel,
! (foreignrel->reloptkind == RELOPT_JOINREL),
! context->params_list);
}
/*
--- 884,899 ----
/*
* Construct FROM clause
*/
! appendStringInfo(buf, " FROM %s", jointree.data);
!
! /*
! * Construct WHERE clause
! */
! if (remote_conds)
! {
! appendStringInfoString(buf, " WHERE ");
! appendConditions(remote_conds, context);
! }
}
/*
***************
*** 1117,1123 **** deparseExplicitTargetList(List *tlist, List **retrieved_attrs,
if (i > 0)
appendStringInfoString(buf, ", ");
! deparseVar(var, context);
*retrieved_attrs = lappend_int(*retrieved_attrs, i + 1);
--- 1159,1165 ----
if (i > 0)
appendStringInfoString(buf, ", ");
! deparseExpr((Expr *) var, context);
*retrieved_attrs = lappend_int(*retrieved_attrs, i + 1);
***************
*** 1137,1143 **** deparseExplicitTargetList(List *tlist, List **retrieved_attrs,
*/
static void
deparseFromExprForRel(StringInfo buf, PlannerInfo *root, RelOptInfo *foreignrel,
! bool use_alias, List **params_list)
{
PgFdwRelationInfo *fpinfo = (PgFdwRelationInfo *) foreignrel->fdw_private;
--- 1179,1185 ----
*/
static void
deparseFromExprForRel(StringInfo buf, PlannerInfo *root, RelOptInfo *foreignrel,
! bool use_alias, List **params_list, ColumnAliases *colaliases)
{
PgFdwRelationInfo *fpinfo = (PgFdwRelationInfo *) foreignrel->fdw_private;
***************
*** 1145,1160 **** deparseFromExprForRel(StringInfo buf, PlannerInfo *root, RelOptInfo *foreignrel,
{
RelOptInfo *rel_o = fpinfo->outerrel;
RelOptInfo *rel_i = fpinfo->innerrel;
StringInfoData join_sql_o;
StringInfoData join_sql_i;
/* Deparse outer relation */
initStringInfo(&join_sql_o);
! deparseFromExprForRel(&join_sql_o, root, rel_o, true, params_list);
/* Deparse inner relation */
initStringInfo(&join_sql_i);
! deparseFromExprForRel(&join_sql_i, root, rel_i, true, params_list);
/*
* For a join relation FROM clause entry is deparsed as
--- 1187,1250 ----
{
RelOptInfo *rel_o = fpinfo->outerrel;
RelOptInfo *rel_i = fpinfo->innerrel;
+ PgFdwRelationInfo *fpinfo_o = (PgFdwRelationInfo *) rel_o->fdw_private;
+ PgFdwRelationInfo *fpinfo_i = (PgFdwRelationInfo *) rel_i->fdw_private;
StringInfoData join_sql_o;
StringInfoData join_sql_i;
/* Deparse outer relation */
initStringInfo(&join_sql_o);
! if (fpinfo->jointype == JOIN_FULL && fpinfo_o->remote_conds)
! {
! List *tlist = NIL;
! List *retrieved_attrs = NIL;
! deparse_expr_cxt context;
!
! context.buf = &join_sql_o;
! context.root = root;
! context.foreignrel = rel_o;
! context.params_list = params_list;
! context.colaliases = colaliases;
!
! tlist = add_to_flat_tlist(tlist, rel_o->reltarget->exprs);
! appendStringInfoChar(&join_sql_o, '(');
! deparseSelectSql(tlist,
! fpinfo_o->remote_conds,
! &retrieved_attrs,
! &context);
! appendStringInfoChar(&join_sql_o, ')');
! appendSubselectAlias(rel_o->reltarget->exprs, &context);
! }
! else
! deparseFromExprForRel(&join_sql_o, root, rel_o, true, params_list,
! colaliases);
/* Deparse inner relation */
initStringInfo(&join_sql_i);
! if (fpinfo->jointype == JOIN_FULL && fpinfo_i->remote_conds)
! {
! List *tlist = NIL;
! List *retrieved_attrs = NIL;
! deparse_expr_cxt context;
!
! context.buf = &join_sql_i;
! context.root = root;
! context.foreignrel = rel_i;
! context.params_list = params_list;
! context.colaliases = colaliases;
!
! tlist = add_to_flat_tlist(tlist, rel_o->reltarget->exprs);
! appendStringInfoChar(&join_sql_i, '(');
! deparseSelectSql(tlist,
! fpinfo_i->remote_conds,
! &retrieved_attrs,
! &context);
! appendStringInfoChar(&join_sql_i, ')');
! appendSubselectAlias(rel_i->reltarget->exprs, &context);
! }
! else
! deparseFromExprForRel(&join_sql_i, root, rel_i, true, params_list,
! colaliases);
/*
* For a join relation FROM clause entry is deparsed as
***************
*** 1173,1178 **** deparseFromExprForRel(StringInfo buf, PlannerInfo *root, RelOptInfo *foreignrel,
--- 1263,1269 ----
context.foreignrel = foreignrel;
context.root = root;
context.params_list = params_list;
+ context.colaliases = colaliases;
appendStringInfo(buf, "(");
appendConditions(fpinfo->joinclauses, &context);
***************
*** 1206,1212 **** deparseFromExprForRel(StringInfo buf, PlannerInfo *root, RelOptInfo *foreignrel,
heap_close(rel, NoLock);
}
! return;
}
/*
--- 1297,1396 ----
heap_close(rel, NoLock);
}
! }
!
! static void
! appendSubselectAlias(List *exprs, deparse_expr_cxt *context)
! {
! StringInfo buf = context->buf;
! ColumnAliases *colaliases = context->colaliases;
! int ssno;
! int sscolno;
! bool first;
! ListCell *lc;
!
! /* Append a table alias */
! ssno = colaliases->next_ssno;
! appendStringInfo(buf, " %s%d", SS_ALIAS_PREFIX, ssno);
!
! /* Append column aliases */
! sscolno = 1;
! first = true;
! appendStringInfoChar(buf, '(');
! foreach(lc, exprs)
! {
! Expr *expr = (Expr *) lfirst(lc);
!
! if (!first)
! appendStringInfoString(buf, ", ");
! first = false;
!
! appendStringInfo(buf, "%s%d", SSCOL_ALIAS_PREFIX, sscolno);
!
! updateColumnAliases(colaliases, expr, ssno, sscolno);
!
! sscolno++;
! }
! appendStringInfoChar(buf, ')');
!
! colaliases->next_ssno++;
! }
!
! static void
! updateColumnAliases(ColumnAliases *colaliases, Expr *expr,
! int ssno, int sscolno)
! {
! int num_exprs = list_length(colaliases->exprs);
! int max_exprs = colaliases->max_exprs;
! int i;
! ListCell *lc;
!
! i = 0;
! foreach(lc, colaliases->exprs)
! {
! if (equal(lfirst(lc), (Node *) expr))
! {
! colaliases->ssno[i] = ssno;
! colaliases->sscolno[i] = sscolno;
! return;
! }
! i++;
! }
! Assert(i == num_exprs);
!
! colaliases->exprs = lappend(colaliases->exprs, expr);
! if (num_exprs + 1 >= max_exprs)
! {
! max_exprs *= 2;
! colaliases->ssno = (int *) repalloc(colaliases->ssno,
! max_exprs * sizeof(int));
! colaliases->sscolno = (int *) repalloc(colaliases->sscolno,
! max_exprs * sizeof(int));
! colaliases->max_exprs = max_exprs;
! }
! colaliases->ssno[num_exprs] = ssno;
! colaliases->sscolno[num_exprs] = sscolno;
! }
!
! static bool
! hasColumnAlias(ColumnAliases *colaliases, Expr *node,
! int *ssno, int *sscolno)
! {
! int i;
! ListCell *lc;
!
! i = 0;
! foreach(lc, colaliases->exprs)
! {
! if (equal(lfirst(lc), (Node *) node))
! {
! *ssno = colaliases->ssno[i];
! *sscolno = colaliases->sscolno[i];
! return true;
! }
! i++;
! }
! return false;
}
/*
***************
*** 1342,1347 **** deparseDirectUpdateSql(StringInfo buf, PlannerInfo *root,
--- 1526,1532 ----
context.foreignrel = baserel;
context.buf = buf;
context.params_list = params_list;
+ context.colaliases = NULL;
appendStringInfoString(buf, "UPDATE ");
deparseRelation(buf, rel);
***************
*** 1426,1431 **** deparseDirectDeleteSql(StringInfo buf, PlannerInfo *root,
--- 1611,1617 ----
context.foreignrel = baserel;
context.buf = buf;
context.params_list = params_list;
+ context.colaliases = NULL;
appendStringInfoString(buf, "DELETE FROM ");
deparseRelation(buf, rel);
***************
*** 1741,1746 **** deparseExpr(Expr *node, deparse_expr_cxt *context)
--- 1927,1946 ----
if (node == NULL)
return;
+ if (context->colaliases)
+ {
+ int ssno;
+ int sscolno;
+
+ if (hasColumnAlias(context->colaliases, node, &ssno, &sscolno))
+ {
+ appendStringInfo(context->buf, "%s%d.%s%d",
+ SS_ALIAS_PREFIX, ssno,
+ SSCOL_ALIAS_PREFIX, sscolno);
+ return;
+ }
+ }
+
switch (nodeTag(node))
{
case T_Var:
*** a/contrib/postgres_fdw/expected/postgres_fdw.out
--- b/contrib/postgres_fdw/expected/postgres_fdw.out
***************
*** 1144,1149 **** SELECT t1.c1, t2.c1 FROM ft4 t1 FULL JOIN ft5 t2 ON (t1.c1 = t2.c1) ORDER BY t1.
--- 1144,1201 ----
| 27
(10 rows)
+ -- full outer join with restrictions on the joining relations
+ EXPLAIN (COSTS false, VERBOSE)
+ SELECT t1.c1, t2.c1 FROM (SELECT c1 FROM ft4 WHERE c1 BETWEEN 50 AND 60) t1 FULL JOIN (SELECT c1 FROM ft5 WHERE c1 BETWEEN 50 AND 60) t2 ON (t1.c1 = t2.c1) ORDER BY t1.c1, t2.c1;
+ QUERY PLAN
+ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Foreign Scan
+ Output: ft4.c1, ft5.c1
+ Relations: (public.ft4) FULL JOIN (public.ft5)
+ Remote SQL: SELECT ss1.c1, ss2.c1 FROM ((SELECT c1 FROM "S 1"."T 3" WHERE ((c1 >= 50)) AND ((c1 <= 60))) ss1(c1) FULL JOIN (SELECT c1 FROM "S 1"."T 4" WHERE ((c1 >= 50)) AND ((c1 <= 60))) ss2(c1) ON (((ss1.c1 = ss2.c1)))) ORDER BY ss1.c1 ASC NULLS LAST, ss2.c1 ASC NULLS LAST
+ (4 rows)
+
+ SELECT t1.c1, t2.c1 FROM (SELECT c1 FROM ft4 WHERE c1 BETWEEN 50 AND 60) t1 FULL JOIN (SELECT c1 FROM ft5 WHERE c1 BETWEEN 50 AND 60) t2 ON (t1.c1 = t2.c1) ORDER BY t1.c1, t2.c1;
+ c1 | c1
+ ----+----
+ 50 |
+ 52 |
+ 54 | 54
+ 56 |
+ 58 |
+ 60 | 60
+ | 51
+ | 57
+ (8 rows)
+
+ -- full outer join + inner join
+ EXPLAIN (COSTS false, VERBOSE)
+ SELECT t1.c1, t2.c1, t3.c1 FROM ft4 t1 INNER JOIN ft5 t2 ON (t1.c1 = t2.c1 + 1 AND t1.c1 BETWEEN 50 AND 60) FULL JOIN ft4 t3 ON (t2.c1 = t3.c1) ORDER BY t1.c1, t2.c1, t3.c1 LIMIT 10;
+ QUERY PLAN
+ --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Limit
+ Output: t1.c1, t2.c1, t3.c1
+ -> Foreign Scan
+ Output: t1.c1, t2.c1, t3.c1
+ Relations: ((public.ft4 t1) INNER JOIN (public.ft5 t2)) FULL JOIN (public.ft4 t3)
+ Remote SQL: SELECT ss1.c1, ss1.c2, r4.c1 FROM ((SELECT r1.c1, r2.c1 FROM ("S 1"."T 3" r1 INNER JOIN "S 1"."T 4" r2 ON (TRUE)) WHERE ((r1.c1 = (r2.c1 + 1))) AND ((r1.c1 >= 50)) AND ((r1.c1 <= 60))) ss1(c1, c2) FULL JOIN "S 1"."T 3" r4 ON (((ss1.c2 = r4.c1)))) ORDER BY ss1.c1 ASC NULLS LAST, ss1.c2 ASC NULLS LAST, r4.c1 ASC NULLS LAST
+ (6 rows)
+
+ SELECT t1.c1, t2.c1, t3.c1 FROM ft4 t1 INNER JOIN ft5 t2 ON (t1.c1 = t2.c1 + 1 AND t1.c1 BETWEEN 50 AND 60) FULL JOIN ft4 t3 ON (t2.c1 = t3.c1) ORDER BY t1.c1, t2.c1, t3.c1 LIMIT 10;
+ c1 | c1 | c1
+ ----+----+----
+ 52 | 51 |
+ 58 | 57 |
+ | | 2
+ | | 4
+ | | 6
+ | | 8
+ | | 10
+ | | 12
+ | | 14
+ | | 16
+ (10 rows)
+
-- full outer join three tables
EXPLAIN (COSTS false, VERBOSE)
SELECT t1.c1, t2.c2, t3.c3 FROM ft2 t1 FULL JOIN ft2 t2 ON (t1.c1 = t2.c1) FULL JOIN ft4 t3 ON (t2.c1 = t3.c1) OFFSET 10 LIMIT 10;
*** a/contrib/postgres_fdw/postgres_fdw.c
--- b/contrib/postgres_fdw/postgres_fdw.c
***************
*** 4073,4082 **** foreign_join_ok(PlannerInfo *root, RelOptInfo *joinrel, JoinType jointype,
break;
case JOIN_FULL:
- fpinfo->joinclauses = list_concat(fpinfo->joinclauses,
- list_copy(fpinfo_i->remote_conds));
- fpinfo->joinclauses = list_concat(fpinfo->joinclauses,
- list_copy(fpinfo_o->remote_conds));
break;
default:
--- 4073,4078 ----
*** a/contrib/postgres_fdw/sql/postgres_fdw.sql
--- b/contrib/postgres_fdw/sql/postgres_fdw.sql
***************
*** 372,377 **** SELECT t1.c1, t2.c2, t3.c3 FROM ft2 t1 RIGHT JOIN ft2 t2 ON (t1.c1 = t2.c1) RIGH
--- 372,385 ----
EXPLAIN (COSTS false, VERBOSE)
SELECT t1.c1, t2.c1 FROM ft4 t1 FULL JOIN ft5 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c1, t2.c1 OFFSET 45 LIMIT 10;
SELECT t1.c1, t2.c1 FROM ft4 t1 FULL JOIN ft5 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c1, t2.c1 OFFSET 45 LIMIT 10;
+ -- full outer join with restrictions on the joining relations
+ EXPLAIN (COSTS false, VERBOSE)
+ SELECT t1.c1, t2.c1 FROM (SELECT c1 FROM ft4 WHERE c1 BETWEEN 50 AND 60) t1 FULL JOIN (SELECT c1 FROM ft5 WHERE c1 BETWEEN 50 AND 60) t2 ON (t1.c1 = t2.c1) ORDER BY t1.c1, t2.c1;
+ SELECT t1.c1, t2.c1 FROM (SELECT c1 FROM ft4 WHERE c1 BETWEEN 50 AND 60) t1 FULL JOIN (SELECT c1 FROM ft5 WHERE c1 BETWEEN 50 AND 60) t2 ON (t1.c1 = t2.c1) ORDER BY t1.c1, t2.c1;
+ -- full outer join + inner join
+ EXPLAIN (COSTS false, VERBOSE)
+ SELECT t1.c1, t2.c1, t3.c1 FROM ft4 t1 INNER JOIN ft5 t2 ON (t1.c1 = t2.c1 + 1 AND t1.c1 BETWEEN 50 AND 60) FULL JOIN ft4 t3 ON (t2.c1 = t3.c1) ORDER BY t1.c1, t2.c1, t3.c1 LIMIT 10;
+ SELECT t1.c1, t2.c1, t3.c1 FROM ft4 t1 INNER JOIN ft5 t2 ON (t1.c1 = t2.c1 + 1 AND t1.c1 BETWEEN 50 AND 60) FULL JOIN ft4 t3 ON (t2.c1 = t3.c1) ORDER BY t1.c1, t2.c1, t3.c1 LIMIT 10;
-- full outer join three tables
EXPLAIN (COSTS false, VERBOSE)
SELECT t1.c1, t2.c2, t3.c3 FROM ft2 t1 FULL JOIN ft2 t2 ON (t1.c1 = t2.c1) FULL JOIN ft4 t3 ON (t2.c1 = t3.c1) OFFSET 10 LIMIT 10;
On Tue, Mar 29, 2016 at 10:20 AM, Ashutosh Bapat
<ashutosh.bapat@enterprisedb.com> wrote:
I think the reason for that is in foreign_join_ok. This in that function:
wrongly pulls up remote_conds from joining relations in the FULL JOIN
case. I think we should not pull up such conditions in the FULL JOIN case.Right. For a full outer join, since each joining relation acts as outer for
the other, we can not pull up the quals to either join clauses or other
clauses. So, in such a case, we will need to encapsulate the joining
relation with conditions into a subquery. Unfortunately, the current deparse
logic does not handle this encapsulation. Adding that functionality so close
to the feature freeze might be risky given the amount of code changes
required.PFA patch with a quick fix. A full outer join with either of the joining
relations having WHERE conditions (or other clauses) is not pushed down. In
the particular case that was reported, the bug triggered because of the way
conditions are handled for an inner join. For an inner join, all the
conditions in ON as well as WHERE clause are treated like they are part of
WHERE clause. This allows pushing down a join even if there are unpushable
join clauses. But the pushable conditions can be put back into the ON
clause. This avoids using subqueries while deparsing.
Committed.
I think we should introduce subquery-based deparsing for 9.7, but I
agree it's better not to do it now. I think we should try to handle
SEMI and ANTI joins that way, too.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers