pbhs.sql
application/octetstream
--Prerequisites
--Some of the testcases using TPC-H so setup should be there on the system.
--Creating TCP-H objects/loading data manually
create database test;
\c test
\i /home/centos/pg_tpch/dss/tpch-load.sql
\i /home/centos/pg_tpch/dss/tpch-pkeys.sql
\i /home/centos/pg_tpch/dss/tpch-alter.sql
\i /home/centos/pg_tpch/dss/tpch-index.sql
create view revenue0 (supplier_no, total_revenue) as
select
l_suppkey,
sum(l_extendedprice * (1 - l_discount))
from
lineitem
where
l_shipdate >= date '1997-06-01'
and l_shipdate < date '1997-06-01' + interval '3' month
group by
l_suppkey;
analyze lineitem ;
\timing
--TestCase: 1
/*Explain plan catching Parallel Bitmap Heap Scan when:
a) column is of timestamp datatype, used in composit index.
b) condition - Column (l_shipdate of timestamp type) is fetching rows from a given condition where it is fetching large number of tuples.
*/
explain analyze verbose
SELECT SUM(l_extendedprice) FROM lineitem
WHERE (l_shipdate >= '1995-01-01'::date)
AND (l_shipdate <='1996-03-31'::date);
--TestsCase: 2 / TPC-H 4.sql
/*
Explain plan catching Parallel Bitmap Heap Scan when:
a) Query fetching records from "Multiple Tables" with "Subqueries".
b) Condition: Lineitem table columns of date datatype having Composit PK as well as B-Tree index,
Orders table columns one is having PK and other is having B-Tree index, last column is a non-key column used in where condition.
*/
explain analyze verbose select
o_orderpriority,
count(*) as order_count
from
orders
where
o_orderdate >= date '1996-11-01'
and o_orderdate < date '1996-11-01' + interval '3' month
and exists (
select
*
from
lineitem
where
l_orderkey = o_orderkey
and l_commitdate < l_receiptdate
)
group by
o_orderpriority
order by
o_orderpriority
LIMIT 1;
--TestCase: 3 / TPC-H 6.sql
/*Explain plan catching Parallel Bitmap Heap Scan when:
a) Query fetching aggregate result from "Multiple Columns".
b) Condition: Lineitem table columns of date, numeric datatype having Composit B-Tree index used in where condition.
*/
explain analyze verbose select
sum(l_extendedprice * l_discount) as revenue
from
lineitem
where
l_shipdate >= date '1996-01-01'
and l_shipdate < date '1996-01-01' + interval '1' year
and l_discount between 0.03 - 0.01 and 0.03 + 0.01
and l_quantity < 25
LIMIT 1;
--TestCase: 4 / TPC-H 14.sql
/*Explain plan catching Parallel Bitmap Heap Scan when:
a) Query fetching aggregate result from "multiple tables multiple Columns".
b) Condition: Lineitem table with one column of date datatype is part of Composit B-Tree index and another column of bigint datatype is part of FK and B-Tree index used in where condition.
*/
explain analyze verbose select
100.00 * sum(case
when p_type like 'PROMO%'
then l_extendedprice * (1 - l_discount)
else 0
end) / sum(l_extendedprice * (1 - l_discount)) as promo_revenue
from
lineitem,
part
where
l_partkey = p_partkey
and l_shipdate >= date '1994-03-01'
and l_shipdate < date '1994-03-01' + interval '1' month
LIMIT 1;
--TestsCase: 5 / TPC-H 15.sql
/*Explain plan catching Parallel Bitmap Heap Scan when:
a) Query fetching data from "table and view having aggregate data".
b) Condition: supplier table with one column of integer datatype is part of PK and referenced to FK and other columns of character datatype of non-key columns used in where condition.*/
explain analyze verbose select
s_suppkey,
s_name,
s_address,
s_phone,
total_revenue
from
supplier,
revenue0
where
s_suppkey = supplier_no
and total_revenue = (
select
max(total_revenue)
from
revenue0
)
order by
s_suppkey
LIMIT 1;
--TestCase: 6
/*Explain plan catching Parallel Bitmap Heap Scan when:
a) Query fetching data from table having int datatype with B-Tree index and varchar datatype with non-key column.
*/
create table test (a int, b varchar);
create index idx on test(a);
insert into test values(generate_series(1,1000000), 'xxxxxxxxxxxxxxxxxxxxxxxxxxccccc');
insert into test values(generate_series(1,1000000), 'xxxxxxxxxxxxxxxxxxxxxxxxxxccccc');
insert into test values(generate_series(1,1000000), 'xxxxxxxxxxxxxxxxxxxxxxxxxxccccc');
insert into test values(generate_series(1,1000000), 'xxxxxxxxxxxxxxxxxxxxxxxxxxccccc');
insert into test values(generate_series(1,1000000), 'xxxxxxxxxxxxxxxxxxxxxxxxxxccccc');
analyze test;
explain analyze verbose select * from test where a < 100000 and b='aa';
drop table test;
--TestCase 7:
/* Explain plan catching Parallel Bitmap Heap Scan when:
a) Query fetching data from table having 2 columns of int datatype with individual B-Tree index and another non-key column with text datatype.*/
CREATE TABLE bmscantest (a int, b int, t text);
CREATE INDEX i_bmtest_a ON bmscantest(a);
CREATE INDEX i_bmtest_b ON bmscantest(b);
INSERT INTO bmscantest
SELECT (r%53), (r%59), 'foooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooo'
FROM generate_series(1,70000) r;
INSERT INTO bmscantest
SELECT (r%53), (r%59), 'foooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooo'
FROM generate_series(1,100000) r;
INSERT INTO bmscantest
SELECT (r%53), (r%59), 'foooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooo'
FROM generate_series(1,100000) r;
INSERT INTO bmscantest
SELECT (r%53), (r%59), 'foooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooo'
FROM generate_series(1,100000) r;
INSERT INTO bmscantest
SELECT (r%53), (r%59), 'foooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooo'
FROM generate_series(1,100000) r;
INSERT INTO bmscantest
SELECT (r%53), (r%59), 'foooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooo'
FROM generate_series(1,100000) r;
analyze bmscantest;
explain analyze verbose SELECT * FROM bmscantest WHERE a <= 10 and b <= 20 and t ='x';
drop table bmscantest;
--TestCase 8:
/* Explain plan catching Parallel Bitmap Heap Scan when:
a) Query fetching data from table having one columns of timestamp datatype with B-Tree index and another non-key column with int datatype.*/
\c test
create table t111(a timestamp(6), b int);
insert into t111 (select date '2001-09-28 01:01:01' + x, x from generate_series(1,1000000) x);
insert into t111 (select date '2001-09-28 01:01:01' + x, x from generate_series(1,1000000) x);
insert into t111 (select date '2001-09-28 01:01:01' + x, x from generate_series(1,1000000) x);
insert into t111 (select date '2001-09-28 01:01:01' + x, x from generate_series(1,1000000) x);
create index t111_tmidx on t111(a);
analyze t111;
explain analyze verbose select * from t111 where a <= date '2207-02-01 00:00:00' and b =500000;
drop table t111;
--TestCase 9:
/*Explain plan catching Parallel Bitmap Heap Scan when:
a) Query fetching data from table having 2 columns of timestamp and varchar datatype with composit B-Tree index and another non-key column with int datatype.*/
create table t1112(a timestamp(6), b int, c varchar(30));
insert into t1112 (select date '2001-09-28 01:01:01' + x, x, x||'char' from generate_series(1,1000000) x);
insert into t1112 (select date '2001-09-28 01:01:01' + x, x, x||'char' from generate_series(1,1000000) x);
insert into t1112 (select date '2001-09-28 01:01:01' + x, x, x||'char' from generate_series(1,1000000) x);
insert into t1112 (select date '2001-09-28 01:01:01' + x, x, x||'char' from generate_series(1,1000000) x);
create index t111_tmchridx on t1112(a,c);
analyze t1112;
explain analyze verbose select * from t1112 where a <= date '2207-02-01 00:00:00' and b =500000;
drop table t1112;
--TestCase 10:
/*Explain plan catching Parallel Bitmap Heap Scan when:
a) Query fetching data from table having 3 columns where BOOLEAN and INT datatype column is having composit B-Tree index and another non-key column with TEXT datatype.*/
create table test1(a boolean, b int, c text);
insert into test1(select 'f', x, 'asdfghjkl' from generate_series(1,10000) x);
insert into test1(select 't', x, 'asdfghjkl' from generate_series(1,3000000) x);
create index test_idx on test1(a,b);
analyze test1;
explain analyze verbose select * from test1 where a = 't' and b < 200000 and c = 'xyz';
drop table test1;
--TestCase 10.0
/*Explain plan catching Parallel bitmap heap scan when:
a) The relation is a MATERIALIZED VIEW on resulting from two tables JOIN with columns(integer, varchar, tiestamp and boolean) datatype.
b) INTEGER and TIMESTAMP column on the MATERIALIZED VIEW is having COMPOSIT INDEX and VARCHAR and BOOLEAN column is non key column.
*/
CREATE TABLE t1 (c1 int, c2 varchar(30));
CREATE TABLE t2 (c3 int, c4 timestamp, c5 boolean);
INSERT INTO t1(select x, 'asdfghjkl' from generate_series(1, 1000000) x);
INSERT INTO t1(select x, 'asdfghjkl' from generate_series(1, 1000000) x);
INSERT INTO t2(select x, date '2016-01-01'-x, cast(cast(random() as int) as boolean) from generate_series(1, 2000000) x);
INSERT INTO t2(select x, date '2016-01-01'-x, cast(cast(random() as int) as boolean) from generate_series(1, 2000000) x);
CREATE MATERIALIZED VIEW mview as (select t1.c1, t1.c2, t2.c4, t2.c5 from t1, t2 where t1.c1 = t2.c3);
CREATE INDEX mview_idx on mview(c1,c4);
refresh materialized view mview;
analyze mview;
explain analyze verbose select * from mview where c1 <= 180000 and c4 <='2015-02-03' and c5 is null;
DROP MATERIALIZED VIEW mview;
DROP TABLE t1;
DROP TABLE t2;
--TestCase 11:
/*Explain plan catching Parallel bitmap heap scan when:
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, to reproduce explain plan catch "parallel bitmap heap scan"
*/
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);
analyze tab4;
explain analyse verbose select * from tab4 where c1 < '2017-10-03 00:00:00' and c2 <90000;
set enable_seqscan to 0;
explain analyse verbose select * from tab4 where c1 < '2017-10-03 00:00:00' and c2 <90000;
drop table tab4;
--TestCase 12:
/*Explain plan catching Parallel bitmap heap 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 SCAN, to reproduce explain plan catches "Parallel bitmap heap scan"
*/
\c test
\timing
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;
explain analyze verbose select * from tt2 where c1 < 999900 and c2 <1000 and c3 ='abc';
set enable_seqscan to 0;
explain analyze verbose select * from tt2 where c1 < 999900 and c2 <1000 and c3 ='abc';
drop table tt2;
--TestCase 13:
/*Explain plan catching Parallel bitmap heap 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 bitmap heap scan"
\c test
\timing
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');
CREATE INDEX t1_cidx on t1(c1,c2);
update t1 set c3 = 500 where c1 <90;
analyze t1;
explain analyze verbose select * from t1 where c1 <5999900 and c2 = 'aa' and c3 = 500;
set enable_seqscan to 0;
explain analyze verbose select * from t1 where c1 <5999900 and c2 = 'aa' and c3 = 500;
drop table t1;
--TestCase 14:
/*Explain plan catching Parallel bitmap heap 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 to reproduce explain plan catch "Parallel bitmap heap scan"
*/
\c test
\timing
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 ary_tab;
explain analyze verbose select count(1) from ary_tab where ARRAY[7,8,9,10]=c2 and c1 = 'four';
set enable_seqscan to 0;
explain analyze verbose select count(1) from ary_tab where ARRAY[7,8,9,10]=c2 and c1 = 'four';
drop table ary_tab;
--TestCase 15:
/*Explain plan catching Parallel bitmap heap 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 bitmap heap scan"
*/
\c test
\timing
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 s_pis;
explain analyze verbose select * from s_pis where n1 < 9000000 and n is not null order by n1 NULLS LAST;
set enable_seqscan to 0;
explain analyze verbose select * from s_pis where n1 < 9000000 and n is not null order by n1 NULLS LAST;
drop table s_pis;
--TestCase 16:
/*Explain plan catching Parallel bitmap heap 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 to reproduce explain plan catch "Parallel bitmap heap scan"
*/
\c test
\timing
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);
analyze tst_pis;
explain analyze verbose select count(1) from tst_pis where c1 > 100000;
set enable_seqscan to 0;
explain analyze verbose select count(1) from tst_pis where c1 > 100000;
drop table tst_pis;
--TestCase 17:
/*Explain plan catching Parallel bitmap heap 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 SEQUENTIAL SCAN to reproduce explain plan catch "Parallel bitmap heap scan"
*/
\c test
\timing
CREATE TABLE tst_pis1(c1 int, c2 text, c3 float, c4 varchar(10));
INSERT INTO tst_pis1 (select x, 'c2_'||x, x/3,'c4_'||x from generate_series(1,1000000) x);
CREATE INDEX tst_cidx1 on tst_pis1 (c1,c3,c4);
CREATE INDEX tst_idx1 on tst_pis1 (c1);
analyze tst_pis1;
explain analyze verbose select * from tst_pis1 where c1>=100 and c2 like 'c2_10%';
set enable_seqscan =0;
explain analyze verbose select * from tst_pis1 where c1>=100 and c2 like 'c2_10%';
drop table tst_pis1;
--TestCase 18:
/*Explain plan catching Parallel bitmap heap 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 bitmap heap scan".
*/
\c test
\timing
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 t2_pis;
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;
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 19:
/*Explain plan catching Parallel bitmap heap 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 to produce "Parallel bitmap heap scan".
*/
\c test
\timing
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';
drop table t2_pis cascade;
--TestCase 20:
/*Explain plan catching Parallel bitmap heap 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 to produce "parallel bitmap heap scan" on MATERIALIZED VIEW Index.
d) Relpages size is more than index size ( > 8 MB )
*/
\c test
\timing
CREATE TABLE tab113 (c1 int, c2 varchar(20));
INSERT INTO tab113(select x, substr(md5(random()::text),1,10)||x from generate_series(1,3000000) x);
CREATE materialized view mview_tab113 as select * from tab113;
CREATE index mvw_idx32 on mview_tab113 (c2);
refresh materialized view mview_tab113 ;
analyze mview_tab113;
explain analyze verbose select * from mview_tab113 where c1 is null and c2 is not null;
set enable_seqscan =0;
explain analyze verbose select * from mview_tab113 where c1 is null and c2 is not null;
drop table tab113 cascade;
--TestCase 21:
/*Explain plan catching Parallel bitmap heap 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 to produce "Parallel bitmap heap scan" on table PRIMARY KEY.
*/
\c test
\timing
CREATE TABLE tab121 (c1 int, c2 varchar(20) PRIMARY KEY);
INSERT INTO tab121(select x, substr(md5(random()::text),1,10)||x from generate_series(1,3000000) x);
CREATE view vw_tab121 as select * from tab121 where c1 < 2500000;
analyze tab121 ;
explain analyze verbose select * from vw_tab121 where c1 < 200 and c2 is not null;
set enable_seqscan =0;
explain analyze verbose select * from vw_tab121 where c1 < 200 and c2 is not null;
drop view vw_tab121;
drop table tab121;
--TestCase 22:
/*Explain plan catching Parallel bitmap heap 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,.
-- Need to disable SEQUENTIALSCAN to produce "Parallel bitmap heap Scan"
d) Relpages size is more than index size ( > 8 MB )
*/
\c test
\timing
CREATE TABLE tab131 (c1 int, c2 varchar(20));
CREATE index tab131_idx2 on tab131 (c2);
INSERT INTO tab131(select x, substr(md5(random()::text),1,10)||x from generate_series(1,3000000) x);
CREATE view vw_tab131 as select * from tab131 where c1 < 2500000;
analyze tab131;
explain analyze verbose select * from vw_tab131 where c1 < 200 and c2 is not null order by c2 desc;
set enable_seqscan to 0;
explain analyze verbose select * from vw_tab131 where c1 < 200 and c2 is not null order by c2 desc;
drop view vw_tab131;
drop table tab131 cascade;
--TestCase 23:
/*Explain plan catching Parallel bitmap heap 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
--disable SEQUENTIALSCAN to produce "Parallel bitmap heap scan"
d) Relpages size is more than index size ( > 8 MB )
*/
\c test
\timing
CREATE TABLE tab141 (c1 int, c2 varchar(20));
INSERT INTO tab141(select x, substr(md5(random()::text),1,10)||x from generate_series(1,3000000) x);
CREATE materialized view mview_tab141 as select * from tab141 where c1 < 2500000;
CREATE index mvw_idx42 on mview_tab141 (c2);
refresh materialized view mview_tab141 ;
analyze mview_tab141;
explain analyze verbose select * from mview_tab141 where c1 < 200 and c2 is not null order by c2 desc;
set enable_seqscan to 0;
explain analyze verbose select * from mview_tab141 where c1 < 200 and c2 is not null order by c2 desc;
drop table tab141 cascade;
--TestCase 24:
/*
Explain plan catching Parallel bitmap heap 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 bitmap heap scan can" in Normal table.
-- Not able to reproduce "Parallel bitmap heap scan" incase of "Temporary Table".
e) Relpages size is more than index size ( > 8 MB )
*/
-- Normal Table :
\c test
\timing
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 to 0;
explain analyze verbose select * from tabl2 where n< 1000000 and n1 <500;
\c test
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;
set enable_seqscan to 0;
explain analyze verbose select * from tabl1 where n< 1000000 and n1 <500;
drop table Tabl2;
drop table tabl1;
--TestCase 25:
/*Explain plan catching Parallel bitmap heap 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 bitmap heap scan"
*/
\c test
\timing
CREATE TABLE testing(n int);
INSERT INTO testing select generate_series(1,5000000);
CREATE INDEX cccc on testing(n);
analyze testing;
vacuum testing;
explain analyze verbose SELECT * FROM testing CROSS JOIN testing as t1 cross join testing as t2 where t1.n>=1 and testing.n=1 and t2.n=1;
set enable_seqscan to 0;
set enable_indexonlyscan to 0;
set parallel_setup_cost to 0;
set parallel_tuple_cost to 0;
explain analyze verbose SELECT * FROM testing CROSS JOIN testing as t1 cross join testing as t2 where t1.n>=1 and testing.n=1 and t2.n=1;
drop table testing;