--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 BEGIN; CREATE TABLE PART ( P_PARTKEY SERIAL, P_NAME VARCHAR(55), P_MFGR CHAR(25), P_BRAND CHAR(10), P_TYPE VARCHAR(25), P_SIZE INTEGER, P_CONTAINER CHAR(10), P_RETAILPRICE DECIMAL, P_COMMENT VARCHAR(23) ); COPY part FROM '/home/centos/pg_tpch/dss/data/part.csv' WITH (FORMAT csv, DELIMITER '|'); COMMIT; BEGIN; CREATE TABLE REGION ( R_REGIONKEY SERIAL, R_NAME CHAR(25), R_COMMENT VARCHAR(152) ); COPY region FROM '/home/centos/pg_tpch/dss/data/region.csv' WITH (FORMAT csv, DELIMITER '|'); COMMIT; BEGIN; CREATE TABLE NATION ( N_NATIONKEY SERIAL, N_NAME CHAR(25), N_REGIONKEY BIGINT NOT NULL, -- references R_REGIONKEY N_COMMENT VARCHAR(152) ); COPY nation FROM '/home/centos/pg_tpch/dss/data/nation.csv' WITH (FORMAT csv, DELIMITER '|'); COMMIT; BEGIN; CREATE TABLE SUPPLIER ( S_SUPPKEY SERIAL, S_NAME CHAR(25), S_ADDRESS VARCHAR(40), S_NATIONKEY BIGINT NOT NULL, -- references N_NATIONKEY S_PHONE CHAR(15), S_ACCTBAL DECIMAL, S_COMMENT VARCHAR(101) ); COPY supplier FROM '/home/centos/pg_tpch/dss/data/supplier.csv' WITH (FORMAT csv, DELIMITER '|'); COMMIT; BEGIN; CREATE TABLE CUSTOMER ( C_CUSTKEY SERIAL, C_NAME VARCHAR(25), C_ADDRESS VARCHAR(40), C_NATIONKEY BIGINT NOT NULL, -- references N_NATIONKEY C_PHONE CHAR(15), C_ACCTBAL DECIMAL, C_MKTSEGMENT CHAR(10), C_COMMENT VARCHAR(117) ); COPY customer FROM '/home/centos/pg_tpch/dss/data/customer.csv' WITH (FORMAT csv, DELIMITER '|'); COMMIT; BEGIN; CREATE TABLE PARTSUPP ( PS_PARTKEY BIGINT NOT NULL, -- references P_PARTKEY PS_SUPPKEY BIGINT NOT NULL, -- references S_SUPPKEY PS_AVAILQTY INTEGER, PS_SUPPLYCOST DECIMAL, PS_COMMENT VARCHAR(199) ); COPY partsupp FROM '/home/centos/pg_tpch/dss/data/partsupp.csv' WITH (FORMAT csv, DELIMITER '|'); COMMIT; BEGIN; CREATE TABLE ORDERS ( O_ORDERKEY SERIAL, O_CUSTKEY BIGINT NOT NULL, -- references C_CUSTKEY O_ORDERSTATUS CHAR(1), O_TOTALPRICE DECIMAL, O_ORDERDATE DATE, O_ORDERPRIORITY CHAR(15), O_CLERK CHAR(15), O_SHIPPRIORITY INTEGER, O_COMMENT VARCHAR(79) ); COPY orders FROM '/home/centos/pg_tpch/dss/data/orders.csv' WITH (FORMAT csv, DELIMITER '|'); COMMIT; BEGIN; CREATE TABLE LINEITEM ( L_ORDERKEY BIGINT NOT NULL, -- references O_ORDERKEY L_PARTKEY BIGINT NOT NULL, -- references P_PARTKEY (compound fk to PARTSUPP) L_SUPPKEY BIGINT NOT NULL, -- references S_SUPPKEY (compound fk to PARTSUPP) L_LINENUMBER INTEGER, L_QUANTITY DECIMAL, L_EXTENDEDPRICE DECIMAL, L_DISCOUNT DECIMAL, L_TAX DECIMAL, L_RETURNFLAG CHAR(1), L_LINESTATUS CHAR(1), L_SHIPDATE DATE, L_COMMITDATE DATE, L_RECEIPTDATE DATE, L_SHIPINSTRUCT CHAR(25), L_SHIPMODE CHAR(10), L_COMMENT VARCHAR(44) ); COPY lineitem FROM '/home/centos/pg_tpch/dss/data/lineitem.csv' WITH (FORMAT csv, DELIMITER '|'); COMMIT; \i /home/centos/pg_tpch/dss/tpch-pkeys.sql ALTER TABLE PART ADD PRIMARY KEY (P_PARTKEY); ALTER TABLE SUPPLIER ADD PRIMARY KEY (S_SUPPKEY); ALTER TABLE PARTSUPP ADD PRIMARY KEY (PS_PARTKEY, PS_SUPPKEY); ALTER TABLE CUSTOMER ADD PRIMARY KEY (C_CUSTKEY); ALTER TABLE ORDERS ADD PRIMARY KEY (O_ORDERKEY); ALTER TABLE LINEITEM ADD PRIMARY KEY (L_ORDERKEY, L_LINENUMBER); ALTER TABLE NATION ADD PRIMARY KEY (N_NATIONKEY); ALTER TABLE REGION ADD PRIMARY KEY (R_REGIONKEY); \i /home/centos/pg_tpch/dss/tpch-alter.sql -- foreign keys ALTER TABLE SUPPLIER ADD FOREIGN KEY (S_NATIONKEY) REFERENCES NATION(N_NATIONKEY); ALTER TABLE PARTSUPP ADD FOREIGN KEY (PS_PARTKEY) REFERENCES PART(P_PARTKEY); ALTER TABLE PARTSUPP ADD FOREIGN KEY (PS_SUPPKEY) REFERENCES SUPPLIER(S_SUPPKEY); ALTER TABLE CUSTOMER ADD FOREIGN KEY (C_NATIONKEY) REFERENCES NATION(N_NATIONKEY); ALTER TABLE ORDERS ADD FOREIGN KEY (O_CUSTKEY) REFERENCES CUSTOMER(C_CUSTKEY); ALTER TABLE LINEITEM ADD FOREIGN KEY (L_ORDERKEY) REFERENCES ORDERS(O_ORDERKEY); ALTER TABLE LINEITEM ADD FOREIGN KEY (L_PARTKEY,L_SUPPKEY) REFERENCES PARTSUPP(PS_PARTKEY,PS_SUPPKEY); ALTER TABLE NATION ADD FOREIGN KEY (N_REGIONKEY) REFERENCES REGION(R_REGIONKEY); \i /home/centos/pg_tpch/dss/tpch-index.sql -- indexes on the foreign keys CREATE INDEX IDX_SUPPLIER_NATION_KEY ON SUPPLIER (S_NATIONKEY); CREATE INDEX IDX_PARTSUPP_PARTKEY ON PARTSUPP (PS_PARTKEY); CREATE INDEX IDX_PARTSUPP_SUPPKEY ON PARTSUPP (PS_SUPPKEY); CREATE INDEX IDX_CUSTOMER_NATIONKEY ON CUSTOMER (C_NATIONKEY); CREATE INDEX IDX_ORDERS_CUSTKEY ON ORDERS (O_CUSTKEY); CREATE INDEX IDX_LINEITEM_ORDERKEY ON LINEITEM (L_ORDERKEY); CREATE INDEX IDX_LINEITEM_PART_SUPP ON LINEITEM (L_PARTKEY,L_SUPPKEY); CREATE INDEX IDX_NATION_REGIONKEY ON NATION (N_REGIONKEY); -- aditional indexes CREATE INDEX IDX_LINEITEM_SHIPDATE ON LINEITEM (L_SHIPDATE, L_DISCOUNT, L_QUANTITY); CREATE INDEX IDX_ORDERS_ORDERDATE ON ORDERS (O_ORDERDATE); 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); QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Finalize Aggregate (cost=1592662.94..1592662.95 rows=1 width=32) (actual time=35336.274..35336.274 rows=1 loops=1) Output: sum(l_extendedprice) -> Gather (cost=1592662.72..1592662.93 rows=2 width=32) (actual time=35334.011..35336.167 rows=3 loops=1) Output: (PARTIAL sum(l_extendedprice)) Workers Planned: 2 Workers Launched: 2 -> Partial Aggregate (cost=1591662.72..1591662.73 rows=1 width=32) (actual time=35209.934..35209.935 rows=1 loops=3) Output: PARTIAL sum(l_extendedprice) Worker 0: actual time=35159.271..35159.272 rows=1 loops=1 Worker 1: actual time=35160.820..35160.821 rows=1 loops=1 -> Parallel Bitmap Heap Scan on public.lineitem (cost=293308.66..1579752.14 rows=4764232 width=8) (actual time=3002.771..32699.591 rows=3790784 loops=3) Output: l_extendedprice Recheck Cond: ((lineitem.l_shipdate >= '01-01-1995'::date) AND (lineitem.l_shipdate <= '03-31-1996'::date)) Rows Removed by Index Recheck: 14734298 Heap Blocks: exact=12127 lossy=369423 Worker 0: actual time=2955.667..32698.902 rows=3747524 loops=1 Worker 1: actual time=2951.397..32588.520 rows=3879647 loops=1 -> Bitmap Index Scan on idx_lineitem_shipdate (cost=0.00..290450.12 rows=11434156 width=0) (actual time=3068.753..3068.753 rows=11372351 loops=1) Index Cond: ((lineitem.l_shipdate >= '01-01-1995'::date) AND (lineitem.l_shipdate <= '03-31-1996'::date)) Planning time: 60.463 ms Execution time: 35393.124 ms (21 rows) Time: 35573.388 ms (00:35.573) --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; QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=513523.80..513523.82 rows=1 width=24) (actual time=27681.228..27681.228 rows=1 loops=1) Output: orders.o_orderpriority, (count(*)) -> Finalize GroupAggregate (cost=513523.80..513523.92 rows=5 width=24) (actual time=27681.223..27681.223 rows=1 loops=1) Output: orders.o_orderpriority, count(*) Group Key: orders.o_orderpriority -> Sort (cost=513523.80..513523.82 rows=10 width=24) (actual time=27681.215..27681.216 rows=4 loops=1) Output: orders.o_orderpriority, (PARTIAL count(*)) Sort Key: orders.o_orderpriority Sort Method: quicksort Memory: 26kB -> Gather (cost=513411.24..513523.63 rows=10 width=24) (actual time=27534.845..27681.131 rows=15 loops=1) Output: orders.o_orderpriority, (PARTIAL count(*)) Workers Planned: 2 Workers Launched: 2 -> Partial GroupAggregate (cost=512411.24..512522.63 rows=5 width=24) (actual time=27428.513..27573.727 rows=5 loops=3) Output: orders.o_orderpriority, PARTIAL count(*) Group Key: orders.o_orderpriority Worker 0: actual time=27381.454..27527.404 rows=5 loops=1 Worker 1: actual time=27379.019..27522.491 rows=5 loops=1 -> Sort (cost=512411.24..512448.35 rows=14846 width=16) (actual time=27375.819..27485.218 rows=175469 loops=3) Output: orders.o_orderpriority Sort Key: orders.o_orderpriority Sort Method: external merge Disk: 4424kB Worker 0: actual time=27326.974..27438.762 rows=176736 loops=1 Worker 1: actual time=27330.073..27435.964 rows=176180 loops=1 -> Nested Loop Semi Join (cost=12304.30..511382.57 rows=14846 width=16) (actual time=222.170..26664.017 rows=175469 loops=3) Output: orders.o_orderpriority Worker 0: actual time=181.352..26606.488 rows=176736 loops=1 Worker 1: actual time=183.255..26625.617 rows=176180 loops=1 -> Parallel Bitmap Heap Scan on public.orders (cost=12303.73..292149.70 rows=241598 width=20) (actual time=221.957..4781.544 rows=191407 loops=3) Output: orders.o_orderkey, orders.o_custkey, orders.o_orderstatus, orders.o_totalprice, orders.o_orderdate, orders.o_orderpriority, orders.o_clerk, orders.o_shippriority, orders.o_comment Recheck Cond: ((orders.o_orderdate >= '11-01-1996'::date) AND (orders.o_orderdate < 'Sat Feb 01 00:00:00 1997'::timestamp without time zone)) Rows Removed by Index Recheck: 3423903 Heap Blocks: exact=14707 lossy=65297 Worker 0: actual time=181.038..4708.094 rows=192729 loops=1 Worker 1: actual time=183.023..4762.284 rows=192285 loops=1 -> Bitmap Index Scan on idx_orders_orderdate (cost=0.00..12158.77 rows=579834 width=0) (actual time=287.316..287.316 rows=574222 loops=1) Index Cond: ((orders.o_orderdate >= '11-01-1996'::date) AND (orders.o_orderdate < 'Sat Feb 01 00:00:00 1997'::timestamp without time zone)) -> Index Scan using idx_lineitem_orderkey on public.lineitem (cost=0.56..17.92 rows=52 width=8) (actual time=0.112..0.112 rows=1 loops=574222) Output: lineitem.l_orderkey, lineitem.l_partkey, lineitem.l_suppkey, lineitem.l_linenumber, lineitem.l_quantity, lineitem.l_extendedprice, lineitem.l_discount, lineitem.l_tax, lineitem.l_returnflag, lineitem.l_linestatus, lineitem.l_shipdate, lineitem.l_commitdate, lineitem.l_receiptdate, lineitem.l_shipinstruct, lineitem.l_shipmode, lineitem.l_comment Index Cond: (lineitem.l_orderkey = orders.o_orderkey) Filter: (lineitem.l_commitdate < lineitem.l_receiptdate) Rows Removed by Filter: 1 Worker 0: actual time=0.111..0.111 rows=1 loops=192729 Worker 1: actual time=0.111..0.111 rows=1 loops=192285 Planning time: 178.880 ms Execution time: 27715.448 ms (46 rows) Time: 28076.475 ms (00:28.076) --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; QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=1573972.90..1573972.91 rows=1 width=32) (actual time=27939.977..27939.978 rows=1 loops=1) Output: (sum((l_extendedprice * l_discount))) -> Finalize Aggregate (cost=1573972.90..1573972.91 rows=1 width=32) (actual time=27939.975..27939.975 rows=1 loops=1) Output: sum((l_extendedprice * l_discount)) -> Gather (cost=1573972.68..1573972.89 rows=2 width=32) (actual time=27934.604..27939.950 rows=3 loops=1) Output: (PARTIAL sum((l_extendedprice * l_discount))) Workers Planned: 2 Workers Launched: 2 -> Partial Aggregate (cost=1572972.68..1572972.69 rows=1 width=32) (actual time=27853.102..27853.102 rows=1 loops=3) Output: PARTIAL sum((l_extendedprice * l_discount)) Worker 0: actual time=27822.149..27822.149 rows=1 loops=1 Worker 1: actual time=27825.981..27825.982 rows=1 loops=1 -> Parallel Bitmap Heap Scan on public.lineitem (cost=303715.82..1570448.86 rows=504763 width=12) (actual time=4842.668..27107.390 rows=398526 loops=3) Output: l_extendedprice, l_discount Recheck Cond: ((lineitem.l_shipdate >= '01-01-1996'::date) AND (lineitem.l_shipdate < 'Wed Jan 01 00:00:00 1997'::timestamp without time zone) AND (lineitem.l_discount >= 0.02) AND (lineitem.l_discount <= 0.04) AND (lineitem.l_quantity < '25'::numeric)) Rows Removed by Index Recheck: 9986142 Heap Blocks: exact=17661 lossy=212307 Worker 0: actual time=4817.331..27090.771 rows=404412 loops=1 Worker 1: actual time=4824.081..27052.222 rows=388092 loops=1 -> Bitmap Index Scan on idx_lineitem_shipdate (cost=0.00..303412.96 rows=1211432 width=0) (actual time=4869.550..4869.550 rows=1195577 loops=1) Index Cond: ((lineitem.l_shipdate >= '01-01-1996'::date) AND (lineitem.l_shipdate < 'Wed Jan 01 00:00:00 1997'::timestamp without time zone) AND (lineitem.l_discount >= 0.02) AND (lineitem.l_discount <= 0.04) AND (lineitem.l_quantity < '25'::numeric)) Planning time: 0.795 ms Execution time: 27968.139 ms (23 rows) Time: 27995.541 ms (00:27.996) --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; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=1291052.44..1291052.45 rows=1 width=32) (actual time=18876.921..18876.923 rows=1 loops=1) Output: (((100.00 * sum(CASE WHEN ((part.p_type)::text ~~ 'PROMO%'::text) THEN (lineitem.l_extendedprice * ('1'::numeric - lineitem.l_discount)) ELSE '0'::numeric END)) / sum((lineitem.l_extendedprice * ('1'::numeric - lineitem.l_discount))))) -> Finalize Aggregate (cost=1291052.44..1291052.45 rows=1 width=32) (actual time=18876.917..18876.917 rows=1 loops=1) Output: ((100.00 * sum(CASE WHEN ((part.p_type)::text ~~ 'PROMO%'::text) THEN (lineitem.l_extendedprice * ('1'::numeric - lineitem.l_discount)) ELSE '0'::numeric END)) / sum((lineitem.l_extendedprice * ('1'::numeric - lineitem.l_discount)))) -> Gather (cost=1291052.20..1291052.41 rows=2 width=64) (actual time=18716.525..18876.787 rows=3 loops=1) Output: (PARTIAL sum(CASE WHEN ((part.p_type)::text ~~ 'PROMO%'::text) THEN (lineitem.l_extendedprice * ('1'::numeric - lineitem.l_discount)) ELSE '0'::numeric END)), (PARTIAL sum((lineitem.l_extendedprice * ('1'::numeric - lineitem.l_discount)))) Workers Planned: 2 Workers Launched: 2 -> Partial Aggregate (cost=1290052.20..1290052.21 rows=1 width=64) (actual time=18785.851..18785.852 rows=1 loops=3) Output: PARTIAL sum(CASE WHEN ((part.p_type)::text ~~ 'PROMO%'::text) THEN (lineitem.l_extendedprice * ('1'::numeric - lineitem.l_discount)) ELSE '0'::numeric END), PARTIAL sum((lineitem.l_extendedprice * ('1'::numeric - lineitem.l_discount))) Worker 0: actual time=18793.441..18793.441 rows=1 loops=1 Worker 1: actual time=18848.601..18848.602 rows=1 loops=1 -> Hash Join (cost=119783.58..1276321.92 rows=784587 width=33) (actual time=3912.894..18305.999 rows=257610 loops=3) Output: part.p_type, lineitem.l_extendedprice, lineitem.l_discount Hash Cond: (lineitem.l_partkey = part.p_partkey) Worker 0: actual time=3887.225..18294.008 rows=253346 loops=1 Worker 1: actual time=3909.881..18338.708 rows=259621 loops=1 -> Parallel Bitmap Heap Scan on public.lineitem (cost=20130.58..1154261.26 rows=326911 width=20) (actual time=566.534..13981.625 rows=257610 loops=3) Output: lineitem.l_extendedprice, lineitem.l_discount, lineitem.l_partkey Recheck Cond: ((lineitem.l_shipdate >= '03-01-1994'::date) AND (lineitem.l_shipdate < 'Fri Apr 01 00:00:00 1994'::timestamp without time zone)) Rows Removed by Index Recheck: 5799659 Heap Blocks: exact=19246 lossy=123474 Worker 0: actual time=556.914..13982.024 rows=253346 loops=1 Worker 1: actual time=567.328..13961.869 rows=259621 loops=1 -> Bitmap Index Scan on idx_lineitem_shipdate (cost=0.00..19934.43 rows=784587 width=0) (actual time=553.762..553.762 rows=772831 loops=1) Index Cond: ((lineitem.l_shipdate >= '03-01-1994'::date) AND (lineitem.l_shipdate < 'Fri Apr 01 00:00:00 1994'::timestamp without time zone)) -> Hash (cost=60981.00..60981.00 rows=2000000 width=25) (actual time=3342.245..3342.245 rows=2000000 loops=3) Output: part.p_type, part.p_partkey Buckets: 65536 Batches: 64 Memory Usage: 2318kB Worker 0: actual time=3327.348..3327.348 rows=2000000 loops=1 Worker 1: actual time=3337.347..3337.347 rows=2000000 loops=1 -> Seq Scan on public.part (cost=0.00..60981.00 rows=2000000 width=25) (actual time=0.039..1841.541 rows=2000000 loops=3) Output: part.p_type, part.p_partkey Worker 0: actual time=0.052..1837.567 rows=2000000 loops=1 Worker 1: actual time=0.036..1791.439 rows=2000000 loops=1 Planning time: 108.005 ms Execution time: 18894.742 ms (37 rows) Time: 19045.489 ms (00:19.045) --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; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=3134810.41..3134810.95 rows=1 width=103) (actual time=52693.660..52693.669 rows=1 loops=1) Output: supplier.s_suppkey, supplier.s_name, supplier.s_address, supplier.s_phone, (sum((lineitem.l_extendedprice * ('1'::numeric - lineitem.l_discount)))) InitPlan 1 (returns $0) -> Aggregate (cost=1482138.36..1482138.37 rows=1 width=32) (actual time=14234.394..14234.394 rows=1 loops=1) Output: max((sum((lineitem_1.l_extendedprice * ('1'::numeric - lineitem_1.l_discount))))) -> Finalize GroupAggregate (cost=1477706.63..1480907.32 rows=98483 width=40) (actual time=13400.869..14202.021 rows=100000 loops=1) Output: lineitem_1.l_suppkey, sum((lineitem_1.l_extendedprice * ('1'::numeric - lineitem_1.l_discount))) Group Key: lineitem_1.l_suppkey -> Sort (cost=1477706.63..1478199.04 rows=196966 width=40) (actual time=13400.813..13552.847 rows=299824 loops=1) Output: lineitem_1.l_suppkey, (PARTIAL sum((lineitem_1.l_extendedprice * ('1'::numeric - lineitem_1.l_discount)))) Sort Key: lineitem_1.l_suppkey Sort Method: external merge Disk: 17936kB -> Gather (cost=1422644.06..1454999.34 rows=196966 width=40) (actual time=10900.562..13031.906 rows=299824 loops=1) Output: lineitem_1.l_suppkey, (PARTIAL sum((lineitem_1.l_extendedprice * ('1'::numeric - lineitem_1.l_discount)))) Workers Planned: 2 Workers Launched: 2 -> Partial GroupAggregate (cost=1421644.06..1434302.74 rows=98483 width=40) (actual time=10857.158..12680.074 rows=99941 loops=3) Output: lineitem_1.l_suppkey, PARTIAL sum((lineitem_1.l_extendedprice * ('1'::numeric - lineitem_1.l_discount))) Group Key: lineitem_1.l_suppkey Worker 0: actual time=10813.477..12418.664 rows=99916 loops=1 Worker 1: actual time=10858.366..12818.913 rows=99956 loops=1 -> Sort (cost=1421644.06..1423929.59 rows=914212 width=20) (actual time=10857.015..11293.526 rows=764547 loops=3) Output: lineitem_1.l_suppkey, lineitem_1.l_extendedprice, lineitem_1.l_discount Sort Key: lineitem_1.l_suppkey Sort Method: external merge Disk: 24352kB Worker 0: actual time=10813.326..11198.269 rows=711293 loops=1 Worker 1: actual time=10858.155..11323.471 rows=793130 loops=1 -> Parallel Bitmap Heap Scan on public.lineitem lineitem_1 (cost=56286.17..1312377.65 rows=914212 width=20) (actual time=872.493..9733.824 rows=764547 loops=3) Output: lineitem_1.l_suppkey, lineitem_1.l_extendedprice, lineitem_1.l_discount Recheck Cond: ((lineitem_1.l_shipdate >= '06-01-1997'::date) AND (lineitem_1.l_shipdate < 'Mon Sep 01 00:00:00 1997'::timestamp without time zone)) Rows Removed by Index Recheck: 10152837 Heap Blocks: exact=10374 lossy=228413 Worker 0: actual time=851.409..9751.704 rows=711293 loops=1 Worker 1: actual time=867.851..9700.153 rows=793130 loops=1 -> Bitmap Index Scan on idx_lineitem_shipdate (cost=0.00..55737.65 rows=2194108 width=0) (actual time=886.037..886.037 rows=2293642 loops=1) Index Cond: ((lineitem_1.l_shipdate >= '06-01-1997'::date) AND (lineitem_1.l_shipdate < 'Mon Sep 01 00:00:00 1997'::timestamp without time zone)) -> Merge Join (cost=1652672.04..1705402.31 rows=98483 width=103) (actual time=52693.656..52693.656 rows=1 loops=1) Output: supplier.s_suppkey, supplier.s_name, supplier.s_address, supplier.s_phone, (sum((lineitem.l_extendedprice * ('1'::numeric - lineitem.l_discount)))) Merge Cond: (lineitem.l_suppkey = supplier.s_suppkey) -> GroupAggregate (cost=1652671.75..1698031.15 rows=98483 width=40) (actual time=52457.181..52457.181 rows=1 loops=1) Output: lineitem.l_suppkey, sum((lineitem.l_extendedprice * ('1'::numeric - lineitem.l_discount))) Group Key: lineitem.l_suppkey Filter: (sum((lineitem.l_extendedprice * ('1'::numeric - lineitem.l_discount))) = $0) Rows Removed by Filter: 42991 -> Sort (cost=1652671.75..1658157.02 rows=2194108 width=20) (actual time=36177.232..36701.330 rows=985901 loops=1) Output: lineitem.l_suppkey, lineitem.l_extendedprice, lineitem.l_discount Sort Key: lineitem.l_suppkey Sort Method: external merge Disk: 70696kB -> Bitmap Heap Scan on public.lineitem (cost=56286.17..1331576.10 rows=2194108 width=20) (actual time=994.501..32090.651 rows=2293642 loops=1) Output: lineitem.l_suppkey, lineitem.l_extendedprice, lineitem.l_discount Recheck Cond: ((lineitem.l_shipdate >= '06-01-1997'::date) AND (lineitem.l_shipdate < 'Mon Sep 01 00:00:00 1997'::timestamp without time zone)) Rows Removed by Index Recheck: 30458512 Heap Blocks: exact=30334 lossy=662532 -> Bitmap Index Scan on idx_lineitem_shipdate (cost=0.00..55737.65 rows=2194108 width=0) (actual time=984.269..984.269 rows=2293642 loops=1) Index Cond: ((lineitem.l_shipdate >= '06-01-1997'::date) AND (lineitem.l_shipdate < 'Mon Sep 01 00:00:00 1997'::timestamp without time zone)) -> Index Scan using supplier_pkey on public.supplier (cost=0.29..4905.29 rows=100000 width=71) (actual time=49.400..213.304 rows=42992 loops=1) Output: supplier.s_suppkey, supplier.s_name, supplier.s_address, supplier.s_nationkey, supplier.s_phone, supplier.s_acctbal, supplier.s_comment Planning time: 74.581 ms Execution time: 52755.845 ms (59 rows) Time: 52874.796 ms (00:52.875) --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); Time: 201.229 ms create index idx on test(a); Time: 1.669 ms insert into test values(generate_series(1,1000000), 'xxxxxxxxxxxxxxxxxxxxxxxxxxccccc'); Time: 7772.591 ms (00:07.773) insert into test values(generate_series(1,1000000), 'xxxxxxxxxxxxxxxxxxxxxxxxxxccccc'); Time: 7736.386 ms (00:07.736) insert into test values(generate_series(1,1000000), 'xxxxxxxxxxxxxxxxxxxxxxxxxxccccc'); Time: 7659.269 ms (00:07.659) insert into test values(generate_series(1,1000000), 'xxxxxxxxxxxxxxxxxxxxxxxxxxccccc'); Time: 7757.046 ms (00:07.757) insert into test values(generate_series(1,1000000), 'xxxxxxxxxxxxxxxxxxxxxxxxxxccccc'); Time: 8364.899 ms (00:08.365) analyze test; Time: 1891.698 ms (00:01.892) explain analyze verbose select * from test where a < 100000 and b='aa'; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------- Gather (cost=12792.72..57683.38 rows=1 width=36) (actual time=252.711..252.711 rows=0 loops=1) Output: a, b Workers Planned: 2 Workers Launched: 2 -> Parallel Bitmap Heap Scan on public.test (cost=11792.72..56683.28 rows=1 width=36) (actual time=221.345..221.345 rows=0 loops=3) Output: a, b Recheck Cond: (test.a < 100000) Filter: ((test.b)::text = 'aa'::text) Rows Removed by Filter: 166665 Heap Blocks: exact=1409 Worker 0: actual time=195.332..195.332 rows=0 loops=1 Worker 1: actual time=217.071..217.071 rows=0 loops=1 -> Bitmap Index Scan on idx (cost=0.00..11792.72 rows=515771 width=0) (actual time=139.566..139.566 rows=499995 loops=1) Index Cond: (test.a < 100000) Planning time: 0.947 ms Execution time: 270.896 ms (16 rows) Time: 314.328 ms drop table test; Time: 291.631 ms --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); Time: 69.902 ms CREATE INDEX i_bmtest_a ON bmscantest(a); Time: 1.646 ms CREATE INDEX i_bmtest_b ON bmscantest(b); Time: 1.322 ms INSERT INTO bmscantest SELECT (r%53), (r%59), 'foooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooo' FROM generate_series(1,70000) r; Time: 995.741 ms INSERT INTO bmscantest SELECT (r%53), (r%59), 'foooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooo' FROM generate_series(1,100000) r; Time: 1604.170 ms (00:01.604) INSERT INTO bmscantest SELECT (r%53), (r%59), 'foooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooo' FROM generate_series(1,100000) r; Time: 1691.388 ms (00:01.691) INSERT INTO bmscantest SELECT (r%53), (r%59), 'foooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooo' FROM generate_series(1,100000) r; Time: 1589.996 ms (00:01.590) INSERT INTO bmscantest SELECT (r%53), (r%59), 'foooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooo' FROM generate_series(1,100000) r; Time: 1660.391 ms (00:01.660) INSERT INTO bmscantest SELECT (r%53), (r%59), 'foooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooo' FROM generate_series(1,100000) r; Time: 1548.249 ms (00:01.548) analyze bmscantest; Time: 219.445 ms explain analyze verbose SELECT * FROM bmscantest WHERE a <= 10 and b <= 20 and t ='x'; QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------- Gather (cost=3508.79..14728.80 rows=1 width=116) (actual time=83.349..83.349 rows=0 loops=1) Output: a, b, t Workers Planned: 2 Workers Launched: 2 -> Parallel Bitmap Heap Scan on public.bmscantest (cost=2508.79..13728.70 rows=1 width=116) (actual time=46.601..46.601 rows=0 loops=3) Output: a, b, t Recheck Cond: (bmscantest.a <= 10) Filter: ((bmscantest.b <= 20) AND (bmscantest.t = 'x'::text)) Rows Removed by Filter: 39437 Heap Blocks: exact=7126 Worker 0: actual time=17.892..17.892 rows=0 loops=1 Worker 1: actual time=39.273..39.273 rows=0 loops=1 -> Bitmap Index Scan on i_bmtest_a (cost=0.00..2508.79 rows=117382 width=0) (actual time=25.861..25.861 rows=118310 loops=1) Index Cond: (bmscantest.a <= 10) Planning time: 0.600 ms Execution time: 105.398 ms (16 rows) Time: 107.261 ms drop table bmscantest; Time: 54.531 ms --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); Time: 95.311 ms insert into t111 (select date '2001-09-28 01:01:01' + x, x from generate_series(1,1000000) x); Time: 2690.101 ms (00:02.690) insert into t111 (select date '2001-09-28 01:01:01' + x, x from generate_series(1,1000000) x); Time: 2763.738 ms (00:02.764) insert into t111 (select date '2001-09-28 01:01:01' + x, x from generate_series(1,1000000) x); Time: 2426.261 ms (00:02.426) insert into t111 (select date '2001-09-28 01:01:01' + x, x from generate_series(1,1000000) x); Time: 2744.733 ms (00:02.745) create index t111_tmidx on t111(a); Time: 6188.071 ms (00:06.188) analyze t111; Time: 257.085 ms explain analyze verbose select * from t111 where a <= date '2207-02-01 00:00:00' and b =500000; QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------- Gather (cost=6359.54..29794.22 rows=1 width=12) (actual time=183.580..183.580 rows=0 loops=1) Output: a, b Workers Planned: 2 Workers Launched: 2 -> Parallel Bitmap Heap Scan on public.t111 (cost=5359.54..28794.12 rows=1 width=12) (actual time=149.565..149.565 rows=0 loops=3) Output: a, b Recheck Cond: (t111.a <= '02-01-2207'::date) Filter: (t111.b = 500000) Rows Removed by Filter: 100000 Heap Blocks: exact=715 Worker 0: actual time=145.635..145.635 rows=0 loops=1 Worker 1: actual time=123.361..123.361 rows=0 loops=1 -> Bitmap Index Scan on t111_tmidx (cost=0.00..5359.53 rows=290014 width=0) (actual time=83.118..83.118 rows=300000 loops=1) Index Cond: (t111.a <= '02-01-2207'::date) Planning time: 0.804 ms Execution time: 212.280 ms (16 rows) Time: 233.904 ms drop table t111; Time: 80.337 ms --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)); Time: 2.207 ms insert into t1112 (select date '2001-09-28 01:01:01' + x, x, x||'char' from generate_series(1,1000000) x); Time: 3436.601 ms (00:03.437) insert into t1112 (select date '2001-09-28 01:01:01' + x, x, x||'char' from generate_series(1,1000000) x); Time: 3398.901 ms (00:03.399) insert into t1112 (select date '2001-09-28 01:01:01' + x, x, x||'char' from generate_series(1,1000000) x); Time: 3521.010 ms (00:03.521) insert into t1112 (select date '2001-09-28 01:01:01' + x, x, x||'char' from generate_series(1,1000000) x); Time: 3297.329 ms (00:03.297) create index t111_tmchridx on t1112(a,c); Time: 6070.803 ms (00:06.071) analyze t1112; Time: 302.617 ms explain analyze verbose select * from t1112 where a <= date '2207-02-01 00:00:00' and b =500000; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------- Gather (cost=8966.08..36265.56 rows=1 width=22) (actual time=130.428..130.428 rows=0 loops=1) Output: a, b, c Workers Planned: 2 Workers Launched: 2 -> Parallel Bitmap Heap Scan on public.t1112 (cost=7966.08..35265.46 rows=1 width=22) (actual time=80.607..80.607 rows=0 loops=3) Output: a, b, c Recheck Cond: (t1112.a <= '02-01-2207'::date) Filter: (t1112.b = 500000) Rows Removed by Filter: 100000 Heap Blocks: exact=814 Worker 0: actual time=50.889..50.889 rows=0 loops=1 Worker 1: actual time=61.313..61.313 rows=0 loops=1 -> Bitmap Index Scan on t111_tmchridx (cost=0.00..7966.08 rows=291420 width=0) (actual time=65.681..65.681 rows=300000 loops=1) Index Cond: (t1112.a <= '02-01-2207'::date) Planning time: 0.232 ms Execution time: 154.650 ms (16 rows) Time: 155.700 ms drop table t1112; Time: 92.867 ms --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); Time: 2.322 ms insert into test1(select 'f', x, 'asdfghjkl' from generate_series(1,10000) x); Time: 22.640 ms insert into test1(select 't', x, 'asdfghjkl' from generate_series(1,3000000) x); Time: 7924.750 ms (00:07.925) create index test_idx on test1(a,b); Time: 3436.457 ms (00:03.436) analyze test1; Time: 172.890 ms explain analyze verbose select * from test1 where a = 't' and b < 200000 and c = 'xyz'; QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------- Gather (cost=5416.63..25903.86 rows=1 width=15) (actual time=97.901..97.901 rows=0 loops=1) Output: a, b, c Workers Planned: 2 Workers Launched: 2 -> Parallel Bitmap Heap Scan on public.test1 (cost=4416.63..24903.76 rows=1 width=15) (actual time=56.790..56.790 rows=0 loops=3) Output: a, b, c Recheck Cond: (test1.b < 200000) Filter: (test1.a AND (test1.c = 'xyz'::text)) Rows Removed by Filter: 66666 Heap Blocks: exact=786 Worker 0: actual time=19.186..19.186 rows=0 loops=1 Worker 1: actual time=53.838..53.838 rows=0 loops=1 -> Bitmap Index Scan on test_idx (cost=0.00..4416.63 rows=210420 width=0) (actual time=40.542..40.542 rows=199999 loops=1) Index Cond: ((test1.a = true) AND (test1.b < 200000)) Planning time: 0.499 ms Execution time: 124.278 ms (16 rows) Time: 126.673 ms drop table test1; Time: 62.268 ms --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)); Time: 1.358 ms CREATE TABLE t2 (c3 int, c4 timestamp, c5 boolean); Time: 0.938 ms INSERT INTO t1(select x, 'asdfghjkl' from generate_series(1, 1000000) x); Time: 2422.066 ms (00:02.422) INSERT INTO t1(select x, 'asdfghjkl' from generate_series(1, 1000000) x); Time: 2170.868 ms (00:02.171) INSERT INTO t2(select x, date '2016-01-01'-x, cast(cast(random() as int) as boolean) from generate_series(1, 2000000) x); Time: 6259.911 ms (00:06.260) INSERT INTO t2(select x, date '2016-01-01'-x, cast(cast(random() as int) as boolean) from generate_series(1, 2000000) x); Time: 6211.037 ms (00:06.211) CREATE MATERIALIZED VIEW mview as (select t1.c1, t1.c2, t2.c4, t2.c5 from t1, t2 where t1.c1 = t2.c3); Time: 14337.309 ms (00:14.337) CREATE INDEX mview_idx on mview(c1,c4); Time: 5051.375 ms (00:05.051) refresh materialized view mview; Time: 17930.759 ms (00:17.931) analyze mview; Time: 292.548 ms explain analyze verbose select * from mview where c1 <= 180000 and c4 <='2015-02-03' and c5 is null; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------ Gather (cost=19575.29..53555.12 rows=1 width=23) (actual time=379.128..379.128 rows=0 loops=1) Output: c1, c2, c4, c5 Workers Planned: 2 Workers Launched: 2 -> Parallel Bitmap Heap Scan on public.mview (cost=18575.29..52555.02 rows=1 width=23) (actual time=321.044..321.044 rows=0 loops=3) Output: c1, c2, c4, c5 Recheck Cond: ((mview.c1 <= 180000) AND (mview.c4 <= 'Tue Feb 03 00:00:00 2015'::timestamp without time zone)) Filter: (mview.c5 IS NULL) Rows Removed by Filter: 239559 Heap Blocks: exact=2125 Worker 0: actual time=297.337..297.337 rows=0 loops=1 Worker 1: actual time=317.173..317.173 rows=0 loops=1 -> Bitmap Index Scan on mview_idx (cost=0.00..18575.29 rows=730837 width=0) (actual time=209.199..209.199 rows=718676 loops=1) Index Cond: ((mview.c1 <= 180000) AND (mview.c4 <= 'Tue Feb 03 00:00:00 2015'::timestamp without time zone)) Planning time: 0.369 ms Execution time: 397.109 ms (16 rows) Time: 426.566 ms DROP MATERIALIZED VIEW mview; Time: 80.807 ms DROP TABLE t1; Time: 19.950 ms DROP TABLE t2; Time: 1071.909 ms (00:01.072) --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); Time: 2.022 ms CREATE INDEX tab4_c1idx on tab4(c1); Time: 1.298 ms INSERT INTO tab4(select date '2016-01-01' - x, x from generate_series(1, 2000000) x); Time: 16673.542 ms (00:16.674) INSERT INTO tab4(select date '2016-01-01' - x, x from generate_series(1, 2000000) x); Time: 17317.615 ms (00:17.318) INSERT INTO tab4(select date '2016-01-01' - x, x from generate_series(1, 2000000) x); Time: 16821.776 ms (00:16.822) analyze tab4; Time: 1989.532 ms (00:01.990) explain analyse verbose select * from tab4 where c1 < '2017-10-03 00:00:00' and c2 <90000; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------ Gather (cost=1000.00..96551.34 rows=256184 width=12) (actual time=0.436..964.317 rows=269997 loops=1) Output: c1, c2 Workers Planned: 2 Workers Launched: 2 -> Parallel Seq Scan on public.tab4 (cost=0.00..69932.94 rows=106743 width=12) (actual time=0.093..810.952 rows=89999 loops=3) Output: c1, c2 Filter: ((tab4.c1 < 'Tue Oct 03 00:00:00 2017'::timestamp without time zone) AND (tab4.c2 < 90000)) Rows Removed by Filter: 1910001 Worker 0: actual time=0.137..835.290 rows=121854 loops=1 Worker 1: actual time=0.118..844.241 rows=125203 loops=1 Planning time: 0.429 ms Execution time: 1041.180 ms (12 rows) Time: 1042.897 ms (00:01.043) set enable_seqscan to 0; Time: 69.576 ms explain analyse verbose select * from tab4 where c1 < '2017-10-03 00:00:00' and c2 <90000; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------ Gather (cost=120488.41..216039.75 rows=256184 width=12) (actual time=1117.213..2535.088 rows=269997 loops=1) Output: c1, c2 Workers Planned: 2 Workers Launched: 2 -> Parallel Bitmap Heap Scan on public.tab4 (cost=119488.41..189421.35 rows=106743 width=12) (actual time=1082.251..2355.206 rows=89999 loops=3) Output: c1, c2 Recheck Cond: (tab4.c1 < 'Tue Oct 03 00:00:00 2017'::timestamp without time zone) Filter: (tab4.c2 < 90000) Rows Removed by Filter: 1910001 Heap Blocks: exact=12337 Worker 0: actual time=1060.607..2370.845 rows=117475 loops=1 Worker 1: actual time=1069.377..2385.458 rows=145757 loops=1 -> Bitmap Index Scan on tab4_c1idx (cost=0.00..119424.36 rows=5999991 width=0) (actual time=1105.911..1105.911 rows=6000000 loops=1) Index Cond: (tab4.c1 < 'Tue Oct 03 00:00:00 2017'::timestamp without time zone) Planning time: 0.506 ms Execution time: 2611.837 ms (16 rows) Time: 2613.454 ms (00:02.613) drop table tab4; Time: 103.902 ms --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'; QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------- Gather (cost=1000.00..14661.77 rows=1 width=19) (actual time=0.576..176.076 rows=30 loops=1) Output: c1, c2, c3 Workers Planned: 2 Workers Launched: 2 -> Parallel Seq Scan on public.tt2 (cost=0.00..13661.67 rows=1 width=19) (actual time=83.228..141.648 rows=10 loops=3) Output: c1, c2, c3 Filter: ((tt2.c1 < 999900) AND (tt2.c2 < 1000) AND (tt2.c3 = 'abc'::bpchar)) Rows Removed by Filter: 333323 Worker 0: actual time=109.808..109.808 rows=0 loops=1 Worker 1: actual time=139.844..139.844 rows=0 loops=1 Planning time: 0.733 ms Execution time: 194.392 ms (12 rows) set enable_seqscan to 0; explain analyze verbose select * from tt2 where c1 < 999900 and c2 <1000 and c3 ='abc'; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------- Gather (cost=19479.69..33140.74 rows=1 width=19) (actual time=179.714..352.040 rows=30 loops=1) Output: c1, c2, c3 Workers Planned: 2 Workers Launched: 2 -> Parallel Bitmap Heap Scan on public.tt2 (cost=18479.69..32140.64 rows=1 width=19) (actual time=273.149..330.475 rows=10 loops=3) Output: c1, c2, c3 Recheck Cond: (tt2.c1 < 999900) Filter: ((tt2.c2 < 1000) AND (tt2.c3 = 'abc'::bpchar)) Rows Removed by Filter: 333289 Heap Blocks: exact=2328 Worker 0: actual time=312.183..312.183 rows=0 loops=1 Worker 1: actual time=328.065..328.065 rows=0 loops=1 -> Bitmap Index Scan on tt2_pkey (cost=0.00..18479.69 rows=999902 width=0) (actual time=177.499..177.499 rows=999898 loops=1) Index Cond: (tt2.c1 < 999900) Planning time: 0.559 ms Execution time: 370.755 ms (16 rows) 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); Time: 2.803 ms INSERT INTO t1 VALUES (generate_series(1,25),'xyz'); Time: 1.251 ms INSERT INTO t1 VALUES (generate_series(26,3000000),'aa'); Time: 6793.103 ms (00:06.793) CREATE INDEX t1_cidx on t1(c1,c2); Time: 3455.372 ms (00:03.455) update t1 set c3 = 500 where c1 <90; Time: 4.031 ms analyze t1; Time: 186.596 ms explain analyze verbose select * from t1 where c1 <5999900 and c2 = 'aa' and c3 = 500; QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------- Gather (cost=1000.00..39092.10 rows=1 width=18) (actual time=488.657..488.892 rows=64 loops=1) Output: c1, c2, c3 Workers Planned: 2 Workers Launched: 2 -> Parallel Seq Scan on public.t1 (cost=0.00..38092.00 rows=1 width=18) (actual time=453.822..453.841 rows=21 loops=3) Output: c1, c2, c3 Filter: ((t1.c1 < 5999900) AND (t1.c2 = 'aa'::bpchar) AND (t1.c3 = 500)) Rows Removed by Filter: 999979 Worker 0: actual time=425.750..425.750 rows=0 loops=1 Worker 1: actual time=447.934..447.934 rows=0 loops=1 Planning time: 0.363 ms Execution time: 508.212 ms (12 rows) Time: 510.243 ms set enable_seqscan to 0; Time: 0.219 ms explain analyze verbose select * from t1 where c1 <5999900 and c2 = 'aa' and c3 = 500; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------- Gather (cost=77220.43..115312.53 rows=1 width=18) (actual time=1646.439..1647.064 rows=64 loops=1) Output: c1, c2, c3 Workers Planned: 2 Workers Launched: 2 -> Parallel Bitmap Heap Scan on public.t1 (cost=76220.43..114312.43 rows=1 width=18) (actual time=1619.037..1619.047 rows=21 loops=3) Output: c1, c2, c3 Recheck Cond: ((t1.c1 < 5999900) AND (t1.c2 = 'aa'::bpchar)) Filter: (t1.c3 = 500) Rows Removed by Filter: 999970 Heap Blocks: exact=5653 Worker 0: actual time=1595.259..1595.259 rows=0 loops=1 Worker 1: actual time=1615.878..1615.878 rows=0 loops=1 -> Bitmap Index Scan on t1_cidx (cost=0.00..76220.43 rows=3000000 width=0) (actual time=838.167..838.167 rows=3000039 loops=1) Index Cond: ((t1.c1 < 5999900) AND (t1.c2 = 'aa'::bpchar)) Planning time: 0.275 ms Execution time: 1672.624 ms (16 rows) Time: 1674.045 ms (00:01.674) drop table t1; Time: 88.518 ms --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'; QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------- Finalize Aggregate (cost=16597.58..16597.59 rows=1 width=8) (actual time=337.584..337.584 rows=1 loops=1) Output: count(1) -> Gather (cost=16597.37..16597.58 rows=2 width=8) (actual time=337.341..337.572 rows=3 loops=1) Output: (PARTIAL count(1)) Workers Planned: 2 Workers Launched: 2 -> Partial Aggregate (cost=15597.37..15597.38 rows=1 width=8) (actual time=303.944..303.944 rows=1 loops=3) Output: PARTIAL count(1) Worker 0: actual time=275.234..275.234 rows=1 loops=1 Worker 1: actual time=300.080..300.080 rows=1 loops=1 -> Parallel Seq Scan on public.ary_tab (cost=0.00..15597.33 rows=14 width=0) (actual time=191.734..297.053 rows=17 loops=3) Filter: (('{7,8,9,10}'::integer[] = ary_tab.c2) AND (ary_tab.c1 = 'four'::text)) Rows Removed by Filter: 333334 Worker 0: actual time=275.139..275.139 rows=0 loops=1 Worker 1: actual time=300.007..300.007 rows=0 loops=1 Planning time: 1.199 ms Execution time: 356.008 ms (17 rows) set enable_seqscan to 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=52942.41..52942.42 rows=1 width=8) (actual time=661.095..661.096 rows=1 loops=1) Output: count(1) -> Gather (cost=52942.20..52942.41 rows=2 width=8) (actual time=660.780..661.084 rows=3 loops=1) Output: (PARTIAL count(1)) Workers Planned: 2 Workers Launched: 2 -> Partial Aggregate (cost=51942.20..51942.21 rows=1 width=8) (actual time=640.002..640.002 rows=1 loops=3) Output: PARTIAL count(1) Worker 0: actual time=622.079..622.079 rows=1 loops=1 Worker 1: actual time=637.510..637.511 rows=1 loops=1 -> Parallel Bitmap Heap Scan on public.ary_tab (cost=36344.83..51942.16 rows=14 width=0) (actual time=574.908..639.984 rows=17 loops=3) Recheck Cond: ('{7,8,9,10}'::integer[] = ary_tab.c2) Filter: (ary_tab.c1 = 'four'::text) Rows Removed by Filter: 333333 Heap Blocks: exact=3602 Worker 0: actual time=622.065..622.065 rows=0 loops=1 Worker 1: actual time=637.495..637.495 rows=0 loops=1 -> Bitmap Index Scan on ary_idx (cost=0.00..36344.82 rows=1000053 width=0) (actual time=462.596..462.596 rows=1000050 loops=1) Index Cond: ('{7,8,9,10}'::integer[] = ary_tab.c2) Planning time: 0.247 ms Execution time: 679.541 ms (21 rows) 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); Time: 6.022 ms INSERT INTO s_pis (select null, 1+x from generate_series(1,1000000) x); Time: 2482.290 ms (00:02.482) INSERT INTO s_pis(n1) select NULL from generate_series(1,100000) ; Time: 227.546 ms CREATE INDEX test2_info_nulls_low ON s_pis (n1 NULLS FIRST); Time: 1426.872 ms (00:01.427) analyze s_pis; Time: 100.635 ms explain analyze verbose 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=115.261..115.261 rows=0 loops=1) Output: n, n1 Sort Key: s_pis.n1 Sort Method: quicksort Memory: 25kB -> Gather (cost=1000.00..11498.27 rows=1 width=8) (actual time=115.249..115.249 rows=0 loops=1) Output: n, n1 Workers Planned: 2 Workers Launched: 2 -> Parallel Seq Scan on public.s_pis (cost=0.00..10498.17 rows=1 width=8) (actual time=80.169..80.169 rows=0 loops=3) Output: n, n1 Filter: ((s_pis.n IS NOT NULL) AND (s_pis.n1 < 9000000)) Rows Removed by Filter: 366667 Worker 0: actual time=47.843..47.843 rows=0 loops=1 Worker 1: actual time=78.301..78.301 rows=0 loops=1 Planning time: 0.993 ms Execution time: 138.301 ms (16 rows) Time: 140.868 ms set enable_seqscan to 0; Time: 0.166 ms explain analyze verbose select * from s_pis where n1 < 9000000 and n is not null order by n1 NULLS LAST; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------ Sort (cost=29448.88..29448.88 rows=1 width=8) (actual time=353.341..353.341 rows=0 loops=1) Output: n, n1 Sort Key: s_pis.n1 Sort Method: quicksort Memory: 25kB -> Gather (cost=19473.48..29448.87 rows=1 width=8) (actual time=353.333..353.333 rows=0 loops=1) Output: n, n1 Workers Planned: 2 Workers Launched: 2 -> Parallel Bitmap Heap Scan on public.s_pis (cost=18473.48..28448.77 rows=1 width=8) (actual time=325.262..325.262 rows=0 loops=3) Output: n, n1 Recheck Cond: (s_pis.n1 < 9000000) Filter: (s_pis.n IS NOT NULL) Rows Removed by Filter: 333333 Heap Blocks: exact=1638 Worker 0: actual time=301.442..301.442 rows=0 loops=1 Worker 1: actual time=321.749..321.749 rows=0 loops=1 -> Bitmap Index Scan on test2_info_nulls_low (cost=0.00..18473.48 rows=999607 width=0) (actual time=173.691..173.691 rows=1000000 loops=1) Index Cond: (s_pis.n1 < 9000000) Planning time: 0.175 ms Execution time: 376.670 ms (20 rows) Time: 377.784 ms drop table s_pis; Time: 19.803 ms --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; QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------------- Finalize Aggregate (cost=15470.14..15470.15 rows=1 width=8) (actual time=299.964..299.965 rows=1 loops=1) Output: count(1) -> Gather (cost=15469.93..15470.14 rows=2 width=8) (actual time=299.804..299.944 rows=3 loops=1) Output: (PARTIAL count(1)) Workers Planned: 2 Workers Launched: 2 -> Partial Aggregate (cost=14469.93..14469.94 rows=1 width=8) (actual time=265.299..265.299 rows=1 loops=3) Output: PARTIAL count(1) Worker 0: actual time=236.252..236.253 rows=1 loops=1 Worker 1: actual time=260.367..260.368 rows=1 loops=1 -> Parallel Seq Scan on public.tst_pis (cost=0.00..13532.33 rows=375038 width=0) (actual time=7.131..163.560 rows=300000 loops=3) Filter: (tst_pis.c1 > 100000) Rows Removed by Filter: 33333 Worker 0: actual time=0.075..143.680 rows=232624 loops=1 Worker 1: actual time=0.068..159.156 rows=255840 loops=1 Planning time: 0.726 ms Execution time: 318.609 ms (17 rows) set enable_seqscan to 0; explain analyze verbose select count(1) from tst_pis where c1 > 100000; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------------------- Finalize Aggregate (cost=31809.93..31809.94 rows=1 width=8) (actual time=456.044..456.045 rows=1 loops=1) Output: count(1) -> Gather (cost=31809.71..31809.92 rows=2 width=8) (actual time=455.618..456.032 rows=3 loops=1) Output: (PARTIAL count(1)) Workers Planned: 2 Workers Launched: 2 -> Partial Aggregate (cost=30809.71..30809.72 rows=1 width=8) (actual time=422.358..422.358 rows=1 loops=3) Output: PARTIAL count(1) Worker 0: actual time=393.019..393.019 rows=1 loops=1 Worker 1: actual time=418.799..418.800 rows=1 loops=1 -> Parallel Bitmap Heap Scan on public.tst_pis (cost=16860.14..29872.12 rows=375038 width=0) (actual time=121.103..323.812 rows=300000 loops=3) Recheck Cond: (tst_pis.c1 > 100000) Heap Blocks: exact=2894 Worker 0: actual time=94.048..291.621 rows=268680 loops=1 Worker 1: actual time=117.826..313.937 rows=284160 loops=1 -> Bitmap Index Scan on tst_idx (cost=0.00..16635.11 rows=900092 width=0) (actual time=149.537..149.537 rows=900000 loops=1) Index Cond: (tst_pis.c1 > 100000) Planning time: 0.180 ms Execution time: 485.087 ms (19 rows) 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)); Time: 7.944 ms INSERT INTO tst_pis1 (select x, 'c2_'||x, x/3,'c4_'||x from generate_series(1,1000000) x); Time: 3550.689 ms (00:03.551) CREATE INDEX tst_cidx1 on tst_pis1 (c1,c3,c4); Time: 1359.475 ms (00:01.359) CREATE INDEX tst_idx1 on tst_pis1 (c1); Time: 792.215 ms analyze tst_pis1; Time: 351.901 ms explain analyze verbose select * from tst_pis1 where c1>=100 and c2 like 'c2_10%'; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------- Gather (cost=1000.00..16584.00 rows=10100 width=30) (actual time=0.775..214.907 rows=11111 loops=1) Output: c1, c2, c3, c4 Workers Planned: 2 Workers Launched: 2 -> Parallel Seq Scan on public.tst_pis1 (cost=0.00..14574.00 rows=4208 width=30) (actual time=0.426..173.202 rows=3704 loops=3) Output: c1, c2, c3, c4 Filter: ((tst_pis1.c1 >= 100) AND (tst_pis1.c2 ~~ 'c2_10%'::text)) Rows Removed by Filter: 329630 Worker 0: actual time=0.103..155.172 rows=1201 loops=1 Worker 1: actual time=1.076..161.472 rows=5263 loops=1 Planning time: 1.089 ms Execution time: 237.452 ms (12 rows) Time: 240.285 ms set enable_seqscan =0; Time: 0.317 ms explain analyze verbose select * from tst_pis1 where c1>=100 and c2 like 'c2_10%'; QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------------- Gather (cost=19482.15..35065.48 rows=10100 width=30) (actual time=240.894..477.140 rows=11111 loops=1) Output: c1, c2, c3, c4 Workers Planned: 2 Workers Launched: 2 -> Parallel Bitmap Heap Scan on public.tst_pis1 (cost=18482.15..33055.48 rows=4208 width=30) (actual time=222.567..453.004 rows=3704 loops=3) Output: c1, c2, c3, c4 Recheck Cond: (tst_pis1.c1 >= 100) Filter: (tst_pis1.c2 ~~ 'c2_10%'::text) Rows Removed by Filter: 329597 Heap Blocks: exact=2628 Worker 0: actual time=205.996..436.770 rows=4544 loops=1 Worker 1: actual time=221.484..454.648 rows=6306 loops=1 -> Bitmap Index Scan on tst_idx1 (cost=0.00..18479.62 rows=999893 width=0) (actual time=237.537..237.537 rows=999901 loops=1) Index Cond: (tst_pis1.c1 >= 100) Planning time: 0.415 ms Execution time: 505.452 ms (16 rows) Time: 507.032 ms drop table tst_pis1; Time: 63.243 ms --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; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Finalize Aggregate (cost=17728.22..17728.23 rows=1 width=8) (actual time=630.670..630.671 rows=1 loops=1) Output: count(*) -> Gather (cost=17728.00..17728.21 rows=2 width=8) (actual time=630.637..630.658 rows=3 loops=1) Output: (PARTIAL count(*)) Workers Planned: 2 Workers Launched: 2 -> Partial Aggregate (cost=16728.00..16728.01 rows=1 width=8) (actual time=594.911..594.911 rows=1 loops=3) Output: PARTIAL count(*) Worker 0: actual time=565.850..565.850 rows=1 loops=1 Worker 1: actual time=588.810..588.811 rows=1 loops=1 -> Parallel Seq Scan on public.t2_pis (cost=0.00..15686.33 rows=416667 width=0) (actual time=0.133..503.597 rows=333333 loops=3) Filter: (((t2_pis.c4)::text = 'xyz'::text) AND (t2_pis.c5 = '1.1'::double precision) AND (t2_pis.c3 = to_date('25-09-2015'::text, 'dd-mm-yyyy'::text))) Worker 0: actual time=0.193..479.293 rows=290496 loops=1 Worker 1: actual time=0.180..498.352 rows=306544 loops=1 Planning time: 0.801 ms Execution time: 649.662 ms (16 rows) 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; QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Finalize Aggregate (cost=45890.64..45890.65 rows=1 width=8) (actual time=657.909..657.909 rows=1 loops=1) Output: count(*) -> Gather (cost=45890.43..45890.64 rows=2 width=8) (actual time=657.878..657.899 rows=3 loops=1) Output: (PARTIAL count(*)) Workers Planned: 2 Workers Launched: 2 -> Partial Aggregate (cost=44890.43..44890.44 rows=1 width=8) (actual time=624.307..624.308 rows=1 loops=3) Output: PARTIAL count(*) Worker 0: actual time=595.362..595.363 rows=1 loops=1 Worker 1: actual time=620.204..620.204 rows=1 loops=1 -> Parallel Bitmap Heap Scan on public.t2_pis (cost=28162.43..43848.76 rows=416667 width=0) (actual time=307.927..526.358 rows=333333 loops=3) Recheck 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)) Heap Blocks: exact=2438 Worker 0: actual time=280.323..498.656 rows=331424 loops=1 Worker 1: actual time=304.742..522.157 rows=337008 loops=1 -> Bitmap Index Scan on t2_idx (cost=0.00..27912.43 rows=1000000 width=0) (actual time=336.679..336.679 rows=1000000 loops=1) 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)) Planning time: 0.332 ms Execution time: 674.893 ms (19 rows) --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; Time: 0.500 ms 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=42348.98..42348.99 rows=1 width=8) (actual time=556.875..556.875 rows=1 loops=1) Output: count(*) -> Gather (cost=42348.76..42348.97 rows=2 width=8) (actual time=556.627..556.861 rows=3 loops=1) Output: (PARTIAL count(*)) Workers Planned: 2 Workers Launched: 2 -> Partial Aggregate (cost=41348.76..41348.77 rows=1 width=8) (actual time=530.913..530.914 rows=1 loops=3) Output: PARTIAL count(*) Worker 0: actual time=509.921..509.922 rows=1 loops=1 Worker 1: actual time=527.028..527.028 rows=1 loops=1 -> Parallel Bitmap Heap Scan on public.t2_pis (cost=25662.43..40307.09 rows=416667 width=0) (actual time=241.031..436.501 rows=333333 loops=3) Recheck Cond: ((t2_pis.c3 = to_date('25-09-2015'::text, 'dd-mm-yyyy'::text)) AND ((t2_pis.c4)::text = 'xyz'::text)) Heap Blocks: exact=2602 Worker 0: actual time=223.074..415.166 rows=308992 loops=1 Worker 1: actual time=236.862..431.995 rows=337144 loops=1 -> Bitmap Index Scan on t2_idx (cost=0.00..25412.43 rows=1000000 width=0) (actual time=261.063..261.063 rows=1000000 loops=1) Index Cond: ((t2_pis.c3 = to_date('25-09-2015'::text, 'dd-mm-yyyy'::text)) AND ((t2_pis.c4)::text = 'xyz'::text)) Planning time: 2.028 ms Execution time: 575.205 ms (19 rows) Time: 581.522 ms drop table t2_pis cascade; Time: 28.453 ms --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; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------- Gather (cost=1000.00..32609.10 rows=1 width=21) (actual time=250.675..250.675 rows=0 loops=1) Output: c1, c2 Workers Planned: 2 Workers Launched: 2 -> Parallel Seq Scan on public.mview_tab113 (cost=0.00..31609.00 rows=1 width=21) (actual time=213.714..213.714 rows=0 loops=3) Output: c1, c2 Filter: ((mview_tab113.c1 IS NULL) AND (mview_tab113.c2 IS NOT NULL)) Rows Removed by Filter: 1000000 Worker 0: actual time=187.308..187.308 rows=0 loops=1 Worker 1: actual time=204.388..204.388 rows=0 loops=1 Planning time: 0.258 ms Execution time: 270.790 ms (12 rows) set enable_seqscan =0; explain analyze verbose select * from mview_tab113 where c1 is null and c2 is not null; QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------------------- Gather (cost=82552.43..114161.53 rows=1 width=21) (actual time=1079.049..1079.049 rows=0 loops=1) Output: c1, c2 Workers Planned: 2 Workers Launched: 2 -> Parallel Bitmap Heap Scan on public.mview_tab113 (cost=81552.43..113161.43 rows=1 width=21) (actual time=1059.002..1059.002 rows=0 loops=3) Output: c1, c2 Recheck Cond: (mview_tab113.c2 IS NOT NULL) Filter: (mview_tab113.c1 IS NULL) Rows Removed by Filter: 1000000 Heap Blocks: exact=6593 Worker 0: actual time=1041.240..1041.240 rows=0 loops=1 Worker 1: actual time=1057.586..1057.586 rows=0 loops=1 -> Bitmap Index Scan on mvw_idx32 (cost=0.00..81552.43 rows=3000000 width=0) (actual time=585.958..585.958 rows=3000000 loops=1) Index Cond: (mview_tab113.c2 IS NOT NULL) Planning time: 0.196 ms Execution time: 1098.697 ms (16 rows) drop table tab113 cascade; NOTICE: drop cascades to materialized view mview_tab113 --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); Time: 7.455 ms INSERT INTO tab121(select x, substr(md5(random()::text),1,10)||x from generate_series(1,3000000) x); Time: 64153.867 ms (01:04.154) CREATE view vw_tab121 as select * from tab121 where c1 < 2500000; Time: 2.705 ms analyze tab121 ; Time: 684.704 ms explain analyze verbose select * from vw_tab121 where c1 < 200 and c2 is not null; QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------- Gather (cost=1000.00..38889.00 rows=300 width=21) (actual time=0.415..438.053 rows=199 loops=1) Output: tab121.c1, tab121.c2 Workers Planned: 2 Workers Launched: 2 -> Parallel Seq Scan on public.tab121 (cost=0.00..37859.00 rows=125 width=21) (actual time=263.458..409.239 rows=66 loops=3) Output: tab121.c1, tab121.c2 Filter: ((tab121.c2 IS NOT NULL) AND (tab121.c1 < 2500000) AND (tab121.c1 < 200)) Rows Removed by Filter: 999934 Worker 0: actual time=384.053..384.053 rows=0 loops=1 Worker 1: actual time=406.285..406.285 rows=0 loops=1 Planning time: 0.653 ms Execution time: 456.633 ms (12 rows) Time: 458.296 ms set enable_seqscan =0; Time: 0.202 ms explain analyze verbose select * from vw_tab121 where c1 < 200 and c2 is not null; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------- Gather (cost=100736.50..138625.51 rows=300 width=21) (actual time=686.849..1269.819 rows=199 loops=1) Output: tab121.c1, tab121.c2 Workers Planned: 2 Workers Launched: 2 -> Parallel Bitmap Heap Scan on public.tab121 (cost=99736.50..137595.51 rows=125 width=21) (actual time=1041.444..1235.570 rows=66 loops=3) Output: tab121.c1, tab121.c2 Recheck Cond: (tab121.c2 IS NOT NULL) Filter: ((tab121.c1 < 2500000) AND (tab121.c1 < 200)) Rows Removed by Filter: 999934 Heap Blocks: exact=6643 Worker 0: actual time=1204.761..1204.761 rows=0 loops=1 Worker 1: actual time=1233.106..1233.106 rows=0 loops=1 -> Bitmap Index Scan on tab121_pkey (cost=0.00..99736.43 rows=3000000 width=0) (actual time=677.902..677.902 rows=3000000 loops=1) Index Cond: (tab121.c2 IS NOT NULL) Planning time: 0.177 ms Execution time: 1289.578 ms (16 rows) Time: 1291.131 ms (00:01.291) drop view vw_tab121; Time: 2.703 ms drop table tab121; Time: 101.274 ms --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; QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------- Sort (cost=38901.34..38902.09 rows=300 width=21) (actual time=415.144..415.213 rows=199 loops=1) Output: tab131.c1, tab131.c2 Sort Key: tab131.c2 DESC Sort Method: quicksort Memory: 41kB -> Gather (cost=1000.00..38889.00 rows=300 width=21) (actual time=0.488..414.807 rows=199 loops=1) Output: tab131.c1, tab131.c2 Workers Planned: 2 Workers Launched: 2 -> Parallel Seq Scan on public.tab131 (cost=0.00..37859.00 rows=125 width=21) (actual time=242.317..380.303 rows=66 loops=3) Output: tab131.c1, tab131.c2 Filter: ((tab131.c2 IS NOT NULL) AND (tab131.c1 < 2500000) AND (tab131.c1 < 200)) Rows Removed by Filter: 999934 Worker 0: actual time=351.229..351.229 rows=0 loops=1 Worker 1: actual time=375.676..375.676 rows=0 loops=1 Planning time: 0.734 ms Execution time: 448.657 ms (16 rows) set enable_seqscan to 0; explain analyze verbose select * from vw_tab131 where c1 < 200 and c2 is not null order by c2 desc; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------- Sort (cost=138177.85..138178.60 rows=300 width=21) (actual time=1216.912..1216.968 rows=199 loops=1) Output: tab131.c1, tab131.c2 Sort Key: tab131.c2 DESC Sort Method: quicksort Memory: 41kB -> Gather (cost=100276.50..138165.51 rows=300 width=21) (actual time=669.972..1216.567 rows=199 loops=1) Output: tab131.c1, tab131.c2 Workers Planned: 2 Workers Launched: 2 -> Parallel Bitmap Heap Scan on public.tab131 (cost=99276.50..137135.51 rows=125 width=21) (actual time=1002.423..1184.456 rows=66 loops=3) Output: tab131.c1, tab131.c2 Recheck Cond: (tab131.c2 IS NOT NULL) Filter: ((tab131.c1 < 2500000) AND (tab131.c1 < 200)) Rows Removed by Filter: 999934 Heap Blocks: exact=6651 Worker 0: actual time=1156.166..1156.166 rows=0 loops=1 Worker 1: actual time=1181.392..1181.392 rows=0 loops=1 -> Bitmap Index Scan on tab131_idx2 (cost=0.00..99276.43 rows=3000000 width=0) (actual time=663.612..663.612 rows=3000000 loops=1) Index Cond: (tab131.c2 IS NOT NULL) Planning time: 0.250 ms Execution time: 1236.838 ms (20 rows) 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)); Time: 5.960 ms INSERT INTO tab141(select x, substr(md5(random()::text),1,10)||x from generate_series(1,3000000) x); Time: 19635.095 ms (00:19.635) CREATE materialized view mview_tab141 as select * from tab141 where c1 < 2500000; Time: 3075.946 ms (00:03.076) CREATE index mvw_idx42 on mview_tab141 (c2); Time: 12591.584 ms (00:12.592) refresh materialized view mview_tab141 ; Time: 14931.692 ms (00:14.932) analyze mview_tab141; Time: 248.587 ms explain analyze verbose select * from mview_tab141 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=274.621..274.670 rows=199 loops=1) Output: c1, c2 Sort Key: mview_tab141.c2 DESC Sort Method: quicksort Memory: 41kB -> Gather (cost=1000.00..29969.83 rows=250 width=21) (actual time=0.510..274.368 rows=199 loops=1) Output: c1, c2 Workers Planned: 2 Workers Launched: 2 -> Parallel Seq Scan on public.mview_tab141 (cost=0.00..28944.83 rows=104 width=21) (actual time=146.565..237.727 rows=66 loops=3) Output: c1, c2 Filter: ((mview_tab141.c2 IS NOT NULL) AND (mview_tab141.c1 < 200)) Rows Removed by Filter: 833267 Worker 0: actual time=204.155..204.155 rows=0 loops=1 Worker 1: actual time=235.514..235.514 rows=0 loops=1 Planning time: 0.397 ms Execution time: 293.297 ms (16 rows) Time: 295.340 ms set enable_seqscan to 0; Time: 0.278 ms explain analyze verbose select * from mview_tab141 where c1 < 200 and c2 is not null order by c2 desc; QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------------------------- Sort (cost=97866.27..97866.90 rows=250 width=21) (actual time=928.403..928.554 rows=199 loops=1) Output: c1, c2 Sort Key: mview_tab141.c2 DESC Sort Method: quicksort Memory: 41kB -> Gather (cost=68886.48..97856.31 rows=250 width=21) (actual time=520.825..927.746 rows=199 loops=1) Output: c1, c2 Workers Planned: 2 Workers Launched: 2 -> Parallel Bitmap Heap Scan on public.mview_tab141 (cost=67886.48..96831.31 rows=104 width=21) (actual time=773.293..908.750 rows=66 loops=3) Output: c1, c2 Recheck Cond: (mview_tab141.c2 IS NOT NULL) Filter: (mview_tab141.c1 < 200) Rows Removed by Filter: 833267 Heap Blocks: exact=5445 Worker 0: actual time=892.476..892.476 rows=0 loops=1 Worker 1: actual time=906.930..906.930 rows=0 loops=1 -> Bitmap Index Scan on mvw_idx42 (cost=0.00..67886.42 rows=2499999 width=0) (actual time=515.487..515.487 rows=2499999 loops=1) Index Cond: (mview_tab141.c2 IS NOT NULL) Planning time: 0.388 ms Execution time: 947.731 ms (20 rows) Time: 949.456 ms drop table tab141 cascade; NOTICE: drop cascades to materialized view mview_tab141 Time: 118.464 ms --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; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------- Gather (cost=1000.00..22365.80 rows=158 width=8) (actual time=0.599..319.835 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=66 width=8) (actual time=176.717..282.934 rows=166 loops=3) Output: n, n1 Filter: ((tabl2.n < 1000000) AND (tabl2.n1 < 500)) Rows Removed by Filter: 666501 Worker 0: actual time=250.703..250.703 rows=0 loops=1 Worker 1: actual time=279.373..279.373 rows=0 loops=1 Planning time: 0.672 ms Execution time: 339.564 ms (12 rows) set enable_seqscan to 0; explain analyze verbose select * from tabl2 where n< 1000000 and n1 <500; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------- Gather (cost=19472.10..34587.59 rows=158 width=8) (actual time=169.016..352.020 rows=498 loops=1) Output: n, n1 Workers Planned: 2 Workers Launched: 2 -> Parallel Bitmap Heap Scan on public.tabl2 (cost=18472.10..33571.79 rows=66 width=8) (actual time=271.601..332.430 rows=166 loops=3) Output: n, n1 Recheck Cond: (tabl2.n < 1000000) Filter: (tabl2.n1 < 500) Rows Removed by Filter: 333167 Heap Blocks: exact=1786 Worker 0: actual time=315.485..315.485 rows=0 loops=1 Worker 1: actual time=330.900..330.900 rows=0 loops=1 -> Bitmap Index Scan on tabl2_pkey (cost=0.00..18472.06 rows=999951 width=0) (actual time=167.072..167.072 rows=999999 loops=1) Index Cond: (tabl2.n < 1000000) Planning time: 0.305 ms Execution time: 371.733 ms (16 rows) \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; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------- Index Scan using tabl1_pkey on pg_temp_2.tabl1 (cost=0.43..35461.15 rows=245 width=8) (actual time=0.078..354.241 rows=498 loops=1) Output: n, n1 Index Cond: (tabl1.n < 1000000) Filter: (tabl1.n1 < 500) Rows Removed by Filter: 999501 Planning time: 0.496 ms Execution time: 354.393 ms (7 rows) set enable_seqscan to 0; 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..35461.15 rows=245 width=8) (actual time=0.048..344.220 rows=498 loops=1) Output: n, n1 Index Cond: (tabl1.n < 1000000) Filter: (tabl1.n1 < 500) Rows Removed by Filter: 999501 Planning time: 0.151 ms Execution time: 344.341 ms (7 rows) 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); Time: 5.130 ms INSERT INTO testing select generate_series(1,5000000); Time: 9917.080 ms (00:09.917) CREATE INDEX cccc on testing(n); Time: 4796.657 ms (00:04.797) analyze testing; Time: 235.502 ms vacuum testing; Time: 1014.194 ms (00:01.014) 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; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------- Nested Loop (cost=0.86..134632.91 rows=5000000 width=12) (actual time=0.061..4053.879 rows=5000000 loops=1) Output: testing.n, t1.n, t2.n -> Nested Loop (cost=0.86..8.91 rows=1 width=8) (actual time=0.042..0.086 rows=1 loops=1) Output: testing.n, t2.n -> Index Only Scan using cccc on public.testing (cost=0.43..4.45 rows=1 width=4) (actual time=0.024..0.024 rows=1 loops=1) Output: testing.n Index Cond: (testing.n = 1) Heap Fetches: 0 -> Index Only Scan using cccc on public.testing t2 (cost=0.43..4.45 rows=1 width=4) (actual time=0.005..0.010 rows=1 loops=1) Output: t2.n Index Cond: (t2.n = 1) Heap Fetches: 0 -> Seq Scan on public.testing t1 (cost=0.00..84624.00 rows=5000000 width=4) (actual time=0.018..2000.499 rows=5000000 loops=1) Output: t1.n Filter: (t1.n >= 1) Planning time: 0.791 ms Execution time: 4870.820 ms (17 rows) Time: 4874.702 ms (00:04.875) set enable_seqscan to 0; Time: 0.319 ms set enable_indexonlyscan to 0; Time: 0.136 ms set parallel_setup_cost to 0; Time: 0.125 ms set parallel_tuple_cost to 0; Time: 0.114 ms 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; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------ Nested Loop (cost=93603.30..191785.01 rows=5000000 width=12) (actual time=885.789..6911.219 rows=5000000 loops=1) Output: testing.n, t1.n, t2.n -> Nested Loop (cost=0.86..16.91 rows=1 width=8) (actual time=0.033..0.038 rows=1 loops=1) Output: testing.n, t2.n -> Index Scan using cccc on public.testing (cost=0.43..8.45 rows=1 width=4) (actual time=0.025..0.026 rows=1 loops=1) Output: testing.n Index Cond: (testing.n = 1) -> Index Scan using cccc on public.testing t2 (cost=0.43..8.45 rows=1 width=4) (actual time=0.005..0.009 rows=1 loops=1) Output: t2.n Index Cond: (t2.n = 1) -> Gather (cost=93602.43..141768.10 rows=5000000 width=4) (actual time=885.750..4663.233 rows=5000000 loops=1) Output: t1.n Workers Planned: 2 Workers Launched: 2 -> Parallel Bitmap Heap Scan on public.testing t1 (cost=93602.43..141768.10 rows=2083333 width=4) (actual time=852.242..1978.973 rows=1666667 loops=3) Output: t1.n Recheck Cond: (t1.n >= 1) Heap Blocks: exact=28 Worker 0: actual time=824.443..2468.427 rows=2461140 loops=1 Worker 1: actual time=847.194..2578.424 rows=2532532 loops=1 -> Bitmap Index Scan on cccc (cost=0.00..92352.43 rows=5000000 width=0) (actual time=878.121..878.121 rows=5000000 loops=1) Index Cond: (t1.n >= 1) Planning time: 0.557 ms Execution time: 7845.241 ms (24 rows) Time: 7846.817 ms (00:07.847) drop table testing; Time: 76.000 ms