create schema srf_fusion;
set search_path to srf_fusion;
set enable_expr_fusion = on;
show enable_expr_fusion;
enable_expr_fusion
--------------------
on
(1 row)
create table empsalary (id int, depname text, empno int, salary int);
INSERT INTO empsalary (id, depname, empno, salary) VALUES
(1,'develop',11,5200), (2, 'develop',7,4200),
(3, 'personnel', 9, 4500), (4, 'personnel', 8, 6000),
(5, 'sales', 10, 5300) , (6, 'sales', 18, 6200);
explain (costs off, verbose)
SELECT generate_series(1, id), depname, empno, salary, rank() OVER (PARTITION BY depname ORDER BY salary DESC) FROM empsalary;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------
ProjectSet
Output: generate_series(1, id), depname, empno, salary, (rank() OVER (PARTITION BY depname ORDER BY salary USING = NULLS LAST))
-> WindowAgg
Output: id, depname, empno, salary, rank() OVER (PARTITION BY depname ORDER BY salary USING = NULLS LAST)
-> Sort
Output: depname, salary, id, empno
Sort Key: empsalary.depname, empsalary.salary DESC
-> Seq Scan on srf_fusion.empsalary
Output: depname, salary, id, empno
(9 rows)
SELECT generate_series(1, id), depname, empno, salary, rank() OVER (PARTITION BY depname ORDER BY salary DESC) FROM empsalary;
generate_series | depname | empno | salary | rank
-----------------+-----------+-------+--------+------
1 | develop | 11 | 5200 | 1
1 | develop | 7 | 4200 | 2
2 | develop | 7 | 4200 | 2
1 | personnel | 8 | 6000 | 1
2 | personnel | 8 | 6000 | 1
3 | personnel | 8 | 6000 | 1
4 | personnel | 8 | 6000 | 1
1 | personnel | 9 | 4500 | 2
2 | personnel | 9 | 4500 | 2
3 | personnel | 9 | 4500 | 2
1 | sales | 18 | 6200 | 1
2 | sales | 18 | 6200 | 1
3 | sales | 18 | 6200 | 1
4 | sales | 18 | 6200 | 1
5 | sales | 18 | 6200 | 1
6 | sales | 18 | 6200 | 1
1 | sales | 10 | 5300 | 2
2 | sales | 10 | 5300 | 2
3 | sales | 10 | 5300 | 2
4 | sales | 10 | 5300 | 2
5 | sales | 10 | 5300 | 2
(21 rows)
explain (costs off, verbose)
SELECT generate_series(id, generate_series(1, 2)), depname, empno, salary, rank() OVER (PARTITION BY depname ORDER BY salary DESC) FROM empsalary;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------
ProjectSet
Output: generate_series(id, (generate_series(1, 2))), depname, empno, salary, (rank() OVER (PARTITION BY depname ORDER BY salary USING = NULLS LAST))
-> ProjectSet
Output: generate_series(1, 2), id, depname, empno, salary, (rank() OVER (PARTITION BY depname ORDER BY salary USING = NULLS LAST))
-> WindowAgg
Output: id, depname, empno, salary, rank() OVER (PARTITION BY depname ORDER BY salary USING = NULLS LAST)
-> Sort
Output: depname, salary, id, empno
Sort Key: empsalary.depname, empsalary.salary DESC
-> Seq Scan on srf_fusion.empsalary
Output: depname, salary, id, empno
(11 rows)
SELECT generate_series(id, generate_series(1, 2)), depname, empno, salary, rank() OVER (PARTITION BY depname ORDER BY salary DESC) FROM empsalary;
generate_series | depname | empno | salary | rank
-----------------+---------+-------+--------+------
1 | develop | 11 | 5200 | 1
1 | develop | 11 | 5200 | 1
2 | develop | 11 | 5200 | 1
2 | develop | 7 | 4200 | 2
(4 rows)
explain (costs off, verbose)
SELECT id, generate_series(1, id), salary, sum(salary) OVER () FROM empsalary;
QUERY PLAN
---------------------------------------------------------------------
ProjectSet
Output: id, generate_series(1, id), salary, (sum(salary) OVER ())
-> WindowAgg
Output: id, salary, sum(salary) OVER ()
-> Seq Scan on srf_fusion.empsalary
Output: id, salary
(6 rows)
SELECT id, generate_series(1, id), salary, sum(salary) OVER () FROM empsalary;
id | generate_series | salary | sum
----+-----------------+--------+-------
1 | 1 | 5200 | 31400
2 | 1 | 4200 | 31400
2 | 2 | 4200 | 31400
3 | 1 | 4500 | 31400
3 | 2 | 4500 | 31400
3 | 3 | 4500 | 31400
4 | 1 | 6000 | 31400
4 | 2 | 6000 | 31400
4 | 3 | 6000 | 31400
4 | 4 | 6000 | 31400
5 | 1 | 5300 | 31400
5 | 2 | 5300 | 31400
5 | 3 | 5300 | 31400
5 | 4 | 5300 | 31400
5 | 5 | 5300 | 31400
6 | 1 | 6200 | 31400
6 | 2 | 6200 | 31400
6 | 3 | 6200 | 31400
6 | 4 | 6200 | 31400
6 | 5 | 6200 | 31400
6 | 6 | 6200 | 31400
(21 rows)
explain (costs off, verbose)
SELECT id, generate_series(1, id), salary, sum(salary) OVER (ORDER BY salary) FROM empsalary;
QUERY PLAN
-------------------------------------------------------------------------------------------------------
ProjectSet
Output: id, generate_series(1, id), salary, (sum(salary) OVER (ORDER BY salary USING = NULLS LAST))
-> WindowAgg
Output: id, salary, sum(salary) OVER (ORDER BY salary USING = NULLS LAST)
-> Sort
Output: salary, id
Sort Key: empsalary.salary
-> Seq Scan on srf_fusion.empsalary
Output: salary, id
(9 rows)
SELECT id, generate_series(1, id), salary, sum(salary) OVER (ORDER BY salary) FROM empsalary;
id | generate_series | salary | sum
----+-----------------+--------+-------
2 | 1 | 4200 | 4200
2 | 2 | 4200 | 4200
3 | 1 | 4500 | 8700
3 | 2 | 4500 | 8700
3 | 3 | 4500 | 8700
1 | 1 | 5200 | 13900
5 | 1 | 5300 | 19200
5 | 2 | 5300 | 19200
5 | 3 | 5300 | 19200
5 | 4 | 5300 | 19200
5 | 5 | 5300 | 19200
4 | 1 | 6000 | 25200
4 | 2 | 6000 | 25200
4 | 3 | 6000 | 25200
4 | 4 | 6000 | 25200
6 | 1 | 6200 | 31400
6 | 2 | 6200 | 31400
6 | 3 | 6200 | 31400
6 | 4 | 6200 | 31400
6 | 5 | 6200 | 31400
6 | 6 | 6200 | 31400
(21 rows)
explain (costs off, verbose)
SELECT generate_series(1,id), sum(salary) OVER w, avg(salary) OVER w
FROM empsalary
WINDOW w AS (PARTITION BY depname ORDER BY salary DESC);
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
ProjectSet
Output: generate_series(1, id), (sum(salary) OVER (PARTITION BY depname ORDER BY salary USING = NULLS LAST)), (avg(salary) OVER (PARTITION BY depname ORDER BY salary USING = NULLS LAST)), salary, depname
-> WindowAgg
Output: id, sum(salary) OVER (PARTITION BY depname ORDER BY salary USING = NULLS LAST), avg(salary) OVER (PARTITION BY depname ORDER BY salary USING = NULLS LAST), salary, depname
-> Sort
Output: salary, depname, id
Sort Key: empsalary.depname, empsalary.salary DESC
-> Seq Scan on srf_fusion.empsalary
Output: salary, depname, id
(9 rows)
SELECT generate_series(1,id), sum(salary) OVER w, avg(salary) OVER w
FROM empsalary
WINDOW w AS (PARTITION BY depname ORDER BY salary DESC);
generate_series | sum | avg
-----------------+-------+-----------------------
1 | 5200 | 5200.0000000000000000
1 | 9400 | 4700.0000000000000000
2 | 9400 | 4700.0000000000000000
1 | 6000 | 6000.0000000000000000
2 | 6000 | 6000.0000000000000000
3 | 6000 | 6000.0000000000000000
4 | 6000 | 6000.0000000000000000
1 | 10500 | 5250.0000000000000000
2 | 10500 | 5250.0000000000000000
3 | 10500 | 5250.0000000000000000
1 | 6200 | 6200.0000000000000000
2 | 6200 | 6200.0000000000000000
3 | 6200 | 6200.0000000000000000
4 | 6200 | 6200.0000000000000000
5 | 6200 | 6200.0000000000000000
6 | 6200 | 6200.0000000000000000
1 | 11500 | 5750.0000000000000000
2 | 11500 | 5750.0000000000000000
3 | 11500 | 5750.0000000000000000
4 | 11500 | 5750.0000000000000000
5 | 11500 | 5750.0000000000000000
(21 rows)
-- window + sort
explain (costs off, verbose)
SELECT id, generate_series(1, id) as g, salary, sum(salary) OVER (ORDER BY salary) FROM empsalary order by g;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------
Sort
Output: id, (generate_series(1, id)), salary, (sum(salary) OVER (ORDER BY salary USING = NULLS LAST))
Sort Key: (generate_series(1, empsalary.id))
-> ProjectSet
Output: id, generate_series(1, id), salary, (sum(salary) OVER (ORDER BY salary USING = NULLS LAST))
-> WindowAgg
Output: id, salary, sum(salary) OVER (ORDER BY salary USING = NULLS LAST)
-> Sort
Output: salary, id
Sort Key: empsalary.salary
-> Seq Scan on srf_fusion.empsalary
Output: salary, id
(12 rows)
SELECT id, generate_series(1, id) as g, salary, sum(salary) OVER (ORDER BY salary) FROM empsalary order by g;
id | g | salary | sum
----+---+--------+-------
6 | 1 | 6200 | 31400
1 | 1 | 5200 | 13900
5 | 1 | 5300 | 19200
2 | 1 | 4200 | 4200
4 | 1 | 6000 | 25200
3 | 1 | 4500 | 8700
4 | 2 | 6000 | 25200
5 | 2 | 5300 | 19200
2 | 2 | 4200 | 4200
6 | 2 | 6200 | 31400
3 | 2 | 4500 | 8700
6 | 3 | 6200 | 31400
5 | 3 | 5300 | 19200
4 | 3 | 6000 | 25200
3 | 3 | 4500 | 8700
5 | 4 | 5300 | 19200
6 | 4 | 6200 | 31400
4 | 4 | 6000 | 25200
5 | 5 | 5300 | 19200
6 | 5 | 6200 | 31400
6 | 6 | 6200 | 31400
(21 rows)
drop table empsalary;
create table tbl_1k(id bigint, v1 numeric, v2 numeric);
insert into tbl_1k select generate_series(1, 1000), (RANDOM() * 67)::int::numeric, (RANDOM() * 77)::int::numeric;
analyze tbl_1k;
set enable_hashagg=off;
explain (costs off, verbose)
select sum(id), v1,v2,generate_series(1,v1) as g1 from tbl_1k group by v1,v2 order by sum(id), g1 limit 1000;
QUERY PLAN
------------------------------------------------------------------------------
Limit
Output: (sum(id)), v1, v2, (generate_series(1::numeric, v1))
-> Sort
Output: (sum(id)), v1, v2, (generate_series(1::numeric, v1))
Sort Key: (sum(tbl_1k.id)), (generate_series(1::numeric, tbl_1k.v1))
-> ProjectSet
Output: (sum(id)), v1, v2, generate_series(1::numeric, v1)
-> GroupAggregate
Output: v1, sum(id), v2
Group By Key: tbl_1k.v1, tbl_1k.v2
-> Sort
Output: v1, v2, id
Sort Key: tbl_1k.v1, tbl_1k.v2
-> Seq Scan on srf_fusion.tbl_1k
Output: v1, v2, id
(15 rows)
create table agg_group as
select sum(id), v1,v2,generate_series(1,v1) as g1 from tbl_1k group by v1,v2 order by sum(id), g1 limit 1000;
set enable_hashagg=on;
explain (costs off, verbose)
select sum(id), v1,v2,generate_series(1,v1) as g1 from tbl_1k group by v1,v2 order by sum(id), g1 limit 1000;
QUERY PLAN
------------------------------------------------------------------------------
Limit
Output: (sum(id)), v1, v2, (generate_series(1::numeric, v1))
-> Sort
Output: (sum(id)), v1, v2, (generate_series(1::numeric, v1))
Sort Key: (sum(tbl_1k.id)), (generate_series(1::numeric, tbl_1k.v1))
-> ProjectSet
Output: (sum(id)), v1, v2, generate_series(1::numeric, v1)
-> HashAggregate
Output: v1, sum(id), v2
Group By Key: tbl_1k.v1, tbl_1k.v2
-> Seq Scan on srf_fusion.tbl_1k
Output: v1, v2, id
(12 rows)
create table agg_hash as
select sum(id), v1,v2,generate_series(1,v1) as g1 from tbl_1k group by v1,v2 order by sum(id), g1 limit 1000;
-- Compare results to hash aggregation results
(select * from agg_group except select * from agg_hash)
union all
(select * from agg_hash except select * from agg_group);
sum | v1 | v2 | g1
-----+----+----+----
(0 rows)
drop table tbl_1k,agg_group,agg_hash;
create table few(id int, dataa text, datab text);
insert into few values(1, 'a', 'foo'), (2, 'a', 'bar'), (3, 'b', 'bar');
explain (costs off)
select id, lag(id) over(order by id), count(*) over(), generate_series(1, 3) from few order by 1,2,3,4;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------
Sort
Sort Key: id, (lag(id) OVER (ORDER BY id USING = NULLS LAST)), (count(*) OVER ()), (generate_series(1, 3))
-> ProjectSet
-> WindowAgg
-> WindowAgg
-> Sort
Sort Key: id
-> Seq Scan on few
(8 rows)
select id, lag(id) over(order by id), count(*) over(), generate_series(1, 3) from few order by 1,2,3,4;
id | lag | count | generate_series
----+-----+-------+-----------------
1 | | 3 | 1
1 | | 3 | 2
1 | | 3 | 3
2 | 1 | 3 | 1
2 | 1 | 3 | 2
2 | 1 | 3 | 3
3 | 2 | 3 | 1
3 | 2 | 3 | 2
3 | 2 | 3 | 3
(9 rows)
drop table few;
create table part_tsrf(a int,b int) partition by range(a) (partition p1 values less than (50), partition p2 values less than (MAXVALUE));
insert into part_tsrf(a,b) select (RANDOM() * 100)::int, generate_series(1, 100);
create table tsrf as select * from part_tsrf;
--scan/join contains srf
explain (costs off, verbose)
SELECT a,generate_series(1,a+b) FROM part_tsrf;
QUERY PLAN
----------------------------------------------------------
ProjectSet
Output: a, generate_series(1, (a + b))
-> Partition Iterator
Output: a, b
Iterations: 2
-> Partitioned Seq Scan on srf_fusion.part_tsrf
Output: a, b
Selected Partitions: 1..2
(8 rows)
create table t1 as SELECT a,generate_series(1,a+b) FROM part_tsrf;
create table t2 as SELECT a,generate_series(1,a+b) FROM tsrf;
(select * from t1 except select * from t2)
union all
(select * from t2 except select * from t1);
a | generate_series
---+-----------------
(0 rows)
drop table t1, t2;
--agg contsins srf
explain (costs off, verbose)
SELECT generate_series(1, max(a*b)), (a+b) % 1000 + 1 FROM part_tsrf group by (a+b) % 1000;
QUERY PLAN
--------------------------------------------------------------------------------------------
ProjectSet
Output: generate_series(1, (max((a * b)))), ((((a + b) % 1000)) + 1), (((a + b) % 1000))
-> HashAggregate
Output: max((a * b)), (((a + b) % 1000))
Group By Key: ((part_tsrf.a + part_tsrf.b) % 1000)
-> Partition Iterator
Output: ((a + b) % 1000), a, b
Iterations: 2
-> Partitioned Seq Scan on srf_fusion.part_tsrf
Output: ((a + b) % 1000), a, b
Selected Partitions: 1..2
(11 rows)
create table t1 as SELECT generate_series(1, max(a*b)), (a+b) % 1000 + 1 FROM part_tsrf group by (a+b) % 1000;
create table t2 as SELECT generate_series(1, max(a*b)), (a+b) % 1000 + 1 FROM tsrf group by (a+b) % 1000;
(select * from t1 except select * from t2)
union all
(select * from t2 except select * from t1);
generate_series | ?column?
-----------------+----------
(0 rows)
drop table t1, t2, part_tsrf, tsrf;
drop schema srf_fusion cascade;
