diff options
Diffstat (limited to 'src/test')
| -rw-r--r-- | src/test/regress/expected/box.out | 14 | ||||
| -rw-r--r-- | src/test/regress/expected/create_index_spgist.out | 42 | ||||
| -rw-r--r-- | src/test/regress/expected/explain.out | 45 | ||||
| -rw-r--r-- | src/test/regress/expected/generated_virtual.out | 3 | ||||
| -rw-r--r-- | src/test/regress/expected/groupingsets.out | 12 | ||||
| -rw-r--r-- | src/test/regress/expected/partition_prune.out | 4 | ||||
| -rw-r--r-- | src/test/regress/expected/polygon.out | 3 | ||||
| -rw-r--r-- | src/test/regress/expected/select_parallel.out | 7 | ||||
| -rw-r--r-- | src/test/regress/expected/sqljson.out | 18 | ||||
| -rw-r--r-- | src/test/regress/expected/window.out | 308 | ||||
| -rw-r--r-- | src/test/regress/sql/explain.sql | 5 |
11 files changed, 286 insertions, 175 deletions
diff --git a/src/test/regress/expected/box.out b/src/test/regress/expected/box.out index 8c9e9e39355..10760870ce7 100644 --- a/src/test/regress/expected/box.out +++ b/src/test/regress/expected/box.out @@ -594,12 +594,13 @@ SET enable_bitmapscan = OFF; EXPLAIN (COSTS OFF) SELECT rank() OVER (ORDER BY b <-> point '123,456') n, b <-> point '123,456' dist, id FROM quad_box_tbl; - QUERY PLAN ---------------------------------------------------------- + QUERY PLAN +-------------------------------------------------------------------------------- WindowAgg + Window: w1 AS (ORDER BY (b <-> '(123,456)'::point) ROWS UNBOUNDED PRECEDING) -> Index Scan using quad_box_tbl_idx on quad_box_tbl Order By: (b <-> '(123,456)'::point) -(3 rows) +(4 rows) CREATE TEMP TABLE quad_box_tbl_ord_idx1 AS SELECT rank() OVER (ORDER BY b <-> point '123,456') n, b <-> point '123,456' dist, id @@ -616,13 +617,14 @@ WHERE seq.id IS NULL OR idx.id IS NULL; EXPLAIN (COSTS OFF) SELECT rank() OVER (ORDER BY b <-> point '123,456') n, b <-> point '123,456' dist, id FROM quad_box_tbl WHERE b <@ box '((200,300),(500,600))'; - QUERY PLAN ---------------------------------------------------------- + QUERY PLAN +-------------------------------------------------------------------------------- WindowAgg + Window: w1 AS (ORDER BY (b <-> '(123,456)'::point) ROWS UNBOUNDED PRECEDING) -> Index Scan using quad_box_tbl_idx on quad_box_tbl Index Cond: (b <@ '(500,600),(200,300)'::box) Order By: (b <-> '(123,456)'::point) -(4 rows) +(5 rows) CREATE TEMP TABLE quad_box_tbl_ord_idx2 AS SELECT rank() OVER (ORDER BY b <-> point '123,456') n, b <-> point '123,456' dist, id diff --git a/src/test/regress/expected/create_index_spgist.out b/src/test/regress/expected/create_index_spgist.out index 5c04df9c01b..c6beb0efaff 100644 --- a/src/test/regress/expected/create_index_spgist.out +++ b/src/test/regress/expected/create_index_spgist.out @@ -329,12 +329,13 @@ SELECT count(*) FROM quad_point_tbl WHERE p ~= '(4585, 365)'; EXPLAIN (COSTS OFF) SELECT row_number() OVER (ORDER BY p <-> '0,0') n, p <-> '0,0' dist, p FROM quad_point_tbl; - QUERY PLAN ------------------------------------------------------------ + QUERY PLAN +---------------------------------------------------------------------------- WindowAgg + Window: w1 AS (ORDER BY (p <-> '(0,0)'::point) ROWS UNBOUNDED PRECEDING) -> Index Only Scan using sp_quad_ind on quad_point_tbl Order By: (p <-> '(0,0)'::point) -(3 rows) +(4 rows) CREATE TEMP TABLE quad_point_tbl_ord_idx1 AS SELECT row_number() OVER (ORDER BY p <-> '0,0') n, p <-> '0,0' dist, p @@ -349,13 +350,14 @@ WHERE seq.dist IS DISTINCT FROM idx.dist; EXPLAIN (COSTS OFF) SELECT row_number() OVER (ORDER BY p <-> '0,0') n, p <-> '0,0' dist, p FROM quad_point_tbl WHERE p <@ box '(200,200,1000,1000)'; - QUERY PLAN ------------------------------------------------------------ + QUERY PLAN +---------------------------------------------------------------------------- WindowAgg + Window: w1 AS (ORDER BY (p <-> '(0,0)'::point) ROWS UNBOUNDED PRECEDING) -> Index Only Scan using sp_quad_ind on quad_point_tbl Index Cond: (p <@ '(1000,1000),(200,200)'::box) Order By: (p <-> '(0,0)'::point) -(4 rows) +(5 rows) CREATE TEMP TABLE quad_point_tbl_ord_idx2 AS SELECT row_number() OVER (ORDER BY p <-> '0,0') n, p <-> '0,0' dist, p @@ -370,13 +372,14 @@ WHERE seq.dist IS DISTINCT FROM idx.dist; EXPLAIN (COSTS OFF) SELECT row_number() OVER (ORDER BY p <-> '333,400') n, p <-> '333,400' dist, p FROM quad_point_tbl WHERE p IS NOT NULL; - QUERY PLAN ------------------------------------------------------------ + QUERY PLAN +-------------------------------------------------------------------------------- WindowAgg + Window: w1 AS (ORDER BY (p <-> '(333,400)'::point) ROWS UNBOUNDED PRECEDING) -> Index Only Scan using sp_quad_ind on quad_point_tbl Index Cond: (p IS NOT NULL) Order By: (p <-> '(333,400)'::point) -(4 rows) +(5 rows) CREATE TEMP TABLE quad_point_tbl_ord_idx3 AS SELECT row_number() OVER (ORDER BY p <-> '333,400') n, p <-> '333,400' dist, p @@ -496,12 +499,13 @@ SELECT count(*) FROM kd_point_tbl WHERE p ~= '(4585, 365)'; EXPLAIN (COSTS OFF) SELECT row_number() OVER (ORDER BY p <-> '0,0') n, p <-> '0,0' dist, p FROM kd_point_tbl; - QUERY PLAN -------------------------------------------------------- + QUERY PLAN +---------------------------------------------------------------------------- WindowAgg + Window: w1 AS (ORDER BY (p <-> '(0,0)'::point) ROWS UNBOUNDED PRECEDING) -> Index Only Scan using sp_kd_ind on kd_point_tbl Order By: (p <-> '(0,0)'::point) -(3 rows) +(4 rows) CREATE TEMP TABLE kd_point_tbl_ord_idx1 AS SELECT row_number() OVER (ORDER BY p <-> '0,0') n, p <-> '0,0' dist, p @@ -516,13 +520,14 @@ WHERE seq.dist IS DISTINCT FROM idx.dist; EXPLAIN (COSTS OFF) SELECT row_number() OVER (ORDER BY p <-> '0,0') n, p <-> '0,0' dist, p FROM kd_point_tbl WHERE p <@ box '(200,200,1000,1000)'; - QUERY PLAN ---------------------------------------------------------- + QUERY PLAN +---------------------------------------------------------------------------- WindowAgg + Window: w1 AS (ORDER BY (p <-> '(0,0)'::point) ROWS UNBOUNDED PRECEDING) -> Index Only Scan using sp_kd_ind on kd_point_tbl Index Cond: (p <@ '(1000,1000),(200,200)'::box) Order By: (p <-> '(0,0)'::point) -(4 rows) +(5 rows) CREATE TEMP TABLE kd_point_tbl_ord_idx2 AS SELECT row_number() OVER (ORDER BY p <-> '0,0') n, p <-> '0,0' dist, p @@ -537,13 +542,14 @@ WHERE seq.dist IS DISTINCT FROM idx.dist; EXPLAIN (COSTS OFF) SELECT row_number() OVER (ORDER BY p <-> '333,400') n, p <-> '333,400' dist, p FROM kd_point_tbl WHERE p IS NOT NULL; - QUERY PLAN -------------------------------------------------------- + QUERY PLAN +-------------------------------------------------------------------------------- WindowAgg + Window: w1 AS (ORDER BY (p <-> '(333,400)'::point) ROWS UNBOUNDED PRECEDING) -> Index Only Scan using sp_kd_ind on kd_point_tbl Index Cond: (p IS NOT NULL) Order By: (p <-> '(333,400)'::point) -(4 rows) +(5 rows) CREATE TEMP TABLE kd_point_tbl_ord_idx3 AS SELECT row_number() OVER (ORDER BY p <-> '333,400') n, p <-> '333,400' dist, p diff --git a/src/test/regress/expected/explain.out b/src/test/regress/expected/explain.out index f5d60e50893..340747a8f75 100644 --- a/src/test/regress/expected/explain.out +++ b/src/test/regress/expected/explain.out @@ -243,6 +243,42 @@ select explain_filter('explain (buffers, format json) select * from int8_tbl i8' ] (1 row) +-- Check expansion of window definitions +select explain_filter('explain verbose select sum(unique1) over w, sum(unique2) over (w order by hundred), sum(tenthous) over (w order by hundred) from tenk1 window w as (partition by ten)'); + explain_filter +------------------------------------------------------------------------------------------------------- + WindowAgg (cost=N.N..N.N rows=N width=N) + Output: sum(unique1) OVER w, (sum(unique2) OVER w1), (sum(tenthous) OVER w1), ten, hundred + Window: w AS (PARTITION BY tenk1.ten) + -> WindowAgg (cost=N.N..N.N rows=N width=N) + Output: ten, hundred, unique1, unique2, tenthous, sum(unique2) OVER w1, sum(tenthous) OVER w1 + Window: w1 AS (PARTITION BY tenk1.ten ORDER BY tenk1.hundred) + -> Sort (cost=N.N..N.N rows=N width=N) + Output: ten, hundred, unique1, unique2, tenthous + Sort Key: tenk1.ten, tenk1.hundred + -> Seq Scan on public.tenk1 (cost=N.N..N.N rows=N width=N) + Output: ten, hundred, unique1, unique2, tenthous +(11 rows) + +select explain_filter('explain verbose select sum(unique1) over w1, sum(unique2) over (w1 order by hundred), sum(tenthous) over (w1 order by hundred rows 10 preceding) from tenk1 window w1 as (partition by ten)'); + explain_filter +--------------------------------------------------------------------------------------------------------- + WindowAgg (cost=N.N..N.N rows=N width=N) + Output: sum(unique1) OVER w1, (sum(unique2) OVER w2), (sum(tenthous) OVER w3), ten, hundred + Window: w1 AS (PARTITION BY tenk1.ten) + -> WindowAgg (cost=N.N..N.N rows=N width=N) + Output: ten, hundred, unique1, unique2, tenthous, (sum(unique2) OVER w2), sum(tenthous) OVER w3 + Window: w3 AS (PARTITION BY tenk1.ten ORDER BY tenk1.hundred ROWS 'N'::bigint PRECEDING) + -> WindowAgg (cost=N.N..N.N rows=N width=N) + Output: ten, hundred, unique1, unique2, tenthous, sum(unique2) OVER w2 + Window: w2 AS (PARTITION BY tenk1.ten ORDER BY tenk1.hundred) + -> Sort (cost=N.N..N.N rows=N width=N) + Output: ten, hundred, unique1, unique2, tenthous + Sort Key: tenk1.ten, tenk1.hundred + -> Seq Scan on public.tenk1 (cost=N.N..N.N rows=N width=N) + Output: ten, hundred, unique1, unique2, tenthous +(14 rows) + -- Check output including I/O timings. These fields are conditional -- but always set in JSON format, so check them only in this case. set track_io_timing = on; @@ -742,11 +778,12 @@ select explain_filter('explain (analyze,buffers off,costs off) select sum(n) ove explain_filter ---------------------------------------------------------------------------------- WindowAgg (actual time=N.N..N.N rows=N.N loops=N) + Window: w1 AS () Storage: Memory Maximum Storage: NkB -> Function Scan on generate_series a (actual time=N.N..N.N rows=N.N loops=N) Planning Time: N.N ms Execution Time: N.N ms -(5 rows) +(6 rows) -- Test tuplestore storage usage in Window aggregate (disk case) set work_mem to 64; @@ -754,17 +791,19 @@ select explain_filter('explain (analyze,buffers off,costs off) select sum(n) ove explain_filter ---------------------------------------------------------------------------------- WindowAgg (actual time=N.N..N.N rows=N.N loops=N) + Window: w1 AS () Storage: Disk Maximum Storage: NkB -> Function Scan on generate_series a (actual time=N.N..N.N rows=N.N loops=N) Planning Time: N.N ms Execution Time: N.N ms -(5 rows) +(6 rows) -- Test tuplestore storage usage in Window aggregate (memory and disk case, final result is disk) select explain_filter('explain (analyze,buffers off,costs off) select sum(n) over(partition by m) from (SELECT n < 3 as m, n from generate_series(1,2000) a(n))'); explain_filter ---------------------------------------------------------------------------------------- WindowAgg (actual time=N.N..N.N rows=N.N loops=N) + Window: w1 AS (PARTITION BY ((a.n < N))) Storage: Disk Maximum Storage: NkB -> Sort (actual time=N.N..N.N rows=N.N loops=N) Sort Key: ((a.n < N)) @@ -772,6 +811,6 @@ select explain_filter('explain (analyze,buffers off,costs off) select sum(n) ove -> Function Scan on generate_series a (actual time=N.N..N.N rows=N.N loops=N) Planning Time: N.N ms Execution Time: N.N ms -(8 rows) +(9 rows) reset work_mem; diff --git a/src/test/regress/expected/generated_virtual.out b/src/test/regress/expected/generated_virtual.out index 7ef05f45be7..dc09c85938e 100644 --- a/src/test/regress/expected/generated_virtual.out +++ b/src/test/regress/expected/generated_virtual.out @@ -1427,6 +1427,7 @@ order by t1.a; Sort Sort Key: t1.a -> WindowAgg + Window: w1 AS (PARTITION BY t2.a) -> Sort Sort Key: t2.a -> Nested Loop Left Join @@ -1434,7 +1435,7 @@ order by t1.a; -> Seq Scan on gtest32 t1 -> Materialize -> Seq Scan on gtest32 t2 -(10 rows) +(11 rows) select sum(t2.b) over (partition by t2.a), sum(t2.c) over (partition by t2.a), diff --git a/src/test/regress/expected/groupingsets.out b/src/test/regress/expected/groupingsets.out index d7c9b44605d..449f0384225 100644 --- a/src/test/regress/expected/groupingsets.out +++ b/src/test/regress/expected/groupingsets.out @@ -1436,8 +1436,9 @@ explain (costs off) QUERY PLAN --------------------------------------------- Sort - Sort Key: (sum((sum(c))) OVER (?)), a, b + Sort Key: (sum((sum(c))) OVER w1), a, b -> WindowAgg + Window: w1 AS (ORDER BY a, b) -> Sort Sort Key: a, b -> MixedAggregate @@ -1446,7 +1447,7 @@ explain (costs off) Hash Key: b Group Key: () -> Seq Scan on gstest2 -(11 rows) +(12 rows) select a, b, sum(v.x) from (values (1),(2)) v(x), gstest_data(v.x) @@ -2427,9 +2428,10 @@ explain (costs off) select a, b, row_number() over (order by a, b nulls first) from (values (1, 1), (2, 2)) as t (a, b) where a = b group by grouping sets((a, b), (a)); - QUERY PLAN ----------------------------------------------------------------------- + QUERY PLAN +-------------------------------------------------------------------------------------------- WindowAgg + Window: w1 AS (ORDER BY "*VALUES*".column1, "*VALUES*".column2 ROWS UNBOUNDED PRECEDING) -> Sort Sort Key: "*VALUES*".column1, "*VALUES*".column2 NULLS FIRST -> HashAggregate @@ -2437,7 +2439,7 @@ group by grouping sets((a, b), (a)); Hash Key: "*VALUES*".column1 -> Values Scan on "*VALUES*" Filter: (column1 = column2) -(8 rows) +(9 rows) select a, b, row_number() over (order by a, b nulls first) from (values (1, 1), (2, 2)) as t (a, b) where a = b diff --git a/src/test/regress/expected/partition_prune.out b/src/test/regress/expected/partition_prune.out index 34f2b0b8dbd..8097f4e9282 100644 --- a/src/test/regress/expected/partition_prune.out +++ b/src/test/regress/expected/partition_prune.out @@ -4678,6 +4678,7 @@ select min(a) over (partition by a order by a) from part_abc where a >= stable_o Append -> Subquery Scan on "*SELECT* 1_1" -> WindowAgg + Window: w1 AS (PARTITION BY part_abc.a ORDER BY part_abc.a) -> Append Subplans Removed: 1 -> Index Scan using part_abc_2_a_idx on part_abc_2 part_abc_1 @@ -4694,6 +4695,7 @@ select min(a) over (partition by a order by a) from part_abc where a >= stable_o Filter: (d <= stable_one()) -> Subquery Scan on "*SELECT* 2" -> WindowAgg + Window: w1 AS (PARTITION BY part_abc_5.a ORDER BY part_abc_5.a) -> Append Subplans Removed: 1 -> Index Scan using part_abc_2_a_idx on part_abc_2 part_abc_6 @@ -4708,7 +4710,7 @@ select min(a) over (partition by a order by a) from part_abc where a >= stable_o -> Index Scan using part_abc_3_3_a_idx on part_abc_3_3 part_abc_9 Index Cond: (a >= (stable_one() + 1)) Filter: (d >= stable_one()) -(33 rows) +(35 rows) drop view part_abc_view; drop table part_abc; diff --git a/src/test/regress/expected/polygon.out b/src/test/regress/expected/polygon.out index 7a9778e70fd..c01848f103e 100644 --- a/src/test/regress/expected/polygon.out +++ b/src/test/regress/expected/polygon.out @@ -286,10 +286,11 @@ FROM quad_poly_tbl WHERE p <@ polygon '((300,300),(400,600),(600,500),(700,200)) QUERY PLAN --------------------------------------------------------------------------------- WindowAgg + Window: w1 AS (ORDER BY (p <-> '(123,456)'::point) ROWS UNBOUNDED PRECEDING) -> Index Scan using quad_poly_tbl_idx on quad_poly_tbl Index Cond: (p <@ '((300,300),(400,600),(600,500),(700,200))'::polygon) Order By: (p <-> '(123,456)'::point) -(4 rows) +(5 rows) CREATE TEMP TABLE quad_poly_tbl_ord_idx2 AS SELECT rank() OVER (ORDER BY p <-> point '123,456') n, p <-> point '123,456' dist, id diff --git a/src/test/regress/expected/select_parallel.out b/src/test/regress/expected/select_parallel.out index 56509540f2a..0185ef661b1 100644 --- a/src/test/regress/expected/select_parallel.out +++ b/src/test/regress/expected/select_parallel.out @@ -1130,9 +1130,10 @@ explain (costs off, verbose) Aggregate Output: count(*) -> Hash Right Semi Join - Hash Cond: ((b.unique1 = a.unique1) AND ((row_number() OVER (?)) = a.two)) + Hash Cond: ((b.unique1 = a.unique1) AND ((row_number() OVER w1) = a.two)) -> WindowAgg - Output: b.unique1, row_number() OVER (?) + Output: b.unique1, row_number() OVER w1 + Window: w1 AS (ROWS UNBOUNDED PRECEDING) -> Gather Output: b.unique1 Workers Planned: 4 @@ -1145,7 +1146,7 @@ explain (costs off, verbose) Workers Planned: 4 -> Parallel Seq Scan on public.tenk1 a Output: a.unique1, a.two -(18 rows) +(19 rows) -- LIMIT/OFFSET within sub-selects can't be pushed to workers. explain (costs off) diff --git a/src/test/regress/expected/sqljson.out b/src/test/regress/expected/sqljson.out index 5e664fae084..7c3e673e5ea 100644 --- a/src/test/regress/expected/sqljson.out +++ b/src/test/regress/expected/sqljson.out @@ -1011,17 +1011,18 @@ FROM generate_series(1,5) i; EXPLAIN (VERBOSE, COSTS OFF) SELECT JSON_OBJECTAGG(i: ('111' || i)::bytea FORMAT JSON WITH UNIQUE RETURNING text) OVER (PARTITION BY i % 2) FROM generate_series(1,5) i; - QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------ + QUERY PLAN +---------------------------------------------------------------------------------------------------------------------------------- WindowAgg - Output: JSON_OBJECTAGG(i : (('111'::text || (i)::text))::bytea FORMAT JSON WITH UNIQUE KEYS RETURNING text) OVER (?), ((i % 2)) + Output: JSON_OBJECTAGG(i : (('111'::text || (i)::text))::bytea FORMAT JSON WITH UNIQUE KEYS RETURNING text) OVER w1, ((i % 2)) + Window: w1 AS (PARTITION BY ((i.i % 2))) -> Sort Output: ((i % 2)), i Sort Key: ((i.i % 2)) -> Function Scan on pg_catalog.generate_series i Output: (i % 2), i Function Call: generate_series(1, 5) -(8 rows) +(9 rows) CREATE VIEW json_objectagg_view AS SELECT JSON_OBJECTAGG(i: ('111' || i)::bytea FORMAT JSON WITH UNIQUE RETURNING text) FILTER (WHERE i > 3) @@ -1047,17 +1048,18 @@ FROM generate_series(1,5) i; EXPLAIN (VERBOSE, COSTS OFF) SELECT JSON_ARRAYAGG(('111' || i)::bytea FORMAT JSON NULL ON NULL RETURNING text) OVER (PARTITION BY i % 2) FROM generate_series(1,5) i; - QUERY PLAN --------------------------------------------------------------------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------------------------------------------------------------- WindowAgg - Output: JSON_ARRAYAGG((('111'::text || (i)::text))::bytea FORMAT JSON NULL ON NULL RETURNING text) OVER (?), ((i % 2)) + Output: JSON_ARRAYAGG((('111'::text || (i)::text))::bytea FORMAT JSON NULL ON NULL RETURNING text) OVER w1, ((i % 2)) + Window: w1 AS (PARTITION BY ((i.i % 2))) -> Sort Output: ((i % 2)), i Sort Key: ((i.i % 2)) -> Function Scan on pg_catalog.generate_series i Output: (i % 2), i Function Call: generate_series(1, 5) -(8 rows) +(9 rows) CREATE VIEW json_arrayagg_view AS SELECT JSON_ARRAYAGG(('111' || i)::bytea FORMAT JSON NULL ON NULL RETURNING text) FILTER (WHERE i > 3) diff --git a/src/test/regress/expected/window.out b/src/test/regress/expected/window.out index 23d1463df22..b86b668f433 100644 --- a/src/test/regress/expected/window.out +++ b/src/test/regress/expected/window.out @@ -652,10 +652,11 @@ select first_value(max(x)) over (), y QUERY PLAN --------------------------------------------- WindowAgg + Window: w1 AS () -> HashAggregate Group Key: (tenk1.ten + tenk1.four) -> Seq Scan on tenk1 -(4 rows) +(5 rows) -- window functions returning pass-by-ref values from different rows select x, lag(x, 1) over (order by x), lead(x, 3) over (order by x) @@ -3537,14 +3538,15 @@ explain (costs off) select f1, sum(f1) over (partition by f1 order by f2 range between 1 preceding and 1 following) from t1 where f1 = f2; - QUERY PLAN ---------------------------------- + QUERY PLAN +------------------------------------------------------------------------------------------------------------- WindowAgg + Window: w1 AS (PARTITION BY f1 ORDER BY f2 RANGE BETWEEN '1'::bigint PRECEDING AND '1'::bigint FOLLOWING) -> Sort Sort Key: f1 -> Seq Scan on t1 Filter: (f1 = f2) -(5 rows) +(6 rows) select f1, sum(f1) over (partition by f1 order by f2 range between 1 preceding and 1 following) @@ -3583,14 +3585,15 @@ explain (costs off) select f1, sum(f1) over (partition by f1 order by f2 groups between 1 preceding and 1 following) from t1 where f1 = f2; - QUERY PLAN ---------------------------------- + QUERY PLAN +-------------------------------------------------------------------------------------------------------------- WindowAgg + Window: w1 AS (PARTITION BY f1 ORDER BY f2 GROUPS BETWEEN '1'::bigint PRECEDING AND '1'::bigint FOLLOWING) -> Sort Sort Key: f1 -> Seq Scan on t1 Filter: (f1 = f2) -(5 rows) +(6 rows) select f1, sum(f1) over (partition by f1 order by f2 groups between 1 preceding and 1 following) @@ -3711,13 +3714,14 @@ SELECT cume_dist() OVER (PARTITION BY depname ORDER BY enroll_date RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) cd FROM empsalary; - QUERY PLAN ----------------------------------------- + QUERY PLAN +-------------------------------------------------------------------------------------- WindowAgg + Window: w1 AS (PARTITION BY depname ORDER BY enroll_date ROWS UNBOUNDED PRECEDING) -> Sort Sort Key: depname, enroll_date -> Seq Scan on empsalary -(4 rows) +(5 rows) -- Ensure WindowFuncs which cannot support their WindowClause's frameOptions -- being changed are untouched @@ -3731,18 +3735,20 @@ SELECT count(*) OVER (PARTITION BY depname ORDER BY enroll_date RANGE BETWEEN CURRENT ROW AND CURRENT ROW) cnt FROM empsalary; - QUERY PLAN ------------------------------------------------------------------------------------------------------- + QUERY PLAN +--------------------------------------------------------------------------------------------------------------------------- WindowAgg - Output: empno, depname, (row_number() OVER (?)), (rank() OVER (?)), count(*) OVER (?), enroll_date + Output: empno, depname, (row_number() OVER w1), (rank() OVER w1), count(*) OVER w2, enroll_date + Window: w2 AS (PARTITION BY empsalary.depname ORDER BY empsalary.enroll_date RANGE BETWEEN CURRENT ROW AND CURRENT ROW) -> WindowAgg - Output: depname, enroll_date, empno, row_number() OVER (?), rank() OVER (?) + Output: depname, enroll_date, empno, row_number() OVER w1, rank() OVER w1 + Window: w1 AS (PARTITION BY empsalary.depname ORDER BY empsalary.enroll_date ROWS UNBOUNDED PRECEDING) -> Sort Output: depname, enroll_date, empno Sort Key: empsalary.depname, empsalary.enroll_date -> Seq Scan on pg_temp.empsalary Output: depname, enroll_date, empno -(9 rows) +(11 rows) -- Ensure the above query gives us the expected results SELECT @@ -3777,16 +3783,18 @@ SELECT * FROM min(salary) OVER (PARTITION BY depname || 'A', depname) depminsalary FROM empsalary) emp WHERE depname = 'sales'; - QUERY PLAN --------------------------------------------------------------------------- + QUERY PLAN +--------------------------------------------------------------------------------------- Subquery Scan on emp -> WindowAgg + Window: w2 AS () -> WindowAgg + Window: w1 AS (PARTITION BY (((empsalary.depname)::text || 'A'::text))) -> Sort Sort Key: (((empsalary.depname)::text || 'A'::text)) -> Seq Scan on empsalary Filter: ((depname)::text = 'sales'::text) -(7 rows) +(9 rows) -- pushdown is unsafe because there's a PARTITION BY clause without depname: EXPLAIN (COSTS OFF) @@ -3796,18 +3804,20 @@ SELECT * FROM min(salary) OVER (PARTITION BY depname) depminsalary FROM empsalary) emp WHERE depname = 'sales'; - QUERY PLAN -------------------------------------------------------- + QUERY PLAN +-------------------------------------------------------------------- Subquery Scan on emp Filter: ((emp.depname)::text = 'sales'::text) -> WindowAgg + Window: w2 AS (PARTITION BY empsalary.enroll_date) -> Sort Sort Key: empsalary.enroll_date -> WindowAgg + Window: w1 AS (PARTITION BY empsalary.depname) -> Sort Sort Key: empsalary.depname -> Seq Scan on empsalary -(9 rows) +(11 rows) -- Test window function run conditions are properly pushed down into the -- WindowAgg @@ -3817,14 +3827,15 @@ SELECT * FROM row_number() OVER (ORDER BY empno) rn FROM empsalary) emp WHERE rn < 3; - QUERY PLAN ----------------------------------------------- + QUERY PLAN +--------------------------------------------------------------------- WindowAgg - Run Condition: (row_number() OVER (?) < 3) + Window: w1 AS (ORDER BY empsalary.empno ROWS UNBOUNDED PRECEDING) + Run Condition: (row_number() OVER w1 < 3) -> Sort Sort Key: empsalary.empno -> Seq Scan on empsalary -(5 rows) +(6 rows) -- The following 3 statements should result the same result. SELECT * FROM @@ -3868,14 +3879,15 @@ SELECT * FROM rank() OVER (ORDER BY salary DESC) r FROM empsalary) emp WHERE r <= 3; - QUERY PLAN ------------------------------------------ + QUERY PLAN +---------------------------------------------------------------------- WindowAgg - Run Condition: (rank() OVER (?) <= 3) + Window: w1 AS (ORDER BY empsalary.salary ROWS UNBOUNDED PRECEDING) + Run Condition: (rank() OVER w1 <= 3) -> Sort Sort Key: empsalary.salary DESC -> Seq Scan on empsalary -(5 rows) +(6 rows) SELECT * FROM (SELECT empno, @@ -3898,16 +3910,17 @@ SELECT * FROM dense_rank() OVER (ORDER BY salary DESC) dr FROM empsalary) emp WHERE dr = 1; - QUERY PLAN ------------------------------------------------------ + QUERY PLAN +---------------------------------------------------------------------------- Subquery Scan on emp Filter: (emp.dr = 1) -> WindowAgg - Run Condition: (dense_rank() OVER (?) <= 1) + Window: w1 AS (ORDER BY empsalary.salary ROWS UNBOUNDED PRECEDING) + Run Condition: (dense_rank() OVER w1 <= 1) -> Sort Sort Key: empsalary.salary DESC -> Seq Scan on empsalary -(7 rows) +(8 rows) SELECT * FROM (SELECT empno, @@ -3928,14 +3941,15 @@ SELECT * FROM count(*) OVER (ORDER BY salary DESC) c FROM empsalary) emp WHERE c <= 3; - QUERY PLAN -------------------------------------------- + QUERY PLAN +--------------------------------------------- WindowAgg - Run Condition: (count(*) OVER (?) <= 3) + Window: w1 AS (ORDER BY empsalary.salary) + Run Condition: (count(*) OVER w1 <= 3) -> Sort Sort Key: empsalary.salary DESC -> Seq Scan on empsalary -(5 rows) +(6 rows) SELECT * FROM (SELECT empno, @@ -3957,14 +3971,15 @@ SELECT * FROM count(empno) OVER (ORDER BY salary DESC) c FROM empsalary) emp WHERE c <= 3; - QUERY PLAN ---------------------------------------------------------- + QUERY PLAN +-------------------------------------------------------- WindowAgg - Run Condition: (count(empsalary.empno) OVER (?) <= 3) + Window: w1 AS (ORDER BY empsalary.salary) + Run Condition: (count(empsalary.empno) OVER w1 <= 3) -> Sort Sort Key: empsalary.salary DESC -> Seq Scan on empsalary -(5 rows) +(6 rows) SELECT * FROM (SELECT empno, @@ -3986,14 +4001,15 @@ SELECT * FROM count(*) OVER (ORDER BY salary DESC ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) c FROM empsalary) emp WHERE c >= 3; - QUERY PLAN -------------------------------------------- + QUERY PLAN +---------------------------------------------------------------------------------------------- WindowAgg - Run Condition: (count(*) OVER (?) >= 3) + Window: w1 AS (ORDER BY empsalary.salary ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) + Run Condition: (count(*) OVER w1 >= 3) -> Sort Sort Key: empsalary.salary DESC -> Seq Scan on empsalary -(5 rows) +(6 rows) EXPLAIN (COSTS OFF) SELECT * FROM @@ -4002,12 +4018,13 @@ SELECT * FROM count(*) OVER () c FROM empsalary) emp WHERE 11 <= c; - QUERY PLAN --------------------------------------------- + QUERY PLAN +------------------------------------------- WindowAgg - Run Condition: (11 <= count(*) OVER (?)) + Window: w1 AS () + Run Condition: (11 <= count(*) OVER w1) -> Seq Scan on empsalary -(3 rows) +(4 rows) EXPLAIN (COSTS OFF) SELECT * FROM @@ -4017,16 +4034,17 @@ SELECT * FROM dense_rank() OVER (ORDER BY salary DESC) dr FROM empsalary) emp WHERE dr = 1; - QUERY PLAN ------------------------------------------------------ + QUERY PLAN +---------------------------------------------------- Subquery Scan on emp Filter: (emp.dr = 1) -> WindowAgg - Run Condition: (dense_rank() OVER (?) <= 1) + Window: w1 AS (ORDER BY empsalary.salary) + Run Condition: (dense_rank() OVER w1 <= 1) -> Sort Sort Key: empsalary.salary DESC -> Seq Scan on empsalary -(7 rows) +(8 rows) -- Ensure we get a run condition when there's a PARTITION BY clause EXPLAIN (COSTS OFF) @@ -4036,14 +4054,15 @@ SELECT * FROM row_number() OVER (PARTITION BY depname ORDER BY empno) rn FROM empsalary) emp WHERE rn < 3; - QUERY PLAN ------------------------------------------------------- + QUERY PLAN +---------------------------------------------------------------------------------------------------- WindowAgg - Run Condition: (row_number() OVER (?) < 3) + Window: w1 AS (PARTITION BY empsalary.depname ORDER BY empsalary.empno ROWS UNBOUNDED PRECEDING) + Run Condition: (row_number() OVER w1 < 3) -> Sort Sort Key: empsalary.depname, empsalary.empno -> Seq Scan on empsalary -(5 rows) +(6 rows) -- and ensure we get the correct results from the above plan SELECT * FROM @@ -4071,15 +4090,16 @@ SELECT empno, depname FROM row_number() OVER (PARTITION BY depname ORDER BY empno) rn FROM empsalary) emp WHERE rn < 3; - QUERY PLAN ------------------------------------------------------------- + QUERY PLAN +---------------------------------------------------------------------------------------------------------- Subquery Scan on emp -> WindowAgg - Run Condition: (row_number() OVER (?) < 3) + Window: w1 AS (PARTITION BY empsalary.depname ORDER BY empsalary.empno ROWS UNBOUNDED PRECEDING) + Run Condition: (row_number() OVER w1 < 3) -> Sort Sort Key: empsalary.depname, empsalary.empno -> Seq Scan on empsalary -(6 rows) +(7 rows) -- likewise with count(empno) instead of row_number() EXPLAIN (COSTS OFF) @@ -4090,14 +4110,15 @@ SELECT * FROM count(empno) OVER (PARTITION BY depname ORDER BY salary DESC) c FROM empsalary) emp WHERE c <= 3; - QUERY PLAN ------------------------------------------------------------- + QUERY PLAN +---------------------------------------------------------------------------- WindowAgg - Run Condition: (count(empsalary.empno) OVER (?) <= 3) + Window: w1 AS (PARTITION BY empsalary.depname ORDER BY empsalary.salary) + Run Condition: (count(empsalary.empno) OVER w1 <= 3) -> Sort Sort Key: empsalary.depname, empsalary.salary DESC -> Seq Scan on empsalary -(5 rows) +(6 rows) -- and again, check the results are what we expect. SELECT * FROM @@ -4129,12 +4150,13 @@ SELECT * FROM count(empno) OVER () c FROM empsalary) emp WHERE c = 1; - QUERY PLAN --------------------------------------------------------- + QUERY PLAN +------------------------------------------------------- WindowAgg - Run Condition: (count(empsalary.empno) OVER (?) = 1) + Window: w1 AS () + Run Condition: (count(empsalary.empno) OVER w1 = 1) -> Seq Scan on empsalary -(3 rows) +(4 rows) -- Try another case with a WindowFunc with a byref return type SELECT * FROM @@ -4157,23 +4179,26 @@ SELECT * FROM ntile(2) OVER (PARTITION BY depname) nt -- w2 FROM empsalary ) e WHERE rn <= 1 AND c1 <= 3 AND nt < 2; - QUERY PLAN ------------------------------------------------------------------------------------------------ + QUERY PLAN +-------------------------------------------------------------------------------------------------------- Subquery Scan on e -> WindowAgg - Filter: (((row_number() OVER (?)) <= 1) AND ((ntile(2) OVER (?)) < 2)) - Run Condition: (count(empsalary.salary) OVER (?) <= 3) + Window: w3 AS (PARTITION BY (((empsalary.depname)::text || ''::text))) + Run Condition: (count(empsalary.salary) OVER w3 <= 3) + Filter: (((row_number() OVER w2) <= 1) AND ((ntile(2) OVER w2) < 2)) -> Sort Sort Key: (((empsalary.depname)::text || ''::text)) -> WindowAgg - Run Condition: ((row_number() OVER (?) <= 1) AND (ntile(2) OVER (?) < 2)) + Window: w2 AS (PARTITION BY empsalary.depname) + Run Condition: ((row_number() OVER w2 <= 1) AND (ntile(2) OVER w2 < 2)) -> Sort Sort Key: empsalary.depname -> WindowAgg + Window: w1 AS (PARTITION BY ((''::text || (empsalary.depname)::text))) -> Sort Sort Key: ((''::text || (empsalary.depname)::text)) -> Seq Scan on empsalary -(14 rows) +(17 rows) -- Ensure we correctly filter out all of the run conditions from each window SELECT * FROM @@ -4199,12 +4224,13 @@ SELECT 1 FROM FROM empsalary e1 LEFT JOIN empsalary e2 ON TRUE WHERE e1.empno = e2.empno) s WHERE s.c = 1; - QUERY PLAN ---------------------------------------------------------- + QUERY PLAN +-------------------------------------------------------------------------- Subquery Scan on s Filter: (s.c = 1) -> WindowAgg - Run Condition: (ntile(e2.salary) OVER (?) <= 1) + Window: w1 AS (PARTITION BY e1.depname ROWS UNBOUNDED PRECEDING) + Run Condition: (ntile(e2.salary) OVER w1 <= 1) -> Sort Sort Key: e1.depname -> Merge Join @@ -4215,7 +4241,7 @@ WHERE s.c = 1; -> Sort Sort Key: e2.empno -> Seq Scan on empsalary e2 -(14 rows) +(15 rows) -- Ensure the run condition optimization is used in cases where the WindowFunc -- has a Var from another query level @@ -4224,16 +4250,17 @@ SELECT 1 FROM (SELECT ntile(s1.x) OVER () AS c FROM (SELECT (SELECT 1) AS x) AS s1) s WHERE s.c = 1; - QUERY PLAN ------------------------------------------------------------------ + QUERY PLAN +---------------------------------------------------------------- Subquery Scan on s Filter: (s.c = 1) -> WindowAgg - Run Condition: (ntile((InitPlan 1).col1) OVER (?) <= 1) + Window: w1 AS (ROWS UNBOUNDED PRECEDING) + Run Condition: (ntile((InitPlan 1).col1) OVER w1 <= 1) InitPlan 1 -> Result -> Result -(7 rows) +(8 rows) -- Tests to ensure we don't push down the run condition when it's not valid to -- do so. @@ -4246,15 +4273,16 @@ SELECT * FROM count(*) OVER (ORDER BY salary DESC ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) c FROM empsalary) emp WHERE c <= 3; - QUERY PLAN ------------------------------------------------ + QUERY PLAN +---------------------------------------------------------------------------------------------------- Subquery Scan on emp Filter: (emp.c <= 3) -> WindowAgg + Window: w1 AS (ORDER BY empsalary.salary ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) -> Sort Sort Key: empsalary.salary DESC -> Seq Scan on empsalary -(6 rows) +(7 rows) -- Ensure we don't push down when the window function's monotonic properties -- don't match that of the clauses. @@ -4265,15 +4293,16 @@ SELECT * FROM count(*) OVER (ORDER BY salary) c FROM empsalary) emp WHERE 3 <= c; - QUERY PLAN ------------------------------------------- + QUERY PLAN +--------------------------------------------------- Subquery Scan on emp Filter: (3 <= emp.c) -> WindowAgg + Window: w1 AS (ORDER BY empsalary.salary) -> Sort Sort Key: empsalary.salary -> Seq Scan on empsalary -(6 rows) +(7 rows) -- Ensure we don't use a run condition when there's a volatile function in the -- WindowFunc @@ -4284,15 +4313,16 @@ SELECT * FROM count(random()) OVER (ORDER BY empno DESC) c FROM empsalary) emp WHERE c = 1; - QUERY PLAN ----------------------------------------------- + QUERY PLAN +-------------------------------------------------- Subquery Scan on emp Filter: (emp.c = 1) -> WindowAgg + Window: w1 AS (ORDER BY empsalary.empno) -> Sort Sort Key: empsalary.empno DESC -> Seq Scan on empsalary -(6 rows) +(7 rows) -- Ensure we don't use a run condition when the WindowFunc contains subplans EXPLAIN (COSTS OFF) @@ -4302,17 +4332,18 @@ SELECT * FROM count((SELECT 1)) OVER (ORDER BY empno DESC) c FROM empsalary) emp WHERE c = 1; - QUERY PLAN ----------------------------------------------- + QUERY PLAN +-------------------------------------------------- Subquery Scan on emp Filter: (emp.c = 1) -> WindowAgg + Window: w1 AS (ORDER BY empsalary.empno) InitPlan 1 -> Result -> Sort Sort Key: empsalary.empno DESC -> Seq Scan on empsalary -(8 rows) +(9 rows) -- Test Sort node collapsing EXPLAIN (COSTS OFF) @@ -4322,16 +4353,18 @@ SELECT * FROM min(salary) OVER (PARTITION BY depname, empno order by enroll_date) depminsalary FROM empsalary) emp WHERE depname = 'sales'; - QUERY PLAN ----------------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------------------------------- Subquery Scan on emp -> WindowAgg + Window: w2 AS (ORDER BY empsalary.empno) -> WindowAgg + Window: w1 AS (PARTITION BY empsalary.empno ORDER BY empsalary.enroll_date) -> Sort Sort Key: empsalary.empno, empsalary.enroll_date -> Seq Scan on empsalary Filter: ((depname)::text = 'sales'::text) -(7 rows) +(9 rows) -- Ensure that the evaluation order of the WindowAggs results in the WindowAgg -- with the same sort order that's required by the ORDER BY is evaluated last. @@ -4343,17 +4376,19 @@ SELECT empno, min(salary) OVER (PARTITION BY depname order by enroll_date) depminsalary FROM empsalary ORDER BY depname, empno; - QUERY PLAN ----------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------------- WindowAgg + Window: w2 AS (PARTITION BY depname ORDER BY empno) -> Incremental Sort Sort Key: depname, empno Presorted Key: depname -> WindowAgg + Window: w1 AS (PARTITION BY depname ORDER BY enroll_date) -> Sort Sort Key: depname, enroll_date -> Seq Scan on empsalary -(8 rows) +(10 rows) -- As above, but with an adjusted ORDER BY to ensure the above plan didn't -- perform only 2 sorts by accident. @@ -4365,17 +4400,19 @@ SELECT empno, min(salary) OVER (PARTITION BY depname order by enroll_date) depminsalary FROM empsalary ORDER BY depname, enroll_date; - QUERY PLAN ------------------------------------------------ + QUERY PLAN +------------------------------------------------------------------- WindowAgg + Window: w2 AS (PARTITION BY depname ORDER BY enroll_date) -> Incremental Sort Sort Key: depname, enroll_date Presorted Key: depname -> WindowAgg + Window: w1 AS (PARTITION BY depname ORDER BY empno) -> Sort Sort Key: depname, empno -> Seq Scan on empsalary -(8 rows) +(10 rows) SET enable_hashagg TO off; -- Ensure we don't get a sort for both DISTINCT and ORDER BY. We expect the @@ -4389,21 +4426,23 @@ SELECT DISTINCT min(salary) OVER (PARTITION BY depname order by enroll_date) depminsalary FROM empsalary ORDER BY depname, enroll_date; - QUERY PLAN ------------------------------------------------------------------------------------------------ + QUERY PLAN +--------------------------------------------------------------------------------------------- Unique -> Incremental Sort - Sort Key: depname, enroll_date, empno, (sum(salary) OVER (?)), (min(salary) OVER (?)) + Sort Key: depname, enroll_date, empno, (sum(salary) OVER w1), (min(salary) OVER w2) Presorted Key: depname, enroll_date -> WindowAgg + Window: w2 AS (PARTITION BY depname ORDER BY enroll_date) -> Incremental Sort Sort Key: depname, enroll_date Presorted Key: depname -> WindowAgg + Window: w1 AS (PARTITION BY depname ORDER BY empno) -> Sort Sort Key: depname, empno -> Seq Scan on empsalary -(12 rows) +(14 rows) -- As above but adjust the ORDER BY clause to help ensure the plan with the -- minimum amount of sorting wasn't a fluke. @@ -4416,21 +4455,23 @@ SELECT DISTINCT min(salary) OVER (PARTITION BY depname order by enroll_date) depminsalary FROM empsalary ORDER BY depname, empno; - QUERY PLAN ------------------------------------------------------------------------------------------------ + QUERY PLAN +--------------------------------------------------------------------------------------------- Unique -> Incremental Sort - Sort Key: depname, empno, enroll_date, (sum(salary) OVER (?)), (min(salary) OVER (?)) + Sort Key: depname, empno, enroll_date, (sum(salary) OVER w2), (min(salary) OVER w1) Presorted Key: depname, empno -> WindowAgg + Window: w2 AS (PARTITION BY depname ORDER BY empno) -> Incremental Sort Sort Key: depname, empno Presorted Key: depname -> WindowAgg + Window: w1 AS (PARTITION BY depname ORDER BY enroll_date) -> Sort Sort Key: depname, enroll_date -> Seq Scan on empsalary -(12 rows) +(14 rows) RESET enable_hashagg; -- Test Sort node reordering @@ -4439,14 +4480,16 @@ SELECT lead(1) OVER (PARTITION BY depname ORDER BY salary, enroll_date), lag(1) OVER (PARTITION BY depname ORDER BY salary,enroll_date,empno) FROM empsalary; - QUERY PLAN -------------------------------------------------------------- + QUERY PLAN +---------------------------------------------------------------------------------- WindowAgg + Window: w2 AS (PARTITION BY depname ORDER BY salary, enroll_date) -> WindowAgg + Window: w1 AS (PARTITION BY depname ORDER BY salary, enroll_date, empno) -> Sort Sort Key: depname, salary, enroll_date, empno -> Seq Scan on empsalary -(5 rows) +(7 rows) -- Test incremental sorting EXPLAIN (COSTS OFF) @@ -4459,19 +4502,21 @@ SELECT * FROM row_number() OVER (PARTITION BY depname ORDER BY enroll_date DESC) AS last_emp FROM empsalary) emp WHERE first_emp = 1 OR last_emp = 1; - QUERY PLAN ------------------------------------------------------------------------------------ + QUERY PLAN +---------------------------------------------------------------------------------------------------------------------------- Subquery Scan on emp Filter: ((emp.first_emp = 1) OR (emp.last_emp = 1)) -> WindowAgg + Window: w2 AS (PARTITION BY empsalary.depname ORDER BY empsalary.enroll_date ROWS UNBOUNDED PRECEDING) -> Incremental Sort Sort Key: empsalary.depname, empsalary.enroll_date Presorted Key: empsalary.depname -> WindowAgg + Window: w1 AS (PARTITION BY empsalary.depname ORDER BY empsalary.enroll_date ROWS UNBOUNDED PRECEDING) -> Sort Sort Key: empsalary.depname, empsalary.enroll_date DESC -> Seq Scan on empsalary -(10 rows) +(12 rows) SELECT * FROM (SELECT depname, @@ -5299,11 +5344,12 @@ LIMIT 1; -------------------------------------------------------------------------- Limit -> WindowAgg + Window: w1 AS (ORDER BY t1.unique1) -> Nested Loop -> Index Only Scan using tenk1_unique1 on tenk1 t1 -> Index Only Scan using tenk1_thous_tenthous on tenk1 t2 Index Cond: (tenthous = t1.unique1) -(6 rows) +(7 rows) -- Ensure we get a cheap total plan. Lack of ORDER BY in the WindowClause -- means that all rows must be read from the join, so a cheap startup plan @@ -5317,13 +5363,14 @@ LIMIT 1; ------------------------------------------------------------------- Limit -> WindowAgg + Window: w1 AS () -> Hash Join Hash Cond: (t1.unique1 = t2.tenthous) -> Index Only Scan using tenk1_unique1 on tenk1 t1 -> Hash -> Seq Scan on tenk1 t2 Filter: (two = 1) -(8 rows) +(9 rows) -- Ensure we get a cheap total plan. This time use UNBOUNDED FOLLOWING, which -- needs to read all join rows to output the first WindowAgg row. @@ -5331,17 +5378,18 @@ EXPLAIN (COSTS OFF) 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 + Window: w1 AS (ORDER BY t1.unique1 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) -> 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) +(9 rows) -- Ensure we get a cheap total plan. This time use 10000 FOLLOWING so we need -- to read all join rows. @@ -5349,17 +5397,18 @@ EXPLAIN (COSTS OFF) 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 + Window: w1 AS (ORDER BY t1.unique1 ROWS BETWEEN UNBOUNDED PRECEDING AND '10000'::bigint FOLLOWING) -> 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) +(9 rows) -- Tests for problems with failure to walk or mutate expressions -- within window frame clauses. @@ -5384,14 +5433,15 @@ AS $$ WINDOW w AS (ORDER BY s ROWS BETWEEN CURRENT ROW AND GROUP_SIZE FOLLOWING) $$ LANGUAGE SQL STABLE; EXPLAIN (costs off) SELECT * FROM pg_temp.f(2); - QUERY PLAN ------------------------------------------------------- + QUERY PLAN +---------------------------------------------------------------------------------------- Subquery Scan on f -> WindowAgg + Window: w AS (ORDER BY s.s ROWS BETWEEN CURRENT ROW AND '2'::bigint FOLLOWING) -> Sort Sort Key: s.s -> Function Scan on generate_series s -(5 rows) +(6 rows) SELECT * FROM pg_temp.f(2); f diff --git a/src/test/regress/sql/explain.sql b/src/test/regress/sql/explain.sql index 0bafa870496..b266764089f 100644 --- a/src/test/regress/sql/explain.sql +++ b/src/test/regress/sql/explain.sql @@ -70,6 +70,11 @@ select explain_filter('explain (analyze, serialize, buffers, format yaml) select select explain_filter('explain (buffers, format text) select * from int8_tbl i8'); select explain_filter('explain (buffers, format json) select * from int8_tbl i8'); +-- Check expansion of window definitions + +select explain_filter('explain verbose select sum(unique1) over w, sum(unique2) over (w order by hundred), sum(tenthous) over (w order by hundred) from tenk1 window w as (partition by ten)'); +select explain_filter('explain verbose select sum(unique1) over w1, sum(unique2) over (w1 order by hundred), sum(tenthous) over (w1 order by hundred rows 10 preceding) from tenk1 window w1 as (partition by ten)'); + -- Check output including I/O timings. These fields are conditional -- but always set in JSON format, so check them only in this case. set track_io_timing = on; |