--create database
--create database test;
-- Running script for pgbanch tables against a scaling factor of 1,000 
--\! ./pgbench -i -s 1000 test > /tmp/my_logs 2>&1
--\c test
-- TestCase: 1
--Description 
/*
Explain plan catching Parallel index Only scan when in WHERE condition
   a) Single column of integer Type having index.
   b) condition: where the column having index is trying to fetch NOT NULL values("IS NOT NULL").
        -- Need to disable SEQUENTIAL SCAN  to reproduce explain plan catch "parallel index Only scan"
*/
\c test
set enable_seqscan =0;
explain analyze  verbose select count(*) from pgbench_accounts where aid is not null;
-- TestCase: 2
--Description 
/*
Explain plan catching  parallel index Only scan:
   a) both columns are of integer Type.
   b) 1 column is having index and another is non key column.
   c) condition: where the column having BETWEEN .. AND .. condition both in Index and Non-Index column in WHERE condition.
*/
explain analyze verbose  select count(aid) from pgbench_accounts where aid between 1000 and 90000000 ;

-- TestCase: 3
--Description 
/*
Explain plan catching  parallel index Only scan :
   a) both columns are of integer Type.
   b) 1 column is having index and another is non key column.
   c) condition: The column having SAFE FUNCTION against Index column in WHERE condition.
*/
CREATE or replace function fun_pis (n int) returns int parallel safe  as $$ begin return 1000; end; $$ language 'plpgsql';
explain  analyze verbose select aid from pgbench_accounts where aid > fun_pis(9) and aid < 90000000 ;
-- TestCase: 4
--Description 
/*
Explain plan catching Parallel index only scan when  in WHERE condition
   a) 3 columns, 1 column is having PRIMARY KEY on "int" Datatype and another non key columns having "int" and "char" datatype.
   b) condition: WHERE clause having 3 conditions, index column is selecting more records as compaired to other column conditions.
        -- Need to disable SEQUENTIAL  to reproduce explain plan catches "parallel index Only scan"
*/
\c test
CREATE TABLE tt2(c1 serial primary key, c2 int, c3 char(10));
INSERT INTO tt2(c2, c3) VALUES (generate_series(1,30), 'abc');
INSERT INTO tt2(c2, c3) VALUES (generate_series(31,1000000), 'pqrs');
analyze tt2;
set enable_seqscan =0;
explain analyze select count(c1) from tt2 where c1 < 999900;

-- TestCase: 5
--Description
/*
Explain plan catching Parallel index only scan when in WHERE condition
   a) 3 columns, 2 column is having composite index on "int" and "character" Datatype and another non key columns having "int" datatype.
   b) condition: WHERE clause having 1 multi-column condition selecting few records.
      -- Need to disable BITMAPSCAN, SEQUENTIALSCAN to reproduce explain plan catch "parallel index only scan"
*/

set enable_seqscan =0;
explain analyze verbose select count(*) from tt2 where (c1) NOT  IN((100));
-- TestCase: 6
--Description 
/*
Explain plan catching Parallel index only scan when in WHERE condition
   a) 2 columns, 1 non-key column having "text" datatype and another column having "array of integer[]" Datatype having index.
   b) condition: WHERE clause having 2 conditions, the array index column is selecting more records as compaired to other non key column condition.
*/
CREATE TABLE ary_tab (c1 text, c2 integer[]);
INSERT INTO ary_tab VALUES ('one', '{1,2,3}');
INSERT INTO ary_tab VALUES ('two', '{4,5,6}');
INSERT INTO ary_tab VALUES ('three', '{2,4,6}');
INSERT INTO ary_tab  (select 'four', '{7,8,9,10}' from generate_series(1,50));
INSERT INTO ary_tab  (select 'five', '{7,8,9,10}' from generate_series(1,1000000));
CREATE INDEX ary_idx on ary_tab (c2);
analyze;
explain analyze  verbose select count(*) from ary_tab where ARRAY[7,8,9,10]=c2 ;

-- TestCase: 7
--Description 
/*
Explain plan catching Parallel index only scan when in WHERE condition
   a) 4 columns, 1 non-key column having "TEXT" datatype and others are "INTEGER", "FLOAT", "VARCHAR" column having "COMPOSIT INDEX", and the same "INTEGER" column have "INDEX".
   b) condition: WHERE clause having 1 conditions, the index column is selecting more records.
      -- Need to disable SEQUENTIALSCAN /BITMAP  to reproduce explain plan catch "parallel index only scan"
*/
\c test
CREATE TABLE tst_pis(c1 int, c2 text, c3 float, c4 varchar(10));
INSERT INTO tst_pis (select x, 'c2_'||x, x/3,'c4_'||x from generate_series(1,1000000) x);
CREATE INDEX tst_cidx on tst_pis (c1,c3,c4);
CREATE INDEX tst_idx on tst_pis (c1);
set enable_seqscan =0;
set enable_bitmapscan = 0 ;
explain analyze verbose select count(1) from tst_pis where c1 > 100000;

