On 01/19/2017 05:37 PM, Rafia Sabih wrote:
We did some testing of this feature and written few testcases. PFA the sql script(along with the expected .out files)Please find the attached file rebased patch of parallel index-only scan on the latest Parallel index-scan patch [1].
In addition we have generated the LCOV (code coverage) report and compared the files which are changed for this. You can see the numbers for "with_patch" V/s "with_patch+TestCases" (.pdf file is attached)
-- regards,tushar EnterpriseDB https://www.enterprisedb.com/ The Enterprise PostgreSQL Company
--create database
--create database test;
-- Running script for pgbanch tables against a scaling factor of 1,000
--\! ./pgbench -i -s 1000 test > /tmp/my_logs 2>&1
--\c test
-- TestCase: 1
--Description
/*
Explain plan catching Parallel index Only scan when in WHERE condition
a) Single column of integer Type having index.
b) condition: where the column having index is trying to fetch NOT NULL values("IS NOT NULL").
-- Need to disable SEQUENTIAL SCAN to reproduce explain plan catch "parallel index Only scan"
*/
\c test
set enable_seqscan =0;
explain analyze verbose select count(*) from pgbench_accounts where aid is not null;
-- TestCase: 2
--Description
/*
Explain plan catching parallel index Only scan:
a) both columns are of integer Type.
b) 1 column is having index and another is non key column.
c) condition: where the column having BETWEEN .. AND .. condition both in Index and Non-Index column in WHERE condition.
*/
explain analyze verbose select count(aid) from pgbench_accounts where aid between 1000 and 90000000 ;
-- TestCase: 3
--Description
/*
Explain plan catching parallel index Only scan :
a) both columns are of integer Type.
b) 1 column is having index and another is non key column.
c) condition: The column having SAFE FUNCTION against Index column in WHERE condition.
*/
CREATE or replace function fun_pis (n int) returns int parallel safe as $$ begin return 1000; end; $$ language 'plpgsql';
explain analyze verbose select aid from pgbench_accounts where aid > fun_pis(9) and aid < 90000000 ;
-- TestCase: 4
--Description
/*
Explain plan catching Parallel index only scan when in WHERE condition
a) 3 columns, 1 column is having PRIMARY KEY on "int" Datatype and another non key columns having "int" and "char" datatype.
b) condition: WHERE clause having 3 conditions, index column is selecting more records as compaired to other column conditions.
-- Need to disable SEQUENTIAL to reproduce explain plan catches "parallel index Only scan"
*/
\c test
CREATE TABLE tt2(c1 serial primary key, c2 int, c3 char(10));
INSERT INTO tt2(c2, c3) VALUES (generate_series(1,30), 'abc');
INSERT INTO tt2(c2, c3) VALUES (generate_series(31,1000000), 'pqrs');
analyze tt2;
set enable_seqscan =0;
explain analyze select count(c1) from tt2 where c1 < 999900;
-- TestCase: 5
--Description
/*
Explain plan catching Parallel index only scan when in WHERE condition
a) 3 columns, 2 column is having composite index on "int" and "character" Datatype and another non key columns having "int" datatype.
b) condition: WHERE clause having 1 multi-column condition selecting few records.
-- Need to disable BITMAPSCAN, SEQUENTIALSCAN to reproduce explain plan catch "parallel index only scan"
*/
set enable_seqscan =0;
explain analyze verbose select count(*) from tt2 where (c1) NOT IN((100));
-- TestCase: 6
--Description
/*
Explain plan catching Parallel index only scan when in WHERE condition
a) 2 columns, 1 non-key column having "text" datatype and another column having "array of integer[]" Datatype having index.
b) condition: WHERE clause having 2 conditions, the array index column is selecting more records as compaired to other non key column condition.
*/
CREATE TABLE ary_tab (c1 text, c2 integer[]);
INSERT INTO ary_tab VALUES ('one', '{1,2,3}');
INSERT INTO ary_tab VALUES ('two', '{4,5,6}');
INSERT INTO ary_tab VALUES ('three', '{2,4,6}');
INSERT INTO ary_tab (select 'four', '{7,8,9,10}' from generate_series(1,50));
INSERT INTO ary_tab (select 'five', '{7,8,9,10}' from generate_series(1,1000000));
CREATE INDEX ary_idx on ary_tab (c2);
analyze;
explain analyze verbose select count(*) from ary_tab where ARRAY[7,8,9,10]=c2 ;
-- TestCase: 7
--Description
/*
Explain plan catching Parallel index only scan when in WHERE condition
a) 4 columns, 1 non-key column having "TEXT" datatype and others are "INTEGER", "FLOAT", "VARCHAR" column having "COMPOSIT INDEX", and the same "INTEGER" column have "INDEX".
b) condition: WHERE clause having 1 conditions, the index column is selecting more records.
-- Need to disable SEQUENTIALSCAN /BITMAP to reproduce explain plan catch "parallel index only scan"
*/
\c test
CREATE TABLE tst_pis(c1 int, c2 text, c3 float, c4 varchar(10));
INSERT INTO tst_pis (select x, 'c2_'||x, x/3,'c4_'||x from generate_series(1,1000000) x);
CREATE INDEX tst_cidx on tst_pis (c1,c3,c4);
CREATE INDEX tst_idx on tst_pis (c1);
set enable_seqscan =0;
set enable_bitmapscan = 0 ;
explain analyze verbose select count(1) from tst_pis where c1 > 100000;
-- TestCase: 8
--Description
/*
Explain plan catching Parallel index Only scan when in WHERE condition
a) 2 columns: "TEXT" and "VARCHAR"
b) 3 indexes: 1 composite index against both columns.
2 btree index, one is against "TEXT" datatype column and another against "VARCHAR" datatype column .
c) Query Selecting Aggregate Count for GROUP BY both columns with equality conditions.
*/
CREATE TABLE t1_pis (c1 text, c2 varchar(30));
INSERT INTO t1_pis (select substr(md5(random()::text),1,10), NULL from generate_series(1,1000000));
update t1_pis set c2 = c1;
INSERT INTO t1_pis(select substr(md5(random()::text),1,10), substr(md5(random()::text),1,10) from generate_series(1,2000000));
CREATE INDEX t1_idx12 on t1_pis(c1,c2);
CREATE INDEX t1_idx1 on t1_pis(c1);
CREATE INDEX t1_idx2 on t1_pis(c2);
analyze t1_pis;
set parallel_setup_cost=0;
set parallel_tuple_cost=0;
explain analyze verbose select count(1) from t1_pis group by c1, c2;
-- TestCase: 9
--Description
/*
Explain plan catching Parallel index Only scan:
a) 3 columns("date", "varchar", "float") having composite index.
b) 2 Non-Key columns.
composite index columns having hard-coded data('25-09-2015', 'xyz', 1.1)
c) Query Selecting with all composite index columns valid data.
-- Need to disable SEQUENTIALSCAN to produce "parallel index only scan".
*/
\c test
CREATE TABLE t2_pis(c1 int, c2 text, c3 date, c4 varchar(20), c5 float);
INSERT INTO t2_pis(select x, 'c2_'||x, to_date('25-09-2015','dd-mm-yyyy'), 'xyz',1.1 from generate_series(1,1000000) x);
CREATE INDEX t2_idx on t2_pis(c3, c4, c5);
analyze;
set enable_seqscan =0;
explain analyze verbose select count(*) from t2_pis where c3 = to_date('25-09-2015','dd-mm-yyyy') and c4 = 'xyz' and c5 = 1.1;
-- TestCase: 10
--Description
/*
Explain plan catching Parallel index only scan:
a) 3 columns("date", "varchar", "float") having composite index.
b) 2 Non-Key columns.
composite index columns having hard-coded data('25-09-2015', 'xyz', 1.1)
c) Query Selecting aggregate count, WHERE condition in 2 columns valid data from composite index of 3 columns.
*/
explain analyze verbose select count(*) from t2_pis where c3 = to_date('25-09-2015','dd-mm-yyyy') and c4 = 'xyz';
-- TestCase: 11
--Description
/*
Explain plan catching Parallel index only scan :
a) Table having 1 column is of INTEGER type having Index.
b) Query consisting of "CROSS JOIN" with same table as 3 different table alias.
-- Need to disable SEQUENTIALSCAN, PARALLEL_SETUP_COST and PARALLEL_TUPLE_COST to produce "Parallel Index Only Scan" in Normal table.
*/
\c test
CREATE TABLE t(n int);
INSERT INTO t select generate_series(1,5000000);
analyze t;
vacuum t;
CREATE INDEX cccc on t(n);
set enable_seqscan =0;
set parallel_setup_cost =0;
set parallel_tuple_cost =0;
explain analyze verbose SELECT * FROM t CROSS JOIN t as t1 cross join t as t2 where t1.n>=1 and t.n=1 and t2.n=1;
--TestCase: 12
--Description
/*
Explain plan catching Parallel index Only scan :
a) Table having 3 column is of INTEGER type having Index (composite)
b) All the columns are in where conditions
-- Need to disable SEQUENTIALSCAN to produce "Parallel Index only Scan"
*/
create table gv(n int,n1 int,n2 int);
insert into gv select (x%20),(x%10),(x%5) from generate_series(1,1000000)x;
create index fcc on gv(n,n1,n2);
analyze gv;
set enable_seqscan =0;
set enable_bitmapscan=0;
explain analyze verbose select count(*) from gv where n<23 and n1 <4 and n2<34;
--TestCase: 13
--Description
/*
Explain plan catching Parallel index Only scan :
a) Table having 3 column is of INTEGER type having Index (composite)
b) using 1=1 in where condition
-- Need to disable SEQUENTIALSCAN to produce "Parallel Index only Scan"
*/
explain analyze verbose select count(*) from gv where 1=1;
--TestCase: 14
--Description
/*
Explain plan catching Parallel index Only scan :
a) Table having 3 column is of INTEGER type having Index (composite)
b) NO where condition
-- Need to disable SEQUENTIALSCAN to produce "Parallel Index only Scan"
*/
explain analyze verbose select count(*) from gv;
--create database
--create database test;
-- Running script for pgbanch tables against a scaling factor of 1,000
--\! ./pgbench -i -s 1000 test > /tmp/my_logs 2>&1
--\c test
-- TestCase: 1
--Description
/*
Explain plan catching Parallel index Only scan when in WHERE condition
a) Single column of integer Type having index.
b) condition: where the column having index is trying to fetch NOT NULL
values("IS NOT NULL").
-- Need to disable SEQUENTIAL SCAN to reproduce explain plan catch
"parallel index Only scan"
*/
\c test
set enable_seqscan =0;
explain analyze verbose select count(*) from pgbench_accounts where aid is not
null;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Finalize Aggregate (cost=2368610.12..2368610.13 rows=1 width=8) (actual
time=24533.125..24533.125 rows=1 loops=1)
Output: count(*)
-> Gather (cost=2368609.90..2368610.11 rows=2 width=8) (actual
time=24532.825..24533.106 rows=3 loops=1)
Output: (PARTIAL count(*))
Workers Planned: 2
Workers Launched: 2
-> Partial Aggregate (cost=2367609.90..2367609.91 rows=1 width=8)
(actual time=24498.826..24498.826 rows=1 loops=3)
Output: PARTIAL count(*)
Worker 0: actual time=24473.300..24473.300 rows=1 loops=1
Worker 1: actual time=24492.327..24492.328 rows=1 loops=1
-> Parallel Index Only Scan using pgbench_accounts_pkey on
public.pgbench_accounts (cost=0.57..2263443.23 rows=41666667 width=0) (actual
time=0.458..15416.622 rows=33333333 loops=3)
Index Cond: (pgbench_accounts.aid IS NOT NULL)
Heap Fetches: 0
Worker 0: actual time=0.655..15461.906 rows=33089328
loops=1
Worker 1: actual time=0.216..15390.534 rows=33172060
loops=1
Planning time: 6.403 ms
Execution time: 24561.215 ms
(17 rows)
-- TestCase: 2
--Description
/*
Explain plan catching parallel index Only scan:
a) both columns are of integer Type.
b) 1 column is having index and another is non key column.
c) condition: where the column having BETWEEN .. AND .. condition both in
Index and Non-Index column in WHERE condition.
*/
explain analyze verbose select count(aid) from pgbench_accounts where aid
between 1000 and 90000000 ;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Finalize Aggregate (cost=2353930.04..2353930.04 rows=1 width=8) (actual
time=22605.912..22605.912 rows=1 loops=1)
Output: count(aid)
-> Gather (cost=2353929.82..2353930.03 rows=2 width=8) (actual
time=22605.497..22605.896 rows=3 loops=1)
Output: (PARTIAL count(aid))
Workers Planned: 2
Workers Launched: 2
-> Partial Aggregate (cost=2352929.82..2352929.83 rows=1 width=8)
(actual time=22546.976..22546.977 rows=1 loops=3)
Output: PARTIAL count(aid)
Worker 0: actual time=22517.885..22517.886 rows=1 loops=1
Worker 1: actual time=22517.932..22517.932 rows=1 loops=1
-> Parallel Index Only Scan using pgbench_accounts_pkey on
public.pgbench_accounts (cost=0.57..2259296.04 rows=37453514 width=4) (actual
time=0.177..13925.926 rows=29999667 loops=3)
Output: aid
Index Cond: ((pgbench_accounts.aid >= 1000) AND
(pgbench_accounts.aid <= 90000000))
Heap Fetches: 0
Worker 0: actual time=0.199..13901.718 rows=29965152
loops=1
Worker 1: actual time=0.279..13977.070 rows=29982954
loops=1
Planning time: 2.182 ms
Execution time: 22634.941 ms
(18 rows)
-- TestCase: 3
--Description
/*
Explain plan catching parallel index Only scan :
a) both columns are of integer Type.
b) 1 column is having index and another is non key column.
c) condition: The column having SAFE FUNCTION against Index column in WHERE
condition.
*/
CREATE or replace function fun_pis (n int) returns int parallel safe as $$
begin return 1000; end; $$ language 'plpgsql';
explain analyze verbose select aid from pgbench_accounts where aid >
fun_pis(9) and aid < 90000000 ;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Gather (cost=1000.57..14489027.51 rows=29963138 width=4) (actual
time=17.493..149965.469 rows=89998999 loops=1)
Output: aid
Workers Planned: 2
Workers Launched: 2
-> Parallel Index Only Scan using pgbench_accounts_pkey on
public.pgbench_accounts (cost=0.57..11491713.71 rows=12484641 width=4) (actual
time=6.807..118999.773 rows=29999666 loops=3)
Output: aid
Index Cond: (pgbench_accounts.aid < 90000000)
Filter: (pgbench_accounts.aid > fun_pis(9))
Rows Removed by Filter: 333
Heap Fetches: 0
Worker 0: actual time=2.096..138640.031 rows=34382406 loops=1
Worker 1: actual time=1.744..138174.642 rows=35810538 loops=1
Planning time: 0.796 ms
Execution time: 167594.090 ms
(14 rows)
-- TestCase: 4
--Description
/*
Explain plan catching Parallel index only scan when in WHERE condition
a) 3 columns, 1 column is having PRIMARY KEY on "int" Datatype and another
non key columns having "int" and "char" datatype.
b) condition: WHERE clause having 3 conditions, index column is selecting
more records as compaired to other column conditions.
-- Need to disable SEQUENTIAL to reproduce explain plan catches
"parallel index Only scan"
*/
\c test
CREATE TABLE tt2(c1 serial primary key, c2 int, c3 char(10));
INSERT INTO tt2(c2, c3) VALUES (generate_series(1,30), 'abc');
INSERT INTO tt2(c2, c3) VALUES (generate_series(31,1000000), 'pqrs');
analyze tt2;
set enable_seqscan =0;
explain analyze select count(c1) from tt2 where c1 < 999900;
QUERY
PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------
Finalize Aggregate (cost=33205.05..33205.06 rows=1 width=8) (actual
time=524.677..524.677 rows=1 loops=1)
-> Gather (cost=33204.94..33205.05 rows=1 width=8) (actual
time=524.309..524.663 rows=2 loops=1)
Workers Planned: 1
Workers Launched: 1
-> Partial Aggregate (cost=32204.94..32204.95 rows=1 width=8)
(actual time=497.990..497.991 rows=1 loops=2)
-> Parallel Index Only Scan using tt2_pkey on tt2
(cost=0.42..30734.49 rows=588179 width=4) (actual time=0.080..351.073
rows=499949 loops=2)
Index Cond: (c1 < 999900)
Heap Fetches: 549732
Planning time: 9.985 ms
Execution time: 540.945 ms
(10 rows)
-- TestCase: 5
--Description
/*
Explain plan catching Parallel index only scan when in WHERE condition
a) 3 columns, 2 column is having composite index on "int" and "character"
Datatype and another non key columns having "int" datatype.
b) condition: WHERE clause having 1 multi-column condition selecting few
records.
-- Need to disable BITMAPSCAN, SEQUENTIALSCAN to reproduce explain plan
catch "parallel index only scan"
*/
set enable_seqscan =0;
explain analyze verbose select count(*) from tt2 where (c1) NOT IN((100));
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
Finalize Aggregate (cost=32177.07..32177.08 rows=1 width=8) (actual
time=602.469..602.469 rows=1 loops=1)
Output: count(*)
-> Gather (cost=32176.95..32177.06 rows=1 width=8) (actual
time=601.755..602.458 rows=2 loops=1)
Output: (PARTIAL count(*))
Workers Planned: 1
Workers Launched: 1
-> Partial Aggregate (cost=31176.95..31176.96 rows=1 width=8)
(actual time=583.317..583.317 rows=1 loops=2)
Output: PARTIAL count(*)
Worker 0: actual time=565.224..565.225 rows=1 loops=1
-> Parallel Index Only Scan using tt2_pkey on public.tt2
(cost=0.42..29706.37 rows=588235 width=0) (actual time=0.145..435.400
rows=500000 loops=2)
Filter: (tt2.c1 <> 100)
Rows Removed by Filter: 0
Heap Fetches: 536644
Worker 0: actual time=0.238..423.161 rows=463356 loops=1
Planning time: 0.166 ms
Execution time: 620.225 ms
(16 rows)
-- TestCase: 6
--Description
/*
Explain plan catching Parallel index only scan when in WHERE condition
a) 2 columns, 1 non-key column having "text" datatype and another column
having "array of integer[]" Datatype having index.
b) condition: WHERE clause having 2 conditions, the array index column is
selecting more records as compaired to other non key column condition.
*/
CREATE TABLE ary_tab (c1 text, c2 integer[]);
INSERT INTO ary_tab VALUES ('one', '{1,2,3}');
INSERT INTO ary_tab VALUES ('two', '{4,5,6}');
INSERT INTO ary_tab VALUES ('three', '{2,4,6}');
INSERT INTO ary_tab (select 'four', '{7,8,9,10}' from generate_series(1,50));
INSERT INTO ary_tab (select 'five', '{7,8,9,10}' from
generate_series(1,1000000));
CREATE INDEX ary_idx on ary_tab (c2);
analyze;
explain analyze verbose select count(*) from ary_tab where ARRAY[7,8,9,10]=c2 ;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Finalize Aggregate (cost=51903.65..51903.66 rows=1 width=8) (actual
time=564.144..564.144 rows=1 loops=1)
Output: count(*)
-> Gather (cost=51903.43..51903.64 rows=2 width=8) (actual
time=563.881..564.126 rows=3 loops=1)
Output: (PARTIAL count(*))
Workers Planned: 2
Workers Launched: 2
-> Partial Aggregate (cost=50903.43..50903.44 rows=1 width=8)
(actual time=518.395..518.396 rows=1 loops=3)
Output: PARTIAL count(*)
Worker 0: actual time=493.272..493.272 rows=1 loops=1
Worker 1: actual time=499.073..499.074 rows=1 loops=1
-> Parallel Index Only Scan using ary_idx on public.ary_tab
(cost=0.42..49861.71 rows=416689 width=0) (actual time=0.847..427.639
rows=333350 loops=3)
Index Cond: (ary_tab.c2 = '{7,8,9,10}'::integer[])
Heap Fetches: 371137
Worker 0: actual time=1.281..409.149 rows=311920 loops=1
Worker 1: actual time=1.114..412.422 rows=316993 loops=1
Planning time: 0.982 ms
Execution time: 585.280 ms
(17 rows)
-- TestCase: 7
--Description
/*
Explain plan catching Parallel index only scan when in WHERE condition
a) 4 columns, 1 non-key column having "TEXT" datatype and others are
"INTEGER", "FLOAT", "VARCHAR" column having "COMPOSIT INDEX", and the same
"INTEGER" column have "INDEX".
b) condition: WHERE clause having 1 conditions, the index column is
selecting more records.
-- Need to disable SEQUENTIALSCAN /BITMAP to reproduce explain plan
catch "parallel index only scan"
*/
\c test
CREATE TABLE tst_pis(c1 int, c2 text, c3 float, c4 varchar(10));
INSERT INTO tst_pis (select x, 'c2_'||x, x/3,'c4_'||x from
generate_series(1,1000000) x);
CREATE INDEX tst_cidx on tst_pis (c1,c3,c4);
CREATE INDEX tst_idx on tst_pis (c1);
set enable_seqscan =0;
set enable_bitmapscan = 0 ;
explain analyze verbose select count(1) from tst_pis where c1 > 100000;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Finalize Aggregate (cost=42907.51..42907.52 rows=1 width=8) (actual
time=598.329..598.330 rows=1 loops=1)
Output: count(1)
-> Gather (cost=42907.40..42907.51 rows=1 width=8) (actual
time=597.823..598.310 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=576.860..576.861 rows=1 loops=2)
Output: PARTIAL count(1)
Worker 0: actual time=556.911..556.911 rows=1 loops=1
-> Parallel Index Only Scan using tst_idx on public.tst_pis
(cost=0.42..41417.20 rows=196078 width=0) (actual time=0.142..405.744
rows=450000 loops=2)
Index Cond: (tst_pis.c1 > 100000)
Heap Fetches: 493008
Worker 0: actual time=0.158..395.476 rows=406992 loops=1
Planning time: 2.120 ms
Execution time: 618.871 ms
(15 rows)
-- TestCase: 8
--Description
/*
Explain plan catching Parallel index Only scan when in WHERE condition
a) 2 columns: "TEXT" and "VARCHAR"
b) 3 indexes: 1 composite index against both columns.
2 btree index, one is against "TEXT" datatype column and
another against "VARCHAR" datatype column .
c) Query Selecting Aggregate Count for GROUP BY both columns with equality
conditions.
*/
CREATE TABLE t1_pis (c1 text, c2 varchar(30));
INSERT INTO t1_pis (select substr(md5(random()::text),1,10), NULL from
generate_series(1,1000000));
update t1_pis set c2 = c1;
INSERT INTO t1_pis(select substr(md5(random()::text),1,10),
substr(md5(random()::text),1,10) from generate_series(1,2000000));
CREATE INDEX t1_idx12 on t1_pis(c1,c2);
CREATE INDEX t1_idx1 on t1_pis(c1);
CREATE INDEX t1_idx2 on t1_pis(c2);
analyze t1_pis;
set parallel_setup_cost=0;
set parallel_tuple_cost=0;
explain analyze verbose select count(1) from t1_pis group by c1, c2;
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Finalize GroupAggregate (cost=206271.01..207471.01 rows=40000 width=118)
(actual time=20403.439..25318.956 rows=2999788 loops=1)
Output: count(1), c1, c2
Group Key: t1_pis.c1, t1_pis.c2
-> Sort (cost=206271.01..206471.01 rows=80000 width=118) (actual
time=20403.418..22187.965 rows=2999788 loops=1)
Output: c1, c2, (PARTIAL count(1))
Sort Key: t1_pis.c1, t1_pis.c2
Sort Method: external merge Disk: 123288kB
-> Gather (cost=0.43..194831.43 rows=80000 width=118) (actual
time=1.447..3724.887 rows=2999788 loops=1)
Output: c1, c2, (PARTIAL count(1))
Workers Planned: 2
Workers Launched: 2
-> Partial GroupAggregate (cost=0.43..194831.43 rows=40000
width=118) (actual time=0.428..5127.291 rows=999929 loops=3)
Output: c1, c2, PARTIAL count(1)
Group Key: t1_pis.c1, t1_pis.c2
Worker 0: actual time=0.197..5262.993 rows=988120 loops=1
Worker 1: actual time=0.341..9940.999 rows=1997255 loops=1
-> Parallel Index Only Scan using t1_idx12 on
public.t1_pis (cost=0.43..185056.43 rows=1250000 width=110) (actual
time=0.303..3400.177 rows=1000000 loops=3)
Output: c1, c2
Heap Fetches: 14413
Worker 0: actual time=0.136..3433.161 rows=988204
loops=1
Worker 1: actual time=0.275..6617.618 rows=1997383
loops=1
Planning time: 2.518 ms
Execution time: 26002.659 ms
(23 rows)
-- TestCase: 9
--Description
/*
Explain plan catching Parallel index Only scan:
a) 3 columns("date", "varchar", "float") having composite index.
b) 2 Non-Key columns.
composite index columns having hard-coded data('25-09-2015', 'xyz',
1.1)
c) Query Selecting with all composite index columns valid data.
-- Need to disable SEQUENTIALSCAN to produce "parallel index only scan".
*/
\c test
CREATE TABLE t2_pis(c1 int, c2 text, c3 date, c4 varchar(20), c5 float);
INSERT INTO t2_pis(select x, 'c2_'||x, to_date('25-09-2015','dd-mm-yyyy'),
'xyz',1.1 from generate_series(1,1000000) x);
CREATE INDEX t2_idx on t2_pis(c3, c4, c5);
analyze;
set enable_seqscan =0;
explain analyze verbose select count(*) from t2_pis where c3 =
to_date('25-09-2015','dd-mm-yyyy') and c4 = 'xyz' and c5 = 1.1;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Finalize Aggregate (cost=51126.48..51126.49 rows=1 width=8) (actual
time=503.849..503.849 rows=1 loops=1)
Output: count(*)
-> Gather (cost=51126.26..51126.47 rows=2 width=8) (actual
time=503.537..503.830 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=462.290..462.291 rows=1 loops=3)
Output: PARTIAL count(*)
Worker 0: actual time=435.264..435.264 rows=1 loops=1
Worker 1: actual time=450.412..450.413 rows=1 loops=1
-> Parallel Index Only Scan using t2_idx on public.t2_pis
(cost=0.43..49084.59 rows=416667 width=0) (actual time=0.388..366.897
rows=333333 loops=3)
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: 355221
Worker 0: actual time=0.408..345.959 rows=314505 loops=1
Worker 1: actual time=0.510..358.804 rows=330274 loops=1
Planning time: 2.202 ms
Execution time: 523.144 ms
(17 rows)
-- TestCase: 10
--Description
/*
Explain plan catching Parallel index only scan:
a) 3 columns("date", "varchar", "float") having composite index.
b) 2 Non-Key columns.
composite index columns having hard-coded data('25-09-2015', 'xyz',
1.1)
c) Query Selecting aggregate count, WHERE condition in 2 columns valid data
from composite index of 3 columns.
*/
explain analyze verbose select count(*) from t2_pis where c3 =
to_date('25-09-2015','dd-mm-yyyy') and c4 = 'xyz';
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
Finalize Aggregate (cost=48626.48..48626.49 rows=1 width=8) (actual
time=426.090..426.090 rows=1 loops=1)
Output: count(*)
-> Gather (cost=48626.26..48626.47 rows=2 width=8) (actual
time=425.446..426.075 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=393.574..393.574 rows=1 loops=3)
Output: PARTIAL count(*)
Worker 0: actual time=365.207..365.207 rows=1 loops=1
Worker 1: actual time=390.407..390.407 rows=1 loops=1
-> Parallel Index Only Scan using t2_idx on public.t2_pis
(cost=0.43..46584.59 rows=416667 width=0) (actual time=0.171..304.642
rows=333333 loops=3)
Index Cond: ((t2_pis.c3 = to_date('25-09-2015'::text,
'dd-mm-yyyy'::text)) AND (t2_pis.c4 = 'xyz'::text))
Heap Fetches: 390565
Worker 0: actual time=0.228..284.700 rows=295974 loops=1
Worker 1: actual time=0.212..301.570 rows=313461 loops=1
Planning time: 0.731 ms
Execution time: 450.988 ms
(17 rows)
-- TestCase: 11
--Description
/*
Explain plan catching Parallel index only scan :
a) Table having 1 column is of INTEGER type having Index.
b) Query consisting of "CROSS JOIN" with same table as 3 different table
alias.
-- Need to disable SEQUENTIALSCAN, PARALLEL_SETUP_COST and
PARALLEL_TUPLE_COST to produce "Parallel Index Only Scan" in Normal table.
*/
\c test
CREATE TABLE t(n int);
INSERT INTO t select generate_series(1,5000000);
analyze t;
vacuum t;
CREATE INDEX cccc on t(n);
set enable_seqscan =0;
set parallel_setup_cost =0;
set parallel_tuple_cost =0;
explain analyze verbose SELECT * FROM t CROSS JOIN t as t1 cross join t as
t2 where t1.n>=1 and t.n=1 and t2.n=1;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=1.30..163208.68 rows=5000000 width=12) (actual
time=4.082..6099.466 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.063..0.068
rows=1 loops=1)
Output: t.n, t2.n
-> Index Only Scan using cccc on public.t (cost=0.43..8.45 rows=1
width=4) (actual time=0.036..0.037 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..8.45 rows=1
width=4) (actual time=0.009..0.013 rows=1 loops=1)
Output: t2.n
Index Cond: (t2.n = 1)
Heap Fetches: 0
-> Gather (cost=0.43..113191.77 rows=5000000 width=4) (actual
time=4.015..3974.556 rows=5000000 loops=1)
Output: t1.n
Workers Planned: 2
Workers Launched: 2
-> Parallel Index Only Scan using cccc on public.t t1
(cost=0.43..113191.77 rows=2083333 width=4) (actual time=0.119..1055.079
rows=1666667 loops=3)
Output: t1.n
Index Cond: (t1.n >= 1)
Heap Fetches: 0
Worker 0: actual time=0.144..2246.801 rows=3531608 loops=1
Worker 1: actual time=0.147..909.576 rows=1458144 loops=1
Planning time: 2.886 ms
Execution time: 6966.238 ms
(24 rows)
--TestCase: 12
--Description
/*
Explain plan catching Parallel index Only scan :
a) Table having 3 column is of INTEGER type having Index (composite)
b) All the columns are in where conditions
-- Need to disable SEQUENTIALSCAN to produce "Parallel Index only Scan"
*/
create table gv(n int,n1 int,n2 int);
insert into gv select (x%20),(x%10),(x%5) from generate_series(1,1000000)x;
create index fcc on gv(n,n1,n2);
analyze gv;
set enable_seqscan =0;
set enable_bitmapscan=0;
explain analyze verbose select count(*) from gv where n<23 and n1 <4 and n2<34;
QUERY
PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------
Finalize Aggregate (cost=51592.35..51592.36 rows=1 width=8) (actual
time=379.136..379.137 rows=1 loops=1)
Output: count(*)
-> Gather (cost=51592.34..51592.35 rows=2 width=8) (actual
time=374.470..379.099 rows=3 loops=1)
Output: (PARTIAL count(*))
Workers Planned: 2
Workers Launched: 2
-> Partial Aggregate (cost=51592.34..51592.35 rows=1 width=8)
(actual time=351.470..351.470 rows=1 loops=3)
Output: PARTIAL count(*)
Worker 0: actual time=346.522..346.523 rows=1 loops=1
Worker 1: actual time=334.976..334.977 rows=1 loops=1
-> Parallel Index Only Scan using fcc on public.gv
(cost=0.42..51176.26 rows=166430 width=0) (actual time=0.153..298.697
rows=133333 loops=3)
Index Cond: ((gv.n < 23) AND (gv.n1 < 4) AND (gv.n2 < 34))
Heap Fetches: 143548
Worker 0: actual time=0.119..295.869 rows=120993 loops=1
Worker 1: actual time=0.180..287.607 rows=135459 loops=1
Planning time: 0.419 ms
Execution time: 396.660 ms
(17 rows)
--TestCase: 13
--Description
/*
Explain plan catching Parallel index Only scan :
a) Table having 3 column is of INTEGER type having Index (composite)
b) using 1=1 in where condition
-- Need to disable SEQUENTIALSCAN to produce "Parallel Index only Scan"
*/
explain analyze verbose select count(*) from gv where 1=1;
QUERY
PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------
Finalize Aggregate (cost=47224.39..47224.40 rows=1 width=8) (actual
time=473.826..473.827 rows=1 loops=1)
Output: count(*)
-> Gather (cost=47224.37..47224.38 rows=2 width=8) (actual
time=473.572..473.805 rows=3 loops=1)
Output: (PARTIAL count(*))
Workers Planned: 2
Workers Launched: 2
-> Partial Aggregate (cost=47224.37..47224.38 rows=1 width=8)
(actual time=445.312..445.312 rows=1 loops=3)
Output: PARTIAL count(*)
Worker 0: actual time=418.973..418.973 rows=1 loops=1
Worker 1: actual time=443.897..443.897 rows=1 loops=1
-> Parallel Index Only Scan using fcc on public.gv
(cost=0.42..46182.71 rows=416667 width=0) (actual time=0.057..347.041
rows=333333 loops=3)
Heap Fetches: 348174
Worker 0: actual time=0.044..327.531 rows=317898 loops=1
Worker 1: actual time=0.074..346.972 rows=333928 loops=1
Planning time: 0.193 ms
Execution time: 495.211 ms
(16 rows)
--TestCase: 14
--Description
/*
Explain plan catching Parallel index Only scan :
a) Table having 3 column is of INTEGER type having Index (composite)
b) NO where condition
-- Need to disable SEQUENTIALSCAN to produce "Parallel Index only Scan"
*/
explain analyze verbose select count(*) from gv;
QUERY
PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------
Finalize Aggregate (cost=47224.39..47224.40 rows=1 width=8) (actual
time=468.613..468.613 rows=1 loops=1)
Output: count(*)
-> Gather (cost=47224.37..47224.38 rows=2 width=8) (actual
time=468.327..468.470 rows=3 loops=1)
Output: (PARTIAL count(*))
Workers Planned: 2
Workers Launched: 2
-> Partial Aggregate (cost=47224.37..47224.38 rows=1 width=8)
(actual time=448.296..448.296 rows=1 loops=3)
Output: PARTIAL count(*)
Worker 0: actual time=429.941..429.941 rows=1 loops=1
Worker 1: actual time=447.108..447.108 rows=1 loops=1
-> Parallel Index Only Scan using fcc on public.gv
(cost=0.42..46182.71 rows=416667 width=0) (actual time=0.209..350.111
rows=333333 loops=3)
Heap Fetches: 353133
Worker 0: actual time=0.502..337.031 rows=312417 loops=1
Worker 1: actual time=0.052..349.202 rows=334450 loops=1
Planning time: 0.126 ms
Execution time: 489.217 ms
(16 rows)
:$
ERROR: syntax error at or near ":"
LINE 1: :$
^
pios.pdf
Description: Adobe PDF document
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
