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 5168873c3c698ccd619d857f04095008f2e4f0b2 Author: zhoujiaqi <[email protected]> AuthorDate: Fri Jan 17 10:31:50 2025 +0800 Fix icw test cases generted from cherry-pick DynamicForeignscan --- contrib/file_fdw/output/file_fdw_optimizer.source | 8 +- .../file_fdw/output/gp_file_fdw_optimizer.source | 2 +- .../isolation2/expected/lockmodes_optimizer.out | 32 ++- src/test/regress/expected/aggregates_optimizer.out | 2 +- src/test/regress/expected/bfv_index.out | 18 +- src/test/regress/expected/gp_aggregates_costs.out | 1 - .../expected/gp_aggregates_costs_optimizer.out | 1 - src/test/regress/expected/gporca_optimizer.out | 10 +- .../regress/expected/groupingsets_optimizer.out | 19 +- src/test/regress/expected/join_optimizer.out | 2 +- .../regress/expected/olap_window_seq_optimizer.out | 94 +++---- src/test/regress/expected/partition_optimizer.out | 16 +- .../regress/expected/partition_prune_optimizer.out | 16 +- .../regress/expected/qp_dropped_cols_optimizer.out | 2 +- src/test/regress/expected/qp_orca_fallback.out | 10 +- .../expected/qp_orca_fallback_optimizer.out | 14 +- .../regress/expected/subselect_gp_optimizer.out | 81 +++--- src/test/regress/expected/subselect_optimizer.out | 4 +- src/test/regress/input/part_external_table.source | 8 + src/test/regress/output/part_external_table.source | 279 +++++++++++---------- .../output/part_external_table_optimizer.source | 62 ++--- 21 files changed, 354 insertions(+), 327 deletions(-) diff --git a/contrib/file_fdw/output/file_fdw_optimizer.source b/contrib/file_fdw/output/file_fdw_optimizer.source index 7553e5f67c..c29e6b1856 100644 --- a/contrib/file_fdw/output/file_fdw_optimizer.source +++ b/contrib/file_fdw/output/file_fdw_optimizer.source @@ -314,6 +314,8 @@ CREATE FOREIGN TABLE p1 partition of pt for values in (1) SERVER file_server OPTIONS (format 'csv', filename '@abs_srcdir@/data/list1.csv', delimiter ','); CREATE TABLE p2 partition of pt for values in (2); SELECT tableoid::regclass, * FROM pt; +NOTICE: One or more columns in the following table(s) do not have statistics: pt +HINT: For non-partitioned tables, run analyze <table_name>(<column_list>). For partitioned tables, run analyze rootpartition <table_name>(<column_list>). See log for columns missing statistics. tableoid | a | b ----------+---+----- p1 | 1 | foo @@ -337,6 +339,8 @@ ERROR: cannot insert into foreign table "p1" CONTEXT: COPY pt, line 2: "1,qux" COPY pt FROM '@abs_srcdir@/data/list2.csv' with (format 'csv', delimiter ','); SELECT tableoid::regclass, * FROM pt; +NOTICE: One or more columns in the following table(s) do not have statistics: pt +HINT: For non-partitioned tables, run analyze <table_name>(<column_list>). For partitioned tables, run analyze rootpartition <table_name>(<column_list>). See log for columns missing statistics. tableoid | a | b ----------+---+----- p1 | 1 | foo @@ -368,9 +372,11 @@ INFO: GPORCA failed to produce a plan, falling back to planner DETAIL: Feature not supported: Insert with External/foreign partition storage types UPDATE pt set a = 1 where a = 2; -- ERROR INFO: GPORCA failed to produce a plan, falling back to planner -DETAIL: Feature not supported: Update with External/foreign partition storage types +DETAIL: Feature not supported: DML(update) on partitioned tables ERROR: cannot insert into foreign table "p1" (seg1 127.0.0.1:7008 pid=50091) SELECT tableoid::regclass, * FROM pt; +NOTICE: One or more columns in the following table(s) do not have statistics: pt +HINT: For non-partitioned tables, run analyze <table_name>(<column_list>). For partitioned tables, run analyze rootpartition <table_name>(<column_list>). See log for columns missing statistics. tableoid | a | b ----------+---+------- p1 | 1 | foo diff --git a/contrib/file_fdw/output/gp_file_fdw_optimizer.source b/contrib/file_fdw/output/gp_file_fdw_optimizer.source index 7276576c2b..87c319e9b0 100644 --- a/contrib/file_fdw/output/gp_file_fdw_optimizer.source +++ b/contrib/file_fdw/output/gp_file_fdw_optimizer.source @@ -1,5 +1,5 @@ -- --- Test foreign-data wrapper file_fdw. Greenplum MPP specific +-- Test foreign-data wrapper file_fdw. Cloudberry Database MPP specific -- -- Clean up in case a prior regression run failed SET client_min_messages TO 'error'; diff --git a/src/test/isolation2/expected/lockmodes_optimizer.out b/src/test/isolation2/expected/lockmodes_optimizer.out index ed247a8a1b..21d8e4ba87 100644 --- a/src/test/isolation2/expected/lockmodes_optimizer.out +++ b/src/test/isolation2/expected/lockmodes_optimizer.out @@ -1041,13 +1041,12 @@ BEGIN DELETE 10 -- on QD, there's a lock on the root and the target partition 1: select * from show_locks_lockmodes; - locktype | mode | granted | relation -----------+-----------------+---------+------------------------------------- - relation | ExclusiveLock | t | t_lockmods_part_tbl_upd_del_1_prt_1 - relation | AccessShareLock | t | t_lockmods_part_tbl_upd_del - relation | ExclusiveLock | t | t_lockmods_part_tbl_upd_del - relation | ExclusiveLock | t | t_lockmods_part_tbl_upd_del_1_prt_2 -(4 rows) + locktype | mode | granted | relation +----------+---------------+---------+------------------------------------- + relation | ExclusiveLock | t | t_lockmods_part_tbl_upd_del + relation | ExclusiveLock | t | t_lockmods_part_tbl_upd_del_1_prt_1 + relation | ExclusiveLock | t | t_lockmods_part_tbl_upd_del_1_prt_2 +(3 rows) 1: ROLLBACK; ROLLBACK @@ -1080,13 +1079,12 @@ BEGIN UPDATE 1 -- on QD, there's a lock on the root and the target partition 1: select * from show_locks_lockmodes; - locktype | mode | granted | relation -----------+-----------------+---------+------------------------------------- - relation | ExclusiveLock | t | t_lockmods_part_tbl_upd_del_1_prt_1 - relation | AccessShareLock | t | t_lockmods_part_tbl_upd_del - relation | ExclusiveLock | t | t_lockmods_part_tbl_upd_del - relation | ExclusiveLock | t | t_lockmods_part_tbl_upd_del_1_prt_2 -(4 rows) + locktype | mode | granted | relation +----------+---------------+---------+------------------------------------- + relation | ExclusiveLock | t | t_lockmods_part_tbl_upd_del + relation | ExclusiveLock | t | t_lockmods_part_tbl_upd_del_1_prt_1 + relation | ExclusiveLock | t | t_lockmods_part_tbl_upd_del_1_prt_2 +(3 rows) 1: ROLLBACK; ROLLBACK 1q: ... <quitting> @@ -2135,9 +2133,8 @@ DELETE 10 ----------+------------------+---------+------------------------------------- relation | RowExclusiveLock | t | t_lockmods_part_tbl_upd_del_1_prt_2 relation | RowExclusiveLock | t | t_lockmods_part_tbl_upd_del_1_prt_1 - relation | AccessShareLock | t | t_lockmods_part_tbl_upd_del relation | RowExclusiveLock | t | t_lockmods_part_tbl_upd_del -(4 rows) +(3 rows) 1: ROLLBACK; ROLLBACK 1q: ... <quitting> @@ -2152,9 +2149,8 @@ UPDATE 1 ----------+------------------+---------+------------------------------------- relation | RowExclusiveLock | t | t_lockmods_part_tbl_upd_del_1_prt_2 relation | RowExclusiveLock | t | t_lockmods_part_tbl_upd_del_1_prt_1 - relation | AccessShareLock | t | t_lockmods_part_tbl_upd_del relation | RowExclusiveLock | t | t_lockmods_part_tbl_upd_del -(4 rows) +(3 rows) 1: ROLLBACK; ROLLBACK 1q: ... <quitting> diff --git a/src/test/regress/expected/aggregates_optimizer.out b/src/test/regress/expected/aggregates_optimizer.out index 842b35fd0e..9328790ecb 100644 --- a/src/test/regress/expected/aggregates_optimizer.out +++ b/src/test/regress/expected/aggregates_optimizer.out @@ -1424,7 +1424,7 @@ explain (costs off) select * from p_t1 group by a,b,c,d; ------------------------------------------ Gather Motion 3:1 (slice1; segments: 3) -> Dynamic Seq Scan on p_t1 - Number of partitions to scan: 2 + Number of partitions to scan: 2 (out of 2) Optimizer: Pivotal Optimizer (GPORCA) (4 rows) diff --git a/src/test/regress/expected/bfv_index.out b/src/test/regress/expected/bfv_index.out index 75ca7f3b53..bb8a63f38c 100644 --- a/src/test/regress/expected/bfv_index.out +++ b/src/test/regress/expected/bfv_index.out @@ -999,23 +999,23 @@ SET enable_seqscan = OFF; SET optimizer_enable_dynamictablescan =OFF; EXPLAIN (COSTS OFF) SELECT * FROM hash_prt_tbl WHERE b=3; - QUERY PLAN ------------------------------------------------------------------------------------ + QUERY PLAN +-------------------------------------------------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) -> Append - -> Index Scan using hash_prt_tbl_1_prt_p1_b_idx on hash_prt_tbl_1_prt_p1 + -> Index Scan using hash_prt_tbl_1_prt_p1_b_idx on hash_prt_tbl_1_prt_p1 hash_prt_tbl_1 Index Cond: (b = 3) - -> Index Scan using hash_prt_tbl_1_prt_p2_b_idx on hash_prt_tbl_1_prt_p2 + -> Index Scan using hash_prt_tbl_1_prt_p2_b_idx on hash_prt_tbl_1_prt_p2 hash_prt_tbl_2 Index Cond: (b = 3) Optimizer: Postgres query optimizer (7 rows) EXPLAIN (COSTS OFF) SELECT * FROM hash_prt_tbl WHERE b=3 and a=3; - QUERY PLAN ------------------------------------------------------------------------------ + QUERY PLAN +------------------------------------------------------------------------------------------ Gather Motion 3:1 (slice1; segments: 3) - -> Index Scan using hash_prt_tbl_1_prt_p1_b_idx on hash_prt_tbl_1_prt_p1 + -> Index Scan using hash_prt_tbl_1_prt_p1_b_idx on hash_prt_tbl_1_prt_p1 hash_prt_tbl Index Cond: (b = 3) Filter: (a = 3) Optimizer: Postgres query optimizer @@ -1027,14 +1027,14 @@ SELECT * FROM hash_prt_tbl WHERE b=3 or b=5; -------------------------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) -> Append - -> Bitmap Heap Scan on hash_prt_tbl_1_prt_p1 + -> Bitmap Heap Scan on hash_prt_tbl_1_prt_p1 hash_prt_tbl_1 Recheck Cond: ((b = 3) OR (b = 5)) -> BitmapOr -> Bitmap Index Scan on hash_prt_tbl_1_prt_p1_b_idx Index Cond: (b = 3) -> Bitmap Index Scan on hash_prt_tbl_1_prt_p1_b_idx Index Cond: (b = 5) - -> Bitmap Heap Scan on hash_prt_tbl_1_prt_p2 + -> Bitmap Heap Scan on hash_prt_tbl_1_prt_p2 hash_prt_tbl_2 Recheck Cond: ((b = 3) OR (b = 5)) -> BitmapOr -> Bitmap Index Scan on hash_prt_tbl_1_prt_p2_b_idx diff --git a/src/test/regress/expected/gp_aggregates_costs.out b/src/test/regress/expected/gp_aggregates_costs.out index 112c0bc857..957ebfa1b0 100644 --- a/src/test/regress/expected/gp_aggregates_costs.out +++ b/src/test/regress/expected/gp_aggregates_costs.out @@ -130,7 +130,6 @@ select count(*) from test_operator_mem; (5 rows) abort; - -- Test user-defined aggregate marked safe to execute on replicated slices without motion CREATE AGGREGATE my_unsafe_avg (float8) ( diff --git a/src/test/regress/expected/gp_aggregates_costs_optimizer.out b/src/test/regress/expected/gp_aggregates_costs_optimizer.out index 5d3dd96a45..920754ae5c 100644 --- a/src/test/regress/expected/gp_aggregates_costs_optimizer.out +++ b/src/test/regress/expected/gp_aggregates_costs_optimizer.out @@ -130,7 +130,6 @@ select count(*) from test_operator_mem; (5 rows) abort; - -- Test user-defined aggregate marked safe to execute on replicated slices without motion CREATE AGGREGATE my_unsafe_avg (float8) ( diff --git a/src/test/regress/expected/gporca_optimizer.out b/src/test/regress/expected/gporca_optimizer.out index 80e4cb5d08..f443584318 100644 --- a/src/test/regress/expected/gporca_optimizer.out +++ b/src/test/regress/expected/gporca_optimizer.out @@ -9895,8 +9895,8 @@ select c.cid cid, from cust c, sales s, datedim d where c.cid = s.cid and s.date_sk = d.date_sk and ((d.year = 2001 and lower(s.type) = 't1' and plusone(d.moy) = 5) or (d.moy = 4 and upper(s.type) = 'T2')); - QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- + QUERY PLAN +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..1293.00 rows=1 width=24) -> Hash Join (cost=0.00..1293.00 rows=1 width=24) Hash Cond: (cust.cid = sales.cid) @@ -14423,7 +14423,7 @@ ON t1.tradingday = t2.tradingday; Hash Key: t_clientproductind2.tradingday -> Dynamic Index Scan on t_clientproductind2_pkey on t_clientproductind2 Index Cond: (((tradingday)::text >= '20190715'::text) AND ((tradingday)::text <= '20190715'::text) AND ((tradingday)::text = '20190715'::text)) - Number of partitions to scan: 1 + Number of partitions to scan: 1 (out of 2) -> Materialize -> GroupAggregate Group Key: t_clientinstrumentind2.tradingday @@ -14433,9 +14433,9 @@ ON t1.tradingday = t2.tradingday; Hash Key: t_clientinstrumentind2.tradingday -> Dynamic Index Scan on t_clientinstrumentind2_pkey on t_clientinstrumentind2 Index Cond: ((tradingday >= '20190715'::text) AND (tradingday <= '20190715'::text) AND (tradingday = '20190715'::text)) - Number of partitions to scan: 1 + Number of partitions to scan: 1 (out of 2) Optimizer: Pivotal Optimizer (GPORCA) -(22 rows) +(23 rows) RESET optimizer_enable_hashjoin; SELECT * FROM( diff --git a/src/test/regress/expected/groupingsets_optimizer.out b/src/test/regress/expected/groupingsets_optimizer.out index 979383088b..403c1fbb63 100644 --- a/src/test/regress/expected/groupingsets_optimizer.out +++ b/src/test/regress/expected/groupingsets_optimizer.out @@ -916,8 +916,8 @@ select a,count(*) from gstest2 group by rollup(a) having a is distinct from 1 or explain (costs off) select a,count(*) from gstest2 group by rollup(a) having a is distinct from 1 order by a; - QUERY PLAN ----------------------------------------------------------------------------------------- + QUERY PLAN +---------------------------------------------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) Merge Key: (NULL::integer) -> Sort @@ -926,13 +926,14 @@ explain (costs off) -> Shared Scan (share slice:id 1:0) -> Seq Scan on gstest2 -> Append - -> Redistribute Motion 1:3 (slice2) + -> Result + Filter: ((NULL::integer) IS DISTINCT FROM 1) -> Result - Filter: ((NULL::integer) IS DISTINCT FROM 1) - -> Finalize Aggregate - -> Gather Motion 3:1 (slice3; segments: 3) - -> Partial Aggregate - -> Shared Scan (share slice:id 3:0) + -> Redistribute Motion 1:3 (slice2) + -> Finalize Aggregate + -> Gather Motion 3:1 (slice3; segments: 3) + -> Partial Aggregate + -> Shared Scan (share slice:id 3:0) -> GroupAggregate Group Key: share0_ref3.a -> Sort @@ -941,7 +942,7 @@ explain (costs off) Filter: (share0_ref3.a IS DISTINCT FROM 1) -> Shared Scan (share slice:id 1:0) Optimizer: Pivotal Optimizer (GPORCA) -(23 rows) +(24 rows) select v.c, (select count(*) from gstest2 group by () having v.c) from (values (false),(true)) v(c) order by v.c; diff --git a/src/test/regress/expected/join_optimizer.out b/src/test/regress/expected/join_optimizer.out index 1c7b3a3d03..556ae39fed 100644 --- a/src/test/regress/expected/join_optimizer.out +++ b/src/test/regress/expected/join_optimizer.out @@ -5475,7 +5475,7 @@ where ss.a = ss.phv and f1 = 0; Filter: (((12) = 12) AND (parttbl.a = (12))) -> Dynamic Index Scan on parttbl_pkey on parttbl Index Cond: (a = 12) - Number of partitions to scan: 1 + Number of partitions to scan: 1 (out of 1) -> Materialize -> Broadcast Motion 3:3 (slice2; segments: 3) -> Seq Scan on int4_tbl diff --git a/src/test/regress/expected/olap_window_seq_optimizer.out b/src/test/regress/expected/olap_window_seq_optimizer.out index bbf392507b..bd79551f22 100644 --- a/src/test/regress/expected/olap_window_seq_optimizer.out +++ b/src/test/regress/expected/olap_window_seq_optimizer.out @@ -8228,54 +8228,62 @@ select k from ( select row_number() over()+2 as k from window_preds union all se (2 rows) explain insert into window_preds select k from ( select row_number() over()+2 as k from window_preds union all select row_number() over()+2 as k from window_preds) as t where k = 3; - QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------ + QUERY PLAN +----------------------------------------------------------------------------------------------------------------------------------------- Insert on window_preds (cost=0.00..862.04 rows=1 width=4) - -> Result (cost=0.00..0.00 rows=0 width=0) - -> Redistribute Motion 1:3 (slice1; segments: 1) (cost=0.00..862.00 rows=2 width=16) - Hash Key: (int4(((row_number() OVER (?) + 2)))) - -> Result (cost=0.00..862.00 rows=2 width=16) - -> Append (cost=0.00..862.00 rows=2 width=8) + -> Result (cost=0.00..862.00 rows=2 width=16) + -> Redistribute Motion 3:3 (slice1; segments: 3) (cost=0.00..862.00 rows=2 width=12) + Hash Key: (((((row_number() OVER (?)) + 2)))::integer) + -> Result (cost=0.00..862.00 rows=1 width=12) + -> Append (cost=0.00..862.00 rows=1 width=8) -> Result (cost=0.00..431.00 rows=1 width=8) - Filter: (((row_number() OVER (?) + 2)) = 3) - -> WindowAgg (cost=0.00..431.00 rows=1 width=8) - -> Gather Motion 3:1 (slice2; segments: 3) (cost=0.00..431.00 rows=1 width=1) - -> Seq Scan on window_preds window_preds_1 (cost=0.00..431.00 rows=1 width=1) + Filter: ((((row_number() OVER (?)) + 2)) = 3) + -> Result (cost=0.00..431.00 rows=1 width=8) + -> Redistribute Motion 1:3 (slice2; segments: 1) (cost=0.00..431.00 rows=1 width=8) + -> WindowAgg (cost=0.00..431.00 rows=1 width=8) + -> Gather Motion 3:1 (slice3; segments: 3) (cost=0.00..431.00 rows=1 width=1) + -> Seq Scan on window_preds window_preds_1 (cost=0.00..431.00 rows=1 width=1) -> Result (cost=0.00..431.00 rows=1 width=8) - Filter: (((row_number() OVER (?) + 2)) = 3) - -> WindowAgg (cost=0.00..431.00 rows=1 width=8) - -> Gather Motion 3:1 (slice3; segments: 3) (cost=0.00..431.00 rows=1 width=1) - -> Seq Scan on window_preds window_preds_2 (cost=0.00..431.00 rows=1 width=1) + Filter: ((((row_number() OVER (?)) + 2)) = 3) + -> Result (cost=0.00..431.00 rows=1 width=8) + -> Redistribute Motion 1:3 (slice4; segments: 1) (cost=0.00..431.00 rows=1 width=8) + -> WindowAgg (cost=0.00..431.00 rows=1 width=8) + -> Gather Motion 3:1 (slice5; segments: 3) (cost=0.00..431.00 rows=1 width=1) + -> Seq Scan on window_preds window_preds_2 (cost=0.00..431.00 rows=1 width=1) Optimizer: Pivotal Optimizer (GPORCA) -(17 rows) +(21 rows) insert into window_preds select k from ( select row_number() over()+2 as k from window_preds union all select row_number() over()+2 as k from window_preds) as t where k = 3; explain SELECT t.k FROM window_preds p1, window_preds p2, (SELECT ROW_NUMBER() OVER() AS k FROM window_preds union all SELECT ROW_NUMBER() OVER() AS k FROM window_preds) AS t WHERE t.k = 1 limit 1; - QUERY PLAN ------------------------------------------------------------------------------------------------------------------ - Limit (cost=0.00..1357132839.11 rows=1 width=8) - -> Nested Loop (cost=0.00..1357132839.11 rows=2 width=8) - Join Filter: true - -> Append (cost=0.00..862.00 rows=2 width=8) - -> Result (cost=0.00..431.00 rows=1 width=8) - Filter: ((row_number() OVER (?)) = 1) - -> WindowAgg (cost=0.00..431.00 rows=1 width=8) - -> Gather Motion 3:1 (slice3; segments: 3) (cost=0.00..431.00 rows=1 width=1) - -> Seq Scan on window_preds window_preds_2 (cost=0.00..431.00 rows=1 width=1) - -> Result (cost=0.00..431.00 rows=1 width=8) - Filter: ((row_number() OVER (?)) = 1) - -> WindowAgg (cost=0.00..431.00 rows=1 width=8) - -> Gather Motion 3:1 (slice4; segments: 3) (cost=0.00..431.00 rows=1 width=1) - -> Seq Scan on window_preds window_preds_3 (cost=0.00..431.00 rows=1 width=1) - -> Materialize (cost=0.00..1324032.04 rows=1 width=1) - -> Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..1324032.04 rows=1 width=1) - -> Nested Loop (cost=0.00..1324032.04 rows=1 width=1) - Join Filter: true - -> Broadcast Motion 3:3 (slice2; segments: 3) (cost=0.00..431.00 rows=1 width=1) - -> Seq Scan on window_preds window_preds_1 (cost=0.00..431.00 rows=1 width=1) - -> Seq Scan on window_preds (cost=0.00..431.00 rows=1 width=1) + QUERY PLAN +----------------------------------------------------------------------------------------------------------------------------------- + Limit (cost=0.00..1357132840.60 rows=1 width=8) + -> Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..1357132840.60 rows=1 width=8) + -> Limit (cost=0.00..1357132840.60 rows=1 width=8) + -> Nested Loop (cost=0.00..1357132840.60 rows=1 width=8) + Join Filter: true + -> Append (cost=0.00..862.00 rows=1 width=8) + -> Result (cost=0.00..431.00 rows=1 width=8) + Filter: ((row_number() OVER (?)) = 1) + -> Redistribute Motion 1:3 (slice4) (cost=0.00..431.00 rows=1 width=8) + -> WindowAgg (cost=0.00..431.00 rows=1 width=8) + -> Gather Motion 3:1 (slice5; segments: 3) (cost=0.00..431.00 rows=1 width=1) + -> Seq Scan on window_preds window_preds_2 (cost=0.00..431.00 rows=1 width=1) + -> Result (cost=0.00..431.00 rows=1 width=8) + Filter: ((row_number() OVER (?)) = 1) + -> Redistribute Motion 1:3 (slice6) (cost=0.00..431.00 rows=1 width=8) + -> WindowAgg (cost=0.00..431.00 rows=1 width=8) + -> Gather Motion 3:1 (slice7; segments: 3) (cost=0.00..431.00 rows=1 width=1) + -> Seq Scan on window_preds window_preds_3 (cost=0.00..431.00 rows=1 width=1) + -> Materialize (cost=0.00..1324032.04 rows=1 width=1) + -> Broadcast Motion 3:3 (slice2; segments: 3) (cost=0.00..1324032.04 rows=1 width=1) + -> Nested Loop (cost=0.00..1324032.04 rows=1 width=1) + Join Filter: true + -> Broadcast Motion 3:3 (slice3; segments: 3) (cost=0.00..431.00 rows=1 width=1) + -> Seq Scan on window_preds window_preds_1 (cost=0.00..431.00 rows=1 width=1) + -> Seq Scan on window_preds (cost=0.00..431.00 rows=1 width=1) Optimizer: Pivotal Optimizer (GPORCA) -(22 rows) +(26 rows) SELECT t.k FROM window_preds p1, window_preds p2, (SELECT ROW_NUMBER() OVER() AS k FROM window_preds union all SELECT ROW_NUMBER() OVER() AS k FROM window_preds) AS t WHERE t.k = 1 limit 1; k @@ -8364,9 +8372,9 @@ explain insert into window_preds select k from ( select k from (select row_numbe Hash Key: (((row_number() OVER (?)))::integer) -> Result (cost=0.00..862.00 rows=1 width=12) -> Append (cost=0.00..862.00 rows=1 width=8) - -> Redistribute Motion 1:3 (slice2; segments: 1) (cost=0.00..431.00 rows=1 width=8) - -> Result (cost=0.00..431.00 rows=1 width=8) - Filter: ((row_number() OVER (?)) = 1) + -> Result (cost=0.00..431.00 rows=1 width=8) + Filter: ((row_number() OVER (?)) = 1) + -> Redistribute Motion 1:3 (slice2; segments: 1) (cost=0.00..431.00 rows=1 width=8) -> WindowAgg (cost=0.00..431.00 rows=1 width=8) -> Gather Motion 3:1 (slice3; segments: 3) (cost=0.00..431.00 rows=1 width=1) -> Seq Scan on window_preds window_preds_1 (cost=0.00..431.00 rows=1 width=1) diff --git a/src/test/regress/expected/partition_optimizer.out b/src/test/regress/expected/partition_optimizer.out index 4e6270642d..01d7e20b2b 100755 --- a/src/test/regress/expected/partition_optimizer.out +++ b/src/test/regress/expected/partition_optimizer.out @@ -6404,7 +6404,7 @@ HINT: For non-partitioned tables, run analyze <table_name>(<column_list>). For -> Gather Motion 3:1 (slice1; segments: 3) -> Partial Aggregate -> Dynamic Seq Scan on test_rangepartition - Number of partitions to scan: 2 + Number of partitions to scan: 2 (out of 3) Filter: ((datedday = '10-23-2022'::date) OR (datedday = 'Sat Oct 22 00:00:00 2022'::timestamp without time zone)) Optimizer: Pivotal Optimizer (GPORCA) (7 rows) @@ -6427,7 +6427,7 @@ HINT: For non-partitioned tables, run analyze <table_name>(<column_list>). For -> Gather Motion 3:1 (slice1; segments: 3) -> Partial Aggregate -> Dynamic Seq Scan on test_rangepartition - Number of partitions to scan: 2 + Number of partitions to scan: 2 (out of 3) Filter: ((datedday = '10-23-2022'::date) OR (datedday = '10-22-2022'::date)) Optimizer: Pivotal Optimizer (GPORCA) (7 rows) @@ -6450,7 +6450,7 @@ HINT: For non-partitioned tables, run analyze <table_name>(<column_list>). For -> Gather Motion 3:1 (slice1; segments: 3) -> Partial Aggregate -> Dynamic Seq Scan on test_rangepartition - Number of partitions to scan: 2 + Number of partitions to scan: 2 (out of 3) Filter: ((datedday = 'Sun Oct 23 00:00:00 2022'::timestamp without time zone) OR (datedday = 'Sat Oct 22 00:00:00 2022'::timestamp without time zone)) Optimizer: Pivotal Optimizer (GPORCA) (7 rows) @@ -6471,7 +6471,7 @@ HINT: For non-partitioned tables, run analyze <table_name>(<column_list>). For --------------------------------------------------------------------------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) -> Dynamic Seq Scan on test_rangepartition - Number of partitions to scan: 2 + Number of partitions to scan: 2 (out of 3) Filter: ((datedday = '10-23-2022'::date) OR (datedday = 'Sat Oct 22 00:00:00 2022'::timestamp without time zone)) Optimizer: Pivotal Optimizer (GPORCA) (5 rows) @@ -6507,7 +6507,7 @@ HINT: For non-partitioned tables, run analyze <table_name>(<column_list>). For Aggregate -> Gather Motion 3:1 (slice1; segments: 3) -> Dynamic Seq Scan on test_listpartition - Number of partitions to scan: 2 + Number of partitions to scan: 2 (out of 3) Filter: ((d = '10-23-2022'::date) OR (d = 'Sat Oct 22 00:00:00 2022'::timestamp without time zone)) Optimizer: Pivotal Optimizer (GPORCA) (6 rows) @@ -6529,7 +6529,7 @@ HINT: For non-partitioned tables, run analyze <table_name>(<column_list>). For Aggregate -> Gather Motion 3:1 (slice1; segments: 3) -> Dynamic Seq Scan on test_listpartition - Number of partitions to scan: 2 + Number of partitions to scan: 2 (out of 3) Filter: ((d = '10-23-2022'::date) OR (d = '10-22-2022'::date)) Optimizer: Pivotal Optimizer (GPORCA) (6 rows) @@ -6551,7 +6551,7 @@ HINT: For non-partitioned tables, run analyze <table_name>(<column_list>). For Aggregate -> Gather Motion 3:1 (slice1; segments: 3) -> Dynamic Seq Scan on test_listpartition - Number of partitions to scan: 2 + Number of partitions to scan: 2 (out of 3) Filter: ((d = 'Sun Oct 23 00:00:00 2022'::timestamp without time zone) OR (d = 'Sat Oct 22 00:00:00 2022'::timestamp without time zone)) Optimizer: Pivotal Optimizer (GPORCA) (6 rows) @@ -6572,7 +6572,7 @@ HINT: For non-partitioned tables, run analyze <table_name>(<column_list>). For ------------------------------------------------------------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) -> Dynamic Seq Scan on test_listpartition - Number of partitions to scan: 2 + Number of partitions to scan: 2 (out of 3) Filter: ((d = '10-23-2022'::date) OR (d = 'Sat Oct 22 00:00:00 2022'::timestamp without time zone)) Optimizer: Pivotal Optimizer (GPORCA) (5 rows) diff --git a/src/test/regress/expected/partition_prune_optimizer.out b/src/test/regress/expected/partition_prune_optimizer.out index 4f0fb12ca1..f7d6a479f4 100644 --- a/src/test/regress/expected/partition_prune_optimizer.out +++ b/src/test/regress/expected/partition_prune_optimizer.out @@ -1336,7 +1336,7 @@ explain (costs off) select * from coercepart where a = any ('{ab,bc}'); ------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) -> Dynamic Seq Scan on coercepart - Number of partitions to scan: 2 + Number of partitions to scan: 2 (out of 3) Filter: ((a)::text = ANY ('{ab,bc}'::text[])) Optimizer: Pivotal Optimizer (GPORCA) (5 rows) @@ -1346,7 +1346,7 @@ explain (costs off) select * from coercepart where a = any ('{ab,null}'); --------------------------------------------------------- Gather Motion 1:1 (slice1; segments: 1) -> Dynamic Seq Scan on coercepart - Number of partitions to scan: 1 + Number of partitions to scan: 1 (out of 3) Filter: ((a)::text = ANY ('{ab,NULL}'::text[])) Optimizer: Pivotal Optimizer (GPORCA) (5 rows) @@ -1356,7 +1356,7 @@ explain (costs off) select * from coercepart where a = any (null::text[]); -------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) -> Dynamic Seq Scan on coercepart - Number of partitions to scan: 3 + Number of partitions to scan: 3 (out of 3) Filter: ((a)::text = ANY (NULL::text[])) Optimizer: Pivotal Optimizer (GPORCA) (5 rows) @@ -1366,7 +1366,7 @@ explain (costs off) select * from coercepart where a = all ('{ab}'); ---------------------------------------------------- Gather Motion 1:1 (slice1; segments: 1) -> Dynamic Seq Scan on coercepart - Number of partitions to scan: 1 + Number of partitions to scan: 1 (out of 3) Filter: ((a)::text = ALL ('{ab}'::text[])) Optimizer: Pivotal Optimizer (GPORCA) (5 rows) @@ -1376,7 +1376,7 @@ explain (costs off) select * from coercepart where a = all ('{ab,bc}'); ------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) -> Dynamic Seq Scan on coercepart - Number of partitions to scan: 2 + Number of partitions to scan: 2 (out of 3) Filter: ((a)::text = ALL ('{ab,bc}'::text[])) Optimizer: Pivotal Optimizer (GPORCA) (5 rows) @@ -1386,7 +1386,7 @@ explain (costs off) select * from coercepart where a = all ('{ab,null}'); --------------------------------------------------------- Gather Motion 1:1 (slice1; segments: 1) -> Dynamic Seq Scan on coercepart - Number of partitions to scan: 1 + Number of partitions to scan: 1 (out of 3) Filter: ((a)::text = ALL ('{ab,NULL}'::text[])) Optimizer: Pivotal Optimizer (GPORCA) (5 rows) @@ -1396,7 +1396,7 @@ explain (costs off) select * from coercepart where a = all (null::text[]); -------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) -> Dynamic Seq Scan on coercepart - Number of partitions to scan: 3 + Number of partitions to scan: 3 (out of 3) Filter: ((a)::text = ALL (NULL::text[])) Optimizer: Pivotal Optimizer (GPORCA) (5 rows) @@ -3224,7 +3224,7 @@ select * from stable_qual_pruning ------------------------------------------------------------------------ Gather Motion 3:1 (slice1; segments: 3) (actual rows=0 loops=1) -> Dynamic Seq Scan on stable_qual_pruning (actual rows=0 loops=1) - Number of partitions to scan: 3 + Number of partitions to scan: 3 (out of 3) Filter: (a = ANY (NULL::timestamp with time zone[])) Partitions scanned: Avg 3.0 x 3 workers. Max 3 parts (seg0). Optimizer: Pivotal Optimizer (GPORCA) diff --git a/src/test/regress/expected/qp_dropped_cols_optimizer.out b/src/test/regress/expected/qp_dropped_cols_optimizer.out index 6fb8176680..cd08375b06 100644 --- a/src/test/regress/expected/qp_dropped_cols_optimizer.out +++ b/src/test/regress/expected/qp_dropped_cols_optimizer.out @@ -14987,7 +14987,7 @@ EXPLAIN (costs off) SELECT * FROM ds_main, non_part2 WHERE ds_main.c = non_part2 -> Hash Join Hash Cond: (ds_main.c = non_part2.e) -> Dynamic Seq Scan on ds_main - Number of partitions to scan: 6 + Number of partitions to scan: 6 (out of 6) Filter: ((a = a) AND (SubPlan 1)) SubPlan 1 -> Materialize diff --git a/src/test/regress/expected/qp_orca_fallback.out b/src/test/regress/expected/qp_orca_fallback.out index 1b5e270598..7b1cfee813 100644 --- a/src/test/regress/expected/qp_orca_fallback.out +++ b/src/test/regress/expected/qp_orca_fallback.out @@ -265,11 +265,11 @@ explain insert into ext_part values (1); (3 rows) explain delete from ext_part where a=1; - QUERY PLAN ------------------------------------------------------------- - Delete on ext_part (cost=0.00..435.25 rows=32 width=10) - Delete on p1 - -> Seq Scan on p1 (cost=0.00..435.25 rows=32 width=10) + QUERY PLAN +----------------------------------------------------------------------- + Delete on ext_part (cost=0.00..435.25 rows=0 width=0) + Delete on p1 ext_part_1 + -> Seq Scan on p1 ext_part_1 (cost=0.00..435.25 rows=32 width=14) Filter: (a = 1) Optimizer: Postgres query optimizer (5 rows) diff --git a/src/test/regress/expected/qp_orca_fallback_optimizer.out b/src/test/regress/expected/qp_orca_fallback_optimizer.out index 9d0b306cf5..1d740857d9 100644 --- a/src/test/regress/expected/qp_orca_fallback_optimizer.out +++ b/src/test/regress/expected/qp_orca_fallback_optimizer.out @@ -318,17 +318,17 @@ DETAIL: Feature not supported: Insert with External/foreign partition storage t explain delete from ext_part where a=1; INFO: GPORCA failed to produce a plan, falling back to planner -DETAIL: Feature not supported: Delete with External/foreign partition storage types - QUERY PLAN ------------------------------------------------------------- - Delete on ext_part (cost=0.00..435.25 rows=32 width=10) - Delete on p1 - -> Seq Scan on p1 (cost=0.00..435.25 rows=32 width=10) +DETAIL: Feature not supported: DML(delete) on partitioned tables + QUERY PLAN +----------------------------------------------------------------------- + Delete on ext_part (cost=0.00..435.25 rows=0 width=0) + Delete on p1 ext_part_1 + -> Seq Scan on p1 ext_part_1 (cost=0.00..435.25 rows=32 width=14) Filter: (a = 1) Optimizer: Postgres query optimizer (5 rows) explain update ext_part set a=1; INFO: GPORCA failed to produce a plan, falling back to planner -DETAIL: Feature not supported: Update with External/foreign partition storage types +DETAIL: Feature not supported: DML(update) on partitioned tables ERROR: cannot update foreign table "p2_ext" diff --git a/src/test/regress/expected/subselect_gp_optimizer.out b/src/test/regress/expected/subselect_gp_optimizer.out index a62f979f84..f6e8ea53bd 100644 --- a/src/test/regress/expected/subselect_gp_optimizer.out +++ b/src/test/regress/expected/subselect_gp_optimizer.out @@ -2973,8 +2973,8 @@ explain (verbose, costs off) select * from ( ) run_dt, extra_flow_dist1 where dt < '2010-01-01'::date; - QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------------- + QUERY PLAN +-------------------------------------------------------------------------------------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) Output: ((SubPlan 1)), extra_flow_dist1.a, extra_flow_dist1.b -> Nested Loop @@ -2993,53 +2993,48 @@ where dt < '2010-01-01'::date; -> Redistribute Motion 3:3 (slice2; segments: 3) Output: ((SubPlan 1)) Hash Key: ((SubPlan 1)) - -> GroupAggregate - Output: ((SubPlan 1)) - Group Key: ((SubPlan 1)) - -> Sort + -> Append + -> Result Output: ((SubPlan 1)) - Sort Key: ((SubPlan 1)) - -> Append - -> Redistribute Motion 1:3 (slice3) - Output: ((SubPlan 1)) + Filter: (((SubPlan 1)) < '01-01-2010'::date) + -> Redistribute Motion 1:3 (slice3) + Output: ((SubPlan 1)) + -> Aggregate + Output: (SubPlan 1) -> Result - Output: ((SubPlan 1)) - Filter: (((SubPlan 1)) < '01-01-2010'::date) - -> Aggregate - Output: (SubPlan 1) - -> Result - Output: true - SubPlan 1 - -> Result - Output: extra_flow_dist.c - Filter: (extra_flow_dist.b = max(1)) - -> Materialize + Output: true + SubPlan 1 + -> Result + Output: extra_flow_dist.c + Filter: (extra_flow_dist.b = max(1)) + -> Materialize + Output: extra_flow_dist.b, extra_flow_dist.c + -> Gather Motion 3:1 (slice4; segments: 3) + Output: extra_flow_dist.b, extra_flow_dist.c + -> Seq Scan on subselect_gp.extra_flow_dist Output: extra_flow_dist.b, extra_flow_dist.c - -> Gather Motion 3:1 (slice4; segments: 3) - Output: extra_flow_dist.b, extra_flow_dist.c - -> Seq Scan on subselect_gp.extra_flow_dist - Output: extra_flow_dist.b, extra_flow_dist.c - -> Redistribute Motion 1:3 (slice5) - Output: ((SubPlan 2)) + -> Result + Output: ((SubPlan 2)) + Filter: (((SubPlan 2)) < '01-01-2010'::date) + -> Redistribute Motion 1:3 (slice5) + Output: ((SubPlan 2)) + -> Aggregate + Output: (SubPlan 2) -> Result - Output: ((SubPlan 2)) - Filter: (((SubPlan 2)) < '01-01-2010'::date) - -> Aggregate - Output: (SubPlan 2) - -> Result - Output: true - SubPlan 2 - -> Result - Output: extra_flow_dist_1.c - Filter: (extra_flow_dist_1.b = max(1)) - -> Materialize + Output: true + SubPlan 2 + -> Result + Output: extra_flow_dist_1.c + Filter: (extra_flow_dist_1.b = max(1)) + -> Materialize + Output: extra_flow_dist_1.b, extra_flow_dist_1.c + -> Gather Motion 3:1 (slice6; segments: 3) + Output: extra_flow_dist_1.b, extra_flow_dist_1.c + -> Seq Scan on subselect_gp.extra_flow_dist extra_flow_dist_1 Output: extra_flow_dist_1.b, extra_flow_dist_1.c - -> Gather Motion 3:1 (slice6; segments: 3) - Output: extra_flow_dist_1.b, extra_flow_dist_1.c - -> Seq Scan on subselect_gp.extra_flow_dist extra_flow_dist_1 - Output: extra_flow_dist_1.b, extra_flow_dist_1.c + Settings: enable_parallel = 'off', optimizer = 'on' Optimizer: Pivotal Optimizer (GPORCA) -(64 rows) +(59 rows) -- Check DISTINCT ON clause and ORDER BY clause in SubLink, See https://github.com/greenplum-db/gpdb/issues/12656. -- For EXISTS SubLink, we don’t need to care about the data deduplication problem, we can delete DISTINCT ON clause and diff --git a/src/test/regress/expected/subselect_optimizer.out b/src/test/regress/expected/subselect_optimizer.out index bc15d00806..f796d9a00b 100644 --- a/src/test/regress/expected/subselect_optimizer.out +++ b/src/test/regress/expected/subselect_optimizer.out @@ -1029,7 +1029,7 @@ select * from exists_tbl t1 -> Hash Left Join Hash Cond: (exists_tbl.c1 = exists_tbl_1.c2) -> Dynamic Seq Scan on exists_tbl - Number of partitions to scan: 2 + Number of partitions to scan: 2 (out of 2) -> Hash -> GroupAggregate Group Key: exists_tbl_1.c2 @@ -1038,7 +1038,7 @@ select * from exists_tbl t1 -> Redistribute Motion 3:3 (slice2; segments: 3) Hash Key: exists_tbl_1.c2 -> Dynamic Seq Scan on exists_tbl exists_tbl_1 - Number of partitions to scan: 2 + Number of partitions to scan: 2 (out of 2) Optimizer: Pivotal Optimizer (GPORCA) (17 rows) diff --git a/src/test/regress/input/part_external_table.source b/src/test/regress/input/part_external_table.source index 6f4ee3f5f1..50550944c2 100644 --- a/src/test/regress/input/part_external_table.source +++ b/src/test/regress/input/part_external_table.source @@ -45,14 +45,22 @@ select * from part where b>10 and a>0; create table non_part (a int, b int) distributed by (a); insert into non_part values (15,15); +analyze non_part; -- mixed partitions with DPE with multiple dynamic scans, select one partition +-- start_ignore +-- unstable test case explain analyze select * from part, non_part where part.b=non_part.b; +--end_ignore select * from part, non_part where part.b=non_part.b; insert into non_part values (5,5); +analyze non_part; -- mixed partitions with DPE with multiple dynamic scans, select both partition +-- start_ignore +-- unstable test case explain analyze select * from part, non_part where part.b=non_part.b; +-- end_ignore select * from part, non_part where part.b=non_part.b; create table p3 (a int, b int) distributed by (a); diff --git a/src/test/regress/output/part_external_table.source b/src/test/regress/output/part_external_table.source index 9eea739efd..25fc683aa3 100644 --- a/src/test/regress/output/part_external_table.source +++ b/src/test/regress/output/part_external_table.source @@ -38,12 +38,12 @@ analyze part; WARNING: skipping "p2_e" --- cannot analyze this foreign table WARNING: skipping "p1_e" --- cannot analyze this foreign table explain select * from part; - QUERY PLAN ---------------------------------------------------------------------------------------- - Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..112000.00 rows=6000000 width=8) - -> Append (cost=0.00..32000.00 rows=2000000 width=8) - -> Foreign Scan on p1_e (cost=0.00..11000.00 rows=1000000 width=8) - -> Foreign Scan on p2_e (cost=0.00..11000.00 rows=1000000 width=8) + QUERY PLAN +------------------------------------------------------------------------------------------ + Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..2722000.00 rows=180000000 width=8) + -> Append (cost=0.00..322000.00 rows=60000000 width=8) + -> Foreign Scan on p1_e part_1 (cost=0.00..11000.00 rows=30000000 width=8) + -> Foreign Scan on p2_e part_2 (cost=0.00..11000.00 rows=30000000 width=8) Optimizer: Postgres query optimizer (5 rows) @@ -62,10 +62,10 @@ select * from part; -- test SPE explain select * from part where b>10 and a>0; - QUERY PLAN -------------------------------------------------------------------------------------- - Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..23698.00 rows=577350 width=8) - -> Foreign Scan on p2_e (cost=0.00..16000.00 rows=192450 width=8) + QUERY PLAN +---------------------------------------------------------------------------------------- + Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..246940.11 rows=17320508 width=8) + -> Foreign Scan on p2_e part (cost=0.00..16000.00 rows=5773503 width=8) Filter: ((b > 10) AND (a > 0)) Optimizer: Postgres query optimizer (4 rows) @@ -81,33 +81,35 @@ select * from part where b>10 and a>0; create table non_part (a int, b int) distributed by (a); insert into non_part values (15,15); +analyze non_part; -- mixed partitions with DPE with multiple dynamic scans, select one partition +-- start_ignore +-- unstable test case explain analyze select * from part, non_part where part.b=non_part.b; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------- - - Gather Motion 3:1 (slice1; segments: 3) (cost=2545.25..24434545.25 rows=172200000 width=16) (actual time=7.605..7.668 rows=1 loops=1) - -> Hash Join (cost=2545.25..22138545.25 rows=57400000 width=16) (actual time=5.665..7.006 rows=1 loops=1) - Hash Cond: (p1_e.b = non_part.b) + Gather Motion 3:1 (slice1; segments: 3) (cost=2545.25..649094545.25 rows=172200000 width=16) (actual time=7.635..7.787 rows=1 loops=1) + -> Hash Join (cost=2545.25..646798545.25 rows=57400000 width=16) (actual time=4.331..6.693 rows=1 loops=1) + Hash Cond: (part.b = non_part.b) Extra Text: (seg0) Hash chain length 1.0 avg, 1 max, using 1 of 524288 buckets. - -> Append (cost=0.00..32000.00 rows=2000000 width=8) (actual time=1.458..1.485 rows=4 loops=1) + -> Append (cost=0.00..322000.00 rows=60000000 width=8) (actual time=0.331..0.350 rows=4 loops=1) Partition Selectors: $0 - -> Foreign Scan on p1_e (cost=0.00..11000.00 rows=1000000 width=8) (never executed) - -> Foreign Scan on p2_e (cost=0.00..11000.00 rows=1000000 width=8) (actual time=1.457..1.482 rows=4 loops=1) - -> Hash (cost=1469.00..1469.00 rows=86100 width=8) (actual time=0.051..0.051 rows=1 loops=1) + -> Foreign Scan on p1_e part_1 (cost=0.00..11000.00 rows=30000000 width=8) (never executed) + -> Foreign Scan on p2_e part_2 (cost=0.00..11000.00 rows=30000000 width=8) (actual time=0.327..0.343 rows=4 loops=1) + -> Hash (cost=1469.00..1469.00 rows=86100 width=8) (actual time=0.080..0.084 rows=1 loops=1) Buckets: 524288 Batches: 1 Memory Usage: 4097kB - -> Partition Selector (selector id: $0) (cost=0.00..1469.00 rows=86100 width=8) (actual time=0.038..0.045 rows=1 loops=1) - -> Broadcast Motion 3:3 (slice2; segments: 3) (cost=0.00..1469.00 rows=86100 width=8) (actual time=0.027..0.034 rows=1 loops=1) - -> Seq Scan on non_part (cost=0.00..321.00 rows=28700 width=8) (actual time=1.924..1.926 rows=1 loops=1) - Optimizer: Postgres query optimizer - Planning Time: 1.016 ms - (slice0) Executor memory: 196K bytes. - (slice1) Executor memory: 4328K bytes avg x 3 workers, 4372K bytes max (seg0). Work_mem: 4097K bytes max. - (slice2) Executor memory: 37K bytes avg x 3 workers, 37K bytes max (seg0). + -> Partition Selector (selector id: $0) (cost=0.00..1469.00 rows=86100 width=8) (actual time=0.058..0.061 rows=1 loops=1) + -> Broadcast Motion 3:3 (slice2; segments: 3) (cost=0.00..1469.00 rows=86100 width=8) (actual time=0.033..0.034 rows=1 loops=1) + -> Seq Scan on non_part (cost=0.00..321.00 rows=28700 width=8) (actual time=0.060..0.063 rows=1 loops=1) + Planning Time: 2.398 ms + (slice0) Executor memory: 211K bytes. + (slice1) Executor memory: 4360K bytes avg x 3x(0) workers, 4454K bytes max (seg0). Work_mem: 4097K bytes max. + (slice2) Executor memory: 112K bytes avg x 3x(0) workers, 112K bytes max (seg0). Memory used: 128000kB - Execution Time: 26.098 ms -(19 rows) - + Optimizer: Postgres query optimizer + Execution Time: 19.240 ms +(20 rows) +-- end_ignore select * from part, non_part where part.b=non_part.b; a | b | a | b ----+----+----+---- @@ -115,32 +117,35 @@ select * from part, non_part where part.b=non_part.b; (1 row) insert into non_part values (5,5); +analyze non_part; -- mixed partitions with DPE with multiple dynamic scans, select both partition +-- start_ignore +-- unstable test case explain analyze select * from part, non_part where part.b=non_part.b; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------- - Gather Motion 3:1 (slice1; segments: 3) (cost=2545.25..24434545.25 rows=172200000 width=16) (actual time=6.239..6.240 rows=2 loops=1) - -> Hash Join (cost=2545.25..22138545.25 rows=57400000 width=16) (actual time=2.728..4.876 rows=2 loops=1) - Hash Cond: (p1_e.b = non_part.b) + Gather Motion 3:1 (slice1; segments: 3) (cost=2545.25..649094545.25 rows=172200000 width=16) (actual time=3.810..3.815 rows=2 loops=1) + -> Hash Join (cost=2545.25..646798545.25 rows=57400000 width=16) (actual time=1.414..3.220 rows=2 loops=1) + Hash Cond: (part.b = non_part.b) Extra Text: (seg0) Hash chain length 1.0 avg, 1 max, using 2 of 524288 buckets. - -> Append (cost=0.00..32000.00 rows=2000000 width=8) (actual time=1.050..1.937 rows=8 loops=1) + -> Append (cost=0.00..322000.00 rows=60000000 width=8) (actual time=0.233..0.447 rows=8 loops=1) Partition Selectors: $0 - -> Foreign Scan on p1_e (cost=0.00..11000.00 rows=1000000 width=8) (actual time=1.048..1.072 rows=4 loops=1) - -> Foreign Scan on p2_e (cost=0.00..11000.00 rows=1000000 width=8) (actual time=0.836..0.861 rows=4 loops=1) - -> Hash (cost=1469.00..1469.00 rows=86100 width=8) (actual time=0.046..0.046 rows=2 loops=1) + -> Foreign Scan on p1_e part_1 (cost=0.00..11000.00 rows=30000000 width=8) (actual time=0.231..0.244 rows=4 loops=1) + -> Foreign Scan on p2_e part_2 (cost=0.00..11000.00 rows=30000000 width=8) (actual time=0.185..0.197 rows=4 loops=1) + -> Hash (cost=1469.00..1469.00 rows=86100 width=8) (actual time=0.791..0.793 rows=2 loops=1) Buckets: 524288 Batches: 1 Memory Usage: 4097kB - -> Partition Selector (selector id: $0) (cost=0.00..1469.00 rows=86100 width=8) (actual time=0.032..0.043 rows=2 loops=1) - -> Broadcast Motion 3:3 (slice2; segments: 3) (cost=0.00..1469.00 rows=86100 width=8) (actual time=0.026..0.034 rows=2 loops=1) - -> Seq Scan on non_part (cost=0.00..321.00 rows=28700 width=8) (actual time=0.189..0.190 rows=1 loops=1) - Optimizer: Postgres query optimizer - Planning Time: 0.732 ms - (slice0) Executor memory: 196K bytes. - (slice1) Executor memory: 4340K bytes avg x 3 workers, 4410K bytes max (seg0). Work_mem: 4097K bytes max. - (slice2) Executor memory: 36K bytes avg x 3 workers, 36K bytes max (seg0). + -> Partition Selector (selector id: $0) (cost=0.00..1469.00 rows=86100 width=8) (actual time=0.640..0.786 rows=2 loops=1) + -> Broadcast Motion 3:3 (slice2; segments: 3) (cost=0.00..1469.00 rows=86100 width=8) (actual time=0.631..0.774 rows=2 loops=1) + -> Seq Scan on non_part (cost=0.00..321.00 rows=28700 width=8) (actual time=0.014..0.015 rows=1 loops=1) + Planning Time: 1.557 ms + (slice0) Executor memory: 211K bytes. + (slice1) Executor memory: 4373K bytes avg x 3x(0) workers, 4492K bytes max (seg0). Work_mem: 4097K bytes max. + (slice2) Executor memory: 111K bytes avg x 3x(0) workers, 111K bytes max (seg0). Memory used: 128000kB - Execution Time: 7.404 ms + Optimizer: Postgres query optimizer + Execution Time: 4.815 ms (20 rows) - +-- end_ignore select * from part, non_part where part.b=non_part.b; a | b | a | b ----+----+----+---- @@ -159,14 +164,14 @@ WARNING: skipping "p1_e" --- cannot analyze this foreign table insert into non_part values (32,32); -- mixed partitions explain select * from part; - QUERY PLAN ---------------------------------------------------------------------------------------- - Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..112002.20 rows=6000011 width=8) - -> Append (cost=0.00..32002.06 rows=2000004 width=8) - -> Foreign Scan on p1_e (cost=0.00..11000.00 rows=1000000 width=8) - -> Foreign Scan on p2_e (cost=0.00..11000.00 rows=1000000 width=8) - -> Seq Scan on p3 (cost=0.00..1.02 rows=2 width=8) - -> Seq Scan on p4 (cost=0.00..1.02 rows=2 width=8) + QUERY PLAN +------------------------------------------------------------------------------------------ + Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..2722002.20 rows=180000011 width=8) + -> Append (cost=0.00..322002.05 rows=60000004 width=8) + -> Foreign Scan on p1_e part_1 (cost=0.00..11000.00 rows=30000000 width=8) + -> Foreign Scan on p2_e part_2 (cost=0.00..11000.00 rows=30000000 width=8) + -> Seq Scan on p3 part_3 (cost=0.00..1.02 rows=2 width=8) + -> Seq Scan on p4 part_4 (cost=0.00..1.02 rows=2 width=8) Optimizer: Postgres query optimizer (7 rows) @@ -196,13 +201,13 @@ select * from part; --mixed partitions with SPE explain select * from part where b>10 and b<25; - QUERY PLAN ------------------------------------------------------------------------------------- - Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..16451.07 rows=30003 width=8) - -> Append (cost=0.00..16051.03 rows=10001 width=8) - -> Foreign Scan on p2_e (cost=0.00..16000.00 rows=10000 width=8) + QUERY PLAN +------------------------------------------------------------------------------------- + Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..29501.07 rows=900003 width=8) + -> Append (cost=0.00..17501.03 rows=300001 width=8) + -> Foreign Scan on p2_e part_1 (cost=0.00..16000.00 rows=300000 width=8) Filter: ((b > 10) AND (b < 25)) - -> Seq Scan on p3 (cost=0.00..1.02 rows=1 width=8) + -> Seq Scan on p3 part_2 (cost=0.00..1.02 rows=1 width=8) Filter: ((b > 10) AND (b < 25)) Optimizer: Postgres query optimizer (7 rows) @@ -222,9 +227,9 @@ explain select * from part where b>25; ----------------------------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..2.20 rows=10 width=8) -> Append (cost=0.00..2.06 rows=3 width=8) - -> Seq Scan on p3 (cost=0.00..1.02 rows=1 width=8) + -> Seq Scan on p3 part_1 (cost=0.00..1.02 rows=1 width=8) Filter: (b > 25) - -> Seq Scan on p4 (cost=0.00..1.02 rows=2 width=8) + -> Seq Scan on p4 part_2 (cost=0.00..1.02 rows=2 width=8) Filter: (b > 25) Optimizer: Postgres query optimizer (7 rows) @@ -246,13 +251,13 @@ select * from part where b>25; --mixed partitions with SPE, only do foreign scans explain select a from part where b<18; - QUERY PLAN --------------------------------------------------------------------------------------- - Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..57000.00 rows=2000000 width=4) - -> Append (cost=0.00..30333.33 rows=666667 width=4) - -> Foreign Scan on p1_e (cost=0.00..13500.00 rows=333333 width=4) + QUERY PLAN +---------------------------------------------------------------------------------------- + Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..927000.00 rows=60000000 width=4) + -> Append (cost=0.00..127000.00 rows=20000000 width=4) + -> Foreign Scan on p1_e part_1 (cost=0.00..13500.00 rows=10000000 width=4) Filter: (b < 18) - -> Foreign Scan on p2_e (cost=0.00..13500.00 rows=333333 width=4) + -> Foreign Scan on p2_e part_2 (cost=0.00..13500.00 rows=10000000 width=4) Filter: (b < 18) Optimizer: Postgres query optimizer (7 rows) @@ -272,28 +277,30 @@ select a from part where b<18; truncate non_part; -- mixed partitions with DPE with multiple dynamic scans, select no partitions explain analyze select * from part, non_part where part.b=non_part.b; - QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------- - Gather Motion 3:1 (slice1; segments: 3) (cost=57002.10..60531.91 rows=86100 width=16) (actual time=7.051..7.051 rows=0 loops=1) - -> Hash Join (cost=57002.10..59383.91 rows=28700 width=16) (actual time=0.000..5.576 rows=0 loops=1) - Hash Cond: (non_part.b = p1_e.b) - -> Broadcast Motion 3:3 (slice2; segments: 3) (cost=0.00..1469.00 rows=86100 width=8) (actual time=0.000..0.029 rows=0 loops=1) - -> Seq Scan on non_part (cost=0.00..321.00 rows=28700 width=8) (actual time=0.000..0.269 rows=0 loops=1) - -> Hash (cost=32002.06..32002.06 rows=2000004 width=8) (actual time=2.491..2.491 rows=11 loops=1) - Buckets: 524288 Batches: 1 Memory Usage: 4097kB - -> Append (cost=0.00..32002.06 rows=2000004 width=8) (actual time=1.029..2.478 rows=11 loops=1) - -> Foreign Scan on p1_e (cost=0.00..11000.00 rows=1000000 width=8) (actual time=1.028..1.070 rows=4 loops=1) - -> Foreign Scan on p2_e (cost=0.00..11000.00 rows=1000000 width=8) (actual time=1.011..1.038 rows=4 loops=1) - -> Seq Scan on p3 (cost=0.00..1.02 rows=2 width=8) (actual time=0.185..0.186 rows=2 loops=1) - -> Seq Scan on p4 (cost=0.00..1.02 rows=2 width=8) (actual time=0.222..0.223 rows=3 loops=1) - Optimizer: Postgres query optimizer - Planning Time: 0.907 ms - (slice0) Executor memory: 192K bytes. - (slice1) Executor memory: 4338K bytes avg x 3 workers, 4402K bytes max (seg0). Work_mem: 4097K bytes max. - (slice2) Executor memory: 44K bytes avg x 3 workers, 44K bytes max (seg0). + QUERY PLAN +-------------------------------------------------------------------------------------------------------------------------------------------------------- + Gather Motion 3:1 (slice1; segments: 3) (cost=2545.25..550982.32 rows=86100 width=16) (actual time=3.922..3.926 rows=0 loops=1) + -> Hash Join (cost=2545.25..549834.32 rows=28700 width=16) (actual time=3.045..3.051 rows=0 loops=1) + Hash Cond: (part.b = non_part.b) + -> Append (cost=0.00..322002.05 rows=60000004 width=8) (never executed) + Partition Selectors: $0 + -> Foreign Scan on p1_e part_1 (cost=0.00..11000.00 rows=30000000 width=8) (never executed) + -> Foreign Scan on p2_e part_2 (cost=0.00..11000.00 rows=30000000 width=8) (never executed) + -> Seq Scan on p3 part_3 (cost=0.00..1.02 rows=2 width=8) (never executed) + -> Seq Scan on p4 part_4 (cost=0.00..1.02 rows=2 width=8) (never executed) + -> Hash (cost=1469.00..1469.00 rows=86100 width=8) (actual time=0.010..0.013 rows=0 loops=1) + Buckets: 524288 Batches: 1 Memory Usage: 4096kB + -> Partition Selector (selector id: $0) (cost=0.00..1469.00 rows=86100 width=8) (actual time=0.009..0.010 rows=0 loops=1) + -> Broadcast Motion 3:3 (slice2; segments: 3) (cost=0.00..1469.00 rows=86100 width=8) (actual time=0.008..0.009 rows=0 loops=1) + -> Seq Scan on non_part (cost=0.00..321.00 rows=28700 width=8) (actual time=0.016..0.018 rows=0 loops=1) + Planning Time: 2.743 ms + (slice0) Executor memory: 286K bytes. + (slice1) Executor memory: 4284K bytes avg x 3x(0) workers, 4284K bytes max (seg0). Work_mem: 4096K bytes max. + (slice2) Executor memory: 112K bytes avg x 3x(0) workers, 112K bytes max (seg0). Memory used: 128000kB - Execution Time: 18.045 ms -(19 rows) + Optimizer: Postgres query optimizer + Execution Time: 14.920 ms +(21 rows) select * from part, non_part where part.b=non_part.b; a | b | a | b @@ -303,29 +310,31 @@ select * from part, non_part where part.b=non_part.b; insert into non_part values (15,15), (32,32); -- mixed partitions with DPE with multiple dynamic scans, select one partition from each dynamic scan explain analyze select * from part, non_part where part.b=non_part.b; - QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------- - Gather Motion 3:1 (slice1; segments: 3) (cost=57002.10..60531.91 rows=86100 width=16) (actual time=5.702..7.798 rows=2 loops=1) - -> Hash Join (cost=57002.10..59383.91 rows=28700 width=16) (actual time=4.516..5.969 rows=1 loops=1) - Hash Cond: (non_part.b = p1_e.b) - Extra Text: (seg0) Hash chain length 1.0 avg, 1 max, using 11 of 524288 buckets. - -> Broadcast Motion 3:3 (slice2; segments: 3) (cost=0.00..1469.00 rows=86100 width=8) (actual time=0.021..0.028 rows=2 loops=1) - -> Seq Scan on non_part (cost=0.00..321.00 rows=28700 width=8) (actual time=0.476..0.477 rows=1 loops=1) - -> Hash (cost=32002.06..32002.06 rows=2000004 width=8) (actual time=2.649..2.649 rows=11 loops=1) + QUERY PLAN +-------------------------------------------------------------------------------------------------------------------------------------------------------- + Gather Motion 3:1 (slice1; segments: 3) (cost=2545.25..550982.32 rows=86100 width=16) (actual time=3.227..3.647 rows=2 loops=1) + -> Hash Join (cost=2545.25..549834.32 rows=28700 width=16) (actual time=1.326..2.957 rows=1 loops=1) + Hash Cond: (part.b = non_part.b) + Extra Text: (seg0) Hash chain length 1.0 avg, 1 max, using 2 of 524288 buckets. + -> Append (cost=0.00..322002.05 rows=60000004 width=8) (actual time=0.346..0.383 rows=5 loops=1) + Partition Selectors: $0 + -> Foreign Scan on p1_e part_1 (cost=0.00..11000.00 rows=30000000 width=8) (never executed) + -> Foreign Scan on p2_e part_2 (cost=0.00..11000.00 rows=30000000 width=8) (actual time=0.343..0.358 rows=4 loops=1) + -> Seq Scan on p3 part_3 (cost=0.00..1.02 rows=2 width=8) (never executed) + -> Seq Scan on p4 part_4 (cost=0.00..1.02 rows=2 width=8) (actual time=0.035..0.036 rows=3 loops=1) + -> Hash (cost=1469.00..1469.00 rows=86100 width=8) (actual time=0.680..0.682 rows=2 loops=1) Buckets: 524288 Batches: 1 Memory Usage: 4097kB - -> Append (cost=0.00..32002.06 rows=2000004 width=8) (actual time=1.319..2.639 rows=11 loops=1) - -> Foreign Scan on p1_e (cost=0.00..11000.00 rows=1000000 width=8) (actual time=1.319..1.342 rows=4 loops=1) - -> Foreign Scan on p2_e (cost=0.00..11000.00 rows=1000000 width=8) (actual time=0.962..0.987 rows=4 loops=1) - -> Seq Scan on p3 (cost=0.00..1.02 rows=2 width=8) (actual time=0.144..0.145 rows=2 loops=1) - -> Seq Scan on p4 (cost=0.00..1.02 rows=2 width=8) (actual time=0.278..0.279 rows=3 loops=1) - Optimizer: Postgres query optimizer - Planning Time: 0.833 ms - (slice0) Executor memory: 192K bytes. - (slice1) Executor memory: 4338K bytes avg x 3 workers, 4402K bytes max (seg0). Work_mem: 4097K bytes max. - (slice2) Executor memory: 36K bytes avg x 3 workers, 36K bytes max (seg0). + -> Partition Selector (selector id: $0) (cost=0.00..1469.00 rows=86100 width=8) (actual time=0.667..0.673 rows=2 loops=1) + -> Broadcast Motion 3:3 (slice2; segments: 3) (cost=0.00..1469.00 rows=86100 width=8) (actual time=0.658..0.661 rows=2 loops=1) + -> Seq Scan on non_part (cost=0.00..321.00 rows=28700 width=8) (actual time=0.046..0.047 rows=1 loops=1) + Planning Time: 2.386 ms + (slice0) Executor memory: 286K bytes. + (slice1) Executor memory: 4366K bytes avg x 3x(0) workers, 4457K bytes max (seg0). Work_mem: 4097K bytes max. + (slice2) Executor memory: 111K bytes avg x 3x(0) workers, 111K bytes max (seg0). Memory used: 128000kB - Execution Time: 9.190 ms -(20 rows) + Optimizer: Postgres query optimizer + Execution Time: 5.052 ms +(22 rows) select * from part, non_part where part.b=non_part.b; a | b | a | b @@ -355,29 +364,29 @@ analyze part; ERROR: could not stat file "/does/not/exist.csv": No such file or directory -- should have multiple dynamic scans explain select * from part; - QUERY PLAN --------------------------------------------------------------------------------------------- - Append (cost=0.00..134172.64 rows=6020491 width=8) - -> Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..51000.00 rows=3000000 width=8) - -> Foreign Scan on p1_e (cost=0.00..11000.00 rows=1000000 width=8) - -> Gather Motion 3:1 (slice2; segments: 3) (cost=0.00..51000.00 rows=3000000 width=8) - -> Foreign Scan on p2_e (cost=0.00..11000.00 rows=1000000 width=8) - -> Gather Motion 3:1 (slice3; segments: 3) (cost=0.00..1469.00 rows=86100 width=8) - -> Seq Scan on p3 (cost=0.00..321.00 rows=28700 width=8) - -> Gather Motion 3:1 (slice4; segments: 3) (cost=0.00..1469.00 rows=86100 width=8) - -> Seq Scan on p4 (cost=0.00..321.00 rows=28700 width=8) - -> Foreign Scan on ft1 (cost=0.00..1034.00 rows=10240 width=8) + QUERY PLAN +----------------------------------------------------------------------------------------------- + Append (cost=0.00..3324172.64 rows=180020491 width=8) + -> Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..1211000.00 rows=90000000 width=8) + -> Foreign Scan on p1_e part_1 (cost=0.00..11000.00 rows=30000000 width=8) + -> Gather Motion 3:1 (slice2; segments: 3) (cost=0.00..1211000.00 rows=90000000 width=8) + -> Foreign Scan on p2_e part_2 (cost=0.00..11000.00 rows=30000000 width=8) + -> Gather Motion 3:1 (slice3; segments: 3) (cost=0.00..1.08 rows=5 width=8) + -> Seq Scan on p3 part_3 (cost=0.00..1.02 rows=2 width=8) + -> Gather Motion 3:1 (slice4; segments: 3) (cost=0.00..1.10 rows=6 width=8) + -> Seq Scan on p4 part_4 (cost=0.00..1.02 rows=2 width=8) + -> Foreign Scan on ft1 part_5 (cost=0.00..1034.00 rows=10240 width=8) Foreign File: /does/not/exist.csv - -> Foreign Scan on ft2 (cost=0.00..1034.00 rows=10240 width=8) + -> Foreign Scan on ft2 part_6 (cost=0.00..1034.00 rows=10240 width=8) Foreign File: /does/not/exist.csv Optimizer: Postgres query optimizer (14 rows) -- eliminate unnecessary dynamic scans and foreign sans explain select * from part where b = 53; - QUERY PLAN ------------------------------------------------------------ - Foreign Scan on ft2 (cost=0.00..1059.60 rows=10 width=8) + QUERY PLAN +---------------------------------------------------------------- + Foreign Scan on ft2 part (cost=0.00..1059.60 rows=10 width=8) Filter: (b = 53) Foreign File: /does/not/exist.csv Optimizer: Postgres query optimizer @@ -385,19 +394,19 @@ explain select * from part where b = 53; -- only select foreign scans explain select * from part where b > 22; - QUERY PLAN ----------------------------------------------------------------------------------------- - Append (cost=0.00..3991.17 rows=64227 width=8) - -> Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..775.42 rows=28700 width=8) - -> Seq Scan on p3 (cost=0.00..392.75 rows=9567 width=8) + QUERY PLAN +---------------------------------------------------------------------------------- + Append (cost=0.00..2155.58 rows=6838 width=8) + -> Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..1.09 rows=5 width=8) + -> Seq Scan on p3 part_1 (cost=0.00..1.02 rows=2 width=8) Filter: (b > 22) - -> Gather Motion 3:1 (slice2; segments: 3) (cost=0.00..775.42 rows=28700 width=8) - -> Seq Scan on p4 (cost=0.00..392.75 rows=9567 width=8) + -> Gather Motion 3:1 (slice2; segments: 3) (cost=0.00..1.10 rows=6 width=8) + -> Seq Scan on p4 part_2 (cost=0.00..1.02 rows=2 width=8) Filter: (b > 22) - -> Foreign Scan on ft1 (cost=0.00..1059.60 rows=3413 width=8) + -> Foreign Scan on ft1 part_3 (cost=0.00..1059.60 rows=3413 width=8) Filter: (b > 22) Foreign File: /does/not/exist.csv - -> Foreign Scan on ft2 (cost=0.00..1059.60 rows=3413 width=8) + -> Foreign Scan on ft2 part_4 (cost=0.00..1059.60 rows=3413 width=8) Filter: (b > 22) Foreign File: /does/not/exist.csv Optimizer: Postgres query optimizer diff --git a/src/test/regress/output/part_external_table_optimizer.source b/src/test/regress/output/part_external_table_optimizer.source index 23854967ad..a5829c03cb 100644 --- a/src/test/regress/output/part_external_table_optimizer.source +++ b/src/test/regress/output/part_external_table_optimizer.source @@ -81,31 +81,34 @@ select * from part where b>10 and a>0; create table non_part (a int, b int) distributed by (a); insert into non_part values (15,15); +analyze non_part; -- mixed partitions with DPE with multiple dynamic scans, select one partition +-- start_ignore +-- unstable test case explain analyze select * from part, non_part where part.b=non_part.b; QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------------- - Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..862.00 rows=1 width=16) (actual time=6.491..6.492 rows=1 loops=1) - -> Hash Join (cost=0.00..862.00 rows=1 width=16) (actual time=4.937..6.264 rows=1 loops=1) + Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..1163.02 rows=2000000 width=16) (actual time=9.772..9.782 rows=1 loops=1) + -> Hash Join (cost=0.00..1043.77 rows=666667 width=16) (actual time=6.506..8.801 rows=1 loops=1) Hash Cond: (b = non_part.b) Extra Text: (seg0) Hash chain length 1.0 avg, 1 max, using 1 of 524288 buckets. - -> Dynamic Foreign Scan on part (cost=0.00..431.00 rows=1 width=8) (actual time=0.982..1.012 rows=4 loops=1) + -> Dynamic Foreign Scan on part (cost=0.00..446.40 rows=666667 width=8) (actual time=0.517..0.541 rows=4 loops=1) Number of partitions to scan: 2 (out of 2) Partitions scanned: Avg 1.0 x 3 workers. Max 1 parts (seg0). - -> Hash (cost=431.00..431.00 rows=1 width=8) (actual time=0.055..0.055 rows=1 loops=1) + -> Hash (cost=431.00..431.00 rows=1 width=8) (actual time=0.075..0.080 rows=1 loops=1) Buckets: 524288 Batches: 1 Memory Usage: 4097kB - -> Partition Selector (selector id: $0) (cost=0.00..431.00 rows=1 width=8) (actual time=0.034..0.048 rows=1 loops=1) - -> Broadcast Motion 3:3 (slice2; segments: 3) (cost=0.00..431.00 rows=1 width=8) (actual time=0.025..0.037 rows=1 loops=1) - -> Seq Scan on non_part (cost=0.00..431.00 rows=1 width=8) (actual time=0.683..0.684 rows=1 loops=1) - Optimizer: Pivotal Optimizer (GPORCA) - Planning Time: 17.740 ms - (slice0) Executor memory: 49K bytes. - (slice1) Executor memory: 4253K bytes avg x 3 workers, 4297K bytes max (seg0). Work_mem: 4097K bytes max. - (slice2) Executor memory: 37K bytes avg x 3 workers, 37K bytes max (seg0). + -> Partition Selector (selector id: $0) (cost=0.00..431.00 rows=1 width=8) (actual time=0.057..0.060 rows=1 loops=1) + -> Broadcast Motion 3:3 (slice2; segments: 3) (cost=0.00..431.00 rows=1 width=8) (actual time=0.026..0.028 rows=1 loops=1) + -> Seq Scan on non_part (cost=0.00..431.00 rows=1 width=8) (actual time=0.034..0.037 rows=1 loops=1) + Planning Time: 33.813 ms + (slice0) Executor memory: 127K bytes. + (slice1) Executor memory: 4309K bytes avg x 3x(0) workers, 4378K bytes max (seg0). Work_mem: 4097K bytes max. + (slice2) Executor memory: 112K bytes avg x 3x(0) workers, 112K bytes max (seg0). Memory used: 128000kB - Execution Time: 26.191 ms + Optimizer: Pivotal Optimizer (GPORCA) + Execution Time: 20.437 ms (19 rows) - +-- end_ignore select * from part, non_part where part.b=non_part.b; a | b | a | b ----+----+----+---- @@ -113,31 +116,34 @@ select * from part, non_part where part.b=non_part.b; (1 row) insert into non_part values (5,5); +analyze non_part; -- mixed partitions with DPE with multiple dynamic scans, select both partition +-- start_ignore +-- unstable test case explain analyze select * from part, non_part where part.b=non_part.b; QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------------- - Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..862.00 rows=1 width=16) (actual time=10.232..10.233 rows=2 loops=1) - -> Hash Join (cost=0.00..862.00 rows=1 width=16) (actual time=4.907..8.663 rows=2 loops=1) + Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..1163.02 rows=2000000 width=16) (actual time=4.645..4.650 rows=2 loops=1) + -> Hash Join (cost=0.00..1043.77 rows=666667 width=16) (actual time=2.021..4.289 rows=2 loops=1) Hash Cond: (b = non_part.b) Extra Text: (seg0) Hash chain length 1.0 avg, 1 max, using 2 of 524288 buckets. - -> Dynamic Foreign Scan on part (cost=0.00..431.00 rows=1 width=8) (actual time=1.333..3.680 rows=8 loops=1) + -> Dynamic Foreign Scan on part (cost=0.00..446.40 rows=666667 width=8) (actual time=0.528..0.949 rows=8 loops=1) Number of partitions to scan: 2 (out of 2) Partitions scanned: Avg 2.0 x 3 workers. Max 2 parts (seg0). - -> Hash (cost=431.00..431.00 rows=1 width=8) (actual time=1.485..1.485 rows=2 loops=1) + -> Hash (cost=431.00..431.00 rows=2 width=8) (actual time=0.783..0.786 rows=2 loops=1) Buckets: 524288 Batches: 1 Memory Usage: 4097kB - -> Partition Selector (selector id: $0) (cost=0.00..431.00 rows=1 width=8) (actual time=0.686..1.457 rows=2 loops=1) - -> Broadcast Motion 3:3 (slice2; segments: 3) (cost=0.00..431.00 rows=1 width=8) (actual time=0.649..1.415 rows=2 loops=1) - -> Seq Scan on non_part (cost=0.00..431.00 rows=1 width=8) (actual time=1.137..1.138 rows=1 loops=1) - Optimizer: Pivotal Optimizer (GPORCA) - Planning Time: 12.815 ms - (slice0) Executor memory: 49K bytes. - (slice1) Executor memory: 4325K bytes avg x 3 workers, 4373K bytes max (seg0). Work_mem: 4097K bytes max. - (slice2) Executor memory: 36K bytes avg x 3 workers, 36K bytes max (seg0). + -> Partition Selector (selector id: $0) (cost=0.00..431.00 rows=2 width=8) (actual time=0.770..0.776 rows=2 loops=1) + -> Broadcast Motion 3:3 (slice2; segments: 3) (cost=0.00..431.00 rows=2 width=8) (actual time=0.757..0.760 rows=2 loops=1) + -> Seq Scan on non_part (cost=0.00..431.00 rows=1 width=8) (actual time=0.037..0.040 rows=1 loops=1) + Planning Time: 32.227 ms + (slice0) Executor memory: 127K bytes. + (slice1) Executor memory: 4382K bytes avg x 3x(0) workers, 4456K bytes max (seg0). Work_mem: 4097K bytes max. + (slice2) Executor memory: 112K bytes avg x 3x(0) workers, 112K bytes max (seg0). Memory used: 128000kB - Execution Time: 11.767 ms + Optimizer: Pivotal Optimizer (GPORCA) + Execution Time: 15.233 ms (19 rows) - +-- end_ignore select * from part, non_part where part.b=non_part.b; a | b | a | b ----+----+----+---- --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