-- TestCase: 8
--Description 
/*
Explain plan catching Parallel index Only  scan when in WHERE condition
   a) 2 columns: "TEXT" and "VARCHAR"
   b) 3 indexes: 1 composite index against both columns.
                 2 btree index, one is against "TEXT" datatype column and another against "VARCHAR" datatype column .
   c) Query Selecting Aggregate Count for GROUP BY both columns with equality conditions.
*/
CREATE TABLE t1_pis (c1 text, c2 varchar(30));
INSERT INTO t1_pis (select substr(md5(random()::text),1,10), NULL from generate_series(1,1000000));
update t1_pis set c2 = c1;
INSERT INTO t1_pis(select substr(md5(random()::text),1,10), substr(md5(random()::text),1,10) from generate_series(1,2000000));
CREATE INDEX t1_idx12 on t1_pis(c1,c2);
CREATE INDEX t1_idx1 on t1_pis(c1);
CREATE INDEX t1_idx2 on t1_pis(c2);
analyze t1_pis;
set parallel_setup_cost=0;
set parallel_tuple_cost=0;
explain analyze verbose select count(1) from t1_pis group by c1, c2;

-- TestCase: 9
--Description 
/*
Explain plan catching Parallel index Only scan:
   a) 3 columns("date", "varchar", "float") having composite index.
   b) 2 Non-Key columns.
         composite index columns having hard-coded data('25-09-2015', 'xyz', 1.1)
   c) Query Selecting with all composite index columns valid data.
      -- Need to disable SEQUENTIALSCAN  to produce "parallel index only scan".
*/
\c test
CREATE TABLE t2_pis(c1 int, c2 text, c3 date, c4 varchar(20), c5 float);
INSERT INTO t2_pis(select x, 'c2_'||x, to_date('25-09-2015','dd-mm-yyyy'), 'xyz',1.1 from generate_series(1,1000000) x);
CREATE INDEX t2_idx on t2_pis(c3, c4, c5);
analyze;
set enable_seqscan =0;
explain analyze verbose select count(*) from t2_pis where c3 = to_date('25-09-2015','dd-mm-yyyy') and c4 = 'xyz' and c5 = 1.1;

-- TestCase: 10
--Description
/*
Explain plan catching Parallel index only scan:
   a) 3 columns("date", "varchar", "float") having composite index.
   b) 2 Non-Key columns.
         composite index columns having hard-coded data('25-09-2015', 'xyz', 1.1)
   c) Query Selecting aggregate count, WHERE condition in 2 columns valid data from composite index of 3 columns.
*/
explain analyze verbose select count(*) from t2_pis where c3 = to_date('25-09-2015','dd-mm-yyyy') and c4 = 'xyz';

-- TestCase: 11
--Description 
/*
Explain plan catching Parallel index only  scan :
   a) Table having 1 column is of INTEGER type having Index.
   b) Query consisting of "CROSS JOIN" with same table as 3 different  table alias.
      -- Need to disable SEQUENTIALSCAN, PARALLEL_SETUP_COST and PARALLEL_TUPLE_COST to produce "Parallel Index Only Scan" in Normal table.
*/
\c test
CREATE TABLE t(n int);
INSERT INTO t select generate_series(1,5000000);
analyze t;
vacuum t;
CREATE INDEX cccc on t(n);
set enable_seqscan =0;
set parallel_setup_cost =0;
set parallel_tuple_cost =0;
explain analyze   verbose   SELECT * FROM t CROSS JOIN t as t1 cross join t as t2 where t1.n>=1 and t.n=1 and t2.n=1;

--TestCase:  12
--Description 
/*
Explain plan catching Parallel index Only scan :
   a) Table having 3 column is of INTEGER type having Index (composite)
   b) All the columns are in where conditions 
      -- Need to disable SEQUENTIALSCAN to produce "Parallel Index only  Scan"
*/

create table gv(n int,n1 int,n2 int);
insert into gv select (x%20),(x%10),(x%5) from generate_series(1,1000000)x;
create index fcc on gv(n,n1,n2);
analyze gv;
set enable_seqscan =0;
set enable_bitmapscan=0;
explain analyze verbose select count(*) from gv where n<23 and n1 <4 and n2<34; 

--TestCase:  13
--Description
/*
Explain plan catching Parallel index Only scan :
   a) Table having 3 column is of INTEGER type having Index (composite)
   b) using 1=1 in where condition
      -- Need to disable SEQUENTIALSCAN to produce "Parallel Index only  Scan"
*/
explain analyze  verbose select count(*) from gv where  1=1;

--TestCase:  14
--Description
/*
Explain plan catching Parallel index Only scan :
   a) Table having 3 column is of INTEGER type having Index (composite)
   b) NO  where condition
      -- Need to disable SEQUENTIALSCAN to produce "Parallel Index only  Scan"
*/
explain analyze  verbose select count(*) from gv;