--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.0 --Description /* Explain plan catching Parallel index scan when in WHERE condition 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 index is trying to fetch more rows as compaired to another column which doesn't have index. d) Relpages size is more than index size ( > 8 MB ) */ \c test explain analyse select * from pgbench_accounts where aid <50000000 and bid <=1 ; -- TestCase: 1.1 --Description - /* Explain plan catching Parallel index scan when in WHERE condition 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 index is trying to fetch more rows and another column trying to fetch NULL values("IS NULL"). */ \c test explain analyse select * from pgbench_accounts where aid <50000000 and bid is null; -- TestCase: 1.2 --Description /* Explain plan catching Parallel index 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 and INDEXONLYSCAN to reproduce explain plan catch "parallel index scan" */ \c test explain analyze verbose select count(*) from pgbench_accounts where aid is not null; set enable_seqscan = 0; set enable_indexonlyscan = 0; explain analyze verbose select count(*) from pgbench_accounts where aid is not null; -- TestCase: 1.3 --Description /* Explain plan catching Parallel index scan when in WHERE condition 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 index is trying to fetch more rows as compaired to another column which doesnt have index. d) ORDER BY index column DESC, results in Parallel Index Scan Backward. */ \c test explain analyse select * from pgbench_accounts where aid <50000000 and bid <=1 order by aid desc; -- TestCase: 1.4 --Description /* Explain plan catching parallel index 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. -- Need to disable SEQUENTIALSCAN to produce "parallel index scan". */ \c test explain analyze select count(aid) from pgbench_accounts where aid between 1000 and 90000000 and bid between 800 and 900; set enable_seqscan =0; explain analyze select count(aid) from pgbench_accounts where aid between 1000 and 90000000 and bid between 800 and 900; -- TestCase: 1.5 --Description /* Explain plan catching parallel index 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. d)need to disable SEQUENTIALSCAN */ \c test CREATE or replace function fun_pis (n int) returns int parallel safe as $$ begin return 1000; end; $$ language 'plpgsql'; explain analyze verbose select * from pgbench_accounts where aid > fun_pis(9) and aid < 90000000 and bid > 800 and bid < 900; set enable_seqscan =0; explain analyze verbose select * from pgbench_accounts where aid > fun_pis(9) and aid < 90000000 and bid > 800 and bid < 900; -- TestCase: 1.6 --Description /* Explain plan catching Parallel index scan when in WHERE condition a) Both columns are of integer Type. b) 1 column is having index and another is non key column. c) Query consisting of "Common Table Expression" with ORDER BY Index column DESC. d) condition: In CTE the column having index is trying to fetch more rows as compaired to another column which doesnt have index. Result in "Parallel Index Scan Backward" e) Relpages size is more than index size ( > 8 MB ) */ \c test explain analyse WITH t1 as (select * from pgbench_accounts where aid <50000000 and bid <=1 order by aid desc) select * from t1; -- TestCase: 2 --Description - /* Explain plan catching Parallel index scan when in WHERE condition a) Columns datatypes are timestamp/int b) 1 column is having index and another is non key column. c) condition: where the column having index is on "timestamp(6) without time zone" datatype. -- Need to disable SEQUENTIAL and BITMAP SCAN, to reproduce explain plan catch "parallel index scan" */ \c test CREATE TABLE tab4(c1 timestamp(6), c2 int); CREATE INDEX tab4_c1idx on tab4(c1); INSERT INTO tab4(select date '2016-01-01' - x, x from generate_series(1, 2000000) x); INSERT INTO tab4(select date '2016-01-01' - x, x from generate_series(1, 2000000) x); INSERT INTO tab4(select date '2016-01-01' - x, x from generate_series(1, 2000000) x); explain analyse select * from tab4 where c1 < '2015-10-03 00:00:00' and c2 >1999990; set enable_bitmapscan =0; set enable_seqscan =0; explain analyse select * from tab4 where c1 < '2015-10-03 00:00:00' and c2 >1999990; -- Object Cleanup: DROP TABLE tab4; -- TestCase: 3 --Description /* Explain plan catching Parallel index 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 and BITMAP SCAN, to reproduce explain plan catches "parallel index 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'); explain analyze select * from tt2 where c1 < 999900 and c2 <1000 and c3 ='abc'; set enable_seqscan =0; set enable_bitmapscan =0; explain analyze select * from tt2 where c1 < 999900 and c2 <1000 and c3 ='abc'; -- Object Cleanup: DROP TABLE tt2; -- TestCase: 4.0 --Description /* Explain plan catching Parallel index 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 3 conditions, composite index columns are selecting more records as compaired to other non key column conditions. -- Need to disable SEQUENTIAL SCAN to reproduce explain plan catch "parallel index scan" */ \c test CREATE TABLE t1 (c1 int,c2 char(9), c3 int); INSERT INTO t1 VALUES (generate_series(1,25),'xyz'); INSERT INTO t1 VALUES (generate_series(26,3000000),'aa'); analyze; CREATE INDEX t1_cidx on t1(c1,c2); update t1 set c3 = 500 where c1 <90; explain analyze select * from t1 where c1 <5999900 and c2 = 'aa' and c3 = 500; set enable_seqscan =0; explain analyze select * from t1 where c1 <5999900 and c2 = 'aa' and c3 = 500; -- TestCase: 4.1 --Description /* Explain plan catching Parallel index 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 and INDEXONLYSCAN to reproduce explain plan catch "parallel index scan" */ set enable_bitmapscan = 0 ; set enable_seqscan = 0; set enable_indexonlyscan = 0; explain analyze verbose select count(*) from t1 where (c1, c2) IN((100,'aa'),(1000,'aa'),(10000,'aa')); -- Object Cleanup: DROP TABLE t1; -- TestCase: 5 --Description /* Explain plan catching Parallel index 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. -- Need to disable SEQUENTIAL and BITMAP SCAN to reproduce explain plan catch "parallel index scan" */ \c test 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(1) from ary_tab where ARRAY[7,8,9,10]=c2 and c1 = 'four'; set enable_bitmapscan = 0 ; set enable_seqscan =0; explain analyze verbose select count(1) from ary_tab where ARRAY[7,8,9,10]=c2 and c1 = 'four'; -- Object Cleanup: DROP TABLE ary_tab; -- TestCase: 6 --Description /* Explain plan catching Parallel index scan when in WHERE condition a) 2 columns, 1 non-key column having "NULL" values and another column having "index" with condition NULLS FIRST. b) condition: WHERE clause having 2 conditions, the index column is selecting more records as compaired to other non key column condition . using ORDER BY index column with NULLS LAST. -- Need to disable SEQUENTIALSCAN to reproduce explain plan catch "parallel index scan" */ \c test CREATE TABLE s_pis(n int,n1 int); INSERT INTO s_pis (select null, 1+x from generate_series(1,1000000) x); INSERT INTO s_pis(n1) select NULL from generate_series(1,100000) ; CREATE INDEX test2_info_nulls_low ON s_pis (n1 NULLS FIRST); analyze; explain analyze select * from s_pis where n1 < 9000000 and n is not null order by n1 NULLS LAST; set enable_seqscan =0; explain analyze select * from s_pis where n1 < 9000000 and n is not null order by n1 NULLS LAST; -- Object Cleanup: DROP TABLE s_pis; -- TestCase: 7 --Description /* Explain plan catching Parallel index 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 / INDEXONLYSCAN to reproduce explain plan catch "parallel index 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); explain analyze verbose select count(1) from tst_pis where c1 > 100000; set enable_seqscan =0; set enable_bitmapscan = 0 ; set enable_indexonlyscan =0; explain analyze verbose select count(1) from tst_pis where c1 > 100000; -- TestCase: 7.1 --Description /* Explain plan catching Parallel index 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 2 conditions, the index column with "INTEGER" datatype is selecting more records, as compaired to other non key "TEXT" column. -- Need to disable SEQUENTIALSCAN /BITMAP SCAN to reproduce explain plan catch "parallel index scan" */ \c test explain analyze verbose select * from tst_pis where c1>=100 and c2 like 'c2_10%'; set enable_seqscan =0; set enable_bitmapscan = 0 ; explain analyze verbose select * from tst_pis where c1>=100 and c2 like 'c2_10%'; -- Object Cleanup: DROP TABLE tst_pis; -- TestCase: 8 --Description /* Explain plan catching Parallel index 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. -- Need to disable SEQUENTIALSCAN to produce INDEXONLYSCAN with composite index . -- Need to disable INDEXONLYSCAN to produce "parallel index scan" with another index on VARCHAR column. */ \c test 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); explain analyze verbose select count(1) from t1_pis group by c1, c2 having c1 = c2; set enable_seqscan =0; explain analyze verbose select count(1) from t1_pis group by c1, c2 having c1 = c2; set enable_indexonlyscan=0; explain analyze verbose select count(1) from t1_pis group by c1, c2 having c1 = c2; -- Object Cleanup: DROP TABLE t1_pis; -- TestCase: 9 --Description /* Explain plan catching Parallel index 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 and INDEXONLYSCAN to produce "parallel index 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; 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; set enable_seqscan =0; set enable_indexonlyscan =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: 9.1 --Description /* Explain plan catching Parallel index 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. -- Need to disable SEQUENTIALSCAN /BITMAP / INDEXONLYSCAN to produce "parallel index scan". */ \c test set enable_seqscan=0; set enable indexonlyscan=0; set enable_bitmapscan = 0 ; explain analyze verbose select count(*) from t2_pis where c3 = to_date('25-09-2015','dd-mm-yyyy') and c4 = 'xyz'; -- Object Cleanup: DROP TABLE t2_pis; -- TestCase: 10 /* Explain plan catching Parallel index scan: a) 2 columns(COMPOSIT type, INTEGER). b) COMPOSIT datatype having "text", "integer", "numeric" datatype c) INDEX on integer column. d) Query Selecting aggregate average of integer value in composit type column [avg((c1).cc1_c2)], GROUP BY index column. -- Need to disable SEQUENTIALSCAN to produce "parallel index scan". */ \c test CREATE TYPE cc1_typ AS (cc1_c1 text, cc1_c2 integer, cc1_c3 numeric); CREATE TABLE comp_tab (c1 cc1_typ, c2 integer); CREATE or replace function ins_comptab(a int) returns int as $$ BEGIN FOR i in 1..2500000 LOOP INSERT INTO comp_tab values(ROW(substr(md5(random()::text),1,10), i, i/3::float), 50); INSERT INTO comp_tab values(ROW(substr(md5(random()::text),1,10), i, i/3::float), 100); END LOOP; return 1; END; $$ LANGUAGE 'plpgsql'; select ins_comptab(1); CREATE INDEX comp_tab_idx on comp_tab (c2); explain analyze verbose select avg((c1).cc1_c2) from comp_tab group by c2; set enable_seqscan =0; explain analyze verbose select avg((c1).cc1_c2) from comp_tab group by c2; -- Object Cleanup: DROP FUNCTION ins_comptab(int); DROP TABLE comp_tab; DROP TYPE cc1_typ; -- TestCase: 11 --Description /* Explain plan catching Parallel index scan : a) The relation is a MATERIALIZED VIEW on a table of 2 columns of (integer, varchar) Type. b) varchar column on MATERIALIZED VIEW is having index and another is non key column. c) condition: Index column "IS NOT NULL" and Non-Index column "IS NULL" in WHERE condition. -- Need to disable SEQUENTIALSCAN and BITMAPSCAN to produce "parallel index scan" on MATERIALIZED VIEW Index. d) Relpages size is more than index size ( > 8 MB ) */ \c test CREATE TABLE tab11 (c1 int, c2 varchar(20)); INSERT INTO tab11(select x, substr(md5(random()::text),1,10)||x from generate_series(1,3000000) x); CREATE materialized view mview_tab11 as select * from tab11; CREATE index mvw_idx2 on mview_tab11 (c2); refresh materialized view mview_tab11 ; analyze mview_tab11; explain analyze verbose select * from mview_tab11 where c1 is null and c2 is not null; set enable_seqscan =0; set enable_bitmapscan =0; explain analyze verbose select * from mview_tab11 where c1 is null and c2 is not null; -- Object Cleanup: DROP MATERIALIZED VIEW mview_tab11; DROP TABLE tab11; -- TestCase: 12 /* Explain plan catching Parallel index scan : a) The relation is a VIEW on a table of 2 columns of (integer, varchar) Type. b) varchar column on the TABLE is having PRIMARY KEY and integer column is non key column. c) condition: PRIMARY KEY column "IS NOT NULL" selecting more records as compaired to Non-Key column in WHERE condition . -- Need to disable SEQUENTIALSCAN and BITMAPSCAN to produce "parallel index scan" on table PRIMARY KEY. */ \c test CREATE TABLE tab11 (c1 int, c2 varchar(20) PRIMARY KEY); INSERT INTO tab11(select x, substr(md5(random()::text),1,10)||x from generate_series(1,3000000) x); analyze ; CREATE view vw_tab11 as select * from tab11 where c1 < 2500000; explain analyze verbose select * from vw_tab11 where c1 < 200 and c2 is not null; set enable_seqscan =0; set enable_bitmapscan =0; explain analyze verbose select * from vw_tab11 where c1 < 200 and c2 is not null; -- Object Cleanup: DROP VIEW vw_tab11; DROP TABLE tab11; -- TestCase: 13 --Description /* Explain plan catching Parallel index scan : a) The relation is a VIEW on a table of 2 columns of (integer, varchar) Type. b) varchar column on the TABLE is having INDEX and integer column is non key column. c) condition: INDEX column "IS NOT NULL" selecting more records as compaired to Non-Key column in WHERE condition . ORDER BY INDEX column in DESCENDING Order, results in "Parallel Index Scan Backward" on table PRIMARY KEY. -- Need to disable SEQUENTIALSCAN and BITMAPSCAN to produce "Parallel Index Scan Backward" on table PRIMARY KEY. d) Relpages size is more than index size ( > 8 MB ) */ \c test CREATE TABLE tab11 (c1 int, c2 varchar(20)); CREATE index tab11_idx2 on tab11 (c2); INSERT INTO tab11(select x, substr(md5(random()::text),1,10)||x from generate_series(1,3000000) x); analyze; CREATE view vw_tab11 as select * from tab11 where c1 < 2500000; explain analyze verbose select * from vw_tab11 where c1 < 200 and c2 is not null order by c2 desc; set enable_seqscan =0; set enable_bitmapscan =0; explain analyze verbose select * from vw_tab11 where c1 < 200 and c2 is not null order by c2 desc; -- Object Cleanup: DROP VIEW vw_tab11; DROP TABLE tab11; -- TestCase: 14 --Description /* Explain plan catching Parallel index scan : a) The relation is a MATERIALIZED VIEW on a table of 2 columns of (integer, varchar) Type. b) varchar column on the MATERIALIZED VIEW is having INDEX and integer column is non key column. c) condition: INDEX column "IS NOT NULL" selecting more records as compaired to Non-Key column in WHERE condition . ORDER BY INDEX column in DESCENDING Order, results in "Parallel Index Scan Backward" on MATERIALIZED VIEW. -- Need to disable SEQUENTIALSCAN and BITMAPSCAN to produce "Parallel Index Scan Backward" on MATERIALIZED VIEW Index. d) Relpages size is more than index size ( > 8 MB ) */ \c test CREATE TABLE tab11 (c1 int, c2 varchar(20)); INSERT INTO tab11(select x, substr(md5(random()::text),1,10)||x from generate_series(1,3000000) x); CREATE materialized view mview_tab11 as select * from tab11 where c1 < 2500000; CREATE index mvw_idx2 on mview_tab11 (c2); refresh materialized view mview_tab11 ; analyze mview_tab11; explain analyze verbose select * from mview_tab11 where c1 < 200 and c2 is not null order by c2 desc; set enable_seqscan =0; set enable_bitmapscan =0; explain analyze verbose select * from mview_tab11 where c1 < 200 and c2 is not null order by c2 desc; -- Object Cleanup: DROP MATERIALIZED VIEW mview_tab11; DROP TABLE tab11; -- TestCase: 15 --Description /* Explain plan catching Parallel index scan : a) Both columns are of integer Type. b) 1 column is having PRIMARY KEY and another is non key column. c) 1 Query consist of "Temporary Table " with index, and another query consist of "Normal table" with index. d) condition: column having PRIMARY KEY is trying to fetch more rows as compaired to another column which doesnt have index. -- Need to disable SEQUENTIALSCAN to produce "Parallel Index Scan" in Normal table. -- Not able to reproduce "Parallel Index Scan" incase of "Temporary Table". e) Relpages size is more than index size ( > 8 MB ) */ \c test -- Normal Table : CREATE TABLE Tabl2(n int primary key,n1 int); INSERT INTO tabl2 select x,x+1 from generate_series(1,2000000) x; analyze tabl2; explain analyze verbose select * from tabl2 where n< 1000000 and n1 <500; set enable_seqscan =0; explain analyze verbose select * from tabl2 where n< 1000000 and n1 <500; -- Temporary table: CREATE temp table Tabl1(n int primary key,n1 int); INSERT INTO tabl1 select x,x+1 from generate_series(1,2000000) x; analyze tabl1; explain analyze verbose select * from tabl1 where n< 1000000 and n1 <500; -- Object Cleanup: DROP TABLE Tabl2; -- TestCase: 16 --Description /* Explain plan catching Parallel index 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, INDEXONLYSCAN, PARALLEL_SETUP_COST and PARALLEL_TUPLE_COST to produce "Parallel Index 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); 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; set enable_seqscan =0; set enable_indexonlyscan =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; -- Object Cleanup: DROP TABLE t;