Hi, The unnest function portion of the test introduced in 0ee83dd4a99 produced different plans on the two machines I tested with. On my Mac it produced the expected plan, but on my Amazon Linux 2 (aarch64) box it consistently failed:
```
# @@ -3135,20 +3135,20 @@
# SELECT t1.c1, t2.c1
# FROM ft1 t1, ft6 t2, unnest(ARRAY[3, 6, 9, 12, 15, 18]::int[]) AS u(id)
# WHERE t1.c1 = u.id AND t2.c1 = u.id AND t1.c3 < '00010';
# -
QUERY PLAN
#
-----------------------------------------------------------------------------------------------------------------------------------------------------
# +
QUERY PLAN
#
+----------------------------------------------------------------------------------------------------------------------------------------------
# Nested Loop
# Output: t1.c1, t2.c1
# Join Filter: (t1.c1 = u.id)
# - -> Foreign Scan on public.ft1 t1
# - Output: t1.c1
# - Remote SQL: SELECT "C 1" FROM "S 1"."T 1" WHERE ((c3 < '00010'))
# - -> Materialize
# + -> Foreign Scan
# Output: t2.c1, u.id
# - -> Foreign Scan
# - Output: t2.c1, u.id
# - Relations: (public.ft6 t2) INNER JOIN (pg_catalog.unnest() u)
# - Remote SQL: SELECT r2.c1, f3.c1 FROM ("S 1"."T 4" r2
INNER JOIN unnest('{3,6,9,12,15,18}'::integer[]) f3(c1) ON (((r2.c1 =
f3.c1))))
# + Relations: (public.ft6 t2) INNER JOIN (pg_catalog.unnest() u)
# + Remote SQL: SELECT r2.c1, f3.c1 FROM ("S 1"."T 4" r2 INNER
JOIN unnest('{3,6,9,12,15,18}'::integer[]) f3(c1) ON (((r2.c1 =
f3.c1))))
# + -> Materialize
# + Output: t1.c1
# + -> Foreign Scan on public.ft1 t1
# + Output: t1.c1
# + Remote SQL: SELECT "C 1" FROM "S 1"."T 1" WHERE ((c3
< '00010'))
# (12 rows)
#
# -- The remaining scenarios reuse a dedicated foreign table to cover the
```
The inner and outer sides of the nested loop flipped between the two
machines, because the costs of the two sides are almost identical. To
stabilize the test, instead of a range predicate WHERE ((c3 < '00010'))
we can use an equality WHERE ((c3 = '00010')), which restricts ft1 to a
single row so it's clearly the outer side of the loop. With the
equality predicate I get the same plan on both machines.
Attached is a patch to stabilize this test.
--
Sami Imseih
Amazon Web Services (AWS)
v1-0001-postgres_fdw-Fix-flaky-push-down-FUNCTION-RTE-tes.patch
Description: Binary data
