From 5843659d091bfb6f2c60e010ea1fd00e55ee6ada Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Sun, 20 Jun 2021 11:48:44 -0400 Subject: Stabilize test case added by commit f61db909d. Buildfarm members ayu and tern have sometimes shown a different plan than expected for this query. I'd been unable to reproduce that before today, but I finally realized what is happening. If there is a concurrent open transaction (probably an autovacuum run in the buildfarm, but this can also be arranged manually), then the index entries for the rows removed by the DELETE a few lines up are not killed promptly, causing a change in the planner's estimate of the extremal value of ft2.c1, which moves the rowcount estimate for "c1 > 1100" by enough to change the join plan from nestloop to hash. To fix, change the query condition to "c1 > 1000", causing the hash plan to be preferred whether or not a concurrent open transaction exists. Since this UPDATE is tailored to be a no-op, nothing else changes. Report: https://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=ayu&dt=2021-06-09%2022%3A45%3A48 Report: https://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=ayu&dt=2021-06-13%2022%3A38%3A18 Report: https://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=tern&dt=2021-06-20%2004%3A55%3A36 --- contrib/postgres_fdw/sql/postgres_fdw.sql | 4 ++-- 1 file changed, 2 insertions(+), 2 deletions(-) (limited to 'contrib/postgres_fdw/sql/postgres_fdw.sql') diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql index 34a67d71605..8983209c74b 100644 --- a/contrib/postgres_fdw/sql/postgres_fdw.sql +++ b/contrib/postgres_fdw/sql/postgres_fdw.sql @@ -1259,9 +1259,9 @@ UPDATE ft2 AS target SET (c2) = ( -- but a SET clause that can't be EXPLAIN (VERBOSE, COSTS OFF) UPDATE ft2 d SET c2 = CASE WHEN random() >= 0 THEN d.c2 ELSE 0 END - FROM ft2 AS t WHERE d.c1 = t.c1 AND d.c1 > 1100; + FROM ft2 AS t WHERE d.c1 = t.c1 AND d.c1 > 1000; UPDATE ft2 d SET c2 = CASE WHEN random() >= 0 THEN d.c2 ELSE 0 END - FROM ft2 AS t WHERE d.c1 = t.c1 AND d.c1 > 1100; + FROM ft2 AS t WHERE d.c1 = t.c1 AND d.c1 > 1000; -- Test UPDATE/DELETE with WHERE or JOIN/ON conditions containing -- user-defined operators/functions -- cgit v1.2.3