diff options
| -rw-r--r-- | contrib/postgres_fdw/expected/postgres_fdw.out | 43 | ||||
| -rw-r--r-- | contrib/postgres_fdw/sql/postgres_fdw.sql | 9 |
2 files changed, 34 insertions, 18 deletions
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out index adbf77ffe53..62e084fb3d8 100644 --- a/contrib/postgres_fdw/expected/postgres_fdw.out +++ b/contrib/postgres_fdw/expected/postgres_fdw.out @@ -4244,6 +4244,10 @@ explain (verbose, costs off) select * from ft3 f, loct3 l -- =================================================================== -- test writable foreign table stuff -- =================================================================== +-- Autovacuum on the remote side might affect remote estimates, +-- so use local stats on ft2 as well +ALTER FOREIGN TABLE ft2 OPTIONS (SET use_remote_estimate 'false'); +ANALYZE ft2; EXPLAIN (verbose, costs off) INSERT INTO ft2 (c1,c2,c3) SELECT c1+1000,c2+100, c3 || c3 FROM ft2 LIMIT 20; QUERY PLAN @@ -5520,32 +5524,32 @@ UPDATE ft2 SET c3 = 'baz' FROM ft4 INNER JOIN ft5 ON (ft4.c1 = ft5.c1) WHERE ft2.c1 > 2000 AND ft2.c2 === ft4.c1 RETURNING ft2.*, ft4.*, ft5.*; -- can't be pushed down - QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- + QUERY PLAN +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Update on public.ft2 Output: ft2.c1, ft2.c2, ft2.c3, ft2.c4, ft2.c5, ft2.c6, ft2.c7, ft2.c8, ft4.c1, ft4.c2, ft4.c3, ft5.c1, ft5.c2, ft5.c3 Remote SQL: UPDATE "S 1"."T 1" SET c3 = $2 WHERE ctid = $1 RETURNING "C 1", c2, c3, c4, c5, c6, c7, c8 - -> Nested Loop + -> Hash Join Output: ft2.c1, ft2.c2, NULL::integer, 'baz'::text, ft2.c4, ft2.c5, ft2.c6, ft2.c7, ft2.c8, ft2.ctid, ft4.*, ft5.*, ft4.c1, ft4.c2, ft4.c3, ft5.c1, ft5.c2, ft5.c3 - Join Filter: (ft2.c2 === ft4.c1) - -> Foreign Scan on public.ft2 - Output: ft2.c1, ft2.c2, ft2.c4, ft2.c5, ft2.c6, ft2.c7, ft2.c8, ft2.ctid - Remote SQL: SELECT "C 1", c2, c4, c5, c6, c7, c8, ctid FROM "S 1"."T 1" WHERE (("C 1" > 2000)) FOR UPDATE + Hash Cond: (ft4.c1 = ft5.c1) -> Foreign Scan - Output: ft4.*, ft4.c1, ft4.c2, ft4.c3, ft5.*, ft5.c1, ft5.c2, ft5.c3 - Relations: (public.ft4) INNER JOIN (public.ft5) - Remote SQL: SELECT CASE WHEN (r2.*)::text IS NOT NULL THEN ROW(r2.c1, r2.c2, r2.c3) END, r2.c1, r2.c2, r2.c3, CASE WHEN (r3.*)::text IS NOT NULL THEN ROW(r3.c1, r3.c2, r3.c3) END, r3.c1, r3.c2, r3.c3 FROM ("S 1"."T 3" r2 INNER JOIN "S 1"."T 4" r3 ON (((r2.c1 = r3.c1)))) - -> Hash Join - Output: ft4.*, ft4.c1, ft4.c2, ft4.c3, ft5.*, ft5.c1, ft5.c2, ft5.c3 - Hash Cond: (ft4.c1 = ft5.c1) + Output: ft2.c1, ft2.c2, ft2.c4, ft2.c5, ft2.c6, ft2.c7, ft2.c8, ft2.ctid, ft4.*, ft4.c1, ft4.c2, ft4.c3 + Filter: (ft2.c2 === ft4.c1) + Relations: (public.ft2) INNER JOIN (public.ft4) + Remote SQL: SELECT r1."C 1", r1.c2, r1.c4, r1.c5, r1.c6, r1.c7, r1.c8, r1.ctid, CASE WHEN (r2.*)::text IS NOT NULL THEN ROW(r2.c1, r2.c2, r2.c3) END, r2.c1, r2.c2, r2.c3 FROM ("S 1"."T 1" r1 INNER JOIN "S 1"."T 3" r2 ON (((r1."C 1" > 2000)))) FOR UPDATE OF r1 + -> Nested Loop + Output: ft2.c1, ft2.c2, ft2.c4, ft2.c5, ft2.c6, ft2.c7, ft2.c8, ft2.ctid, ft4.*, ft4.c1, ft4.c2, ft4.c3 + -> Foreign Scan on public.ft2 + Output: ft2.c1, ft2.c2, ft2.c4, ft2.c5, ft2.c6, ft2.c7, ft2.c8, ft2.ctid + Remote SQL: SELECT "C 1", c2, c4, c5, c6, c7, c8, ctid FROM "S 1"."T 1" WHERE (("C 1" > 2000)) FOR UPDATE -> Foreign Scan on public.ft4 Output: ft4.*, ft4.c1, ft4.c2, ft4.c3 Remote SQL: SELECT c1, c2, c3 FROM "S 1"."T 3" - -> Hash - Output: ft5.*, ft5.c1, ft5.c2, ft5.c3 - -> Foreign Scan on public.ft5 - Output: ft5.*, ft5.c1, ft5.c2, ft5.c3 - Remote SQL: SELECT c1, c2, c3 FROM "S 1"."T 4" + -> Hash + Output: ft5.*, ft5.c1, ft5.c2, ft5.c3 + -> Foreign Scan on public.ft5 + Output: ft5.*, ft5.c1, ft5.c2, ft5.c3 + Remote SQL: SELECT c1, c2, c3 FROM "S 1"."T 4" (24 rows) UPDATE ft2 SET c3 = 'baz' @@ -5999,6 +6003,9 @@ select c2, count(*) from "S 1"."T 1" where c2 < 500 group by 1 order by 1; 407 | 100 (13 rows) +-- Go back to use remote-estimate mode on ft2 +VACUUM ANALYZE "S 1"."T 1"; +ALTER FOREIGN TABLE ft2 OPTIONS (SET use_remote_estimate 'true'); -- Above DMLs add data with c6 as NULL in ft1, so test ORDER BY NULLS LAST and NULLs -- FIRST behavior here. -- ORDER BY DESC NULLS LAST options diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql index 0b2c5289e3f..68fdfdc7659 100644 --- a/contrib/postgres_fdw/sql/postgres_fdw.sql +++ b/contrib/postgres_fdw/sql/postgres_fdw.sql @@ -1068,6 +1068,11 @@ explain (verbose, costs off) select * from ft3 f, loct3 l -- =================================================================== -- test writable foreign table stuff -- =================================================================== +-- Autovacuum on the remote side might affect remote estimates, +-- so use local stats on ft2 as well +ALTER FOREIGN TABLE ft2 OPTIONS (SET use_remote_estimate 'false'); +ANALYZE ft2; + EXPLAIN (verbose, costs off) INSERT INTO ft2 (c1,c2,c3) SELECT c1+1000,c2+100, c3 || c3 FROM ft2 LIMIT 20; INSERT INTO ft2 (c1,c2,c3) SELECT c1+1000,c2+100, c3 || c3 FROM ft2 LIMIT 20; @@ -1208,6 +1213,10 @@ commit; select c2, count(*) from ft2 where c2 < 500 group by 1 order by 1; select c2, count(*) from "S 1"."T 1" where c2 < 500 group by 1 order by 1; +-- Go back to use remote-estimate mode on ft2 +VACUUM ANALYZE "S 1"."T 1"; +ALTER FOREIGN TABLE ft2 OPTIONS (SET use_remote_estimate 'true'); + -- Above DMLs add data with c6 as NULL in ft1, so test ORDER BY NULLS LAST and NULLs -- FIRST behavior here. -- ORDER BY DESC NULLS LAST options |