pip.sql

application/sql

Download

/*--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
---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;
---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;
---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);
---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 <;
---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));

--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));
--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));
---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;
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);
---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));
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));
---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;
explain analyze select * from (select max(n) from tv where n=(select max(n) from tv) and n is null)  d;
---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)));
---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));
---Testcase 13 -Subquery used in Group by  
explain analyze select count(*) from tv group by (select n from tv limit 1);
explain analyze select count(*) from tv group by (select n from tv where n not  in (select max(n) from tv) limit 1);
---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)));
explain analyze Select count(*) from tv order by (select max(n) from tv where (n,n)<>(select sum(1),avg(1)));
explain analyze Select count(*) from tv order by (select max(n) from tv where (n,n,1)<>(select sum(1),avg(1),count(*)));
--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;
\c postgres
SET min_parallel_table_scan_size = 0;
SET min_parallel_index_scan_size = 0;
set parallel_tuple_cost = 0;
set parallel_setup_cost = 0;
---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));
explain  analyze  verbose  select * from t1 where t1.k = (select max(t3.k) from t3 );
explain analyze   verbose  select * from t1 where (k,k)= (select max(k),min(k) from t3);
\c test
--Testcase 17- 
explain  verbose select * from (select * from pgbench_accounts) as t where aid= (select max(aid) from pgbench_accounts);
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);
--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));
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));
--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')));
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')));
--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) ;
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;
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);
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);
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')));
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);
--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);
--Testcase 22 - aggreagte functions used against partition table .
SET min_parallel_table_scan_size = 0;
SET min_parallel_index_scan_size = 0;
set parallel_tuple_cost = 0;
set parallel_setup_cost = 0;
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) ;