--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 ; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Gather (cost=1000.57..2002230.56 rows=4255 width=97) (actual time=53.630..97660.835 rows=100000 loops=1) Workers Planned: 2 Workers Launched: 2 -> Parallel Index Scan using pgbench_accounts_pkey on pgbench_accounts (cost=0.57..2000805.06 rows=1773 width=97) (actual time=9.715..97365.739 rows=33333 loops=3) Index Cond: (aid < 50000000) Filter: (bid <= 1) Rows Removed by Filter: 16633333 Planning time: 63.688 ms Execution time: 97689.225 ms (9 rows) -- 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; QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------------------------------------- Gather (cost=1000.57..1949791.43 rows=1 width=97) (actual time=97100.635..97100.635 rows=0 loops=1) Workers Planned: 2 Workers Launched: 2 -> Parallel Index Scan using pgbench_accounts_pkey on pgbench_accounts (cost=0.57..1948791.33 rows=1 width=97) (actual time=96902.142..96902.142 rows=0 loops=3) Index Cond: (aid < 50000000) Filter: (bid IS NULL) Rows Removed by Filter: 16666666 Planning time: 40.131 ms Execution time: 97108.171 ms (9 rows) -- 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; QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------------------------------- Finalize Aggregate (cost=2161178.55..2161178.56 rows=1 width=8) (actual time=148264.134..148264.135 rows=1 loops=1) Output: count(*) -> Gather (cost=2161178.33..2161178.54 rows=2 width=8) (actual time=148263.522..148264.117 rows=3 loops=1) Output: (PARTIAL count(*)) Workers Planned: 2 Workers Launched: 2 -> Partial Aggregate (cost=2160178.33..2160178.34 rows=1 width=8) (actual time=148136.192..148136.193 rows=1 loops=3) Output: PARTIAL count(*) Worker 0: actual time=148072.665..148072.666 rows=1 loops=1 Worker 1: actual time=148072.866..148072.867 rows=1 loops=1 -> Parallel Seq Scan on public.pgbench_accounts (cost=0.00..2056011.67 rows=41666667 width=0) (actual time=4.714..138028.788 rows=33333333 loops=3) Filter: (pgbench_accounts.aid IS NOT NULL) Worker 0: actual time=0.051..137924.856 rows=33525356 loops=1 Worker 1: actual time=0.053..137963.497 rows=33366101 loops=1 Planning time: 22.170 ms Execution time: 148270.117 ms (16 rows) set enable_seqscan = 0; set enable_indexonlyscan = 0; explain analyze verbose select count(*) from pgbench_accounts where aid is not null; QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Finalize Aggregate (cost=4007958.12..4007958.13 rows=1 width=8) (actual time=179067.104..179067.104 rows=1 loops=1) Output: count(*) -> Gather (cost=4007957.90..4007958.11 rows=2 width=8) (actual time=179066.878..179067.095 rows=3 loops=1) Output: (PARTIAL count(*)) Workers Planned: 2 Workers Launched: 2 -> Partial Aggregate (cost=4006957.90..4006957.91 rows=1 width=8) (actual time=179047.047..179047.048 rows=1 loops=3) Output: PARTIAL count(*) Worker 0: actual time=179037.350..179037.350 rows=1 loops=1 Worker 1: actual time=179037.299..179037.300 rows=1 loops=1 -> Parallel Index Scan using pgbench_accounts_pkey on public.pgbench_accounts (cost=0.57..3902791.23 rows=41666667 width=0) (actual time=27.307..169156.323 rows=33333333 loops=3) Index Cond: (pgbench_accounts.aid IS NOT NULL) Worker 0: actual time=13.949..169155.034 rows=33280014 loops=1 Worker 1: actual time=16.641..169163.527 rows=33419826 loops=1 Planning time: 0.210 ms Execution time: 179077.145 ms (16 rows) -- 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; QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Sort (cost=2002487.03..2002497.67 rows=4255 width=97) (actual time=427368.256..427406.314 rows=100000 loops=1) Sort Key: aid DESC Sort Method: external sort Disk: 10472kB -> Gather (cost=1000.57..2002230.56 rows=4255 width=97) (actual time=426482.285..427143.008 rows=100000 loops=1) Workers Planned: 2 Workers Launched: 2 -> Parallel Index Scan Backward using pgbench_accounts_pkey on pgbench_accounts (cost=0.57..2000805.06 rows=1773 width=97) (actual time=426444.502..427174.112 rows=33333 loops=3) Index Cond: (aid < 50000000) Filter: (bid <= 1) Rows Removed by Filter: 16633333 Planning time: 15.625 ms Execution time: 427419.895 ms (12 rows) -- 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; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------------------- Finalize Aggregate (cost=2483297.99..2483298.00 rows=1 width=8) (actual time=143194.242..143194.243 rows=1 loops=1) -> Gather (cost=2483297.78..2483297.99 rows=2 width=8) (actual time=143193.914..143194.222 rows=3 loops=1) Workers Planned: 2 Workers Launched: 2 -> Partial Aggregate (cost=2482297.78..2482297.79 rows=1 width=8) (actual time=143184.484..143184.485 rows=1 loops=3) -> Parallel Seq Scan on pgbench_accounts (cost=0.00..2472678.33 rows=3847777 width=4) (actual time=113977.715..141752.535 rows=3366667 loops=3) Filter: ((aid >= 1000) AND (aid <= 90000000) AND (bid >= 800) AND (bid <= 900)) Rows Removed by Filter: 29966667 Planning time: 37.582 ms Execution time: 143198.896 ms (10 rows) set enable_seqscan =0; explain analyze select count(aid) from pgbench_accounts where aid between 1000 and 90000000 and bid between 800 and 900; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Finalize Aggregate (cost=3935843.05..3935843.06 rows=1 width=8) (actual time=164064.453..164064.454 rows=1 loops=1) -> Gather (cost=3935842.83..3935843.04 rows=2 width=8) (actual time=164064.063..164064.441 rows=3 loops=1) Workers Planned: 2 Workers Launched: 2 -> Partial Aggregate (cost=3934842.83..3934842.84 rows=1 width=8) (actual time=164040.194..164040.194 rows=1 loops=3) -> Parallel Index Scan using pgbench_accounts_pkey on pgbench_accounts (cost=0.57..3925223.39 rows=3847777 width=4) (actual time=144727.823..162872.005 rows=3366667 loops=3) Index Cond: ((aid >= 1000) AND (aid <= 90000000)) Filter: ((bid >= 800) AND (bid <= 900)) Rows Removed by Filter: 26633000 Planning time: 0.494 ms Execution time: 164073.735 ms (11 rows) -- 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. */ \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; QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------------------------- Gather (cost=1000.00..13192770.10 rows=3024251 width=97) (actual time=119424.754..148514.459 rows=9900000 loops=1) Output: aid, bid, abalance, filler Workers Planned: 2 Workers Launched: 2 -> Parallel Seq Scan on public.pgbench_accounts (cost=0.00..12889345.00 rows=1260105 width=97) (actual time=119095.477..145117.056 rows=3300000 loops=3) Output: aid, bid, abalance, filler Filter: ((pgbench_accounts.aid < 90000000) AND (pgbench_accounts.bid > 800) AND (pgbench_accounts.bid < 900) AND (pgbench_accounts.aid > fun_pis(9))) Rows Removed by Filter: 30033333 Worker 0: actual time=118926.872..146705.543 rows=3789015 loops=1 Worker 1: actual time=118936.029..146566.646 rows=3707366 loops=1 Planning time: 28.120 ms Execution time: 149397.241 ms (12 rows) 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; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Gather (cost=1000.57..13473033.34 rows=3024251 width=97) (actual time=146322.787..164363.113 rows=9900000 loops=1) Output: aid, bid, abalance, filler Workers Planned: 2 Workers Launched: 2 -> Parallel Index Scan using pgbench_accounts_pkey on public.pgbench_accounts (cost=0.57..13169608.24 rows=1260105 width=97) (actual time=146387.150..161031.921 rows=3300000 loops=3) Output: aid, bid, abalance, filler Index Cond: (pgbench_accounts.aid < 90000000) Filter: ((pgbench_accounts.bid > 800) AND (pgbench_accounts.bid < 900) AND (pgbench_accounts.aid > fun_pis(9))) Rows Removed by Filter: 26700000 Worker 0: actual time=146419.198..162778.687 rows=3634746 loops=1 Worker 1: actual time=146419.881..162797.907 rows=3752384 loops=1 Planning time: 0.585 ms Execution time: 165311.151 ms (13 rows) -- 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 SCAN 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; QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------- Bitmap Heap Scan on tab4 (cost=41533.05..107047.71 rows=735148 width=12) (actual time=1539.631..2908.478 rows=30 loops=1) Recheck Cond: (c1 < '2015-10-03 00:00:00'::timestamp without time zone) Filter: (c2 > 1999990) Rows Removed by Filter: 5999700 Heap Blocks: exact=32433 -> Bitmap Index Scan on tab4_c1idx (cost=0.00..41349.26 rows=2205444 width=0) (actual time=884.557..884.557 rows=5999730 loops=1) Index Cond: (c1 < '2015-10-03 00:00:00'::timestamp without time zone) Planning time: 25.221 ms Execution time: 2909.031 ms (9 rows) set enable_bitmapscan =0; set enable_seqscan =0; explain analyse select * from tab4 where c1 < '2015-10-03 00:00:00' and c2 >1999990; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------- Gather (cost=1000.43..271429.69 rows=596 width=12) (actual time=0.502..2331.529 rows=30 loops=1) Workers Planned: 2 Workers Launched: 2 -> Parallel Index Scan using tab4_c1idx on tab4 (cost=0.43..270370.09 rows=248 width=12) (actual time=1547.107..2324.074 rows=10 loops=3) Index Cond: (c1 < '2015-10-03 00:00:00'::timestamp without time zone) Filter: (c2 > 1999990) Rows Removed by Filter: 1999900 Planning time: 0.414 ms Execution time: 2337.319 ms (9 rows) -- 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 /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'; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------- Gather (cost=1000.00..12143.79 rows=361 width=52) (actual time=1.254..157.057 rows=30 loops=1) Workers Planned: 2 Workers Launched: 2 -> Parallel Seq Scan on tt2 (cost=0.00..11107.69 rows=150 width=52) (actual time=95.434..147.316 rows=10 loops=3) Filter: ((c1 < 999900) AND (c2 < 1000) AND (c3 = 'abc'::bpchar)) Rows Removed by Filter: 333323 Planning time: 4.827 ms Execution time: 158.621 ms (8 rows) set enable_seqscan =0; set enable_bitmapscan =0; explain analyze select * from tt2 where c1 < 999900 and c2 <1000 and c3 ='abc'; QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------- Gather (cost=1000.42..34675.21 rows=1 width=19) (actual time=0.451..271.919 rows=30 loops=1) Workers Planned: 1 Workers Launched: 1 -> Parallel Index Scan using tt2_pkey on tt2 (cost=0.42..33675.11 rows=1 width=19) (actual time=132.713..268.392 rows=15 loops=2) Index Cond: (c1 < 999900) Filter: ((c2 < 1000) AND (c3 = 'abc'::bpchar)) Rows Removed by Filter: 499934 Planning time: 0.464 ms Execution time: 273.766 ms (9 rows) -- 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 and BITMAPSCAN 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; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------- Gather (cost=1000.43..130349.03 rows=1 width=18) (actual time=0.476..743.023 rows=64 loops=1) Workers Planned: 2 Workers Launched: 2 -> Parallel Index Scan using t1_cidx on t1 (cost=0.43..129348.93 rows=1 width=18) (actual time=484.320..731.761 rows=21 loops=3) Index Cond: ((c1 < 5999900) AND (c2 = 'aa'::bpchar)) Filter: (c3 = 500) Rows Removed by Filter: 999970 Planning time: 0.303 ms Execution time: 749.194 ms (9 rows) set enable_seqscan =0; explain analyze select * from t1 where c1 <5999900 and c2 = 'aa' and c3 = 500; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------- Gather (cost=1000.43..130349.03 rows=1 width=18) (actual time=0.205..566.904 rows=64 loops=1) Workers Planned: 2 Workers Launched: 2 -> Parallel Index Scan using t1_cidx on t1 (cost=0.43..129348.93 rows=1 width=18) (actual time=375.606..564.438 rows=21 loops=3) Index Cond: ((c1 < 5999900) AND (c2 = 'aa'::bpchar)) Filter: (c3 = 500) Rows Removed by Filter: 999970 Planning time: 0.155 ms Execution time: 570.907 ms (9 rows) -- 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')); QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------- Finalize Aggregate (cost=129099.15..129099.16 rows=1 width=8) (actual time=737.476..737.476 rows=1 loops=1) Output: count(*) -> Gather (cost=129098.93..129099.14 rows=2 width=8) (actual time=737.378..737.468 rows=3 loops=1) Output: (PARTIAL count(*)) Workers Planned: 2 Workers Launched: 2 -> Partial Aggregate (cost=128098.93..128098.94 rows=1 width=8) (actual time=731.167..731.167 rows=1 loops=3) Output: PARTIAL count(*) Worker 0: actual time=728.312..728.313 rows=1 loops=1 Worker 1: actual time=728.373..728.373 rows=1 loops=1 -> Parallel Index Scan using t1_cidx on public.t1 (cost=0.43..128098.93 rows=1 width=0) (actual time=243.716..731.152 rows=1 loops=3) Index Cond: (t1.c2 = 'aa'::bpchar) Filter: ((t1.c1 = 100) OR (t1.c1 = 1000) OR (t1.c1 = 10000)) Rows Removed by Filter: 999991 Worker 0: actual time=2.591..728.290 rows=1 loops=1 Worker 1: actual time=728.365..728.365 rows=0 loops=1 Planning time: 0.381 ms Execution time: 740.945 ms (18 rows) -- 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 SEQUENTIALSCAN and BITMAP 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); explain analyze verbose select count(1) from ary_tab where ARRAY[7,8,9,10]=c2 and c1 = 'four'; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------- Aggregate (cost=8352.25..8352.26 rows=1 width=8) (actual time=663.569..663.570 rows=1 loops=1) Output: count(1) -> Bitmap Heap Scan on public.ary_tab (cost=185.93..8352.18 rows=25 width=0) (actual time=395.743..663.545 rows=50 loops=1) Output: c1, c2 Recheck Cond: ('{7,8,9,10}'::integer[] = ary_tab.c2) Filter: (ary_tab.c1 = 'four'::text) Rows Removed by Filter: 1000000 Heap Blocks: exact=9347 -> Bitmap Index Scan on ary_idx (cost=0.00..185.93 rows=5000 width=0) (actual time=393.691..393.691 rows=1000050 loops=1) Index Cond: ('{7,8,9,10}'::integer[] = ary_tab.c2) Planning time: 0.952 ms Execution time: 663.728 ms (12 rows) 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'; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------- Finalize Aggregate (cost=51903.75..51903.76 rows=1 width=8) (actual time=274.901..274.901 rows=1 loops=1) Output: count(1) -> Gather (cost=51903.54..51903.75 rows=2 width=8) (actual time=274.806..274.895 rows=3 loops=1) Output: (PARTIAL count(1)) Workers Planned: 2 Workers Launched: 2 -> Partial Aggregate (cost=50903.54..50903.55 rows=1 width=8) (actual time=272.394..272.394 rows=1 loops=3) Output: PARTIAL count(1) Worker 0: actual time=271.185..271.186 rows=1 loops=1 Worker 1: actual time=271.388..271.388 rows=1 loops=1 -> Parallel Index Scan using ary_idx on public.ary_tab (cost=0.42..50903.43 rows=42 width=0) (actual time=180.883..272.380 rows=17 loops=3) Index Cond: ('{7,8,9,10}'::integer[] = ary_tab.c2) Filter: (ary_tab.c1 = 'four'::text) Rows Removed by Filter: 333333 Worker 0: actual time=271.177..271.177 rows=0 loops=1 Worker 1: actual time=271.382..271.382 rows=0 loops=1 Planning time: 0.125 ms Execution time: 277.408 ms (18 rows) -- 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; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------- Sort (cost=11498.28..11498.28 rows=1 width=8) (actual time=84.473..84.473 rows=0 loops=1) Sort Key: n1 Sort Method: quicksort Memory: 25kB -> Gather (cost=1000.00..11498.27 rows=1 width=8) (actual time=84.462..84.462 rows=0 loops=1) Workers Planned: 2 Workers Launched: 2 -> Parallel Seq Scan on s_pis (cost=0.00..10498.17 rows=1 width=8) (actual time=76.151..76.151 rows=0 loops=3) Filter: ((n IS NOT NULL) AND (n1 < 9000000)) Rows Removed by Filter: 366667 Planning time: 0.515 ms Execution time: 85.761 ms (11 rows) set enable_seqscan =0; explain analyze select * from s_pis where n1 < 9000000 and n is not null order by n1 NULLS LAST; QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------------------- Sort (cost=29694.08..29694.09 rows=1 width=8) (actual time=221.899..221.899 rows=0 loops=1) Sort Key: n1 Sort Method: quicksort Memory: 25kB -> Gather (cost=1000.43..29694.07 rows=1 width=8) (actual time=221.893..221.893 rows=0 loops=1) Workers Planned: 1 Workers Launched: 1 -> Parallel Index Scan using test2_info_nulls_low on s_pis (cost=0.43..28693.97 rows=1 width=8) (actual time=219.644..219.644 rows=0 loops=2) Index Cond: (n1 < 9000000) Filter: (n IS NOT NULL) Rows Removed by Filter: 500000 Planning time: 0.271 ms Execution time: 223.631 ms (12 rows) -- 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 and 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; QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------------- Finalize Aggregate (cost=14879.77..14879.78 rows=1 width=8) (actual time=178.205..178.205 rows=1 loops=1) Output: count(1) -> Gather (cost=14879.56..14879.77 rows=2 width=8) (actual time=178.134..178.199 rows=3 loops=1) Output: (PARTIAL count(1)) Workers Planned: 2 Workers Launched: 2 -> Partial Aggregate (cost=13879.56..13879.57 rows=1 width=8) (actual time=173.866..173.867 rows=1 loops=3) Output: PARTIAL count(1) Worker 0: actual time=172.112..172.113 rows=1 loops=1 Worker 1: actual time=171.635..171.635 rows=1 loops=1 -> Parallel Seq Scan on public.tst_pis (cost=0.00..13532.33 rows=138889 width=0) (actual time=8.747..112.049 rows=300000 loops=3) Filter: (tst_pis.c1 > 100000) Rows Removed by Filter: 33333 Worker 0: actual time=7.030..110.527 rows=275160 loops=1 Worker 1: actual time=6.519..110.733 rows=251040 loops=1 Planning time: 0.435 ms Execution time: 179.754 ms (17 rows) set enable_seqscan =0; set enable_bitmapscan = 0 ; set enable_indexonlyscan =0; explain analyze verbose select count(1) from tst_pis where c1 > 100000; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------- Finalize Aggregate (cost=42907.51..42907.52 rows=1 width=8) (actual time=275.760..275.760 rows=1 loops=1) Output: count(1) -> Gather (cost=42907.40..42907.51 rows=1 width=8) (actual time=275.744..275.753 rows=2 loops=1) Output: (PARTIAL count(1)) Workers Planned: 1 Workers Launched: 1 -> Partial Aggregate (cost=41907.40..41907.41 rows=1 width=8) (actual time=274.022..274.022 rows=1 loops=2) Output: PARTIAL count(1) Worker 0: actual time=272.499..272.499 rows=1 loops=1 -> Parallel Index Scan using tst_idx on public.tst_pis (cost=0.42..41417.20 rows=196078 width=0) (actual time=0.048..196.769 rows=450000 loops=2) Index Cond: (tst_pis.c1 > 100000) Worker 0: actual time=0.042..195.618 rows=450268 loops=1 Planning time: 0.107 ms Execution time: 277.560 ms (14 rows) -- 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 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%'; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------ Gather (cost=1000.00..15607.30 rows=333 width=82) (actual time=0.255..158.127 rows=11111 loops=1) Output: c1, c2, c3, c4 Workers Planned: 2 Workers Launched: 2 -> Parallel Seq Scan on public.tst_pis (cost=0.00..14574.00 rows=139 width=82) (actual time=10.083..148.473 rows=3704 loops=3) Output: c1, c2, c3, c4 Filter: ((tst_pis.c1 >= 100) AND (tst_pis.c2 ~~ 'c2_10%'::text)) Rows Removed by Filter: 329630 Worker 0: actual time=14.925..147.273 rows=4184 loops=1 Worker 1: actual time=15.287..147.589 rows=4080 loops=1 Planning time: 0.538 ms Execution time: 160.350 ms (12 rows) set enable_seqscan =0; set enable_bitmapscan = 0 ; explain analyze verbose select * from tst_pis where c1>=100 and c2 like 'c2_10%'; QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------------- Gather (cost=1000.42..42940.70 rows=333 width=82) (actual time=0.195..247.299 rows=11111 loops=1) Output: c1, c2, c3, c4 Workers Planned: 1 Workers Launched: 1 -> Parallel Index Scan using tst_idx on public.tst_pis (cost=0.42..41907.40 rows=196 width=82) (actual time=0.543..241.698 rows=5556 loops=2) Output: c1, c2, c3, c4 Index Cond: (tst_pis.c1 >= 100) Filter: (tst_pis.c2 ~~ 'c2_10%'::text) Rows Removed by Filter: 494395 Worker 0: actual time=1.049..241.477 rows=7340 loops=1 Planning time: 0.109 ms Execution time: 249.888 ms (12 rows) -- 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; QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------------- Finalize HashAggregate (cost=42592.12..42717.46 rows=12534 width=118) (actual time=183476.023..183801.822 rows=999786 loops=1) Output: count(1), c1, c2 Group Key: t1_pis.c1, t1_pis.c2 -> Gather (cost=41185.88..42498.38 rows=12500 width=118) (actual time=740.757..1668.206 rows=999930 loops=1) Output: c1, c2, (PARTIAL count(1)) Workers Planned: 2 Workers Launched: 2 -> Partial HashAggregate (cost=40185.88..40248.38 rows=6250 width=118) (actual time=727.874..1056.550 rows=333310 loops=3) Output: c1, c2, PARTIAL count(1) Group Key: t1_pis.c1, t1_pis.c2 Worker 0: actual time=717.235..1072.461 rows=324811 loops=1 Worker 1: actual time=726.061..1209.518 rows=332633 loops=1 -> Parallel Seq Scan on public.t1_pis (cost=0.00..40139.00 rows=6250 width=110) (actual time=92.900..414.242 rows=333333 loops=3) Output: c1, c2 Filter: (t1_pis.c1 = (t1_pis.c2)::text) Rows Removed by Filter: 666667 Worker 0: actual time=82.276..404.658 rows=324833 loops=1 Worker 1: actual time=91.097..410.806 rows=332657 loops=1 Planning time: 0.888 ms Execution time: 183960.661 ms (20 rows) set enable_seqscan =0; explain analyze verbose select count(1) from t1_pis group by c1, c2 having c1 = c2; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------- GroupAggregate (cost=0.43..112262.93 rows=15000 width=30) (actual time=0.100..1689.870 rows=999786 loops=1) Output: count(1), c1, c2 Group Key: t1_pis.c1, t1_pis.c2 -> Index Only Scan using t1_idx12 on public.t1_pis (cost=0.43..112000.43 rows=15000 width=22) (actual time=0.085..978.992 rows=1000000 loops=1) Output: c1, c2 Filter: (t1_pis.c1 = (t1_pis.c2)::text) Rows Removed by Filter: 2000000 Heap Fetches: 0 Planning time: 0.736 ms Execution time: 1795.762 ms (10 rows) set enable_indexonlyscan=0; explain analyze verbose select count(1) from t1_pis group by c1, c2 having c1 = c2; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Finalize GroupAggregate (cost=178089.06..178364.06 rows=15000 width=30) (actual time=6924.634..7921.855 rows=999786 loops=1) Output: count(1), c1, c2 Group Key: t1_pis.c1, t1_pis.c2 -> Sort (cost=178089.06..178120.31 rows=12500 width=30) (actual time=6924.626..7289.229 rows=999786 loops=1) Output: c1, c2, (PARTIAL count(1)) Sort Key: t1_pis.c1 Sort Method: external merge Disk: 41048kB -> Gather (cost=1000.43..177238.46 rows=12500 width=30) (actual time=0.269..660.141 rows=999786 loops=1) Output: c1, c2, (PARTIAL count(1)) Workers Planned: 2 Workers Launched: 2 -> Partial GroupAggregate (cost=0.43..174988.46 rows=6250 width=30) (actual time=0.108..2580.914 rows=333262 loops=3) Output: c1, c2, PARTIAL count(1) Group Key: t1_pis.c1, t1_pis.c2 Worker 0: actual time=0.102..3462.352 rows=445328 loops=1 Worker 1: actual time=0.161..4130.996 rows=542351 loops=1 -> Parallel Index Scan using t1_idx2 on public.t1_pis (cost=0.43..174879.09 rows=6250 width=22) (actual time=0.076..2258.219 rows=333333 loops=3) Output: c1, c2 Filter: (t1_pis.c1 = (t1_pis.c2)::text) Rows Removed by Filter: 666667 Worker 0: actual time=0.057..3026.639 rows=445410 loops=1 Worker 1: actual time=0.121..3612.149 rows=542478 loops=1 Planning time: 0.145 ms Execution time: 8027.773 ms (24 rows) -- 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; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------- Aggregate (cost=8.45..8.46 rows=1 width=8) (actual time=753.399..753.399 rows=1 loops=1) Output: count(*) -> Index Only Scan using t2_idx on public.t2_pis (cost=0.43..8.45 rows=1 width=0) (actual time=0.172..614.586 rows=1000000 loops=1) Output: c3, c4, c5 Index Cond: ((t2_pis.c3 = to_date('25-09-2015'::text, 'dd-mm-yyyy'::text)) AND (t2_pis.c4 = 'xyz'::text) AND (t2_pis.c5 = '1.1'::double precision)) Heap Fetches: 1000000 Planning time: 19.052 ms Execution time: 753.498 ms (8 rows) 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; QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Finalize Aggregate (cost=51126.48..51126.49 rows=1 width=8) (actual time=289.908..289.909 rows=1 loops=1) Output: count(*) -> Gather (cost=51126.26..51126.47 rows=2 width=8) (actual time=289.642..289.900 rows=3 loops=1) Output: (PARTIAL count(*)) Workers Planned: 2 Workers Launched: 2 -> Partial Aggregate (cost=50126.26..50126.27 rows=1 width=8) (actual time=283.471..283.472 rows=1 loops=3) Output: PARTIAL count(*) Worker 0: actual time=282.284..282.284 rows=1 loops=1 Worker 1: actual time=279.039..279.040 rows=1 loops=1 -> Parallel Index Scan using t2_idx on public.t2_pis (cost=0.43..49084.59 rows=416667 width=0) (actual time=0.199..229.147 rows=333333 loops=3) Index Cond: ((t2_pis.c3 = to_date('25-09-2015'::text, 'dd-mm-yyyy'::text)) AND ((t2_pis.c4)::text = 'xyz'::text) AND (t2_pis.c5 = '1.1'::double precision)) Worker 0: actual time=0.195..228.740 rows=333819 loops=1 Worker 1: actual time=0.228..227.670 rows=323901 loops=1 Planning time: 0.373 ms Execution time: 292.374 ms (16 rows) -- 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'; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------------------- Finalize Aggregate (cost=48626.48..48626.49 rows=1 width=8) (actual time=305.137..305.137 rows=1 loops=1) Output: count(*) -> Gather (cost=48626.26..48626.47 rows=2 width=8) (actual time=304.944..305.127 rows=3 loops=1) Output: (PARTIAL count(*)) Workers Planned: 2 Workers Launched: 2 -> Partial Aggregate (cost=47626.26..47626.27 rows=1 width=8) (actual time=299.246..299.247 rows=1 loops=3) Output: PARTIAL count(*) Worker 0: actual time=296.722..296.723 rows=1 loops=1 Worker 1: actual time=296.602..296.602 rows=1 loops=1 -> Parallel Index Scan using t2_idx on public.t2_pis (cost=0.43..46584.59 rows=416667 width=0) (actual time=0.149..236.767 rows=333333 loops=3) Index Cond: ((t2_pis.c3 = to_date('25-09-2015'::text, 'dd-mm-yyyy'::text)) AND ((t2_pis.c4)::text = 'xyz'::text)) Worker 0: actual time=0.163..235.011 rows=334080 loops=1 Worker 1: actual time=0.150..236.208 rows=291124 loops=1 Planning time: 0.409 ms Execution time: 307.486 ms (16 rows) -- 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); ins_comptab ------------- 1 (1 row) CREATE INDEX comp_tab_idx on comp_tab (c2); explain analyze verbose select avg((c1).cc1_c2) from comp_tab group by c2; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------- Finalize GroupAggregate (cost=82309.29..82314.79 rows=200 width=36) (actual time=1448.356..1448.361 rows=2 loops=1) Output: avg((c1).cc1_c2), c2 Group Key: comp_tab.c2 -> Sort (cost=82309.29..82310.29 rows=400 width=36) (actual time=1448.336..1448.337 rows=6 loops=1) Output: c2, (PARTIAL avg((c1).cc1_c2)) Sort Key: comp_tab.c2 Sort Method: quicksort Memory: 25kB -> Gather (cost=82250.00..82292.00 rows=400 width=36) (actual time=1448.162..1448.308 rows=6 loops=1) Output: c2, (PARTIAL avg((c1).cc1_c2)) Workers Planned: 2 Workers Launched: 2 -> Partial HashAggregate (cost=81250.00..81252.00 rows=200 width=36) (actual time=1442.306..1442.309 rows=2 loops=3) Output: c2, PARTIAL avg((c1).cc1_c2) Group Key: comp_tab.c2 Worker 0: actual time=1440.493..1440.495 rows=2 loops=1 Worker 1: actual time=1438.561..1438.563 rows=2 loops=1 -> Parallel Seq Scan on public.comp_tab (cost=0.00..70833.33 rows=2083333 width=36) (actual time=0.050..472.586 rows=1666667 loops=3) Output: c2, c1 Worker 0: actual time=0.028..471.856 rows=1720600 loops=1 Worker 1: actual time=0.079..472.921 rows=1671800 loops=1 Planning time: 31.126 ms Execution time: 1451.096 ms (22 rows) set enable_seqscan =0; explain analyze verbose select avg((c1).cc1_c2) from comp_tab group by c2; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Finalize GroupAggregate (cost=312161.72..312167.22 rows=200 width=36) (actual time=1864.588..1864.592 rows=2 loops=1) Output: avg((c1).cc1_c2), c2 Group Key: comp_tab.c2 -> Sort (cost=312161.72..312162.72 rows=400 width=36) (actual time=1864.576..1864.579 rows=6 loops=1) Output: c2, (PARTIAL avg((c1).cc1_c2)) Sort Key: comp_tab.c2 Sort Method: quicksort Memory: 25kB -> Gather (cost=1000.43..312144.43 rows=400 width=36) (actual time=970.109..1864.550 rows=6 loops=1) Output: c2, (PARTIAL avg((c1).cc1_c2)) Workers Planned: 2 Workers Launched: 2 -> Partial GroupAggregate (cost=0.43..311104.43 rows=200 width=36) (actual time=965.722..1859.929 rows=2 loops=3) Output: c2, PARTIAL avg((c1).cc1_c2) Group Key: comp_tab.c2 Worker 0: actual time=961.517..1855.599 rows=2 loops=1 Worker 1: actual time=965.950..1860.067 rows=2 loops=1 -> Parallel Index Scan using comp_tab_idx on public.comp_tab (cost=0.43..300685.77 rows=2083333 width=36) (actual time=0.948..1051.530 rows=1666667 loops=3) Output: c2, c1 Worker 0: actual time=0.101..1055.421 rows=1651758 loops=1 Worker 1: actual time=0.053..1062.346 rows=1732718 loops=1 Planning time: 0.188 ms Execution time: 1868.919 ms (22 rows) -- 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; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------ Gather (cost=1000.00..32609.10 rows=1 width=21) (actual time=193.054..193.054 rows=0 loops=1) Output: c1, c2 Workers Planned: 2 Workers Launched: 2 -> Parallel Seq Scan on public.mview_tab11 (cost=0.00..31609.00 rows=1 width=21) (actual time=188.217..188.217 rows=0 loops=3) Output: c1, c2 Filter: ((mview_tab11.c1 IS NULL) AND (mview_tab11.c2 IS NOT NULL)) Rows Removed by Filter: 1000000 Worker 0: actual time=186.054..186.054 rows=0 loops=1 Worker 1: actual time=186.021..186.021 rows=0 loops=1 Planning time: 0.139 ms Execution time: 193.937 ms (12 rows) set enable_seqscan =0; set enable_bitmapscan =0; explain analyze verbose select * from mview_tab11 where c1 is null and c2 is not null; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------- Gather (cost=1000.43..171484.18 rows=1 width=21) (actual time=2140.623..2140.623 rows=0 loops=1) Output: c1, c2 Workers Planned: 2 Workers Launched: 2 -> Parallel Index Scan using mvw_idx2 on public.mview_tab11 (cost=0.43..170484.08 rows=1 width=21) (actual time=2138.036..2138.036 rows=0 loops=3) Output: c1, c2 Index Cond: (mview_tab11.c2 IS NOT NULL) Filter: (mview_tab11.c1 IS NULL) Rows Removed by Filter: 1000000 Worker 0: actual time=2136.719..2136.719 rows=0 loops=1 Worker 1: actual time=2136.982..2136.982 rows=0 loops=1 Planning time: 0.075 ms Execution time: 2146.133 ms (13 rows) -- 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; QUERY PLAN --------------------------------------------------------------------------------------------------------------------- Seq Scan on public.tab11 (cost=0.00..44906.15 rows=570404 width=62) (actual time=0.073..1217.751 rows=199 loops=1) Output: tab11.c1, tab11.c2 Filter: ((tab11.c2 IS NOT NULL) AND (tab11.c1 < 2500000) AND (tab11.c1 < 200)) Rows Removed by Filter: 2999801 Planning time: 0.494 ms Execution time: 1217.854 ms (6 rows) set enable_seqscan =0; set enable_bitmapscan =0; explain analyze verbose select * from vw_tab11 where c1 < 200 and c2 is not null; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------- Gather (cost=1000.43..195268.07 rows=300 width=21) (actual time=4.129..2146.732 rows=199 loops=1) Output: tab11.c1, tab11.c2 Workers Planned: 2 Workers Launched: 2 -> Parallel Index Scan using tab11_pkey on public.tab11 (cost=0.43..194238.07 rows=125 width=21) (actual time=54.680..2143.671 rows=66 loops=3) Output: tab11.c1, tab11.c2 Index Cond: (tab11.c2 IS NOT NULL) Filter: ((tab11.c1 < 2500000) AND (tab11.c1 < 200)) Rows Removed by Filter: 999934 Worker 0: actual time=122.145..2142.538 rows=64 loops=1 Worker 1: actual time=37.929..2142.906 rows=68 loops=1 Planning time: 0.102 ms Execution time: 2152.649 ms (13 rows) -- 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); analyze; INSERT INTO tab11(select x, substr(md5(random()::text),1,10)||x from generate_series(1,3000000) x); 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; QUERY PLAN --------------------------------------------------------------------------------------------------------------------------- Sort (cost=120889.41..122315.42 rows=570404 width=62) (actual time=1113.050..1113.102 rows=199 loops=1) Output: tab11.c1, tab11.c2 Sort Key: tab11.c2 DESC Sort Method: quicksort Memory: 41kB -> Seq Scan on public.tab11 (cost=0.00..44906.15 rows=570404 width=62) (actual time=0.042..1112.779 rows=199 loops=1) Output: tab11.c1, tab11.c2 Filter: ((tab11.c2 IS NOT NULL) AND (tab11.c1 < 2500000) AND (tab11.c1 < 200)) Rows Removed by Filter: 2999801 Planning time: 0.410 ms Execution time: 1113.162 ms (10 rows) 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; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------------------- Sort (cost=195740.65..195741.40 rows=300 width=21) (actual time=2304.441..2304.476 rows=199 loops=1) Output: tab11.c1, tab11.c2 Sort Key: tab11.c2 DESC Sort Method: quicksort Memory: 41kB -> Gather (cost=1000.43..195728.31 rows=300 width=21) (actual time=33.904..2303.809 rows=199 loops=1) Output: tab11.c1, tab11.c2 Workers Planned: 2 Workers Launched: 2 -> Parallel Index Scan Backward using tab11_idx2 on public.tab11 (cost=0.43..194698.31 rows=125 width=21) (actual time=40.901..2299.041 rows=66 loops=3) Output: tab11.c1, tab11.c2 Index Cond: (tab11.c2 IS NOT NULL) Filter: ((tab11.c1 < 2500000) AND (tab11.c1 < 200)) Rows Removed by Filter: 999934 Worker 0: actual time=70.692..2297.126 rows=68 loops=1 Worker 1: actual time=18.326..2297.164 rows=69 loops=1 Planning time: 0.186 ms Execution time: 2308.166 ms (17 rows) -- 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; QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------- Sort (cost=29979.79..29980.41 rows=250 width=21) (actual time=224.700..224.764 rows=199 loops=1) Output: c1, c2 Sort Key: mview_tab11.c2 DESC Sort Method: quicksort Memory: 41kB -> Gather (cost=1000.00..29969.83 rows=250 width=21) (actual time=0.254..224.367 rows=199 loops=1) Output: c1, c2 Workers Planned: 2 Workers Launched: 2 -> Parallel Seq Scan on public.mview_tab11 (cost=0.00..28944.83 rows=104 width=21) (actual time=144.916..219.551 rows=66 loops=3) Output: c1, c2 Filter: ((mview_tab11.c2 IS NOT NULL) AND (mview_tab11.c1 < 200)) Rows Removed by Filter: 833267 Worker 0: actual time=217.365..217.365 rows=0 loops=1 Worker 1: actual time=217.355..217.355 rows=0 loops=1 Planning time: 0.240 ms Execution time: 225.429 ms (16 rows) 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; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Sort (cost=145638.12..145638.75 rows=250 width=21) (actual time=1100.465..1100.494 rows=199 loops=1) Output: c1, c2 Sort Key: mview_tab11.c2 DESC Sort Method: quicksort Memory: 41kB -> Gather (cost=1000.43..145628.16 rows=250 width=21) (actual time=30.604..1099.990 rows=199 loops=1) Output: c1, c2 Workers Planned: 2 Workers Launched: 2 -> Parallel Index Scan Backward using mvw_idx2 on public.mview_tab11 (cost=0.43..144603.16 rows=104 width=21) (actual time=34.703..1097.360 rows=66 loops=3) Output: c1, c2 Index Cond: (mview_tab11.c2 IS NOT NULL) Filter: (mview_tab11.c1 < 200) Rows Removed by Filter: 833267 Worker 0: actual time=64.708..1096.275 rows=77 loops=1 Worker 1: actual time=8.945..1096.366 rows=61 loops=1 Planning time: 0.103 ms Execution time: 1103.926 ms (17 rows) -- Object Cleanup: DROP MATERIALIZED VIEW mview_tab11; DROP TABLE tab11; -- TestCase: 15 --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; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ CTE Scan on t1 (cost=2029763.40..2029849.66 rows=4313 width=352) (actual time=428163.511..428259.232 rows=100000 loops=1) CTE t1 -> Sort (cost=2029752.62..2029763.40 rows=4313 width=97) (actual time=428163.505..428197.640 rows=100000 loops=1) Sort Key: pgbench_accounts.aid DESC Sort Method: external sort Disk: 10472kB -> Gather (cost=1000.57..2029492.23 rows=4313 width=97) (actual time=427351.104..427940.046 rows=100000 loops=1) Workers Planned: 2 Workers Launched: 2 -> Parallel Index Scan Backward using pgbench_accounts_pkey on pgbench_accounts (cost=0.57..2028060.93 rows=1797 width=97) (actual time=427343.881..428007.997 rows=33333 loops=3) Index Cond: (aid < 50000000) Filter: (bid <= 1) Rows Removed by Filter: 16633333 Planning time: 25.882 ms Execution time: 428274.081 ms (14 rows) -- TestCase: 16 --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; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------- Gather (cost=1000.00..22370.80 rows=208 width=8) (actual time=0.271..339.193 rows=498 loops=1) Output: n, n1 Workers Planned: 2 Workers Launched: 2 -> Parallel Seq Scan on public.tabl2 (cost=0.00..21350.00 rows=87 width=8) (actual time=77.525..190.389 rows=166 loops=3) Output: n, n1 Filter: ((tabl2.n < 1000000) AND (tabl2.n1 < 500)) Rows Removed by Filter: 666501 Worker 0: actual time=116.265..116.265 rows=0 loops=1 Worker 1: actual time=116.290..116.290 rows=0 loops=1 Planning time: 0.251 ms Execution time: 341.049 ms (12 rows) set enable_seqscan =0; explain analyze verbose select * from tabl2 where n< 1000000 and n1 <500; QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------------- Gather (cost=1000.43..29375.25 rows=208 width=8) (actual time=0.216..159.058 rows=498 loops=1) Output: n, n1 Workers Planned: 2 Workers Launched: 2 -> Parallel Index Scan using tabl2_pkey on public.tabl2 (cost=0.43..28354.45 rows=87 width=8) (actual time=102.442..155.238 rows=166 loops=3) Output: n, n1 Index Cond: (tabl2.n < 1000000) Filter: (tabl2.n1 < 500) Rows Removed by Filter: 333167 Worker 0: actual time=152.360..152.360 rows=0 loops=1 Worker 1: actual time=154.934..154.934 rows=0 loops=1 Planning time: 0.093 ms Execution time: 162.142 ms (13 rows) -- 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; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------- Index Scan using tabl1_pkey on pg_temp_2.tabl1 (cost=0.43..35526.37 rows=229 width=8) (actual time=0.049..300.535 rows=498 loops=1) Output: n, n1 Index Cond: (tabl1.n < 1000000) Filter: (tabl1.n1 < 500) Rows Removed by Filter: 999501 Planning time: 0.182 ms Execution time: 300.593 ms (7 rows) -- Object Cleanup: DROP TABLE Tabl2; -- TestCase: 17 --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; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------- Nested Loop (cost=0.86..134632.91 rows=5000000 width=12) (actual time=0.055..2293.492 rows=5000000 loops=1) Output: t.n, t1.n, t2.n -> Nested Loop (cost=0.86..8.91 rows=1 width=8) (actual time=0.037..0.043 rows=1 loops=1) Output: t.n, t2.n -> Index Only Scan using cccc on public.t (cost=0.43..4.45 rows=1 width=4) (actual time=0.026..0.027 rows=1 loops=1) Output: t.n Index Cond: (t.n = 1) Heap Fetches: 0 -> Index Only Scan using cccc on public.t t2 (cost=0.43..4.45 rows=1 width=4) (actual time=0.008..0.013 rows=1 loops=1) Output: t2.n Index Cond: (t2.n = 1) Heap Fetches: 0 -> Seq Scan on public.t t1 (cost=0.00..84624.00 rows=5000000 width=4) (actual time=0.015..1232.917 rows=5000000 loops=1) Output: t1.n Filter: (t1.n >= 1) Planning time: 0.867 ms Execution time: 2671.307 ms (17 rows) 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; QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------------------------- Nested Loop (cost=1.30..185329.68 rows=5000000 width=12) (actual time=0.366..3826.556 rows=5000000 loops=1) Output: t.n, t1.n, t2.n -> Nested Loop (cost=0.86..16.91 rows=1 width=8) (actual time=0.017..0.020 rows=1 loops=1) Output: t.n, t2.n -> Index Scan using cccc on public.t (cost=0.43..8.45 rows=1 width=4) (actual time=0.009..0.010 rows=1 loops=1) Output: t.n Index Cond: (t.n = 1) -> Index Scan using cccc on public.t t2 (cost=0.43..8.45 rows=1 width=4) (actual time=0.005..0.007 rows=1 loops=1) Output: t2.n Index Cond: (t2.n = 1) -> Gather (cost=0.43..135312.77 rows=5000000 width=4) (actual time=0.349..2684.653 rows=5000000 loops=1) Output: t1.n Workers Planned: 2 Workers Launched: 2 -> Parallel Index Scan using cccc on public.t t1 (cost=0.43..135312.77 rows=2083333 width=4) (actual time=0.065..978.331 rows=1666667 loops=3) Output: t1.n Index Cond: (t1.n >= 1) Worker 0: actual time=0.072..1472.121 rows=2515152 loops=1 Worker 1: actual time=0.102..1457.794 rows=2476064 loops=1 Planning time: 0.268 ms Execution time: 4231.119 ms (21 rows) -- Object Cleanup: DROP TABLE t;