drop schema if exists dpe_single cascade; NOTICE: schema "dpe_single" does not exist, skipping create schema dpe_single; set search_path='dpe_single'; set gp_segments_for_planner=2; set optimizer_segments=2; drop table if exists pt; NOTICE: table "pt" does not exist, skipping drop table if exists pt1; NOTICE: table "pt1" does not exist, skipping drop table if exists t; NOTICE: table "t" does not exist, skipping drop table if exists t1; NOTICE: table "t1" does not exist, skipping create table pt(dist int, pt1 text, pt2 text, pt3 text, ptid int) DISTRIBUTED BY (dist) PARTITION BY RANGE(ptid) ( START (0) END (5) EVERY (1), DEFAULT PARTITION junk_data ) ; NOTICE: CREATE TABLE will create partition "pt_1_prt_junk_data" for table "pt" NOTICE: CREATE TABLE will create partition "pt_1_prt_2" for table "pt" NOTICE: CREATE TABLE will create partition "pt_1_prt_3" for table "pt" NOTICE: CREATE TABLE will create partition "pt_1_prt_4" for table "pt" NOTICE: CREATE TABLE will create partition "pt_1_prt_5" for table "pt" NOTICE: CREATE TABLE will create partition "pt_1_prt_6" for table "pt" create table pt1(dist int, pt1 text, pt2 text, pt3 text, ptid int) DISTRIBUTED RANDOMLY PARTITION BY RANGE(ptid) ( START (0) END (5) EVERY (1), DEFAULT PARTITION junk_data ) ; NOTICE: CREATE TABLE will create partition "pt1_1_prt_junk_data" for table "pt1" NOTICE: CREATE TABLE will create partition "pt1_1_prt_2" for table "pt1" NOTICE: CREATE TABLE will create partition "pt1_1_prt_3" for table "pt1" NOTICE: CREATE TABLE will create partition "pt1_1_prt_4" for table "pt1" NOTICE: CREATE TABLE will create partition "pt1_1_prt_5" for table "pt1" NOTICE: CREATE TABLE will create partition "pt1_1_prt_6" for table "pt1" create table t(dist int, tid int, t1 text, t2 text); NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'dist' as the Greenplum Database data distribution key for this table. HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. create index pt1_idx on pt using btree (pt1); NOTICE: building index for child partition "pt_1_prt_junk_data" NOTICE: building index for child partition "pt_1_prt_2" NOTICE: building index for child partition "pt_1_prt_3" NOTICE: building index for child partition "pt_1_prt_4" NOTICE: building index for child partition "pt_1_prt_5" NOTICE: building index for child partition "pt_1_prt_6" create index ptid_idx on pt using btree (ptid); NOTICE: building index for child partition "pt_1_prt_junk_data" NOTICE: building index for child partition "pt_1_prt_2" NOTICE: building index for child partition "pt_1_prt_3" NOTICE: building index for child partition "pt_1_prt_4" NOTICE: building index for child partition "pt_1_prt_5" NOTICE: building index for child partition "pt_1_prt_6" insert into pt select i, 'hello' || i, 'world', 'drop this', i % 6 from generate_series(0,53) i; insert into t select i, i % 6, 'hello' || i, 'bar' from generate_series(0,1) i; create table t1 as select * from t; NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column(s) named 'dist' as the Greenplum Database data distribution key for this table. HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. insert into pt1 select * from pt; analyze pt; analyze pt1; analyze t; analyze t1; -- -- Simple positive cases -- explain select * from t, pt where tid = ptid; QUERY PLAN -------------------------------------------------------------------------------------------------------- Gather Motion 2:1 (slice2; segments: 2) (cost=0.00..7.17 rows=9 width=50) -> Hash Join (cost=0.00..5.73 rows=9 width=50) Hash Cond: pt.ptid = t.tid -> Dynamic Table Scan on pt (partIndex: 0) (cost=0.00..0.82 rows=27 width=31) -> Hash (cost=100.00..100.00 rows=50 width=4) -> Result (cost=10.00..100.00 rows=50 width=4) -> Broadcast Motion 2:2 (slice1; segments: 2) (cost=0.00..1.06 rows=2 width=19) -> Table Scan on t (cost=0.00..0.02 rows=1 width=19) Settings: gp_segments_for_planner=2; optimizer=on (9 rows) select * from t, pt where tid = ptid; dist | tid | t1 | t2 | dist | pt1 | pt2 | pt3 | ptid ------+-----+--------+-----+------+---------+-------+-----------+------ 1 | 1 | hello1 | bar | 1 | hello1 | world | drop this | 1 1 | 1 | hello1 | bar | 7 | hello7 | world | drop this | 1 1 | 1 | hello1 | bar | 13 | hello13 | world | drop this | 1 1 | 1 | hello1 | bar | 19 | hello19 | world | drop this | 1 1 | 1 | hello1 | bar | 25 | hello25 | world | drop this | 1 1 | 1 | hello1 | bar | 31 | hello31 | world | drop this | 1 1 | 1 | hello1 | bar | 37 | hello37 | world | drop this | 1 1 | 1 | hello1 | bar | 43 | hello43 | world | drop this | 1 1 | 1 | hello1 | bar | 49 | hello49 | world | drop this | 1 0 | 0 | hello0 | bar | 0 | hello0 | world | drop this | 0 0 | 0 | hello0 | bar | 6 | hello6 | world | drop this | 0 0 | 0 | hello0 | bar | 12 | hello12 | world | drop this | 0 0 | 0 | hello0 | bar | 18 | hello18 | world | drop this | 0 0 | 0 | hello0 | bar | 24 | hello24 | world | drop this | 0 0 | 0 | hello0 | bar | 30 | hello30 | world | drop this | 0 0 | 0 | hello0 | bar | 36 | hello36 | world | drop this | 0 0 | 0 | hello0 | bar | 42 | hello42 | world | drop this | 0 0 | 0 | hello0 | bar | 48 | hello48 | world | drop this | 0 (18 rows) explain select * from t, pt where tid + 1 = ptid; QUERY PLAN -------------------------------------------------------------------------------------------------------- Gather Motion 2:1 (slice2; segments: 2) (cost=0.00..9.37 rows=54 width=50) -> Hash Join (cost=0.00..5.73 rows=54 width=50) Hash Cond: pt.ptid = (t.tid + 1) -> Dynamic Table Scan on pt (partIndex: 0) (cost=0.00..0.82 rows=27 width=31) -> Hash (cost=100.00..100.00 rows=50 width=4) -> Result (cost=10.00..100.00 rows=50 width=4) -> Broadcast Motion 2:2 (slice1; segments: 2) (cost=0.00..1.06 rows=2 width=19) -> Table Scan on t (cost=0.00..0.02 rows=1 width=19) Settings: gp_segments_for_planner=2; optimizer=on (9 rows) select * from t, pt where tid + 1 = ptid; dist | tid | t1 | t2 | dist | pt1 | pt2 | pt3 | ptid ------+-----+--------+-----+------+---------+-------+-----------+------ 0 | 0 | hello0 | bar | 1 | hello1 | world | drop this | 1 0 | 0 | hello0 | bar | 7 | hello7 | world | drop this | 1 0 | 0 | hello0 | bar | 13 | hello13 | world | drop this | 1 0 | 0 | hello0 | bar | 19 | hello19 | world | drop this | 1 0 | 0 | hello0 | bar | 25 | hello25 | world | drop this | 1 0 | 0 | hello0 | bar | 31 | hello31 | world | drop this | 1 0 | 0 | hello0 | bar | 37 | hello37 | world | drop this | 1 0 | 0 | hello0 | bar | 43 | hello43 | world | drop this | 1 0 | 0 | hello0 | bar | 49 | hello49 | world | drop this | 1 1 | 1 | hello1 | bar | 2 | hello2 | world | drop this | 2 1 | 1 | hello1 | bar | 8 | hello8 | world | drop this | 2 1 | 1 | hello1 | bar | 14 | hello14 | world | drop this | 2 1 | 1 | hello1 | bar | 20 | hello20 | world | drop this | 2 1 | 1 | hello1 | bar | 26 | hello26 | world | drop this | 2 1 | 1 | hello1 | bar | 32 | hello32 | world | drop this | 2 1 | 1 | hello1 | bar | 38 | hello38 | world | drop this | 2 1 | 1 | hello1 | bar | 44 | hello44 | world | drop this | 2 1 | 1 | hello1 | bar | 50 | hello50 | world | drop this | 2 (18 rows) explain select * from t, pt where tid = ptid and t1 = 'hello' || tid; QUERY PLAN -------------------------------------------------------------------------------------------------------- Gather Motion 2:1 (slice2; segments: 2) (cost=0.00..7.77 rows=4 width=50) -> Hash Join (cost=0.00..6.59 rows=4 width=50) Hash Cond: pt.ptid = t.tid -> Dynamic Table Scan on pt (partIndex: 0) (cost=0.00..0.82 rows=27 width=31) -> Hash (cost=100.00..100.00 rows=50 width=4) -> Result (cost=10.00..100.00 rows=50 width=4) -> Broadcast Motion 2:2 (slice1; segments: 2) (cost=0.00..2.05 rows=1 width=19) -> Table Scan on t (cost=0.00..1.03 rows=1 width=19) Filter: t1 = ('hello'::text || text(tid)) Settings: gp_segments_for_planner=2; optimizer=on (10 rows) select * from t, pt where tid = ptid and t1 = 'hello' || tid; dist | tid | t1 | t2 | dist | pt1 | pt2 | pt3 | ptid ------+-----+--------+-----+------+---------+-------+-----------+------ 1 | 1 | hello1 | bar | 1 | hello1 | world | drop this | 1 1 | 1 | hello1 | bar | 7 | hello7 | world | drop this | 1 1 | 1 | hello1 | bar | 13 | hello13 | world | drop this | 1 1 | 1 | hello1 | bar | 19 | hello19 | world | drop this | 1 1 | 1 | hello1 | bar | 25 | hello25 | world | drop this | 1 1 | 1 | hello1 | bar | 31 | hello31 | world | drop this | 1 1 | 1 | hello1 | bar | 37 | hello37 | world | drop this | 1 1 | 1 | hello1 | bar | 43 | hello43 | world | drop this | 1 1 | 1 | hello1 | bar | 49 | hello49 | world | drop this | 1 0 | 0 | hello0 | bar | 0 | hello0 | world | drop this | 0 0 | 0 | hello0 | bar | 6 | hello6 | world | drop this | 0 0 | 0 | hello0 | bar | 12 | hello12 | world | drop this | 0 0 | 0 | hello0 | bar | 18 | hello18 | world | drop this | 0 0 | 0 | hello0 | bar | 24 | hello24 | world | drop this | 0 0 | 0 | hello0 | bar | 30 | hello30 | world | drop this | 0 0 | 0 | hello0 | bar | 36 | hello36 | world | drop this | 0 0 | 0 | hello0 | bar | 42 | hello42 | world | drop this | 0 0 | 0 | hello0 | bar | 48 | hello48 | world | drop this | 0 (18 rows) explain select * from t, pt where t1 = pt1 and ptid = tid; QUERY PLAN -------------------------------------------------------------------------------------------------------- Gather Motion 2:1 (slice2; segments: 2) (cost=0.00..6.78 rows=1 width=50) -> Hash Join (cost=0.00..5.73 rows=1 width=50) Hash Cond: pt.pt1 = t.t1 AND pt.ptid = t.tid -> Dynamic Table Scan on pt (partIndex: 0) (cost=0.00..0.82 rows=27 width=31) -> Hash (cost=100.00..100.00 rows=50 width=4) -> Result (cost=10.00..100.00 rows=50 width=4) -> Broadcast Motion 2:2 (slice1; segments: 2) (cost=0.00..1.06 rows=2 width=19) -> Table Scan on t (cost=0.00..0.02 rows=1 width=19) Settings: gp_segments_for_planner=2; optimizer=on (9 rows) select * from t, pt where t1 = pt1 and ptid = tid; dist | tid | t1 | t2 | dist | pt1 | pt2 | pt3 | ptid ------+-----+--------+-----+------+--------+-------+-----------+------ 0 | 0 | hello0 | bar | 0 | hello0 | world | drop this | 0 1 | 1 | hello1 | bar | 1 | hello1 | world | drop this | 1 (2 rows) -- -- in and exists clauses -- explain select * from pt where ptid in (select tid from t where t1 = 'hello' || tid); QUERY PLAN ------------------------------------------------------------------------------------------------------- Gather Motion 2:1 (slice2; segments: 2) (cost=0.00..7.60 rows=4 width=31) -> Hash EXISTS Join (cost=0.00..6.49 rows=4 width=31) Hash Cond: pt.ptid = t.tid -> Dynamic Table Scan on pt (partIndex: 0) (cost=0.00..0.82 rows=27 width=31) -> Hash (cost=100.00..100.00 rows=50 width=4) -> Result (cost=10.00..100.00 rows=50 width=4) -> Broadcast Motion 2:2 (slice1; segments: 2) (cost=0.00..2.02 rows=1 width=4) -> Table Scan on t (cost=0.00..1.01 rows=1 width=4) Filter: t1 = ('hello'::text || text(tid)) Settings: gp_segments_for_planner=2; optimizer=on (10 rows) select * from pt where ptid in (select tid from t where t1 = 'hello' || tid); dist | pt1 | pt2 | pt3 | ptid ------+---------+-------+-----------+------ 1 | hello1 | world | drop this | 1 7 | hello7 | world | drop this | 1 13 | hello13 | world | drop this | 1 19 | hello19 | world | drop this | 1 25 | hello25 | world | drop this | 1 31 | hello31 | world | drop this | 1 37 | hello37 | world | drop this | 1 43 | hello43 | world | drop this | 1 49 | hello49 | world | drop this | 1 0 | hello0 | world | drop this | 0 6 | hello6 | world | drop this | 0 12 | hello12 | world | drop this | 0 18 | hello18 | world | drop this | 0 24 | hello24 | world | drop this | 0 30 | hello30 | world | drop this | 0 36 | hello36 | world | drop this | 0 42 | hello42 | world | drop this | 0 48 | hello48 | world | drop this | 0 (18 rows) explain select * from pt where exists (select 1 from t where tid = ptid and t1 = 'hello' || tid); QUERY PLAN ------------------------------------------------------------------------------------------------------------------- Gather Motion 2:1 (slice3; segments: 2) (cost=0.00..14.68 rows=4 width=31) -> Hash Join (cost=0.00..13.57 rows=4 width=31) Hash Cond: t.tid = pt.ptid -> HashAggregate (cost=0.00..4.03 rows=1 width=4) Group By: t.tid -> Redistribute Motion 2:2 (slice1; segments: 2) (cost=0.00..3.02 rows=1 width=4) Hash Key: t.tid -> Result (cost=0.00..2.02 rows=1 width=4) -> Result (cost=0.00..2.02 rows=1 width=4) -> Table Scan on t (cost=0.00..1.01 rows=1 width=4) Filter: t1 = ('hello'::text || text(tid)) -> Hash (cost=5.27..5.27 rows=27 width=31) -> Redistribute Motion 2:2 (slice2; segments: 2) (cost=0.00..5.27 rows=27 width=31) Hash Key: pt.ptid -> Sequence (cost=0.00..3.45 rows=27 width=31) -> Result (cost=10.00..100.00 rows=50 width=4) -> Function Scan on gp_partition_expansion (cost=10.00..100.00 rows=50 width=4) -> Dynamic Table Scan on pt (partIndex: 0) (cost=0.00..0.82 rows=27 width=31) Settings: gp_segments_for_planner=2; optimizer=on (19 rows) select * from pt where exists (select 1 from t where tid = ptid and t1 = 'hello' || tid); dist | pt1 | pt2 | pt3 | ptid ------+---------+-------+-----------+------ 49 | hello49 | world | drop this | 1 43 | hello43 | world | drop this | 1 37 | hello37 | world | drop this | 1 31 | hello31 | world | drop this | 1 25 | hello25 | world | drop this | 1 19 | hello19 | world | drop this | 1 13 | hello13 | world | drop this | 1 7 | hello7 | world | drop this | 1 1 | hello1 | world | drop this | 1 48 | hello48 | world | drop this | 0 42 | hello42 | world | drop this | 0 36 | hello36 | world | drop this | 0 30 | hello30 | world | drop this | 0 24 | hello24 | world | drop this | 0 18 | hello18 | world | drop this | 0 12 | hello12 | world | drop this | 0 6 | hello6 | world | drop this | 0 0 | hello0 | world | drop this | 0 (18 rows) -- -- group-by on top -- explain select count(*) from t, pt where tid = ptid; QUERY PLAN ------------------------------------------------------------------------------------------------------------- Aggregate (cost=0.00..5.43 rows=1 width=8) -> Gather Motion 2:1 (slice2; segments: 2) (cost=0.00..4.38 rows=9 width=1) -> Hash Join (cost=0.00..3.38 rows=9 width=1) Hash Cond: pt.ptid = t.tid -> Dynamic Table Scan on pt (partIndex: 0) (cost=0.00..0.11 rows=27 width=4) -> Hash (cost=100.00..100.00 rows=50 width=4) -> Result (cost=10.00..100.00 rows=50 width=4) -> Broadcast Motion 2:2 (slice1; segments: 2) (cost=0.00..1.01 rows=2 width=4) -> Table Scan on t (cost=0.00..0.00 rows=1 width=4) Settings: gp_segments_for_planner=2; optimizer=on (10 rows) select count(*) from t, pt where tid = ptid; count ------- 18 (1 row) -- -- window function on top -- explain select *, rank() over (order by ptid,pt1) from t, pt where tid = ptid; QUERY PLAN -------------------------------------------------------------------------------------------------------------------- Window (cost=0.00..151.99 rows=9 width=80) Order By: pt.ptid, pt.pt1 -> Gather Motion 2:1 (slice2; segments: 2) (cost=0.00..147.47 rows=9 width=50) Merge Key: pt.ptid, pt.pt1 -> Sort (cost=0.00..146.03 rows=9 width=50) Sort Key: pt.ptid, pt.pt1 -> Hash Join (cost=0.00..5.73 rows=9 width=50) Hash Cond: pt.ptid = t.tid -> Dynamic Table Scan on pt (partIndex: 0) (cost=0.00..0.82 rows=27 width=31) -> Hash (cost=100.00..100.00 rows=50 width=4) -> Result (cost=10.00..100.00 rows=50 width=4) -> Broadcast Motion 2:2 (slice1; segments: 2) (cost=0.00..1.06 rows=2 width=19) -> Table Scan on t (cost=0.00..0.02 rows=1 width=19) Settings: gp_segments_for_planner=2; optimizer=on (14 rows) select *, rank() over (order by ptid,pt1) from t, pt where tid = ptid; dist | tid | t1 | t2 | dist | pt1 | pt2 | pt3 | ptid | rank ------+-----+--------+-----+------+---------+-------+-----------+------+------ 0 | 0 | hello0 | bar | 0 | hello0 | world | drop this | 0 | 1 0 | 0 | hello0 | bar | 12 | hello12 | world | drop this | 0 | 2 0 | 0 | hello0 | bar | 18 | hello18 | world | drop this | 0 | 3 0 | 0 | hello0 | bar | 24 | hello24 | world | drop this | 0 | 4 0 | 0 | hello0 | bar | 30 | hello30 | world | drop this | 0 | 5 0 | 0 | hello0 | bar | 36 | hello36 | world | drop this | 0 | 6 0 | 0 | hello0 | bar | 42 | hello42 | world | drop this | 0 | 7 0 | 0 | hello0 | bar | 48 | hello48 | world | drop this | 0 | 8 0 | 0 | hello0 | bar | 6 | hello6 | world | drop this | 0 | 9 1 | 1 | hello1 | bar | 1 | hello1 | world | drop this | 1 | 10 1 | 1 | hello1 | bar | 13 | hello13 | world | drop this | 1 | 11 1 | 1 | hello1 | bar | 19 | hello19 | world | drop this | 1 | 12 1 | 1 | hello1 | bar | 25 | hello25 | world | drop this | 1 | 13 1 | 1 | hello1 | bar | 31 | hello31 | world | drop this | 1 | 14 1 | 1 | hello1 | bar | 37 | hello37 | world | drop this | 1 | 15 1 | 1 | hello1 | bar | 43 | hello43 | world | drop this | 1 | 16 1 | 1 | hello1 | bar | 49 | hello49 | world | drop this | 1 | 17 1 | 1 | hello1 | bar | 7 | hello7 | world | drop this | 1 | 18 (18 rows) -- -- set ops -- explain select * from t, pt where tid = ptid union all select * from t, pt where tid + 2 = ptid; QUERY PLAN -------------------------------------------------------------------------------------------------------------- Gather Motion 2:1 (slice3; segments: 2) (cost=0.00..22.69 rows=63 width=50) -> Append (cost=0.00..18.61 rows=63 width=50) -> Hash Join (cost=0.00..5.73 rows=9 width=50) Hash Cond: dpe_single.pt.ptid = dpe_single.t.tid -> Dynamic Table Scan on pt (partIndex: 0) (cost=0.00..0.82 rows=27 width=31) -> Hash (cost=100.00..100.00 rows=50 width=4) -> Result (cost=10.00..100.00 rows=50 width=4) -> Broadcast Motion 2:2 (slice1; segments: 2) (cost=0.00..1.06 rows=2 width=19) -> Table Scan on t (cost=0.00..0.02 rows=1 width=19) -> Hash Join (cost=0.00..5.73 rows=54 width=50) Hash Cond: dpe_single.pt.ptid = (dpe_single.t.tid + 2) -> Dynamic Table Scan on pt (partIndex: 1) (cost=0.00..0.82 rows=27 width=31) -> Hash (cost=100.00..100.00 rows=50 width=4) -> Result (cost=10.00..100.00 rows=50 width=4) -> Broadcast Motion 2:2 (slice2; segments: 2) (cost=0.00..1.06 rows=2 width=19) -> Table Scan on t (cost=0.00..0.02 rows=1 width=19) Settings: gp_segments_for_planner=2; optimizer=on (17 rows) select * from t, pt where tid = ptid union all select * from t, pt where tid + 2 = ptid; dist | tid | t1 | t2 | dist | pt1 | pt2 | pt3 | ptid ------+-----+--------+-----+------+---------+-------+-----------+------ 1 | 1 | hello1 | bar | 1 | hello1 | world | drop this | 1 1 | 1 | hello1 | bar | 7 | hello7 | world | drop this | 1 1 | 1 | hello1 | bar | 13 | hello13 | world | drop this | 1 1 | 1 | hello1 | bar | 19 | hello19 | world | drop this | 1 1 | 1 | hello1 | bar | 25 | hello25 | world | drop this | 1 1 | 1 | hello1 | bar | 31 | hello31 | world | drop this | 1 1 | 1 | hello1 | bar | 37 | hello37 | world | drop this | 1 1 | 1 | hello1 | bar | 43 | hello43 | world | drop this | 1 1 | 1 | hello1 | bar | 49 | hello49 | world | drop this | 1 1 | 1 | hello1 | bar | 3 | hello3 | world | drop this | 3 1 | 1 | hello1 | bar | 9 | hello9 | world | drop this | 3 1 | 1 | hello1 | bar | 15 | hello15 | world | drop this | 3 1 | 1 | hello1 | bar | 21 | hello21 | world | drop this | 3 1 | 1 | hello1 | bar | 27 | hello27 | world | drop this | 3 1 | 1 | hello1 | bar | 33 | hello33 | world | drop this | 3 1 | 1 | hello1 | bar | 39 | hello39 | world | drop this | 3 1 | 1 | hello1 | bar | 45 | hello45 | world | drop this | 3 1 | 1 | hello1 | bar | 51 | hello51 | world | drop this | 3 0 | 0 | hello0 | bar | 0 | hello0 | world | drop this | 0 0 | 0 | hello0 | bar | 6 | hello6 | world | drop this | 0 0 | 0 | hello0 | bar | 12 | hello12 | world | drop this | 0 0 | 0 | hello0 | bar | 18 | hello18 | world | drop this | 0 0 | 0 | hello0 | bar | 24 | hello24 | world | drop this | 0 0 | 0 | hello0 | bar | 30 | hello30 | world | drop this | 0 0 | 0 | hello0 | bar | 36 | hello36 | world | drop this | 0 0 | 0 | hello0 | bar | 42 | hello42 | world | drop this | 0 0 | 0 | hello0 | bar | 48 | hello48 | world | drop this | 0 0 | 0 | hello0 | bar | 2 | hello2 | world | drop this | 2 0 | 0 | hello0 | bar | 8 | hello8 | world | drop this | 2 0 | 0 | hello0 | bar | 14 | hello14 | world | drop this | 2 0 | 0 | hello0 | bar | 20 | hello20 | world | drop this | 2 0 | 0 | hello0 | bar | 26 | hello26 | world | drop this | 2 0 | 0 | hello0 | bar | 32 | hello32 | world | drop this | 2 0 | 0 | hello0 | bar | 38 | hello38 | world | drop this | 2 0 | 0 | hello0 | bar | 44 | hello44 | world | drop this | 2 0 | 0 | hello0 | bar | 50 | hello50 | world | drop this | 2 (36 rows) -- -- set-ops -- explain select count(*) from ( select * from t, pt where tid = ptid union all select * from t, pt where tid + 2 = ptid ) foo; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------- Aggregate (cost=0.00..14.91 rows=1 width=8) -> Gather Motion 2:1 (slice3; segments: 2) (cost=0.00..13.65 rows=63 width=1) -> Result (cost=0.00..12.58 rows=63 width=1) -> Append (cost=0.00..12.58 rows=63 width=1) -> Hash Join (cost=0.00..5.73 rows=9 width=50) Hash Cond: dpe_single.pt.ptid = dpe_single.t.tid -> Dynamic Table Scan on pt (partIndex: 0) (cost=0.00..0.82 rows=27 width=31) -> Hash (cost=100.00..100.00 rows=50 width=4) -> Result (cost=10.00..100.00 rows=50 width=4) -> Broadcast Motion 2:2 (slice1; segments: 2) (cost=0.00..1.06 rows=2 width=19) -> Table Scan on t (cost=0.00..0.02 rows=1 width=19) -> Hash Join (cost=0.00..5.73 rows=54 width=50) Hash Cond: dpe_single.pt.ptid = (dpe_single.t.tid + 2) -> Dynamic Table Scan on pt (partIndex: 1) (cost=0.00..0.82 rows=27 width=31) -> Hash (cost=100.00..100.00 rows=50 width=4) -> Result (cost=10.00..100.00 rows=50 width=4) -> Broadcast Motion 2:2 (slice2; segments: 2) (cost=0.00..1.06 rows=2 width=19) -> Table Scan on t (cost=0.00..0.02 rows=1 width=19) Settings: gp_segments_for_planner=2; optimizer=on (19 rows) select count(*) from ( select * from t, pt where tid = ptid union all select * from t, pt where tid + 2 = ptid ) foo; count ------- 36 (1 row) -- -- other join types (NL) -- set enable_hashjoin=off; set enable_nestloop=on; set enable_mergejoin=off; explain select * from t, pt where tid = ptid; QUERY PLAN ------------------------------------------------------------------------------------------------------------------- Gather Motion 2:1 (slice2; segments: 2) (cost=0.00..7.17 rows=9 width=50) -> Hash Join (cost=0.00..5.73 rows=9 width=50) Hash Cond: pt.ptid = t.tid -> Dynamic Table Scan on pt (partIndex: 0) (cost=0.00..0.82 rows=27 width=31) -> Hash (cost=100.00..100.00 rows=50 width=4) -> Result (cost=10.00..100.00 rows=50 width=4) -> Broadcast Motion 2:2 (slice1; segments: 2) (cost=0.00..1.06 rows=2 width=19) -> Table Scan on t (cost=0.00..0.02 rows=1 width=19) Settings: enable_hashjoin=off; enable_mergejoin=off; enable_nestloop=on; gp_segments_for_planner=2; optimizer=on (9 rows) select * from t, pt where tid = ptid; dist | tid | t1 | t2 | dist | pt1 | pt2 | pt3 | ptid ------+-----+--------+-----+------+---------+-------+-----------+------ 1 | 1 | hello1 | bar | 1 | hello1 | world | drop this | 1 1 | 1 | hello1 | bar | 7 | hello7 | world | drop this | 1 1 | 1 | hello1 | bar | 13 | hello13 | world | drop this | 1 1 | 1 | hello1 | bar | 19 | hello19 | world | drop this | 1 1 | 1 | hello1 | bar | 25 | hello25 | world | drop this | 1 1 | 1 | hello1 | bar | 31 | hello31 | world | drop this | 1 1 | 1 | hello1 | bar | 37 | hello37 | world | drop this | 1 1 | 1 | hello1 | bar | 43 | hello43 | world | drop this | 1 1 | 1 | hello1 | bar | 49 | hello49 | world | drop this | 1 0 | 0 | hello0 | bar | 0 | hello0 | world | drop this | 0 0 | 0 | hello0 | bar | 6 | hello6 | world | drop this | 0 0 | 0 | hello0 | bar | 12 | hello12 | world | drop this | 0 0 | 0 | hello0 | bar | 18 | hello18 | world | drop this | 0 0 | 0 | hello0 | bar | 24 | hello24 | world | drop this | 0 0 | 0 | hello0 | bar | 30 | hello30 | world | drop this | 0 0 | 0 | hello0 | bar | 36 | hello36 | world | drop this | 0 0 | 0 | hello0 | bar | 42 | hello42 | world | drop this | 0 0 | 0 | hello0 | bar | 48 | hello48 | world | drop this | 0 (18 rows) set enable_hashjoin=on; set enable_nestloop=off; set enable_mergejoin=off; -- -- index scan -- set enable_seqscan=off; set enable_indexscan=on; set enable_bitmapscan=off; set enable_hashjoin=off; explain select * from t, pt where tid = ptid and pt1 = 'hello0'; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Gather Motion 2:1 (slice2; segments: 2) (cost=0.00..5.81 rows=1 width=50) -> Hash Join (cost=0.00..4.78 rows=1 width=50) Hash Cond: t.tid = pt.ptid -> Table Scan on t (cost=0.00..0.02 rows=1 width=19) -> Hash (cost=3.61..3.61 rows=1 width=31) -> Broadcast Motion 2:2 (slice1; segments: 2) (cost=0.00..3.61 rows=1 width=31) -> Sequence (cost=0.00..1.55 rows=1 width=31) -> Result (cost=10.00..100.00 rows=50 width=4) -> Function Scan on gp_partition_expansion (cost=10.00..100.00 rows=50 width=4) -> Dynamic Index Scan on pt (cost=0.00..0.52 rows=1 width=31) Index Cond: tid = 'hello0'::text Settings: enable_bitmapscan=off; enable_hashjoin=off; enable_indexscan=on; enable_mergejoin=off; enable_nestloop=off; enable_seqscan=off; gp_segments_for_planner=2; optimizer=on (12 rows) select * from t, pt where tid = ptid and pt1 = 'hello0'; dist | tid | t1 | t2 | dist | pt1 | pt2 | pt3 | ptid ------+-----+--------+-----+------+--------+-------+-----------+------ 0 | 0 | hello0 | bar | 0 | hello0 | world | drop this | 0 (1 row) -- -- NL Index Scan -- set enable_nestloop=on; set enable_indexscan=on; set enable_seqscan=off; set enable_hashjoin=off; explain select * from t, pt where tid = ptid; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Gather Motion 2:1 (slice2; segments: 2) (cost=0.00..7.17 rows=9 width=50) -> Hash Join (cost=0.00..5.73 rows=9 width=50) Hash Cond: pt.ptid = t.tid -> Dynamic Table Scan on pt (partIndex: 0) (cost=0.00..0.82 rows=27 width=31) -> Hash (cost=100.00..100.00 rows=50 width=4) -> Result (cost=10.00..100.00 rows=50 width=4) -> Broadcast Motion 2:2 (slice1; segments: 2) (cost=0.00..1.06 rows=2 width=19) -> Table Scan on t (cost=0.00..0.02 rows=1 width=19) Settings: enable_bitmapscan=off; enable_hashjoin=off; enable_indexscan=on; enable_mergejoin=off; enable_nestloop=on; enable_seqscan=off; gp_segments_for_planner=2; optimizer=on (9 rows) select * from t, pt where tid = ptid; dist | tid | t1 | t2 | dist | pt1 | pt2 | pt3 | ptid ------+-----+--------+-----+------+---------+-------+-----------+------ 1 | 1 | hello1 | bar | 1 | hello1 | world | drop this | 1 1 | 1 | hello1 | bar | 7 | hello7 | world | drop this | 1 1 | 1 | hello1 | bar | 13 | hello13 | world | drop this | 1 1 | 1 | hello1 | bar | 19 | hello19 | world | drop this | 1 1 | 1 | hello1 | bar | 25 | hello25 | world | drop this | 1 1 | 1 | hello1 | bar | 31 | hello31 | world | drop this | 1 1 | 1 | hello1 | bar | 37 | hello37 | world | drop this | 1 1 | 1 | hello1 | bar | 43 | hello43 | world | drop this | 1 1 | 1 | hello1 | bar | 49 | hello49 | world | drop this | 1 0 | 0 | hello0 | bar | 0 | hello0 | world | drop this | 0 0 | 0 | hello0 | bar | 6 | hello6 | world | drop this | 0 0 | 0 | hello0 | bar | 12 | hello12 | world | drop this | 0 0 | 0 | hello0 | bar | 18 | hello18 | world | drop this | 0 0 | 0 | hello0 | bar | 24 | hello24 | world | drop this | 0 0 | 0 | hello0 | bar | 30 | hello30 | world | drop this | 0 0 | 0 | hello0 | bar | 36 | hello36 | world | drop this | 0 0 | 0 | hello0 | bar | 42 | hello42 | world | drop this | 0 0 | 0 | hello0 | bar | 48 | hello48 | world | drop this | 0 (18 rows) -- -- Negative test cases where transform does not apply -- set enable_indexscan=off; set enable_seqscan=on; set enable_hashjoin=on; set enable_nestloop=off; explain select * from t, pt where t1 = pt1; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Gather Motion 2:1 (slice2; segments: 2) (cost=0.00..8.34 rows=1 width=50) -> Hash Join (cost=0.00..7.29 rows=1 width=50) Hash Cond: pt.pt1 = t.t1 -> Sequence (cost=0.00..3.45 rows=27 width=31) -> Result (cost=10.00..100.00 rows=50 width=4) -> Function Scan on gp_partition_expansion (cost=10.00..100.00 rows=50 width=4) -> Dynamic Table Scan on pt (partIndex: 0) (cost=0.00..0.82 rows=27 width=31) -> Hash (cost=1.06..1.06 rows=2 width=19) -> Broadcast Motion 2:2 (slice1; segments: 2) (cost=0.00..1.06 rows=2 width=19) -> Table Scan on t (cost=0.00..0.02 rows=1 width=19) Settings: enable_bitmapscan=off; enable_hashjoin=on; enable_indexscan=off; enable_mergejoin=off; enable_nestloop=off; enable_seqscan=on; gp_segments_for_planner=2; optimizer=on (11 rows) select * from t, pt where t1 = pt1; dist | tid | t1 | t2 | dist | pt1 | pt2 | pt3 | ptid ------+-----+--------+-----+------+--------+-------+-----------+------ 1 | 1 | hello1 | bar | 1 | hello1 | world | drop this | 1 0 | 0 | hello0 | bar | 0 | hello0 | world | drop this | 0 (2 rows) explain select * from t, pt where tid < ptid; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Gather Motion 2:1 (slice2; segments: 2) (cost=0.00..63622.22 rows=54 width=50) -> Nested Loop (cost=0.00..63618.58 rows=54 width=50) Join Filter: t.tid < pt.ptid -> Sequence (cost=0.00..3.45 rows=27 width=31) -> Result (cost=10.00..100.00 rows=50 width=4) -> Function Scan on gp_partition_expansion (cost=10.00..100.00 rows=50 width=4) -> Dynamic Table Scan on pt (partIndex: 0) (cost=0.00..0.82 rows=27 width=31) -> Materialize (cost=0.00..2.13 rows=2 width=19) -> Broadcast Motion 2:2 (slice1; segments: 2) (cost=0.00..1.06 rows=2 width=19) -> Table Scan on t (cost=0.00..0.02 rows=1 width=19) Settings: enable_bitmapscan=off; enable_hashjoin=on; enable_indexscan=off; enable_mergejoin=off; enable_nestloop=off; enable_seqscan=on; gp_segments_for_planner=2; optimizer=on (11 rows) select * from t, pt where tid < ptid; dist | tid | t1 | t2 | dist | pt1 | pt2 | pt3 | ptid ------+-----+--------+-----+------+---------+-------+-----------+------ 1 | 1 | hello1 | bar | 2 | hello2 | world | drop this | 2 0 | 0 | hello0 | bar | 2 | hello2 | world | drop this | 2 1 | 1 | hello1 | bar | 8 | hello8 | world | drop this | 2 0 | 0 | hello0 | bar | 8 | hello8 | world | drop this | 2 1 | 1 | hello1 | bar | 14 | hello14 | world | drop this | 2 0 | 0 | hello0 | bar | 14 | hello14 | world | drop this | 2 1 | 1 | hello1 | bar | 20 | hello20 | world | drop this | 2 0 | 0 | hello0 | bar | 20 | hello20 | world | drop this | 2 1 | 1 | hello1 | bar | 26 | hello26 | world | drop this | 2 0 | 0 | hello0 | bar | 26 | hello26 | world | drop this | 2 1 | 1 | hello1 | bar | 32 | hello32 | world | drop this | 2 0 | 0 | hello0 | bar | 32 | hello32 | world | drop this | 2 1 | 1 | hello1 | bar | 38 | hello38 | world | drop this | 2 0 | 0 | hello0 | bar | 38 | hello38 | world | drop this | 2 1 | 1 | hello1 | bar | 44 | hello44 | world | drop this | 2 0 | 0 | hello0 | bar | 44 | hello44 | world | drop this | 2 1 | 1 | hello1 | bar | 50 | hello50 | world | drop this | 2 0 | 0 | hello0 | bar | 50 | hello50 | world | drop this | 2 1 | 1 | hello1 | bar | 4 | hello4 | world | drop this | 4 0 | 0 | hello0 | bar | 4 | hello4 | world | drop this | 4 1 | 1 | hello1 | bar | 10 | hello10 | world | drop this | 4 0 | 0 | hello0 | bar | 10 | hello10 | world | drop this | 4 1 | 1 | hello1 | bar | 16 | hello16 | world | drop this | 4 0 | 0 | hello0 | bar | 16 | hello16 | world | drop this | 4 1 | 1 | hello1 | bar | 22 | hello22 | world | drop this | 4 0 | 0 | hello0 | bar | 22 | hello22 | world | drop this | 4 1 | 1 | hello1 | bar | 28 | hello28 | world | drop this | 4 0 | 0 | hello0 | bar | 28 | hello28 | world | drop this | 4 1 | 1 | hello1 | bar | 34 | hello34 | world | drop this | 4 0 | 0 | hello0 | bar | 34 | hello34 | world | drop this | 4 1 | 1 | hello1 | bar | 40 | hello40 | world | drop this | 4 0 | 0 | hello0 | bar | 40 | hello40 | world | drop this | 4 1 | 1 | hello1 | bar | 46 | hello46 | world | drop this | 4 0 | 0 | hello0 | bar | 46 | hello46 | world | drop this | 4 1 | 1 | hello1 | bar | 52 | hello52 | world | drop this | 4 0 | 0 | hello0 | bar | 52 | hello52 | world | drop this | 4 1 | 1 | hello1 | bar | 5 | hello5 | world | drop this | 5 0 | 0 | hello0 | bar | 5 | hello5 | world | drop this | 5 1 | 1 | hello1 | bar | 11 | hello11 | world | drop this | 5 0 | 0 | hello0 | bar | 11 | hello11 | world | drop this | 5 1 | 1 | hello1 | bar | 17 | hello17 | world | drop this | 5 0 | 0 | hello0 | bar | 17 | hello17 | world | drop this | 5 1 | 1 | hello1 | bar | 23 | hello23 | world | drop this | 5 0 | 0 | hello0 | bar | 23 | hello23 | world | drop this | 5 1 | 1 | hello1 | bar | 29 | hello29 | world | drop this | 5 0 | 0 | hello0 | bar | 29 | hello29 | world | drop this | 5 1 | 1 | hello1 | bar | 35 | hello35 | world | drop this | 5 0 | 0 | hello0 | bar | 35 | hello35 | world | drop this | 5 1 | 1 | hello1 | bar | 41 | hello41 | world | drop this | 5 0 | 0 | hello0 | bar | 41 | hello41 | world | drop this | 5 1 | 1 | hello1 | bar | 47 | hello47 | world | drop this | 5 0 | 0 | hello0 | bar | 47 | hello47 | world | drop this | 5 1 | 1 | hello1 | bar | 53 | hello53 | world | drop this | 5 0 | 0 | hello0 | bar | 53 | hello53 | world | drop this | 5 0 | 0 | hello0 | bar | 1 | hello1 | world | drop this | 1 0 | 0 | hello0 | bar | 7 | hello7 | world | drop this | 1 0 | 0 | hello0 | bar | 13 | hello13 | world | drop this | 1 0 | 0 | hello0 | bar | 19 | hello19 | world | drop this | 1 0 | 0 | hello0 | bar | 25 | hello25 | world | drop this | 1 0 | 0 | hello0 | bar | 31 | hello31 | world | drop this | 1 0 | 0 | hello0 | bar | 37 | hello37 | world | drop this | 1 0 | 0 | hello0 | bar | 43 | hello43 | world | drop this | 1 0 | 0 | hello0 | bar | 49 | hello49 | world | drop this | 1 1 | 1 | hello1 | bar | 3 | hello3 | world | drop this | 3 0 | 0 | hello0 | bar | 3 | hello3 | world | drop this | 3 1 | 1 | hello1 | bar | 9 | hello9 | world | drop this | 3 0 | 0 | hello0 | bar | 9 | hello9 | world | drop this | 3 1 | 1 | hello1 | bar | 15 | hello15 | world | drop this | 3 0 | 0 | hello0 | bar | 15 | hello15 | world | drop this | 3 1 | 1 | hello1 | bar | 21 | hello21 | world | drop this | 3 0 | 0 | hello0 | bar | 21 | hello21 | world | drop this | 3 1 | 1 | hello1 | bar | 27 | hello27 | world | drop this | 3 0 | 0 | hello0 | bar | 27 | hello27 | world | drop this | 3 1 | 1 | hello1 | bar | 33 | hello33 | world | drop this | 3 0 | 0 | hello0 | bar | 33 | hello33 | world | drop this | 3 1 | 1 | hello1 | bar | 39 | hello39 | world | drop this | 3 0 | 0 | hello0 | bar | 39 | hello39 | world | drop this | 3 1 | 1 | hello1 | bar | 45 | hello45 | world | drop this | 3 0 | 0 | hello0 | bar | 45 | hello45 | world | drop this | 3 1 | 1 | hello1 | bar | 51 | hello51 | world | drop this | 3 0 | 0 | hello0 | bar | 51 | hello51 | world | drop this | 3 (81 rows) -- -- cascading joins -- explain select * from t, t1, pt where t1.t2 = t.t2 and t1.tid = ptid; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Gather Motion 2:1 (slice3; segments: 2) (cost=0.00..10.96 rows=18 width=69) -> Hash Join (cost=0.00..8.75 rows=18 width=69) Hash Cond: pt.ptid = t1.tid -> Dynamic Table Scan on pt (partIndex: 0) (cost=0.00..0.82 rows=27 width=31) -> Hash (cost=100.00..100.00 rows=50 width=4) -> Result (cost=10.00..100.00 rows=50 width=4) -> Broadcast Motion 2:2 (slice2; segments: 2) (cost=0.00..3.41 rows=4 width=38) -> Hash Join (cost=0.00..2.26 rows=2 width=38) Hash Cond: t1.t2 = t.t2 -> Table Scan on t1 (cost=0.00..0.02 rows=1 width=19) -> Hash (cost=1.06..1.06 rows=2 width=19) -> Broadcast Motion 2:2 (slice1; segments: 2) (cost=0.00..1.06 rows=2 width=19) -> Table Scan on t (cost=0.00..0.02 rows=1 width=19) Settings: enable_bitmapscan=off; enable_hashjoin=on; enable_indexscan=off; enable_mergejoin=off; enable_nestloop=off; enable_seqscan=on; gp_segments_for_planner=2; optimizer=on (14 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 ------+-----+--------+-----+------+-----+--------+-----+------+---------+-------+-----------+------ 1 | 1 | hello1 | bar | 0 | 0 | hello0 | bar | 0 | hello0 | world | drop this | 0 0 | 0 | hello0 | bar | 0 | 0 | hello0 | bar | 0 | hello0 | world | drop this | 0 1 | 1 | hello1 | bar | 0 | 0 | hello0 | bar | 6 | hello6 | world | drop this | 0 0 | 0 | hello0 | bar | 0 | 0 | hello0 | bar | 6 | hello6 | world | drop this | 0 1 | 1 | hello1 | bar | 0 | 0 | hello0 | bar | 12 | hello12 | world | drop this | 0 0 | 0 | hello0 | bar | 0 | 0 | hello0 | bar | 12 | hello12 | world | drop this | 0 1 | 1 | hello1 | bar | 0 | 0 | hello0 | bar | 18 | hello18 | world | drop this | 0 0 | 0 | hello0 | bar | 0 | 0 | hello0 | bar | 18 | hello18 | world | drop this | 0 1 | 1 | hello1 | bar | 0 | 0 | hello0 | bar | 24 | hello24 | world | drop this | 0 0 | 0 | hello0 | bar | 0 | 0 | hello0 | bar | 24 | hello24 | world | drop this | 0 1 | 1 | hello1 | bar | 0 | 0 | hello0 | bar | 30 | hello30 | world | drop this | 0 0 | 0 | hello0 | bar | 0 | 0 | hello0 | bar | 30 | hello30 | world | drop this | 0 1 | 1 | hello1 | bar | 0 | 0 | hello0 | bar | 36 | hello36 | world | drop this | 0 0 | 0 | hello0 | bar | 0 | 0 | hello0 | bar | 36 | hello36 | world | drop this | 0 1 | 1 | hello1 | bar | 0 | 0 | hello0 | bar | 42 | hello42 | world | drop this | 0 0 | 0 | hello0 | bar | 0 | 0 | hello0 | bar | 42 | hello42 | world | drop this | 0 1 | 1 | hello1 | bar | 0 | 0 | hello0 | bar | 48 | hello48 | world | drop this | 0 0 | 0 | hello0 | bar | 0 | 0 | hello0 | bar | 48 | hello48 | world | drop this | 0 1 | 1 | hello1 | bar | 1 | 1 | hello1 | bar | 1 | hello1 | world | drop this | 1 0 | 0 | hello0 | bar | 1 | 1 | hello1 | bar | 1 | hello1 | world | drop this | 1 1 | 1 | hello1 | bar | 1 | 1 | hello1 | bar | 7 | hello7 | world | drop this | 1 0 | 0 | hello0 | bar | 1 | 1 | hello1 | bar | 7 | hello7 | world | drop this | 1 1 | 1 | hello1 | bar | 1 | 1 | hello1 | bar | 13 | hello13 | world | drop this | 1 0 | 0 | hello0 | bar | 1 | 1 | hello1 | bar | 13 | hello13 | world | drop this | 1 1 | 1 | hello1 | bar | 1 | 1 | hello1 | bar | 19 | hello19 | world | drop this | 1 0 | 0 | hello0 | bar | 1 | 1 | hello1 | bar | 19 | hello19 | world | drop this | 1 1 | 1 | hello1 | bar | 1 | 1 | hello1 | bar | 25 | hello25 | world | drop this | 1 0 | 0 | hello0 | bar | 1 | 1 | hello1 | bar | 25 | hello25 | world | drop this | 1 1 | 1 | hello1 | bar | 1 | 1 | hello1 | bar | 31 | hello31 | world | drop this | 1 0 | 0 | hello0 | bar | 1 | 1 | hello1 | bar | 31 | hello31 | world | drop this | 1 1 | 1 | hello1 | bar | 1 | 1 | hello1 | bar | 37 | hello37 | world | drop this | 1 0 | 0 | hello0 | bar | 1 | 1 | hello1 | bar | 37 | hello37 | world | drop this | 1 1 | 1 | hello1 | bar | 1 | 1 | hello1 | bar | 43 | hello43 | world | drop this | 1 0 | 0 | hello0 | bar | 1 | 1 | hello1 | bar | 43 | hello43 | world | drop this | 1 1 | 1 | hello1 | bar | 1 | 1 | hello1 | bar | 49 | hello49 | world | drop this | 1 0 | 0 | hello0 | bar | 1 | 1 | hello1 | bar | 49 | hello49 | world | drop this | 1 (36 rows) -- -- explain analyze -- explain analyze select * from t, pt where tid = ptid; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Gather Motion 2:1 (slice2; segments: 2) (cost=0.00..7.17 rows=9 width=50) Rows out: 18 rows at destination with 6.844 ms to first row, 6.870 ms to end, start offset by 0.889 ms. -> Hash Join (cost=0.00..5.73 rows=9 width=50) Hash Cond: pt.ptid = t.tid Rows out: Avg 9.0 rows x 2 workers. Max 9 rows (seg0) with 3.129 ms to first row, 5.949 ms to end, start offset by 1.629 ms. Executor memory: 1K bytes avg, 1K bytes max (seg0). Work_mem used: 1K bytes avg, 1K bytes max (seg0). Workfile: (0 spilling, 0 reused) (seg0) Hash chain length 1.0 avg, 1 max, using 2 of 524341 buckets. -> Dynamic Table Scan on pt (partIndex: 0) (cost=0.00..0.82 rows=27 width=31) Rows out: Avg 9.0 rows x 2 workers. Max 9 rows (seg0) with 0.075 ms to first row, 0.080 ms to end, start offset by 4.678 ms. -> Hash (cost=100.00..100.00 rows=50 width=4) Rows in: Avg 2.0 rows x 2 workers. Max 2 rows (seg0) with 0.119 ms to end, start offset by 4.559 ms. -> Result (cost=10.00..100.00 rows=50 width=4) Rows out: Avg 2.0 rows x 2 workers. Max 2 rows (seg0) with 0.096 ms to first row, 0.106 ms to end, start offset by 4.560 ms. -> Broadcast Motion 2:2 (slice1; segments: 2) (cost=0.00..1.06 rows=2 width=19) Rows out: Avg 2.0 rows x 2 workers at destination. Max 2 rows (seg0) with 0.031 ms to first row, 0.037 ms to end, start offset by 4.561 ms. -> Table Scan on t (cost=0.00..0.02 rows=1 width=19) Rows out: Avg 1.0 rows x 2 workers. Max 1 rows (seg0) with 0.052 ms to first row, 0.057 ms to end, start offset by 3.015 ms. Slice statistics: (slice0) Executor memory: 227K bytes. (slice1) Executor memory: 191K bytes avg x 2 workers, 191K bytes max (seg0). (slice2) Executor memory: 6363K bytes avg x 2 workers, 6363K bytes max (seg0). Work_mem: 1K bytes max. Statement statistics: Memory used: 128000K bytes Settings: enable_bitmapscan=off; enable_hashjoin=on; enable_indexscan=off; enable_mergejoin=off; enable_nestloop=off; enable_seqscan=on; gp_segments_for_planner=2; optimizer=on Total runtime: 8.299 ms (26 rows) -- -- Partitioned table on both sides of the join. This will create a result node as Append node is -- not projection capable. -- explain select * from pt, pt1 where pt.ptid = pt1.ptid and pt.pt1 = 'hello0' order by pt1.dist; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Gather Motion 2:1 (slice2; segments: 2) (cost=0.00..83.82 rows=5 width=62) Merge Key: pt1.dist -> Sort (cost=0.00..82.55 rows=5 width=62) Sort Key: pt1.dist -> Hash Join (cost=0.00..8.24 rows=5 width=62) Hash Cond: pt1.ptid = pt.ptid -> Dynamic Table Scan on pt1 (partIndex: 1) (cost=0.00..0.82 rows=27 width=31) -> Hash (cost=100.00..100.00 rows=50 width=4) -> Result (cost=10.00..100.00 rows=50 width=4) -> Broadcast Motion 2:2 (slice1; segments: 2) (cost=0.00..3.61 rows=1 width=31) -> Sequence (cost=0.00..1.55 rows=1 width=31) -> Result (cost=10.00..100.00 rows=50 width=4) -> Function Scan on gp_partition_expansion (cost=10.00..100.00 rows=50 width=4) -> Dynamic Index Scan on pt (cost=0.00..0.52 rows=1 width=31) Index Cond: pt1 = 'hello0'::text Settings: enable_bitmapscan=off; enable_hashjoin=on; enable_indexscan=off; enable_mergejoin=off; enable_nestloop=off; enable_seqscan=on; gp_segments_for_planner=2; optimizer=on (16 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 ------+--------+-------+-----------+------+------+---------+-------+-----------+------ 0 | hello0 | world | drop this | 0 | 0 | hello0 | world | drop this | 0 0 | hello0 | world | drop this | 0 | 6 | hello6 | world | drop this | 0 0 | hello0 | world | drop this | 0 | 12 | hello12 | world | drop this | 0 0 | hello0 | world | drop this | 0 | 18 | hello18 | world | drop this | 0 0 | hello0 | world | drop this | 0 | 24 | hello24 | world | drop this | 0 0 | hello0 | world | drop this | 0 | 30 | hello30 | world | drop this | 0 0 | hello0 | world | drop this | 0 | 36 | hello36 | world | drop this | 0 0 | hello0 | world | drop this | 0 | 42 | hello42 | world | drop this | 0 0 | hello0 | world | drop this | 0 | 48 | hello48 | world | drop this | 0 (9 rows) explain select count(*) from pt, pt1 where pt.ptid = pt1.ptid and pt.pt1 = 'hello0'; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Aggregate (cost=0.00..7.57 rows=1 width=8) -> Gather Motion 2:1 (slice2; segments: 2) (cost=0.00..6.54 rows=5 width=1) -> Hash Join (cost=0.00..5.53 rows=5 width=1) Hash Cond: pt1.ptid = pt.ptid -> Dynamic Table Scan on pt1 (partIndex: 1) (cost=0.00..0.11 rows=27 width=4) -> Hash (cost=100.00..100.00 rows=50 width=4) -> Result (cost=10.00..100.00 rows=50 width=4) -> Broadcast Motion 2:2 (slice1; segments: 2) (cost=0.00..3.20 rows=1 width=4) -> Sequence (cost=0.00..2.19 rows=1 width=4) -> Result (cost=10.00..100.00 rows=50 width=4) -> Function Scan on gp_partition_expansion (cost=10.00..100.00 rows=50 width=4) -> Result (cost=0.00..1.19 rows=1 width=4) -> Dynamic Index Scan on pt (cost=0.00..0.18 rows=1 width=11) Index Cond: pt1 = 'hello0'::text Settings: enable_bitmapscan=off; enable_hashjoin=on; enable_indexscan=off; enable_mergejoin=off; enable_nestloop=off; enable_seqscan=on; gp_segments_for_planner=2; optimizer=on (15 rows) select count(*) from pt, pt1 where pt.ptid = pt1.ptid and pt.pt1 = 'hello0'; count ------- 9 (1 row) -- -- Multi-level partitions -- drop schema if exists dpe_multi cascade; NOTICE: schema "dpe_multi" does not exist, skipping create schema dpe_multi; set search_path='dpe_multi'; set gp_segments_for_planner=2; set optimizer_segments=2; create table dim1(dist int, pid int, code text, t1 text); NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'dist' as the Greenplum Database data distribution key for this table. HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. insert into dim1 values (1, 0, 'OH', 'world1'); insert into dim1 values (1, 1, 'OH', 'world2'); insert into dim1 values (1, 100, 'GA', 'world2'); -- should not have a match at all create table fact1(dist int, pid int, code text, u int) partition by range(pid) subpartition by list(code) subpartition template ( subpartition ca values('CA'), subpartition oh values('OH'), subpartition wa values('WA') ) ( start (0) end (4) every (1) ); NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'dist' as the Greenplum Database data distribution key for this table. HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. NOTICE: CREATE TABLE will create partition "fact1_1_prt_1" for table "fact1" NOTICE: CREATE TABLE will create partition "fact1_1_prt_2" for table "fact1" NOTICE: CREATE TABLE will create partition "fact1_1_prt_3" for table "fact1" NOTICE: CREATE TABLE will create partition "fact1_1_prt_4" for table "fact1" NOTICE: CREATE TABLE will create partition "fact1_1_prt_1_2_prt_ca" for table "fact1_1_prt_1" NOTICE: CREATE TABLE will create partition "fact1_1_prt_1_2_prt_oh" for table "fact1_1_prt_1" NOTICE: CREATE TABLE will create partition "fact1_1_prt_1_2_prt_wa" for table "fact1_1_prt_1" NOTICE: CREATE TABLE will create partition "fact1_1_prt_2_2_prt_ca" for table "fact1_1_prt_2" NOTICE: CREATE TABLE will create partition "fact1_1_prt_2_2_prt_oh" for table "fact1_1_prt_2" NOTICE: CREATE TABLE will create partition "fact1_1_prt_2_2_prt_wa" for table "fact1_1_prt_2" NOTICE: CREATE TABLE will create partition "fact1_1_prt_3_2_prt_ca" for table "fact1_1_prt_3" NOTICE: CREATE TABLE will create partition "fact1_1_prt_3_2_prt_oh" for table "fact1_1_prt_3" NOTICE: CREATE TABLE will create partition "fact1_1_prt_3_2_prt_wa" for table "fact1_1_prt_3" NOTICE: CREATE TABLE will create partition "fact1_1_prt_4_2_prt_ca" for table "fact1_1_prt_4" NOTICE: CREATE TABLE will create partition "fact1_1_prt_4_2_prt_oh" for table "fact1_1_prt_4" NOTICE: CREATE TABLE will create partition "fact1_1_prt_4_2_prt_wa" for table "fact1_1_prt_4" insert into fact1 select 1, i % 4 , 'OH', i from generate_series (1,100) i; insert into fact1 select 1, i % 4 , 'CA', i + 10000 from generate_series (1,100) i; -- -- Join on all partitioning columns -- set gp_dynamic_partition_pruning=off; explain select * from dim1 inner join fact1 on (dim1.pid=fact1.pid and dim1.code=fact1.code) order by fact1.u; QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Gather Motion 2:1 (slice2; segments: 2) (cost=62.46..62.49 rows=6 width=62) Merge Key: "?column9?" -> Sort (cost=62.46..62.49 rows=6 width=62) Sort Key: dpe_multi.fact1.u -> Hash Join (cost=1.07..62.25 rows=6 width=62) Hash Cond: dpe_multi.fact1.pid = dim1.pid AND dpe_multi.fact1.code = dim1.code -> Append (cost=0.00..43.36 rows=1770 width=44) -> Seq Scan on fact1_1_prt_2_2_prt_oh fact1 (cost=0.00..5.42 rows=221 width=44) -> Seq Scan on fact1_1_prt_2_2_prt_wa fact1 (cost=0.00..0.00 rows=1 width=44) -> Seq Scan on fact1_1_prt_2_2_prt_ca fact1 (cost=0.00..5.42 rows=221 width=44) -> Seq Scan on fact1_1_prt_1_2_prt_ca fact1 (cost=0.00..5.42 rows=221 width=44) -> Seq Scan on fact1_1_prt_1_2_prt_oh fact1 (cost=0.00..5.42 rows=221 width=44) -> Seq Scan on fact1_1_prt_1_2_prt_wa fact1 (cost=0.00..0.00 rows=1 width=44) -> Seq Scan on fact1_1_prt_3_2_prt_oh fact1 (cost=0.00..5.42 rows=221 width=44) -> Seq Scan on fact1_1_prt_3_2_prt_wa fact1 (cost=0.00..0.00 rows=1 width=44) -> Seq Scan on fact1_1_prt_3_2_prt_ca fact1 (cost=0.00..5.42 rows=221 width=44) -> Seq Scan on fact1_1_prt_4_2_prt_ca fact1 (cost=0.00..5.42 rows=221 width=44) -> Seq Scan on fact1_1_prt_4_2_prt_oh fact1 (cost=0.00..5.42 rows=221 width=44) -> Seq Scan on fact1_1_prt_4_2_prt_wa fact1 (cost=0.00..0.00 rows=1 width=44) -> Hash (cost=1.04..1.04 rows=1 width=18) -> Broadcast Motion 2:2 (slice1; segments: 2) (cost=0.00..1.04 rows=1 width=18) -> Seq Scan on dim1 (cost=0.00..1.01 rows=1 width=18) Settings: enable_bitmapscan=off; enable_hashjoin=on; enable_indexscan=off; enable_mergejoin=off; enable_nestloop=off; enable_seqscan=on; gp_dynamic_partition_pruning=off; gp_segments_for_planner=2; optimizer=on (23 rows) select * from dim1 inner join fact1 on (dim1.pid=fact1.pid and dim1.code=fact1.code) order by fact1.u; dist | pid | code | t1 | dist | pid | code | u ------+-----+------+--------+------+-----+------+----- 1 | 1 | OH | world2 | 1 | 1 | OH | 1 1 | 0 | OH | world1 | 1 | 0 | OH | 4 1 | 1 | OH | world2 | 1 | 1 | OH | 5 1 | 0 | OH | world1 | 1 | 0 | OH | 8 1 | 1 | OH | world2 | 1 | 1 | OH | 9 1 | 0 | OH | world1 | 1 | 0 | OH | 12 1 | 1 | OH | world2 | 1 | 1 | OH | 13 1 | 0 | OH | world1 | 1 | 0 | OH | 16 1 | 1 | OH | world2 | 1 | 1 | OH | 17 1 | 0 | OH | world1 | 1 | 0 | OH | 20 1 | 1 | OH | world2 | 1 | 1 | OH | 21 1 | 0 | OH | world1 | 1 | 0 | OH | 24 1 | 1 | OH | world2 | 1 | 1 | OH | 25 1 | 0 | OH | world1 | 1 | 0 | OH | 28 1 | 1 | OH | world2 | 1 | 1 | OH | 29 1 | 0 | OH | world1 | 1 | 0 | OH | 32 1 | 1 | OH | world2 | 1 | 1 | OH | 33 1 | 0 | OH | world1 | 1 | 0 | OH | 36 1 | 1 | OH | world2 | 1 | 1 | OH | 37 1 | 0 | OH | world1 | 1 | 0 | OH | 40 1 | 1 | OH | world2 | 1 | 1 | OH | 41 1 | 0 | OH | world1 | 1 | 0 | OH | 44 1 | 1 | OH | world2 | 1 | 1 | OH | 45 1 | 0 | OH | world1 | 1 | 0 | OH | 48 1 | 1 | OH | world2 | 1 | 1 | OH | 49 1 | 0 | OH | world1 | 1 | 0 | OH | 52 1 | 1 | OH | world2 | 1 | 1 | OH | 53 1 | 0 | OH | world1 | 1 | 0 | OH | 56 1 | 1 | OH | world2 | 1 | 1 | OH | 57 1 | 0 | OH | world1 | 1 | 0 | OH | 60 1 | 1 | OH | world2 | 1 | 1 | OH | 61 1 | 0 | OH | world1 | 1 | 0 | OH | 64 1 | 1 | OH | world2 | 1 | 1 | OH | 65 1 | 0 | OH | world1 | 1 | 0 | OH | 68 1 | 1 | OH | world2 | 1 | 1 | OH | 69 1 | 0 | OH | world1 | 1 | 0 | OH | 72 1 | 1 | OH | world2 | 1 | 1 | OH | 73 1 | 0 | OH | world1 | 1 | 0 | OH | 76 1 | 1 | OH | world2 | 1 | 1 | OH | 77 1 | 0 | OH | world1 | 1 | 0 | OH | 80 1 | 1 | OH | world2 | 1 | 1 | OH | 81 1 | 0 | OH | world1 | 1 | 0 | OH | 84 1 | 1 | OH | world2 | 1 | 1 | OH | 85 1 | 0 | OH | world1 | 1 | 0 | OH | 88 1 | 1 | OH | world2 | 1 | 1 | OH | 89 1 | 0 | OH | world1 | 1 | 0 | OH | 92 1 | 1 | OH | world2 | 1 | 1 | OH | 93 1 | 0 | OH | world1 | 1 | 0 | OH | 96 1 | 1 | OH | world2 | 1 | 1 | OH | 97 1 | 0 | OH | world1 | 1 | 0 | OH | 100 (50 rows) set gp_dynamic_partition_pruning=on; explain select * from dim1 inner join fact1 on (dim1.pid=fact1.pid and dim1.code=fact1.code) order by fact1.u; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Gather Motion 2:1 (slice3; segments: 2) (cost=63.49..63.52 rows=6 width=62) Merge Key: "?column9?" -> Sort (cost=63.49..63.52 rows=6 width=62) Sort Key: dpe_multi.fact1.u InitPlan (slice4) -> Aggregate (cost=1.01..1.02 rows=1 width=32) -> Gather Motion 2:1 (slice2; segments: 2) (cost=0.00..1.01 rows=1 width=18) -> Seq Scan on dim1 (cost=0.00..1.01 rows=1 width=18) -> Hash Join (cost=1.07..62.25 rows=6 width=62) Hash Cond: dpe_multi.fact1.pid = dpe_multi.dim1.pid AND dpe_multi.fact1.code = dpe_multi.dim1.code -> Append (cost=0.00..43.36 rows=1770 width=44) -> Result (cost=0.00..5.42 rows=221 width=44) One-Time Filter: 446016::oid = ANY ($0) -> Seq Scan on fact1_1_prt_2_2_prt_oh fact1 (cost=0.00..5.42 rows=221 width=44) -> Result (cost=0.00..0.00 rows=1 width=44) One-Time Filter: 446043::oid = ANY ($0) -> Seq Scan on fact1_1_prt_2_2_prt_wa fact1 (cost=0.00..0.00 rows=1 width=44) -> Result (cost=0.00..5.42 rows=221 width=44) One-Time Filter: 445989::oid = ANY ($0) -> Seq Scan on fact1_1_prt_2_2_prt_ca fact1 (cost=0.00..5.42 rows=221 width=44) -> Result (cost=0.00..5.42 rows=221 width=44) One-Time Filter: 445882::oid = ANY ($0) -> Seq Scan on fact1_1_prt_1_2_prt_ca fact1 (cost=0.00..5.42 rows=221 width=44) -> Result (cost=0.00..5.42 rows=221 width=44) One-Time Filter: 445909::oid = ANY ($0) -> Seq Scan on fact1_1_prt_1_2_prt_oh fact1 (cost=0.00..5.42 rows=221 width=44) -> Result (cost=0.00..0.00 rows=1 width=44) One-Time Filter: 445936::oid = ANY ($0) -> Seq Scan on fact1_1_prt_1_2_prt_wa fact1 (cost=0.00..0.00 rows=1 width=44) -> Result (cost=0.00..5.42 rows=221 width=44) One-Time Filter: 446123::oid = ANY ($0) -> Seq Scan on fact1_1_prt_3_2_prt_oh fact1 (cost=0.00..5.42 rows=221 width=44) -> Result (cost=0.00..0.00 rows=1 width=44) One-Time Filter: 446150::oid = ANY ($0) -> Seq Scan on fact1_1_prt_3_2_prt_wa fact1 (cost=0.00..0.00 rows=1 width=44) -> Result (cost=0.00..5.42 rows=221 width=44) One-Time Filter: 446096::oid = ANY ($0) -> Seq Scan on fact1_1_prt_3_2_prt_ca fact1 (cost=0.00..5.42 rows=221 width=44) -> Result (cost=0.00..5.42 rows=221 width=44) One-Time Filter: 446203::oid = ANY ($0) -> Seq Scan on fact1_1_prt_4_2_prt_ca fact1 (cost=0.00..5.42 rows=221 width=44) -> Result (cost=0.00..5.42 rows=221 width=44) One-Time Filter: 446230::oid = ANY ($0) -> Seq Scan on fact1_1_prt_4_2_prt_oh fact1 (cost=0.00..5.42 rows=221 width=44) -> Result (cost=0.00..0.00 rows=1 width=44) One-Time Filter: 446257::oid = ANY ($0) -> Seq Scan on fact1_1_prt_4_2_prt_wa fact1 (cost=0.00..0.00 rows=1 width=44) -> Hash (cost=1.04..1.04 rows=1 width=18) -> Broadcast Motion 2:2 (slice1; segments: 2) (cost=0.00..1.04 rows=1 width=18) -> Seq Scan on dim1 (cost=0.00..1.01 rows=1 width=18) Settings: enable_bitmapscan=off; enable_hashjoin=on; enable_indexscan=off; enable_mergejoin=off; enable_nestloop=off; enable_seqscan=on; gp_dynamic_partition_pruning=on; gp_segments_for_planner=2; optimizer=on (51 rows) select * from dim1 inner join fact1 on (dim1.pid=fact1.pid and dim1.code=fact1.code) order by fact1.u; dist | pid | code | t1 | dist | pid | code | u ------+-----+------+--------+------+-----+------+----- 1 | 1 | OH | world2 | 1 | 1 | OH | 1 1 | 0 | OH | world1 | 1 | 0 | OH | 4 1 | 1 | OH | world2 | 1 | 1 | OH | 5 1 | 0 | OH | world1 | 1 | 0 | OH | 8 1 | 1 | OH | world2 | 1 | 1 | OH | 9 1 | 0 | OH | world1 | 1 | 0 | OH | 12 1 | 1 | OH | world2 | 1 | 1 | OH | 13 1 | 0 | OH | world1 | 1 | 0 | OH | 16 1 | 1 | OH | world2 | 1 | 1 | OH | 17 1 | 0 | OH | world1 | 1 | 0 | OH | 20 1 | 1 | OH | world2 | 1 | 1 | OH | 21 1 | 0 | OH | world1 | 1 | 0 | OH | 24 1 | 1 | OH | world2 | 1 | 1 | OH | 25 1 | 0 | OH | world1 | 1 | 0 | OH | 28 1 | 1 | OH | world2 | 1 | 1 | OH | 29 1 | 0 | OH | world1 | 1 | 0 | OH | 32 1 | 1 | OH | world2 | 1 | 1 | OH | 33 1 | 0 | OH | world1 | 1 | 0 | OH | 36 1 | 1 | OH | world2 | 1 | 1 | OH | 37 1 | 0 | OH | world1 | 1 | 0 | OH | 40 1 | 1 | OH | world2 | 1 | 1 | OH | 41 1 | 0 | OH | world1 | 1 | 0 | OH | 44 1 | 1 | OH | world2 | 1 | 1 | OH | 45 1 | 0 | OH | world1 | 1 | 0 | OH | 48 1 | 1 | OH | world2 | 1 | 1 | OH | 49 1 | 0 | OH | world1 | 1 | 0 | OH | 52 1 | 1 | OH | world2 | 1 | 1 | OH | 53 1 | 0 | OH | world1 | 1 | 0 | OH | 56 1 | 1 | OH | world2 | 1 | 1 | OH | 57 1 | 0 | OH | world1 | 1 | 0 | OH | 60 1 | 1 | OH | world2 | 1 | 1 | OH | 61 1 | 0 | OH | world1 | 1 | 0 | OH | 64 1 | 1 | OH | world2 | 1 | 1 | OH | 65 1 | 0 | OH | world1 | 1 | 0 | OH | 68 1 | 1 | OH | world2 | 1 | 1 | OH | 69 1 | 0 | OH | world1 | 1 | 0 | OH | 72 1 | 1 | OH | world2 | 1 | 1 | OH | 73 1 | 0 | OH | world1 | 1 | 0 | OH | 76 1 | 1 | OH | world2 | 1 | 1 | OH | 77 1 | 0 | OH | world1 | 1 | 0 | OH | 80 1 | 1 | OH | world2 | 1 | 1 | OH | 81 1 | 0 | OH | world1 | 1 | 0 | OH | 84 1 | 1 | OH | world2 | 1 | 1 | OH | 85 1 | 0 | OH | world1 | 1 | 0 | OH | 88 1 | 1 | OH | world2 | 1 | 1 | OH | 89 1 | 0 | OH | world1 | 1 | 0 | OH | 92 1 | 1 | OH | world2 | 1 | 1 | OH | 93 1 | 0 | OH | world1 | 1 | 0 | OH | 96 1 | 1 | OH | world2 | 1 | 1 | OH | 97 1 | 0 | OH | world1 | 1 | 0 | OH | 100 (50 rows) -- -- Join on one of the partitioning columns -- set gp_dynamic_partition_pruning=off; explain select * from dim1 inner join fact1 on (dim1.pid=fact1.pid) order by fact1.u; QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Gather Motion 2:1 (slice2; segments: 2) (cost=53.62..53.65 rows=6 width=62) Merge Key: "?column9?" -> Sort (cost=53.62..53.65 rows=6 width=62) Sort Key: dpe_multi.fact1.u -> Hash Join (cost=1.06..53.41 rows=6 width=62) Hash Cond: dpe_multi.fact1.pid = dim1.pid -> Append (cost=0.00..43.36 rows=1770 width=44) -> Seq Scan on fact1_1_prt_2_2_prt_oh fact1 (cost=0.00..5.42 rows=221 width=44) -> Seq Scan on fact1_1_prt_2_2_prt_wa fact1 (cost=0.00..0.00 rows=1 width=44) -> Seq Scan on fact1_1_prt_2_2_prt_ca fact1 (cost=0.00..5.42 rows=221 width=44) -> Seq Scan on fact1_1_prt_1_2_prt_ca fact1 (cost=0.00..5.42 rows=221 width=44) -> Seq Scan on fact1_1_prt_1_2_prt_oh fact1 (cost=0.00..5.42 rows=221 width=44) -> Seq Scan on fact1_1_prt_1_2_prt_wa fact1 (cost=0.00..0.00 rows=1 width=44) -> Seq Scan on fact1_1_prt_3_2_prt_oh fact1 (cost=0.00..5.42 rows=221 width=44) -> Seq Scan on fact1_1_prt_3_2_prt_wa fact1 (cost=0.00..0.00 rows=1 width=44) -> Seq Scan on fact1_1_prt_3_2_prt_ca fact1 (cost=0.00..5.42 rows=221 width=44) -> Seq Scan on fact1_1_prt_4_2_prt_ca fact1 (cost=0.00..5.42 rows=221 width=44) -> Seq Scan on fact1_1_prt_4_2_prt_oh fact1 (cost=0.00..5.42 rows=221 width=44) -> Seq Scan on fact1_1_prt_4_2_prt_wa fact1 (cost=0.00..0.00 rows=1 width=44) -> Hash (cost=1.04..1.04 rows=1 width=18) -> Broadcast Motion 2:2 (slice1; segments: 2) (cost=0.00..1.04 rows=1 width=18) -> Seq Scan on dim1 (cost=0.00..1.01 rows=1 width=18) Settings: enable_bitmapscan=off; enable_hashjoin=on; enable_indexscan=off; enable_mergejoin=off; enable_nestloop=off; enable_seqscan=on; gp_dynamic_partition_pruning=off; gp_segments_for_planner=2; optimizer=on (23 rows) select * from dim1 inner join fact1 on (dim1.pid=fact1.pid) order by fact1.u; dist | pid | code | t1 | dist | pid | code | u ------+-----+------+--------+------+-----+------+------- 1 | 1 | OH | world2 | 1 | 1 | OH | 1 1 | 0 | OH | world1 | 1 | 0 | OH | 4 1 | 1 | OH | world2 | 1 | 1 | OH | 5 1 | 0 | OH | world1 | 1 | 0 | OH | 8 1 | 1 | OH | world2 | 1 | 1 | OH | 9 1 | 0 | OH | world1 | 1 | 0 | OH | 12 1 | 1 | OH | world2 | 1 | 1 | OH | 13 1 | 0 | OH | world1 | 1 | 0 | OH | 16 1 | 1 | OH | world2 | 1 | 1 | OH | 17 1 | 0 | OH | world1 | 1 | 0 | OH | 20 1 | 1 | OH | world2 | 1 | 1 | OH | 21 1 | 0 | OH | world1 | 1 | 0 | OH | 24 1 | 1 | OH | world2 | 1 | 1 | OH | 25 1 | 0 | OH | world1 | 1 | 0 | OH | 28 1 | 1 | OH | world2 | 1 | 1 | OH | 29 1 | 0 | OH | world1 | 1 | 0 | OH | 32 1 | 1 | OH | world2 | 1 | 1 | OH | 33 1 | 0 | OH | world1 | 1 | 0 | OH | 36 1 | 1 | OH | world2 | 1 | 1 | OH | 37 1 | 0 | OH | world1 | 1 | 0 | OH | 40 1 | 1 | OH | world2 | 1 | 1 | OH | 41 1 | 0 | OH | world1 | 1 | 0 | OH | 44 1 | 1 | OH | world2 | 1 | 1 | OH | 45 1 | 0 | OH | world1 | 1 | 0 | OH | 48 1 | 1 | OH | world2 | 1 | 1 | OH | 49 1 | 0 | OH | world1 | 1 | 0 | OH | 52 1 | 1 | OH | world2 | 1 | 1 | OH | 53 1 | 0 | OH | world1 | 1 | 0 | OH | 56 1 | 1 | OH | world2 | 1 | 1 | OH | 57 1 | 0 | OH | world1 | 1 | 0 | OH | 60 1 | 1 | OH | world2 | 1 | 1 | OH | 61 1 | 0 | OH | world1 | 1 | 0 | OH | 64 1 | 1 | OH | world2 | 1 | 1 | OH | 65 1 | 0 | OH | world1 | 1 | 0 | OH | 68 1 | 1 | OH | world2 | 1 | 1 | OH | 69 1 | 0 | OH | world1 | 1 | 0 | OH | 72 1 | 1 | OH | world2 | 1 | 1 | OH | 73 1 | 0 | OH | world1 | 1 | 0 | OH | 76 1 | 1 | OH | world2 | 1 | 1 | OH | 77 1 | 0 | OH | world1 | 1 | 0 | OH | 80 1 | 1 | OH | world2 | 1 | 1 | OH | 81 1 | 0 | OH | world1 | 1 | 0 | OH | 84 1 | 1 | OH | world2 | 1 | 1 | OH | 85 1 | 0 | OH | world1 | 1 | 0 | OH | 88 1 | 1 | OH | world2 | 1 | 1 | OH | 89 1 | 0 | OH | world1 | 1 | 0 | OH | 92 1 | 1 | OH | world2 | 1 | 1 | OH | 93 1 | 0 | OH | world1 | 1 | 0 | OH | 96 1 | 1 | OH | world2 | 1 | 1 | OH | 97 1 | 0 | OH | world1 | 1 | 0 | OH | 100 1 | 1 | OH | world2 | 1 | 1 | CA | 10001 1 | 0 | OH | world1 | 1 | 0 | CA | 10004 1 | 1 | OH | world2 | 1 | 1 | CA | 10005 1 | 0 | OH | world1 | 1 | 0 | CA | 10008 1 | 1 | OH | world2 | 1 | 1 | CA | 10009 1 | 0 | OH | world1 | 1 | 0 | CA | 10012 1 | 1 | OH | world2 | 1 | 1 | CA | 10013 1 | 0 | OH | world1 | 1 | 0 | CA | 10016 1 | 1 | OH | world2 | 1 | 1 | CA | 10017 1 | 0 | OH | world1 | 1 | 0 | CA | 10020 1 | 1 | OH | world2 | 1 | 1 | CA | 10021 1 | 0 | OH | world1 | 1 | 0 | CA | 10024 1 | 1 | OH | world2 | 1 | 1 | CA | 10025 1 | 0 | OH | world1 | 1 | 0 | CA | 10028 1 | 1 | OH | world2 | 1 | 1 | CA | 10029 1 | 0 | OH | world1 | 1 | 0 | CA | 10032 1 | 1 | OH | world2 | 1 | 1 | CA | 10033 1 | 0 | OH | world1 | 1 | 0 | CA | 10036 1 | 1 | OH | world2 | 1 | 1 | CA | 10037 1 | 0 | OH | world1 | 1 | 0 | CA | 10040 1 | 1 | OH | world2 | 1 | 1 | CA | 10041 1 | 0 | OH | world1 | 1 | 0 | CA | 10044 1 | 1 | OH | world2 | 1 | 1 | CA | 10045 1 | 0 | OH | world1 | 1 | 0 | CA | 10048 1 | 1 | OH | world2 | 1 | 1 | CA | 10049 1 | 0 | OH | world1 | 1 | 0 | CA | 10052 1 | 1 | OH | world2 | 1 | 1 | CA | 10053 1 | 0 | OH | world1 | 1 | 0 | CA | 10056 1 | 1 | OH | world2 | 1 | 1 | CA | 10057 1 | 0 | OH | world1 | 1 | 0 | CA | 10060 1 | 1 | OH | world2 | 1 | 1 | CA | 10061 1 | 0 | OH | world1 | 1 | 0 | CA | 10064 1 | 1 | OH | world2 | 1 | 1 | CA | 10065 1 | 0 | OH | world1 | 1 | 0 | CA | 10068 1 | 1 | OH | world2 | 1 | 1 | CA | 10069 1 | 0 | OH | world1 | 1 | 0 | CA | 10072 1 | 1 | OH | world2 | 1 | 1 | CA | 10073 1 | 0 | OH | world1 | 1 | 0 | CA | 10076 1 | 1 | OH | world2 | 1 | 1 | CA | 10077 1 | 0 | OH | world1 | 1 | 0 | CA | 10080 1 | 1 | OH | world2 | 1 | 1 | CA | 10081 1 | 0 | OH | world1 | 1 | 0 | CA | 10084 1 | 1 | OH | world2 | 1 | 1 | CA | 10085 1 | 0 | OH | world1 | 1 | 0 | CA | 10088 1 | 1 | OH | world2 | 1 | 1 | CA | 10089 1 | 0 | OH | world1 | 1 | 0 | CA | 10092 1 | 1 | OH | world2 | 1 | 1 | CA | 10093 1 | 0 | OH | world1 | 1 | 0 | CA | 10096 1 | 1 | OH | world2 | 1 | 1 | CA | 10097 1 | 0 | OH | world1 | 1 | 0 | CA | 10100 (100 rows) set gp_dynamic_partition_pruning=on; explain select * from dim1 inner join fact1 on (dim1.pid=fact1.pid) order by fact1.u; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Gather Motion 2:1 (slice3; segments: 2) (cost=54.65..54.67 rows=6 width=62) Merge Key: "?column9?" -> Sort (cost=54.65..54.67 rows=6 width=62) Sort Key: dpe_multi.fact1.u InitPlan (slice4) -> Aggregate (cost=1.01..1.02 rows=1 width=32) -> Gather Motion 2:1 (slice2; segments: 2) (cost=0.00..1.01 rows=1 width=18) -> Seq Scan on dim1 (cost=0.00..1.01 rows=1 width=18) -> Hash Join (cost=1.06..53.41 rows=6 width=62) Hash Cond: dpe_multi.fact1.pid = dpe_multi.dim1.pid -> Append (cost=0.00..43.36 rows=1770 width=44) -> Result (cost=0.00..5.42 rows=221 width=44) One-Time Filter: 446016::oid = ANY ($0) -> Seq Scan on fact1_1_prt_2_2_prt_oh fact1 (cost=0.00..5.42 rows=221 width=44) -> Result (cost=0.00..0.00 rows=1 width=44) One-Time Filter: 446043::oid = ANY ($0) -> Seq Scan on fact1_1_prt_2_2_prt_wa fact1 (cost=0.00..0.00 rows=1 width=44) -> Result (cost=0.00..5.42 rows=221 width=44) One-Time Filter: 445989::oid = ANY ($0) -> Seq Scan on fact1_1_prt_2_2_prt_ca fact1 (cost=0.00..5.42 rows=221 width=44) -> Result (cost=0.00..5.42 rows=221 width=44) One-Time Filter: 445882::oid = ANY ($0) -> Seq Scan on fact1_1_prt_1_2_prt_ca fact1 (cost=0.00..5.42 rows=221 width=44) -> Result (cost=0.00..5.42 rows=221 width=44) One-Time Filter: 445909::oid = ANY ($0) -> Seq Scan on fact1_1_prt_1_2_prt_oh fact1 (cost=0.00..5.42 rows=221 width=44) -> Result (cost=0.00..0.00 rows=1 width=44) One-Time Filter: 445936::oid = ANY ($0) -> Seq Scan on fact1_1_prt_1_2_prt_wa fact1 (cost=0.00..0.00 rows=1 width=44) -> Result (cost=0.00..5.42 rows=221 width=44) One-Time Filter: 446123::oid = ANY ($0) -> Seq Scan on fact1_1_prt_3_2_prt_oh fact1 (cost=0.00..5.42 rows=221 width=44) -> Result (cost=0.00..0.00 rows=1 width=44) One-Time Filter: 446150::oid = ANY ($0) -> Seq Scan on fact1_1_prt_3_2_prt_wa fact1 (cost=0.00..0.00 rows=1 width=44) -> Result (cost=0.00..5.42 rows=221 width=44) One-Time Filter: 446096::oid = ANY ($0) -> Seq Scan on fact1_1_prt_3_2_prt_ca fact1 (cost=0.00..5.42 rows=221 width=44) -> Result (cost=0.00..5.42 rows=221 width=44) One-Time Filter: 446203::oid = ANY ($0) -> Seq Scan on fact1_1_prt_4_2_prt_ca fact1 (cost=0.00..5.42 rows=221 width=44) -> Result (cost=0.00..5.42 rows=221 width=44) One-Time Filter: 446230::oid = ANY ($0) -> Seq Scan on fact1_1_prt_4_2_prt_oh fact1 (cost=0.00..5.42 rows=221 width=44) -> Result (cost=0.00..0.00 rows=1 width=44) One-Time Filter: 446257::oid = ANY ($0) -> Seq Scan on fact1_1_prt_4_2_prt_wa fact1 (cost=0.00..0.00 rows=1 width=44) -> Hash (cost=1.04..1.04 rows=1 width=18) -> Broadcast Motion 2:2 (slice1; segments: 2) (cost=0.00..1.04 rows=1 width=18) -> Seq Scan on dim1 (cost=0.00..1.01 rows=1 width=18) Settings: enable_bitmapscan=off; enable_hashjoin=on; enable_indexscan=off; enable_mergejoin=off; enable_nestloop=off; enable_seqscan=on; gp_dynamic_partition_pruning=on; gp_segments_for_planner=2; optimizer=on (51 rows) select * from dim1 inner join fact1 on (dim1.pid=fact1.pid) order by fact1.u; dist | pid | code | t1 | dist | pid | code | u ------+-----+------+--------+------+-----+------+------- 1 | 1 | OH | world2 | 1 | 1 | OH | 1 1 | 0 | OH | world1 | 1 | 0 | OH | 4 1 | 1 | OH | world2 | 1 | 1 | OH | 5 1 | 0 | OH | world1 | 1 | 0 | OH | 8 1 | 1 | OH | world2 | 1 | 1 | OH | 9 1 | 0 | OH | world1 | 1 | 0 | OH | 12 1 | 1 | OH | world2 | 1 | 1 | OH | 13 1 | 0 | OH | world1 | 1 | 0 | OH | 16 1 | 1 | OH | world2 | 1 | 1 | OH | 17 1 | 0 | OH | world1 | 1 | 0 | OH | 20 1 | 1 | OH | world2 | 1 | 1 | OH | 21 1 | 0 | OH | world1 | 1 | 0 | OH | 24 1 | 1 | OH | world2 | 1 | 1 | OH | 25 1 | 0 | OH | world1 | 1 | 0 | OH | 28 1 | 1 | OH | world2 | 1 | 1 | OH | 29 1 | 0 | OH | world1 | 1 | 0 | OH | 32 1 | 1 | OH | world2 | 1 | 1 | OH | 33 1 | 0 | OH | world1 | 1 | 0 | OH | 36 1 | 1 | OH | world2 | 1 | 1 | OH | 37 1 | 0 | OH | world1 | 1 | 0 | OH | 40 1 | 1 | OH | world2 | 1 | 1 | OH | 41 1 | 0 | OH | world1 | 1 | 0 | OH | 44 1 | 1 | OH | world2 | 1 | 1 | OH | 45 1 | 0 | OH | world1 | 1 | 0 | OH | 48 1 | 1 | OH | world2 | 1 | 1 | OH | 49 1 | 0 | OH | world1 | 1 | 0 | OH | 52 1 | 1 | OH | world2 | 1 | 1 | OH | 53 1 | 0 | OH | world1 | 1 | 0 | OH | 56 1 | 1 | OH | world2 | 1 | 1 | OH | 57 1 | 0 | OH | world1 | 1 | 0 | OH | 60 1 | 1 | OH | world2 | 1 | 1 | OH | 61 1 | 0 | OH | world1 | 1 | 0 | OH | 64 1 | 1 | OH | world2 | 1 | 1 | OH | 65 1 | 0 | OH | world1 | 1 | 0 | OH | 68 1 | 1 | OH | world2 | 1 | 1 | OH | 69 1 | 0 | OH | world1 | 1 | 0 | OH | 72 1 | 1 | OH | world2 | 1 | 1 | OH | 73 1 | 0 | OH | world1 | 1 | 0 | OH | 76 1 | 1 | OH | world2 | 1 | 1 | OH | 77 1 | 0 | OH | world1 | 1 | 0 | OH | 80 1 | 1 | OH | world2 | 1 | 1 | OH | 81 1 | 0 | OH | world1 | 1 | 0 | OH | 84 1 | 1 | OH | world2 | 1 | 1 | OH | 85 1 | 0 | OH | world1 | 1 | 0 | OH | 88 1 | 1 | OH | world2 | 1 | 1 | OH | 89 1 | 0 | OH | world1 | 1 | 0 | OH | 92 1 | 1 | OH | world2 | 1 | 1 | OH | 93 1 | 0 | OH | world1 | 1 | 0 | OH | 96 1 | 1 | OH | world2 | 1 | 1 | OH | 97 1 | 0 | OH | world1 | 1 | 0 | OH | 100 1 | 1 | OH | world2 | 1 | 1 | CA | 10001 1 | 0 | OH | world1 | 1 | 0 | CA | 10004 1 | 1 | OH | world2 | 1 | 1 | CA | 10005 1 | 0 | OH | world1 | 1 | 0 | CA | 10008 1 | 1 | OH | world2 | 1 | 1 | CA | 10009 1 | 0 | OH | world1 | 1 | 0 | CA | 10012 1 | 1 | OH | world2 | 1 | 1 | CA | 10013 1 | 0 | OH | world1 | 1 | 0 | CA | 10016 1 | 1 | OH | world2 | 1 | 1 | CA | 10017 1 | 0 | OH | world1 | 1 | 0 | CA | 10020 1 | 1 | OH | world2 | 1 | 1 | CA | 10021 1 | 0 | OH | world1 | 1 | 0 | CA | 10024 1 | 1 | OH | world2 | 1 | 1 | CA | 10025 1 | 0 | OH | world1 | 1 | 0 | CA | 10028 1 | 1 | OH | world2 | 1 | 1 | CA | 10029 1 | 0 | OH | world1 | 1 | 0 | CA | 10032 1 | 1 | OH | world2 | 1 | 1 | CA | 10033 1 | 0 | OH | world1 | 1 | 0 | CA | 10036 1 | 1 | OH | world2 | 1 | 1 | CA | 10037 1 | 0 | OH | world1 | 1 | 0 | CA | 10040 1 | 1 | OH | world2 | 1 | 1 | CA | 10041 1 | 0 | OH | world1 | 1 | 0 | CA | 10044 1 | 1 | OH | world2 | 1 | 1 | CA | 10045 1 | 0 | OH | world1 | 1 | 0 | CA | 10048 1 | 1 | OH | world2 | 1 | 1 | CA | 10049 1 | 0 | OH | world1 | 1 | 0 | CA | 10052 1 | 1 | OH | world2 | 1 | 1 | CA | 10053 1 | 0 | OH | world1 | 1 | 0 | CA | 10056 1 | 1 | OH | world2 | 1 | 1 | CA | 10057 1 | 0 | OH | world1 | 1 | 0 | CA | 10060 1 | 1 | OH | world2 | 1 | 1 | CA | 10061 1 | 0 | OH | world1 | 1 | 0 | CA | 10064 1 | 1 | OH | world2 | 1 | 1 | CA | 10065 1 | 0 | OH | world1 | 1 | 0 | CA | 10068 1 | 1 | OH | world2 | 1 | 1 | CA | 10069 1 | 0 | OH | world1 | 1 | 0 | CA | 10072 1 | 1 | OH | world2 | 1 | 1 | CA | 10073 1 | 0 | OH | world1 | 1 | 0 | CA | 10076 1 | 1 | OH | world2 | 1 | 1 | CA | 10077 1 | 0 | OH | world1 | 1 | 0 | CA | 10080 1 | 1 | OH | world2 | 1 | 1 | CA | 10081 1 | 0 | OH | world1 | 1 | 0 | CA | 10084 1 | 1 | OH | world2 | 1 | 1 | CA | 10085 1 | 0 | OH | world1 | 1 | 0 | CA | 10088 1 | 1 | OH | world2 | 1 | 1 | CA | 10089 1 | 0 | OH | world1 | 1 | 0 | CA | 10092 1 | 1 | OH | world2 | 1 | 1 | CA | 10093 1 | 0 | OH | world1 | 1 | 0 | CA | 10096 1 | 1 | OH | world2 | 1 | 1 | CA | 10097 1 | 0 | OH | world1 | 1 | 0 | CA | 10100 (100 rows) -- -- Join on one of the partitioning columns and static elimination on other -- set gp_dynamic_partition_pruning=off; explain select * from dim1 inner join fact1 on (dim1.pid=fact1.pid) and fact1.code = 'OH' order by fact1.u; QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Gather Motion 2:1 (slice2; segments: 2) (cost=27.24..27.25 rows=2 width=62) Merge Key: "?column9?" -> Sort (cost=27.24..27.25 rows=2 width=62) Sort Key: dpe_multi.fact1.u -> Hash Join (cost=1.06..27.21 rows=2 width=62) Hash Cond: dpe_multi.fact1.pid = dim1.pid -> Append (cost=0.00..26.10 rows=2 width=44) -> Seq Scan on fact1_1_prt_2_2_prt_oh fact1 (cost=0.00..6.53 rows=1 width=44) Filter: code = 'OH'::text -> Seq Scan on fact1_1_prt_1_2_prt_oh fact1 (cost=0.00..6.53 rows=1 width=44) Filter: code = 'OH'::text -> Seq Scan on fact1_1_prt_3_2_prt_oh fact1 (cost=0.00..6.53 rows=1 width=44) Filter: code = 'OH'::text -> Seq Scan on fact1_1_prt_4_2_prt_oh fact1 (cost=0.00..6.53 rows=1 width=44) Filter: code = 'OH'::text -> Hash (cost=1.04..1.04 rows=1 width=18) -> Broadcast Motion 2:2 (slice1; segments: 2) (cost=0.00..1.04 rows=1 width=18) -> Seq Scan on dim1 (cost=0.00..1.01 rows=1 width=18) Settings: enable_bitmapscan=off; enable_hashjoin=on; enable_indexscan=off; enable_mergejoin=off; enable_nestloop=off; enable_seqscan=on; gp_dynamic_partition_pruning=off; gp_segments_for_planner=2; optimizer=on (19 rows) select * from dim1 inner join fact1 on (dim1.pid=fact1.pid) and fact1.code = 'OH' order by fact1.u; dist | pid | code | t1 | dist | pid | code | u ------+-----+------+--------+------+-----+------+----- 1 | 1 | OH | world2 | 1 | 1 | OH | 1 1 | 0 | OH | world1 | 1 | 0 | OH | 4 1 | 1 | OH | world2 | 1 | 1 | OH | 5 1 | 0 | OH | world1 | 1 | 0 | OH | 8 1 | 1 | OH | world2 | 1 | 1 | OH | 9 1 | 0 | OH | world1 | 1 | 0 | OH | 12 1 | 1 | OH | world2 | 1 | 1 | OH | 13 1 | 0 | OH | world1 | 1 | 0 | OH | 16 1 | 1 | OH | world2 | 1 | 1 | OH | 17 1 | 0 | OH | world1 | 1 | 0 | OH | 20 1 | 1 | OH | world2 | 1 | 1 | OH | 21 1 | 0 | OH | world1 | 1 | 0 | OH | 24 1 | 1 | OH | world2 | 1 | 1 | OH | 25 1 | 0 | OH | world1 | 1 | 0 | OH | 28 1 | 1 | OH | world2 | 1 | 1 | OH | 29 1 | 0 | OH | world1 | 1 | 0 | OH | 32 1 | 1 | OH | world2 | 1 | 1 | OH | 33 1 | 0 | OH | world1 | 1 | 0 | OH | 36 1 | 1 | OH | world2 | 1 | 1 | OH | 37 1 | 0 | OH | world1 | 1 | 0 | OH | 40 1 | 1 | OH | world2 | 1 | 1 | OH | 41 1 | 0 | OH | world1 | 1 | 0 | OH | 44 1 | 1 | OH | world2 | 1 | 1 | OH | 45 1 | 0 | OH | world1 | 1 | 0 | OH | 48 1 | 1 | OH | world2 | 1 | 1 | OH | 49 1 | 0 | OH | world1 | 1 | 0 | OH | 52 1 | 1 | OH | world2 | 1 | 1 | OH | 53 1 | 0 | OH | world1 | 1 | 0 | OH | 56 1 | 1 | OH | world2 | 1 | 1 | OH | 57 1 | 0 | OH | world1 | 1 | 0 | OH | 60 1 | 1 | OH | world2 | 1 | 1 | OH | 61 1 | 0 | OH | world1 | 1 | 0 | OH | 64 1 | 1 | OH | world2 | 1 | 1 | OH | 65 1 | 0 | OH | world1 | 1 | 0 | OH | 68 1 | 1 | OH | world2 | 1 | 1 | OH | 69 1 | 0 | OH | world1 | 1 | 0 | OH | 72 1 | 1 | OH | world2 | 1 | 1 | OH | 73 1 | 0 | OH | world1 | 1 | 0 | OH | 76 1 | 1 | OH | world2 | 1 | 1 | OH | 77 1 | 0 | OH | world1 | 1 | 0 | OH | 80 1 | 1 | OH | world2 | 1 | 1 | OH | 81 1 | 0 | OH | world1 | 1 | 0 | OH | 84 1 | 1 | OH | world2 | 1 | 1 | OH | 85 1 | 0 | OH | world1 | 1 | 0 | OH | 88 1 | 1 | OH | world2 | 1 | 1 | OH | 89 1 | 0 | OH | world1 | 1 | 0 | OH | 92 1 | 1 | OH | world2 | 1 | 1 | OH | 93 1 | 0 | OH | world1 | 1 | 0 | OH | 96 1 | 1 | OH | world2 | 1 | 1 | OH | 97 1 | 0 | OH | world1 | 1 | 0 | OH | 100 (50 rows) set gp_dynamic_partition_pruning=on; explain select * from dim1 inner join fact1 on (dim1.pid=fact1.pid) and fact1.code = 'OH' order by fact1.u; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Gather Motion 2:1 (slice3; segments: 2) (cost=28.26..28.27 rows=2 width=62) Merge Key: "?column9?" -> Sort (cost=28.26..28.27 rows=2 width=62) Sort Key: dpe_multi.fact1.u InitPlan (slice4) -> Aggregate (cost=1.01..1.02 rows=1 width=32) -> Gather Motion 2:1 (slice2; segments: 2) (cost=0.00..1.01 rows=1 width=18) -> Seq Scan on dim1 (cost=0.00..1.01 rows=1 width=18) -> Hash Join (cost=1.06..27.21 rows=2 width=62) Hash Cond: dpe_multi.fact1.pid = dpe_multi.dim1.pid -> Append (cost=0.00..26.10 rows=2 width=44) -> Result (cost=0.00..6.53 rows=1 width=44) One-Time Filter: 446016::oid = ANY ($0) -> Seq Scan on fact1_1_prt_2_2_prt_oh fact1 (cost=0.00..6.53 rows=1 width=44) Filter: code = 'OH'::text -> Result (cost=0.00..6.53 rows=1 width=44) One-Time Filter: 445909::oid = ANY ($0) -> Seq Scan on fact1_1_prt_1_2_prt_oh fact1 (cost=0.00..6.53 rows=1 width=44) Filter: code = 'OH'::text -> Result (cost=0.00..6.53 rows=1 width=44) One-Time Filter: 446123::oid = ANY ($0) -> Seq Scan on fact1_1_prt_3_2_prt_oh fact1 (cost=0.00..6.53 rows=1 width=44) Filter: code = 'OH'::text -> Result (cost=0.00..6.53 rows=1 width=44) One-Time Filter: 446230::oid = ANY ($0) -> Seq Scan on fact1_1_prt_4_2_prt_oh fact1 (cost=0.00..6.53 rows=1 width=44) Filter: code = 'OH'::text -> Hash (cost=1.04..1.04 rows=1 width=18) -> Broadcast Motion 2:2 (slice1; segments: 2) (cost=0.00..1.04 rows=1 width=18) -> Seq Scan on dim1 (cost=0.00..1.01 rows=1 width=18) Settings: enable_bitmapscan=off; enable_hashjoin=on; enable_indexscan=off; enable_mergejoin=off; enable_nestloop=off; enable_seqscan=on; gp_dynamic_partition_pruning=on; gp_segments_for_planner=2; optimizer=on (31 rows) select * from dim1 inner join fact1 on (dim1.pid=fact1.pid) and fact1.code = 'OH' order by fact1.u; dist | pid | code | t1 | dist | pid | code | u ------+-----+------+--------+------+-----+------+----- 1 | 1 | OH | world2 | 1 | 1 | OH | 1 1 | 0 | OH | world1 | 1 | 0 | OH | 4 1 | 1 | OH | world2 | 1 | 1 | OH | 5 1 | 0 | OH | world1 | 1 | 0 | OH | 8 1 | 1 | OH | world2 | 1 | 1 | OH | 9 1 | 0 | OH | world1 | 1 | 0 | OH | 12 1 | 1 | OH | world2 | 1 | 1 | OH | 13 1 | 0 | OH | world1 | 1 | 0 | OH | 16 1 | 1 | OH | world2 | 1 | 1 | OH | 17 1 | 0 | OH | world1 | 1 | 0 | OH | 20 1 | 1 | OH | world2 | 1 | 1 | OH | 21 1 | 0 | OH | world1 | 1 | 0 | OH | 24 1 | 1 | OH | world2 | 1 | 1 | OH | 25 1 | 0 | OH | world1 | 1 | 0 | OH | 28 1 | 1 | OH | world2 | 1 | 1 | OH | 29 1 | 0 | OH | world1 | 1 | 0 | OH | 32 1 | 1 | OH | world2 | 1 | 1 | OH | 33 1 | 0 | OH | world1 | 1 | 0 | OH | 36 1 | 1 | OH | world2 | 1 | 1 | OH | 37 1 | 0 | OH | world1 | 1 | 0 | OH | 40 1 | 1 | OH | world2 | 1 | 1 | OH | 41 1 | 0 | OH | world1 | 1 | 0 | OH | 44 1 | 1 | OH | world2 | 1 | 1 | OH | 45 1 | 0 | OH | world1 | 1 | 0 | OH | 48 1 | 1 | OH | world2 | 1 | 1 | OH | 49 1 | 0 | OH | world1 | 1 | 0 | OH | 52 1 | 1 | OH | world2 | 1 | 1 | OH | 53 1 | 0 | OH | world1 | 1 | 0 | OH | 56 1 | 1 | OH | world2 | 1 | 1 | OH | 57 1 | 0 | OH | world1 | 1 | 0 | OH | 60 1 | 1 | OH | world2 | 1 | 1 | OH | 61 1 | 0 | OH | world1 | 1 | 0 | OH | 64 1 | 1 | OH | world2 | 1 | 1 | OH | 65 1 | 0 | OH | world1 | 1 | 0 | OH | 68 1 | 1 | OH | world2 | 1 | 1 | OH | 69 1 | 0 | OH | world1 | 1 | 0 | OH | 72 1 | 1 | OH | world2 | 1 | 1 | OH | 73 1 | 0 | OH | world1 | 1 | 0 | OH | 76 1 | 1 | OH | world2 | 1 | 1 | OH | 77 1 | 0 | OH | world1 | 1 | 0 | OH | 80 1 | 1 | OH | world2 | 1 | 1 | OH | 81 1 | 0 | OH | world1 | 1 | 0 | OH | 84 1 | 1 | OH | world2 | 1 | 1 | OH | 85 1 | 0 | OH | world1 | 1 | 0 | OH | 88 1 | 1 | OH | world2 | 1 | 1 | OH | 89 1 | 0 | OH | world1 | 1 | 0 | OH | 92 1 | 1 | OH | world2 | 1 | 1 | OH | 93 1 | 0 | OH | world1 | 1 | 0 | OH | 96 1 | 1 | OH | world2 | 1 | 1 | OH | 97 1 | 0 | OH | world1 | 1 | 0 | OH | 100 (50 rows) -- -- add aggregates -- set gp_dynamic_partition_pruning=off; explain select fact1.code, count(*) from dim1 inner join fact1 on (dim1.pid=fact1.pid) group by 1 order by 1; QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Gather Motion 2:1 (slice3; segments: 2) (cost=54.26..54.59 rows=6 width=40) Merge Key: code -> GroupAggregate (cost=54.26..54.59 rows=6 width=40) Group By: dpe_multi.fact1.code -> Sort (cost=54.26..54.28 rows=6 width=40) Sort Key: dpe_multi.fact1.code -> Redistribute Motion 2:2 (slice2; segments: 2) (cost=53.62..54.07 rows=6 width=40) Hash Key: dpe_multi.fact1.code -> GroupAggregate (cost=53.62..53.85 rows=6 width=40) Group By: dpe_multi.fact1.code -> Sort (cost=53.62..53.65 rows=6 width=32) Sort Key: dpe_multi.fact1.code -> Hash Join (cost=1.06..53.41 rows=6 width=32) Hash Cond: dpe_multi.fact1.pid = dim1.pid -> Append (cost=0.00..43.36 rows=1770 width=36) -> Seq Scan on fact1_1_prt_2_2_prt_oh fact1 (cost=0.00..5.42 rows=221 width=36) -> Seq Scan on fact1_1_prt_2_2_prt_wa fact1 (cost=0.00..0.00 rows=1 width=36) -> Seq Scan on fact1_1_prt_2_2_prt_ca fact1 (cost=0.00..5.42 rows=221 width=36) -> Seq Scan on fact1_1_prt_1_2_prt_ca fact1 (cost=0.00..5.42 rows=221 width=36) -> Seq Scan on fact1_1_prt_1_2_prt_oh fact1 (cost=0.00..5.42 rows=221 width=36) -> Seq Scan on fact1_1_prt_1_2_prt_wa fact1 (cost=0.00..0.00 rows=1 width=36) -> Seq Scan on fact1_1_prt_3_2_prt_oh fact1 (cost=0.00..5.42 rows=221 width=36) -> Seq Scan on fact1_1_prt_3_2_prt_wa fact1 (cost=0.00..0.00 rows=1 width=36) -> Seq Scan on fact1_1_prt_3_2_prt_ca fact1 (cost=0.00..5.42 rows=221 width=36) -> Seq Scan on fact1_1_prt_4_2_prt_ca fact1 (cost=0.00..5.42 rows=221 width=36) -> Seq Scan on fact1_1_prt_4_2_prt_oh fact1 (cost=0.00..5.42 rows=221 width=36) -> Seq Scan on fact1_1_prt_4_2_prt_wa fact1 (cost=0.00..0.00 rows=1 width=36) -> Hash (cost=1.04..1.04 rows=1 width=4) -> Broadcast Motion 2:2 (slice1; segments: 2) (cost=0.00..1.04 rows=1 width=4) -> Seq Scan on dim1 (cost=0.00..1.01 rows=1 width=4) Settings: enable_bitmapscan=off; enable_hashjoin=on; enable_indexscan=off; enable_mergejoin=off; enable_nestloop=off; enable_seqscan=on; gp_dynamic_partition_pruning=off; gp_segments_for_planner=2; optimizer=on (31 rows) select fact1.code, count(*) from dim1 inner join fact1 on (dim1.pid=fact1.pid) group by 1 order by 1; code | count ------+------- CA | 50 OH | 50 (2 rows) set gp_dynamic_partition_pruning=on; explain select fact1.code, count(*) from dim1 inner join fact1 on (dim1.pid=fact1.pid) group by 1 order by 1; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Gather Motion 2:1 (slice4; segments: 2) (cost=55.28..55.61 rows=6 width=40) Merge Key: code -> GroupAggregate (cost=55.28..55.61 rows=6 width=40) Group By: dpe_multi.fact1.code InitPlan (slice5) -> Aggregate (cost=1.01..1.02 rows=1 width=32) -> Gather Motion 2:1 (slice3; segments: 2) (cost=0.00..1.01 rows=1 width=4) -> Seq Scan on dim1 (cost=0.00..1.01 rows=1 width=4) -> Sort (cost=54.26..54.28 rows=6 width=40) Sort Key: dpe_multi.fact1.code -> Redistribute Motion 2:2 (slice2; segments: 2) (cost=53.62..54.07 rows=6 width=40) Hash Key: dpe_multi.fact1.code -> GroupAggregate (cost=53.62..53.85 rows=6 width=40) Group By: dpe_multi.fact1.code -> Sort (cost=53.62..53.65 rows=6 width=32) Sort Key: dpe_multi.fact1.code -> Hash Join (cost=1.06..53.41 rows=6 width=32) Hash Cond: dpe_multi.fact1.pid = dpe_multi.dim1.pid -> Append (cost=0.00..43.36 rows=1770 width=36) -> Result (cost=0.00..5.42 rows=221 width=36) One-Time Filter: 446016::oid = ANY ($0) -> Seq Scan on fact1_1_prt_2_2_prt_oh fact1 (cost=0.00..5.42 rows=221 width=36) -> Result (cost=0.00..0.00 rows=1 width=36) One-Time Filter: 446043::oid = ANY ($0) -> Seq Scan on fact1_1_prt_2_2_prt_wa fact1 (cost=0.00..0.00 rows=1 width=36) -> Result (cost=0.00..5.42 rows=221 width=36) One-Time Filter: 445989::oid = ANY ($0) -> Seq Scan on fact1_1_prt_2_2_prt_ca fact1 (cost=0.00..5.42 rows=221 width=36) -> Result (cost=0.00..5.42 rows=221 width=36) One-Time Filter: 445882::oid = ANY ($0) -> Seq Scan on fact1_1_prt_1_2_prt_ca fact1 (cost=0.00..5.42 rows=221 width=36) -> Result (cost=0.00..5.42 rows=221 width=36) One-Time Filter: 445909::oid = ANY ($0) -> Seq Scan on fact1_1_prt_1_2_prt_oh fact1 (cost=0.00..5.42 rows=221 width=36) -> Result (cost=0.00..0.00 rows=1 width=36) One-Time Filter: 445936::oid = ANY ($0) -> Seq Scan on fact1_1_prt_1_2_prt_wa fact1 (cost=0.00..0.00 rows=1 width=36) -> Result (cost=0.00..5.42 rows=221 width=36) One-Time Filter: 446123::oid = ANY ($0) -> Seq Scan on fact1_1_prt_3_2_prt_oh fact1 (cost=0.00..5.42 rows=221 width=36) -> Result (cost=0.00..0.00 rows=1 width=36) One-Time Filter: 446150::oid = ANY ($0) -> Seq Scan on fact1_1_prt_3_2_prt_wa fact1 (cost=0.00..0.00 rows=1 width=36) -> Result (cost=0.00..5.42 rows=221 width=36) One-Time Filter: 446096::oid = ANY ($0) -> Seq Scan on fact1_1_prt_3_2_prt_ca fact1 (cost=0.00..5.42 rows=221 width=36) -> Result (cost=0.00..5.42 rows=221 width=36) One-Time Filter: 446203::oid = ANY ($0) -> Seq Scan on fact1_1_prt_4_2_prt_ca fact1 (cost=0.00..5.42 rows=221 width=36) -> Result (cost=0.00..5.42 rows=221 width=36) One-Time Filter: 446230::oid = ANY ($0) -> Seq Scan on fact1_1_prt_4_2_prt_oh fact1 (cost=0.00..5.42 rows=221 width=36) -> Result (cost=0.00..0.00 rows=1 width=36) One-Time Filter: 446257::oid = ANY ($0) -> Seq Scan on fact1_1_prt_4_2_prt_wa fact1 (cost=0.00..0.00 rows=1 width=36) -> Hash (cost=1.04..1.04 rows=1 width=4) -> Broadcast Motion 2:2 (slice1; segments: 2) (cost=0.00..1.04 rows=1 width=4) -> Seq Scan on dim1 (cost=0.00..1.01 rows=1 width=4) Settings: enable_bitmapscan=off; enable_hashjoin=on; enable_indexscan=off; enable_mergejoin=off; enable_nestloop=off; enable_seqscan=on; gp_dynamic_partition_pruning=on; gp_segments_for_planner=2; optimizer=on (59 rows) select fact1.code, count(*) from dim1 inner join fact1 on (dim1.pid=fact1.pid) group by 1 order by 1; code | count ------+------- CA | 50 OH | 50 (2 rows) -- -- multi-attribute list partitioning -- drop schema if exists dpe_malp cascade; NOTICE: schema "dpe_malp" does not exist, skipping create schema dpe_malp; set search_path='dpe_malp'; set gp_segments_for_planner=2; set optimizer_segments=2; create table malp (i int, j int, t text) distributed by (i) partition by list (i, j) ( partition p1 values((1,10)) , partition p2 values((2,20)), partition p3 values((3,30)) ); NOTICE: CREATE TABLE will create partition "malp_1_prt_p1" for table "malp" NOTICE: CREATE TABLE will create partition "malp_1_prt_p2" for table "malp" NOTICE: CREATE TABLE will create partition "malp_1_prt_p3" for table "malp" insert into malp select 1, 10, 'hello1'; insert into malp select 1, 10, 'hello2'; insert into malp select 1, 10, 'hello3'; insert into malp select 2, 20, 'hello4'; insert into malp select 2, 20, 'hello5'; insert into malp select 3, 30, 'hello6'; create table dim(i int, j int) distributed randomly; insert into dim values(1, 10); analyze malp; analyze dim; explain select * from dim inner join malp on (dim.i = malp.i); QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Gather Motion 2:1 (slice3; segments: 2) (cost=2.07..5.22 rows=3 width=23) -> Hash Join (cost=2.07..5.22 rows=3 width=23) Hash Cond: dpe_malp.malp.i = dpe_malp.dim.i InitPlan (slice4) -> Aggregate (cost=1.01..1.02 rows=1 width=32) -> Gather Motion 2:1 (slice2; segments: 2) (cost=0.00..1.01 rows=1 width=8) -> Seq Scan on dim (cost=0.00..1.01 rows=1 width=8) -> Append (cost=0.00..3.06 rows=3 width=15) -> Result (cost=0.00..1.03 rows=2 width=15) One-Time Filter: 446508::oid = ANY ($0) -> Seq Scan on malp_1_prt_p1 malp (cost=0.00..1.03 rows=2 width=15) -> Result (cost=0.00..1.02 rows=1 width=15) One-Time Filter: 446534::oid = ANY ($0) -> Seq Scan on malp_1_prt_p2 malp (cost=0.00..1.02 rows=1 width=15) -> Result (cost=0.00..1.01 rows=1 width=15) One-Time Filter: 446560::oid = ANY ($0) -> Seq Scan on malp_1_prt_p3 malp (cost=0.00..1.01 rows=1 width=15) -> Hash (cost=1.03..1.03 rows=1 width=8) -> Redistribute Motion 2:2 (slice1; segments: 2) (cost=0.00..1.03 rows=1 width=8) Hash Key: dpe_malp.dim.i -> Seq Scan on dim (cost=0.00..1.01 rows=1 width=8) Settings: enable_bitmapscan=off; enable_hashjoin=on; enable_indexscan=off; enable_mergejoin=off; enable_nestloop=off; enable_seqscan=on; gp_dynamic_partition_pruning=on; gp_segments_for_planner=2; optimizer=on (22 rows) set gp_dynamic_partition_pruning = off; select * from dim inner join malp on (dim.i = malp.i); i | j | i | j | t ---+----+---+----+-------- 1 | 10 | 1 | 10 | hello1 1 | 10 | 1 | 10 | hello2 1 | 10 | 1 | 10 | hello3 (3 rows) set gp_dynamic_partition_pruning = on; select * from dim inner join malp on (dim.i = malp.i); i | j | i | j | t ---+----+---+----+-------- 1 | 10 | 1 | 10 | hello1 1 | 10 | 1 | 10 | hello2 1 | 10 | 1 | 10 | hello3 (3 rows) set gp_dynamic_partition_pruning = on; select * from dim inner join malp on (dim.i = malp.i and dim.j = malp.j); -- only one partition should be chosen i | j | i | j | t ---+----+---+----+-------- 1 | 10 | 1 | 10 | hello1 1 | 10 | 1 | 10 | hello2 1 | 10 | 1 | 10 | hello3 (3 rows) -- -- bugs -- drop schema if exists dpe_bugs cascade; NOTICE: schema "dpe_bugs" does not exist, skipping create schema dpe_bugs; set search_path='dpe_bugs'; set gp_segments_for_planner=2; set optimizer_segments=2; create table pat(a int, b date) partition by range (b) (start ('2010-01-01') end ('2010-01-05') every (1), default partition other); NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Greenplum Database data distribution key for this table. HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. NOTICE: CREATE TABLE will create partition "pat_1_prt_other" for table "pat" NOTICE: CREATE TABLE will create partition "pat_1_prt_2" for table "pat" NOTICE: CREATE TABLE will create partition "pat_1_prt_3" for table "pat" NOTICE: CREATE TABLE will create partition "pat_1_prt_4" for table "pat" NOTICE: CREATE TABLE will create partition "pat_1_prt_5" for table "pat" insert into pat select i,date '2010-01-01' + i from generate_series(1, 10)i; create table jpat(a int, b date); NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Greenplum Database data distribution key for this table. HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. insert into jpat values(1, '2010-01-02'); explain select * from (select count(*) over (order by a rows between 1 preceding and 1 following), a, b from jpat)jpat inner join pat using(b); QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Hash Join (cost=0.00..6.16 rows=1 width=28) Hash Cond: jpat.b = pat.b -> Window (cost=0.00..3.02 rows=1 width=20) Order By: jpat.a -> Sort (cost=0.00..2.01 rows=1 width=8) Sort Key: jpat.a -> Gather Motion 2:1 (slice1; segments: 2) (cost=0.00..1.01 rows=1 width=8) -> Table Scan on jpat (cost=0.00..0.00 rows=1 width=8) -> Hash (cost=2.03..2.03 rows=1 width=16) -> Gather Motion 2:1 (slice2; segments: 2) (cost=0.00..2.03 rows=1 width=16) -> Sequence (cost=0.00..1.02 rows=1 width=16) -> Result (cost=10.00..100.00 rows=50 width=4) -> Function Scan on gp_partition_expansion (cost=10.00..100.00 rows=50 width=4) -> Dynamic Table Scan on pat (partIndex: 0) (cost=0.00..0.01 rows=1 width=16) Settings: enable_bitmapscan=off; enable_hashjoin=on; enable_indexscan=off; enable_mergejoin=off; enable_nestloop=off; enable_seqscan=on; gp_dynamic_partition_pruning=on; gp_segments_for_planner=2; optimizer=on (15 rows) select * from (select count(*) over (order by a rows between 1 preceding and 1 following), a, b from jpat)jpat inner join pat using(b); b | count | a | a ------------+-------+---+--- 01-02-2010 | 1 | 1 | 1 (1 row)