| From: | Sami Imseih <samimseih(at)gmail(dot)com> |
|---|---|
| To: | pgsql-hackers <pgsql-hackers(at)postgresql(dot)org> |
| Cc: | akorotkov(at)postgresql(dot)org, a(dot)pyhalov(at)postgrespro(dot)ru |
| Subject: | postgres_fdw: Fix flaky push down FUNCTION RTE test |
| Date: | 2026-08-21 03:34:22 |
| Message-ID: | CAA5RZ0uC=GOihOKcF7o3JVETF3e-Fkc4OUO5NbqMEKCTeSN-dQ@mail.gmail.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-hackers |
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)
| Attachment | Content-Type | Size |
|---|---|---|
| v1-0001-postgres_fdw-Fix-flaky-push-down-FUNCTION-RTE-tes.patch | application/octet-stream | 4.6 KB |
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Fujii Masao | 2026-08-21 03:38:57 | Re: Fix small psql slash option leaks |
| Previous Message | Zhijie Hou (Fujitsu) | 2026-08-21 03:21:41 | RE: Parallel Apply |