| -- @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; |
| SET |
| |
| DROP TABLE IF EXISTS t1; |
| DROP |
| CREATE TABLE t1 (a INT) DISTRIBUTED by (a); |
| CREATE |
| insert into t1 select generate_series(1,100); |
| INSERT 100 |
| CREATE TABLE t2 (a INT) DISTRIBUTED RANDOMLY; |
| CREATE |
| insert into t2 select generate_series(1,100); |
| INSERT 100 |
| DROP TABLE IF EXISTS rt1; |
| DROP |
| CREATE TABLE rt1 (a INT) DISTRIBUTED REPLICATED; |
| CREATE |
| insert into rt1 select generate_series(1,100); |
| INSERT 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; |
| QUERY PLAN |
| ----------------------------------- |
| Seq Scan on t1 |
| Endpoint: on all 3 segments |
| (3 rows) |
| EXPLAIN (COSTS false) DECLARE c1 PARALLEL RETRIEVE CURSOR WITH HOLD FOR SELECT * FROM t1; |
| ERROR: DECLARE PARALLEL RETRIEVE CURSOR WITH HOLD ... is not supported |
| DETAIL: Holdable cursors can not be parallel |
| |
| EXPLAIN (COSTS false) DECLARE c1 NO SCROLL PARALLEL RETRIEVE CURSOR FOR SELECT * FROM t1; |
| QUERY PLAN |
| ----------------------------------- |
| Seq Scan on t1 |
| Endpoint: on all 3 segments |
| (3 rows) |
| EXPLAIN (COSTS false) DECLARE c1 SCROLL PARALLEL RETRIEVE CURSOR FOR SELECT * FROM t1; |
| ERROR: SCROLL is not allowed for the PARALLEL RETRIEVE CURSORs |
| DETAIL: Scrollable cursors can not be parallel |
| |
| -- 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; |
| QUERY PLAN |
| ----------------------------------- |
| Seq Scan on public.t1 |
| Output: a |
| Endpoint: on all 3 segments |
| (4 rows) |
| EXPLAIN (VERBOSE, COSTS false) DECLARE c1 PARALLEL RETRIEVE CURSOR FOR SELECT * FROM t1 ORDER BY a; |
| QUERY PLAN |
| ------------------------------------------ |
| Gather Motion 3:1 (slice1; segments: 3) |
| Output: a |
| Merge Key: a |
| -> Sort |
| Output: a |
| Sort Key: t1.a |
| -> Seq Scan on public.t1 |
| Output: a |
| Endpoint: "on coordinator" |
| Optimizer: Postgres query optimizer |
| (10 rows) |
| EXPLAIN (VERBOSE, COSTS false) DECLARE c1 PARALLEL RETRIEVE CURSOR FOR SELECT * FROM t1 WHERE a=1; |
| QUERY PLAN |
| -------------------------------------- |
| Seq Scan on public.t1 |
| Output: a |
| Filter: (t1.a = 1) |
| Endpoint: on segments: contentid [1] |
| Optimizer: Postgres query optimizer |
| (5 rows) |
| EXPLAIN (VERBOSE, COSTS false) DECLARE c1 PARALLEL RETRIEVE CURSOR FOR SELECT * FROM t1 WHERE a=1 OR a=2; |
| QUERY PLAN |
| ----------------------------------------- |
| Seq Scan on public.t1 |
| Output: a |
| Filter: ((t1.a = 1) OR (t1.a = 2)) |
| Endpoint: on segments: contentid [1, 0] |
| Optimizer: Postgres query optimizer |
| (5 rows) |
| |
| -- Test: Locus CdbLocusType_Strewn |
| EXPLAIN (VERBOSE, COSTS false) DECLARE c1 PARALLEL RETRIEVE CURSOR FOR SELECT * FROM t2; |
| QUERY PLAN |
| ----------------------------------- |
| Seq Scan on public.t2 |
| Output: a |
| Endpoint: on all 3 segments |
| (4 rows) |
| |
| -- Do some prepatation work in order to obtain correct `reltuples` number. |
| VACUUM pg_class; |
| VACUUM |
| |
| -- Test for system table which is accessible on coordinator |
| -- analyze pg_class to avoid generating different plan |
| VACUUM ANALYZE pg_class; |
| VACUUM |
| EXPLAIN (VERBOSE, COSTS false) DECLARE c1 CURSOR FOR SELECT * FROM pg_class; |
| QUERY PLAN |
| ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ |
| Seq Scan on pg_catalog.pg_class |
| Output: oid, relname, relnamespace, reltype, reloftype, relowner, relam, relfilenode, reltablespace, relpages, reltuples, relallvisible, reltoastrelid, relhasindex, relisshared, relpersistence, relkind, relnatts, relchecks, relhasrules, relhastriggers, relhassubclass, relrowsecurity, relforcerowsecurity, relispopulated, relreplident, relispartition, relisivm, relisdynamic, relmvrefcount, relrewrite, relfrozenxid, relminmxid, relacl, reloptions, relpartbound |
| Settings: enable_incremental_sort = 'on' |
| Optimizer: Postgres query optimizer |
| (4 rows) |
| |
| -- 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; |
| QUERY PLAN |
| ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ |
| Seq Scan on pg_catalog.pg_class |
| Output: oid, relname, relnamespace, reltype, reloftype, relowner, relam, relfilenode, reltablespace, relpages, reltuples, relallvisible, reltoastrelid, relhasindex, relisshared, relpersistence, relkind, relnatts, relchecks, relhasrules, relhastriggers, relhassubclass, relrowsecurity, relforcerowsecurity, relispopulated, relreplident, relispartition, relisivm, relisdynamic, relmvrefcount, relrewrite, relfrozenxid, relminmxid, relacl, reloptions, relpartbound |
| Settings: enable_incremental_sort = 'on' |
| Endpoint: "on coordinator" |
| Optimizer: Postgres query optimizer |
| (5 rows) |
| EXPLAIN (VERBOSE, COSTS false) DECLARE c1 PARALLEL RETRIEVE CURSOR FOR SELECT * FROM pg_class ORDER BY relname; |
| QUERY PLAN |
| ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ |
| Sort |
| Output: oid, relname, relnamespace, reltype, reloftype, relowner, relam, relfilenode, reltablespace, relpages, reltuples, relallvisible, reltoastrelid, relhasindex, relisshared, relpersistence, relkind, relnatts, relchecks, relhasrules, relhastriggers, relhassubclass, relrowsecurity, relforcerowsecurity, relispopulated, relreplident, relispartition, relisivm, relisdynamic, relmvrefcount, relrewrite, relfrozenxid, relminmxid, relacl, reloptions, relpartbound |
| Sort Key: pg_class.relname |
| -> Seq Scan on pg_catalog.pg_class |
| Output: oid, relname, relnamespace, reltype, reloftype, relowner, relam, relfilenode, reltablespace, relpages, reltuples, relallvisible, reltoastrelid, relhasindex, relisshared, relpersistence, relkind, relnatts, relchecks, relhasrules, relhastriggers, relhassubclass, relrowsecurity, relforcerowsecurity, relispopulated, relreplident, relispartition, relisivm, relisdynamic, relmvrefcount, relrewrite, relfrozenxid, relminmxid, relacl, reloptions, relpartbound |
| Settings: enable_incremental_sort = 'on' |
| Endpoint: "on coordinator" |
| Optimizer: Postgres query optimizer |
| (8 rows) |
| EXPLAIN (VERBOSE, COSTS false) DECLARE c1 PARALLEL RETRIEVE CURSOR FOR SELECT * FROM pg_class WHERE gp_segment_id=1; |
| QUERY PLAN |
| ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ |
| Seq Scan on pg_catalog.pg_class |
| Output: oid, relname, relnamespace, reltype, reloftype, relowner, relam, relfilenode, reltablespace, relpages, reltuples, relallvisible, reltoastrelid, relhasindex, relisshared, relpersistence, relkind, relnatts, relchecks, relhasrules, relhastriggers, relhassubclass, relrowsecurity, relforcerowsecurity, relispopulated, relreplident, relispartition, relisivm, relisdynamic, relmvrefcount, relrewrite, relfrozenxid, relminmxid, relacl, reloptions, relpartbound |
| Filter: (pg_class.gp_segment_id = 1) |
| Settings: enable_incremental_sort = 'on' |
| Endpoint: "on coordinator" |
| Optimizer: Postgres query optimizer |
| (6 rows) |
| EXPLAIN (VERBOSE, COSTS false) DECLARE c1 PARALLEL RETRIEVE CURSOR FOR SELECT * FROM pg_class WHERE gp_segment_id=1 OR gp_segment_id=2; |
| QUERY PLAN |
| ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ |
| Seq Scan on pg_catalog.pg_class |
| Output: oid, relname, relnamespace, reltype, reloftype, relowner, relam, relfilenode, reltablespace, relpages, reltuples, relallvisible, reltoastrelid, relhasindex, relisshared, relpersistence, relkind, relnatts, relchecks, relhasrules, relhastriggers, relhassubclass, relrowsecurity, relforcerowsecurity, relispopulated, relreplident, relispartition, relisivm, relisdynamic, relmvrefcount, relrewrite, relfrozenxid, relminmxid, relacl, reloptions, relpartbound |
| Filter: ((pg_class.gp_segment_id = 1) OR (pg_class.gp_segment_id = 2)) |
| Settings: enable_incremental_sort = 'on' |
| Endpoint: "on coordinator" |
| Optimizer: Postgres query optimizer |
| (6 rows) |
| |
| -- 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; |
| QUERY PLAN |
| ----------------------------------------------- |
| Function Scan on pg_catalog.generate_series f |
| Output: f |
| Function Call: generate_series(1, 10) |
| Endpoint: "on coordinator" |
| Optimizer: Postgres query optimizer |
| (5 rows) |
| EXPLAIN (VERBOSE, COSTS false) DECLARE c1 PARALLEL RETRIEVE CURSOR FOR SELECT * FROM generate_series(1,10) as F ORDER BY F; |
| QUERY PLAN |
| ----------------------------------------------------- |
| Sort |
| Output: f |
| Sort Key: f.f |
| -> Function Scan on pg_catalog.generate_series f |
| Output: f |
| Function Call: generate_series(1, 10) |
| Endpoint: "on coordinator" |
| Optimizer: Postgres query optimizer |
| (8 rows) |
| EXPLAIN (VERBOSE, COSTS false) DECLARE c1 PARALLEL RETRIEVE CURSOR FOR SELECT * FROM generate_series(1,10) as F WHERE F=1; |
| QUERY PLAN |
| ----------------------------------------------- |
| Function Scan on pg_catalog.generate_series f |
| Output: f |
| Function Call: generate_series(1, 10) |
| Filter: (f.f = 1) |
| Endpoint: "on coordinator" |
| Optimizer: Postgres query optimizer |
| (6 rows) |
| EXPLAIN (VERBOSE, COSTS false) DECLARE c1 PARALLEL RETRIEVE CURSOR FOR SELECT * FROM generate_series(1,10) as F WHERE F=1 OR F=2; |
| QUERY PLAN |
| ----------------------------------------------- |
| Function Scan on pg_catalog.generate_series f |
| Output: f |
| Function Call: generate_series(1, 10) |
| Filter: ((f.f = 1) OR (f.f = 2)) |
| Endpoint: "on coordinator" |
| Optimizer: Postgres query optimizer |
| (6 rows) |
| |
| -- 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; |
| QUERY PLAN |
| --------------------------------------- |
| Seq Scan on public.rt1 |
| Output: a |
| Endpoint: "on segment: contentid [SEGIDX]" |
| Optimizer: Postgres query optimizer |
| (4 rows) |
| 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; |
| QUERY PLAN |
| --------------------------------------- |
| Sort |
| Output: a |
| Sort Key: rt1.a |
| -> Seq Scan on public.rt1 |
| Output: a |
| Endpoint: "on segment: contentid [SEGIDX]" |
| Optimizer: Postgres query optimizer |
| (7 rows) |
| 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; |
| QUERY PLAN |
| --------------------------------------- |
| Seq Scan on public.rt1 |
| Output: a |
| Filter: (rt1.a = 1) |
| Endpoint: "on segment: contentid [SEGIDX]" |
| Optimizer: Postgres query optimizer |
| (5 rows) |
| 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; |
| QUERY PLAN |
| ---------------------------------------- |
| Seq Scan on public.rt1 |
| Output: a |
| Filter: ((rt1.a = 1) OR (rt1.a = 2)) |
| Endpoint: "on segment: contentid [SEGIDX]" |
| Optimizer: Postgres query optimizer |
| (5 rows) |
| |
