src/test/isolation2/input/parallel_retrieve_cursor/explain.source

-- @Description Tests "EXPLAIN" statement for the PARALLEL RETRIEVE CURSOR
-- 
-- TODO: incremental sort is turned off by default, because it may have
-- wrong result for some core case. Turn it on to run the existing tests
-- and minimize the difference from upstream.
-- start_matchignore
-- m/ Settings: enable_incremental_sort = 'on'/
-- end_matchignore
-- start_matchsubs
-- m/ *$/
-- s/ *$//
-- end_matchsubs
set enable_incremental_sort=on;

DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a INT) DISTRIBUTED by (a);
insert into t1 select generate_series(1,100);
CREATE TABLE t2 (a INT) DISTRIBUTED RANDOMLY;
insert into t2 select generate_series(1,100);
DROP TABLE IF EXISTS rt1;
CREATE TABLE rt1 (a INT) DISTRIBUTED REPLICATED;
insert into rt1 select generate_series(1,100);

-- PARALLEL RETRIEVE CURSOR with other options (WITH HOLD/SCROLL) is not supported
EXPLAIN (COSTS false) DECLARE c1 PARALLEL RETRIEVE CURSOR WITHOUT HOLD FOR SELECT * FROM t1;
EXPLAIN (COSTS false) DECLARE c1 PARALLEL RETRIEVE CURSOR WITH HOLD FOR SELECT * FROM t1;

EXPLAIN (COSTS false) DECLARE c1 NO SCROLL PARALLEL RETRIEVE CURSOR FOR SELECT * FROM t1;
EXPLAIN (COSTS false) DECLARE c1 SCROLL PARALLEL RETRIEVE CURSOR FOR SELECT * FROM t1;

-- Test: explain output: Endpoint info (on coordinator/on some segments/on all segments)
EXPLAIN (VERBOSE, COSTS false) DECLARE c1 PARALLEL RETRIEVE CURSOR FOR SELECT * FROM t1;
EXPLAIN (VERBOSE, COSTS false) DECLARE c1 PARALLEL RETRIEVE CURSOR FOR SELECT * FROM t1 ORDER BY a;
EXPLAIN (VERBOSE, COSTS false) DECLARE c1 PARALLEL RETRIEVE CURSOR FOR SELECT * FROM t1 WHERE a=1;
EXPLAIN (VERBOSE, COSTS false) DECLARE c1 PARALLEL RETRIEVE CURSOR FOR SELECT * FROM t1 WHERE a=1 OR a=2;

-- Test: Locus CdbLocusType_Strewn
EXPLAIN (VERBOSE, COSTS false) DECLARE c1 PARALLEL RETRIEVE CURSOR FOR SELECT * FROM t2;

-- Do some prepatation work in order to obtain correct `reltuples` number.
VACUUM pg_class;

-- Test for system table which is accessible on coordinator
-- analyze pg_class to avoid generating different plan
VACUUM ANALYZE pg_class;
EXPLAIN (VERBOSE, COSTS false) DECLARE c1 CURSOR FOR SELECT * FROM pg_class;

-- Test: explain output: Endpoint info (on coordinator/on some segments/on all segments)
EXPLAIN (VERBOSE, COSTS false) DECLARE c1 PARALLEL RETRIEVE CURSOR FOR SELECT * FROM pg_class;
EXPLAIN (VERBOSE, COSTS false) DECLARE c1 PARALLEL RETRIEVE CURSOR FOR SELECT * FROM pg_class ORDER BY relname;
EXPLAIN (VERBOSE, COSTS false) DECLARE c1 PARALLEL RETRIEVE CURSOR FOR SELECT * FROM pg_class WHERE gp_segment_id=1;
EXPLAIN (VERBOSE, COSTS false) DECLARE c1 PARALLEL RETRIEVE CURSOR FOR SELECT * FROM pg_class WHERE gp_segment_id=1 OR gp_segment_id=2;

-- Test for UDF which can be executed on coordinator
EXPLAIN (VERBOSE, COSTS false) DECLARE c1 PARALLEL RETRIEVE CURSOR FOR SELECT * FROM generate_series(1,10) as F;
EXPLAIN (VERBOSE, COSTS false) DECLARE c1 PARALLEL RETRIEVE CURSOR FOR SELECT * FROM generate_series(1,10) as F ORDER BY F;
EXPLAIN (VERBOSE, COSTS false) DECLARE c1 PARALLEL RETRIEVE CURSOR FOR SELECT * FROM generate_series(1,10) as F WHERE F=1;
EXPLAIN (VERBOSE, COSTS false) DECLARE c1 PARALLEL RETRIEVE CURSOR FOR SELECT * FROM generate_series(1,10) as F WHERE F=1 OR F=2;

-- Test: explain output: Endpoint info (on coordinator/on some segments/on all segments)
-- Here because replicated table will execute on seg id: session_id % segment_number
-- Just replace the random specific seg id to SEGIDX for the output
1: @post_run 'create_sub  "on segment: contentid \[[0-9]+\]" "on segment: contentid [SEGIDX]" ':EXPLAIN (VERBOSE, COSTS false) DECLARE c1 PARALLEL RETRIEVE CURSOR FOR SELECT * FROM rt1;
1: @post_run 'create_sub  "on segment: contentid \[[0-9]+\]" "on segment: contentid [SEGIDX]" ':EXPLAIN (VERBOSE, COSTS false) DECLARE c1 PARALLEL RETRIEVE CURSOR FOR SELECT * FROM rt1 ORDER BY a;
1: @post_run 'create_sub  "on segment: contentid \[[0-9]+\]" "on segment: contentid [SEGIDX]" ':EXPLAIN (VERBOSE, COSTS false) DECLARE c1 PARALLEL RETRIEVE CURSOR FOR SELECT * FROM rt1 WHERE a=1;
1: @post_run 'create_sub  "on segment: contentid \[[0-9]+\]" "on segment: contentid [SEGIDX]" ':EXPLAIN (VERBOSE, COSTS false) DECLARE c1 PARALLEL RETRIEVE CURSOR FOR SELECT * FROM rt1 WHERE a=1 OR a=2;