This is an automated email from the ASF dual-hosted git repository. maxyang pushed a commit to branch main in repository https://gitbox.apache.org/repos/asf/cloudberry.git
commit 63a4e168a6d58a7e0197ec480921253048a2dd00 Author: zhoujiaqi <[email protected]> AuthorDate: Thu Feb 13 18:36:19 2025 +0800 Fix build and icw tests "CLogical[Dynamic]IndexOnlyGet, Hash subplans, Update Index Scan Costing..." --- src/backend/gpopt/gpdbwrappers.cpp | 20 -- .../gpopt/translate/CTranslatorDXLToPlStmt.cpp | 4 +- src/backend/optimizer/plan/subselect.c | 1 - src/include/gpopt/gpdbwrappers.h | 4 - ...le_operations_should_not_deadlock_optimizer.out | 4 +- src/test/regress/expected/catcache.out | 2 + src/test/regress/expected/catcache_optimizer.out | 60 ++--- src/test/regress/expected/dpe_optimizer.out | 43 ++-- src/test/regress/expected/gp_covering_index.out | 36 --- .../expected/gp_covering_index_optimizer.out | 40 --- src/test/regress/expected/gp_index_costing.out | 100 ++++---- src/test/regress/expected/join_optimizer.out | 42 ++-- src/test/regress/expected/partition_pruning.out | 272 ++++++++++----------- src/test/regress/expected/qp_indexscan.out | 82 +++---- src/test/regress/expected/subselect_gp.out | 53 +++- .../regress/expected/subselect_gp_optimizer.out | 33 +++ src/test/regress/sql/catcache.sql | 3 +- src/test/regress/sql/gp_covering_index.sql | 21 -- 18 files changed, 372 insertions(+), 448 deletions(-) diff --git a/src/backend/gpopt/gpdbwrappers.cpp b/src/backend/gpopt/gpdbwrappers.cpp index 21ba90c189..8343d36ba8 100644 --- a/src/backend/gpopt/gpdbwrappers.cpp +++ b/src/backend/gpopt/gpdbwrappers.cpp @@ -2800,26 +2800,6 @@ gpdb::GetIndexAmRoutineFromAmHandler(Oid am_handler) GP_WRAP_END; } -PartitionDesc -gpdb::GPDBRelationRetrievePartitionDesc(Relation rel) -{ - GP_WRAP_START; - { - return RelationRetrievePartitionDesc(rel); - } - GP_WRAP_END; -} - -PartitionKey -gpdb::GPDBRelationRetrievePartitionKey(Relation rel) -{ - GP_WRAP_START; - { - return RelationRetrievePartitionKey(rel); - } - GP_WRAP_END; -} - bool gpdb::TestexprIsHashable(Node *testexpr, List *param_ids) { diff --git a/src/backend/gpopt/translate/CTranslatorDXLToPlStmt.cpp b/src/backend/gpopt/translate/CTranslatorDXLToPlStmt.cpp index 864d284e91..b6b8231ae2 100644 --- a/src/backend/gpopt/translate/CTranslatorDXLToPlStmt.cpp +++ b/src/backend/gpopt/translate/CTranslatorDXLToPlStmt.cpp @@ -5480,11 +5480,11 @@ update_unknown_locale_walker(Node *node, void *context) return gpdb::WalkExpressionTree( (Node *) query->targetList, - (bool (*)()) update_unknown_locale_walker, (void *) context); + (bool (*)(Node *, void *)) update_unknown_locale_walker, (void *) context); } return gpdb::WalkExpressionTree( - node, (bool (*)()) update_unknown_locale_walker, (void *) context); + node, (bool (*)(Node *, void *)) update_unknown_locale_walker, (void *) context); } //--------------------------------------------------------------------------- diff --git a/src/backend/optimizer/plan/subselect.c b/src/backend/optimizer/plan/subselect.c index 8dffb3310f..18f831ec31 100644 --- a/src/backend/optimizer/plan/subselect.c +++ b/src/backend/optimizer/plan/subselect.c @@ -91,7 +91,6 @@ static Node *convert_testexpr_mutator(Node *node, static bool subplan_is_hashable(Plan *plan); static bool subpath_is_hashable(Path *path); -static bool testexpr_is_hashable(Node *testexpr, List *param_ids); static bool test_opexpr_is_hashable(OpExpr *testexpr, List *param_ids); static bool hash_ok_operator(OpExpr *expr); #if 0 diff --git a/src/include/gpopt/gpdbwrappers.h b/src/include/gpopt/gpdbwrappers.h index 001f28fdae..6105174b50 100644 --- a/src/include/gpopt/gpdbwrappers.h +++ b/src/include/gpopt/gpdbwrappers.h @@ -682,10 +682,6 @@ char *GetRelAmName(Oid reloid); IndexAmRoutine *GetIndexAmRoutineFromAmHandler(Oid am_handler); -PartitionDesc GPDBRelationRetrievePartitionDesc(Relation rel); - -PartitionKey GPDBRelationRetrievePartitionKey(Relation rel); - bool TestexprIsHashable(Node *testexpr, List *param_ids); } //namespace gpdb diff --git a/src/test/isolation2/expected/concurrent_partition_table_operations_should_not_deadlock_optimizer.out b/src/test/isolation2/expected/concurrent_partition_table_operations_should_not_deadlock_optimizer.out index 0e6ce5ab8f..468811c505 100644 --- a/src/test/isolation2/expected/concurrent_partition_table_operations_should_not_deadlock_optimizer.out +++ b/src/test/isolation2/expected/concurrent_partition_table_operations_should_not_deadlock_optimizer.out @@ -159,10 +159,10 @@ VACUUM QUERY PLAN ---------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) - -> Dynamic Index Scan on idx on pt + -> Dynamic Index Only Scan on idx on pt Index Cond: (a < 4) Number of partitions to scan: 2 (out of 5) - Optimizer: Pivotal Optimizer (GPORCA) + Optimizer: GPORCA (5 rows) -- [ORCA] Fetch stats outside transaction so that we skip locking inside the transaction due to fetching stats. 1: SELECT a FROM pt WHERE a<4; diff --git a/src/test/regress/expected/catcache.out b/src/test/regress/expected/catcache.out index ccd8399b84..79c41c8fa6 100644 --- a/src/test/regress/expected/catcache.out +++ b/src/test/regress/expected/catcache.out @@ -1,6 +1,7 @@ -- Test abort transaction should invalidate reader gang's cat cache -- Discussion: https://groups.google.com/a/greenplum.org/g/gpdb-dev/c/u3-D7isdvmM set optimizer_force_multistage_agg = 1; +SET optimizer_trace_fallback = on; create table dml_14027_union_s (a int not null, b numeric default 10.00) distributed by (a) partition by range(b); create table dml_14027_union_s_1_prt_2 partition of dml_14027_union_s for values from (1) to (1001); NOTICE: table has parent, setting distribution columns to match parent table @@ -55,3 +56,4 @@ ERROR: null value in column "a" of relation "dml_14027_union_s_1_prt_2" violate DETAIL: Failing row contains (null, 1). drop table dml_14027_union_s; reset optimizer_force_multistage_agg; +reset optimizer_trace_fallback; diff --git a/src/test/regress/expected/catcache_optimizer.out b/src/test/regress/expected/catcache_optimizer.out index 44bd45a049..9358b27469 100644 --- a/src/test/regress/expected/catcache_optimizer.out +++ b/src/test/regress/expected/catcache_optimizer.out @@ -1,6 +1,7 @@ -- Test abort transaction should invalidate reader gang's cat cache -- Discussion: https://groups.google.com/a/greenplum.org/g/gpdb-dev/c/u3-D7isdvmM set optimizer_force_multistage_agg = 1; +SET optimizer_trace_fallback = on; create table dml_14027_union_s (a int not null, b numeric default 10.00) distributed by (a) partition by range(b); create table dml_14027_union_s_1_prt_2 partition of dml_14027_union_s for values from (1) to (1001); NOTICE: table has parent, setting distribution columns to match parent table @@ -36,45 +37,36 @@ select count(distinct(b)) from dml_14027_union_s; rollback; explain update dml_14027_union_s set a = (select null union select null)::numeric; - QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------------- - Update on dml_14027_union_s (cost=0.00..882689.40 rows=1 width=1) - -> Result (cost=0.00..0.00 rows=0 width=0) - -> Redistribute Motion 3:3 (slice1; segments: 3) (cost=0.00..882689.33 rows=2 width=26) - Hash Key: a - -> Split (cost=0.00..882689.33 rows=1 width=26) - -> Nested Loop Left Join (cost=0.00..882689.33 rows=1 width=30) - Join Filter: true - -> Dynamic Seq Scan on dml_14027_union_s (cost=0.00..431.00 rows=1 width=22) - Number of partitions to scan: 2 (out of 2) - -> Assert (cost=0.00..0.00 rows=1 width=8) - Assert Cond: ((row_number() OVER (?)) = 1) - -> Materialize (cost=0.00..0.00 rows=1 width=16) - -> Broadcast Motion 1:3 (slice2; segments: 1) (cost=0.00..0.00 rows=1 width=16) - -> WindowAgg (cost=0.00..0.00 rows=1 width=16) - -> Gather Motion 3:1 (slice3; segments: 3) (cost=0.00..0.00 rows=1 width=8) - -> GroupAggregate (cost=0.00..0.00 rows=1 width=8) - Group Key: (NULL::text) - -> Sort (cost=0.00..0.00 rows=1 width=8) - Sort Key: (NULL::text) - -> Redistribute Motion 3:3 (slice4; segments: 3) (cost=0.00..0.00 rows=1 width=8) - Hash Key: (NULL::text) - -> Streaming HashAggregate (cost=0.00..0.00 rows=1 width=8) - Group Key: (NULL::text) - -> Append (cost=0.00..0.00 rows=1 width=8) - -> Result (cost=0.00..0.00 rows=1 width=1) - One-Time Filter: (gp_execution_segment() = 0) - -> Result (cost=0.00..0.00 rows=1 width=1) - -> Result (cost=0.00..0.00 rows=1 width=1) - One-Time Filter: (gp_execution_segment() = 2) - -> Result (cost=0.00..0.00 rows=1 width=1) - Optimizer: GPORCA -(31 rows) +INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner +DETAIL: Falling back to Postgres-based planner because GPORCA does not support the following feature: DML(update) on partitioned tables + QUERY PLAN +------------------------------------------------------------------------------------------------------------------------ + Update on dml_14027_union_s (cost=0.07..2.19 rows=0 width=0) + Update on dml_14027_union_s_1_prt_2 dml_14027_union_s_1 + Update on dml_14027_union_s_1_prt_def dml_14027_union_s_2 + InitPlan 1 (returns $0) (slice2) + -> Unique (cost=0.06..0.07 rows=2 width=32) + Group Key: (NULL::text) + -> Sort (cost=0.06..0.07 rows=2 width=32) + Sort Key: (NULL::text) + -> Append (cost=0.00..0.05 rows=2 width=32) + -> Result (cost=0.00..0.01 rows=1 width=32) + -> Result (cost=0.00..0.01 rows=1 width=32) + -> Explicit Redistribute Motion 3:3 (slice1; segments: 3) (cost=0.00..2.12 rows=4 width=36) + -> Split (cost=0.00..2.04 rows=4 width=36) + -> Append (cost=0.00..2.04 rows=2 width=36) + -> Seq Scan on dml_14027_union_s_1_prt_2 dml_14027_union_s_1 (cost=0.00..1.02 rows=1 width=36) + -> Seq Scan on dml_14027_union_s_1_prt_def dml_14027_union_s_2 (cost=0.00..1.02 rows=1 width=36) + Optimizer: Postgres query optimizer +(17 rows) -- Should not raise error due to stale catcache in reader gang. -- eg: ERROR: expected partdefid 134733, but got 0 update dml_14027_union_s set a = (select null union select null)::numeric; +INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner +DETAIL: Falling back to Postgres-based planner because GPORCA does not support the following feature: DML(update) on partitioned tables ERROR: null value in column "a" of relation "dml_14027_union_s_1_prt_2" violates not-null constraint (seg0 127.0.0.1:7002 pid=1872432) DETAIL: Failing row contains (null, 1). drop table dml_14027_union_s; reset optimizer_force_multistage_agg; +reset optimizer_trace_fallback; diff --git a/src/test/regress/expected/dpe_optimizer.out b/src/test/regress/expected/dpe_optimizer.out index f23fc88772..a98b0f713f 100644 --- a/src/test/regress/expected/dpe_optimizer.out +++ b/src/test/regress/expected/dpe_optimizer.out @@ -264,31 +264,25 @@ select * from pt where ptid in (select tid from t where t1 = 'hello' || tid); (18 rows) explain (costs off, timing off, summary off, analyze) select ptid from pt where ptid in (select tid from t where t1 = 'hello' || tid) and pt1 = 'hello1'; - QUERY PLAN ------------------------------------------------------------------------------------------------- + QUERY PLAN +--------------------------------------------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) (actual rows=1 loops=1) - -> Hash Join (actual rows=1 loops=1) - Hash Cond: (t.tid = pt.ptid) - Extra Text: (seg1) Hash chain length 1.0 avg, 1 max, using 1 of 262144 buckets. - -> GroupAggregate (actual rows=2 loops=1) - Group Key: t.tid - -> Sort (actual rows=2 loops=1) - Sort Key: t.tid - Sort Method: quicksort Memory: 25kB - -> Redistribute Motion 3:3 (slice2; segments: 3) (actual rows=2 loops=1) - Hash Key: t.tid + -> Hash Semi Join (actual rows=1 loops=1) + Hash Cond: (pt.ptid = t.tid) + Extra Text: (seg1) Hash chain length 1.0 avg, 1 max, using 2 of 524288 buckets. + -> Dynamic Index Only Scan on ptid_pt1_idx on pt (actual rows=1 loops=1) + Index Cond: (pt1 = 'hello1'::text) + Heap Fetches: 0 + Number of partitions to scan: 6 (out of 6) + Partitions scanned: Avg 2.0 x 3 workers. Max 2 parts (seg0). + -> Hash (actual rows=2 loops=1) + Buckets: 524288 Batches: 1 Memory Usage: 4097kB + -> Partition Selector (selector id: $0) (actual rows=2 loops=1) + -> Broadcast Motion 3:3 (slice2; segments: 3) (actual rows=2 loops=1) -> Seq Scan on t (actual rows=2 loops=1) Filter: (t1 = ('hello'::text || (tid)::text)) - -> Hash (actual rows=1 loops=1) - Buckets: 262144 Batches: 1 Memory Usage: 2049kB - -> Redistribute Motion 3:3 (slice3; segments: 3) (actual rows=1 loops=1) - Hash Key: pt.ptid - -> Dynamic Index Scan on pt1_idx on pt (actual rows=1 loops=1) - Index Cond: (pt1 = 'hello1'::text) - Number of partitions to scan: 6 (out of 6) - Partitions scanned: Avg 6.0 x 3 workers. Max 6 parts (seg0). Optimizer: GPORCA -(22 rows) +(16 rows) select ptid from pt where ptid in (select tid from t where t1 = 'hello' || tid) and pt1 = 'hello1'; ptid @@ -370,12 +364,13 @@ explain (costs off, timing off, summary off, analyze) select count(*) from t, pt Join Filter: true -> Broadcast Motion 3:3 (slice2; segments: 3) (actual rows=2 loops=1) -> Seq Scan on t (actual rows=2 loops=1) - -> Dynamic Index Scan on ptid_idx on pt (actual rows=4 loops=2) + -> Dynamic Index Only Scan on ptid_idx on pt (actual rows=4 loops=2) Index Cond: (ptid = t.tid) + Heap Fetches: 0 Number of partitions to scan: 6 (out of 6) Partitions scanned: Avg 6.0 x 3 workers of 2 scans. Max 6 parts (seg0). - Optimizer: Pivotal Optimizer (GPORCA) -(12 rows) + Optimizer: GPORCA +(13 rows) select count(*) from t, pt where tid = ptid; count diff --git a/src/test/regress/expected/gp_covering_index.out b/src/test/regress/expected/gp_covering_index.out index adcc187fcc..97e496052a 100644 --- a/src/test/regress/expected/gp_covering_index.out +++ b/src/test/regress/expected/gp_covering_index.out @@ -627,24 +627,6 @@ EXPLAIN SELECT a FROM ao_pt WHERE a=29; Optimizer: Postgres query optimizer (6 rows) --- imitate child partition has GPDB 6 version file via catalog --- start_ignore -SET allow_system_table_mods=on; -UPDATE pg_appendonly SET version=1 WHERE relid='ao_pt_1_prt_3'::regclass; -RESET allow_system_table_mods; --- end_ignore --- Disallow if the table contains child partition with GPDB 6 version -EXPLAIN SELECT a FROM ao_pt WHERE a=29; - QUERY PLAN ----------------------------------------------------------------------------------------- - Gather Motion 1:1 (slice1; segments: 1) (cost=4.14..8.18 rows=1 width=8) - -> Bitmap Heap Scan on ao_pt_1_prt_3 ao_pt (cost=4.14..8.16 rows=1 width=8) - Recheck Cond: (a = 29) - -> Bitmap Index Scan on ao_pt_1_prt_3_a_idx (cost=0.00..4.14 rows=1 width=0) - Index Cond: (a = 29) - Optimizer: Postgres query optimizer -(6 rows) - DROP TABLE ao_pt; -- AO/CO partitioned table contains a non-AO leaf partition CREATE TABLE aocs_pt(a bigint) WITH (appendonly=true, orientation=column) PARTITION BY RANGE(a) @@ -670,24 +652,6 @@ EXPLAIN SELECT a FROM aocs_pt WHERE a=29; Optimizer: Postgres query optimizer (6 rows) --- imitate child partition has GPDB 6 version file via catalog --- start_ignore -SET allow_system_table_mods=on; -UPDATE pg_appendonly SET version=1 WHERE relid='aocs_pt_1_prt_3'::regclass; -RESET allow_system_table_mods; --- end_ignore --- Disallow if the table contains child partition with GPDB 6 version -EXPLAIN SELECT a FROM aocs_pt WHERE a=29; - QUERY PLAN ------------------------------------------------------------------------------------------- - Gather Motion 1:1 (slice1; segments: 1) (cost=4.14..8.18 rows=1 width=8) - -> Bitmap Heap Scan on aocs_pt_1_prt_3 aocs_pt (cost=4.14..8.16 rows=1 width=8) - Recheck Cond: (a = 29) - -> Bitmap Index Scan on aocs_pt_1_prt_3_a_idx (cost=0.00..4.14 rows=1 width=0) - Index Cond: (a = 29) - Optimizer: Postgres query optimizer -(6 rows) - DROP TABLE aocs_pt; -- Test various index types -- diff --git a/src/test/regress/expected/gp_covering_index_optimizer.out b/src/test/regress/expected/gp_covering_index_optimizer.out index a029ac5d7f..3a7135ba61 100644 --- a/src/test/regress/expected/gp_covering_index_optimizer.out +++ b/src/test/regress/expected/gp_covering_index_optimizer.out @@ -585,26 +585,6 @@ EXPLAIN SELECT a FROM ao_pt WHERE a=29; Optimizer: Pivotal Optimizer (GPORCA) (5 rows) --- imitate child partition has GPDB 6 version file via catalog --- start_ignore -SET allow_system_table_mods=on; -UPDATE pg_appendonly SET version=1 WHERE relid='ao_pt_1_prt_3'::regclass; -RESET allow_system_table_mods; --- end_ignore --- Disallow if the table contains child partition with GPDB 6 version -EXPLAIN SELECT a FROM ao_pt WHERE a=29; - QUERY PLAN ----------------------------------------------------------------------------------------- - Gather Motion 1:1 (slice1; segments: 1) (cost=0.00..387.97 rows=1 width=8) - -> Dynamic Bitmap Heap Scan on ao_pt (cost=0.00..387.97 rows=1 width=8) - Number of partitions to scan: 1 (out of 3) - Recheck Cond: (a = 29) - Filter: (a = 29) - -> Dynamic Bitmap Index Scan on idx_ao_pt_a (cost=0.00..0.00 rows=0 width=0) - Index Cond: (a = 29) - Optimizer: Pivotal Optimizer (GPORCA) -(8 rows) - DROP TABLE ao_pt; -- AO/CO partitioned table contains a non-AO leaf partition CREATE TABLE aocs_pt(a bigint) WITH (appendonly=true, orientation=column) PARTITION BY RANGE(a) @@ -629,26 +609,6 @@ EXPLAIN SELECT a FROM aocs_pt WHERE a=29; Optimizer: Pivotal Optimizer (GPORCA) (5 rows) --- imitate child partition has GPDB 6 version file via catalog --- start_ignore -SET allow_system_table_mods=on; -UPDATE pg_appendonly SET version=1 WHERE relid='aocs_pt_1_prt_3'::regclass; -RESET allow_system_table_mods; --- end_ignore --- Disallow if the table contains child partition with GPDB 6 version -EXPLAIN SELECT a FROM aocs_pt WHERE a=29; - QUERY PLAN ------------------------------------------------------------------------------------------- - Gather Motion 1:1 (slice1; segments: 1) (cost=0.00..387.97 rows=1 width=8) - -> Dynamic Bitmap Heap Scan on aocs_pt (cost=0.00..387.97 rows=1 width=8) - Number of partitions to scan: 1 (out of 3) - Recheck Cond: (a = 29) - Filter: (a = 29) - -> Dynamic Bitmap Index Scan on idx_aocs_pt_a (cost=0.00..0.00 rows=0 width=0) - Index Cond: (a = 29) - Optimizer: Pivotal Optimizer (GPORCA) -(8 rows) - DROP TABLE aocs_pt; -- Test various index types -- diff --git a/src/test/regress/expected/gp_index_costing.out b/src/test/regress/expected/gp_index_costing.out index 98127e45b7..617b206518 100644 --- a/src/test/regress/expected/gp_index_costing.out +++ b/src/test/regress/expected/gp_index_costing.out @@ -88,19 +88,19 @@ explain select * from bar_PT join foo on bar_PT.a =foo.a; ------------------------------------------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) (cost=1.04..7.23 rows=5 width=24) -> Hash Join (cost=1.04..7.16 rows=2 width=24) - Hash Cond: (bar_pt_1_prt_1.a = foo.a) + Hash Cond: (bar_pt.a = foo.a) -> Append (cost=0.00..6.09 rows=6 width=12) Partition Selectors: $0 - -> Seq Scan on bar_pt_1_prt_1 (cost=0.00..1.01 rows=1 width=12) - -> Seq Scan on bar_pt_1_prt_2 (cost=0.00..1.01 rows=1 width=12) - -> Seq Scan on bar_pt_1_prt_3 (cost=0.00..1.01 rows=1 width=12) - -> Seq Scan on bar_pt_1_prt_4 (cost=0.00..1.01 rows=1 width=12) - -> Seq Scan on bar_pt_1_prt_5 (cost=0.00..1.01 rows=1 width=12) - -> Seq Scan on bar_pt_1_prt_6 (cost=0.00..1.01 rows=1 width=12) + -> Seq Scan on bar_pt_1_prt_1 bar_pt_1 (cost=0.00..1.01 rows=1 width=12) + -> Seq Scan on bar_pt_1_prt_2 bar_pt_2 (cost=0.00..1.01 rows=1 width=12) + -> Seq Scan on bar_pt_1_prt_3 bar_pt_3 (cost=0.00..1.01 rows=1 width=12) + -> Seq Scan on bar_pt_1_prt_4 bar_pt_4 (cost=0.00..1.01 rows=1 width=12) + -> Seq Scan on bar_pt_1_prt_5 bar_pt_5 (cost=0.00..1.01 rows=1 width=12) + -> Seq Scan on bar_pt_1_prt_6 bar_pt_6 (cost=0.00..1.01 rows=1 width=12) -> Hash (cost=1.02..1.02 rows=2 width=12) -> Partition Selector (selector id: $0) (cost=0.00..1.02 rows=2 width=12) -> Seq Scan on foo (cost=0.00..1.02 rows=2 width=12) - Optimizer: Postgres-based planner + Optimizer: Postgres query optimizer (15 rows) -- Index idx_bar_PT_ba should be selected @@ -109,18 +109,18 @@ explain select * from bar_PT join foo on bar_PT.b =foo.b; -------------------------------------------------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) (cost=1.15..7.34 rows=5 width=24) -> Hash Join (cost=1.15..7.27 rows=2 width=24) - Hash Cond: (bar_pt_1_prt_1.b = foo.b) + Hash Cond: (bar_pt.b = foo.b) -> Append (cost=0.00..6.09 rows=6 width=12) - -> Seq Scan on bar_pt_1_prt_1 (cost=0.00..1.01 rows=1 width=12) - -> Seq Scan on bar_pt_1_prt_2 (cost=0.00..1.01 rows=1 width=12) - -> Seq Scan on bar_pt_1_prt_3 (cost=0.00..1.01 rows=1 width=12) - -> Seq Scan on bar_pt_1_prt_4 (cost=0.00..1.01 rows=1 width=12) - -> Seq Scan on bar_pt_1_prt_5 (cost=0.00..1.01 rows=1 width=12) - -> Seq Scan on bar_pt_1_prt_6 (cost=0.00..1.01 rows=1 width=12) + -> Seq Scan on bar_pt_1_prt_1 bar_pt_1 (cost=0.00..1.01 rows=1 width=12) + -> Seq Scan on bar_pt_1_prt_2 bar_pt_2 (cost=0.00..1.01 rows=1 width=12) + -> Seq Scan on bar_pt_1_prt_3 bar_pt_3 (cost=0.00..1.01 rows=1 width=12) + -> Seq Scan on bar_pt_1_prt_4 bar_pt_4 (cost=0.00..1.01 rows=1 width=12) + -> Seq Scan on bar_pt_1_prt_5 bar_pt_5 (cost=0.00..1.01 rows=1 width=12) + -> Seq Scan on bar_pt_1_prt_6 bar_pt_6 (cost=0.00..1.01 rows=1 width=12) -> Hash (cost=1.08..1.08 rows=5 width=12) -> Broadcast Motion 3:3 (slice2; segments: 3) (cost=0.00..1.08 rows=5 width=12) -> Seq Scan on foo (cost=0.00..1.02 rows=2 width=12) - Optimizer: Postgres-based planner + Optimizer: Postgres query optimizer (14 rows) drop index idx_bar_PT_ab; @@ -135,19 +135,19 @@ explain select * from bar_PT join foo on bar_PT.a =foo.a; ------------------------------------------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) (cost=1.04..7.23 rows=5 width=24) -> Hash Join (cost=1.04..7.16 rows=2 width=24) - Hash Cond: (bar_pt_1_prt_1.a = foo.a) + Hash Cond: (bar_pt.a = foo.a) -> Append (cost=0.00..6.09 rows=6 width=12) Partition Selectors: $0 - -> Seq Scan on bar_pt_1_prt_1 (cost=0.00..1.01 rows=1 width=12) - -> Seq Scan on bar_pt_1_prt_2 (cost=0.00..1.01 rows=1 width=12) - -> Seq Scan on bar_pt_1_prt_3 (cost=0.00..1.01 rows=1 width=12) - -> Seq Scan on bar_pt_1_prt_4 (cost=0.00..1.01 rows=1 width=12) - -> Seq Scan on bar_pt_1_prt_5 (cost=0.00..1.01 rows=1 width=12) - -> Seq Scan on bar_pt_1_prt_6 (cost=0.00..1.01 rows=1 width=12) + -> Seq Scan on bar_pt_1_prt_1 bar_pt_1 (cost=0.00..1.01 rows=1 width=12) + -> Seq Scan on bar_pt_1_prt_2 bar_pt_2 (cost=0.00..1.01 rows=1 width=12) + -> Seq Scan on bar_pt_1_prt_3 bar_pt_3 (cost=0.00..1.01 rows=1 width=12) + -> Seq Scan on bar_pt_1_prt_4 bar_pt_4 (cost=0.00..1.01 rows=1 width=12) + -> Seq Scan on bar_pt_1_prt_5 bar_pt_5 (cost=0.00..1.01 rows=1 width=12) + -> Seq Scan on bar_pt_1_prt_6 bar_pt_6 (cost=0.00..1.01 rows=1 width=12) -> Hash (cost=1.02..1.02 rows=2 width=12) -> Partition Selector (selector id: $0) (cost=0.00..1.02 rows=2 width=12) -> Seq Scan on foo (cost=0.00..1.02 rows=2 width=12) - Optimizer: Postgres-based planner + Optimizer: Postgres query optimizer (15 rows) -- Index idx_bar_PT_cba should be selected @@ -156,18 +156,18 @@ explain select * from bar_PT join foo on bar_PT.c =foo.c; -------------------------------------------------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) (cost=1.15..7.34 rows=5 width=24) -> Hash Join (cost=1.15..7.27 rows=2 width=24) - Hash Cond: (bar_pt_1_prt_1.c = foo.c) + Hash Cond: (bar_pt.c = foo.c) -> Append (cost=0.00..6.09 rows=6 width=12) - -> Seq Scan on bar_pt_1_prt_1 (cost=0.00..1.01 rows=1 width=12) - -> Seq Scan on bar_pt_1_prt_2 (cost=0.00..1.01 rows=1 width=12) - -> Seq Scan on bar_pt_1_prt_3 (cost=0.00..1.01 rows=1 width=12) - -> Seq Scan on bar_pt_1_prt_4 (cost=0.00..1.01 rows=1 width=12) - -> Seq Scan on bar_pt_1_prt_5 (cost=0.00..1.01 rows=1 width=12) - -> Seq Scan on bar_pt_1_prt_6 (cost=0.00..1.01 rows=1 width=12) + -> Seq Scan on bar_pt_1_prt_1 bar_pt_1 (cost=0.00..1.01 rows=1 width=12) + -> Seq Scan on bar_pt_1_prt_2 bar_pt_2 (cost=0.00..1.01 rows=1 width=12) + -> Seq Scan on bar_pt_1_prt_3 bar_pt_3 (cost=0.00..1.01 rows=1 width=12) + -> Seq Scan on bar_pt_1_prt_4 bar_pt_4 (cost=0.00..1.01 rows=1 width=12) + -> Seq Scan on bar_pt_1_prt_5 bar_pt_5 (cost=0.00..1.01 rows=1 width=12) + -> Seq Scan on bar_pt_1_prt_6 bar_pt_6 (cost=0.00..1.01 rows=1 width=12) -> Hash (cost=1.08..1.08 rows=5 width=12) -> Broadcast Motion 3:3 (slice2; segments: 3) (cost=0.00..1.08 rows=5 width=12) -> Seq Scan on foo (cost=0.00..1.02 rows=2 width=12) - Optimizer: Postgres-based planner + Optimizer: Postgres query optimizer (14 rows) drop table if exists foo; @@ -228,40 +228,40 @@ explain select * from bar_PT join foo on bar_PT.a =foo.a ; ------------------------------------------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) (cost=1.04..7.23 rows=5 width=24) -> Hash Join (cost=1.04..7.16 rows=2 width=24) - Hash Cond: (bar_pt_1_prt_1.a = foo.a) + Hash Cond: (bar_pt.a = foo.a) -> Append (cost=0.00..6.09 rows=6 width=12) Partition Selectors: $0 - -> Seq Scan on bar_pt_1_prt_1 (cost=0.00..1.01 rows=1 width=12) - -> Seq Scan on bar_pt_1_prt_2 (cost=0.00..1.01 rows=1 width=12) - -> Seq Scan on bar_pt_1_prt_3 (cost=0.00..1.01 rows=1 width=12) - -> Seq Scan on bar_pt_1_prt_4 (cost=0.00..1.01 rows=1 width=12) - -> Seq Scan on bar_pt_1_prt_5 (cost=0.00..1.01 rows=1 width=12) - -> Seq Scan on bar_pt_1_prt_6 (cost=0.00..1.01 rows=1 width=12) + -> Seq Scan on bar_pt_1_prt_1 bar_pt_1 (cost=0.00..1.01 rows=1 width=12) + -> Seq Scan on bar_pt_1_prt_2 bar_pt_2 (cost=0.00..1.01 rows=1 width=12) + -> Seq Scan on bar_pt_1_prt_3 bar_pt_3 (cost=0.00..1.01 rows=1 width=12) + -> Seq Scan on bar_pt_1_prt_4 bar_pt_4 (cost=0.00..1.01 rows=1 width=12) + -> Seq Scan on bar_pt_1_prt_5 bar_pt_5 (cost=0.00..1.01 rows=1 width=12) + -> Seq Scan on bar_pt_1_prt_6 bar_pt_6 (cost=0.00..1.01 rows=1 width=12) -> Hash (cost=1.02..1.02 rows=2 width=12) -> Partition Selector (selector id: $0) (cost=0.00..1.02 rows=2 width=12) -> Seq Scan on foo (cost=0.00..1.02 rows=2 width=12) - Optimizer: Postgres-based planner + Optimizer: Postgres query optimizer (15 rows) -- Query2 : Index idx_bar_PT_abc should be selected. explain select * from bar_PT join foo on bar_PT.a =foo.a and bar_PT.b =foo.b and bar_PT.c =foo.c; - QUERY PLAN ---------------------------------------------------------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) (cost=1.05..7.25 rows=3 width=24) -> Hash Join (cost=1.05..7.20 rows=1 width=24) - Hash Cond: ((bar_pt_1_prt_1.a = foo.a) AND (bar_pt_1_prt_1.b = foo.b) AND (bar_pt_1_prt_1.c = foo.c)) + Hash Cond: ((bar_pt.a = foo.a) AND (bar_pt.b = foo.b) AND (bar_pt.c = foo.c)) -> Append (cost=0.00..6.09 rows=6 width=12) Partition Selectors: $0 - -> Seq Scan on bar_pt_1_prt_1 (cost=0.00..1.01 rows=1 width=12) - -> Seq Scan on bar_pt_1_prt_2 (cost=0.00..1.01 rows=1 width=12) - -> Seq Scan on bar_pt_1_prt_3 (cost=0.00..1.01 rows=1 width=12) - -> Seq Scan on bar_pt_1_prt_4 (cost=0.00..1.01 rows=1 width=12) - -> Seq Scan on bar_pt_1_prt_5 (cost=0.00..1.01 rows=1 width=12) - -> Seq Scan on bar_pt_1_prt_6 (cost=0.00..1.01 rows=1 width=12) + -> Seq Scan on bar_pt_1_prt_1 bar_pt_1 (cost=0.00..1.01 rows=1 width=12) + -> Seq Scan on bar_pt_1_prt_2 bar_pt_2 (cost=0.00..1.01 rows=1 width=12) + -> Seq Scan on bar_pt_1_prt_3 bar_pt_3 (cost=0.00..1.01 rows=1 width=12) + -> Seq Scan on bar_pt_1_prt_4 bar_pt_4 (cost=0.00..1.01 rows=1 width=12) + -> Seq Scan on bar_pt_1_prt_5 bar_pt_5 (cost=0.00..1.01 rows=1 width=12) + -> Seq Scan on bar_pt_1_prt_6 bar_pt_6 (cost=0.00..1.01 rows=1 width=12) -> Hash (cost=1.02..1.02 rows=2 width=12) -> Partition Selector (selector id: $0) (cost=0.00..1.02 rows=2 width=12) -> Seq Scan on foo (cost=0.00..1.02 rows=2 width=12) - Optimizer: Postgres-based planner + Optimizer: Postgres query optimizer (15 rows) drop table if exists foo; diff --git a/src/test/regress/expected/join_optimizer.out b/src/test/regress/expected/join_optimizer.out index 22bda0940c..ea9a67b714 100644 --- a/src/test/regress/expected/join_optimizer.out +++ b/src/test/regress/expected/join_optimizer.out @@ -3698,27 +3698,23 @@ where x = unique1; ----------------------------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) Output: unique1, (1), (random()) - -> Hash Join + -> Nested Loop Output: unique1, (1), (random()) - Hash Cond: ((1) = tenk1.unique1) - -> Result - Output: random(), (1) - One-Time Filter: (gp_execution_segment() = 2) + Join Filter: true + -> Redistribute Motion 1:3 (slice2) + Output: (1), (random()) + Hash Key: (1) -> Result - Output: (1) + Output: (1), random() Filter: ((1) = 1) -> Result Output: 1 - -> Hash + -> Index Scan using tenk1_unique1 on public.tenk1 Output: unique1 - -> Broadcast Motion 3:3 (slice2; segments: 3) - Output: unique1 - -> Index Only Scan using tenk1_unique1 on public.tenk1 - Output: unique1 - Index Cond: (tenk1.unique1 = 1) + Index Cond: ((tenk1.unique1 = (1)) AND (tenk1.unique1 = 1)) Settings: enable_nestloop = 'on', enable_parallel = 'off', optimizer = 'on' - Optimizer: Pivotal Optimizer (GPORCA) -(22 rows) + Optimizer: GPORCA +(18 rows) explain (costs off) select unique1 from tenk1, f_immutable_int4(1) x where x = unique1; @@ -3783,16 +3779,16 @@ select unique1, x from tenk1 left join f_immutable_int4(1) x on unique1 = x; explain (costs off) select unique1, x from tenk1 right join f_immutable_int4(1) x on unique1 = x; - QUERY PLAN ----------------------------------------------------------- - Hash Right Join - Hash Cond: (unique1 = (1)) - -> Gather Motion 1:1 (slice1; segments: 1) - -> Index Only Scan using tenk1_unique1 on tenk1 - Index Cond: (unique1 = 1) - -> Hash + QUERY PLAN +--------------------------------------------------------------- + Gather Motion 3:1 (slice1; segments: 3) + -> Nested Loop Left Join + Join Filter: true -> Result - Optimizer: Pivotal Optimizer (GPORCA) + -> Result + -> Index Scan using tenk1_unique1 on tenk1 + Index Cond: ((unique1 = (1)) AND (unique1 = 1)) + Optimizer: GPORCA (8 rows) explain (costs off) diff --git a/src/test/regress/expected/partition_pruning.out b/src/test/regress/expected/partition_pruning.out index 25db296802..5cd238a917 100644 --- a/src/test/regress/expected/partition_pruning.out +++ b/src/test/regress/expected/partition_pruning.out @@ -3166,15 +3166,15 @@ SELECT * FROM pt_bool_tab WHERE col2 IS NOT NULL; (5 rows) EXPLAIN SELECT * FROM pt_bool_tab WHERE (not col2) IS true; - QUERY PLAN ------------------------------------------------------------------------------------------------------------ + QUERY PLAN +------------------------------------------------------------------------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) (cost=10000000000.00..20000002093.83 rows=93500 width=5) -> Append (cost=10000000000.00..20000000847.17 rows=31167 width=5) - -> Seq Scan on pt_bool_tab_1_prt_part2 (cost=10000000000.00..10000000345.67 rows=15583 width=5) + -> Seq Scan on pt_bool_tab_1_prt_part2 pt_bool_tab_1 (cost=10000000000.00..10000000345.67 rows=15583 width=5) Filter: ((NOT col2) IS TRUE) - -> Seq Scan on pt_bool_tab_1_prt_part1 (cost=10000000000.00..10000000345.67 rows=15583 width=5) + -> Seq Scan on pt_bool_tab_1_prt_part1 pt_bool_tab_2 (cost=10000000000.00..10000000345.67 rows=15583 width=5) Filter: ((NOT col2) IS TRUE) - Optimizer: Postgres-based planner + Optimizer: Postgres query optimizer (7 rows) SELECT * FROM pt_bool_tab WHERE (not col2) IS true; @@ -3185,15 +3185,15 @@ SELECT * FROM pt_bool_tab WHERE (not col2) IS true; (2 rows) EXPLAIN SELECT * FROM pt_bool_tab WHERE (not col2) IS false; - QUERY PLAN ------------------------------------------------------------------------------------------------------------ + QUERY PLAN +------------------------------------------------------------------------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) (cost=10000000000.00..20000002093.83 rows=93500 width=5) -> Append (cost=10000000000.00..20000000847.17 rows=31167 width=5) - -> Seq Scan on pt_bool_tab_1_prt_part2 (cost=10000000000.00..10000000345.67 rows=15583 width=5) + -> Seq Scan on pt_bool_tab_1_prt_part2 pt_bool_tab_1 (cost=10000000000.00..10000000345.67 rows=15583 width=5) Filter: ((NOT col2) IS FALSE) - -> Seq Scan on pt_bool_tab_1_prt_part1 (cost=10000000000.00..10000000345.67 rows=15583 width=5) + -> Seq Scan on pt_bool_tab_1_prt_part1 pt_bool_tab_2 (cost=10000000000.00..10000000345.67 rows=15583 width=5) Filter: ((NOT col2) IS FALSE) - Optimizer: Postgres-based planner + Optimizer: Postgres query optimizer (7 rows) SELECT * FROM pt_bool_tab WHERE (not col2) IS false; @@ -3205,15 +3205,15 @@ SELECT * FROM pt_bool_tab WHERE (not col2) IS false; (3 rows) EXPLAIN SELECT * FROM pt_bool_tab WHERE (not col2) IS NULL; - QUERY PLAN --------------------------------------------------------------------------------------------------------- + QUERY PLAN +---------------------------------------------------------------------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) (cost=10000000000.00..20000000694.14 rows=187 width=5) -> Append (cost=10000000000.00..20000000691.65 rows=62 width=5) - -> Seq Scan on pt_bool_tab_1_prt_part2 (cost=10000000000.00..10000000345.67 rows=31 width=5) + -> Seq Scan on pt_bool_tab_1_prt_part2 pt_bool_tab_1 (cost=10000000000.00..10000000345.67 rows=31 width=5) Filter: ((NOT col2) IS NULL) - -> Seq Scan on pt_bool_tab_1_prt_part1 (cost=10000000000.00..10000000345.67 rows=31 width=5) + -> Seq Scan on pt_bool_tab_1_prt_part1 pt_bool_tab_2 (cost=10000000000.00..10000000345.67 rows=31 width=5) Filter: ((NOT col2) IS NULL) - Optimizer: Postgres-based planner + Optimizer: Postgres query optimizer (7 rows) SELECT * FROM pt_bool_tab WHERE (not col2) IS NULL; @@ -3222,15 +3222,15 @@ SELECT * FROM pt_bool_tab WHERE (not col2) IS NULL; (0 rows) EXPLAIN SELECT * FROM pt_bool_tab WHERE (not col2) IS unknown; - QUERY PLAN --------------------------------------------------------------------------------------------------------- + QUERY PLAN +---------------------------------------------------------------------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) (cost=10000000000.00..20000000694.14 rows=187 width=5) -> Append (cost=10000000000.00..20000000691.65 rows=62 width=5) - -> Seq Scan on pt_bool_tab_1_prt_part2 (cost=10000000000.00..10000000345.67 rows=31 width=5) + -> Seq Scan on pt_bool_tab_1_prt_part2 pt_bool_tab_1 (cost=10000000000.00..10000000345.67 rows=31 width=5) Filter: ((NOT col2) IS UNKNOWN) - -> Seq Scan on pt_bool_tab_1_prt_part1 (cost=10000000000.00..10000000345.67 rows=31 width=5) + -> Seq Scan on pt_bool_tab_1_prt_part1 pt_bool_tab_2 (cost=10000000000.00..10000000345.67 rows=31 width=5) Filter: ((NOT col2) IS UNKNOWN) - Optimizer: Postgres-based planner + Optimizer: Postgres query optimizer (7 rows) SELECT * FROM pt_bool_tab WHERE (not col2) IS unknown; @@ -3239,15 +3239,15 @@ SELECT * FROM pt_bool_tab WHERE (not col2) IS unknown; (0 rows) EXPLAIN SELECT * FROM pt_bool_tab WHERE (not col2) IS NOT true; - QUERY PLAN ------------------------------------------------------------------------------------------------------------ + QUERY PLAN +------------------------------------------------------------------------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) (cost=10000000000.00..20000002093.83 rows=93500 width=5) -> Append (cost=10000000000.00..20000000847.17 rows=31167 width=5) - -> Seq Scan on pt_bool_tab_1_prt_part2 (cost=10000000000.00..10000000345.67 rows=15583 width=5) + -> Seq Scan on pt_bool_tab_1_prt_part2 pt_bool_tab_1 (cost=10000000000.00..10000000345.67 rows=15583 width=5) Filter: ((NOT col2) IS NOT TRUE) - -> Seq Scan on pt_bool_tab_1_prt_part1 (cost=10000000000.00..10000000345.67 rows=15583 width=5) + -> Seq Scan on pt_bool_tab_1_prt_part1 pt_bool_tab_2 (cost=10000000000.00..10000000345.67 rows=15583 width=5) Filter: ((NOT col2) IS NOT TRUE) - Optimizer: Postgres-based planner + Optimizer: Postgres query optimizer (7 rows) SELECT * FROM pt_bool_tab WHERE (not col2) IS NOT true; @@ -3259,15 +3259,15 @@ SELECT * FROM pt_bool_tab WHERE (not col2) IS NOT true; (3 rows) EXPLAIN SELECT * FROM pt_bool_tab WHERE (not col2) IS NOT false; - QUERY PLAN ------------------------------------------------------------------------------------------------------------ + QUERY PLAN +------------------------------------------------------------------------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) (cost=10000000000.00..20000002093.83 rows=93500 width=5) -> Append (cost=10000000000.00..20000000847.17 rows=31167 width=5) - -> Seq Scan on pt_bool_tab_1_prt_part2 (cost=10000000000.00..10000000345.67 rows=15583 width=5) + -> Seq Scan on pt_bool_tab_1_prt_part2 pt_bool_tab_1 (cost=10000000000.00..10000000345.67 rows=15583 width=5) Filter: ((NOT col2) IS NOT FALSE) - -> Seq Scan on pt_bool_tab_1_prt_part1 (cost=10000000000.00..10000000345.67 rows=15583 width=5) + -> Seq Scan on pt_bool_tab_1_prt_part1 pt_bool_tab_2 (cost=10000000000.00..10000000345.67 rows=15583 width=5) Filter: ((NOT col2) IS NOT FALSE) - Optimizer: Postgres-based planner + Optimizer: Postgres query optimizer (7 rows) SELECT * FROM pt_bool_tab WHERE (not col2) IS NOT false; @@ -3278,15 +3278,15 @@ SELECT * FROM pt_bool_tab WHERE (not col2) IS NOT false; (2 rows) EXPLAIN SELECT * FROM pt_bool_tab WHERE (not col2) IS NOT unknown; - QUERY PLAN ------------------------------------------------------------------------------------------------------------ + QUERY PLAN +------------------------------------------------------------------------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) (cost=10000000000.00..20000003493.53 rows=186813 width=5) -> Append (cost=10000000000.00..20000001002.69 rows=62271 width=5) - -> Seq Scan on pt_bool_tab_1_prt_part2 (cost=10000000000.00..10000000345.67 rows=31136 width=5) + -> Seq Scan on pt_bool_tab_1_prt_part2 pt_bool_tab_1 (cost=10000000000.00..10000000345.67 rows=31136 width=5) Filter: ((NOT col2) IS NOT UNKNOWN) - -> Seq Scan on pt_bool_tab_1_prt_part1 (cost=10000000000.00..10000000345.67 rows=31136 width=5) + -> Seq Scan on pt_bool_tab_1_prt_part1 pt_bool_tab_2 (cost=10000000000.00..10000000345.67 rows=31136 width=5) Filter: ((NOT col2) IS NOT UNKNOWN) - Optimizer: Postgres-based planner + Optimizer: Postgres query optimizer (7 rows) SELECT * FROM pt_bool_tab WHERE (not col2) IS NOT unknown; @@ -3300,15 +3300,15 @@ SELECT * FROM pt_bool_tab WHERE (not col2) IS NOT unknown; (5 rows) EXPLAIN SELECT * FROM pt_bool_tab WHERE (not col2) IS NOT NULL; - QUERY PLAN ------------------------------------------------------------------------------------------------------------ + QUERY PLAN +------------------------------------------------------------------------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) (cost=10000000000.00..20000003493.53 rows=186813 width=5) -> Append (cost=10000000000.00..20000001002.69 rows=62271 width=5) - -> Seq Scan on pt_bool_tab_1_prt_part2 (cost=10000000000.00..10000000345.67 rows=31136 width=5) + -> Seq Scan on pt_bool_tab_1_prt_part2 pt_bool_tab_1 (cost=10000000000.00..10000000345.67 rows=31136 width=5) Filter: ((NOT col2) IS NOT NULL) - -> Seq Scan on pt_bool_tab_1_prt_part1 (cost=10000000000.00..10000000345.67 rows=31136 width=5) + -> Seq Scan on pt_bool_tab_1_prt_part1 pt_bool_tab_2 (cost=10000000000.00..10000000345.67 rows=31136 width=5) Filter: ((NOT col2) IS NOT NULL) - Optimizer: Postgres-based planner + Optimizer: Postgres query optimizer (7 rows) SELECT * FROM pt_bool_tab WHERE (not col2) IS NOT NULL; @@ -3486,17 +3486,17 @@ SELECT * FROM pt_bool_tab_df WHERE col2 IS NOT NULL; (5 rows) EXPLAIN SELECT * FROM pt_bool_tab_df WHERE (not col2) IS true; - QUERY PLAN --------------------------------------------------------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------------------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) (cost=10000000000.00..30000003140.75 rows=140250 width=5) -> Append (cost=10000000000.00..30000001270.75 rows=46750 width=5) - -> Seq Scan on pt_bool_tab_df_1_prt_part2 (cost=10000000000.00..10000000345.67 rows=15583 width=5) + -> Seq Scan on pt_bool_tab_df_1_prt_part2 pt_bool_tab_df_1 (cost=10000000000.00..10000000345.67 rows=15583 width=5) Filter: ((NOT col2) IS TRUE) - -> Seq Scan on pt_bool_tab_df_1_prt_part1 (cost=10000000000.00..10000000345.67 rows=15583 width=5) + -> Seq Scan on pt_bool_tab_df_1_prt_part1 pt_bool_tab_df_2 (cost=10000000000.00..10000000345.67 rows=15583 width=5) Filter: ((NOT col2) IS TRUE) - -> Seq Scan on pt_bool_tab_df_1_prt_def (cost=10000000000.00..10000000345.67 rows=15583 width=5) + -> Seq Scan on pt_bool_tab_df_1_prt_def pt_bool_tab_df_3 (cost=10000000000.00..10000000345.67 rows=15583 width=5) Filter: ((NOT col2) IS TRUE) - Optimizer: Postgres-based planner + Optimizer: Postgres query optimizer (9 rows) SELECT * FROM pt_bool_tab_df WHERE (not col2) IS true; @@ -3507,17 +3507,17 @@ SELECT * FROM pt_bool_tab_df WHERE (not col2) IS true; (2 rows) EXPLAIN SELECT * FROM pt_bool_tab_df WHERE (not col2) IS false; - QUERY PLAN --------------------------------------------------------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------------------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) (cost=10000000000.00..30000003140.75 rows=140250 width=5) -> Append (cost=10000000000.00..30000001270.75 rows=46750 width=5) - -> Seq Scan on pt_bool_tab_df_1_prt_part2 (cost=10000000000.00..10000000345.67 rows=15583 width=5) + -> Seq Scan on pt_bool_tab_df_1_prt_part2 pt_bool_tab_df_1 (cost=10000000000.00..10000000345.67 rows=15583 width=5) Filter: ((NOT col2) IS FALSE) - -> Seq Scan on pt_bool_tab_df_1_prt_part1 (cost=10000000000.00..10000000345.67 rows=15583 width=5) + -> Seq Scan on pt_bool_tab_df_1_prt_part1 pt_bool_tab_df_2 (cost=10000000000.00..10000000345.67 rows=15583 width=5) Filter: ((NOT col2) IS FALSE) - -> Seq Scan on pt_bool_tab_df_1_prt_def (cost=10000000000.00..10000000345.67 rows=15583 width=5) + -> Seq Scan on pt_bool_tab_df_1_prt_def pt_bool_tab_df_3 (cost=10000000000.00..10000000345.67 rows=15583 width=5) Filter: ((NOT col2) IS FALSE) - Optimizer: Postgres-based planner + Optimizer: Postgres query optimizer (9 rows) SELECT * FROM pt_bool_tab_df WHERE (not col2) IS false; @@ -3529,17 +3529,17 @@ SELECT * FROM pt_bool_tab_df WHERE (not col2) IS false; (3 rows) EXPLAIN SELECT * FROM pt_bool_tab_df WHERE (not col2) IS NULL; - QUERY PLAN ------------------------------------------------------------------------------------------------------------ + QUERY PLAN +---------------------------------------------------------------------------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) (cost=10000000000.00..30000001041.21 rows=280 width=5) -> Append (cost=10000000000.00..30000001037.47 rows=94 width=5) - -> Seq Scan on pt_bool_tab_df_1_prt_part2 (cost=10000000000.00..10000000345.67 rows=31 width=5) + -> Seq Scan on pt_bool_tab_df_1_prt_part2 pt_bool_tab_df_1 (cost=10000000000.00..10000000345.67 rows=31 width=5) Filter: ((NOT col2) IS NULL) - -> Seq Scan on pt_bool_tab_df_1_prt_part1 (cost=10000000000.00..10000000345.67 rows=31 width=5) + -> Seq Scan on pt_bool_tab_df_1_prt_part1 pt_bool_tab_df_2 (cost=10000000000.00..10000000345.67 rows=31 width=5) Filter: ((NOT col2) IS NULL) - -> Seq Scan on pt_bool_tab_df_1_prt_def (cost=10000000000.00..10000000345.67 rows=31 width=5) + -> Seq Scan on pt_bool_tab_df_1_prt_def pt_bool_tab_df_3 (cost=10000000000.00..10000000345.67 rows=31 width=5) Filter: ((NOT col2) IS NULL) - Optimizer: Postgres-based planner + Optimizer: Postgres query optimizer (9 rows) SELECT * FROM pt_bool_tab_df WHERE (not col2) IS NULL; @@ -3549,17 +3549,17 @@ SELECT * FROM pt_bool_tab_df WHERE (not col2) IS NULL; (1 row) EXPLAIN SELECT * FROM pt_bool_tab_df WHERE (not col2) IS unknown; - QUERY PLAN ------------------------------------------------------------------------------------------------------------ + QUERY PLAN +---------------------------------------------------------------------------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) (cost=10000000000.00..30000001041.21 rows=280 width=5) -> Append (cost=10000000000.00..30000001037.47 rows=94 width=5) - -> Seq Scan on pt_bool_tab_df_1_prt_part2 (cost=10000000000.00..10000000345.67 rows=31 width=5) + -> Seq Scan on pt_bool_tab_df_1_prt_part2 pt_bool_tab_df_1 (cost=10000000000.00..10000000345.67 rows=31 width=5) Filter: ((NOT col2) IS UNKNOWN) - -> Seq Scan on pt_bool_tab_df_1_prt_part1 (cost=10000000000.00..10000000345.67 rows=31 width=5) + -> Seq Scan on pt_bool_tab_df_1_prt_part1 pt_bool_tab_df_2 (cost=10000000000.00..10000000345.67 rows=31 width=5) Filter: ((NOT col2) IS UNKNOWN) - -> Seq Scan on pt_bool_tab_df_1_prt_def (cost=10000000000.00..10000000345.67 rows=31 width=5) + -> Seq Scan on pt_bool_tab_df_1_prt_def pt_bool_tab_df_3 (cost=10000000000.00..10000000345.67 rows=31 width=5) Filter: ((NOT col2) IS UNKNOWN) - Optimizer: Postgres-based planner + Optimizer: Postgres query optimizer (9 rows) SELECT * FROM pt_bool_tab_df WHERE (not col2) IS unknown; @@ -3569,17 +3569,17 @@ SELECT * FROM pt_bool_tab_df WHERE (not col2) IS unknown; (1 row) EXPLAIN SELECT * FROM pt_bool_tab_df WHERE (not col2) IS NOT true; - QUERY PLAN --------------------------------------------------------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------------------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) (cost=10000000000.00..30000003140.75 rows=140250 width=5) -> Append (cost=10000000000.00..30000001270.75 rows=46750 width=5) - -> Seq Scan on pt_bool_tab_df_1_prt_part2 (cost=10000000000.00..10000000345.67 rows=15583 width=5) + -> Seq Scan on pt_bool_tab_df_1_prt_part2 pt_bool_tab_df_1 (cost=10000000000.00..10000000345.67 rows=15583 width=5) Filter: ((NOT col2) IS NOT TRUE) - -> Seq Scan on pt_bool_tab_df_1_prt_part1 (cost=10000000000.00..10000000345.67 rows=15583 width=5) + -> Seq Scan on pt_bool_tab_df_1_prt_part1 pt_bool_tab_df_2 (cost=10000000000.00..10000000345.67 rows=15583 width=5) Filter: ((NOT col2) IS NOT TRUE) - -> Seq Scan on pt_bool_tab_df_1_prt_def (cost=10000000000.00..10000000345.67 rows=15583 width=5) + -> Seq Scan on pt_bool_tab_df_1_prt_def pt_bool_tab_df_3 (cost=10000000000.00..10000000345.67 rows=15583 width=5) Filter: ((NOT col2) IS NOT TRUE) - Optimizer: Postgres-based planner + Optimizer: Postgres query optimizer (9 rows) SELECT * FROM pt_bool_tab_df WHERE (not col2) IS NOT true; @@ -3592,17 +3592,17 @@ SELECT * FROM pt_bool_tab_df WHERE (not col2) IS NOT true; (4 rows) EXPLAIN SELECT * FROM pt_bool_tab_df WHERE (not col2) IS NOT false; - QUERY PLAN --------------------------------------------------------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------------------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) (cost=10000000000.00..30000003140.75 rows=140250 width=5) -> Append (cost=10000000000.00..30000001270.75 rows=46750 width=5) - -> Seq Scan on pt_bool_tab_df_1_prt_part2 (cost=10000000000.00..10000000345.67 rows=15583 width=5) + -> Seq Scan on pt_bool_tab_df_1_prt_part2 pt_bool_tab_df_1 (cost=10000000000.00..10000000345.67 rows=15583 width=5) Filter: ((NOT col2) IS NOT FALSE) - -> Seq Scan on pt_bool_tab_df_1_prt_part1 (cost=10000000000.00..10000000345.67 rows=15583 width=5) + -> Seq Scan on pt_bool_tab_df_1_prt_part1 pt_bool_tab_df_2 (cost=10000000000.00..10000000345.67 rows=15583 width=5) Filter: ((NOT col2) IS NOT FALSE) - -> Seq Scan on pt_bool_tab_df_1_prt_def (cost=10000000000.00..10000000345.67 rows=15583 width=5) + -> Seq Scan on pt_bool_tab_df_1_prt_def pt_bool_tab_df_3 (cost=10000000000.00..10000000345.67 rows=15583 width=5) Filter: ((NOT col2) IS NOT FALSE) - Optimizer: Postgres-based planner + Optimizer: Postgres query optimizer (9 rows) SELECT * FROM pt_bool_tab_df WHERE (not col2) IS NOT false; @@ -3614,17 +3614,17 @@ SELECT * FROM pt_bool_tab_df WHERE (not col2) IS NOT false; (3 rows) EXPLAIN SELECT * FROM pt_bool_tab_df WHERE (not col2) IS NOT unknown; - QUERY PLAN --------------------------------------------------------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------------------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) (cost=10000000000.00..30000005240.29 rows=280220 width=5) -> Append (cost=10000000000.00..30000001504.03 rows=93406 width=5) - -> Seq Scan on pt_bool_tab_df_1_prt_part2 (cost=10000000000.00..10000000345.67 rows=31136 width=5) + -> Seq Scan on pt_bool_tab_df_1_prt_part2 pt_bool_tab_df_1 (cost=10000000000.00..10000000345.67 rows=31136 width=5) Filter: ((NOT col2) IS NOT UNKNOWN) - -> Seq Scan on pt_bool_tab_df_1_prt_part1 (cost=10000000000.00..10000000345.67 rows=31136 width=5) + -> Seq Scan on pt_bool_tab_df_1_prt_part1 pt_bool_tab_df_2 (cost=10000000000.00..10000000345.67 rows=31136 width=5) Filter: ((NOT col2) IS NOT UNKNOWN) - -> Seq Scan on pt_bool_tab_df_1_prt_def (cost=10000000000.00..10000000345.67 rows=31136 width=5) + -> Seq Scan on pt_bool_tab_df_1_prt_def pt_bool_tab_df_3 (cost=10000000000.00..10000000345.67 rows=31136 width=5) Filter: ((NOT col2) IS NOT UNKNOWN) - Optimizer: Postgres-based planner + Optimizer: Postgres query optimizer (9 rows) SELECT * FROM pt_bool_tab_df WHERE (not col2) IS NOT unknown; @@ -3638,17 +3638,17 @@ SELECT * FROM pt_bool_tab_df WHERE (not col2) IS NOT unknown; (5 rows) EXPLAIN SELECT * FROM pt_bool_tab_df WHERE (not col2) IS NOT NULL; - QUERY PLAN --------------------------------------------------------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------------------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) (cost=10000000000.00..30000005240.29 rows=280220 width=5) -> Append (cost=10000000000.00..30000001504.03 rows=93406 width=5) - -> Seq Scan on pt_bool_tab_df_1_prt_part2 (cost=10000000000.00..10000000345.67 rows=31136 width=5) + -> Seq Scan on pt_bool_tab_df_1_prt_part2 pt_bool_tab_df_1 (cost=10000000000.00..10000000345.67 rows=31136 width=5) Filter: ((NOT col2) IS NOT NULL) - -> Seq Scan on pt_bool_tab_df_1_prt_part1 (cost=10000000000.00..10000000345.67 rows=31136 width=5) + -> Seq Scan on pt_bool_tab_df_1_prt_part1 pt_bool_tab_df_2 (cost=10000000000.00..10000000345.67 rows=31136 width=5) Filter: ((NOT col2) IS NOT NULL) - -> Seq Scan on pt_bool_tab_df_1_prt_def (cost=10000000000.00..10000000345.67 rows=31136 width=5) + -> Seq Scan on pt_bool_tab_df_1_prt_def pt_bool_tab_df_3 (cost=10000000000.00..10000000345.67 rows=31136 width=5) Filter: ((NOT col2) IS NOT NULL) - Optimizer: Postgres-based planner + Optimizer: Postgres query optimizer (9 rows) SELECT * FROM pt_bool_tab_df WHERE (not col2) IS NOT NULL; @@ -3824,17 +3824,17 @@ SELECT * FROM pt_bool_tab_null WHERE col2 IS NOT NULL; (5 rows) EXPLAIN SELECT * FROM pt_bool_tab_null WHERE (not col2) IS true; - QUERY PLAN ----------------------------------------------------------------------------------------------------------------- + QUERY PLAN +----------------------------------------------------------------------------------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) (cost=10000000000.00..30000003140.75 rows=140250 width=5) -> Append (cost=10000000000.00..30000001270.75 rows=46750 width=5) - -> Seq Scan on pt_bool_tab_null_1_prt_part2 (cost=10000000000.00..10000000345.67 rows=15583 width=5) + -> Seq Scan on pt_bool_tab_null_1_prt_part2 pt_bool_tab_null_1 (cost=10000000000.00..10000000345.67 rows=15583 width=5) Filter: ((NOT col2) IS TRUE) - -> Seq Scan on pt_bool_tab_null_1_prt_part1 (cost=10000000000.00..10000000345.67 rows=15583 width=5) + -> Seq Scan on pt_bool_tab_null_1_prt_part1 pt_bool_tab_null_2 (cost=10000000000.00..10000000345.67 rows=15583 width=5) Filter: ((NOT col2) IS TRUE) - -> Seq Scan on pt_bool_tab_null_1_prt_part3 (cost=10000000000.00..10000000345.67 rows=15583 width=5) + -> Seq Scan on pt_bool_tab_null_1_prt_part3 pt_bool_tab_null_3 (cost=10000000000.00..10000000345.67 rows=15583 width=5) Filter: ((NOT col2) IS TRUE) - Optimizer: Postgres-based planner + Optimizer: Postgres query optimizer (9 rows) SELECT * FROM pt_bool_tab_null WHERE (not col2) IS true; @@ -3845,17 +3845,17 @@ SELECT * FROM pt_bool_tab_null WHERE (not col2) IS true; (2 rows) EXPLAIN SELECT * FROM pt_bool_tab_null WHERE (not col2) IS false; - QUERY PLAN ----------------------------------------------------------------------------------------------------------------- + QUERY PLAN +----------------------------------------------------------------------------------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) (cost=10000000000.00..30000003140.75 rows=140250 width=5) -> Append (cost=10000000000.00..30000001270.75 rows=46750 width=5) - -> Seq Scan on pt_bool_tab_null_1_prt_part2 (cost=10000000000.00..10000000345.67 rows=15583 width=5) + -> Seq Scan on pt_bool_tab_null_1_prt_part2 pt_bool_tab_null_1 (cost=10000000000.00..10000000345.67 rows=15583 width=5) Filter: ((NOT col2) IS FALSE) - -> Seq Scan on pt_bool_tab_null_1_prt_part1 (cost=10000000000.00..10000000345.67 rows=15583 width=5) + -> Seq Scan on pt_bool_tab_null_1_prt_part1 pt_bool_tab_null_2 (cost=10000000000.00..10000000345.67 rows=15583 width=5) Filter: ((NOT col2) IS FALSE) - -> Seq Scan on pt_bool_tab_null_1_prt_part3 (cost=10000000000.00..10000000345.67 rows=15583 width=5) + -> Seq Scan on pt_bool_tab_null_1_prt_part3 pt_bool_tab_null_3 (cost=10000000000.00..10000000345.67 rows=15583 width=5) Filter: ((NOT col2) IS FALSE) - Optimizer: Postgres-based planner + Optimizer: Postgres query optimizer (9 rows) SELECT * FROM pt_bool_tab_null WHERE (not col2) IS false; @@ -3867,17 +3867,17 @@ SELECT * FROM pt_bool_tab_null WHERE (not col2) IS false; (3 rows) EXPLAIN SELECT * FROM pt_bool_tab_null WHERE (not col2) IS NULL; - QUERY PLAN -------------------------------------------------------------------------------------------------------------- + QUERY PLAN +-------------------------------------------------------------------------------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) (cost=10000000000.00..30000001041.21 rows=280 width=5) -> Append (cost=10000000000.00..30000001037.47 rows=94 width=5) - -> Seq Scan on pt_bool_tab_null_1_prt_part2 (cost=10000000000.00..10000000345.67 rows=31 width=5) + -> Seq Scan on pt_bool_tab_null_1_prt_part2 pt_bool_tab_null_1 (cost=10000000000.00..10000000345.67 rows=31 width=5) Filter: ((NOT col2) IS NULL) - -> Seq Scan on pt_bool_tab_null_1_prt_part1 (cost=10000000000.00..10000000345.67 rows=31 width=5) + -> Seq Scan on pt_bool_tab_null_1_prt_part1 pt_bool_tab_null_2 (cost=10000000000.00..10000000345.67 rows=31 width=5) Filter: ((NOT col2) IS NULL) - -> Seq Scan on pt_bool_tab_null_1_prt_part3 (cost=10000000000.00..10000000345.67 rows=31 width=5) + -> Seq Scan on pt_bool_tab_null_1_prt_part3 pt_bool_tab_null_3 (cost=10000000000.00..10000000345.67 rows=31 width=5) Filter: ((NOT col2) IS NULL) - Optimizer: Postgres-based planner + Optimizer: Postgres query optimizer (9 rows) SELECT * FROM pt_bool_tab_null WHERE (not col2) IS NULL; @@ -3887,17 +3887,17 @@ SELECT * FROM pt_bool_tab_null WHERE (not col2) IS NULL; (1 row) EXPLAIN SELECT * FROM pt_bool_tab_null WHERE (not col2) IS unknown; - QUERY PLAN -------------------------------------------------------------------------------------------------------------- + QUERY PLAN +-------------------------------------------------------------------------------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) (cost=10000000000.00..30000001041.21 rows=280 width=5) -> Append (cost=10000000000.00..30000001037.47 rows=94 width=5) - -> Seq Scan on pt_bool_tab_null_1_prt_part2 (cost=10000000000.00..10000000345.67 rows=31 width=5) + -> Seq Scan on pt_bool_tab_null_1_prt_part2 pt_bool_tab_null_1 (cost=10000000000.00..10000000345.67 rows=31 width=5) Filter: ((NOT col2) IS UNKNOWN) - -> Seq Scan on pt_bool_tab_null_1_prt_part1 (cost=10000000000.00..10000000345.67 rows=31 width=5) + -> Seq Scan on pt_bool_tab_null_1_prt_part1 pt_bool_tab_null_2 (cost=10000000000.00..10000000345.67 rows=31 width=5) Filter: ((NOT col2) IS UNKNOWN) - -> Seq Scan on pt_bool_tab_null_1_prt_part3 (cost=10000000000.00..10000000345.67 rows=31 width=5) + -> Seq Scan on pt_bool_tab_null_1_prt_part3 pt_bool_tab_null_3 (cost=10000000000.00..10000000345.67 rows=31 width=5) Filter: ((NOT col2) IS UNKNOWN) - Optimizer: Postgres-based planner + Optimizer: Postgres query optimizer (9 rows) SELECT * FROM pt_bool_tab_null WHERE (not col2) IS unknown; @@ -3907,17 +3907,17 @@ SELECT * FROM pt_bool_tab_null WHERE (not col2) IS unknown; (1 row) EXPLAIN SELECT * FROM pt_bool_tab_null WHERE (not col2) IS NOT true; - QUERY PLAN ----------------------------------------------------------------------------------------------------------------- + QUERY PLAN +----------------------------------------------------------------------------------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) (cost=10000000000.00..30000003140.75 rows=140250 width=5) -> Append (cost=10000000000.00..30000001270.75 rows=46750 width=5) - -> Seq Scan on pt_bool_tab_null_1_prt_part2 (cost=10000000000.00..10000000345.67 rows=15583 width=5) + -> Seq Scan on pt_bool_tab_null_1_prt_part2 pt_bool_tab_null_1 (cost=10000000000.00..10000000345.67 rows=15583 width=5) Filter: ((NOT col2) IS NOT TRUE) - -> Seq Scan on pt_bool_tab_null_1_prt_part1 (cost=10000000000.00..10000000345.67 rows=15583 width=5) + -> Seq Scan on pt_bool_tab_null_1_prt_part1 pt_bool_tab_null_2 (cost=10000000000.00..10000000345.67 rows=15583 width=5) Filter: ((NOT col2) IS NOT TRUE) - -> Seq Scan on pt_bool_tab_null_1_prt_part3 (cost=10000000000.00..10000000345.67 rows=15583 width=5) + -> Seq Scan on pt_bool_tab_null_1_prt_part3 pt_bool_tab_null_3 (cost=10000000000.00..10000000345.67 rows=15583 width=5) Filter: ((NOT col2) IS NOT TRUE) - Optimizer: Postgres-based planner + Optimizer: Postgres query optimizer (9 rows) SELECT * FROM pt_bool_tab_null WHERE (not col2) IS NOT true; @@ -3930,17 +3930,17 @@ SELECT * FROM pt_bool_tab_null WHERE (not col2) IS NOT true; (4 rows) EXPLAIN SELECT * FROM pt_bool_tab_null WHERE (not col2) IS NOT false; - QUERY PLAN ----------------------------------------------------------------------------------------------------------------- + QUERY PLAN +----------------------------------------------------------------------------------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) (cost=10000000000.00..30000003140.75 rows=140250 width=5) -> Append (cost=10000000000.00..30000001270.75 rows=46750 width=5) - -> Seq Scan on pt_bool_tab_null_1_prt_part2 (cost=10000000000.00..10000000345.67 rows=15583 width=5) + -> Seq Scan on pt_bool_tab_null_1_prt_part2 pt_bool_tab_null_1 (cost=10000000000.00..10000000345.67 rows=15583 width=5) Filter: ((NOT col2) IS NOT FALSE) - -> Seq Scan on pt_bool_tab_null_1_prt_part1 (cost=10000000000.00..10000000345.67 rows=15583 width=5) + -> Seq Scan on pt_bool_tab_null_1_prt_part1 pt_bool_tab_null_2 (cost=10000000000.00..10000000345.67 rows=15583 width=5) Filter: ((NOT col2) IS NOT FALSE) - -> Seq Scan on pt_bool_tab_null_1_prt_part3 (cost=10000000000.00..10000000345.67 rows=15583 width=5) + -> Seq Scan on pt_bool_tab_null_1_prt_part3 pt_bool_tab_null_3 (cost=10000000000.00..10000000345.67 rows=15583 width=5) Filter: ((NOT col2) IS NOT FALSE) - Optimizer: Postgres-based planner + Optimizer: Postgres query optimizer (9 rows) SELECT * FROM pt_bool_tab_null WHERE (not col2) IS NOT false; @@ -3952,17 +3952,17 @@ SELECT * FROM pt_bool_tab_null WHERE (not col2) IS NOT false; (3 rows) EXPLAIN SELECT * FROM pt_bool_tab_null WHERE (not col2) IS NOT unknown; - QUERY PLAN ----------------------------------------------------------------------------------------------------------------- + QUERY PLAN +----------------------------------------------------------------------------------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) (cost=10000000000.00..30000005240.29 rows=280220 width=5) -> Append (cost=10000000000.00..30000001504.03 rows=93406 width=5) - -> Seq Scan on pt_bool_tab_null_1_prt_part2 (cost=10000000000.00..10000000345.67 rows=31136 width=5) + -> Seq Scan on pt_bool_tab_null_1_prt_part2 pt_bool_tab_null_1 (cost=10000000000.00..10000000345.67 rows=31136 width=5) Filter: ((NOT col2) IS NOT UNKNOWN) - -> Seq Scan on pt_bool_tab_null_1_prt_part1 (cost=10000000000.00..10000000345.67 rows=31136 width=5) + -> Seq Scan on pt_bool_tab_null_1_prt_part1 pt_bool_tab_null_2 (cost=10000000000.00..10000000345.67 rows=31136 width=5) Filter: ((NOT col2) IS NOT UNKNOWN) - -> Seq Scan on pt_bool_tab_null_1_prt_part3 (cost=10000000000.00..10000000345.67 rows=31136 width=5) + -> Seq Scan on pt_bool_tab_null_1_prt_part3 pt_bool_tab_null_3 (cost=10000000000.00..10000000345.67 rows=31136 width=5) Filter: ((NOT col2) IS NOT UNKNOWN) - Optimizer: Postgres-based planner + Optimizer: Postgres query optimizer (9 rows) SELECT * FROM pt_bool_tab_null WHERE (not col2) IS NOT unknown; @@ -3976,17 +3976,17 @@ SELECT * FROM pt_bool_tab_null WHERE (not col2) IS NOT unknown; (5 rows) EXPLAIN SELECT * FROM pt_bool_tab_null WHERE (not col2) IS NOT NULL; - QUERY PLAN ----------------------------------------------------------------------------------------------------------------- + QUERY PLAN +----------------------------------------------------------------------------------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) (cost=10000000000.00..30000005240.29 rows=280220 width=5) -> Append (cost=10000000000.00..30000001504.03 rows=93406 width=5) - -> Seq Scan on pt_bool_tab_null_1_prt_part2 (cost=10000000000.00..10000000345.67 rows=31136 width=5) + -> Seq Scan on pt_bool_tab_null_1_prt_part2 pt_bool_tab_null_1 (cost=10000000000.00..10000000345.67 rows=31136 width=5) Filter: ((NOT col2) IS NOT NULL) - -> Seq Scan on pt_bool_tab_null_1_prt_part1 (cost=10000000000.00..10000000345.67 rows=31136 width=5) + -> Seq Scan on pt_bool_tab_null_1_prt_part1 pt_bool_tab_null_2 (cost=10000000000.00..10000000345.67 rows=31136 width=5) Filter: ((NOT col2) IS NOT NULL) - -> Seq Scan on pt_bool_tab_null_1_prt_part3 (cost=10000000000.00..10000000345.67 rows=31136 width=5) + -> Seq Scan on pt_bool_tab_null_1_prt_part3 pt_bool_tab_null_3 (cost=10000000000.00..10000000345.67 rows=31136 width=5) Filter: ((NOT col2) IS NOT NULL) - Optimizer: Postgres-based planner + Optimizer: Postgres query optimizer (9 rows) SELECT * FROM pt_bool_tab_null WHERE (not col2) IS NOT NULL; diff --git a/src/test/regress/expected/qp_indexscan.out b/src/test/regress/expected/qp_indexscan.out index e8af9eee9f..6061419c51 100644 --- a/src/test/regress/expected/qp_indexscan.out +++ b/src/test/regress/expected/qp_indexscan.out @@ -3913,17 +3913,14 @@ INSERT INTO test_ao_table SELECT i, i from generate_series(1,100) i; ANALYZE test_ao_table; -- Test max() aggregate. This query is eligible to use optimization explain(costs off) select max(b) from test_ao_table; - QUERY PLAN ------------------------------------------------------------------------ - Result - InitPlan 1 (returns $0) (slice1) - -> Limit - -> Gather Motion 3:1 (slice2; segments: 3) - Merge Key: test_ao_table.b - -> Index Only Scan using ao_index_b on test_ao_table - Index Cond: (b IS NOT NULL) - Optimizer: Postgres-based planner -(8 rows) + QUERY PLAN +------------------------------------------------ + Finalize Aggregate + -> Gather Motion 3:1 (slice1; segments: 3) + -> Partial Aggregate + -> Seq Scan on test_ao_table + Optimizer: Postgres query optimizer +(5 rows) select max(b) from test_ao_table; max @@ -3933,17 +3930,14 @@ select max(b) from test_ao_table; -- Test min() aggregate. This query is eligible to use optimization explain(costs off) select min(b) from test_ao_table; - QUERY PLAN --------------------------------------------------------------------------------- - Result - InitPlan 1 (returns $0) (slice1) - -> Limit - -> Gather Motion 3:1 (slice2; segments: 3) - Merge Key: test_ao_table.b - -> Index Only Scan Backward using ao_index_b on test_ao_table - Index Cond: (b IS NOT NULL) - Optimizer: Postgres-based planner -(8 rows) + QUERY PLAN +------------------------------------------------ + Finalize Aggregate + -> Gather Motion 3:1 (slice1; segments: 3) + -> Partial Aggregate + -> Seq Scan on test_ao_table + Optimizer: Postgres query optimizer +(5 rows) select min(b) from test_ao_table; min @@ -3972,29 +3966,29 @@ ANALYZE test_partition_table; -- as first child of LogicalGbAgg whereas transform's pattern has LogicalGet. -- Support for these queries is beyond the scope of the current PR. explain(costs off) select max(b) from test_partition_table; - QUERY PLAN -------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------------------ Finalize Aggregate -> Gather Motion 3:1 (slice1; segments: 3) -> Partial Aggregate -> Append - -> Seq Scan on partition1 - -> Seq Scan on partition2 - -> Seq Scan on default_partition - Optimizer: Postgres-based planner + -> Seq Scan on partition1 test_partition_table_1 + -> Seq Scan on partition2 test_partition_table_2 + -> Seq Scan on default_partition test_partition_table_3 + Optimizer: Postgres query optimizer (8 rows) explain(costs off) select min(b) from test_partition_table; - QUERY PLAN -------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------------------ Finalize Aggregate -> Gather Motion 3:1 (slice1; segments: 3) -> Partial Aggregate -> Append - -> Seq Scan on partition1 - -> Seq Scan on partition2 - -> Seq Scan on default_partition - Optimizer: Postgres-based planner + -> Seq Scan on partition1 test_partition_table_1 + -> Seq Scan on partition2 test_partition_table_2 + -> Seq Scan on default_partition test_partition_table_3 + Optimizer: Postgres query optimizer (8 rows) -- Test IS NULL, IS NOT NULL on partition table btree index column. @@ -4002,12 +3996,12 @@ explain(costs off) select min(b) from test_partition_table; -- whereas, for IS NOT NULL it doesn't because the Non null values -- could be in all of the partitions explain(costs off) select * from test_partition_table where b is null; - QUERY PLAN ------------------------------------------- + QUERY PLAN +---------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) - -> Seq Scan on default_partition + -> Seq Scan on default_partition test_partition_table Filter: (b IS NULL) - Optimizer: Postgres-based planner + Optimizer: Postgres query optimizer (4 rows) select * from test_partition_table where b is null; @@ -4017,17 +4011,17 @@ select * from test_partition_table where b is null; (1 row) explain(costs off) select * from test_partition_table where b is not null; - QUERY PLAN -------------------------------------------- + QUERY PLAN +------------------------------------------------------------------ Gather Motion 3:1 (slice1; segments: 3) -> Append - -> Seq Scan on partition1 + -> Seq Scan on partition1 test_partition_table_1 Filter: (b IS NOT NULL) - -> Seq Scan on partition2 + -> Seq Scan on partition2 test_partition_table_2 Filter: (b IS NOT NULL) - -> Seq Scan on default_partition + -> Seq Scan on default_partition test_partition_table_3 Filter: (b IS NOT NULL) - Optimizer: Postgres-based planner + Optimizer: Postgres query optimizer (9 rows) select * from test_partition_table where b is not null; diff --git a/src/test/regress/expected/subselect_gp.out b/src/test/regress/expected/subselect_gp.out index 2d723d5350..6c10081be5 100644 --- a/src/test/regress/expected/subselect_gp.out +++ b/src/test/regress/expected/subselect_gp.out @@ -3546,6 +3546,39 @@ select (SELECT EXISTS (0 rows) drop table tmp; +-- Test nested query with aggregate inside a sublink, +-- ORCA should correctly normalize the aggregate expression inside the +-- sublink's nested query and the column variable accessed in aggregate should +-- be accessible to the aggregate after the normalization of query. +-- If the query is not supported, ORCA should gracefully fallback to postgres +explain (COSTS OFF) with t0 AS ( + SELECT + ROW_TO_JSON((SELECT x FROM (SELECT max(t.b)) x)) + AS c + FROM r + JOIN s ON true + JOIN s as t ON true + ) +SELECT c FROM t0; + QUERY PLAN +--------------------------------------------------------------------------------------- + Finalize Aggregate + -> Gather Motion 3:1 (slice1; segments: 3) + -> Partial Aggregate + -> Nested Loop + -> Broadcast Motion 3:3 (slice2; segments: 3) + -> Nested Loop + -> Seq Scan on r + -> Materialize + -> Broadcast Motion 3:3 (slice3; segments: 3) + -> Seq Scan on s + -> Materialize + -> Seq Scan on s t + SubPlan 1 + -> Result + Optimizer: Postgres query optimizer +(15 rows) + -- -- Test case for ORCA semi join with random table -- See https://github.com/greenplum-db/gpdb/issues/16611 @@ -3671,17 +3704,17 @@ explain (costs off) select * from table_left where exists (select 1 from table_r -> Redistribute Motion 3:3 (slice2; segments: 3) Hash Key: (RowIdExpr) -> Hash Join - Hash Cond: (table_right_1_prt_1.r1 = table_left.l1) + Hash Cond: (table_right.r1 = table_left.l1) -> Append Partition Selectors: $0 - -> Seq Scan on table_right_1_prt_1 - -> Seq Scan on table_right_1_prt_2 - -> Seq Scan on table_right_1_prt_3 + -> Seq Scan on table_right_1_prt_1 table_right_1 + -> Seq Scan on table_right_1_prt_2 table_right_2 + -> Seq Scan on table_right_1_prt_3 table_right_3 -> Hash -> Partition Selector (selector id: $0) -> Broadcast Motion 3:3 (slice3; segments: 3) -> Seq Scan on table_left - Optimizer: Postgres-based planner + Optimizer: Postgres query optimizer (17 rows) select * from table_left where exists (select 1 from table_right where l1 = r1); @@ -3699,17 +3732,17 @@ explain (costs off) select * from table_left where l1 in (select r1 from table_r -> Redistribute Motion 3:3 (slice2; segments: 3) Hash Key: (RowIdExpr) -> Hash Join - Hash Cond: (table_right_1_prt_1.r1 = table_left.l1) + Hash Cond: (table_right.r1 = table_left.l1) -> Append Partition Selectors: $0 - -> Seq Scan on table_right_1_prt_1 - -> Seq Scan on table_right_1_prt_2 - -> Seq Scan on table_right_1_prt_3 + -> Seq Scan on table_right_1_prt_1 table_right_1 + -> Seq Scan on table_right_1_prt_2 table_right_2 + -> Seq Scan on table_right_1_prt_3 table_right_3 -> Hash -> Partition Selector (selector id: $0) -> Broadcast Motion 3:3 (slice3; segments: 3) -> Seq Scan on table_left - Optimizer: Postgres-based planner + Optimizer: Postgres query optimizer (17 rows) select * from table_left where exists (select 1 from table_right where l1 = r1); diff --git a/src/test/regress/expected/subselect_gp_optimizer.out b/src/test/regress/expected/subselect_gp_optimizer.out index ab14862bbf..d5dd311003 100644 --- a/src/test/regress/expected/subselect_gp_optimizer.out +++ b/src/test/regress/expected/subselect_gp_optimizer.out @@ -3666,6 +3666,39 @@ select (SELECT EXISTS (0 rows) drop table tmp; +-- Test nested query with aggregate inside a sublink, +-- ORCA should correctly normalize the aggregate expression inside the +-- sublink's nested query and the column variable accessed in aggregate should +-- be accessible to the aggregate after the normalization of query. +-- If the query is not supported, ORCA should gracefully fallback to postgres +explain (COSTS OFF) with t0 AS ( + SELECT + ROW_TO_JSON((SELECT x FROM (SELECT max(t.b)) x)) + AS c + FROM r + JOIN s ON true + JOIN s as t ON true + ) +SELECT c FROM t0; + QUERY PLAN +--------------------------------------------------------------------------------------- + Finalize Aggregate + -> Gather Motion 3:1 (slice1; segments: 3) + -> Partial Aggregate + -> Nested Loop + -> Broadcast Motion 3:3 (slice2; segments: 3) + -> Nested Loop + -> Seq Scan on r + -> Materialize + -> Broadcast Motion 3:3 (slice3; segments: 3) + -> Seq Scan on s + -> Materialize + -> Seq Scan on s t + SubPlan 1 + -> Result + Optimizer: Postgres query optimizer +(15 rows) + -- -- Test case for ORCA semi join with random table -- See https://github.com/greenplum-db/gpdb/issues/16611 diff --git a/src/test/regress/sql/catcache.sql b/src/test/regress/sql/catcache.sql index 0d8d0f8380..6d83116ea4 100644 --- a/src/test/regress/sql/catcache.sql +++ b/src/test/regress/sql/catcache.sql @@ -2,7 +2,7 @@ -- Discussion: https://groups.google.com/a/greenplum.org/g/gpdb-dev/c/u3-D7isdvmM set optimizer_force_multistage_agg = 1; - +SET optimizer_trace_fallback = on; create table dml_14027_union_s (a int not null, b numeric default 10.00) distributed by (a) partition by range(b); create table dml_14027_union_s_1_prt_2 partition of dml_14027_union_s for values from (1) to (1001); create table dml_14027_union_s_1_prt_def partition of dml_14027_union_s default; @@ -23,3 +23,4 @@ update dml_14027_union_s set a = (select null union select null)::numeric; drop table dml_14027_union_s; reset optimizer_force_multistage_agg; +reset optimizer_trace_fallback; \ No newline at end of file diff --git a/src/test/regress/sql/gp_covering_index.sql b/src/test/regress/sql/gp_covering_index.sql index fe33396aa6..defdbb2a12 100644 --- a/src/test/regress/sql/gp_covering_index.sql +++ b/src/test/regress/sql/gp_covering_index.sql @@ -308,16 +308,6 @@ VACUUM ANALYZE ao_pt; -- Allow dynamic index-only scan on mixed partitioned AO table EXPLAIN SELECT a FROM ao_pt WHERE a=29; - --- imitate child partition has GPDB 6 version file via catalog --- start_ignore -SET allow_system_table_mods=on; -UPDATE pg_appendonly SET version=1 WHERE relid='ao_pt_1_prt_3'::regclass; -RESET allow_system_table_mods; --- end_ignore - --- Disallow if the table contains child partition with GPDB 6 version -EXPLAIN SELECT a FROM ao_pt WHERE a=29; DROP TABLE ao_pt; -- AO/CO partitioned table contains a non-AO leaf partition @@ -334,18 +324,7 @@ VACUUM ANALYZE aocs_pt; -- Allow dynamic index-only scan on mixed partitioned AO/CO table EXPLAIN SELECT a FROM aocs_pt WHERE a=29; --- imitate child partition has GPDB 6 version file via catalog --- start_ignore -SET allow_system_table_mods=on; -UPDATE pg_appendonly SET version=1 WHERE relid='aocs_pt_1_prt_3'::regclass; -RESET allow_system_table_mods; --- end_ignore - --- Disallow if the table contains child partition with GPDB 6 version -EXPLAIN SELECT a FROM aocs_pt WHERE a=29; DROP TABLE aocs_pt; - - -- Test various index types -- -- Check that different index types can be used with cover indexes. --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
