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 43ca55d2a9beb0266106f877623028f7a33d9601 Author: zhoujiaqi <[email protected]> AuthorDate: Fri Dec 20 14:07:50 2024 +0800 Fix the icw tests which generated by dynamic (bitmap/index/table) scan The reason for the current test failure is not only that the scan of the partition table has been changed to a dynamic scan, but also that we have disabled the index scan in nest loop in ORCA. --- src/test/regress/expected/aggregates_optimizer.out | 7 +- src/test/regress/expected/bfv_index_optimizer.out | 122 ++-- src/test/regress/expected/bfv_partition_plans.out | 1 + .../expected/bfv_partition_plans_optimizer.out | 3 +- src/test/regress/expected/dpe_optimizer.out | 789 +++++++++++++-------- .../regress/expected/explain_format_optimizer.out | 1 + src/test/regress/expected/gporca_optimizer.out | 68 +- src/test/regress/expected/join_optimizer.out | 15 +- .../expected/orca_static_pruning_optimizer.out | 18 +- .../regress/expected/partition_prune_optimizer.out | 162 ++--- src/test/regress/expected/subselect_optimizer.out | 28 +- src/test/regress/sql/bfv_partition_plans.sql | 1 + 12 files changed, 712 insertions(+), 503 deletions(-) diff --git a/src/test/regress/expected/aggregates_optimizer.out b/src/test/regress/expected/aggregates_optimizer.out index 959aef0c0b..6c2d7c06f9 100644 --- a/src/test/regress/expected/aggregates_optimizer.out +++ b/src/test/regress/expected/aggregates_optimizer.out @@ -1431,11 +1431,10 @@ explain (costs off) select * from p_t1 group by a,b,c,d; QUERY PLAN ------------------------------------------ Gather Motion 3:1 (slice1; segments: 3) - -> Append - -> Seq Scan on p_t1_1 - -> Seq Scan on p_t1_2 + -> Dynamic Seq Scan on p_t1 + Number of partitions to scan: 2 Optimizer: Pivotal Optimizer (GPORCA) -(5 rows) +(4 rows) drop table t1 cascade; NOTICE: drop cascades to table t1c diff --git a/src/test/regress/expected/bfv_index_optimizer.out b/src/test/regress/expected/bfv_index_optimizer.out index da317532ff..7829a16f26 100644 --- a/src/test/regress/expected/bfv_index_optimizer.out +++ b/src/test/regress/expected/bfv_index_optimizer.out @@ -127,55 +127,89 @@ explain SELECT count(*) FROM bfv_tab2_facttable1 ft, bfv_tab2_dimdate dt, bfv_tab2_dimtabl1 dt1 WHERE ft.wk_id = dt.wk_id AND ft.id = dt1.id; - QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------- - Aggregate (cost=0.00..1250.34 rows=1 width=8) - -> Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..1250.34 rows=7 width=1) - -> Hash Join (cost=0.00..1250.34 rows=3 width=1) - Hash Cond: (bfv_tab2_dimdate.wk_id = bfv_tab2_facttable1.wk_id) - -> Seq Scan on bfv_tab2_dimdate (cost=0.00..431.00 rows=4 width=2) - -> Hash (cost=819.34..819.34 rows=3 width=2) - -> Redistribute Motion 3:3 (slice2; segments: 3) (cost=0.00..819.34 rows=3 width=2) - Hash Key: bfv_tab2_facttable1.wk_id - -> Nested Loop (cost=0.00..819.34 rows=3 width=2) - Join Filter: true - -> Broadcast Motion 3:3 (slice3; segments: 3) (cost=0.00..431.00 rows=7 width=4) - -> Seq Scan on bfv_tab2_dimtabl1 (cost=0.00..431.00 rows=3 width=4) - -> Dynamic Bitmap Heap Scan on bfv_tab2_facttable1 (cost=0.00..388.34 rows=1 width=2) - Number of partitions to scan: 21 - Recheck Cond: (id = bfv_tab2_dimtabl1.id) - Filter: (id = bfv_tab2_dimtabl1.id) - -> Dynamic Bitmap Index Scan on idx_bfv_tab2_facttable1 (cost=0.00..0.00 rows=0 width=0) - Index Cond: (id = bfv_tab2_dimtabl1.id) - Optimizer: Pivotal Optimizer (GPORCA) -(19 rows) + QUERY PLAN +------------------------------------------------------------------------------------------------------------------------ + Aggregate (cost=23.80..23.81 rows=1 width=8) + -> Gather Motion 3:1 (slice1; segments: 3) (cost=2.28..23.79 rows=3 width=0) + -> Hash Join (cost=2.28..23.74 rows=1 width=0) + Hash Cond: (ft.wk_id = dt.wk_id) + -> Redistribute Motion 3:3 (slice2; segments: 3) (cost=1.20..22.65 rows=2 width=2) + Hash Key: ft.wk_id + -> Hash Join (cost=1.20..22.60 rows=2 width=2) + Hash Cond: (ft.id = dt1.id) + -> Append (cost=0.00..21.32 rows=21 width=6) + -> Seq Scan on bfv_tab2_facttable1_1_prt_2 ft_1 (cost=0.00..1.01 rows=1 width=6) + -> Seq Scan on bfv_tab2_facttable1_1_prt_3 ft_2 (cost=0.00..1.01 rows=1 width=6) + -> Seq Scan on bfv_tab2_facttable1_1_prt_4 ft_3 (cost=0.00..1.01 rows=1 width=6) + -> Seq Scan on bfv_tab2_facttable1_1_prt_5 ft_4 (cost=0.00..1.01 rows=1 width=6) + -> Seq Scan on bfv_tab2_facttable1_1_prt_6 ft_5 (cost=0.00..1.01 rows=1 width=6) + -> Seq Scan on bfv_tab2_facttable1_1_prt_7 ft_6 (cost=0.00..1.01 rows=1 width=6) + -> Seq Scan on bfv_tab2_facttable1_1_prt_8 ft_7 (cost=0.00..1.01 rows=1 width=6) + -> Seq Scan on bfv_tab2_facttable1_1_prt_9 ft_8 (cost=0.00..1.01 rows=1 width=6) + -> Seq Scan on bfv_tab2_facttable1_1_prt_10 ft_9 (cost=0.00..1.01 rows=1 width=6) + -> Seq Scan on bfv_tab2_facttable1_1_prt_11 ft_10 (cost=0.00..1.01 rows=1 width=6) + -> Seq Scan on bfv_tab2_facttable1_1_prt_12 ft_11 (cost=0.00..1.01 rows=1 width=6) + -> Seq Scan on bfv_tab2_facttable1_1_prt_13 ft_12 (cost=0.00..1.01 rows=1 width=6) + -> Seq Scan on bfv_tab2_facttable1_1_prt_14 ft_13 (cost=0.00..1.01 rows=1 width=6) + -> Seq Scan on bfv_tab2_facttable1_1_prt_15 ft_14 (cost=0.00..1.01 rows=1 width=6) + -> Seq Scan on bfv_tab2_facttable1_1_prt_16 ft_15 (cost=0.00..1.01 rows=1 width=6) + -> Seq Scan on bfv_tab2_facttable1_1_prt_17 ft_16 (cost=0.00..1.01 rows=1 width=6) + -> Seq Scan on bfv_tab2_facttable1_1_prt_18 ft_17 (cost=0.00..1.01 rows=1 width=6) + -> Seq Scan on bfv_tab2_facttable1_1_prt_19 ft_18 (cost=0.00..1.01 rows=1 width=6) + -> Seq Scan on bfv_tab2_facttable1_1_prt_20 ft_19 (cost=0.00..1.01 rows=1 width=6) + -> Seq Scan on bfv_tab2_facttable1_1_prt_21 ft_20 (cost=0.00..1.01 rows=1 width=6) + -> Seq Scan on bfv_tab2_facttable1_1_prt_dflt ft_21 (cost=0.00..1.01 rows=1 width=6) + -> Hash (cost=1.12..1.12 rows=7 width=4) + -> Broadcast Motion 3:3 (slice3; segments: 3) (cost=0.00..1.12 rows=7 width=4) + -> Seq Scan on bfv_tab2_dimtabl1 dt1 (cost=0.00..1.02 rows=2 width=4) + -> Hash (cost=1.03..1.03 rows=3 width=2) + -> Seq Scan on bfv_tab2_dimdate dt (cost=0.00..1.03 rows=3 width=2) + Optimizer: Postgres query optimizer +(36 rows) explain SELECT count(*) FROM bfv_tab2_facttable1 ft, bfv_tab2_dimdate dt, bfv_tab2_dimtabl1 dt1 WHERE ft.wk_id = dt.wk_id AND ft.id = dt1.id; - QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------- - Aggregate (cost=0.00..1250.34 rows=1 width=8) - -> Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..1250.34 rows=7 width=1) - -> Hash Join (cost=0.00..1250.34 rows=3 width=1) - Hash Cond: (bfv_tab2_dimdate.wk_id = bfv_tab2_facttable1.wk_id) - -> Seq Scan on bfv_tab2_dimdate (cost=0.00..431.00 rows=4 width=2) - -> Hash (cost=819.34..819.34 rows=3 width=2) - -> Redistribute Motion 3:3 (slice2; segments: 3) (cost=0.00..819.34 rows=3 width=2) - Hash Key: bfv_tab2_facttable1.wk_id - -> Nested Loop (cost=0.00..819.34 rows=3 width=2) - Join Filter: true - -> Broadcast Motion 3:3 (slice3; segments: 3) (cost=0.00..431.00 rows=7 width=4) - -> Seq Scan on bfv_tab2_dimtabl1 (cost=0.00..431.00 rows=3 width=4) - -> Dynamic Bitmap Heap Scan on bfv_tab2_facttable1 (cost=0.00..388.34 rows=1 width=2) - Number of partitions to scan: 21 - Recheck Cond: (id = bfv_tab2_dimtabl1.id) - Filter: (id = bfv_tab2_dimtabl1.id) - -> Dynamic Bitmap Index Scan on idx_bfv_tab2_facttable1 (cost=0.00..0.00 rows=0 width=0) - Index Cond: (id = bfv_tab2_dimtabl1.id) - Optimizer: Pivotal Optimizer (GPORCA) -(19 rows) + QUERY PLAN +------------------------------------------------------------------------------------------------------------------------ + Aggregate (cost=23.80..23.81 rows=1 width=8) + -> Gather Motion 3:1 (slice1; segments: 3) (cost=2.28..23.79 rows=3 width=0) + -> Hash Join (cost=2.28..23.74 rows=1 width=0) + Hash Cond: (ft.wk_id = dt.wk_id) + -> Redistribute Motion 3:3 (slice2; segments: 3) (cost=1.20..22.65 rows=2 width=2) + Hash Key: ft.wk_id + -> Hash Join (cost=1.20..22.60 rows=2 width=2) + Hash Cond: (ft.id = dt1.id) + -> Append (cost=0.00..21.32 rows=21 width=6) + -> Seq Scan on bfv_tab2_facttable1_1_prt_2 ft_1 (cost=0.00..1.01 rows=1 width=6) + -> Seq Scan on bfv_tab2_facttable1_1_prt_3 ft_2 (cost=0.00..1.01 rows=1 width=6) + -> Seq Scan on bfv_tab2_facttable1_1_prt_4 ft_3 (cost=0.00..1.01 rows=1 width=6) + -> Seq Scan on bfv_tab2_facttable1_1_prt_5 ft_4 (cost=0.00..1.01 rows=1 width=6) + -> Seq Scan on bfv_tab2_facttable1_1_prt_6 ft_5 (cost=0.00..1.01 rows=1 width=6) + -> Seq Scan on bfv_tab2_facttable1_1_prt_7 ft_6 (cost=0.00..1.01 rows=1 width=6) + -> Seq Scan on bfv_tab2_facttable1_1_prt_8 ft_7 (cost=0.00..1.01 rows=1 width=6) + -> Seq Scan on bfv_tab2_facttable1_1_prt_9 ft_8 (cost=0.00..1.01 rows=1 width=6) + -> Seq Scan on bfv_tab2_facttable1_1_prt_10 ft_9 (cost=0.00..1.01 rows=1 width=6) + -> Seq Scan on bfv_tab2_facttable1_1_prt_11 ft_10 (cost=0.00..1.01 rows=1 width=6) + -> Seq Scan on bfv_tab2_facttable1_1_prt_12 ft_11 (cost=0.00..1.01 rows=1 width=6) + -> Seq Scan on bfv_tab2_facttable1_1_prt_13 ft_12 (cost=0.00..1.01 rows=1 width=6) + -> Seq Scan on bfv_tab2_facttable1_1_prt_14 ft_13 (cost=0.00..1.01 rows=1 width=6) + -> Seq Scan on bfv_tab2_facttable1_1_prt_15 ft_14 (cost=0.00..1.01 rows=1 width=6) + -> Seq Scan on bfv_tab2_facttable1_1_prt_16 ft_15 (cost=0.00..1.01 rows=1 width=6) + -> Seq Scan on bfv_tab2_facttable1_1_prt_17 ft_16 (cost=0.00..1.01 rows=1 width=6) + -> Seq Scan on bfv_tab2_facttable1_1_prt_18 ft_17 (cost=0.00..1.01 rows=1 width=6) + -> Seq Scan on bfv_tab2_facttable1_1_prt_19 ft_18 (cost=0.00..1.01 rows=1 width=6) + -> Seq Scan on bfv_tab2_facttable1_1_prt_20 ft_19 (cost=0.00..1.01 rows=1 width=6) + -> Seq Scan on bfv_tab2_facttable1_1_prt_21 ft_20 (cost=0.00..1.01 rows=1 width=6) + -> Seq Scan on bfv_tab2_facttable1_1_prt_dflt ft_21 (cost=0.00..1.01 rows=1 width=6) + -> Hash (cost=1.12..1.12 rows=7 width=4) + -> Broadcast Motion 3:3 (slice3; segments: 3) (cost=0.00..1.12 rows=7 width=4) + -> Seq Scan on bfv_tab2_dimtabl1 dt1 (cost=0.00..1.02 rows=2 width=4) + -> Hash (cost=1.03..1.03 rows=3 width=2) + -> Seq Scan on bfv_tab2_dimdate dt (cost=0.00..1.03 rows=3 width=2) + Optimizer: Postgres query optimizer +(36 rows) -- start_ignore create language plpython3u; diff --git a/src/test/regress/expected/bfv_partition_plans.out b/src/test/regress/expected/bfv_partition_plans.out index 597f507a65..b9cc5a0d40 100644 --- a/src/test/regress/expected/bfv_partition_plans.out +++ b/src/test/regress/expected/bfv_partition_plans.out @@ -114,6 +114,7 @@ HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sur insert into mpp23195_t1 values (generate_series(1,19)); insert into mpp23195_t2 values (1); -- TEST +-- Operator Fallback: InnerIndexNestLoopJoin may have wrong plan not supported select find_operator('select * from mpp23195_t1,mpp23195_t2 where mpp23195_t1.i < mpp23195_t2.i;', 'Dynamic Index Scan'); find_operator --------------- diff --git a/src/test/regress/expected/bfv_partition_plans_optimizer.out b/src/test/regress/expected/bfv_partition_plans_optimizer.out index a5bb5f0570..816629637f 100644 --- a/src/test/regress/expected/bfv_partition_plans_optimizer.out +++ b/src/test/regress/expected/bfv_partition_plans_optimizer.out @@ -114,10 +114,11 @@ HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sur insert into mpp23195_t1 values (generate_series(1,19)); insert into mpp23195_t2 values (1); -- TEST +-- Operator Fallback: InnerIndexNestLoopJoin may have wrong plan not supported select find_operator('select * from mpp23195_t1,mpp23195_t2 where mpp23195_t1.i < mpp23195_t2.i;', 'Dynamic Index Scan'); find_operator --------------- - ['true'] + ['false'] (1 row) select * from mpp23195_t1,mpp23195_t2 where mpp23195_t1.i < mpp23195_t2.i; diff --git a/src/test/regress/expected/dpe_optimizer.out b/src/test/regress/expected/dpe_optimizer.out index b78f6e3172..a35f9b80b9 100644 --- a/src/test/regress/expected/dpe_optimizer.out +++ b/src/test/regress/expected/dpe_optimizer.out @@ -77,19 +77,27 @@ analyze t1; -- Simple positive cases -- explain (costs off, timing off, summary off, analyze) select * from t, pt where tid = ptid; - QUERY PLAN ------------------------------------------------------------------------------------------ + QUERY PLAN +--------------------------------------------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) (actual rows=18 loops=1) - -> Nested Loop (actual rows=8 loops=1) - 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) - Index Cond: (ptid = t.tid) - Number of partitions to scan: 6 - Partitions scanned: Avg 6.0 x 3 workers of 2 scans. Max 6 parts (seg0). - Optimizer: Pivotal Optimizer (GPORCA) -(10 rows) + -> Hash Join (actual rows=8 loops=1) + Hash Cond: (pt.ptid = t.tid) + Extra Text: (seg1) Hash chain length 1.0 avg, 1 max, using 2 of 262144 buckets. + -> Append (actual rows=8 loops=1) + Partition Selectors: $0 + -> Seq Scan on pt_1_prt_2 pt_1 (actual rows=5 loops=1) + -> Seq Scan on pt_1_prt_3 pt_2 (actual rows=3 loops=1) + -> Seq Scan on pt_1_prt_4 pt_3 (never executed) + -> Seq Scan on pt_1_prt_5 pt_4 (never executed) + -> Seq Scan on pt_1_prt_6 pt_5 (never executed) + -> Seq Scan on pt_1_prt_junk_data pt_6 (never executed) + -> Hash (actual rows=2 loops=1) + Buckets: 262144 Batches: 1 Memory Usage: 2049kB + -> 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) + Optimizer: Postgres query optimizer +(18 rows) select * from t, pt where tid = ptid; dist | tid | t1 | t2 | dist | pt1 | pt2 | pt3 | ptid @@ -115,19 +123,27 @@ select * from t, pt where tid = ptid; (18 rows) explain (costs off, timing off, summary off, analyze) select * from t, pt where tid + 1 = ptid; - QUERY PLAN ------------------------------------------------------------------------------------------ + QUERY PLAN +--------------------------------------------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) (actual rows=18 loops=1) - -> Nested Loop (actual rows=8 loops=1) - 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) - Index Cond: (ptid = (t.tid + 1)) - Number of partitions to scan: 6 - Partitions scanned: Avg 6.0 x 3 workers of 2 scans. Max 6 parts (seg0). - Optimizer: Pivotal Optimizer (GPORCA) -(10 rows) + -> Hash Join (actual rows=8 loops=1) + Hash Cond: (pt.ptid = (t.tid + 1)) + Extra Text: (seg1) Hash chain length 1.0 avg, 1 max, using 2 of 262144 buckets. + -> Append (actual rows=8 loops=1) + Partition Selectors: $0 + -> Seq Scan on pt_1_prt_2 pt_1 (never executed) + -> Seq Scan on pt_1_prt_3 pt_2 (actual rows=3 loops=1) + -> Seq Scan on pt_1_prt_4 pt_3 (actual rows=5 loops=1) + -> Seq Scan on pt_1_prt_5 pt_4 (never executed) + -> Seq Scan on pt_1_prt_6 pt_5 (never executed) + -> Seq Scan on pt_1_prt_junk_data pt_6 (never executed) + -> Hash (actual rows=2 loops=1) + Buckets: 262144 Batches: 1 Memory Usage: 2049kB + -> 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) + Optimizer: Postgres query optimizer +(18 rows) select * from t, pt where tid + 1 = ptid; dist | tid | t1 | t2 | dist | pt1 | pt2 | pt3 | ptid @@ -153,20 +169,28 @@ select * from t, pt where tid + 1 = ptid; (18 rows) explain (costs off, timing off, summary off, analyze) select * from t, pt where tid = ptid and t1 = 'hello' || tid; - QUERY PLAN ------------------------------------------------------------------------------------------ + QUERY PLAN +--------------------------------------------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) (actual rows=18 loops=1) - -> Nested Loop (actual rows=8 loops=1) - Join Filter: true - -> 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)) - -> Dynamic Index Scan on ptid_idx on pt (actual rows=4 loops=2) - Index Cond: (ptid = t.tid) - Number of partitions to scan: 6 - Partitions scanned: Avg 6.0 x 3 workers of 2 scans. Max 6 parts (seg0). - Optimizer: Pivotal Optimizer (GPORCA) -(11 rows) + -> Hash Join (actual rows=8 loops=1) + Hash Cond: (pt.ptid = t.tid) + Extra Text: (seg1) Hash chain length 1.0 avg, 1 max, using 2 of 262144 buckets. + -> Append (actual rows=8 loops=1) + Partition Selectors: $0 + -> Seq Scan on pt_1_prt_2 pt_1 (actual rows=5 loops=1) + -> Seq Scan on pt_1_prt_3 pt_2 (actual rows=3 loops=1) + -> Seq Scan on pt_1_prt_4 pt_3 (never executed) + -> Seq Scan on pt_1_prt_5 pt_4 (never executed) + -> Seq Scan on pt_1_prt_6 pt_5 (never executed) + -> Seq Scan on pt_1_prt_junk_data pt_6 (never executed) + -> Hash (actual rows=2 loops=1) + Buckets: 262144 Batches: 1 Memory Usage: 2049kB + -> 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)) + Optimizer: Postgres query optimizer +(19 rows) select * from t, pt where tid = ptid and t1 = 'hello' || tid; dist | tid | t1 | t2 | dist | pt1 | pt2 | pt3 | ptid @@ -192,20 +216,27 @@ select * from t, pt where tid = ptid and t1 = 'hello' || tid; (18 rows) explain (costs off, timing off, summary off, analyze) select * from t, pt where t1 = pt1 and ptid = tid; - QUERY PLAN ------------------------------------------------------------------------------------------ + QUERY PLAN +--------------------------------------------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) (actual rows=2 loops=1) - -> Nested Loop (actual rows=2 loops=1) - 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 pt1_idx on pt (actual rows=1 loops=2) - Index Cond: (pt1 = t.t1) - Filter: ((pt1 = t.t1) AND (ptid = t.tid)) - Number of partitions to scan: 6 - Partitions scanned: Avg 6.0 x 3 workers of 2 scans. Max 6 parts (seg0). - Optimizer: Pivotal Optimizer (GPORCA) -(11 rows) + -> Hash Join (actual rows=2 loops=1) + Hash Cond: ((pt.pt1 = t.t1) AND (pt.ptid = t.tid)) + Extra Text: (seg1) Hash chain length 1.0 avg, 1 max, using 2 of 262144 buckets. + -> Append (actual rows=8 loops=1) + Partition Selectors: $0 + -> Seq Scan on pt_1_prt_2 pt_1 (actual rows=5 loops=1) + -> Seq Scan on pt_1_prt_3 pt_2 (actual rows=3 loops=1) + -> Seq Scan on pt_1_prt_4 pt_3 (never executed) + -> Seq Scan on pt_1_prt_5 pt_4 (never executed) + -> Seq Scan on pt_1_prt_6 pt_5 (never executed) + -> Seq Scan on pt_1_prt_junk_data pt_6 (never executed) + -> Hash (actual rows=2 loops=1) + Buckets: 262144 Batches: 1 Memory Usage: 2049kB + -> 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) + Optimizer: Postgres query optimizer +(18 rows) select * from t, pt where t1 = pt1 and ptid = tid; dist | tid | t1 | t2 | dist | pt1 | pt2 | pt3 | ptid @@ -221,28 +252,25 @@ explain (costs off, timing off, summary off, analyze) select * from pt where pti QUERY PLAN --------------------------------------------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) (actual rows=18 loops=1) - -> Hash Join (actual rows=8 loops=1) + -> Hash Semi Join (actual rows=8 loops=1) Hash Cond: (pt.ptid = t.tid) - Extra Text: (seg1) Hash chain length 1.0 avg, 1 max, using 2 of 262144 buckets. - -> Dynamic Seq Scan on pt (actual rows=8 loops=1) - Number of partitions to scan: 6 - Partitions scanned: Avg 2.0 x 3 workers. Max 2 parts (seg0). + Extra Text: (seg1) Hash chain length 1.0 avg, 1 max, using 2 of 524288 buckets. + -> Append (actual rows=8 loops=1) + Partition Selectors: $0 + -> Seq Scan on pt_1_prt_2 pt_1 (actual rows=5 loops=1) + -> Seq Scan on pt_1_prt_3 pt_2 (actual rows=3 loops=1) + -> Seq Scan on pt_1_prt_4 pt_3 (never executed) + -> Seq Scan on pt_1_prt_5 pt_4 (never executed) + -> Seq Scan on pt_1_prt_6 pt_5 (never executed) + -> Seq Scan on pt_1_prt_junk_data pt_6 (never executed) -> 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) - -> GroupAggregate (actual rows=2 loops=1) - Group Key: t.tid - -> Sort (actual rows=2 loops=1) - Sort Key: t.tid - Sort Method: quicksort Memory: 75kB - Executor Memory: 178kB Segments: 3 Max: 60kB (segment 0) - -> Redistribute Motion 3:3 (slice3; segments: 3) (actual rows=2 loops=1) - Hash Key: t.tid - -> Seq Scan on t (actual rows=2 loops=1) - Filter: (t1 = ('hello'::text || (tid)::text)) - Optimizer: Pivotal Optimizer (GPORCA) -(20 rows) + -> Seq Scan on t (actual rows=2 loops=1) + Filter: (t1 = ('hello'::text || (tid)::text)) + Optimizer: Postgres query optimizer +(19 rows) select * from pt where ptid in (select tid from t where t1 = 'hello' || tid); dist | pt1 | pt2 | pt3 | ptid @@ -274,28 +302,25 @@ explain (costs off, timing off, summary off, analyze) select * from pt where exi QUERY PLAN --------------------------------------------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) (actual rows=18 loops=1) - -> Hash Join (actual rows=8 loops=1) + -> Hash Semi Join (actual rows=8 loops=1) Hash Cond: (pt.ptid = t.tid) - Extra Text: (seg1) Hash chain length 1.0 avg, 1 max, using 2 of 262144 buckets. - -> Dynamic Seq Scan on pt (actual rows=8 loops=1) - Number of partitions to scan: 6 - Partitions scanned: Avg 2.0 x 3 workers. Max 2 parts (seg0). + Extra Text: (seg1) Hash chain length 1.0 avg, 1 max, using 2 of 524288 buckets. + -> Append (actual rows=8 loops=1) + Partition Selectors: $0 + -> Seq Scan on pt_1_prt_2 pt_1 (actual rows=5 loops=1) + -> Seq Scan on pt_1_prt_3 pt_2 (actual rows=3 loops=1) + -> Seq Scan on pt_1_prt_4 pt_3 (never executed) + -> Seq Scan on pt_1_prt_5 pt_4 (never executed) + -> Seq Scan on pt_1_prt_6 pt_5 (never executed) + -> Seq Scan on pt_1_prt_junk_data pt_6 (never executed) -> 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) - -> GroupAggregate (actual rows=2 loops=1) - Group Key: t.tid - -> Sort (actual rows=2 loops=1) - Sort Key: t.tid - Sort Method: quicksort Memory: 75kB - Executor Memory: 76kB Segments: 3 Max: 26kB (segment 1) - -> Redistribute Motion 3:3 (slice3; segments: 3) (actual rows=2 loops=1) - Hash Key: t.tid - -> Seq Scan on t (actual rows=2 loops=1) - Filter: (t1 = ('hello'::text || (tid)::text)) - Optimizer: Pivotal Optimizer (GPORCA) -(20 rows) + -> Seq Scan on t (actual rows=2 loops=1) + Filter: (t1 = ('hello'::text || (tid)::text)) + Optimizer: Postgres query optimizer +(19 rows) select * from pt where exists (select 1 from t where tid = ptid and t1 = 'hello' || tid); dist | pt1 | pt2 | pt3 | ptid @@ -324,21 +349,29 @@ select * from pt where exists (select 1 from t where tid = ptid and t1 = 'hello' -- group-by on top -- explain (costs off, timing off, summary off, analyze) select count(*) from t, pt where tid = ptid; - QUERY PLAN ------------------------------------------------------------------------------------------------------ + QUERY PLAN +--------------------------------------------------------------------------------------------------------- Finalize Aggregate (actual rows=1 loops=1) -> Gather Motion 3:1 (slice1; segments: 3) (actual rows=3 loops=1) -> Partial Aggregate (actual rows=1 loops=1) - -> Nested Loop (actual rows=8 loops=1) - 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) - Index Cond: (ptid = t.tid) - Number of partitions to scan: 6 - Partitions scanned: Avg 6.0 x 3 workers of 2 scans. Max 6 parts (seg0). - Optimizer: Pivotal Optimizer (GPORCA) -(12 rows) + -> Hash Join (actual rows=8 loops=1) + Hash Cond: (pt.ptid = t.tid) + Extra Text: (seg1) Hash chain length 1.0 avg, 1 max, using 2 of 524288 buckets. + -> Append (actual rows=8 loops=1) + Partition Selectors: $0 + -> Seq Scan on pt_1_prt_2 pt_1 (actual rows=5 loops=1) + -> Seq Scan on pt_1_prt_3 pt_2 (actual rows=3 loops=1) + -> Seq Scan on pt_1_prt_4 pt_3 (never executed) + -> Seq Scan on pt_1_prt_5 pt_4 (never executed) + -> Seq Scan on pt_1_prt_6 pt_5 (never executed) + -> Seq Scan on pt_1_prt_junk_data pt_6 (never executed) + -> 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) + Optimizer: Postgres query optimizer +(20 rows) select count(*) from t, pt where tid = ptid; count @@ -350,8 +383,8 @@ select count(*) from t, pt where tid = ptid; -- window function on top -- explain (costs off, timing off, summary off, analyze) select *, rank() over (order by ptid,pt1) from t, pt where tid = ptid; - QUERY PLAN ------------------------------------------------------------------------------------------------------ + QUERY PLAN +--------------------------------------------------------------------------------------------------------- WindowAgg (actual rows=18 loops=1) Order By: pt.ptid, pt.pt1 -> Gather Motion 3:1 (slice1; segments: 3) (actual rows=18 loops=1) @@ -359,17 +392,24 @@ explain (costs off, timing off, summary off, analyze) select *, rank() over (ord -> Sort (actual rows=8 loops=1) Sort Key: pt.ptid, pt.pt1 Sort Method: quicksort Memory: 76kB - Executor Memory: 85kB Segments: 3 Max: 29kB (segment 1) - -> Nested Loop (actual rows=8 loops=1) - 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) - Index Cond: (ptid = t.tid) - Number of partitions to scan: 6 - Partitions scanned: Avg 6.0 x 3 workers of 2 scans. Max 6 parts (seg0). - Optimizer: Pivotal Optimizer (GPORCA) -(16 rows) + -> Hash Join (actual rows=8 loops=1) + Hash Cond: (pt.ptid = t.tid) + Extra Text: (seg1) Hash chain length 1.0 avg, 1 max, using 2 of 131072 buckets. + -> Append (actual rows=8 loops=1) + Partition Selectors: $0 + -> Seq Scan on pt_1_prt_2 pt_1 (actual rows=5 loops=1) + -> Seq Scan on pt_1_prt_3 pt_2 (actual rows=3 loops=1) + -> Seq Scan on pt_1_prt_4 pt_3 (never executed) + -> Seq Scan on pt_1_prt_5 pt_4 (never executed) + -> Seq Scan on pt_1_prt_6 pt_5 (never executed) + -> Seq Scan on pt_1_prt_junk_data pt_6 (never executed) + -> Hash (actual rows=2 loops=1) + Buckets: 131072 Batches: 1 Memory Usage: 1025kB + -> 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) + Optimizer: Postgres query optimizer +(24 rows) select *, rank() over (order by ptid,pt1) from t, pt where tid = ptid; dist | tid | t1 | t2 | dist | pt1 | pt2 | pt3 | ptid | rank @@ -400,28 +440,44 @@ select *, rank() over (order by ptid,pt1) from t, pt where tid = ptid; explain (costs off, timing off, summary off, analyze) select * from t, pt where tid = ptid union all select * from t, pt where tid + 2 = ptid; - QUERY PLAN ------------------------------------------------------------------------------------------------ + QUERY PLAN +--------------------------------------------------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) (actual rows=36 loops=1) -> Append (actual rows=14 loops=1) - -> Nested Loop (actual rows=8 loops=1) - 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) - Index Cond: (ptid = t.tid) - Number of partitions to scan: 6 - Partitions scanned: Avg 6.0 x 3 workers of 2 scans. Max 6 parts (seg0). - -> Nested Loop (actual rows=7 loops=1) - Join Filter: true - -> Broadcast Motion 3:3 (slice3; segments: 3) (actual rows=2 loops=1) - -> Seq Scan on t t_1 (actual rows=2 loops=1) - -> Dynamic Index Scan on ptid_idx on pt pt_1 (actual rows=4 loops=2) - Index Cond: (ptid = (t_1.tid + 2)) - Number of partitions to scan: 6 - Partitions scanned: Avg 6.0 x 3 workers of 2 scans. Max 6 parts (seg0). - Optimizer: Pivotal Optimizer (GPORCA) -(19 rows) + -> Hash Join (actual rows=8 loops=1) + Hash Cond: (pt.ptid = t.tid) + Extra Text: (seg1) Hash chain length 1.0 avg, 1 max, using 2 of 131072 buckets. + -> Append (actual rows=8 loops=1) + Partition Selectors: $0 + -> Seq Scan on pt_1_prt_2 pt_1 (actual rows=5 loops=1) + -> Seq Scan on pt_1_prt_3 pt_2 (actual rows=3 loops=1) + -> Seq Scan on pt_1_prt_4 pt_3 (never executed) + -> Seq Scan on pt_1_prt_5 pt_4 (never executed) + -> Seq Scan on pt_1_prt_6 pt_5 (never executed) + -> Seq Scan on pt_1_prt_junk_data pt_6 (never executed) + -> Hash (actual rows=2 loops=1) + Buckets: 131072 Batches: 1 Memory Usage: 1025kB + -> 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) + -> Hash Join (actual rows=7 loops=1) + Hash Cond: (pt_7.ptid = (t_1.tid + 2)) + Extra Text: (seg0) Hash chain length 1.0 avg, 1 max, using 2 of 131072 buckets. + -> Append (actual rows=7 loops=1) + Partition Selectors: $1 + -> Seq Scan on pt_1_prt_2 pt_8 (never executed) + -> Seq Scan on pt_1_prt_3 pt_9 (never executed) + -> Seq Scan on pt_1_prt_4 pt_10 (actual rows=5 loops=1) + -> Seq Scan on pt_1_prt_5 pt_11 (actual rows=5 loops=1) + -> Seq Scan on pt_1_prt_6 pt_12 (never executed) + -> Seq Scan on pt_1_prt_junk_data pt_13 (never executed) + -> Hash (actual rows=2 loops=1) + Buckets: 131072 Batches: 1 Memory Usage: 1025kB + -> Partition Selector (selector id: $1) (actual rows=2 loops=1) + -> Broadcast Motion 3:3 (slice3; segments: 3) (actual rows=2 loops=1) + -> Seq Scan on t t_1 (actual rows=2 loops=1) + Optimizer: Postgres query optimizer +(35 rows) select * from t, pt where tid = ptid union all @@ -474,30 +530,48 @@ explain (costs off, timing off, summary off, analyze) select count(*) from union all select * from t, pt where tid + 2 = ptid ) foo; - QUERY PLAN ------------------------------------------------------------------------------------------------------------ + QUERY PLAN +--------------------------------------------------------------------------------------------------------------------- Finalize Aggregate (actual rows=1 loops=1) -> Gather Motion 3:1 (slice1; segments: 3) (actual rows=3 loops=1) -> Partial Aggregate (actual rows=1 loops=1) -> Append (actual rows=14 loops=1) - -> Nested Loop (actual rows=8 loops=1) - 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) - Index Cond: (ptid = t.tid) - Number of partitions to scan: 6 - Partitions scanned: Avg 6.0 x 3 workers of 2 scans. Max 6 parts (seg0). - -> Nested Loop (actual rows=7 loops=1) - Join Filter: true - -> Broadcast Motion 3:3 (slice3; segments: 3) (actual rows=2 loops=1) - -> Seq Scan on t t_1 (actual rows=2 loops=1) - -> Dynamic Index Scan on ptid_idx on pt pt_1 (actual rows=4 loops=2) - Index Cond: (ptid = (t_1.tid + 2)) - Number of partitions to scan: 6 - Partitions scanned: Avg 6.0 x 3 workers of 2 scans. Max 6 parts (seg0). - Optimizer: Pivotal Optimizer (GPORCA) -(21 rows) + -> Subquery Scan on "*SELECT* 1" (actual rows=8 loops=1) + -> Hash Join (actual rows=8 loops=1) + Hash Cond: (pt.ptid = t.tid) + Extra Text: (seg1) Hash chain length 1.0 avg, 1 max, using 2 of 262144 buckets. + -> Append (actual rows=8 loops=1) + Partition Selectors: $0 + -> Seq Scan on pt_1_prt_2 pt_1 (actual rows=5 loops=1) + -> Seq Scan on pt_1_prt_3 pt_2 (actual rows=3 loops=1) + -> Seq Scan on pt_1_prt_4 pt_3 (never executed) + -> Seq Scan on pt_1_prt_5 pt_4 (never executed) + -> Seq Scan on pt_1_prt_6 pt_5 (never executed) + -> Seq Scan on pt_1_prt_junk_data pt_6 (never executed) + -> Hash (actual rows=2 loops=1) + Buckets: 262144 Batches: 1 Memory Usage: 2049kB + -> 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) + -> Subquery Scan on "*SELECT* 2" (actual rows=7 loops=1) + -> Hash Join (actual rows=7 loops=1) + Hash Cond: (pt_7.ptid = (t_1.tid + 2)) + Extra Text: (seg0) Hash chain length 1.0 avg, 1 max, using 2 of 262144 buckets. + -> Append (actual rows=7 loops=1) + Partition Selectors: $1 + -> Seq Scan on pt_1_prt_2 pt_8 (never executed) + -> Seq Scan on pt_1_prt_3 pt_9 (never executed) + -> Seq Scan on pt_1_prt_4 pt_10 (actual rows=5 loops=1) + -> Seq Scan on pt_1_prt_5 pt_11 (actual rows=5 loops=1) + -> Seq Scan on pt_1_prt_6 pt_12 (never executed) + -> Seq Scan on pt_1_prt_junk_data pt_13 (never executed) + -> Hash (actual rows=2 loops=1) + Buckets: 262144 Batches: 1 Memory Usage: 2049kB + -> Partition Selector (selector id: $1) (actual rows=2 loops=1) + -> Broadcast Motion 3:3 (slice3; segments: 3) (actual rows=2 loops=1) + -> Seq Scan on t t_1 (actual rows=2 loops=1) + Optimizer: Postgres query optimizer +(39 rows) select count(*) from ( select * from t, pt where tid = ptid @@ -516,20 +590,24 @@ set enable_hashjoin=off; set enable_nestloop=on; set enable_mergejoin=off; explain (costs off, timing off, summary off, analyze) select * from t, pt where tid = ptid; - QUERY PLAN ------------------------------------------------------------------------------------------ + QUERY PLAN +------------------------------------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) (actual rows=18 loops=1) -> Nested Loop (actual rows=8 loops=1) Join Filter: (t.tid = pt.ptid) Rows Removed by Join Filter: 30 -> 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) - Index Cond: (ptid = t.tid) - Number of partitions to scan: 6 - Partitions scanned: Avg 6.0 x 3 workers of 2 scans. Max 6 parts (seg0). - Optimizer: Pivotal Optimizer (GPORCA) -(10 rows) + -> Materialize (actual rows=20 loops=2) + -> Append (actual rows=20 loops=1) + -> Seq Scan on pt_1_prt_2 pt_1 (actual rows=5 loops=1) + -> Seq Scan on pt_1_prt_3 pt_2 (actual rows=3 loops=1) + -> Seq Scan on pt_1_prt_4 pt_3 (actual rows=5 loops=1) + -> Seq Scan on pt_1_prt_5 pt_4 (actual rows=5 loops=1) + -> Seq Scan on pt_1_prt_6 pt_5 (actual rows=4 loops=1) + -> Seq Scan on pt_1_prt_junk_data pt_6 (actual rows=3 loops=1) + Optimizer: Postgres query optimizer +(15 rows) select * from t, pt where tid = ptid; dist | tid | t1 | t2 | dist | pt1 | pt2 | pt3 | ptid @@ -566,22 +644,30 @@ set enable_hashjoin=off; -- Known_opt_diff: MPP-21322 -- end_ignore explain (costs off, timing off, summary off, analyze) select * from t, pt where tid = ptid and pt1 = 'hello0'; - QUERY PLAN ------------------------------------------------------------------------------------- - Hash Join (actual rows=1 loops=1) - Hash Cond: (t.tid = pt.ptid) - Extra Text: Hash chain length 1.0 avg, 1 max, using 1 of 262144 buckets. - -> Gather Motion 3:1 (slice1; segments: 3) (actual rows=2 loops=1) - -> Seq Scan on t (actual rows=2 loops=1) - -> Hash (actual rows=1 loops=1) - Buckets: 262144 Batches: 1 Memory Usage: 2049kB - -> Gather Motion 3:1 (slice2; segments: 3) (actual rows=1 loops=1) - -> Dynamic Index Scan on pt1_idx on pt (actual rows=1 loops=1) - Index Cond: (pt1 = 'hello0'::text) - Number of partitions to scan: 6 - Partitions scanned: Avg 6.0 x 3 workers. Max 6 parts (seg0). - Optimizer: Pivotal Optimizer (GPORCA) -(12 rows) + QUERY PLAN +------------------------------------------------------------------------------------------------------------------------ + Gather Motion 3:1 (slice1; segments: 3) (actual rows=1 loops=1) + -> Nested Loop (actual rows=1 loops=1) + Join Filter: (t.tid = pt.ptid) + Rows Removed by Join Filter: 1 + -> Broadcast Motion 3:3 (slice2; segments: 3) (actual rows=2 loops=1) + -> Seq Scan on t (actual rows=2 loops=1) + -> Materialize (actual rows=1 loops=2) + -> Append (actual rows=1 loops=1) + -> Index Scan using pt_1_prt_2_pt1_idx on pt_1_prt_2 pt_1 (actual rows=1 loops=1) + Index Cond: (pt1 = 'hello0'::text) + -> Index Scan using pt_1_prt_3_pt1_idx on pt_1_prt_3 pt_2 (actual rows=0 loops=1) + Index Cond: (pt1 = 'hello0'::text) + -> Index Scan using pt_1_prt_4_pt1_idx on pt_1_prt_4 pt_3 (actual rows=0 loops=1) + Index Cond: (pt1 = 'hello0'::text) + -> Index Scan using pt_1_prt_5_pt1_idx on pt_1_prt_5 pt_4 (actual rows=0 loops=1) + Index Cond: (pt1 = 'hello0'::text) + -> Index Scan using pt_1_prt_6_pt1_idx on pt_1_prt_6 pt_5 (actual rows=0 loops=1) + Index Cond: (pt1 = 'hello0'::text) + -> Index Scan using pt_1_prt_junk_data_pt1_idx on pt_1_prt_junk_data pt_6 (actual rows=0 loops=1) + Index Cond: (pt1 = 'hello0'::text) + Optimizer: Postgres query optimizer +(21 rows) select * from t, pt where tid = ptid and pt1 = 'hello0'; dist | tid | t1 | t2 | dist | pt1 | pt2 | pt3 | ptid @@ -597,20 +683,24 @@ set enable_indexscan=on; set enable_seqscan=off; set enable_hashjoin=off; explain (costs off, timing off, summary off, analyze) select * from t, pt where tid = ptid; - QUERY PLAN ------------------------------------------------------------------------------------------ + QUERY PLAN +------------------------------------------------------------------------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) (actual rows=18 loops=1) -> Nested Loop (actual rows=8 loops=1) Join Filter: (t.tid = pt.ptid) Rows Removed by Join Filter: 30 -> 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) - Index Cond: (ptid = t.tid) - Number of partitions to scan: 6 - Partitions scanned: Avg 6.0 x 3 workers of 2 scans. Max 6 parts (seg0). - Optimizer: Pivotal Optimizer (GPORCA) -(10 rows) + -> Materialize (actual rows=20 loops=2) + -> Append (actual rows=20 loops=1) + -> Index Scan using pt_1_prt_2_ptid_idx on pt_1_prt_2 pt_1 (actual rows=5 loops=1) + -> Index Scan using pt_1_prt_3_ptid_idx on pt_1_prt_3 pt_2 (actual rows=3 loops=1) + -> Index Scan using pt_1_prt_4_ptid_idx on pt_1_prt_4 pt_3 (actual rows=5 loops=1) + -> Index Scan using pt_1_prt_5_ptid_idx on pt_1_prt_5 pt_4 (actual rows=5 loops=1) + -> Index Scan using pt_1_prt_6_ptid_idx on pt_1_prt_6 pt_5 (actual rows=4 loops=1) + -> Index Scan using pt_1_prt_junk_data_ptid_idx on pt_1_prt_junk_data pt_6 (actual rows=3 loops=1) + Optimizer: Postgres query optimizer +(15 rows) select * from t, pt where tid = ptid; dist | tid | t1 | t2 | dist | pt1 | pt2 | pt3 | ptid @@ -643,19 +733,25 @@ set enable_seqscan=on; set enable_hashjoin=on; set enable_nestloop=off; explain (costs off, timing off, summary off, analyze) select * from t, pt where t1 = pt1; - QUERY PLAN ------------------------------------------------------------------------------------------ + QUERY PLAN +------------------------------------------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) (actual rows=2 loops=1) - -> Nested Loop (actual rows=2 loops=1) - 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 pt1_idx on pt (actual rows=1 loops=2) - Index Cond: (pt1 = t.t1) - Number of partitions to scan: 6 - Partitions scanned: Avg 6.0 x 3 workers of 2 scans. Max 6 parts (seg0). - Optimizer: Pivotal Optimizer (GPORCA) -(10 rows) + -> Hash Join (actual rows=2 loops=1) + Hash Cond: (pt.pt1 = t.t1) + Extra Text: (seg1) Hash chain length 1.0 avg, 1 max, using 2 of 262144 buckets. + -> Append (actual rows=20 loops=1) + -> Seq Scan on pt_1_prt_2 pt_1 (actual rows=5 loops=1) + -> Seq Scan on pt_1_prt_3 pt_2 (actual rows=3 loops=1) + -> Seq Scan on pt_1_prt_4 pt_3 (actual rows=5 loops=1) + -> Seq Scan on pt_1_prt_5 pt_4 (actual rows=5 loops=1) + -> Seq Scan on pt_1_prt_6 pt_5 (actual rows=4 loops=1) + -> Seq Scan on pt_1_prt_junk_data pt_6 (actual rows=3 loops=1) + -> Hash (actual rows=2 loops=1) + Buckets: 262144 Batches: 1 Memory Usage: 2049kB + -> Broadcast Motion 3:3 (slice2; segments: 3) (actual rows=2 loops=1) + -> Seq Scan on t (actual rows=2 loops=1) + Optimizer: Postgres query optimizer +(16 rows) select * from t, pt where t1 = pt1; dist | tid | t1 | t2 | dist | pt1 | pt2 | pt3 | ptid @@ -665,20 +761,24 @@ select * from t, pt where t1 = pt1; (2 rows) explain (costs off, timing off, summary off, analyze) select * from t, pt where tid < ptid; - QUERY PLAN ------------------------------------------------------------------------------------------ + QUERY PLAN +------------------------------------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) (actual rows=81 loops=1) -> Nested Loop (actual rows=31 loops=1) Join Filter: (t.tid < pt.ptid) Rows Removed by Join Filter: 9 -> 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=16 loops=2) - Index Cond: (ptid > t.tid) - Number of partitions to scan: 6 - Partitions scanned: Avg 6.0 x 3 workers of 2 scans. Max 6 parts (seg0). - Optimizer: Pivotal Optimizer (GPORCA) -(10 rows) + -> Materialize (actual rows=20 loops=2) + -> Append (actual rows=20 loops=1) + -> Seq Scan on pt_1_prt_2 pt_1 (actual rows=5 loops=1) + -> Seq Scan on pt_1_prt_3 pt_2 (actual rows=3 loops=1) + -> Seq Scan on pt_1_prt_4 pt_3 (actual rows=5 loops=1) + -> Seq Scan on pt_1_prt_5 pt_4 (actual rows=5 loops=1) + -> Seq Scan on pt_1_prt_6 pt_5 (actual rows=4 loops=1) + -> Seq Scan on pt_1_prt_junk_data pt_6 (actual rows=3 loops=1) + Optimizer: Postgres query optimizer +(15 rows) select * from t, pt where tid < ptid; dist | tid | t1 | t2 | dist | pt1 | pt2 | pt3 | ptid @@ -775,26 +875,34 @@ reset enable_nestloop; -- -- one of the joined tables can be used for partition elimination, the other can not explain (costs off, timing off, summary off, analyze) select * from t, t1, pt where t1.t2 = t.t2 and t1.tid = ptid; - QUERY PLAN ------------------------------------------------------------------------------------------------ - Hash Join (actual rows=36 loops=1) - Hash Cond: (t1.t2 = t.t2) - Extra Text: Hash chain length 2.0 avg, 2 max, using 1 of 262144 buckets. - -> Gather Motion 3:1 (slice1; segments: 3) (actual rows=18 loops=1) - -> Nested Loop (actual rows=8 loops=1) - Join Filter: true - -> Broadcast Motion 3:3 (slice2; segments: 3) (actual rows=2 loops=1) - -> Seq Scan on t1 (actual rows=1 loops=1) - -> Dynamic Index Scan on ptid_idx on pt (actual rows=4 loops=2) - Index Cond: (ptid = t1.tid) - Number of partitions to scan: 6 - Partitions scanned: Avg 6.0 x 3 workers of 2 scans. Max 6 parts (seg0). - -> Hash (actual rows=2 loops=1) - Buckets: 262144 Batches: 1 Memory Usage: 2049kB - -> Gather Motion 3:1 (slice3; segments: 3) (actual rows=2 loops=1) - -> Seq Scan on t (actual rows=2 loops=1) - Optimizer: Pivotal Optimizer (GPORCA) -(16 rows) + QUERY PLAN +--------------------------------------------------------------------------------------------------- + Gather Motion 3:1 (slice1; segments: 3) (actual rows=36 loops=1) + -> Hash Join (actual rows=16 loops=1) + Hash Cond: (t1.t2 = t.t2) + Extra Text: (seg1) Hash chain length 2.0 avg, 2 max, using 1 of 131072 buckets. + -> Hash Join (actual rows=8 loops=1) + Hash Cond: (pt.ptid = t1.tid) + Extra Text: (seg1) Hash chain length 1.0 avg, 1 max, using 2 of 131072 buckets. + -> Append (actual rows=8 loops=1) + Partition Selectors: $0 + -> Seq Scan on pt_1_prt_2 pt_1 (never executed) + -> Seq Scan on pt_1_prt_3 pt_2 (actual rows=3 loops=1) + -> Seq Scan on pt_1_prt_4 pt_3 (actual rows=5 loops=1) + -> Seq Scan on pt_1_prt_5 pt_4 (never executed) + -> Seq Scan on pt_1_prt_6 pt_5 (never executed) + -> Seq Scan on pt_1_prt_junk_data pt_6 (never executed) + -> Hash (actual rows=2 loops=1) + Buckets: 131072 Batches: 1 Memory Usage: 1025kB + -> 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 t1 (actual rows=1 loops=1) + -> Hash (actual rows=2 loops=1) + Buckets: 131072 Batches: 1 Memory Usage: 1025kB + -> Broadcast Motion 3:3 (slice3; segments: 3) (actual rows=2 loops=1) + -> Seq Scan on t (actual rows=2 loops=1) + Optimizer: Postgres query optimizer +(25 rows) select * from t, t1, pt where t1.t2 = t.t2 and t1.tid = ptid; dist | tid | t1 | t2 | dist | tid | t1 | t2 | dist | pt1 | pt2 | pt3 | ptid @@ -846,29 +954,35 @@ insert into t1 select i, -100, 'dummy' from generate_series(1,10) i; analyze t; analyze t1; explain (costs off, timing off, summary off, analyze) select * from t, t1, pt where t1.tid = ptid and t.tid = ptid; - QUERY PLAN ------------------------------------------------------------------------------------------------------------- + QUERY PLAN +---------------------------------------------------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) (actual rows=9 loops=1) - -> Hash Join (actual rows=9 loops=1) - Hash Cond: (t.tid = pt.ptid) - Extra Text: (seg1) Hash chain length 9.0 avg, 9 max, using 1 of 262144 buckets. - -> Redistribute Motion 3:3 (slice2; segments: 3) (actual rows=2 loops=1) - Hash Key: t.tid - -> Seq Scan on t (actual rows=5 loops=1) - -> Hash (actual rows=9 loops=1) - Buckets: 262144 Batches: 1 Memory Usage: 2049kB - -> Redistribute Motion 3:3 (slice3; segments: 3) (actual rows=9 loops=1) - Hash Key: pt.ptid - -> Nested Loop (actual rows=8 loops=1) - Join Filter: true - -> Broadcast Motion 3:3 (slice4; segments: 3) (actual rows=12 loops=1) - -> Seq Scan on t1 (actual rows=6 loops=1) - -> Dynamic Index Scan on ptid_idx on pt (actual rows=1 loops=12) - Index Cond: (ptid = t1.tid) - Number of partitions to scan: 6 - Partitions scanned: Avg 6.0 x 3 workers of 12 scans. Max 6 parts (seg0). - Optimizer: Pivotal Optimizer (GPORCA) -(19 rows) + -> Hash Join (actual rows=3 loops=1) + Hash Cond: (pt.ptid = t1.tid) + Extra Text: (seg0) Hash chain length 4.0 avg, 10 max, using 3 of 131072 buckets. + -> Hash Join (actual rows=3 loops=1) + Hash Cond: (pt.ptid = t.tid) + Extra Text: (seg0) Hash chain length 4.0 avg, 10 max, using 3 of 131072 buckets. + -> Append (actual rows=6 loops=1) + Partition Selectors: $0, $1 + -> Seq Scan on pt_1_prt_2 pt_1 (never executed) + -> Seq Scan on pt_1_prt_3 pt_2 (actual rows=3 loops=1) + -> Seq Scan on pt_1_prt_4 pt_3 (never executed) + -> Seq Scan on pt_1_prt_5 pt_4 (never executed) + -> Seq Scan on pt_1_prt_6 pt_5 (never executed) + -> Seq Scan on pt_1_prt_junk_data pt_6 (actual rows=3 loops=1) + -> Hash (actual rows=12 loops=1) + Buckets: 131072 Batches: 1 Memory Usage: 1025kB + -> Partition Selector (selector id: $0) (actual rows=12 loops=1) + -> Broadcast Motion 3:3 (slice2; segments: 3) (actual rows=12 loops=1) + -> Seq Scan on t (actual rows=5 loops=1) + -> Hash (actual rows=12 loops=1) + Buckets: 131072 Batches: 1 Memory Usage: 1025kB + -> Partition Selector (selector id: $1) (actual rows=12 loops=1) + -> Broadcast Motion 3:3 (slice3; segments: 3) (actual rows=12 loops=1) + -> Seq Scan on t1 (actual rows=6 loops=1) + Optimizer: Postgres query optimizer +(26 rows) select * from t, t1, pt where t1.tid = ptid and t.tid = ptid; dist | tid | t1 | t2 | dist | tid | t1 | t2 | dist | pt1 | pt2 | pt3 | ptid @@ -893,26 +1007,43 @@ begin; set local from_collapse_limit = 1; set local join_collapse_limit = 1; explain (costs off, timing off, summary off, analyze) select * from t1 inner join (select pt1.*, pt2.ptid as ptid2 from pt as pt1, pt as pt2 WHERE pt1.ptid <= pt2.ptid and pt1.dist = pt2.dist ) as ptx ON t1.dist = ptx.dist and t1.tid = ptx.ptid and t1.tid = ptx.ptid2; - QUERY PLAN -------------------------------------------------------------------------------------- + QUERY PLAN +-------------------------------------------------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) (actual rows=2 loops=1) - -> Nested Loop (actual rows=1 loops=1) - Join Filter: true - -> Nested Loop (actual rows=1 loops=1) - Join Filter: true - -> Seq Scan on t1 (actual rows=1 loops=1) - -> Dynamic Index Scan on ptid_idx on pt (actual rows=1 loops=1) - Index Cond: (ptid = t1.tid) - Filter: ((ptid = t1.tid) AND (t1.dist = dist)) - Number of partitions to scan: 6 - Partitions scanned: Avg 6.0 x 2 workers. Max 6 parts (seg0). - -> Dynamic Index Scan on ptid_idx on pt pt_1 (actual rows=1 loops=1) - Index Cond: ((ptid <= pt.ptid) AND (ptid = pt.ptid)) - Filter: ((ptid <= pt.ptid) AND (ptid = pt.ptid) AND (dist = pt.dist)) - Number of partitions to scan: 6 - Partitions scanned: Avg 6.0 x 2 workers. Max 6 parts (seg0). - Optimizer: Pivotal Optimizer (GPORCA) -(17 rows) + -> Hash Join (actual rows=1 loops=1) + Hash Cond: ((pt1.dist = t1.dist) AND (pt1.ptid = t1.tid)) + Join Filter: ((t1.tid <= pt2.ptid) AND (pt1.ptid <= t1.tid)) + Extra Text: (seg1) Hash chain length 1.0 avg, 1 max, using 1 of 131072 buckets. + -> Hash Join (actual rows=3 loops=1) + Hash Cond: ((pt1.dist = pt2.dist) AND (pt1.ptid = pt2.ptid)) + Join Filter: (pt1.ptid <= pt2.ptid) + Extra Text: (seg1) Hash chain length 1.0 avg, 1 max, using 14 of 262144 buckets. + -> Append (actual rows=3 loops=1) + Partition Selectors: $0, $1 + -> Seq Scan on pt_1_prt_2 pt1_1 (never executed) + -> Seq Scan on pt_1_prt_3 pt1_2 (actual rows=3 loops=1) + -> Seq Scan on pt_1_prt_4 pt1_3 (actual rows=2 loops=1) + -> Seq Scan on pt_1_prt_5 pt1_4 (never executed) + -> Seq Scan on pt_1_prt_6 pt1_5 (never executed) + -> Seq Scan on pt_1_prt_junk_data pt1_6 (never executed) + -> Hash (actual rows=14 loops=1) + Buckets: 262144 Batches: 1 Memory Usage: 2049kB + -> Partition Selector (selector id: $0) (actual rows=14 loops=1) + -> Append (actual rows=14 loops=1) + Partition Selectors: $2 + -> Seq Scan on pt_1_prt_2 pt2_1 (never executed) + -> Seq Scan on pt_1_prt_3 pt2_2 (actual rows=3 loops=1) + -> Seq Scan on pt_1_prt_4 pt2_3 (actual rows=5 loops=1) + -> Seq Scan on pt_1_prt_5 pt2_4 (actual rows=5 loops=1) + -> Seq Scan on pt_1_prt_6 pt2_5 (actual rows=4 loops=1) + -> Seq Scan on pt_1_prt_junk_data pt2_6 (actual rows=3 loops=1) + -> Hash (actual rows=1 loops=1) + Buckets: 131072 Batches: 1 Memory Usage: 1025kB + -> Partition Selector (selector id: $2) (actual rows=1 loops=1) + -> Partition Selector (selector id: $1) (actual rows=1 loops=1) + -> Seq Scan on t1 (actual rows=1 loops=1) + Optimizer: Postgres query optimizer +(34 rows) rollback; -- @@ -920,8 +1051,8 @@ rollback; -- not projection capable. -- explain (costs off, timing off, summary off, analyze) select * from pt, pt1 where pt.ptid = pt1.ptid and pt.pt1 = 'hello0' order by pt1.dist; - QUERY PLAN ------------------------------------------------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) (actual rows=9 loops=1) Merge Key: pt1.dist -> Sort (actual rows=5 loops=1) @@ -930,19 +1061,39 @@ explain (costs off, timing off, summary off, analyze) select * from pt, pt1 wher -> Hash Join (actual rows=5 loops=1) Hash Cond: (pt1.ptid = pt.ptid) Extra Text: (seg1) Hash chain length 1.0 avg, 1 max, using 1 of 131072 buckets. - -> Dynamic Seq Scan on pt1 (actual rows=5 loops=1) - Number of partitions to scan: 6 - Partitions scanned: Avg 1.0 x 3 workers. Max 1 parts (seg0). + -> Append (actual rows=5 loops=1) + Partition Selectors: $0 + -> Seq Scan on pt1_1_prt_2 pt1_1 (actual rows=5 loops=1) + -> Seq Scan on pt1_1_prt_3 pt1_2 (never executed) + -> Seq Scan on pt1_1_prt_4 pt1_3 (never executed) + -> Seq Scan on pt1_1_prt_5 pt1_4 (never executed) + -> Seq Scan on pt1_1_prt_6 pt1_5 (never executed) + -> Seq Scan on pt1_1_prt_junk_data pt1_6 (never executed) -> Hash (actual rows=1 loops=1) Buckets: 131072 Batches: 1 Memory Usage: 1025kB -> Partition Selector (selector id: $0) (actual rows=1 loops=1) -> Broadcast Motion 3:3 (slice2; segments: 3) (actual rows=1 loops=1) - -> Dynamic Index Scan on pt1_idx on pt (actual rows=1 loops=1) - Index Cond: (pt1 = 'hello0'::text) - Number of partitions to scan: 6 - Partitions scanned: Avg 6.0 x 3 workers. Max 6 parts (seg0). - Optimizer: Pivotal Optimizer (GPORCA) -(19 rows) + -> Append (actual rows=1 loops=1) + -> Seq Scan on pt_1_prt_2 pt_1 (actual rows=1 loops=1) + Filter: (pt1 = 'hello0'::text) + Rows Removed by Filter: 4 + -> Seq Scan on pt_1_prt_3 pt_2 (actual rows=0 loops=1) + Filter: (pt1 = 'hello0'::text) + Rows Removed by Filter: 3 + -> Seq Scan on pt_1_prt_4 pt_3 (actual rows=0 loops=1) + Filter: (pt1 = 'hello0'::text) + Rows Removed by Filter: 2 + -> Seq Scan on pt_1_prt_5 pt_4 (actual rows=0 loops=1) + Filter: (pt1 = 'hello0'::text) + Rows Removed by Filter: 5 + -> Seq Scan on pt_1_prt_6 pt_5 (actual rows=0 loops=1) + Filter: (pt1 = 'hello0'::text) + Rows Removed by Filter: 4 + -> Seq Scan on pt_1_prt_junk_data pt_6 (actual rows=0 loops=1) + Filter: (pt1 = 'hello0'::text) + Rows Removed by Filter: 3 + Optimizer: Postgres query optimizer +(40 rows) select * from pt, pt1 where pt.ptid = pt1.ptid and pt.pt1 = 'hello0' order by pt1.dist; dist | pt1 | pt2 | pt3 | ptid | dist | pt1 | pt2 | pt3 | ptid @@ -959,27 +1110,47 @@ select * from pt, pt1 where pt.ptid = pt1.ptid and pt.pt1 = 'hello0' order by pt (9 rows) explain (costs off, timing off, summary off, analyze) select count(*) from pt, pt1 where pt.ptid = pt1.ptid and pt.pt1 = 'hello0'; - QUERY PLAN ------------------------------------------------------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------------------------------------------------- Finalize Aggregate (actual rows=1 loops=1) -> Gather Motion 3:1 (slice1; segments: 3) (actual rows=3 loops=1) -> Partial Aggregate (actual rows=1 loops=1) -> Hash Join (actual rows=5 loops=1) Hash Cond: (pt1.ptid = pt.ptid) Extra Text: (seg1) Hash chain length 1.0 avg, 1 max, using 1 of 524288 buckets. - -> Dynamic Seq Scan on pt1 (actual rows=5 loops=1) - Number of partitions to scan: 6 - Partitions scanned: Avg 1.0 x 3 workers. Max 1 parts (seg0). + -> Append (actual rows=5 loops=1) + Partition Selectors: $0 + -> Seq Scan on pt1_1_prt_2 pt1_1 (actual rows=5 loops=1) + -> Seq Scan on pt1_1_prt_3 pt1_2 (never executed) + -> Seq Scan on pt1_1_prt_4 pt1_3 (never executed) + -> Seq Scan on pt1_1_prt_5 pt1_4 (never executed) + -> Seq Scan on pt1_1_prt_6 pt1_5 (never executed) + -> Seq Scan on pt1_1_prt_junk_data pt1_6 (never executed) -> Hash (actual rows=1 loops=1) Buckets: 524288 Batches: 1 Memory Usage: 4097kB -> Partition Selector (selector id: $0) (actual rows=1 loops=1) -> Broadcast Motion 3:3 (slice2; segments: 3) (actual rows=1 loops=1) - -> Dynamic Index Scan on pt1_idx on pt (actual rows=1 loops=1) - Index Cond: (pt1 = 'hello0'::text) - Number of partitions to scan: 6 - Partitions scanned: Avg 6.0 x 3 workers. Max 6 parts (seg0). - Optimizer: Pivotal Optimizer (GPORCA) -(17 rows) + -> Append (actual rows=1 loops=1) + -> Seq Scan on pt_1_prt_2 pt_1 (actual rows=1 loops=1) + Filter: (pt1 = 'hello0'::text) + Rows Removed by Filter: 4 + -> Seq Scan on pt_1_prt_3 pt_2 (actual rows=0 loops=1) + Filter: (pt1 = 'hello0'::text) + Rows Removed by Filter: 3 + -> Seq Scan on pt_1_prt_4 pt_3 (actual rows=0 loops=1) + Filter: (pt1 = 'hello0'::text) + Rows Removed by Filter: 2 + -> Seq Scan on pt_1_prt_5 pt_4 (actual rows=0 loops=1) + Filter: (pt1 = 'hello0'::text) + Rows Removed by Filter: 5 + -> Seq Scan on pt_1_prt_6 pt_5 (actual rows=0 loops=1) + Filter: (pt1 = 'hello0'::text) + Rows Removed by Filter: 4 + -> Seq Scan on pt_1_prt_junk_data pt_6 (actual rows=0 loops=1) + Filter: (pt1 = 'hello0'::text) + Rows Removed by Filter: 3 + Optimizer: Postgres query optimizer +(38 rows) select count(*) from pt, pt1 where pt.ptid = pt1.ptid and pt.pt1 = 'hello0'; count @@ -1063,9 +1234,9 @@ explain (costs off, timing off, summary off, analyze) select * from t, pt where QUERY PLAN -------------------------------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) (actual rows=0 loops=1) - -> Hash Join (never executed) + -> Hash Join (actual rows=0 loops=1) Hash Cond: (pt.b = t.a) - -> Dynamic Seq Scan on pt (never executed) + -> Dynamic Seq Scan on pt (actual rows=0 loops=1) Number of partitions to scan: 5 -> Hash (actual rows=4 loops=1) Buckets: 524288 Batches: 1 Memory Usage: 4097kB @@ -2732,24 +2903,30 @@ set enable_seqscan=off; -- force_explain explain (analyze, timing off, summary off) select * from pt, t where t.dist = pt.dist and t.tid = pt.ptid order by t.tid, t.sk; - QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------ - Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..862.10 rows=98 width=24) (actual rows=5 loops=1) - Merge Key: t.tid, t.sk - -> Sort (cost=0.00..862.09 rows=33 width=24) (actual rows=2 loops=1) - Sort Key: t.tid, t.sk - Sort Method: quicksort Memory: 75kB - Executor Memory: 76kB Segments: 3 Max: 26kB (segment 0) - -> Hash Join (cost=0.00..862.05 rows=33 width=24) (actual rows=2 loops=1) - Hash Cond: ((pt.dist = t.dist) AND (pt.ptid = t.tid)) - Extra Text: (seg0) Hash chain length 1.0 avg, 1 max, using 36 of 262144 buckets. - -> Dynamic Seq Scan on pt (cost=0.00..431.00 rows=33 width=12) (actual rows=36 loops=1) - Number of partitions to scan: 6 - Partitions scanned: Avg 2.0 x 3 workers. Max 2 parts (seg0). - -> Hash (cost=431.00..431.00 rows=33 width=12) (actual rows=37 loops=1) - Buckets: 262144 Batches: 1 Memory Usage: 2050kB - -> Partition Selector (selector id: $0) (cost=0.00..431.00 rows=33 width=12) (actual rows=37 loops=1) - -> Seq Scan on t (cost=0.00..431.00 rows=33 width=12) (actual rows=37 loops=1) - Optimizer: Pivotal Optimizer (GPORCA) -(15 rows) + QUERY PLAN +------------------------------------------------------------------------------------------------------------------------------------------------ + Gather Motion 3:1 (slice1; segments: 3) (cost=10000000008.18..10000000014.83 rows=7 width=24) (actual rows=5 loops=1) + Merge Key: pt.ptid, t.sk + -> Incremental Sort (cost=10000000008.18..10000000014.74 rows=2 width=24) (actual rows=2 loops=1) + Sort Key: pt.ptid, t.sk + Presorted Key: pt.ptid + Full-sort Groups: 1 Sort Method: quicksort Average Memory: 25kB Peak Memory: 25kB + -> Merge Join (cost=10000000003.00..10000000014.64 rows=2 width=24) (actual rows=2 loops=1) + Merge Cond: (pt.ptid = t.tid) + Join Filter: (pt.dist = t.dist) + -> Merge Append (cost=0.85..446.48 rows=39 width=12) (actual rows=3 loops=1) + Sort Key: pt.ptid + -> Index Scan using pt1_ptid_sk_idx on pt1 pt_1 (cost=0.12..8.14 rows=1 width=12) (actual rows=1 loops=1) + -> Index Scan using pt2_ptid_sk_idx on pt2 pt_2 (cost=0.12..8.14 rows=1 width=12) (actual rows=1 loops=1) + -> Index Scan using pt3_ptid_sk_idx on pt3 pt_3 (cost=0.12..8.14 rows=1 width=12) (never executed) + -> Index Scan using pt4_ptid_sk_idx on pt4 pt_4 (cost=0.12..8.14 rows=1 width=12) (never executed) + -> Index Scan using pt5_ptid_sk_idx on pt5 pt_5 (cost=0.12..8.14 rows=1 width=12) (actual rows=1 loops=1) + -> Index Scan using ptdefault_ptid_sk_idx on ptdefault pt_6 (cost=0.14..405.00 rows=34 width=12) (actual rows=2 loops=1) + -> Sort (cost=10000000002.15..10000000002.23 rows=33 width=12) (actual rows=19 loops=2) + Sort Key: t.tid + Sort Method: quicksort Memory: 79kB + -> Partition Selector (selector id: $0) (cost=10000000000.00..10000000001.33 rows=33 width=12) (actual rows=37 loops=1) + -> Seq Scan on t (cost=10000000000.00..10000000001.33 rows=33 width=12) (actual rows=37 loops=1) + Optimizer: Postgres query optimizer +(23 rows) diff --git a/src/test/regress/expected/explain_format_optimizer.out b/src/test/regress/expected/explain_format_optimizer.out index bfbf1a589e..1122773b46 100644 --- a/src/test/regress/expected/explain_format_optimizer.out +++ b/src/test/regress/expected/explain_format_optimizer.out @@ -515,6 +515,7 @@ QUERY PLAN "Segments": 1, "Gang Type": "primary reader", "Parallel Aware": false, + "Async Capable": false, "Relation Name": "jsonexplaintest", "Alias": "jsonexplaintest", "Number of partitions to scan": 1, diff --git a/src/test/regress/expected/gporca_optimizer.out b/src/test/regress/expected/gporca_optimizer.out index d6774aa879..3fc2cea054 100644 --- a/src/test/regress/expected/gporca_optimizer.out +++ b/src/test/regress/expected/gporca_optimizer.out @@ -13354,21 +13354,21 @@ explain (costs off) select * from tpart_dim d join t_ao_btree f on d.a=f.a where (8 rows) explain (costs off) select * from tpart_dim d join tpart_ao_btree f on d.a=f.a where d.b=1; - QUERY PLAN ------------------------------------------------------------------- +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Operator Fallback: InnerIndexNestLoopJoin may have wrong plan not supported + QUERY PLAN +---------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) - -> Nested Loop - Join Filter: true - -> Seq Scan on tpart_dim - Filter: (b = 1) - -> Dynamic Bitmap Heap Scan on tpart_ao_btree - Number of partitions to scan: 2 - Recheck Cond: (a = tpart_dim.a) - Filter: (a = tpart_dim.a) - -> Dynamic Bitmap Index Scan on tpart_ao_btree_ix - Index Cond: (a = tpart_dim.a) - Optimizer: Pivotal Optimizer (GPORCA) -(12 rows) + -> Hash Join + Hash Cond: (f.a = d.a) + -> Append + -> Seq Scan on tpart_ao_btree_1_prt_1 f_1 + -> Seq Scan on tpart_ao_btree_1_prt_2 f_2 + -> Hash + -> Seq Scan on tpart_dim d + Filter: (b = 1) + Optimizer: Postgres query optimizer +(10 rows) -- negative test, make sure we don't use a btree scan on an AO table -- start_ignore @@ -13535,20 +13535,34 @@ default partition def); create INDEX y_idx on y (j); set optimizer_enable_indexjoin=on; explain (costs off) select count(*) from x, y where (x.i > y.j AND x.j <= y.i); - QUERY PLAN ---------------------------------------------------------------- - Aggregate +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Operator Fallback: InnerIndexNestLoopJoin may have wrong plan not supported + QUERY PLAN +------------------------------------------------------------------------------------- + Finalize Aggregate -> Gather Motion 3:1 (slice1; segments: 3) - -> Nested Loop - Join Filter: true - -> Broadcast Motion 3:3 (slice2; segments: 3) - -> Seq Scan on x - -> Dynamic Index Scan on y_idx on y - Index Cond: (j < x.i) - Filter: ((j < x.i) AND (x.j <= i)) - Number of partitions to scan: 5 - Optimizer: Pivotal Optimizer (GPORCA) -(11 rows) + -> Partial Aggregate + -> Nested Loop + -> Broadcast Motion 3:3 (slice2; segments: 3) + -> Seq Scan on x + -> Append + -> Index Scan using y_1_prt_p0_j_idx on y_1_prt_p0 y_1 + Index Cond: (j < x.i) + Filter: (x.j <= i) + -> Index Scan using y_1_prt_p1_j_idx on y_1_prt_p1 y_2 + Index Cond: (j < x.i) + Filter: (x.j <= i) + -> Index Scan using y_1_prt_p2_j_idx on y_1_prt_p2 y_3 + Index Cond: (j < x.i) + Filter: (x.j <= i) + -> Index Scan using y_1_prt_p3_j_idx on y_1_prt_p3 y_4 + Index Cond: (j < x.i) + Filter: (x.j <= i) + -> Index Scan using y_1_prt_def_j_idx on y_1_prt_def y_5 + Index Cond: (j < x.i) + Filter: (x.j <= i) + Optimizer: Postgres query optimizer +(23 rows) reset optimizer_enable_indexjoin; -- InferPredicatesBCC-vcpart-txt.mdp diff --git a/src/test/regress/expected/join_optimizer.out b/src/test/regress/expected/join_optimizer.out index e9d8c38557..436d1ee200 100644 --- a/src/test/regress/expected/join_optimizer.out +++ b/src/test/regress/expected/join_optimizer.out @@ -5402,23 +5402,22 @@ select * from (select *, 12 as phv from parttbl) as ss right join int4_tbl on true where ss.a = ss.phv and f1 = 0; - QUERY PLAN ------------------------------------- + QUERY PLAN +----------------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) -> Nested Loop Join Filter: true -> Result - Filter: (((12) = 12) AND (parttbl1.a = (12))) - -> Result - -> Append - -> Index Scan using parttbl1_pkey on parttbl1 - Index Cond: (a = 12) + 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 -> Materialize -> Broadcast Motion 3:3 (slice2; segments: 3) -> Seq Scan on int4_tbl Filter: (f1 = 0) Optimizer: Pivotal Optimizer (GPORCA) -(14 rows) +(13 rows) select * from (select *, 12 as phv from parttbl) as ss diff --git a/src/test/regress/expected/orca_static_pruning_optimizer.out b/src/test/regress/expected/orca_static_pruning_optimizer.out index a51ea2bac2..45032fd959 100644 --- a/src/test/regress/expected/orca_static_pruning_optimizer.out +++ b/src/test/regress/expected/orca_static_pruning_optimizer.out @@ -241,22 +241,22 @@ SET enable_hashjoin TO off; SET enable_mergejoin TO off; SET enable_nestloop TO on; EXPLAIN (COSTS OFF, VERBOSE) SELECT * FROM foo JOIN bar on foo.a = bar.a AND foo.b = 11; - QUERY PLAN -------------------------------------------------------------------------------------- + QUERY PLAN +-------------------------------------------------------------------------------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) Output: foo.a, foo.b, bar.a -> Nested Loop Output: foo.a, foo.b, bar.a - Join Filter: true + Join Filter: (foo.a = bar.a) -> Seq Scan on orca_static_pruning.bar Output: bar.a - -> Dynamic Index Scan on foo_idx on orca_static_pruning.foo + -> Materialize Output: foo.a, foo.b - Index Cond: (foo.a = bar.a) - Filter: ((foo.a = bar.a) AND (foo.b = 11)) - Number of partitions to scan: 1 - Optimizer: Pivotal Optimizer (GPORCA) - Settings: enable_hashjoin=off, enable_mergejoin=off, enable_nestloop=on, optimizer=on + -> Seq Scan on orca_static_pruning.foo_part2 foo + Output: foo.a, foo.b + Filter: (foo.b = 11) + Settings: enable_hashjoin = 'off', enable_mergejoin = 'off', enable_nestloop = 'on', enable_parallel = 'off', optimizer = 'on' + Optimizer: Postgres query optimizer (14 rows) SELECT * FROM foo JOIN bar on foo.a = bar.a AND foo.b = 11; diff --git a/src/test/regress/expected/partition_prune_optimizer.out b/src/test/regress/expected/partition_prune_optimizer.out index fc684dda33..7c9c1bcdd0 100644 --- a/src/test/regress/expected/partition_prune_optimizer.out +++ b/src/test/regress/expected/partition_prune_optimizer.out @@ -43,7 +43,7 @@ explain (costs off) select * from lp; (4 rows) explain (costs off) select * from lp where a > 'a' and a < 'd'; - QUERY PLAN + QUERY PLAN ----------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) -> Dynamic Seq Scan on lp @@ -53,7 +53,7 @@ explain (costs off) select * from lp where a > 'a' and a < 'd'; (5 rows) explain (costs off) select * from lp where a > 'a' and a <= 'd'; - QUERY PLAN + QUERY PLAN ------------------------------------------------------------ Gather Motion 3:1 (slice1; segments: 3) -> Dynamic Seq Scan on lp @@ -103,7 +103,7 @@ explain (costs off) select * from lp where a is null; (5 rows) explain (costs off) select * from lp where a = 'a' or a = 'c'; - QUERY PLAN + QUERY PLAN ---------------------------------------------------------- Gather Motion 1:1 (slice1; segments: 1) -> Dynamic Seq Scan on lp @@ -113,7 +113,7 @@ explain (costs off) select * from lp where a = 'a' or a = 'c'; (5 rows) explain (costs off) select * from lp where a is not null and (a = 'a' or a = 'c'); - QUERY PLAN + QUERY PLAN ---------------------------------------------------------------------------------- Gather Motion 1:1 (slice1; segments: 1) -> Dynamic Seq Scan on lp @@ -133,7 +133,7 @@ explain (costs off) select * from lp where a <> 'g'; (5 rows) explain (costs off) select * from lp where a <> 'a' and a <> 'd'; - QUERY PLAN + QUERY PLAN ------------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) -> Dynamic Seq Scan on lp @@ -143,7 +143,7 @@ explain (costs off) select * from lp where a <> 'a' and a <> 'd'; (5 rows) explain (costs off) select * from lp where a not in ('a', 'd'); - QUERY PLAN + QUERY PLAN ------------------------------------------------ Gather Motion 3:1 (slice1; segments: 3) -> Dynamic Seq Scan on lp @@ -1332,86 +1332,74 @@ explain (costs off) select * from coercepart where a !~ all ('{ab,bc}'); (5 rows) explain (costs off) select * from coercepart where a = any ('{ab,bc}'); - QUERY PLAN -------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) - -> Append - -> Seq Scan on coercepart_ab - Filter: ((a)::text = ANY ('{ab,bc}'::text[])) - -> Seq Scan on coercepart_bc - Filter: ((a)::text = ANY ('{ab,bc}'::text[])) + -> Dynamic Seq Scan on coercepart + Number of partitions to scan: 2 + Filter: ((a)::text = ANY ('{ab,bc}'::text[])) Optimizer: Pivotal Optimizer (GPORCA) -(7 rows) +(5 rows) explain (costs off) select * from coercepart where a = any ('{ab,null}'); - QUERY PLAN ---------------------------------------------------------------- + QUERY PLAN +--------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) - -> Append - -> Seq Scan on coercepart_ab - Filter: ((a)::text = ANY ('{ab,NULL}'::text[])) + -> Dynamic Seq Scan on coercepart + Number of partitions to scan: 1 + Filter: ((a)::text = ANY ('{ab,NULL}'::text[])) Optimizer: Pivotal Optimizer (GPORCA) (5 rows) explain (costs off) select * from coercepart where a = any (null::text[]); - QUERY PLAN --------------------------------------------------------- + QUERY PLAN +-------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) - -> Append - -> Seq Scan on coercepart_ab - Filter: ((a)::text = ANY (NULL::text[])) - -> Seq Scan on coercepart_bc - Filter: ((a)::text = ANY (NULL::text[])) - -> Seq Scan on coercepart_cd - Filter: ((a)::text = ANY (NULL::text[])) + -> Dynamic Seq Scan on coercepart + Number of partitions to scan: 3 + Filter: ((a)::text = ANY (NULL::text[])) Optimizer: Pivotal Optimizer (GPORCA) -(9 rows) +(5 rows) explain (costs off) select * from coercepart where a = all ('{ab}'); - QUERY PLAN ----------------------------------------------------------- + QUERY PLAN +---------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) - -> Append - -> Seq Scan on coercepart_ab - Filter: ((a)::text = ALL ('{ab}'::text[])) + -> Dynamic Seq Scan on coercepart + Number of partitions to scan: 1 + Filter: ((a)::text = ALL ('{ab}'::text[])) Optimizer: Pivotal Optimizer (GPORCA) (5 rows) explain (costs off) select * from coercepart where a = all ('{ab,bc}'); - QUERY PLAN -------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) - -> Append - -> Seq Scan on coercepart_ab - Filter: ((a)::text = ALL ('{ab,bc}'::text[])) - -> Seq Scan on coercepart_bc - Filter: ((a)::text = ALL ('{ab,bc}'::text[])) + -> Dynamic Seq Scan on coercepart + Number of partitions to scan: 2 + Filter: ((a)::text = ALL ('{ab,bc}'::text[])) Optimizer: Pivotal Optimizer (GPORCA) -(7 rows) +(5 rows) explain (costs off) select * from coercepart where a = all ('{ab,null}'); - QUERY PLAN ---------------------------------------------------------------- + QUERY PLAN +--------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) - -> Append - -> Seq Scan on coercepart_ab - Filter: ((a)::text = ALL ('{ab,NULL}'::text[])) + -> Dynamic Seq Scan on coercepart + Number of partitions to scan: 1 + Filter: ((a)::text = ALL ('{ab,NULL}'::text[])) Optimizer: Pivotal Optimizer (GPORCA) (5 rows) explain (costs off) select * from coercepart where a = all (null::text[]); - QUERY PLAN --------------------------------------------------------- + QUERY PLAN +-------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) - -> Append - -> Seq Scan on coercepart_ab - Filter: ((a)::text = ALL (NULL::text[])) - -> Seq Scan on coercepart_bc - Filter: ((a)::text = ALL (NULL::text[])) - -> Seq Scan on coercepart_cd - Filter: ((a)::text = ALL (NULL::text[])) + -> Dynamic Seq Scan on coercepart + Number of partitions to scan: 3 + Filter: ((a)::text = ALL (NULL::text[])) Optimizer: Pivotal Optimizer (GPORCA) -(9 rows) +(5 rows) drop table coercepart; CREATE TABLE part (a INT, b INT) PARTITION BY LIST (a); @@ -2158,7 +2146,7 @@ explain (analyze, costs off, summary off, timing off) select * from list_part wh QUERY PLAN ------------------------------------------------------------------------ Gather Motion 3:1 (slice1; segments: 3) (actual rows=0 loops=1) - -> Dynamic Seq Scan on list_part (never executed) + -> Dynamic Seq Scan on list_part (actual rows=0 loops=1) Number of partitions to scan: 4 Filter: (a = (1 + a)) Partitions scanned: Avg 4.0 x 3 workers. Max 4 parts (seg0). @@ -2996,7 +2984,7 @@ select * from tbl1 join tprt on tbl1.col1 = tprt.col1; QUERY PLAN ------------------------------------------------------------------------------------ Gather Motion 3:1 (slice1; segments: 3) (actual rows=0 loops=1) - -> Hash Join (never executed) + -> Hash Join (actual rows=0 loops=1) Hash Cond: (tbl1.col1 = tprt.col1) -> Seq Scan on tbl1 (actual rows=1 loops=1) -> Hash (actual rows=3 loops=1) @@ -3146,12 +3134,12 @@ create table stable_qual_pruning3 partition of stable_qual_pruning -- comparison against a stable value requires run-time pruning explain (analyze, costs off, summary off, timing off) select * from stable_qual_pruning where a < localtimestamp; - QUERY PLAN -------------------------------------------------------------------------------------- + QUERY PLAN +-------------------------------------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) (actual rows=0 loops=1) - -> Dynamic Seq Scan on stable_qual_pruning (never executed) + -> Dynamic Seq Scan on stable_qual_pruning (actual rows=0 loops=1) Number of partitions to scan: 2 - Filter: (a < 'Thu May 19 15:14:26.02528 2022'::timestamp without time zone) + Filter: (a < 'Thu Dec 19 19:04:45.779097 2024'::timestamp without time zone) Partitions scanned: Avg 2.0 x 3 workers. Max 2 parts (seg0). Optimizer: Pivotal Optimizer (GPORCA) (6 rows) @@ -3162,7 +3150,7 @@ select * from stable_qual_pruning where a < '2000-02-01'::timestamptz; QUERY PLAN -------------------------------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) (actual rows=0 loops=1) - -> Dynamic Seq Scan on stable_qual_pruning (never executed) + -> Dynamic Seq Scan on stable_qual_pruning (actual rows=0 loops=1) Number of partitions to scan: 1 Filter: (a < 'Tue Feb 01 00:00:00 2000 PST'::timestamp with time zone) Partitions scanned: Avg 1.0 x 3 workers. Max 1 parts (seg0). @@ -3186,7 +3174,7 @@ select * from stable_qual_pruning QUERY PLAN ---------------------------------------------------------------------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) (actual rows=0 loops=1) - -> Dynamic Seq Scan on stable_qual_pruning (never executed) + -> Dynamic Seq Scan on stable_qual_pruning (actual rows=0 loops=1) Number of partitions to scan: 1 Filter: (a = ANY ('{"Tue Feb 01 00:00:00 2000","Fri Jan 01 00:00:00 2010"}'::timestamp without time zone[])) Partitions scanned: Avg 1.0 x 3 workers. Max 1 parts (seg0). @@ -3199,9 +3187,9 @@ select * from stable_qual_pruning QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) (actual rows=0 loops=1) - -> Dynamic Seq Scan on stable_qual_pruning (never executed) + -> Dynamic Seq Scan on stable_qual_pruning (actual rows=0 loops=1) Number of partitions to scan: 1 - Filter: (a = ANY ('{"Tue Feb 01 00:00:00 2000","Thu May 19 15:14:26.037645 2022"}'::timestamp without time zone[])) + Filter: (a = ANY ('{"Tue Feb 01 00:00:00 2000","Thu Dec 19 19:04:45.852371 2024"}'::timestamp without time zone[])) Partitions scanned: Avg 1.0 x 3 workers. Max 1 parts (seg0). Optimizer: Pivotal Optimizer (GPORCA) (6 rows) @@ -3222,7 +3210,7 @@ select * from stable_qual_pruning QUERY PLAN --------------------------------------------------------------------------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) (actual rows=0 loops=1) - -> Dynamic Seq Scan on stable_qual_pruning (never executed) + -> Dynamic Seq Scan on stable_qual_pruning (actual rows=0 loops=1) Number of partitions to scan: 1 Filter: (a = ANY ('{"Tue Feb 01 00:00:00 2000 PST","Fri Jan 01 00:00:00 2010 PST"}'::timestamp with time zone[])) Partitions scanned: Avg 1.0 x 3 workers. Max 1 parts (seg0). @@ -3232,18 +3220,15 @@ select * from stable_qual_pruning explain (analyze, costs off, summary off, timing off) select * from stable_qual_pruning where a = any(null::timestamptz[]); - QUERY PLAN --------------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------------ Gather Motion 3:1 (slice1; segments: 3) (actual rows=0 loops=1) - -> Append (actual rows=0 loops=1) - -> Seq Scan on stable_qual_pruning1 (actual rows=0 loops=1) - Filter: (a = ANY (NULL::timestamp with time zone[])) - -> Seq Scan on stable_qual_pruning2 (actual rows=0 loops=1) - Filter: (a = ANY (NULL::timestamp with time zone[])) - -> Seq Scan on stable_qual_pruning3 (actual rows=0 loops=1) - Filter: (a = ANY (NULL::timestamp with time zone[])) + -> Dynamic Seq Scan on stable_qual_pruning (actual rows=0 loops=1) + Number of partitions to scan: 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) -(9 rows) +(6 rows) drop table stable_qual_pruning; -- @@ -3328,7 +3313,7 @@ select * from boolp where a = (select value from boolvalues where value); ------------------------------------------------------------------------------------------------ Gather Motion 3:1 (slice1; segments: 3) (actual rows=0 loops=1) -> Hash Join (actual rows=0 loops=1) - Hash Cond: (boolvalues.value = boolp_f.a) + Hash Cond: (boolvalues.value = boolp.a) -> Redistribute Motion 1:3 (slice2) (never executed) -> Assert (actual rows=1 loops=1) Assert Cond: ((row_number() OVER (?)) = 1) @@ -3340,9 +3325,9 @@ select * from boolp where a = (select value from boolvalues where value); -> Hash (actual rows=0 loops=1) Buckets: 262144 Batches: 1 Memory Usage: 2048kB -> Broadcast Motion 3:3 (slice4; segments: 3) (actual rows=0 loops=1) - -> Append (actual rows=0 loops=1) - -> Seq Scan on boolp_f (actual rows=0 loops=1) - -> Seq Scan on boolp_t (actual rows=0 loops=1) + -> Dynamic Seq Scan on boolp (actual rows=0 loops=1) + Number of partitions to scan: 2 + Partitions scanned: Avg 2.0 x 3 workers. Max 2 parts (seg0). Optimizer: Pivotal Optimizer (GPORCA) (18 rows) @@ -3352,7 +3337,7 @@ select * from boolp where a = (select value from boolvalues where not value); ------------------------------------------------------------------------------------------------ Gather Motion 3:1 (slice1; segments: 3) (actual rows=0 loops=1) -> Hash Join (actual rows=0 loops=1) - Hash Cond: (boolvalues.value = boolp_f.a) + Hash Cond: (boolvalues.value = boolp.a) -> Redistribute Motion 1:3 (slice2) (never executed) -> Assert (actual rows=1 loops=1) Assert Cond: ((row_number() OVER (?)) = 1) @@ -3364,9 +3349,9 @@ select * from boolp where a = (select value from boolvalues where not value); -> Hash (actual rows=0 loops=1) Buckets: 262144 Batches: 1 Memory Usage: 2048kB -> Broadcast Motion 3:3 (slice4; segments: 3) (actual rows=0 loops=1) - -> Append (actual rows=0 loops=1) - -> Seq Scan on boolp_f (actual rows=0 loops=1) - -> Seq Scan on boolp_t (actual rows=0 loops=1) + -> Dynamic Seq Scan on boolp (actual rows=0 loops=1) + Number of partitions to scan: 2 + Partitions scanned: Avg 2.0 x 3 workers. Max 2 parts (seg0). Optimizer: Pivotal Optimizer (GPORCA) (18 rows) @@ -3474,8 +3459,7 @@ explain (analyze, costs off, summary off, timing off) select * from ma_test wher Merge Key: ma_test.b -> Sort (actual rows=8 loops=1) Sort Key: ma_test.b - Sort Method: quicksort Memory: ###kB - Executor Memory: ###kB Segments: ### Max: ###kB (segment ###) + Sort Method: quicksort Memory: 75kB -> Nested Loop (actual rows=8 loops=1) Join Filter: (ma_test.a >= (min(ma_test_p2.b))) Rows Removed by Join Filter: 3 @@ -3628,7 +3612,7 @@ create table pp_recpart_23 partition of pp_recpart for values in ('(2,3)'); explain (costs off) select * from pp_recpart where a = '(1,1)'::pp_rectype; QUERY PLAN ------------------------------------------- - Gather Motion 3:1 (slice1; segments: 3) + Gather Motion 1:1 (slice1; segments: 1) -> Dynamic Seq Scan on pp_recpart Number of partitions to scan: 1 Filter: (a = '(1,1)'::pp_rectype) diff --git a/src/test/regress/expected/subselect_optimizer.out b/src/test/regress/expected/subselect_optimizer.out index b50164c455..643cca693f 100644 --- a/src/test/regress/expected/subselect_optimizer.out +++ b/src/test/regress/expected/subselect_optimizer.out @@ -1016,28 +1016,26 @@ analyze exists_tbl; explain (costs off) select * from exists_tbl t1 where (exists(select 1 from exists_tbl t2 where t1.c1 = t2.c2) or c3 < 0); - QUERY PLAN ------------------------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) -> Result - Filter: ((COALESCE((count()), '0'::bigint) > '0'::bigint) OR (exists_tbl_null.c3 < 0)) + Filter: ((COALESCE((count()), '0'::bigint) > '0'::bigint) OR (exists_tbl.c3 < 0)) -> Hash Left Join - Hash Cond: (exists_tbl_null.c1 = exists_tbl_null_1.c2) - -> Append - -> Seq Scan on exists_tbl_null - -> Seq Scan on exists_tbl_def + Hash Cond: (exists_tbl.c1 = exists_tbl_1.c2) + -> Dynamic Seq Scan on exists_tbl + Number of partitions to scan: 2 -> Hash -> GroupAggregate - Group Key: exists_tbl_null_1.c2 + Group Key: exists_tbl_1.c2 -> Sort - Sort Key: exists_tbl_null_1.c2 + Sort Key: exists_tbl_1.c2 -> Redistribute Motion 3:3 (slice2; segments: 3) - Hash Key: exists_tbl_null_1.c2 - -> Append - -> Seq Scan on exists_tbl_null exists_tbl_null_1 - -> Seq Scan on exists_tbl_def exists_tbl_def_1 - Optimizer: Postgres query optimizer -(19 rows) + Hash Key: exists_tbl_1.c2 + -> Dynamic Seq Scan on exists_tbl exists_tbl_1 + Number of partitions to scan: 2 + Optimizer: Pivotal Optimizer (GPORCA) +(17 rows) select * from exists_tbl t1 where (exists(select 1 from exists_tbl t2 where t1.c1 = t2.c2) or c3 < 0); diff --git a/src/test/regress/sql/bfv_partition_plans.sql b/src/test/regress/sql/bfv_partition_plans.sql index c730d50d8c..a60fd0adc8 100644 --- a/src/test/regress/sql/bfv_partition_plans.sql +++ b/src/test/regress/sql/bfv_partition_plans.sql @@ -105,6 +105,7 @@ insert into mpp23195_t1 values (generate_series(1,19)); insert into mpp23195_t2 values (1); -- TEST +-- Operator Fallback: InnerIndexNestLoopJoin may have wrong plan not supported select find_operator('select * from mpp23195_t1,mpp23195_t2 where mpp23195_t1.i < mpp23195_t2.i;', 'Dynamic Index Scan'); select * from mpp23195_t1,mpp23195_t2 where mpp23195_t1.i < mpp23195_t2.i; --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
