diff -U3 /tmp/cirrus-ci-build/src/test/regress/expected/create_index.out /tmp/cirrus-ci-build/build-32/testrun/recovery/027_stream_regress/data/results/create_index.out --- /tmp/cirrus-ci-build/src/test/regress/expected/create_index.out 2024-04-03 02:34:47.237264464 +0000 +++ /tmp/cirrus-ci-build/build-32/testrun/recovery/027_stream_regress/data/results/create_index.out 2024-04-03 02:41:40.688930488 +0000 @@ -1916,11 +1916,15 @@ SELECT unique1 FROM tenk1 WHERE unique1 IN (1,42,7) ORDER BY unique1; - QUERY PLAN -------------------------------------------------------- - Index Only Scan using tenk1_unique1 on tenk1 - Index Cond: (unique1 = ANY ('{1,42,7}'::integer[])) -(2 rows) + QUERY PLAN +------------------------------------------------------------------- + Sort + Sort Key: unique1 + -> Bitmap Heap Scan on tenk1 + Recheck Cond: (unique1 = ANY ('{1,42,7}'::integer[])) + -> Bitmap Index Scan on tenk1_unique1 + Index Cond: (unique1 = ANY ('{1,42,7}'::integer[])) +(6 rows) SELECT unique1 FROM tenk1 WHERE unique1 IN (1,42,7) @@ -1936,12 +1940,13 @@ SELECT thousand, tenthous FROM tenk1 WHERE thousand < 2 AND tenthous IN (1001,3000) ORDER BY thousand; - QUERY PLAN -------------------------------------------------------- - Index Only Scan using tenk1_thous_tenthous on tenk1 - Index Cond: (thousand < 2) - Filter: (tenthous = ANY ('{1001,3000}'::integer[])) -(3 rows) + QUERY PLAN +-------------------------------------------------------------------------------------- + Sort + Sort Key: thousand + -> Index Only Scan using tenk1_thous_tenthous on tenk1 + Index Cond: ((thousand < 2) AND (tenthous = ANY ('{1001,3000}'::integer[]))) +(4 rows) SELECT thousand, tenthous FROM tenk1 WHERE thousand < 2 AND tenthous IN (1001,3000) diff -U3 /tmp/cirrus-ci-build/src/test/regress/expected/select_distinct.out /tmp/cirrus-ci-build/build-32/testrun/recovery/027_stream_regress/data/results/select_distinct.out --- /tmp/cirrus-ci-build/src/test/regress/expected/select_distinct.out 2024-04-03 02:34:47.297264461 +0000 +++ /tmp/cirrus-ci-build/build-32/testrun/recovery/027_stream_regress/data/results/select_distinct.out 2024-04-03 02:41:48.192924660 +0000 @@ -262,12 +262,12 @@ -- Ensure we don't do parallel distinct with a parallel unsafe function EXPLAIN (COSTS OFF) SELECT DISTINCT distinct_func(1) FROM tenk1; - QUERY PLAN ----------------------------------------------------------- + QUERY PLAN +-------------------------------------- Unique -> Sort Sort Key: (distinct_func(1)) - -> Index Only Scan using tenk1_hundred on tenk1 + -> Seq Scan on tenk1 (4 rows) -- make the function parallel safe diff -U3 /tmp/cirrus-ci-build/src/test/regress/expected/subselect.out /tmp/cirrus-ci-build/build-32/testrun/recovery/027_stream_regress/data/results/subselect.out --- /tmp/cirrus-ci-build/src/test/regress/expected/subselect.out 2024-04-03 02:34:47.305264461 +0000 +++ /tmp/cirrus-ci-build/build-32/testrun/recovery/027_stream_regress/data/results/subselect.out 2024-04-03 02:41:48.904924106 +0000 @@ -1029,7 +1029,7 @@ -> Seq Scan on tenk1 t Filter: ((ANY (unique2 = (hashed SubPlan 2).col1)) OR (ten < 0)) SubPlan 2 - -> Index Only Scan using tenk1_unique1 on tenk1 k + -> Seq Scan on tenk1 k (5 rows) select count(*) from tenk1 t @@ -1403,7 +1403,7 @@ -> Seq Scan on public.tenk1 b Output: b.unique1, b.unique2, b.two, b.four, b.ten, b.twenty, b.hundred, b.thousand, b.twothousand, b.fivethous, b.tenthous, b.odd, b.even, b.stringu1, b.stringu2, b.string4 SubPlan 1 - -> Index Only Scan using tenk1_unique1 on public.tenk1 a + -> Seq Scan on public.tenk1 a Output: a.unique1 (10 rows) diff -U3 /tmp/cirrus-ci-build/src/test/regress/expected/union.out /tmp/cirrus-ci-build/build-32/testrun/recovery/027_stream_regress/data/results/union.out --- /tmp/cirrus-ci-build/src/test/regress/expected/union.out 2024-04-03 02:34:47.313264461 +0000 +++ /tmp/cirrus-ci-build/build-32/testrun/recovery/027_stream_regress/data/results/union.out 2024-04-03 02:41:48.324924556 +0000 @@ -350,13 +350,13 @@ explain (costs off) select count(*) from ( select unique1 from tenk1 union select fivethous from tenk1 ) ss; - QUERY PLAN ----------------------------------------------------------------- + QUERY PLAN +--------------------------------------------- Aggregate -> HashAggregate Group Key: tenk1.unique1 -> Append - -> Index Only Scan using tenk1_unique1 on tenk1 + -> Seq Scan on tenk1 -> Seq Scan on tenk1 tenk1_1 (6 rows) @@ -370,8 +370,8 @@ explain (costs off) select count(*) from ( select unique1 from tenk1 intersect select fivethous from tenk1 ) ss; - QUERY PLAN ------------------------------------------------------------------------------------- + QUERY PLAN +--------------------------------------------------------- Aggregate -> Subquery Scan on ss -> HashSetOp Intersect @@ -379,7 +379,7 @@ -> Subquery Scan on "*SELECT* 2" -> Seq Scan on tenk1 -> Subquery Scan on "*SELECT* 1" - -> Index Only Scan using tenk1_unique1 on tenk1 tenk1_1 + -> Seq Scan on tenk1 tenk1_1 (8 rows) select count(*) from @@ -391,14 +391,14 @@ explain (costs off) select unique1 from tenk1 except select unique2 from tenk1 where unique2 != 10; - QUERY PLAN ------------------------------------------------------------------------- + QUERY PLAN +--------------------------------------------- HashSetOp Except -> Append -> Subquery Scan on "*SELECT* 1" - -> Index Only Scan using tenk1_unique1 on tenk1 + -> Seq Scan on tenk1 -> Subquery Scan on "*SELECT* 2" - -> Index Only Scan using tenk1_unique2 on tenk1 tenk1_1 + -> Seq Scan on tenk1 tenk1_1 Filter: (unique2 <> 10) (7 rows) @@ -412,17 +412,19 @@ explain (costs off) select count(*) from ( select unique1 from tenk1 union select fivethous from tenk1 ) ss; - QUERY PLAN ----------------------------------------------------------------- + QUERY PLAN +--------------------------------------------------- Aggregate -> Unique -> Merge Append Sort Key: tenk1.unique1 - -> Index Only Scan using tenk1_unique1 on tenk1 + -> Sort + Sort Key: tenk1.unique1 + -> Seq Scan on tenk1 -> Sort Sort Key: tenk1_1.fivethous -> Seq Scan on tenk1 tenk1_1 -(8 rows) +(10 rows) select count(*) from ( select unique1 from tenk1 union select fivethous from tenk1 ) ss; @@ -434,8 +436,8 @@ explain (costs off) select count(*) from ( select unique1 from tenk1 intersect select fivethous from tenk1 ) ss; - QUERY PLAN ------------------------------------------------------------------------------------------- + QUERY PLAN +--------------------------------------------------------------- Aggregate -> Subquery Scan on ss -> SetOp Intersect @@ -445,7 +447,7 @@ -> Subquery Scan on "*SELECT* 2" -> Seq Scan on tenk1 -> Subquery Scan on "*SELECT* 1" - -> Index Only Scan using tenk1_unique1 on tenk1 tenk1_1 + -> Seq Scan on tenk1 tenk1_1 (10 rows) select count(*) from @@ -457,16 +459,16 @@ explain (costs off) select unique1 from tenk1 except select unique2 from tenk1 where unique2 != 10; - QUERY PLAN ------------------------------------------------------------------------------- + QUERY PLAN +--------------------------------------------------- SetOp Except -> Sort Sort Key: "*SELECT* 1".unique1 -> Append -> Subquery Scan on "*SELECT* 1" - -> Index Only Scan using tenk1_unique1 on tenk1 + -> Seq Scan on tenk1 -> Subquery Scan on "*SELECT* 2" - -> Index Only Scan using tenk1_unique2 on tenk1 tenk1_1 + -> Seq Scan on tenk1 tenk1_1 Filter: (unique2 <> 10) (9 rows) diff -U3 /tmp/cirrus-ci-build/src/test/regress/expected/join.out /tmp/cirrus-ci-build/build-32/testrun/recovery/027_stream_regress/data/results/join.out --- /tmp/cirrus-ci-build/src/test/regress/expected/join.out 2024-04-03 02:34:47.265264463 +0000 +++ /tmp/cirrus-ci-build/build-32/testrun/recovery/027_stream_regress/data/results/join.out 2024-04-03 02:41:51.248922285 +0000 @@ -2780,20 +2780,22 @@ from tenk1 x inner join tenk1 y on x.thousand = y.thousand group by x.thousand, x.twothousand order by x.thousand desc, x.twothousand; - QUERY PLAN ----------------------------------------------------------------------------------- + QUERY PLAN +----------------------------------------------------- GroupAggregate Group Key: x.thousand, x.twothousand -> Incremental Sort Sort Key: x.thousand DESC, x.twothousand Presorted Key: x.thousand -> Merge Join - Merge Cond: (y.thousand = x.thousand) - -> Index Only Scan Backward using tenk1_thous_tenthous on tenk1 y + Merge Cond: (x.thousand = y.thousand) -> Sort Sort Key: x.thousand DESC -> Seq Scan on tenk1 x -(11 rows) + -> Sort + Sort Key: y.thousand DESC + -> Seq Scan on tenk1 y +(13 rows) reset enable_hashagg; reset enable_nestloop; @@ -2934,12 +2936,12 @@ explain (costs off) select count(*) from tenk1 a, tenk1 b where a.hundred = b.thousand and (b.fivethous % 10) < 10; - QUERY PLAN ------------------------------------------------------------- + QUERY PLAN +----------------------------------------------------- Aggregate -> Hash Join Hash Cond: (a.hundred = b.thousand) - -> Index Only Scan using tenk1_hundred on tenk1 a + -> Seq Scan on tenk1 a -> Hash -> Seq Scan on tenk1 b Filter: ((fivethous % 10) < 10) @@ -3015,13 +3017,13 @@ explain (costs off) select a.* from tenk1 a where unique1 in (select unique2 from tenk1 b); - QUERY PLAN ------------------------------------------------------------- + QUERY PLAN +-------------------------------------- Hash Semi Join Hash Cond: (a.unique1 = b.unique2) -> Seq Scan on tenk1 a -> Hash - -> Index Only Scan using tenk1_unique2 on tenk1 b + -> Seq Scan on tenk1 b (5 rows) -- sadly, this is not an antijoin @@ -3033,43 +3035,43 @@ Seq Scan on tenk1 a Filter: (NOT (ANY (unique1 = (hashed SubPlan 1).col1))) SubPlan 1 - -> Index Only Scan using tenk1_unique2 on tenk1 b + -> Seq Scan on tenk1 b (4 rows) explain (costs off) select a.* from tenk1 a where exists (select 1 from tenk1 b where a.unique1 = b.unique2); - QUERY PLAN ------------------------------------------------------------- + QUERY PLAN +-------------------------------------- Hash Semi Join Hash Cond: (a.unique1 = b.unique2) -> Seq Scan on tenk1 a -> Hash - -> Index Only Scan using tenk1_unique2 on tenk1 b + -> Seq Scan on tenk1 b (5 rows) explain (costs off) select a.* from tenk1 a where not exists (select 1 from tenk1 b where a.unique1 = b.unique2); - QUERY PLAN ------------------------------------------------------------- + QUERY PLAN +-------------------------------------- Hash Anti Join Hash Cond: (a.unique1 = b.unique2) -> Seq Scan on tenk1 a -> Hash - -> Index Only Scan using tenk1_unique2 on tenk1 b + -> Seq Scan on tenk1 b (5 rows) explain (costs off) select a.* from tenk1 a left join tenk1 b on a.unique1 = b.unique2 where b.unique2 is null; - QUERY PLAN ------------------------------------------------------------- + QUERY PLAN +-------------------------------------- Hash Anti Join Hash Cond: (a.unique1 = b.unique2) -> Seq Scan on tenk1 a -> Hash - -> Index Only Scan using tenk1_unique2 on tenk1 b + -> Seq Scan on tenk1 b (5 rows) -- @@ -4029,11 +4031,11 @@ explain (costs off) select unique1, x from tenk1 left join f_immutable_int4(1) x on unique1 = x; - QUERY PLAN ----------------------------------------------------- + QUERY PLAN +------------------------------------ Nested Loop Left Join Join Filter: (tenk1.unique1 = 1) - -> Index Only Scan using tenk1_unique1 on tenk1 + -> Seq Scan on tenk1 -> Materialize -> Result (5 rows) @@ -4050,15 +4052,14 @@ explain (costs off) select unique1, x from tenk1 full join f_immutable_int4(1) x on unique1 = x; - QUERY PLAN ----------------------------------------------------- - Merge Full Join - Merge Cond: (tenk1.unique1 = (1)) - -> Index Only Scan using tenk1_unique1 on tenk1 - -> Sort - Sort Key: (1) + QUERY PLAN +------------------------------------ + Hash Full Join + Hash Cond: (tenk1.unique1 = (1)) + -> Seq Scan on tenk1 + -> Hash -> Result -(6 rows) +(5 rows) -- check that pullup of a const function allows further const-folding explain (costs off) @@ -4141,15 +4142,15 @@ select (t2.*).unique1, f_field_select(t2) from tenk1 t1 left join onek t2 on t1.unique1 = t2.unique1 left join int8_tbl t3 on true; - QUERY PLAN --------------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Nested Loop Left Join Output: t2.unique1, t2.unique2 -> Hash Left Join Output: t2.unique1, t2.unique2 Hash Cond: (t1.unique1 = t2.unique1) - -> Index Only Scan using tenk1_unique1 on public.tenk1 t1 - Output: t1.unique1 + -> Seq Scan on public.tenk1 t1 + Output: t1.unique1, t1.unique2, t1.two, t1.four, t1.ten, t1.twenty, t1.hundred, t1.thousand, t1.twothousand, t1.fivethous, t1.tenthous, t1.odd, t1.even, t1.stringu1, t1.stringu2, t1.string4 -> Hash Output: t2.unique1, t2.unique2 -> Seq Scan on public.onek t2 @@ -5288,7 +5289,7 @@ -> Seq Scan on int8_tbl c Filter: (q1 < b.unique1) -> Hash - -> Index Only Scan using onek_unique1 on onek a + -> Seq Scan on onek a (9 rows) select a.unique1, b.unique2 @@ -7412,14 +7413,15 @@ explain (costs off) select count(*) from tenk1 a, tenk1 b join lateral (values(a.unique1)) ss(x) on b.unique2 = ss.x; - QUERY PLAN ------------------------------------------------------------- + QUERY PLAN +-------------------------------------------- Aggregate - -> Merge Join - Merge Cond: (a.unique1 = b.unique2) - -> Index Only Scan using tenk1_unique1 on tenk1 a - -> Index Only Scan using tenk1_unique2 on tenk1 b -(5 rows) + -> Hash Join + Hash Cond: (a.unique1 = b.unique2) + -> Seq Scan on tenk1 a + -> Hash + -> Seq Scan on tenk1 b +(6 rows) select count(*) from tenk1 a, tenk1 b join lateral (values(a.unique1)) ss(x) on b.unique2 = ss.x; @@ -7437,7 +7439,7 @@ Aggregate -> Nested Loop -> Nested Loop - -> Index Only Scan using tenk1_unique1 on tenk1 a + -> Seq Scan on tenk1 a -> Values Scan on "*VALUES*" -> Memoize Cache Key: "*VALUES*".column1 @@ -8907,8 +8909,8 @@ where exists (select 1 from tenk1 t3 where t3.thousand = t1.unique1 and t3.tenthous = t2.hundred) and t1.unique1 < 1; - QUERY PLAN ---------------------------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Nested Loop Output: t1.unique1, t2.hundred -> Hash Join @@ -8917,8 +8919,8 @@ -> HashAggregate Output: t3.thousand, t3.tenthous Group Key: t3.thousand, t3.tenthous - -> Index Only Scan using tenk1_thous_tenthous on public.tenk1 t3 - Output: t3.thousand, t3.tenthous + -> Seq Scan on public.tenk1 t3 + Output: t3.unique1, t3.unique2, t3.two, t3.four, t3.ten, t3.twenty, t3.hundred, t3.thousand, t3.twothousand, t3.fivethous, t3.tenthous, t3.odd, t3.even, t3.stringu1, t3.stringu2, t3.string4 -> Hash Output: t1.unique1 -> Index Only Scan using onek_unique1 on public.onek t1 diff -U3 /tmp/cirrus-ci-build/src/test/regress/expected/aggregates.out /tmp/cirrus-ci-build/build-32/testrun/recovery/027_stream_regress/data/results/aggregates.out --- /tmp/cirrus-ci-build/src/test/regress/expected/aggregates.out 2024-04-03 02:34:47.225264465 +0000 +++ /tmp/cirrus-ci-build/build-32/testrun/recovery/027_stream_regress/data/results/aggregates.out 2024-04-03 02:41:51.108922394 +0000 @@ -2942,13 +2942,13 @@ SET LOCAL parallel_setup_cost=0; SET LOCAL max_parallel_workers_per_gather=4; EXPLAIN (COSTS OFF) SELECT balk(hundred) FROM tenk1; - QUERY PLAN -------------------------------------------------------------------------- + QUERY PLAN +---------------------------------------------- Finalize Aggregate -> Gather Workers Planned: 4 -> Partial Aggregate - -> Parallel Index Only Scan using tenk1_hundred on tenk1 + -> Parallel Seq Scan on tenk1 (5 rows) SELECT balk(hundred) FROM tenk1; diff -U3 /tmp/cirrus-ci-build/src/test/regress/expected/select_parallel.out /tmp/cirrus-ci-build/build-32/testrun/recovery/027_stream_regress/data/results/select_parallel.out --- /tmp/cirrus-ci-build/src/test/regress/expected/select_parallel.out 2024-04-03 02:34:47.297264461 +0000 +++ /tmp/cirrus-ci-build/build-32/testrun/recovery/027_stream_regress/data/results/select_parallel.out 2024-04-03 02:42:06.348910556 +0000 @@ -257,13 +257,13 @@ explain (costs off) select sum(sp_parallel_restricted(unique1)) from tenk1 group by(sp_parallel_restricted(unique1)); - QUERY PLAN -------------------------------------------------------------------- + QUERY PLAN +---------------------------------------------- HashAggregate Group Key: sp_parallel_restricted(unique1) -> Gather Workers Planned: 4 - -> Parallel Index Only Scan using tenk1_unique1 on tenk1 + -> Parallel Seq Scan on tenk1 (5 rows) -- test prepared statement @@ -608,17 +608,21 @@ set enable_nestloop to off; explain (costs off) select count(*) from tenk1, tenk2 where tenk1.unique1 = tenk2.unique1; - QUERY PLAN -------------------------------------------------------------------------------- + QUERY PLAN +----------------------------------------------------------------- Finalize Aggregate -> Gather Workers Planned: 4 -> Partial Aggregate -> Merge Join Merge Cond: (tenk1.unique1 = tenk2.unique1) - -> Parallel Index Only Scan using tenk1_unique1 on tenk1 - -> Index Only Scan using tenk2_unique1 on tenk2 -(8 rows) + -> Sort + Sort Key: tenk1.unique1 + -> Parallel Seq Scan on tenk1 + -> Sort + Sort Key: tenk2.unique1 + -> Seq Scan on tenk2 +(12 rows) select count(*) from tenk1, tenk2 where tenk1.unique1 = tenk2.unique1; count @@ -847,13 +851,13 @@ -- check parallelized int8 aggregate (bug #14897) explain (costs off) select avg(unique1::int8) from tenk1; - QUERY PLAN -------------------------------------------------------------------------- + QUERY PLAN +---------------------------------------------- Finalize Aggregate -> Gather Workers Planned: 4 -> Partial Aggregate - -> Parallel Index Only Scan using tenk1_unique1 on tenk1 + -> Parallel Seq Scan on tenk1 (5 rows) select avg(unique1::int8) from tenk1; @@ -1051,8 +1055,8 @@ explain (costs off, verbose) select count(*) from tenk1 a where (unique1, two) in (select unique1, row_number() over() from tenk1 b); - QUERY PLAN ----------------------------------------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------------------------ Aggregate Output: count(*) -> Hash Semi Join @@ -1069,7 +1073,7 @@ -> Gather Output: b.unique1 Workers Planned: 4 - -> Parallel Index Only Scan using tenk1_unique1 on public.tenk1 b + -> Parallel Seq Scan on public.tenk1 b Output: b.unique1 (18 rows) @@ -1204,8 +1208,8 @@ SELECT unnest(ARRAY[]::integer[]) + 1 AS pathkey FROM tenk1 t1 JOIN tenk1 t2 ON TRUE ORDER BY pathkey; - QUERY PLAN ------------------------------------------------------------------------------------------------------ + QUERY PLAN +-------------------------------------------------------------------------- Sort Output: (((unnest('{}'::integer[])) + 1)) Sort Key: (((unnest('{}'::integer[])) + 1)) @@ -1216,11 +1220,11 @@ -> Nested Loop -> Gather Workers Planned: 4 - -> Parallel Index Only Scan using tenk1_hundred on public.tenk1 t1 + -> Parallel Seq Scan on public.tenk1 t1 -> Materialize -> Gather Workers Planned: 4 - -> Parallel Index Only Scan using tenk1_hundred on public.tenk1 t2 + -> Parallel Seq Scan on public.tenk1 t2 (15 rows) -- test passing expanded-value representations to workers @@ -1255,13 +1259,13 @@ EXPLAIN (COSTS OFF) SELECT 1 FROM tenk1_vw_sec WHERE (SELECT sum(f1) FROM int4_tbl WHERE f1 < unique1) < 100; - QUERY PLAN -------------------------------------------------------------------- + QUERY PLAN +----------------------------------------------------- Subquery Scan on tenk1_vw_sec Filter: ((SubPlan 1) < 100) -> Gather Workers Planned: 4 - -> Parallel Index Only Scan using tenk1_unique1 on tenk1 + -> Parallel Seq Scan on tenk1 SubPlan 1 -> Aggregate -> Seq Scan on int4_tbl diff -U3 /tmp/cirrus-ci-build/src/test/regress/expected/window.out /tmp/cirrus-ci-build/build-32/testrun/recovery/027_stream_regress/data/results/window.out --- /tmp/cirrus-ci-build/src/test/regress/expected/window.out 2024-04-03 02:34:47.317264461 +0000 +++ /tmp/cirrus-ci-build/build-32/testrun/recovery/027_stream_regress/data/results/window.out 2024-04-03 02:42:09.120908403 +0000 @@ -5285,13 +5285,13 @@ FROM tenk1 t1 INNER JOIN tenk1 t2 ON t1.unique1 = t2.tenthous WHERE t2.two = 1 LIMIT 1; - QUERY PLAN -------------------------------------------------------------------- + QUERY PLAN +----------------------------------------------------- Limit -> WindowAgg -> Hash Join Hash Cond: (t1.unique1 = t2.tenthous) - -> Index Only Scan using tenk1_unique1 on tenk1 t1 + -> Seq Scan on tenk1 t1 -> Hash -> Seq Scan on tenk1 t2 Filter: (two = 1) @@ -5303,17 +5303,18 @@ SELECT COUNT(*) OVER (ORDER BY t1.unique1 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) FROM tenk1 t1 INNER JOIN tenk1 t2 ON t1.unique1 = t2.tenthous LIMIT 1; - QUERY PLAN --------------------------------------------------------------------------------- + QUERY PLAN +----------------------------------------------------------- Limit -> WindowAgg - -> Merge Join - Merge Cond: (t1.unique1 = t2.tenthous) - -> Index Only Scan using tenk1_unique1 on tenk1 t1 - -> Sort - Sort Key: t2.tenthous - -> Index Only Scan using tenk1_thous_tenthous on tenk1 t2 -(8 rows) + -> Sort + Sort Key: t1.unique1 + -> Hash Join + Hash Cond: (t1.unique1 = t2.tenthous) + -> Seq Scan on tenk1 t1 + -> Hash + -> Seq Scan on tenk1 t2 +(9 rows) -- Ensure we get a cheap total plan. This time use 10000 FOLLOWING so we need -- to read all join rows. @@ -5321,17 +5322,18 @@ SELECT COUNT(*) OVER (ORDER BY t1.unique1 ROWS BETWEEN UNBOUNDED PRECEDING AND 10000 FOLLOWING) FROM tenk1 t1 INNER JOIN tenk1 t2 ON t1.unique1 = t2.tenthous LIMIT 1; - QUERY PLAN --------------------------------------------------------------------------------- + QUERY PLAN +----------------------------------------------------------- Limit -> WindowAgg - -> Merge Join - Merge Cond: (t1.unique1 = t2.tenthous) - -> Index Only Scan using tenk1_unique1 on tenk1 t1 - -> Sort - Sort Key: t2.tenthous - -> Index Only Scan using tenk1_thous_tenthous on tenk1 t2 -(8 rows) + -> Sort + Sort Key: t1.unique1 + -> Hash Join + Hash Cond: (t1.unique1 = t2.tenthous) + -> Seq Scan on tenk1 t1 + -> Hash + -> Seq Scan on tenk1 t2 +(9 rows) -- Tests for problems with failure to walk or mutate expressions -- within window frame clauses. diff -U3 /tmp/cirrus-ci-build/src/test/regress/expected/rowtypes.out /tmp/cirrus-ci-build/build-32/testrun/recovery/027_stream_regress/data/results/rowtypes.out --- /tmp/cirrus-ci-build/src/test/regress/expected/rowtypes.out 2024-04-03 02:34:47.297264461 +0000 +++ /tmp/cirrus-ci-build/build-32/testrun/recovery/027_stream_regress/data/results/rowtypes.out 2024-04-03 02:42:12.796905405 +0000 @@ -302,11 +302,15 @@ select thousand, tenthous from tenk1 where (thousand, tenthous) >= (997, 5000) order by thousand, tenthous; - QUERY PLAN ------------------------------------------------------------ - Index Only Scan using tenk1_thous_tenthous on tenk1 - Index Cond: (ROW(thousand, tenthous) >= ROW(997, 5000)) -(2 rows) + QUERY PLAN +----------------------------------------------------------------------- + Sort + Sort Key: thousand, tenthous + -> Bitmap Heap Scan on tenk1 + Recheck Cond: (ROW(thousand, tenthous) >= ROW(997, 5000)) + -> Bitmap Index Scan on tenk1_thous_tenthous + Index Cond: (ROW(thousand, tenthous) >= ROW(997, 5000)) +(6 rows) select thousand, tenthous from tenk1 where (thousand, tenthous) >= (997, 5000) @@ -380,11 +384,15 @@ select thousand, tenthous from tenk1 where (998, 5000) < (thousand, tenthous) order by thousand, tenthous; - QUERY PLAN ----------------------------------------------------------- - Index Only Scan using tenk1_thous_tenthous on tenk1 - Index Cond: (ROW(thousand, tenthous) > ROW(998, 5000)) -(2 rows) + QUERY PLAN +---------------------------------------------------------------------- + Sort + Sort Key: thousand, tenthous + -> Bitmap Heap Scan on tenk1 + Filter: (ROW(998, 5000) < ROW(thousand, tenthous)) + -> Bitmap Index Scan on tenk1_thous_tenthous + Index Cond: (ROW(thousand, tenthous) > ROW(998, 5000)) +(6 rows) select thousand, tenthous from tenk1 where (998, 5000) < (thousand, tenthous) @@ -1132,20 +1140,22 @@ select row_to_json(q) from (select thousand, tenthous from tenk1 where thousand = 42 and tenthous < 2000 offset 0) q; - QUERY PLAN -------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------- Subquery Scan on q - -> Index Only Scan using tenk1_thous_tenthous on tenk1 - Index Cond: ((thousand = 42) AND (tenthous < 2000)) -(3 rows) + -> Bitmap Heap Scan on tenk1 + Recheck Cond: ((thousand = 42) AND (tenthous < 2000)) + -> Bitmap Index Scan on tenk1_thous_tenthous + Index Cond: ((thousand = 42) AND (tenthous < 2000)) +(5 rows) select row_to_json(q) from (select thousand, tenthous from tenk1 where thousand = 42 and tenthous < 2000 offset 0) q; row_to_json --------------------------------- - {"thousand":42,"tenthous":42} {"thousand":42,"tenthous":1042} + {"thousand":42,"tenthous":42} (2 rows) select row_to_json(q) from @@ -1153,8 +1163,8 @@ where thousand = 42 and tenthous < 2000 offset 0) q; row_to_json ------------------- - {"x":42,"y":42} {"x":42,"y":1042} + {"x":42,"y":42} (2 rows) select row_to_json(q) from @@ -1162,8 +1172,8 @@ where thousand = 42 and tenthous < 2000 offset 0) q(a,b); row_to_json ------------------- - {"a":42,"b":42} {"a":42,"b":1042} + {"a":42,"b":42} (2 rows) create temp table tt1 as select * from int8_tbl limit 2; diff -U3 /tmp/cirrus-ci-build/src/test/regress/expected/with.out /tmp/cirrus-ci-build/build-32/testrun/recovery/027_stream_regress/data/results/with.out --- /tmp/cirrus-ci-build/src/test/regress/expected/with.out 2024-04-03 02:34:47.317264461 +0000 +++ /tmp/cirrus-ci-build/build-32/testrun/recovery/027_stream_regress/data/results/with.out 2024-04-03 02:42:12.988905218 +0000 @@ -642,14 +642,14 @@ with x as materialized (select unique1 from tenk1 b) select count(*) from tenk1 a where unique1 in (select * from x); - QUERY PLAN ------------------------------------------------------------- + QUERY PLAN +-------------------------------------------- Aggregate CTE x - -> Index Only Scan using tenk1_unique1 on tenk1 b + -> Seq Scan on tenk1 b -> Hash Semi Join Hash Cond: (a.unique1 = x.unique1) - -> Index Only Scan using tenk1_unique1 on tenk1 a + -> Seq Scan on tenk1 a -> Hash -> CTE Scan on x (8 rows) @@ -678,16 +678,19 @@ with x as materialized (select unique1 from tenk1 b order by unique1) select count(*) from tenk1 a where unique1 in (select * from x); - QUERY PLAN ------------------------------------------------------------- + QUERY PLAN +-------------------------------------------- Aggregate CTE x - -> Index Only Scan using tenk1_unique1 on tenk1 b - -> Merge Semi Join - Merge Cond: (a.unique1 = x.unique1) - -> Index Only Scan using tenk1_unique1 on tenk1 a - -> CTE Scan on x -(7 rows) + -> Sort + Sort Key: b.unique1 + -> Seq Scan on tenk1 b + -> Hash Semi Join + Hash Cond: (a.unique1 = x.unique1) + -> Seq Scan on tenk1 a + -> Hash + -> CTE Scan on x +(10 rows) -- SEARCH clause create temp table graph0( f int, t int, label text );