/*--create database create database test; -- Running script for pgbanch tables against a scaling factor of 1,00000 / drop constraint from pgbench_tables \! ./pgbench -i -s 100000 postgres > /tmp/my_logs 2>&1 \c postgres create table t1 (i int, j int, k int); create table t2 (i int, j int, k int); create table t3 (i int, j int, k int); insert into t1 values (generate_series(1,100)*random(), generate_series(5,900)*random(), generate_series(8,800)*random()); insert into t2 values (generate_series(4,100)*random(), generate_series(5,900)*random(), generate_series(2,1000)*random()); insert into t3 values (generate_series(1,100)*random(), generate_series(3,900)*random(), generate_series(1,900)*random()); \c test --create objects - create table tv(n int,n1 char(100)); insert into tv values (generate_series(1,1000000),'aaa'); create table tv1(n int,n1 char(100)); insert into tv values (generate_series(10000,1000000),'a'); insert into tv1 values (generate_series(10000,1000000),'a'); analyze tv; analyze tv1; CREATE TABLE part_tbl6 (c1 INTEGER, c2 INTEGER, c3 VARCHAR) PARTITION BY RANGE (c1); CREATE TABLE part_tbl6_p1 PARTITION OF part_tbl6 default; CREATE TABLE part_tbl6_p2 PARTITION OF part_tbl6 FOR VALUES FROM (0) TO (10000); CREATE TABLE part_tbl6_p3 PARTITION OF part_tbl6 FOR VALUES FROM (10001) TO (50000); insert into part_tbl6 values (generate_series(1,10000),generate_series(10001,30000)); */ \c test You are now connected to database "test" as user "centos". ---TestCase 1- Subquery compared with aggregate functions against having clause explain analyze SELECT n,n1 FROM tv GROUP BY n,n1 HAVING max(n) = (select max(n) from tv where n=(select min(n) from tv)) ORDER BY n,n1; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------ Finalize GroupAggregate (cost=290490.93..521150.95 rows=1022628 width=105) (actual time=1419.851..7445.418 rows=1 loops=1) Group Key: tv.n, tv.n1 Filter: (max(tv.n) = $4) Rows Removed by Filter: 1990000 InitPlan 3 (returns $4) -> Result (cost=69013.71..69013.72 rows=1 width=4) (actual time=633.147..633.148 rows=1 loops=1) InitPlan 1 (returns $1) -> Finalize Aggregate (cost=45675.80..45675.81 rows=1 width=4) (actual time=453.324..453.324 rows=1 loops=1) -> Gather (cost=45675.59..45675.80 rows=2 width=4) (actual time=453.271..453.318 rows=3 loops=1) Workers Planned: 2 Workers Launched: 2 -> Partial Aggregate (cost=44675.59..44675.60 rows=1 width=4) (actual time=449.636..449.637 rows=1 loops=3) -> Parallel Seq Scan on tv tv_1 (cost=0.00..42602.67 rows=829167 width=4) (actual time=0.013..264.211 rows=663334 loops=3) InitPlan 2 (returns $2) -> Limit (cost=1000.00..23337.89 rows=1 width=4) (actual time=633.139..633.139 rows=1 loops=1) -> Gather (cost=1000.00..45675.79 rows=2 width=4) (actual time=633.135..633.135 rows=1 loops=1) Workers Planned: 2 Params Evaluated: $1 Workers Launched: 2 -> Parallel Seq Scan on tv tv_2 (cost=0.00..44675.59 rows=1 width=4) (actual time=144.129..144.129 rows=0 loops=3) Filter: ((n IS NOT NULL) AND (n = $1)) Rows Removed by Filter: 663321 -> Gather Merge (cost=221477.21..429473.44 rows=1658334 width=109) (actual time=786.674..4195.436 rows=1990001 loops=1) Workers Planned: 2 Workers Launched: 2 -> Partial GroupAggregate (cost=220477.19..237060.53 rows=829167 width=109) (actual time=760.039..2449.608 rows=663334 loops=3) Group Key: tv.n, tv.n1 -> Sort (cost=220477.19..222550.11 rows=829167 width=105) (actual time=760.018..1185.822 rows=663334 loops=3) Sort Key: tv.n, tv.n1 Sort Method: external merge Disk: 75824kB Worker 0: Sort Method: external merge Disk: 71256kB Worker 1: Sort Method: external merge Disk: 77136kB -> Parallel Seq Scan on tv (cost=0.00..42602.67 rows=829167 width=105) (actual time=0.021..213.174 rows=663334 loops=3) Planning time: 2.022 ms Execution time: 7487.239 ms (35 rows) ---TestCase 2-Subquery compared with having clause explain analyze SELECT n,n1 FROM tv GROUP BY n,n1 HAVING n = (select max(n) from tv where n=(select min(n) from tv)) ORDER BY n,n1; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------ Group (cost=114689.52..114689.53 rows=2 width=105) (actual time=773.461..773.462 rows=1 loops=1) Group Key: tv.n, tv.n1 InitPlan 3 (returns $4) -> Result (cost=69013.71..69013.72 rows=1 width=4) (actual time=612.539..612.540 rows=1 loops=1) InitPlan 1 (returns $1) -> Finalize Aggregate (cost=45675.80..45675.81 rows=1 width=4) (actual time=426.191..426.191 rows=1 loops=1) -> Gather (cost=45675.59..45675.80 rows=2 width=4) (actual time=426.132..426.184 rows=3 loops=1) Workers Planned: 2 Workers Launched: 2 -> Partial Aggregate (cost=44675.59..44675.60 rows=1 width=4) (actual time=422.578..422.578 rows=1 loops=3) -> Parallel Seq Scan on tv tv_1 (cost=0.00..42602.67 rows=829167 width=4) (actual time=0.011..251.793 rows=663334 loops=3) InitPlan 2 (returns $2) -> Limit (cost=1000.00..23337.89 rows=1 width=4) (actual time=612.531..612.532 rows=1 loops=1) -> Gather (cost=1000.00..45675.79 rows=2 width=4) (actual time=612.529..612.529 rows=1 loops=1) Workers Planned: 2 Params Evaluated: $1 Workers Launched: 2 -> Parallel Seq Scan on tv tv_2 (cost=0.00..44675.59 rows=1 width=4) (actual time=148.276..148.276 rows=0 loops=3) Filter: ((n IS NOT NULL) AND (n = $1)) Rows Removed by Filter: 663321 -> Sort (cost=45675.80..45675.80 rows=2 width=105) (actual time=773.453..773.454 rows=1 loops=1) Sort Key: tv.n1 Sort Method: quicksort Memory: 25kB -> Gather (cost=1000.00..45675.79 rows=2 width=105) (actual time=698.889..773.434 rows=1 loops=1) Workers Planned: 2 Params Evaluated: $4 Workers Launched: 2 -> Parallel Seq Scan on tv (cost=0.00..44675.59 rows=1 width=105) (actual time=132.930..157.765 rows=0 loops=3) Filter: (n = $4) Rows Removed by Filter: 663333 Planning time: 0.324 ms Execution time: 774.657 ms (32 rows) ---Testcase 3 -Subquery with aggregate function in where / having clause with multiple conditions explain analyze SELECT lower(n1), count(n) FROM tv where 1=(select max(n) from tv) group by lower(n1) HAVING count(*) = (select max(n) from tv) and min(n) = (select max(n) from tv) ORDER BY lower(n1); QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------------------- Finalize GroupAggregate (cost=193068.09..193068.17 rows=2 width=40) (actual time=403.716..403.716 rows=0 loops=1) Group Key: (lower((tv.n1)::text)) Filter: ((count(*) = $3) AND (min(tv.n) = $5)) InitPlan 1 (returns $1) -> Finalize Aggregate (cost=45675.80..45675.81 rows=1 width=4) (actual time=399.384..399.384 rows=1 loops=1) -> Gather (cost=45675.59..45675.80 rows=2 width=4) (actual time=399.335..399.375 rows=3 loops=1) Workers Planned: 2 Workers Launched: 2 -> Partial Aggregate (cost=44675.59..44675.60 rows=1 width=4) (actual time=396.808..396.808 rows=1 loops=3) -> Parallel Seq Scan on tv tv_1 (cost=0.00..42602.67 rows=829167 width=4) (actual time=0.007..237.584 rows=663334 loops=3) InitPlan 2 (returns $3) -> Finalize Aggregate (cost=45675.80..45675.81 rows=1 width=4) (never executed) -> Gather (cost=45675.59..45675.80 rows=2 width=4) (never executed) Workers Planned: 2 Workers Launched: 0 -> Partial Aggregate (cost=44675.59..44675.60 rows=1 width=4) (never executed) -> Parallel Seq Scan on tv tv_2 (cost=0.00..42602.67 rows=829167 width=4) (never executed) InitPlan 3 (returns $5) -> Finalize Aggregate (cost=45675.80..45675.81 rows=1 width=4) (never executed) -> Gather (cost=45675.59..45675.80 rows=2 width=4) (never executed) Workers Planned: 2 Workers Launched: 0 -> Partial Aggregate (cost=44675.59..44675.60 rows=1 width=4) (never executed) -> Parallel Seq Scan on tv tv_3 (cost=0.00..42602.67 rows=829167 width=4) (never executed) -> Sort (cost=56040.65..56040.66 rows=4 width=52) (actual time=403.714..403.714 rows=0 loops=1) Sort Key: (lower((tv.n1)::text)) Sort Method: quicksort Memory: 25kB -> Gather (cost=56040.18..56040.61 rows=4 width=52) (actual time=403.696..403.696 rows=0 loops=1) Workers Planned: 2 Params Evaluated: $1 Workers Launched: 2 -> Partial HashAggregate (cost=55040.18..55040.21 rows=2 width=52) (actual time=0.004..0.004 rows=0 loops=3) Group Key: lower((tv.n1)::text) -> Result (cost=0.00..46748.51 rows=829167 width=36) (actual time=0.002..0.002 rows=0 loops=3) One-Time Filter: (1 = $1) -> Parallel Seq Scan on tv (cost=0.00..42602.67 rows=829167 width=105) (never executed) Planning time: 0.396 ms Execution time: 404.581 ms (38 rows) ---Testcase 4 - Subquery with aggregate functions using different operator in order by clause explain analyze SELECT DISTINCT ON (n, n1) n , n1 FROM tv where n =(select min(n) from tv) ORDER BY n using <, n1 using <, n1 using <; QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------------------- Unique (cost=91351.61..91351.62 rows=2 width=105) (actual time=542.572..542.573 rows=1 loops=1) InitPlan 1 (returns $1) -> Finalize Aggregate (cost=45675.80..45675.81 rows=1 width=4) (actual time=400.802..400.802 rows=1 loops=1) -> Gather (cost=45675.59..45675.80 rows=2 width=4) (actual time=400.780..400.791 rows=3 loops=1) Workers Planned: 2 Workers Launched: 2 -> Partial Aggregate (cost=44675.59..44675.60 rows=1 width=4) (actual time=398.823..398.823 rows=1 loops=3) -> Parallel Seq Scan on tv tv_1 (cost=0.00..42602.67 rows=829167 width=4) (actual time=0.007..238.001 rows=663334 loops=3) -> Sort (cost=45675.80..45675.80 rows=2 width=105) (actual time=542.568..542.568 rows=1 loops=1) Sort Key: tv.n1 Sort Method: quicksort Memory: 25kB -> Gather (cost=1000.00..45675.79 rows=2 width=105) (actual time=477.330..542.547 rows=1 loops=1) Workers Planned: 2 Params Evaluated: $1 Workers Launched: 2 -> Parallel Seq Scan on tv (cost=0.00..44675.59 rows=1 width=105) (actual time=117.355..139.080 rows=0 loops=3) Filter: (n = $1) Rows Removed by Filter: 663333 Planning time: 0.175 ms Execution time: 543.592 ms (20 rows) ---Testcase 5- Subquery with aggregagte functions used in order by explain analyze SELECT count(*) FROM tv x, tv y WHERE x.n = y.n GROUP BY x.n ORDER BY (select min(n) from tv where n1=(select lower(n1) from tv limit 1)); QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------------- GroupAggregate (cost=681411.68..818191.69 rows=1022628 width=16) (actual time=5273.380..12790.336 rows=1000000 loops=1) Group Key: x.n InitPlan 2 (returns $2) -> Finalize Aggregate (cost=47759.12..47759.13 rows=1 width=4) (actual time=1860.665..1860.665 rows=1 loops=1) InitPlan 1 (returns $0) -> Limit (cost=0.00..0.03 rows=1 width=32) (actual time=0.139..0.139 rows=1 loops=1) -> Seq Scan on tv (cost=0.00..64161.01 rows=1990001 width=32) (actual time=0.137..0.137 rows=1 loops=1) -> Gather (cost=47758.87..47759.08 rows=2 width=4) (actual time=1860.462..1860.633 rows=3 loops=1) Workers Planned: 2 Params Evaluated: $0 Workers Launched: 2 -> Partial Aggregate (cost=46758.87..46758.88 rows=1 width=4) (actual time=1855.575..1855.575 rows=1 loops=3) -> Parallel Seq Scan on tv tv_1 (cost=0.00..46748.51 rows=4146 width=4) (actual time=0.033..1763.368 rows=333333 loops=3) Filter: ((n1)::text = $0) Rows Removed by Filter: 330000 -> Merge Join (cost=633652.55..732299.10 rows=5581436 width=4) (actual time=3412.691..9090.067 rows=3970003 loops=1) Merge Cond: (x.n = y.n) -> Sort (cost=316826.27..321801.28 rows=1990001 width=4) (actual time=1731.024..2443.904 rows=1990001 loops=1) Sort Key: x.n Sort Method: external merge Disk: 27336kB -> Seq Scan on tv x (cost=0.00..54211.01 rows=1990001 width=4) (actual time=0.007..698.718 rows=1990001 loops=1) -> Materialize (cost=316826.27..326776.28 rows=1990001 width=4) (actual time=1681.645..3715.883 rows=3970002 loops=1) -> Sort (cost=316826.27..321801.28 rows=1990001 width=4) (actual time=1681.636..2408.457 rows=1990001 loops=1) Sort Key: y.n Sort Method: external merge Disk: 27336kB -> Seq Scan on tv y (cost=0.00..54211.01 rows=1990001 width=4) (actual time=0.020..675.987 rows=1990001 loops=1) Planning time: 0.443 ms Execution time: 13041.489 ms (28 rows) --Testcase 6- Aggregate function in group by/ order by clause explain verbose SELECT count(*) FROM tv x, tv y WHERE x.n = y.n GROUP BY (select min(n) from tv where n1 not in (select lower(n1) from tv)) ORDER BY (select min(n) from tv where n1=(select lower(n1) from tv)); QUERY PLAN ----------------------------------------------------------------------------------------------------------------- Finalize GroupAggregate (cost=38428006370.54..38428238031.34 rows=1 width=16) Output: count(*), $2, ($5) Group Key: ($5) InitPlan 2 (returns $2) -> Finalize Aggregate (cost=111920.10..111920.11 rows=1 width=4) Output: min(tv_1.n) InitPlan 1 (returns $0) -> Seq Scan on public.tv (cost=0.00..64161.01 rows=1990001 width=32) Output: lower((tv.n1)::text) -> Gather (cost=47758.87..47759.08 rows=2 width=4) Output: (PARTIAL min(tv_1.n)) Workers Planned: 2 Params Evaluated: $0 -> Partial Aggregate (cost=46758.87..46758.88 rows=1 width=4) Output: PARTIAL min(tv_1.n) -> Parallel Seq Scan on public.tv tv_1 (cost=0.00..46748.51 rows=4146 width=4) Output: tv_1.n Filter: ((tv_1.n1)::text = $0) InitPlan 4 (returns $5) -> Finalize Aggregate (cost=38427806590.39..38427806590.40 rows=1 width=4) Output: min(tv_3.n) -> Gather (cost=38427806590.18..38427806590.39 rows=2 width=4) Output: (PARTIAL min(tv_3.n)) Workers Planned: 2 -> Partial Aggregate (cost=38427805590.18..38427805590.19 rows=1 width=4) Output: PARTIAL min(tv_3.n) -> Parallel Seq Scan on public.tv tv_3 (cost=0.00..38427804553.72 rows=414583 width=4) Output: tv_3.n Filter: (NOT (SubPlan 3)) SubPlan 3 -> Materialize (cost=0.00..87715.02 rows=1990001 width=32) Output: (lower((tv_2.n1)::text)) -> Seq Scan on public.tv tv_2 (cost=0.00..64161.01 rows=1990001 width=32) Output: lower((tv_2.n1)::text) -> Gather (cost=87860.02..319520.80 rows=2 width=12) Output: ($5), (PARTIAL count(*)) Workers Planned: 2 Params Evaluated: $5 -> Partial GroupAggregate (cost=86860.02..318520.60 rows=1 width=12) Output: ($5), PARTIAL count(*) Group Key: $5 -> Hash Join (cost=86860.02..306892.60 rows=2325598 width=4) Output: $5 Hash Cond: (x.n = y.n) -> Parallel Seq Scan on public.tv x (cost=0.00..42602.67 rows=829167 width=4) Output: x.n -> Hash (cost=54211.01..54211.01 rows=1990001 width=4) Output: y.n -> Seq Scan on public.tv y (cost=0.00..54211.01 rows=1990001 width=4) Output: y.n (50 rows) --Testcase 7 - Aggregate function in where / group by/ order by clause explain verbose SELECT count(*) FROM tv x, tv y WHERE x.n = (select min(n) from tv) GROUP BY (select min(n) from tv where n1 not in (select lower(n1) from tv)) ORDER BY (select min(n) from tv where n1=(select lower(n1) from tv)); QUERY PLAN ----------------------------------------------------------------------------------------------------------------- Finalize GroupAggregate (cost=38427965186.33..38428092264.83 rows=1 width=16) Output: count(*), $2, ($5) Group Key: ($5) InitPlan 2 (returns $2) -> Finalize Aggregate (cost=111920.10..111920.11 rows=1 width=4) Output: min(tv_1.n) InitPlan 1 (returns $0) -> Seq Scan on public.tv (cost=0.00..64161.01 rows=1990001 width=32) Output: lower((tv.n1)::text) -> Gather (cost=47758.87..47759.08 rows=2 width=4) Output: (PARTIAL min(tv_1.n)) Workers Planned: 2 Params Evaluated: $0 -> Partial Aggregate (cost=46758.87..46758.88 rows=1 width=4) Output: PARTIAL min(tv_1.n) -> Parallel Seq Scan on public.tv tv_1 (cost=0.00..46748.51 rows=4146 width=4) Output: tv_1.n Filter: ((tv_1.n1)::text = $0) InitPlan 4 (returns $5) -> Finalize Aggregate (cost=38427806590.39..38427806590.40 rows=1 width=4) Output: min(tv_3.n) -> Gather (cost=38427806590.18..38427806590.39 rows=2 width=4) Output: (PARTIAL min(tv_3.n)) Workers Planned: 2 -> Partial Aggregate (cost=38427805590.18..38427805590.19 rows=1 width=4) Output: PARTIAL min(tv_3.n) -> Parallel Seq Scan on public.tv tv_3 (cost=0.00..38427804553.72 rows=414583 width=4) Output: tv_3.n Filter: (NOT (SubPlan 3)) SubPlan 3 -> Materialize (cost=0.00..87715.02 rows=1990001 width=32) Output: (lower((tv_2.n1)::text)) -> Seq Scan on public.tv tv_2 (cost=0.00..64161.01 rows=1990001 width=32) Output: lower((tv_2.n1)::text) InitPlan 5 (returns $7) -> Finalize Aggregate (cost=45675.80..45675.81 rows=1 width=4) Output: min(tv_4.n) -> Gather (cost=45675.59..45675.80 rows=2 width=4) Output: (PARTIAL min(tv_4.n)) Workers Planned: 2 -> Partial Aggregate (cost=44675.59..44675.60 rows=1 width=4) Output: PARTIAL min(tv_4.n) -> Parallel Seq Scan on public.tv tv_4 (cost=0.00..42602.67 rows=829167 width=4) Output: tv_4.n -> Gather (cost=1000.00..128078.49 rows=2 width=12) Output: ($5), (PARTIAL count(*)) Workers Planned: 2 Params Evaluated: $5, $7 -> Partial GroupAggregate (cost=0.00..127078.29 rows=1 width=12) Output: ($5), PARTIAL count(*) Group Key: $5 -> Nested Loop (cost=0.00..118786.61 rows=1658334 width=4) Output: $5 -> Parallel Seq Scan on public.tv x (cost=0.00..44675.59 rows=1 width=0) Output: x.n, x.n1 Filter: (x.n = $7) -> Seq Scan on public.tv y (cost=0.00..54211.01 rows=1990001 width=0) Output: y.n, y.n1 (58 rows) ---Testcase 8- Subquery having multiple comparision in where clause against aggregate functions also having union all explain analyze select count(*) from tv where (n,n1)=(select max(n),lower(n1) from tv group by n1 limit 1) union all select 5; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Append (cost=97570.52..97570.56 rows=2 width=8) (actual time=1460.956..1460.961 rows=2 loops=1) -> Aggregate (cost=97570.52..97570.53 rows=1 width=8) (actual time=1460.954..1460.955 rows=1 loops=1) InitPlan 1 (returns $1,$2) -> Limit (cost=47748.97..47749.00 rows=1 width=137) (actual time=1284.638..1284.638 rows=1 loops=1) -> Finalize GroupAggregate (cost=47748.97..47749.03 rows=2 width=137) (actual time=1284.636..1284.636 rows=1 loops=1) Group Key: tv_1.n1 -> Sort (cost=47748.97..47748.98 rows=4 width=105) (actual time=1284.616..1284.617 rows=4 loops=1) Sort Key: tv_1.n1 Sort Method: quicksort Memory: 25kB -> Gather (cost=47748.51..47748.93 rows=4 width=105) (actual time=1284.586..1284.595 rows=6 loops=1) Workers Planned: 2 Workers Launched: 2 -> Partial HashAggregate (cost=46748.51..46748.53 rows=2 width=105) (actual time=1279.164..1279.166 rows=2 loops=3) Group Key: tv_1.n1 -> Parallel Seq Scan on tv tv_1 (cost=0.00..42602.67 rows=829167 width=105) (actual time=0.014..300.670 rows=663334 loops=3) -> Gather (cost=1000.00..49821.52 rows=1 width=0) (actual time=1460.946..1460.948 rows=1 loops=1) Workers Planned: 2 Params Evaluated: $1, $2 Workers Launched: 2 -> Parallel Seq Scan on tv (cost=0.00..48821.42 rows=1 width=0) (actual time=147.616..172.413 rows=0 loops=3) Filter: ((n = $1) AND ((n1)::text = $2)) Rows Removed by Filter: 663333 -> Subquery Scan on "*SELECT* 2" (cost=0.00..0.02 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=1) -> Result (cost=0.00..0.01 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=1) Planning time: 0.242 ms Execution time: 1461.949 ms (26 rows) explain analyze select count(*) from tv where (n,n1,1) in (select max(n),lower(n1),2 from tv group by n1) union all select min(n) from tv where n=(select max(n) from tv); QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------------------------------- Append (cost=48059.59..117073.34 rows=2 width=8) (actual time=4.791..440.906 rows=2 loops=1) -> Aggregate (cost=48059.59..48059.60 rows=1 width=8) (actual time=4.790..4.790 rows=1 loops=1) -> Gather (cost=1000.04..48059.58 rows=1 width=0) (actual time=4.787..4.787 rows=0 loops=1) Workers Planned: 2 Workers Launched: 2 -> Hash Semi Join (cost=0.04..47059.48 rows=1 width=0) (actual time=0.082..0.082 rows=0 loops=3) Hash Cond: ((tv.n = "ANY_subquery".max) AND ((tv.n1)::text = "ANY_subquery".lower)) -> Parallel Seq Scan on tv (cost=0.00..42602.67 rows=829167 width=105) (actual time=0.007..0.007 rows=1 loops=3) -> Hash (cost=0.03..0.03 rows=1 width=36) (actual time=0.004..0.004 rows=0 loops=3) Buckets: 1024 Batches: 1 Memory Usage: 8kB -> Subquery Scan on "ANY_subquery" (cost=0.00..0.03 rows=1 width=36) (actual time=0.003..0.003 rows=0 loops=3) -> HashAggregate (cost=0.00..0.01 rows=1 width=444) (actual time=0.002..0.002 rows=0 loops=3) Group Key: n1 -> Result (cost=0.00..0.00 rows=0 width=408) (actual time=0.001..0.001 rows=0 loops=3) One-Time Filter: false -> Subquery Scan on "*SELECT* 2" (cost=69013.71..69013.73 rows=1 width=8) (actual time=436.111..436.112 rows=1 loops=1) -> Result (cost=69013.71..69013.72 rows=1 width=4) (actual time=436.109..436.110 rows=1 loops=1) InitPlan 1 (returns $1) -> Finalize Aggregate (cost=45675.80..45675.81 rows=1 width=4) (actual time=433.239..433.240 rows=1 loops=1) -> Gather (cost=45675.59..45675.80 rows=2 width=4) (actual time=433.223..433.232 rows=3 loops=1) Workers Planned: 2 Workers Launched: 2 -> Partial Aggregate (cost=44675.59..44675.60 rows=1 width=4) (actual time=430.709..430.709 rows=1 loops=3) -> Parallel Seq Scan on tv tv_1 (cost=0.00..42602.67 rows=829167 width=4) (actual time=0.008..256.574 rows=663334 loops=3) InitPlan 2 (returns $2) -> Limit (cost=1000.00..23337.89 rows=1 width=4) (actual time=436.105..436.105 rows=1 loops=1) -> Gather (cost=1000.00..45675.79 rows=2 width=4) (actual time=436.102..436.102 rows=1 loops=1) Workers Planned: 2 Params Evaluated: $1 Workers Launched: 2 -> Parallel Seq Scan on tv tv_2 (cost=0.00..44675.59 rows=1 width=4) (actual time=145.478..145.478 rows=1 loops=3) Filter: ((n IS NOT NULL) AND (n = $1)) Rows Removed by Filter: 663309 Planning time: 0.639 ms Execution time: 756.022 ms (35 rows) ---Testcase 9 - Subquery in where clause with IN operator against aggregate functions in where condition in subquery explain analyze SELECT '' AS six, n AS "Correlated Field", n1 AS "Second Field" FROM tv WHERE n IN (SELECT n FROM tv WHERE CAST(n AS float) = (select min(n) from tv)); QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------------------- Hash Semi Join (cost=96616.61..161741.80 rows=32309 width=137) (actual time=1165.186..1705.967 rows=1 loops=1) Hash Cond: (tv.n = tv_1.n) InitPlan 1 (returns $1) -> Finalize Aggregate (cost=45675.80..45675.81 rows=1 width=4) (actual time=454.122..454.122 rows=1 loops=1) -> Gather (cost=45675.59..45675.80 rows=2 width=4) (actual time=454.101..454.113 rows=3 loops=1) Workers Planned: 2 Workers Launched: 2 -> Partial Aggregate (cost=44675.59..44675.60 rows=1 width=4) (actual time=450.267..450.267 rows=1 loops=3) -> Parallel Seq Scan on tv tv_2 (cost=0.00..42602.67 rows=829167 width=4) (actual time=0.012..267.096 rows=663334 loops=3) -> Seq Scan on tv (cost=0.00..54211.01 rows=1990001 width=105) (actual time=0.012..506.000 rows=1990001 loops=1) -> Hash (cost=50816.42..50816.42 rows=9950 width=4) (actual time=618.986..618.986 rows=1 loops=1) Buckets: 16384 Batches: 1 Memory Usage: 129kB -> Gather (cost=1000.00..50816.42 rows=9950 width=4) (actual time=618.942..618.945 rows=1 loops=1) Workers Planned: 2 Params Evaluated: $1 Workers Launched: 2 -> Parallel Seq Scan on tv tv_1 (cost=0.00..48821.42 rows=4146 width=4) (actual time=137.606..162.166 rows=0 loops=3) Filter: ((n)::double precision = ($1)::double precision) Rows Removed by Filter: 663333 Planning time: 0.698 ms Execution time: 1706.335 ms (21 rows) explain analyze select 5/(select max(n) from tv where n in (select min(n) from tv where n=(select avg(n) from tv)) and n1 in (select lower(n1) from tv)); QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Result (cost=231707.04..231707.05 rows=1 width=4) (actual time=8486.380..8486.381 rows=1 loops=1) InitPlan 2 (returns $3) -> Aggregate (cost=231707.03..231707.04 rows=1 width=4) (actual time=8486.373..8486.373 rows=1 loops=1) -> Hash Join (cost=152620.96..231707.02 rows=1 width=4) (actual time=8486.368..8486.368 rows=0 loops=1) Hash Cond: ((tv_1.n1)::text = lower((tv_2.n1)::text)) -> Nested Loop (cost=93434.90..172520.94 rows=2 width=105) (actual time=1893.552..1893.552 rows=0 loops=1) Join Filter: (tv_1.n = (min(tv_3.n))) Rows Removed by Join Filter: 1990001 -> Finalize Aggregate (cost=93434.90..93434.91 rows=1 width=4) (actual time=843.928..843.929 rows=1 loops=1) InitPlan 1 (returns $1) -> Finalize Aggregate (cost=45675.81..45675.82 rows=1 width=32) (actual time=443.386..443.386 rows=1 loops=1) -> Gather (cost=45675.59..45675.80 rows=2 width=32) (actual time=443.353..443.359 rows=3 loops=1) Workers Planned: 2 Workers Launched: 2 -> Partial Aggregate (cost=44675.59..44675.60 rows=1 width=32) (actual time=439.436..439.437 rows=1 loops=3) -> Parallel Seq Scan on tv (cost=0.00..42602.67 rows=829167 width=4) (actual time=0.011..256.361 rows=663334 loops=3) -> Gather (cost=47758.87..47759.08 rows=2 width=4) (actual time=843.909..843.923 rows=3 loops=1) Workers Planned: 2 Params Evaluated: $1 Workers Launched: 2 -> Partial Aggregate (cost=46758.87..46758.88 rows=1 width=4) (actual time=396.960..396.960 rows=1 loops=3) -> Parallel Seq Scan on tv tv_3 (cost=0.00..46748.51 rows=4146 width=4) (actual time=396.951..396.951 rows=0 loops=3) Filter: ((n)::numeric = $1) Rows Removed by Filter: 663334 -> Seq Scan on tv tv_1 (cost=0.00..54211.01 rows=1990001 width=105) (actual time=0.007..521.840 rows=1990001 loops=1) -> Hash (cost=59186.03..59186.03 rows=2 width=101) (actual time=6592.808..6592.808 rows=2 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 9kB -> HashAggregate (cost=59186.01..59186.03 rows=2 width=101) (actual time=6592.792..6592.793 rows=2 loops=1) Group Key: lower((tv_2.n1)::text) -> Seq Scan on tv tv_2 (cost=0.00..54211.01 rows=1990001 width=101) (actual time=0.017..5656.215 rows=1990001 loops=1) Planning time: 0.510 ms Execution time: 8486.792 ms (32 rows) ---Testcase 10 - Inline subquery and aggregate function in where clause of inline suquery Explain analyze select count(distinct n) from (select n from tv a where n IN (select distinct avg(n) from tv b where n=(select avg(9)))) ss; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------ Aggregate (cost=127290.45..127290.46 rows=1 width=8) (actual time=2039.094..2039.094 rows=1 loops=1) -> Hash Join (cost=47759.16..127265.57 rows=9950 width=4) (actual time=1238.715..2039.062 rows=1 loops=1) Hash Cond: ((a.n)::numeric = (avg(b.n))) -> Seq Scan on tv a (cost=0.00..54211.01 rows=1990001 width=4) (actual time=0.013..509.550 rows=1990001 loops=1) -> Hash (cost=47759.15..47759.15 rows=1 width=32) (actual time=424.723..424.723 rows=1 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 9kB -> Unique (cost=47759.13..47759.14 rows=1 width=32) (actual time=424.716..424.717 rows=1 loops=1) InitPlan 1 (returns $0) -> Aggregate (cost=0.02..0.03 rows=1 width=32) (actual time=0.017..0.017 rows=1 loops=1) -> Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.001..0.002 rows=1 loops=1) -> Sort (cost=47759.11..47759.11 rows=1 width=32) (actual time=424.714..424.714 rows=1 loops=1) Sort Key: (avg(b.n)) Sort Method: quicksort Memory: 25kB -> Finalize Aggregate (cost=47759.09..47759.10 rows=1 width=32) (actual time=424.657..424.658 rows=1 loops=1) -> Gather (cost=47758.87..47759.08 rows=2 width=32) (actual time=424.571..424.645 rows=3 loops=1) Workers Planned: 2 Params Evaluated: $0 Workers Launched: 2 -> Partial Aggregate (cost=46758.87..46758.88 rows=1 width=32) (actual time=420.411..420.411 rows=1 loops=3) -> Parallel Seq Scan on tv b (cost=0.00..46748.51 rows=4146 width=4) (actual time=355.577..420.379 rows=0 loops=3) Filter: ((n)::numeric = $0) Rows Removed by Filter: 663333 Planning time: 0.618 ms Execution time: 2039.415 ms (24 rows) explain analyze select * from (select max(n) from tv where n=(select max(n) from tv) and n is null) d; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------- Result (cost=91351.50..91351.51 rows=1 width=4) (actual time=609.864..609.864 rows=1 loops=1) InitPlan 1 (returns $1) -> Finalize Aggregate (cost=45675.80..45675.81 rows=1 width=4) (actual time=442.499..442.499 rows=1 loops=1) -> Gather (cost=45675.59..45675.80 rows=2 width=4) (actual time=442.471..442.494 rows=3 loops=1) Workers Planned: 2 Workers Launched: 2 -> Partial Aggregate (cost=44675.59..44675.60 rows=1 width=4) (actual time=437.989..437.989 rows=1 loops=3) -> Parallel Seq Scan on tv (cost=0.00..42602.67 rows=829167 width=4) (actual time=0.013..258.645 rows=663334 loops=3) InitPlan 2 (returns $2) -> Limit (cost=1000.00..45675.69 rows=1 width=4) (actual time=609.859..609.859 rows=0 loops=1) -> Gather (cost=1000.00..45675.69 rows=1 width=4) (actual time=609.857..609.857 rows=0 loops=1) Workers Planned: 2 Params Evaluated: $1 Workers Launched: 2 -> Parallel Seq Scan on tv tv_1 (cost=0.00..44675.59 rows=1 width=4) (actual time=162.985..162.985 rows=0 loops=3) Filter: ((n IS NOT NULL) AND (n IS NULL) AND (n = $1)) Rows Removed by Filter: 663334 Planning time: 0.190 ms Execution time: 610.811 ms (19 rows) ---Testcase 11 - Subselect with multiple columns comparisions in where clause against subquery Explain analyze select (select (select max(n) from tv where (n,n,n,n,n,n,n,n,n,n,n,n,n,n,n,n,n,n,n,n,n,n)=(select 1,2,3,4,5,6,7,8,9,1,2,3,3,4,5,6,7,8,9,1,1,1))); QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Result (cost=23337.98..23337.99 rows=1 width=4) (actual time=6.777..6.777 rows=1 loops=1) InitPlan 4 (returns $25) -> Result (cost=23337.97..23337.98 rows=1 width=4) (actual time=6.774..6.774 rows=1 loops=1) InitPlan 3 (returns $24) -> Result (cost=23337.96..23337.97 rows=1 width=4) (actual time=6.771..6.771 rows=1 loops=1) InitPlan 1 (returns $0,$1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17,$18,$19,$20,$21) -> Result (cost=0.00..0.01 rows=1 width=88) (actual time=0.001..0.002 rows=1 loops=1) InitPlan 2 (returns $22) -> Limit (cost=1000.05..23337.95 rows=1 width=4) (actual time=6.769..6.769 rows=0 loops=1) -> Gather (cost=1000.05..45675.84 rows=2 width=4) (actual time=6.766..6.766 rows=0 loops=1) Workers Planned: 2 Params Evaluated: $0, $1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20, $21 Workers Launched: 2 -> Result (cost=0.05..44675.64 rows=1 width=4) (actual time=0.003..0.003 rows=0 loops=3) One-Time Filter: (($0 = $21) AND ($1 = $21) AND ($2 = $21) AND ($3 = $21) AND ($4 = $21) AND ($5 = $21) AND ($6 = $21) AND ($7 = $21) AND ($8 = $21) AND ($9 = $21) AND ($10 = $21) AND ($11 = $21) AND ($12 = $21) AND ($13 = $21) AND ($14 = $21) AND ($15 = $21) AND ($16 = $21) AND ($17 = $21) AND ($18 = $21) AND ($19 = $21) AND ($20 = $21)) -> Parallel Seq Scan on tv (cost=0.05..44675.64 rows=1 width=4) (never executed) Filter: ((n IS NOT NULL) AND (n = $21)) Planning time: 0.829 ms Execution time: 8.018 ms (19 rows) ---Testcase 12 - Multiple conditions in where clause explain analyze select max(n) from tv where (n=(select max(n) from tv) and n=(select min(n) from tv)); QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------------------- Result (cost=114689.52..114689.53 rows=1 width=4) (actual time=872.636..872.637 rows=1 loops=1) InitPlan 1 (returns $1) -> Finalize Aggregate (cost=45675.80..45675.81 rows=1 width=4) (actual time=457.179..457.179 rows=1 loops=1) -> Gather (cost=45675.59..45675.80 rows=2 width=4) (actual time=457.132..457.169 rows=3 loops=1) Workers Planned: 2 Workers Launched: 2 -> Partial Aggregate (cost=44675.59..44675.60 rows=1 width=4) (actual time=453.360..453.360 rows=1 loops=3) -> Parallel Seq Scan on tv (cost=0.00..42602.67 rows=829167 width=4) (actual time=0.015..271.289 rows=663334 loops=3) InitPlan 2 (returns $3) -> Finalize Aggregate (cost=45675.80..45675.81 rows=1 width=4) (actual time=412.022..412.022 rows=1 loops=1) -> Gather (cost=45675.59..45675.80 rows=2 width=4) (actual time=411.926..412.002 rows=3 loops=1) Workers Planned: 2 Workers Launched: 2 -> Partial Aggregate (cost=44675.59..44675.60 rows=1 width=4) (actual time=409.199..409.200 rows=1 loops=3) -> Parallel Seq Scan on tv tv_1 (cost=0.00..42602.67 rows=829167 width=4) (actual time=0.010..243.988 rows=663334 loops=3) InitPlan 3 (returns $4) -> Limit (cost=1000.00..23337.90 rows=1 width=4) (actual time=872.632..872.632 rows=0 loops=1) -> Gather (cost=1000.00..45675.79 rows=2 width=4) (actual time=872.630..872.630 rows=0 loops=1) Workers Planned: 2 Params Evaluated: $1, $3 Workers Launched: 2 -> Result (cost=0.00..44675.59 rows=1 width=4) (actual time=0.002..0.002 rows=0 loops=3) One-Time Filter: ($1 = $3) -> Parallel Seq Scan on tv tv_2 (cost=0.00..44675.59 rows=1 width=4) (never executed) Filter: ((n IS NOT NULL) AND (n = $3)) Planning time: 0.377 ms Execution time: 873.322 ms (27 rows) ---Testcase 13 -Subquery used in Group by explain analyze select count(*) from tv group by (select n from tv limit 1); QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------- Finalize GroupAggregate (cost=1000.03..47748.76 rows=1 width=12) (actual time=418.451..418.451 rows=1 loops=1) Group Key: ($0) InitPlan 1 (returns $0) -> Limit (cost=0.00..0.03 rows=1 width=4) (actual time=0.015..0.015 rows=1 loops=1) -> Seq Scan on tv tv_1 (cost=0.00..54211.01 rows=1990001 width=4) (actual time=0.007..0.007 rows=1 loops=1) -> Gather (cost=1000.00..47748.72 rows=2 width=12) (actual time=418.410..418.441 rows=3 loops=1) Workers Planned: 2 Params Evaluated: $0 Workers Launched: 2 -> Partial GroupAggregate (cost=0.00..46748.52 rows=1 width=12) (actual time=415.458..415.458 rows=1 loops=3) Group Key: $0 -> Parallel Seq Scan on tv (cost=0.00..42602.67 rows=829167 width=4) (actual time=0.007..223.412 rows=663334 loops=3) Planning time: 0.157 ms Execution time: 419.449 ms (14 rows) explain analyze select count(*) from tv group by (select n from tv where n not in (select max(n) from tv) limit 1); QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------ Finalize GroupAggregate (cost=46675.88..93424.61 rows=1 width=12) (actual time=823.687..823.688 rows=1 loops=1) Group Key: ($2) InitPlan 2 (returns $2) -> Limit (cost=45675.82..45675.88 rows=1 width=4) (actual time=423.493..423.494 rows=1 loops=1) -> Seq Scan on tv tv_2 (cost=45675.82..104861.83 rows=995000 width=4) (actual time=423.491..423.491 rows=1 loops=1) Filter: (NOT (hashed SubPlan 1)) SubPlan 1 -> Finalize Aggregate (cost=45675.80..45675.81 rows=1 width=4) (actual time=423.464..423.464 rows=1 loops=1) -> Gather (cost=45675.59..45675.80 rows=2 width=4) (actual time=423.444..423.456 rows=3 loops=1) Workers Planned: 2 Workers Launched: 2 -> Partial Aggregate (cost=44675.59..44675.60 rows=1 width=4) (actual time=420.934..420.934 rows=1 loops=3) -> Parallel Seq Scan on tv tv_1 (cost=0.00..42602.67 rows=829167 width=4) (actual time=0.006..251.443 rows=663334 loops=3) -> Gather (cost=1000.00..47748.72 rows=2 width=12) (actual time=823.648..823.678 rows=3 loops=1) Workers Planned: 2 Params Evaluated: $2 Workers Launched: 2 -> Partial GroupAggregate (cost=0.00..46748.52 rows=1 width=12) (actual time=397.336..397.336 rows=1 loops=3) Group Key: $2 -> Parallel Seq Scan on tv (cost=0.00..42602.67 rows=829167 width=4) (actual time=0.010..213.960 rows=663334 loops=3) Planning time: 0.200 ms Execution time: 824.721 ms (22 rows) ---Testcase 14 - Subquery used in Order by explain analyze Select count(*) from tv order by (select max(n) from tv where n=(select (select avg(n) from tv))); QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------------------- Finalize Aggregate (cost=139110.73..139110.74 rows=1 width=12) (actual time=1207.665..1207.665 rows=1 loops=1) InitPlan 3 (returns $4) -> Finalize Aggregate (cost=93434.91..93434.92 rows=1 width=4) (actual time=839.876..839.876 rows=1 loops=1) InitPlan 2 (returns $2) -> Result (cost=45675.82..45675.83 rows=1 width=32) (actual time=446.010..446.011 rows=1 loops=1) InitPlan 1 (returns $1) -> Finalize Aggregate (cost=45675.81..45675.82 rows=1 width=32) (actual time=446.003..446.003 rows=1 loops=1) -> Gather (cost=45675.59..45675.80 rows=2 width=32) (actual time=445.976..445.983 rows=3 loops=1) Workers Planned: 2 Workers Launched: 2 -> Partial Aggregate (cost=44675.59..44675.60 rows=1 width=32) (actual time=442.533..442.533 rows=1 loops=3) -> Parallel Seq Scan on tv tv_1 (cost=0.00..42602.67 rows=829167 width=4) (actual time=0.012..257.063 rows=663334 loops=3) -> Gather (cost=47758.87..47759.08 rows=2 width=4) (actual time=839.830..839.869 rows=3 loops=1) Workers Planned: 2 Params Evaluated: $2 Workers Launched: 2 -> Partial Aggregate (cost=46758.87..46758.88 rows=1 width=4) (actual time=391.025..391.025 rows=1 loops=3) -> Parallel Seq Scan on tv tv_2 (cost=0.00..46748.51 rows=4146 width=4) (actual time=391.017..391.017 rows=0 loops=3) Filter: ((n)::numeric = $2) Rows Removed by Filter: 663334 -> Gather (cost=45675.59..45675.80 rows=2 width=8) (actual time=367.748..367.771 rows=3 loops=1) Workers Planned: 2 Workers Launched: 2 -> Partial Aggregate (cost=44675.59..44675.60 rows=1 width=8) (actual time=365.246..365.247 rows=1 loops=3) -> Parallel Seq Scan on tv (cost=0.00..42602.67 rows=829167 width=0) (actual time=0.006..206.494 rows=663334 loops=3) Planning time: 0.214 ms Execution time: 1208.765 ms (27 rows) explain analyze Select count(*) from tv order by (select max(n) from tv where (n,n)<>(select sum(1),avg(1))); QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------------------- Finalize Aggregate (cost=97570.40..97570.41 rows=1 width=12) (actual time=853.931..853.931 rows=1 loops=1) InitPlan 2 (returns $3) -> Finalize Aggregate (cost=51894.58..51894.59 rows=1 width=4) (actual time=458.660..458.660 rows=1 loops=1) InitPlan 1 (returns $0,$1) -> Aggregate (cost=0.02..0.03 rows=1 width=40) (actual time=0.013..0.013 rows=1 loops=1) -> Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.001..0.001 rows=1 loops=1) -> Gather (cost=51894.34..51894.55 rows=2 width=4) (actual time=458.611..458.650 rows=3 loops=1) Workers Planned: 2 Params Evaluated: $0, $1 Workers Launched: 2 -> Partial Aggregate (cost=50894.34..50894.35 rows=1 width=4) (actual time=455.244..455.244 rows=1 loops=3) -> Parallel Seq Scan on tv tv_1 (cost=0.00..48821.42 rows=829167 width=4) (actual time=0.098..287.991 rows=663333 loops=3) Filter: ((n <> $0) OR ((n)::numeric <> $1)) Rows Removed by Filter: 0 -> Gather (cost=45675.59..45675.80 rows=2 width=8) (actual time=395.241..395.255 rows=3 loops=1) Workers Planned: 2 Workers Launched: 2 -> Partial Aggregate (cost=44675.59..44675.60 rows=1 width=8) (actual time=391.913..391.914 rows=1 loops=3) -> Parallel Seq Scan on tv (cost=0.00..42602.67 rows=829167 width=0) (actual time=0.022..220.485 rows=663334 loops=3) Planning time: 0.253 ms Execution time: 855.179 ms (21 rows) explain analyze Select count(*) from tv order by (select max(n) from tv where (n,n,1)<>(select sum(1),avg(1),count(*))); QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------------------- Finalize Aggregate (cost=99643.32..99643.33 rows=1 width=12) (actual time=839.833..839.834 rows=1 loops=1) InitPlan 2 (returns $4) -> Finalize Aggregate (cost=53967.50..53967.51 rows=1 width=4) (actual time=467.280..467.280 rows=1 loops=1) InitPlan 1 (returns $0,$1,$2) -> Aggregate (cost=0.02..0.03 rows=1 width=48) (actual time=0.010..0.010 rows=1 loops=1) -> Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.001..0.001 rows=1 loops=1) -> Gather (cost=53967.26..53967.47 rows=2 width=4) (actual time=467.260..467.271 rows=3 loops=1) Workers Planned: 2 Params Evaluated: $0, $1, $2 Workers Launched: 2 -> Partial Aggregate (cost=52967.26..52967.27 rows=1 width=4) (actual time=464.117..464.118 rows=1 loops=3) -> Parallel Seq Scan on tv tv_1 (cost=0.00..50894.34 rows=829167 width=4) (actual time=0.010..290.662 rows=663333 loops=3) Filter: ((n <> $0) OR ((n)::numeric <> $1) OR (1 <> $2)) Rows Removed by Filter: 0 -> Gather (cost=45675.59..45675.80 rows=2 width=8) (actual time=372.530..372.539 rows=3 loops=1) Workers Planned: 2 Workers Launched: 2 -> Partial Aggregate (cost=44675.59..44675.60 rows=1 width=8) (actual time=369.988..369.989 rows=1 loops=3) -> Parallel Seq Scan on tv (cost=0.00..42602.67 rows=829167 width=0) (actual time=0.006..209.705 rows=663334 loops=3) Planning time: 0.229 ms Execution time: 840.841 ms (21 rows) --Testcase 15- CTE query ,having aggregate functions in where clause explain analyze with x as (select count(*) from tv where n=(select max(n) from tv order by 1)) select * from tv x1 where x1.n=(select max(n) from tv) order by 1; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------- Gather (cost=46675.81..91351.60 rows=2 width=105) (actual time=573.216..573.221 rows=2 loops=1) Workers Planned: 2 Params Evaluated: $1 Workers Launched: 2 InitPlan 1 (returns $1) -> Finalize Aggregate (cost=45675.80..45675.81 rows=1 width=4) (actual time=424.384..424.384 rows=1 loops=1) -> Gather (cost=45675.59..45675.80 rows=2 width=4) (actual time=424.340..424.375 rows=3 loops=1) Workers Planned: 2 Workers Launched: 2 -> Partial Aggregate (cost=44675.59..44675.60 rows=1 width=4) (actual time=421.833..421.833 rows=1 loops=3) -> Parallel Seq Scan on tv (cost=0.00..42602.67 rows=829167 width=4) (actual time=0.007..250.437 rows=663334 loops=3) -> Parallel Seq Scan on tv x1 (cost=0.00..44675.59 rows=1 width=105) (actual time=74.651..146.278 rows=1 loops=3) Filter: (n = $1) Rows Removed by Filter: 663333 Planning time: 0.179 ms Execution time: 574.201 ms (16 rows) \c postgres You are now connected to database "postgres" as user "centos". SET min_parallel_table_scan_size = 0; SET SET min_parallel_index_scan_size = 0; SET set parallel_tuple_cost = 0; SET set parallel_setup_cost = 0; SET ---Testcase 16- Multiple table comparison with subquery in where clause explain analyze verbose select * from t1 where t1.k = (select max(t3.k) from t3 where t3.j = (select avg(j) from t2)); QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------- Gather (cost=20.88..30.54 rows=2 width=12) (actual time=16.068..16.068 rows=0 loops=1) Output: t1.i, t1.j, t1.k Workers Planned: 2 Params Evaluated: $3 Workers Launched: 2 InitPlan 2 (returns $3) -> Aggregate (cost=20.87..20.88 rows=1 width=4) (actual time=12.766..12.772 rows=1 loops=1) Output: max(t3.k) InitPlan 1 (returns $1) -> Finalize Aggregate (cost=10.22..10.23 rows=1 width=32) (actual time=6.794..6.796 rows=1 loops=1) Output: avg(t2.j) -> Gather (cost=10.20..10.21 rows=2 width=32) (actual time=1.111..6.751 rows=3 loops=1) Output: (PARTIAL avg(t2.j)) Workers Planned: 2 Workers Launched: 2 -> Partial Aggregate (cost=10.20..10.21 rows=1 width=32) (actual time=0.203..0.203 rows=1 loops=3) Output: PARTIAL avg(t2.j) Worker 0: actual time=0.016..0.017 rows=1 loops=1 Worker 1: actual time=0.009..0.009 rows=1 loops=1 -> Parallel Seq Scan on public.t2 (cost=0.00..9.16 rows=416 width=4) (actual time=0.007..0.112 rows=333 loops=3) Output: t2.j Worker 0: actual time=0.002..0.002 rows=0 loops=1 Worker 1: actual time=0.001..0.001 rows=0 loops=1 -> Gather (cost=0.00..10.62 rows=4 width=4) (actual time=12.755..12.755 rows=0 loops=1) Output: t3.k Workers Planned: 2 Params Evaluated: $1 Workers Launched: 2 -> Parallel Seq Scan on public.t3 (cost=0.00..10.62 rows=2 width=4) (actual time=0.159..0.159 rows=0 loops=3) Output: t3.k Filter: ((t3.j)::numeric = $1) Rows Removed by Filter: 300 Worker 0: actual time=0.003..0.003 rows=0 loops=1 Worker 1: actual time=0.001..0.001 rows=0 loops=1 -> Parallel Seq Scan on public.t1 (cost=0.00..9.67 rows=1 width=12) (actual time=0.058..0.058 rows=0 loops=3) Output: t1.i, t1.j, t1.k Filter: (t1.k = $3) Rows Removed by Filter: 299 Worker 0: actual time=0.001..0.001 rows=0 loops=1 Worker 1: actual time=0.001..0.001 rows=0 loops=1 Planning time: 0.745 ms Execution time: 16.757 ms (42 rows) explain analyze verbose select * from t1 where t1.k = (select max(t3.k) from t3 ); QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------ Gather (cost=9.71..19.38 rows=2 width=12) (actual time=8.702..8.702 rows=0 loops=1) Output: t1.i, t1.j, t1.k Workers Planned: 2 Params Evaluated: $1 Workers Launched: 2 InitPlan 1 (returns $1) -> Finalize Aggregate (cost=9.70..9.71 rows=1 width=4) (actual time=5.689..5.689 rows=1 loops=1) Output: max(t3.k) -> Gather (cost=9.69..9.70 rows=2 width=4) (actual time=0.689..5.683 rows=3 loops=1) Output: (PARTIAL max(t3.k)) Workers Planned: 2 Workers Launched: 2 -> Partial Aggregate (cost=9.69..9.70 rows=1 width=4) (actual time=0.172..0.172 rows=1 loops=3) Output: PARTIAL max(t3.k) Worker 0: actual time=0.010..0.011 rows=1 loops=1 Worker 1: actual time=0.004..0.005 rows=1 loops=1 -> Parallel Seq Scan on public.t3 (cost=0.00..8.75 rows=375 width=4) (actual time=0.004..0.096 rows=300 loops=3) Output: t3.k Worker 0: actual time=0.002..0.002 rows=0 loops=1 Worker 1: actual time=0.001..0.001 rows=0 loops=1 -> Parallel Seq Scan on public.t1 (cost=0.00..9.67 rows=1 width=12) (actual time=0.053..0.053 rows=0 loops=3) Output: t1.i, t1.j, t1.k Filter: (t1.k = $1) Rows Removed by Filter: 299 Worker 0: actual time=0.001..0.001 rows=0 loops=1 Worker 1: actual time=0.001..0.001 rows=0 loops=1 Planning time: 0.121 ms Execution time: 9.268 ms (28 rows) explain analyze verbose select * from t1 where (k,k)= (select max(k),min(k) from t3); QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------ Gather (cost=10.66..20.32 rows=2 width=12) (actual time=8.615..8.615 rows=0 loops=1) Output: t1.i, t1.j, t1.k Workers Planned: 2 Params Evaluated: $1, $2 Workers Launched: 2 InitPlan 1 (returns $1,$2) -> Finalize Aggregate (cost=10.64..10.65 rows=1 width=8) (actual time=5.342..5.342 rows=1 loops=1) Output: max(t3.k), min(t3.k) -> Gather (cost=10.62..10.63 rows=2 width=8) (actual time=0.740..5.337 rows=3 loops=1) Output: (PARTIAL max(t3.k)), (PARTIAL min(t3.k)) Workers Planned: 2 Workers Launched: 2 -> Partial Aggregate (cost=10.62..10.63 rows=1 width=8) (actual time=0.189..0.189 rows=1 loops=3) Output: PARTIAL max(t3.k), PARTIAL min(t3.k) Worker 0: actual time=0.010..0.011 rows=1 loops=1 Worker 1: actual time=0.005..0.005 rows=1 loops=1 -> Parallel Seq Scan on public.t3 (cost=0.00..8.75 rows=375 width=4) (actual time=0.004..0.094 rows=300 loops=3) Output: t3.k Worker 0: actual time=0.002..0.002 rows=0 loops=1 Worker 1: actual time=0.001..0.001 rows=0 loops=1 -> Result (cost=0.00..9.67 rows=1 width=12) (actual time=0.001..0.001 rows=0 loops=3) Output: t1.i, t1.j, t1.k One-Time Filter: ($1 = $2) Worker 0: actual time=0.002..0.002 rows=0 loops=1 Worker 1: actual time=0.002..0.002 rows=0 loops=1 -> Parallel Seq Scan on public.t1 (cost=0.00..9.67 rows=1 width=12) (never executed) Output: t1.i, t1.j, t1.k Filter: (t1.k = $2) Planning time: 0.149 ms Execution time: 9.064 ms (30 rows) \c test You are now connected to database "test" as user "centos". --Testcase 17- explain verbose select * from (select * from pgbench_accounts) as t where aid= (select max(aid) from pgbench_accounts); QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------ Gather (cost=2162178.31..4322356.49 rows=1 width=97) Output: pgbench_accounts.aid, pgbench_accounts.bid, pgbench_accounts.abalance, pgbench_accounts.filler Workers Planned: 2 Params Evaluated: $1 InitPlan 1 (returns $1) -> Finalize Aggregate (cost=2161178.30..2161178.31 rows=1 width=4) Output: max(pgbench_accounts_1.aid) -> Gather (cost=2161178.08..2161178.29 rows=2 width=4) Output: (PARTIAL max(pgbench_accounts_1.aid)) Workers Planned: 2 -> Partial Aggregate (cost=2160178.08..2160178.09 rows=1 width=4) Output: PARTIAL max(pgbench_accounts_1.aid) -> Parallel Seq Scan on public.pgbench_accounts pgbench_accounts_1 (cost=0.00..2056011.47 rows=41666647 width=4) Output: pgbench_accounts_1.aid -> Parallel Seq Scan on public.pgbench_accounts (cost=0.00..2160178.08 rows=1 width=97) Output: pgbench_accounts.aid, pgbench_accounts.bid, pgbench_accounts.abalance, pgbench_accounts.filler Filter: (pgbench_accounts.aid = $1) (17 rows) explain verbose select * from (select * from pgbench_accounts where aid=(select min(bid) from pgbench_accounts)) as t where bid = (select max(aid) from pgbench_accounts); QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------ Gather (cost=4323356.62..6587701.42 rows=1 width=97) Output: pgbench_accounts.aid, pgbench_accounts.bid, pgbench_accounts.abalance, pgbench_accounts.filler Workers Planned: 2 Params Evaluated: $1, $3 InitPlan 1 (returns $1) -> Finalize Aggregate (cost=2161178.30..2161178.31 rows=1 width=4) Output: min(pgbench_accounts_1.bid) -> Gather (cost=2161178.08..2161178.29 rows=2 width=4) Output: (PARTIAL min(pgbench_accounts_1.bid)) Workers Planned: 2 -> Partial Aggregate (cost=2160178.08..2160178.09 rows=1 width=4) Output: PARTIAL min(pgbench_accounts_1.bid) -> Parallel Seq Scan on public.pgbench_accounts pgbench_accounts_1 (cost=0.00..2056011.47 rows=41666647 width=4) Output: pgbench_accounts_1.bid InitPlan 2 (returns $3) -> Finalize Aggregate (cost=2161178.30..2161178.31 rows=1 width=4) Output: max(pgbench_accounts_2.aid) -> Gather (cost=2161178.08..2161178.29 rows=2 width=4) Output: (PARTIAL max(pgbench_accounts_2.aid)) Workers Planned: 2 -> Partial Aggregate (cost=2160178.08..2160178.09 rows=1 width=4) Output: PARTIAL max(pgbench_accounts_2.aid) -> Parallel Seq Scan on public.pgbench_accounts pgbench_accounts_2 (cost=0.00..2056011.47 rows=41666647 width=4) Output: pgbench_accounts_2.aid -> Parallel Seq Scan on public.pgbench_accounts (cost=0.00..2264344.70 rows=1 width=97) Output: pgbench_accounts.aid, pgbench_accounts.bid, pgbench_accounts.abalance, pgbench_accounts.filler Filter: ((pgbench_accounts.aid = $1) AND (pgbench_accounts.bid = $3)) (27 rows) --Testcase 18- aggregate function in where condition of subquery as well as inline query explain analyze verbose select * from (select count(*) n from pgbench_accounts) as t where n = (select max(aid) from pgbench_accounts where aid=(select count(*) from pgbench_tellers)); QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Finalize Aggregate (cost=2161178.30..2161178.31 rows=1 width=8) (actual time=29235.836..29235.836 rows=0 loops=1) Output: count(*) Filter: (count(*) = $3) Rows Removed by Filter: 1 InitPlan 3 (returns $3) -> Result (cost=2161358.19..2161358.20 rows=1 width=4) (actual time=8872.770..8872.771 rows=1 loops=1) Output: $1 InitPlan 1 (returns $0) -> Aggregate (cost=180.00..180.01 rows=1 width=8) (actual time=4.657..4.657 rows=1 loops=1) Output: count(*) -> Seq Scan on public.pgbench_tellers (cost=0.00..155.00 rows=10000 width=0) (actual time=0.043..2.522 rows=10000 loops=1) Output: pgbench_tellers.tid, pgbench_tellers.bid, pgbench_tellers.tbalance, pgbench_tellers.filler InitPlan 2 (returns $1) -> Limit (cost=1000.00..2161178.18 rows=1 width=4) (actual time=8872.764..8872.765 rows=1 loops=1) Output: pgbench_accounts_1.aid -> Gather (cost=1000.00..2161178.18 rows=1 width=4) (actual time=8872.754..8872.754 rows=1 loops=1) Output: pgbench_accounts_1.aid Workers Planned: 2 Params Evaluated: $0 Workers Launched: 2 -> Parallel Seq Scan on public.pgbench_accounts pgbench_accounts_1 (cost=0.00..2160178.08 rows=1 width=4) (actual time=8419.839..8419.839 rows=0 loops=3) Output: pgbench_accounts_1.aid Filter: ((pgbench_accounts_1.aid IS NOT NULL) AND (pgbench_accounts_1.aid = $0)) Rows Removed by Filter: 33333332 Worker 0: actual time=8864.305..8864.305 rows=0 loops=1 Worker 1: actual time=7527.657..7527.657 rows=1 loops=1 -> Gather (cost=2161178.08..2161178.29 rows=2 width=8) (actual time=20363.030..20363.048 rows=3 loops=1) Output: (PARTIAL count(*)) Workers Planned: 2 Workers Launched: 2 -> Partial Aggregate (cost=2160178.08..2160178.09 rows=1 width=8) (actual time=20360.255..20360.255 rows=1 loops=3) Output: PARTIAL count(*) Worker 0: actual time=20358.432..20358.432 rows=1 loops=1 Worker 1: actual time=20359.688..20359.689 rows=1 loops=1 -> Parallel Seq Scan on public.pgbench_accounts (cost=0.00..2056011.47 rows=41666647 width=0) (actual time=0.023..11869.772 rows=33333333 loops=3) Worker 0: actual time=0.034..11799.161 rows=33387634 loops=1 Worker 1: actual time=0.023..11796.680 rows=32925848 loops=1 Planning time: 0.458 ms Execution time: 29236.878 ms (39 rows) explain analyze verbose select * from (select count(*) n from pgbench_accounts) as t where n = (select max(aid) from pgbench_accounts where aid<>(select count(*) from pgbench_tellers)); QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Finalize Aggregate (cost=2161178.30..2161178.31 rows=1 width=8) (actual time=44559.868..44559.868 rows=1 loops=1) Output: count(*) Filter: (count(*) = $2) InitPlan 2 (returns $2) -> Finalize Aggregate (cost=2265524.92..2265524.93 rows=1 width=4) (actual time=24084.037..24084.037 rows=1 loops=1) Output: max(pgbench_accounts_1.aid) InitPlan 1 (returns $0) -> Aggregate (cost=180.00..180.01 rows=1 width=8) (actual time=5.070..5.070 rows=1 loops=1) Output: count(*) -> Seq Scan on public.pgbench_tellers (cost=0.00..155.00 rows=10000 width=0) (actual time=0.013..2.586 rows=10000 loops=1) Output: pgbench_tellers.tid, pgbench_tellers.bid, pgbench_tellers.tbalance, pgbench_tellers.filler -> Gather (cost=2265344.70..2265344.91 rows=2 width=4) (actual time=24084.009..24084.031 rows=3 loops=1) Output: (PARTIAL max(pgbench_accounts_1.aid)) Workers Planned: 2 Params Evaluated: $0 Workers Launched: 2 -> Partial Aggregate (cost=2264344.70..2264344.71 rows=1 width=4) (actual time=24076.055..24076.056 rows=1 loops=3) Output: PARTIAL max(pgbench_accounts_1.aid) Worker 0: actual time=24074.969..24074.969 rows=1 loops=1 Worker 1: actual time=24074.943..24074.943 rows=1 loops=1 -> Parallel Seq Scan on public.pgbench_accounts pgbench_accounts_1 (cost=0.00..2160178.08 rows=41666646 width=4) (actual time=0.025..14989.963 rows=33333333 loops=3) Output: pgbench_accounts_1.aid Filter: (pgbench_accounts_1.aid <> $0) Rows Removed by Filter: 0 Worker 0: actual time=0.029..14969.743 rows=33101055 loops=1 Worker 1: actual time=0.029..14977.727 rows=33345772 loops=1 -> Gather (cost=2161178.08..2161178.29 rows=2 width=8) (actual time=20475.799..20475.815 rows=3 loops=1) Output: (PARTIAL count(*)) Workers Planned: 2 Workers Launched: 2 -> Partial Aggregate (cost=2160178.08..2160178.09 rows=1 width=8) (actual time=20472.495..20472.495 rows=1 loops=3) Output: PARTIAL count(*) Worker 0: actual time=20472.214..20472.215 rows=1 loops=1 Worker 1: actual time=20469.891..20469.892 rows=1 loops=1 -> Parallel Seq Scan on public.pgbench_accounts (cost=0.00..2056011.47 rows=41666647 width=0) (actual time=0.028..11912.656 rows=33333333 loops=3) Worker 0: actual time=0.025..11938.422 rows=33186440 loops=1 Worker 1: actual time=0.047..11891.912 rows=33109397 loops=1 Planning time: 0.206 ms Execution time: 44561.057 ms (39 rows) --Testcase 19 - multiple columns comparisions in where clause of subquery explain analyze verbose select count(*) from tv where n=(select count(n1) from tv1 where (n,n) not in (select avg(n),count(n) from tv where n1=(select 'a'))); QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Aggregate (cost=129076.80..129076.81 rows=1 width=8) (actual time=3147.978..3147.978 rows=1 loops=1) Output: count(*) InitPlan 3 (returns $4) -> Aggregate (cost=83401.00..83401.01 rows=1 width=8) (actual time=2968.014..2968.015 rows=1 loops=1) Output: count(tv1.n1) -> Seq Scan on public.tv1 (cost=47769.48..82163.50 rows=495000 width=101) (actual time=1742.176..2646.537 rows=990001 loops=1) Output: tv1.n, tv1.n1 Filter: (NOT (hashed SubPlan 2)) SubPlan 2 -> Finalize Aggregate (cost=47769.47..47769.48 rows=1 width=40) (actual time=1742.144..1742.144 rows=1 loops=1) Output: avg(tv_1.n), count(tv_1.n) InitPlan 1 (returns $0) -> Result (cost=0.00..0.01 rows=1 width=32) (actual time=0.002..0.002 rows=1 loops=1) Output: 'a'::text -> Gather (cost=47769.24..47769.45 rows=2 width=40) (actual time=1742.113..1742.122 rows=3 loops=1) Output: (PARTIAL avg(tv_1.n)), (PARTIAL count(tv_1.n)) Workers Planned: 2 Params Evaluated: $0 Workers Launched: 2 -> Partial Aggregate (cost=46769.24..46769.25 rows=1 width=40) (actual time=1739.511..1739.511 rows=1 loops=3) Output: PARTIAL avg(tv_1.n), PARTIAL count(tv_1.n) Worker 0: actual time=1738.290..1738.291 rows=1 loops=1 Worker 1: actual time=1738.489..1738.490 rows=1 loops=1 -> Parallel Seq Scan on public.tv tv_1 (cost=0.00..46748.51 rows=4146 width=4) (actual time=8.871..1628.555 rows=330000 loops=3) Output: tv_1.n Filter: ((tv_1.n1)::text = $0) Rows Removed by Filter: 333333 Worker 0: actual time=7.698..1626.087 rows=316585 loops=1 Worker 1: actual time=7.820..1628.357 rows=346876 loops=1 -> Gather (cost=1000.00..45675.79 rows=2 width=0) (actual time=2968.783..3147.968 rows=2 loops=1) Workers Planned: 2 Params Evaluated: $4 Workers Launched: 2 -> Parallel Seq Scan on public.tv (cost=0.00..44675.59 rows=1 width=0) (actual time=115.856..175.567 rows=1 loops=3) Filter: (tv.n = $4) Rows Removed by Filter: 663333 Worker 0: actual time=173.685..173.685 rows=0 loops=1 Worker 1: actual time=173.708..173.708 rows=0 loops=1 Planning time: 0.325 ms Execution time: 3149.061 ms (40 rows) explain analyze verbose select count(*) from tv where n!=(select count(n1) from tv1 where (n,n) not in (select avg(n),count(n) from tv where n1=(select 'a'))); QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Finalize Aggregate (cost=131149.73..131149.74 rows=1 width=8) (actual time=3626.697..3626.698 rows=1 loops=1) Output: count(*) InitPlan 3 (returns $4) -> Aggregate (cost=83401.00..83401.01 rows=1 width=8) (actual time=3147.787..3147.787 rows=1 loops=1) Output: count(tv1.n1) -> Seq Scan on public.tv1 (cost=47769.48..82163.50 rows=495000 width=101) (actual time=1883.801..2816.955 rows=990001 loops=1) Output: tv1.n, tv1.n1 Filter: (NOT (hashed SubPlan 2)) SubPlan 2 -> Finalize Aggregate (cost=47769.47..47769.48 rows=1 width=40) (actual time=1883.132..1883.132 rows=1 loops=1) Output: avg(tv_1.n), count(tv_1.n) InitPlan 1 (returns $0) -> Result (cost=0.00..0.01 rows=1 width=32) (actual time=0.001..0.001 rows=1 loops=1) Output: 'a'::text -> Gather (cost=47769.24..47769.45 rows=2 width=40) (actual time=1882.831..1883.081 rows=3 loops=1) Output: (PARTIAL avg(tv_1.n)), (PARTIAL count(tv_1.n)) Workers Planned: 2 Params Evaluated: $0 Workers Launched: 2 -> Partial Aggregate (cost=46769.24..46769.25 rows=1 width=40) (actual time=1879.676..1879.676 rows=1 loops=3) Output: PARTIAL avg(tv_1.n), PARTIAL count(tv_1.n) Worker 0: actual time=1877.211..1877.212 rows=1 loops=1 Worker 1: actual time=1879.413..1879.413 rows=1 loops=1 -> Parallel Seq Scan on public.tv tv_1 (cost=0.00..46748.51 rows=4146 width=4) (actual time=10.507..1760.949 rows=330000 loops=3) Output: tv_1.n Filter: ((tv_1.n1)::text = $0) Rows Removed by Filter: 333333 Worker 0: actual time=8.014..1758.021 rows=324763 loops=1 Worker 1: actual time=10.243..1759.632 rows=327620 loops=1 -> Gather (cost=47748.50..47748.71 rows=2 width=8) (actual time=3626.677..3626.684 rows=3 loops=1) Output: (PARTIAL count(*)) Workers Planned: 2 Params Evaluated: $4 Workers Launched: 2 -> Partial Aggregate (cost=46748.50..46748.51 rows=1 width=8) (actual time=474.548..474.548 rows=1 loops=3) Output: PARTIAL count(*) Worker 0: actual time=472.369..472.370 rows=1 loops=1 Worker 1: actual time=472.777..472.777 rows=1 loops=1 -> Parallel Seq Scan on public.tv (cost=0.00..44675.59 rows=829166 width=0) (actual time=0.037..295.515 rows=663333 loops=3) Filter: (tv.n <> $4) Rows Removed by Filter: 1 Worker 0: actual time=0.047..295.317 rows=629241 loops=1 Worker 1: actual time=0.044..294.044 rows=649078 loops=1 Planning time: 0.244 ms Execution time: 3627.730 ms (45 rows) --Testcase 20 - aggregate functions used with different joins conditions explain analyze verbose select count(*) from tv f right outer join tv1 g on f.n not in (select avg(g.n) from tv g ,tv1 f where g.n=(select max(f.n) from tv1 f)) where f.n in (1,2,3) ; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Aggregate (cost=250975.40..250975.41 rows=1 width=8) (actual time=5463.092..5463.092 rows=1 loops=1) Output: count(*) -> Nested Loop (cost=107832.82..243550.39 rows=2970003 width=0) (actual time=1696.055..4704.618 rows=2970003 loops=1) -> Seq Scan on public.tv1 g (cost=0.00..26969.01 rows=990001 width=0) (actual time=0.034..350.988 rows=990001 loops=1) Output: g.n, g.n1 -> Materialize (cost=107832.82..179456.35 rows=3 width=0) (actual time=0.002..0.003 rows=3 loops=990001) -> Seq Scan on public.tv f (cost=107832.82..179456.34 rows=3 width=0) (actual time=1696.013..1972.396 rows=3 loops=1) Filter: ((f.n = ANY ('{1,2,3}'::integer[])) AND (NOT (hashed SubPlan 2))) Rows Removed by Filter: 1989998 SubPlan 2 -> Finalize Aggregate (cost=107832.81..107832.82 rows=1 width=32) (actual time=1435.208..1435.208 rows=1 loops=1) Output: avg(g_1.n) InitPlan 1 (returns $1) -> Finalize Aggregate (cost=23225.47..23225.48 rows=1 width=4) (actual time=238.335..238.335 rows=1 loops=1) Output: max(f_1.n) -> Gather (cost=23225.25..23225.46 rows=2 width=4) (actual time=238.302..238.323 rows=3 loops=1) Output: (PARTIAL max(f_1.n)) Workers Planned: 2 Workers Launched: 2 -> Partial Aggregate (cost=22225.25..22225.26 rows=1 width=4) (actual time=233.887..233.888 rows=1 loops=3) Output: PARTIAL max(f_1.n) Worker 0: actual time=231.712..231.712 rows=1 loops=1 Worker 1: actual time=232.209..232.209 rows=1 loops=1 -> Parallel Seq Scan on public.tv1 f_1 (cost=0.00..21194.00 rows=412500 width=4) (actual time=0.037..140.555 rows=330000 loops=3) Output: f_1.n Worker 0: actual time=0.048..138.385 rows=294871 loops=1 Worker 1: actual time=0.055..139.817 rows=325206 loops=1 -> Gather (cost=84607.11..84607.32 rows=2 width=32) (actual time=1239.868..1435.146 rows=3 loops=1) Output: (PARTIAL avg(g_1.n)) Workers Planned: 2 Params Evaluated: $1 Workers Launched: 2 -> Partial Aggregate (cost=83607.11..83607.12 rows=1 width=32) (actual time=852.836..852.837 rows=1 loops=3) Output: PARTIAL avg(g_1.n) Worker 0: actual time=1192.378..1192.378 rows=1 loops=1 Worker 1: actual time=365.247..365.247 rows=1 loops=1 -> Nested Loop (cost=0.00..81544.61 rows=825001 width=4) (actual time=162.419..659.449 rows=660001 loops=3) Output: g_1.n Worker 0: actual time=121.910..891.721 rows=990001 loops=1 Worker 1: actual time=365.221..365.221 rows=0 loops=1 -> Parallel Seq Scan on public.tv g_1 (cost=0.00..44675.59 rows=1 width=4) (actual time=162.392..162.400 rows=1 loops=3) Output: g_1.n, g_1.n1 Filter: (g_1.n = $1) Rows Removed by Filter: 663333 Worker 0: actual time=121.869..121.882 rows=1 loops=1 Worker 1: actual time=365.219..365.219 rows=0 loops=1 -> Seq Scan on public.tv1 f_2 (cost=0.00..26969.01 rows=990001 width=0) (actual time=0.033..345.329 rows=990001 loops=2) Output: f_2.n, f_2.n1 Worker 0: actual time=0.031..355.986 rows=990001 loops=1 Planning time: 0.421 ms Execution time: 5463.476 ms (51 rows) explain verbose select count(*) from tv f right outer join tv1 g on f.n != (select avg(g.n) from tv g ,tv1 f where (g.n,g.n,g.n)!=Any(select max(f.n),min(f.n),count(*) from tv1 f)) where f.n in (1,2,3) limit 5; QUERY PLAN ------------------------------------------------------------------------------------------------------------------ Limit (cost=35324821258.75..35324821258.76 rows=1 width=8) Output: (count(*)) InitPlan 1 (returns $0) -> Aggregate (cost=35324690475.10..35324690475.11 rows=1 width=32) Output: avg(g_1.n) -> Nested Loop (cost=34394.02..30399433025.09 rows=1970102980001 width=4) Output: g_1.n -> Nested Loop (cost=34394.02..123430.07 rows=1990001 width=4) Output: g_1.n Join Filter: ((g_1.n <> (max(f_2.n))) OR (g_1.n <> (min(f_2.n))) OR (g_1.n <> (count(*)))) -> Aggregate (cost=34394.02..34394.03 rows=1 width=16) Output: max(f_2.n), min(f_2.n), count(*) -> Seq Scan on public.tv1 f_2 (cost=0.00..26969.01 rows=990001 width=4) Output: f_2.n, f_2.n1 -> Seq Scan on public.tv g_1 (cost=0.00..54211.01 rows=1990001 width=4) Output: g_1.n, g_1.n1 -> Materialize (cost=0.00..34820.01 rows=990001 width=0) -> Seq Scan on public.tv1 f_1 (cost=0.00..26969.01 rows=990001 width=0) -> Finalize Aggregate (cost=130783.64..130783.65 rows=1 width=8) Output: count(*) -> Gather (cost=130783.43..130783.64 rows=2 width=8) Output: (PARTIAL count(*)) Workers Planned: 2 Params Evaluated: $0 -> Partial Aggregate (cost=129783.43..129783.44 rows=1 width=8) Output: PARTIAL count(*) -> Nested Loop (cost=0.00..123595.92 rows=2475002 width=0) -> Parallel Seq Scan on public.tv f (cost=0.00..49857.88 rows=2 width=0) Output: f.n, f.n1 Filter: ((f.n = ANY ('{1,2,3}'::integer[])) AND ((f.n)::numeric <> $0)) -> Seq Scan on public.tv1 g (cost=0.00..26969.01 rows=990001 width=0) Output: g.n, g.n1 (32 rows) explain analyze verbose select count(*) from tv f right outer join tv1 g on (f.n,f.n,f.n)=(select max(f.n),min(f.n),count(*) from tv1 f); QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------------- Aggregate (cost=127632.83..127632.84 rows=1 width=8) (actual time=1546.405..1546.406 rows=1 loops=1) Output: count(*) InitPlan 1 (returns $1,$2,$3) -> Finalize Aggregate (cost=25287.98..25287.99 rows=1 width=16) (actual time=284.683..284.683 rows=1 loops=1) Output: max(f_1.n), min(f_1.n), count(*) -> Gather (cost=25287.75..25287.96 rows=2 width=16) (actual time=284.606..284.664 rows=3 loops=1) Output: (PARTIAL max(f_1.n)), (PARTIAL min(f_1.n)), (PARTIAL count(*)) Workers Planned: 2 Workers Launched: 2 -> Partial Aggregate (cost=24287.75..24287.76 rows=1 width=16) (actual time=280.305..280.306 rows=1 loops=3) Output: PARTIAL max(f_1.n), PARTIAL min(f_1.n), PARTIAL count(*) Worker 0: actual time=277.991..277.992 rows=1 loops=1 Worker 1: actual time=278.896..278.896 rows=1 loops=1 -> Parallel Seq Scan on public.tv1 f_1 (cost=0.00..21194.00 rows=412500 width=4) (actual time=0.033..148.256 rows=330000 loops=3) Output: f_1.n Worker 0: actual time=0.048..149.101 rows=330658 loops=1 Worker 1: actual time=0.039..146.672 rows=329033 loops=1 -> Nested Loop Left Join (cost=1000.00..97394.83 rows=1980002 width=0) (actual time=288.459..1314.710 rows=990001 loops=1) -> Seq Scan on public.tv1 g (cost=0.00..26969.01 rows=990001 width=0) (actual time=0.029..291.234 rows=990001 loops=1) Output: g.n, g.n1 -> Materialize (cost=1000.00..45675.80 rows=2 width=0) (actual time=0.000..0.000 rows=0 loops=990001) -> Gather (cost=1000.00..45675.79 rows=2 width=0) (actual time=288.422..288.422 rows=0 loops=1) Workers Planned: 2 Params Evaluated: $1, $2, $3 Workers Launched: 2 -> Result (cost=0.01..44675.59 rows=1 width=0) (actual time=0.002..0.002 rows=0 loops=3) One-Time Filter: (($1 = $3) AND ($2 = $3)) Worker 0: actual time=0.003..0.003 rows=0 loops=1 Worker 1: actual time=0.002..0.002 rows=0 loops=1 -> Parallel Seq Scan on public.tv f (cost=0.01..44675.59 rows=1 width=0) (never executed) Output: f.n, f.n1 Filter: (f.n = $3) Planning time: 0.361 ms Execution time: 1546.711 ms (34 rows) explain verbose select count(*) from tv f left outer join tv1 g on (f.n,f.n,f.n)=(select max(f.n),min(f.n),count(*) from tv1 f); QUERY PLAN ---------------------------------------------------------------------------------------------------------- Finalize Aggregate (cost=36727216863.89..36727216863.90 rows=1 width=8) Output: count(*) InitPlan 1 (returns $1,$2,$3) -> Finalize Aggregate (cost=25287.98..25287.99 rows=1 width=16) Output: max(f_1.n), min(f_1.n), count(*) -> Gather (cost=25287.75..25287.96 rows=2 width=16) Output: (PARTIAL max(f_1.n)), (PARTIAL min(f_1.n)), (PARTIAL count(*)) Workers Planned: 2 -> Partial Aggregate (cost=24287.75..24287.76 rows=1 width=16) Output: PARTIAL max(f_1.n), PARTIAL min(f_1.n), PARTIAL count(*) -> Parallel Seq Scan on public.tv1 f_1 (cost=0.00..21194.00 rows=412500 width=4) Output: f_1.n -> Gather (cost=36727191575.68..36727191575.89 rows=2 width=8) Output: (PARTIAL count(*)) Workers Planned: 2 Params Evaluated: $1, $2, $3 -> Partial Aggregate (cost=36727190575.68..36727190575.69 rows=1 width=8) Output: PARTIAL count(*) -> Nested Loop Left Join (cost=0.00..36727188502.76 rows=829167 width=0) Join Filter: ((f.n = $1) AND (f.n = $2) AND (f.n = $3)) -> Parallel Seq Scan on public.tv f (cost=0.00..42602.67 rows=829167 width=4) Output: f.n, f.n1 -> Seq Scan on public.tv1 g (cost=0.00..26969.01 rows=990001 width=0) Output: g.n, g.n1 (24 rows) explain verbose select count(*) from tv f left outer join tv1 g on (f.n,f.n,f.n)=(select max(f.n),min(f.n),count(*) from tv1 f where f.n1=(select string_agg('ss','sss'))); QUERY PLAN -------------------------------------------------------------------------------------------------------- Finalize Aggregate (cost=36727215848.13..36727215848.14 rows=1 width=8) Output: count(*) InitPlan 2 (returns $2,$3,$4) -> Finalize Aggregate (cost=24272.22..24272.23 rows=1 width=16) Output: max(f_1.n), min(f_1.n), count(*) InitPlan 1 (returns $0) -> Aggregate (cost=0.02..0.03 rows=1 width=32) Output: string_agg('ss'::text, 'sss'::text) -> Result (cost=0.00..0.01 rows=1 width=0) -> Gather (cost=24271.97..24272.18 rows=2 width=16) Output: (PARTIAL max(f_1.n)), (PARTIAL min(f_1.n)), (PARTIAL count(*)) Workers Planned: 2 Params Evaluated: $0 -> Partial Aggregate (cost=23271.97..23271.98 rows=1 width=16) Output: PARTIAL max(f_1.n), PARTIAL min(f_1.n), PARTIAL count(*) -> Parallel Seq Scan on public.tv1 f_1 (cost=0.00..23256.51 rows=2062 width=4) Output: f_1.n Filter: ((f_1.n1)::text = $0) -> Gather (cost=36727191575.68..36727191575.89 rows=2 width=8) Output: (PARTIAL count(*)) Workers Planned: 2 Params Evaluated: $2, $3, $4 -> Partial Aggregate (cost=36727190575.68..36727190575.69 rows=1 width=8) Output: PARTIAL count(*) -> Nested Loop Left Join (cost=0.00..36727188502.76 rows=829167 width=0) Join Filter: ((f.n = $2) AND (f.n = $3) AND (f.n = $4)) -> Parallel Seq Scan on public.tv f (cost=0.00..42602.67 rows=829167 width=4) Output: f.n, f.n1 -> Seq Scan on public.tv1 g (cost=0.00..26969.01 rows=990001 width=0) Output: g.n, g.n1 (30 rows) explain verbose select sum(a.aid),min(b.n),count(c.n1) from pgbench_accounts a ,tv b,tv1 c where b.n=c.n and a.aid=(select max(bid) from pgbench_tellers); QUERY PLAN ---------------------------------------------------------------------------------------------------------------------- Finalize Aggregate (cost=2331320.20..2331320.21 rows=1 width=20) Output: sum(a.aid), min(b.n), count(c.n1) InitPlan 1 (returns $0) -> Aggregate (cost=180.00..180.01 rows=1 width=4) Output: max(pgbench_tellers.bid) -> Seq Scan on public.pgbench_tellers (cost=0.00..155.00 rows=10000 width=4) Output: pgbench_tellers.tid, pgbench_tellers.bid, pgbench_tellers.tbalance, pgbench_tellers.filler -> Gather (cost=2331139.96..2331140.17 rows=2 width=20) Output: (PARTIAL sum(a.aid)), (PARTIAL min(b.n)), (PARTIAL count(c.n1)) Workers Planned: 2 Params Evaluated: $0 -> Partial Aggregate (cost=2330139.96..2330139.97 rows=1 width=20) Output: PARTIAL sum(a.aid), PARTIAL min(b.n), PARTIAL count(c.n1) -> Hash Join (cost=55780.02..2324119.62 rows=802712 width=109) Output: a.aid, b.n, c.n1 Hash Cond: (b.n = c.n) -> Nested Loop (cost=0.00..2234289.10 rows=829167 width=8) Output: a.aid, b.n -> Parallel Seq Scan on public.pgbench_accounts a (cost=0.00..2160178.08 rows=1 width=4) Output: a.aid, a.bid, a.abalance, a.filler Filter: (a.aid = $0) -> Seq Scan on public.tv b (cost=0.00..54211.01 rows=1990001 width=4) Output: b.n, b.n1 -> Hash (cost=26969.01..26969.01 rows=990001 width=105) Output: c.n1, c.n -> Seq Scan on public.tv1 c (cost=0.00..26969.01 rows=990001 width=105) Output: c.n1, c.n (27 rows) --Testcase 21 - aggregate in aggregate function used in subquery of where clause explain select max(n) from tv a where a.n =(select trunc(avg(123.33)) from tv1,tv); QUERY PLAN ---------------------------------------------------------------------------------------------------- Finalize Aggregate (cost=32622856672.50..32622856672.51 rows=1 width=4) InitPlan 1 (returns $1) -> Finalize Aggregate (cost=32622808913.40..32622808913.42 rows=1 width=32) -> Gather (cost=32622808913.18..32622808913.39 rows=2 width=32) Workers Planned: 2 -> Partial Aggregate (cost=32622807913.18..32622807913.19 rows=1 width=32) -> Nested Loop (cost=0.00..30570617309.01 rows=820876241667 width=0) -> Parallel Seq Scan on tv (cost=0.00..42602.67 rows=829167 width=0) -> Seq Scan on tv1 (cost=0.00..26969.01 rows=990001 width=0) -> Gather (cost=47758.87..47759.08 rows=2 width=4) Workers Planned: 2 Params Evaluated: $1 -> Partial Aggregate (cost=46758.87..46758.88 rows=1 width=4) -> Parallel Seq Scan on tv a (cost=0.00..46748.51 rows=4146 width=4) Filter: ((n)::numeric = $1) (15 rows) --Testcase 22 - aggreagte functions used against partition table . SET min_parallel_table_scan_size = 0; SET SET min_parallel_index_scan_size = 0; SET set parallel_tuple_cost = 0; SET set parallel_setup_cost = 0; SET explain analyze select * from part_tbl6 where c1=(select max(c1) from part_tbl6_p3) and c1=(select max(c1) from part_tbl6_p3) and c1=(select max(c1) from part_tbl6_p3) or c1=(select max(c1) from part_tbl6_p3) and c1=(select max(c1) from part_tbl6_p3) and c1=(select max(c1) from part_tbl6_p3)or c1=(select max(c1) from part_tbl6_p3)and c1=(select max(c1) from part_tbl6_p3)or c1=(select max(c1) from part_tbl6_p3) and c1=(select max(c1) from part_tbl6_p3) or c1=(select max(c1) from part_tbl6_p3) or c1=(select max(c1) from part_tbl6) and c1=(select max(c1) from part_tbl6) ; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Gather (cost=599.91..1075.32 rows=8 width=40) (actual time=96.856..97.002 rows=1 loops=1) Workers Planned: 2 Params Evaluated: $1, $3, $5, $7, $9, $11, $13, $15, $17, $19, $21, $23, $25 Workers Launched: 2 InitPlan 1 (returns $1) -> Finalize Aggregate (cost=16.27..16.28 rows=1 width=4) (actual time=7.162..7.162 rows=1 loops=1) -> Gather (cost=16.25..16.26 rows=2 width=4) (actual time=0.430..7.157 rows=3 loops=1) Workers Planned: 2 Workers Launched: 2 -> Partial Aggregate (cost=16.25..16.26 rows=1 width=4) (actual time=0.008..0.008 rows=1 loops=3) -> Parallel Seq Scan on part_tbl6_p3 part_tbl6_p3_1 (cost=0.00..15.00 rows=500 width=4) (actual time=0.001..0.001 rows=0 loops=3) InitPlan 2 (returns $3) -> Finalize Aggregate (cost=16.27..16.28 rows=1 width=4) (actual time=6.110..6.111 rows=1 loops=1) -> Gather (cost=16.25..16.26 rows=2 width=4) (actual time=0.444..6.106 rows=3 loops=1) Workers Planned: 2 Workers Launched: 2 -> Partial Aggregate (cost=16.25..16.26 rows=1 width=4) (actual time=0.008..0.008 rows=1 loops=3) -> Parallel Seq Scan on part_tbl6_p3 part_tbl6_p3_2 (cost=0.00..15.00 rows=500 width=4) (actual time=0.001..0.001 rows=0 loops=3) InitPlan 3 (returns $5) -> Finalize Aggregate (cost=16.27..16.28 rows=1 width=4) (actual time=5.827..5.827 rows=1 loops=1) -> Gather (cost=16.25..16.26 rows=2 width=4) (actual time=0.385..5.811 rows=3 loops=1) Workers Planned: 2 Workers Launched: 2 -> Partial Aggregate (cost=16.25..16.26 rows=1 width=4) (actual time=0.007..0.007 rows=1 loops=3) -> Parallel Seq Scan on part_tbl6_p3 part_tbl6_p3_3 (cost=0.00..15.00 rows=500 width=4) (actual time=0.001..0.001 rows=0 loops=3) InitPlan 4 (returns $7) -> Finalize Aggregate (cost=16.27..16.28 rows=1 width=4) (actual time=5.644..5.644 rows=1 loops=1) -> Gather (cost=16.25..16.26 rows=2 width=4) (actual time=0.392..5.639 rows=3 loops=1) Workers Planned: 2 Workers Launched: 2 -> Partial Aggregate (cost=16.25..16.26 rows=1 width=4) (actual time=0.006..0.007 rows=1 loops=3) -> Parallel Seq Scan on part_tbl6_p3 part_tbl6_p3_4 (cost=0.00..15.00 rows=500 width=4) (actual time=0.001..0.001 rows=0 loops=3) InitPlan 5 (returns $9) -> Finalize Aggregate (cost=16.27..16.28 rows=1 width=4) (actual time=5.675..5.675 rows=1 loops=1) -> Gather (cost=16.25..16.26 rows=2 width=4) (actual time=0.329..5.670 rows=3 loops=1) Workers Planned: 2 Workers Launched: 2 -> Partial Aggregate (cost=16.25..16.26 rows=1 width=4) (actual time=0.006..0.007 rows=1 loops=3) -> Parallel Seq Scan on part_tbl6_p3 part_tbl6_p3_5 (cost=0.00..15.00 rows=500 width=4) (actual time=0.001..0.001 rows=0 loops=3) InitPlan 6 (returns $11) -> Finalize Aggregate (cost=16.27..16.28 rows=1 width=4) (actual time=5.123..5.124 rows=1 loops=1) -> Gather (cost=16.25..16.26 rows=2 width=4) (actual time=0.388..5.119 rows=3 loops=1) Workers Planned: 2 Workers Launched: 2 -> Partial Aggregate (cost=16.25..16.26 rows=1 width=4) (actual time=0.006..0.006 rows=1 loops=3) -> Parallel Seq Scan on part_tbl6_p3 part_tbl6_p3_6 (cost=0.00..15.00 rows=500 width=4) (actual time=0.001..0.001 rows=0 loops=3) InitPlan 7 (returns $13) -> Finalize Aggregate (cost=16.27..16.28 rows=1 width=4) (actual time=5.313..5.313 rows=1 loops=1) -> Gather (cost=16.25..16.26 rows=2 width=4) (actual time=0.374..5.309 rows=3 loops=1) Workers Planned: 2 Workers Launched: 2 -> Partial Aggregate (cost=16.25..16.26 rows=1 width=4) (actual time=0.006..0.007 rows=1 loops=3) -> Parallel Seq Scan on part_tbl6_p3 part_tbl6_p3_7 (cost=0.00..15.00 rows=500 width=4) (actual time=0.001..0.001 rows=0 loops=3) InitPlan 8 (returns $15) -> Finalize Aggregate (cost=16.27..16.28 rows=1 width=4) (actual time=4.836..4.836 rows=1 loops=1) -> Gather (cost=16.25..16.26 rows=2 width=4) (actual time=0.304..4.831 rows=3 loops=1) Workers Planned: 2 Workers Launched: 2 -> Partial Aggregate (cost=16.25..16.26 rows=1 width=4) (actual time=0.006..0.006 rows=1 loops=3) -> Parallel Seq Scan on part_tbl6_p3 part_tbl6_p3_8 (cost=0.00..15.00 rows=500 width=4) (actual time=0.001..0.001 rows=0 loops=3) InitPlan 9 (returns $17) -> Finalize Aggregate (cost=16.27..16.28 rows=1 width=4) (actual time=5.060..5.061 rows=1 loops=1) -> Gather (cost=16.25..16.26 rows=2 width=4) (actual time=0.362..5.054 rows=3 loops=1) Workers Planned: 2 Workers Launched: 2 -> Partial Aggregate (cost=16.25..16.26 rows=1 width=4) (actual time=0.006..0.007 rows=1 loops=3) -> Parallel Seq Scan on part_tbl6_p3 part_tbl6_p3_9 (cost=0.00..15.00 rows=500 width=4) (actual time=0.001..0.001 rows=0 loops=3) InitPlan 10 (returns $19) -> Finalize Aggregate (cost=16.27..16.28 rows=1 width=4) (actual time=4.787..4.787 rows=1 loops=1) -> Gather (cost=16.25..16.26 rows=2 width=4) (actual time=0.294..4.782 rows=3 loops=1) Workers Planned: 2 Workers Launched: 2 -> Partial Aggregate (cost=16.25..16.26 rows=1 width=4) (actual time=0.006..0.006 rows=1 loops=3) -> Parallel Seq Scan on part_tbl6_p3 part_tbl6_p3_10 (cost=0.00..15.00 rows=500 width=4) (actual time=0.001..0.001 rows=0 loops=3) InitPlan 11 (returns $21) -> Finalize Aggregate (cost=16.27..16.28 rows=1 width=4) (actual time=4.983..4.984 rows=1 loops=1) -> Gather (cost=16.25..16.26 rows=2 width=4) (actual time=0.332..4.977 rows=3 loops=1) Workers Planned: 2 Workers Launched: 2 -> Partial Aggregate (cost=16.25..16.26 rows=1 width=4) (actual time=0.006..0.006 rows=1 loops=3) -> Parallel Seq Scan on part_tbl6_p3 part_tbl6_p3_11 (cost=0.00..15.00 rows=500 width=4) (actual time=0.001..0.001 rows=0 loops=3) InitPlan 12 (returns $23) -> Finalize Aggregate (cost=210.43..210.44 rows=1 width=4) (actual time=14.137..14.147 rows=1 loops=1) -> Gather (cost=210.42..210.43 rows=2 width=4) (actual time=14.112..14.126 rows=3 loops=1) Workers Planned: 2 Workers Launched: 2 -> Partial Aggregate (cost=210.42..210.43 rows=1 width=4) (actual time=10.867..10.868 rows=1 loops=3) -> Append (cost=0.00..188.33 rows=8833 width=4) (actual time=0.038..7.931 rows=6667 loops=3) -> Parallel Seq Scan on part_tbl6_p2 part_tbl6_p2_1 (cost=0.00..86.66 rows=4166 width=4) (actual time=0.037..2.107 rows=3333 loops=3) -> Parallel Seq Scan on part_tbl6_p3 part_tbl6_p3_12 (cost=0.00..15.00 rows=500 width=4) (actual time=0.001..0.001 rows=0 loops=3) -> Parallel Seq Scan on part_tbl6_p1 part_tbl6_p1_1 (cost=0.00..86.67 rows=4167 width=4) (actual time=0.025..1.883 rows=3334 loops=3) InitPlan 13 (returns $25) -> Finalize Aggregate (cost=210.43..210.44 rows=1 width=4) (actual time=13.315..13.315 rows=1 loops=1) -> Gather (cost=210.42..210.43 rows=2 width=4) (actual time=13.183..13.308 rows=3 loops=1) Workers Planned: 2 Workers Launched: 2 -> Partial Aggregate (cost=210.42..210.43 rows=1 width=4) (actual time=9.977..9.977 rows=1 loops=3) -> Append (cost=0.00..188.33 rows=8833 width=4) (actual time=0.025..7.205 rows=6667 loops=3) -> Parallel Seq Scan on part_tbl6_p2 part_tbl6_p2_2 (cost=0.00..86.66 rows=4166 width=4) (actual time=0.024..1.916 rows=3333 loops=3) -> Parallel Seq Scan on part_tbl6_p3 part_tbl6_p3_13 (cost=0.00..15.00 rows=500 width=4) (actual time=0.001..0.001 rows=0 loops=3) -> Parallel Seq Scan on part_tbl6_p1 part_tbl6_p1_2 (cost=0.00..86.67 rows=4167 width=4) (actual time=0.012..1.647 rows=3334 loops=3) -> Append (cost=0.00..475.42 rows=4 width=40) (actual time=4.408..5.278 rows=0 loops=3) -> Parallel Seq Scan on part_tbl6_p2 (cost=0.00..222.07 rows=1 width=40) (actual time=2.755..2.755 rows=0 loops=3) Filter: (((c1 = $1) AND (c1 = $3) AND (c1 = $5)) OR ((c1 = $7) AND (c1 = $9) AND (c1 = $11)) OR ((c1 = $13) AND (c1 = $15)) OR ((c1 = $17) AND (c1 = $19)) OR (c1 = $21) OR ((c1 = $23) AND (c1 = $25))) Rows Removed by Filter: 3333 -> Parallel Seq Scan on part_tbl6_p3 (cost=0.00..31.25 rows=2 width=40) (actual time=0.001..0.001 rows=0 loops=3) Filter: (((c1 = $1) AND (c1 = $3) AND (c1 = $5)) OR ((c1 = $7) AND (c1 = $9) AND (c1 = $11)) OR ((c1 = $13) AND (c1 = $15)) OR ((c1 = $17) AND (c1 = $19)) OR (c1 = $21) OR ((c1 = $23) AND (c1 = $25))) -> Parallel Seq Scan on part_tbl6_p1 (cost=0.00..222.10 rows=1 width=40) (actual time=1.650..2.520 rows=0 loops=3) Filter: (((c1 = $1) AND (c1 = $3) AND (c1 = $5)) OR ((c1 = $7) AND (c1 = $9) AND (c1 = $11)) OR ((c1 = $13) AND (c1 = $15)) OR ((c1 = $17) AND (c1 = $19)) OR (c1 = $21) OR ((c1 = $23) AND (c1 = $25))) Rows Removed by Filter: 3333 Planning time: 2.240 ms Execution time: 98.560 ms (112 rows)