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 3:1 (slice2; segments: 3) (cost=0.00..485.01 rows=18 width=50) -> Nested Loop (cost=0.00..485.01 rows=6 width=50) Join Filter: true -> Broadcast Motion 3:3 (slice1; segments: 3) (cost=0.00..431.00 rows=2 width=19) -> Table Scan on t (cost=0.00..431.00 rows=1 width=19) -> Sequence (cost=0.00..54.00 rows=3 width=31) -> Partition Selector for pt (dynamic scan id: 1) (cost=10.00..100.00 rows=34 width=4) Partitions selected: 6 (out of 6) -> Dynamic Index Scan on pt (dynamic scan id: 1) (cost=0.00..54.00 rows=3 width=31) Index Cond: pt.ptid = t.tid Settings: gp_segments_for_planner=2; optimizer=on; optimizer_segments=2 Optimizer status: PQO version 2.5.0 (12 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 3:1 (slice2; segments: 3) (cost=0.00..560.62 rows=44 width=50) -> Nested Loop (cost=0.00..560.62 rows=15 width=50) Join Filter: true -> Broadcast Motion 3:3 (slice1; segments: 3) (cost=0.00..431.00 rows=2 width=19) -> Table Scan on t (cost=0.00..431.00 rows=1 width=19) -> Sequence (cost=0.00..129.61 rows=8 width=31) -> Partition Selector for pt (dynamic scan id: 1) (cost=10.00..100.00 rows=34 width=4) Partitions selected: 6 (out of 6) -> Dynamic Index Scan on pt (dynamic scan id: 1) (cost=0.00..129.61 rows=8 width=31) Index Cond: pt.ptid = (t.tid + 1) Settings: gp_segments_for_planner=2; optimizer=on; optimizer_segments=2 Optimizer status: PQO version 2.5.0 (12 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 3:1 (slice2; segments: 3) (cost=0.00..458.01 rows=9 width=50) -> Nested Loop (cost=0.00..458.00 rows=3 width=50) Join Filter: true -> Broadcast Motion 3:3 (slice1; segments: 3) (cost=0.00..431.00 rows=1 width=19) -> Table Scan on t (cost=0.00..431.00 rows=1 width=19) Filter: t1 = ('hello'::text || tid::text) -> Sequence (cost=0.00..27.00 rows=3 width=31) -> Partition Selector for pt (dynamic scan id: 1) (cost=10.00..100.00 rows=34 width=4) Partitions selected: 6 (out of 6) -> Dynamic Index Scan on pt (dynamic scan id: 1) (cost=0.00..27.00 rows=3 width=31) Index Cond: pt.ptid = t.tid Settings: gp_segments_for_planner=2; optimizer=on; optimizer_segments=2 Optimizer status: PQO version 2.5.0 (13 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 3:1 (slice2; segments: 3) (cost=0.00..437.00 rows=2 width=50) -> Nested Loop (cost=0.00..437.00 rows=1 width=50) Join Filter: true -> Broadcast Motion 3:3 (slice1; segments: 3) (cost=0.00..431.00 rows=2 width=19) -> Table Scan on t (cost=0.00..431.00 rows=1 width=19) -> Sequence (cost=0.00..6.00 rows=1 width=31) -> Partition Selector for pt (dynamic scan id: 1) (cost=10.00..100.00 rows=34 width=4) Partitions selected: 6 (out of 6) -> Dynamic Index Scan on pt (dynamic scan id: 1) (cost=0.00..6.00 rows=1 width=31) Index Cond: pt.pt1 = t.t1 Filter: t.tid = pt.ptid Settings: gp_segments_for_planner=2; optimizer=on; optimizer_segments=2 Optimizer status: PQO version 2.5.0 (13 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 3:1 (slice2; segments: 3) (cost=0.00..862.01 rows=9 width=31) -> Hash EXISTS Join (cost=0.00..862.01 rows=3 width=31) Hash Cond: pt.ptid = t.tid -> Dynamic Table Scan on pt (dynamic scan id: 1) (cost=0.00..431.00 rows=18 width=31) -> Hash (cost=100.00..100.00 rows=34 width=4) -> Partition Selector for pt (dynamic scan id: 1) (cost=10.00..100.00 rows=34 width=4) Filter: pt.ptid = t.tid -> Broadcast Motion 3:3 (slice1; segments: 3) (cost=0.00..431.00 rows=1 width=4) -> Table Scan on t (cost=0.00..431.00 rows=1 width=4) Filter: t1 = ('hello'::text || tid::text) Settings: gp_segments_for_planner=2; optimizer=on; optimizer_segments=2 Optimizer status: PQO version 2.5.0 (12 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 3:1 (slice2; segments: 3) (cost=0.00..862.01 rows=9 width=31) -> Hash EXISTS Join (cost=0.00..862.01 rows=3 width=31) Hash Cond: pt.ptid = t.tid -> Dynamic Table Scan on pt (dynamic scan id: 1) (cost=0.00..431.00 rows=18 width=31) -> Hash (cost=100.00..100.00 rows=34 width=4) -> Partition Selector for pt (dynamic scan id: 1) (cost=10.00..100.00 rows=34 width=4) Filter: pt.ptid = t.tid -> Broadcast Motion 3:3 (slice1; segments: 3) (cost=0.00..431.00 rows=1 width=4) -> Result (cost=0.00..431.00 rows=1 width=4) -> Result (cost=0.00..431.00 rows=1 width=4) -> Table Scan on t (cost=0.00..431.00 rows=1 width=4) Filter: t1 = ('hello'::text || tid::text) Settings: gp_segments_for_planner=2; optimizer=on; optimizer_segments=2 Optimizer status: PQO version 2.5.0 (14 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..485.00 rows=1 width=8) -> Gather Motion 3:1 (slice2; segments: 3) (cost=0.00..485.00 rows=1 width=8) -> Aggregate (cost=0.00..485.00 rows=1 width=8) -> Nested Loop (cost=0.00..485.00 rows=6 width=1) Join Filter: true -> Broadcast Motion 3:3 (slice1; segments: 3) (cost=0.00..431.00 rows=2 width=4) -> Table Scan on t (cost=0.00..431.00 rows=1 width=4) -> Sequence (cost=0.00..54.00 rows=3 width=1) -> Partition Selector for pt (dynamic scan id: 1) (cost=10.00..100.00 rows=34 width=4) Partitions selected: 6 (out of 6) -> Dynamic Index Scan on pt (dynamic scan id: 1) (cost=0.00..54.00 rows=3 width=1) Index Cond: pt.ptid = t.tid Settings: gp_segments_for_planner=2; optimizer=on; optimizer_segments=2 Optimizer status: PQO version 2.5.0 (14 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..485.02 rows=6 width=64) Order By: pt.ptid, pt.pt1 -> Gather Motion 3:1 (slice2; segments: 3) (cost=0.00..485.02 rows=18 width=50) Merge Key: pt.ptid, pt.pt1 -> Sort (cost=0.00..485.02 rows=6 width=50) Sort Key: pt.ptid, pt.pt1 -> Nested Loop (cost=0.00..485.01 rows=6 width=50) Join Filter: true -> Broadcast Motion 3:3 (slice1; segments: 3) (cost=0.00..431.00 rows=2 width=19) -> Table Scan on t (cost=0.00..431.00 rows=1 width=19) -> Sequence (cost=0.00..54.00 rows=3 width=31) -> Partition Selector for pt (dynamic scan id: 1) (cost=10.00..100.00 rows=34 width=4) Partitions selected: 6 (out of 6) -> Dynamic Index Scan on pt (dynamic scan id: 1) (cost=0.00..54.00 rows=3 width=31) Index Cond: pt.ptid = t.tid Settings: gp_segments_for_planner=2; optimizer=on; optimizer_segments=2 Optimizer status: PQO version 2.5.0 (17 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 3:1 (slice3; segments: 3) (cost=0.00..1045.64 rows=62 width=50) -> Append (cost=0.00..1045.62 rows=21 width=50) -> Nested Loop (cost=0.00..485.01 rows=6 width=50) Join Filter: true -> Broadcast Motion 3:3 (slice1; segments: 3) (cost=0.00..431.00 rows=2 width=19) -> Table Scan on t (cost=0.00..431.00 rows=1 width=19) -> Sequence (cost=0.00..54.00 rows=3 width=31) -> Partition Selector for pt (dynamic scan id: 1) (cost=10.00..100.00 rows=34 width=4) Partitions selected: 6 (out of 6) -> Dynamic Index Scan on pt (dynamic scan id: 1) (cost=0.00..54.00 rows=3 width=31) Index Cond: dpe_single.pt.ptid = dpe_single.t.tid -> Nested Loop (cost=0.00..560.62 rows=15 width=50) Join Filter: true -> Broadcast Motion 3:3 (slice2; segments: 3) (cost=0.00..431.00 rows=2 width=19) -> Table Scan on t (cost=0.00..431.00 rows=1 width=19) -> Sequence (cost=0.00..129.61 rows=8 width=31) -> Partition Selector for pt (dynamic scan id: 2) (cost=10.00..100.00 rows=34 width=4) Partitions selected: 6 (out of 6) -> Dynamic Index Scan on pt (dynamic scan id: 2) (cost=0.00..129.61 rows=8 width=31) Index Cond: dpe_single.pt.ptid = (dpe_single.t.tid + 2) Settings: gp_segments_for_planner=2; optimizer=on; optimizer_segments=2 Optimizer status: PQO version 2.5.0 (22 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..1045.62 rows=1 width=8) -> Gather Motion 3:1 (slice3; segments: 3) (cost=0.00..1045.62 rows=1 width=8) -> Aggregate (cost=0.00..1045.62 rows=1 width=8) -> Append (cost=0.00..1045.62 rows=21 width=1) -> Nested Loop (cost=0.00..485.01 rows=6 width=50) Join Filter: true -> Broadcast Motion 3:3 (slice1; segments: 3) (cost=0.00..431.00 rows=2 width=19) -> Table Scan on t (cost=0.00..431.00 rows=1 width=19) -> Sequence (cost=0.00..54.00 rows=3 width=31) -> Partition Selector for pt (dynamic scan id: 1) (cost=10.00..100.00 rows=34 width=4) Partitions selected: 6 (out of 6) -> Dynamic Index Scan on pt (dynamic scan id: 1) (cost=0.00..54.00 rows=3 width=31) Index Cond: dpe_single.pt.ptid = dpe_single.t.tid -> Nested Loop (cost=0.00..560.62 rows=15 width=50) Join Filter: true -> Broadcast Motion 3:3 (slice2; segments: 3) (cost=0.00..431.00 rows=2 width=19) -> Table Scan on t (cost=0.00..431.00 rows=1 width=19) -> Sequence (cost=0.00..129.61 rows=8 width=31) -> Partition Selector for pt (dynamic scan id: 2) (cost=10.00..100.00 rows=34 width=4) Partitions selected: 6 (out of 6) -> Dynamic Index Scan on pt (dynamic scan id: 2) (cost=0.00..129.61 rows=8 width=31) Index Cond: dpe_single.pt.ptid = (dpe_single.t.tid + 2) Settings: gp_segments_for_planner=2; optimizer=on; optimizer_segments=2 Optimizer status: PQO version 2.5.0 (24 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 3:1 (slice2; segments: 3) (cost=0.00..485.01 rows=18 width=50) -> Nested Loop (cost=0.00..485.01 rows=6 width=50) Join Filter: true -> Broadcast Motion 3:3 (slice1; segments: 3) (cost=0.00..431.00 rows=2 width=19) -> Table Scan on t (cost=0.00..431.00 rows=1 width=19) -> Sequence (cost=0.00..54.00 rows=3 width=31) -> Partition Selector for pt (dynamic scan id: 1) (cost=10.00..100.00 rows=34 width=4) Partitions selected: 6 (out of 6) -> Dynamic Index Scan on pt (dynamic scan id: 1) (cost=0.00..54.00 rows=3 width=31) Index Cond: pt.ptid = t.tid Settings: enable_hashjoin=off; enable_mergejoin=off; enable_nestloop=on; gp_segments_for_planner=2; optimizer=on; optimizer_segments=2 Optimizer status: PQO version 2.5.0 (12 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 3:1 (slice2; segments: 3) (cost=0.00..434.00 rows=1 width=50) -> Nested Loop (cost=0.00..434.00 rows=1 width=50) Join Filter: true -> Broadcast Motion 3:3 (slice1; segments: 3) (cost=0.00..431.00 rows=2 width=19) -> Table Scan on t (cost=0.00..431.00 rows=1 width=19) -> Sequence (cost=0.00..3.00 rows=1 width=31) -> Partition Selector for pt (dynamic scan id: 1) (cost=10.00..100.00 rows=34 width=4) Partitions selected: 6 (out of 6) -> Dynamic Index Scan on pt (dynamic scan id: 1) (cost=0.00..3.00 rows=1 width=31) Index Cond: pt.pt1 = 'hello0'::text Filter: t.tid = pt.ptid 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; optimizer_segments=2 Optimizer status: PQO version 2.5.0 (13 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 3:1 (slice2; segments: 3) (cost=0.00..485.01 rows=18 width=50) -> Nested Loop (cost=0.00..485.01 rows=6 width=50) Join Filter: true -> Broadcast Motion 3:3 (slice1; segments: 3) (cost=0.00..431.00 rows=2 width=19) -> Table Scan on t (cost=0.00..431.00 rows=1 width=19) -> Sequence (cost=0.00..54.00 rows=3 width=31) -> Partition Selector for pt (dynamic scan id: 1) (cost=10.00..100.00 rows=34 width=4) Partitions selected: 6 (out of 6) -> Dynamic Index Scan on pt (dynamic scan id: 1) (cost=0.00..54.00 rows=3 width=31) Index Cond: pt.ptid = t.tid 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; optimizer_segments=2 Optimizer status: PQO version 2.5.0 (12 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 3:1 (slice2; segments: 3) (cost=0.00..437.00 rows=2 width=50) -> Nested Loop (cost=0.00..437.00 rows=1 width=50) Join Filter: true -> Broadcast Motion 3:3 (slice1; segments: 3) (cost=0.00..431.00 rows=2 width=19) -> Table Scan on t (cost=0.00..431.00 rows=1 width=19) -> Sequence (cost=0.00..6.00 rows=1 width=31) -> Partition Selector for pt (dynamic scan id: 1) (cost=10.00..100.00 rows=34 width=4) Partitions selected: 6 (out of 6) -> Dynamic Index Scan on pt (dynamic scan id: 1) (cost=0.00..6.00 rows=1 width=31) Index Cond: pt.pt1 = t.t1 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; optimizer_segments=2 Optimizer status: PQO version 2.5.0 (12 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 3:1 (slice2; segments: 3) (cost=0.00..539.02 rows=36 width=50) -> Nested Loop (cost=0.00..539.01 rows=12 width=50) Join Filter: true -> Broadcast Motion 3:3 (slice1; segments: 3) (cost=0.00..431.00 rows=2 width=19) -> Table Scan on t (cost=0.00..431.00 rows=1 width=19) -> Sequence (cost=0.00..108.01 rows=6 width=31) -> Partition Selector for pt (dynamic scan id: 1) (cost=10.00..100.00 rows=34 width=4) Partitions selected: 6 (out of 6) -> Dynamic Index Scan on pt (dynamic scan id: 1) (cost=0.00..108.01 rows=6 width=31) Index Cond: pt.ptid > t.tid 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; optimizer_segments=2 Optimizer status: PQO version 2.5.0 (12 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 --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Hash Join (cost=0.00..916.02 rows=12 width=69) Hash Cond: t1.t2 = t.t2 -> Gather Motion 3:1 (slice2; segments: 3) (cost=0.00..485.01 rows=18 width=50) -> Nested Loop (cost=0.00..485.01 rows=6 width=50) Join Filter: true -> Broadcast Motion 3:3 (slice1; segments: 3) (cost=0.00..431.00 rows=2 width=19) -> Table Scan on t1 (cost=0.00..431.00 rows=1 width=19) -> Sequence (cost=0.00..54.00 rows=3 width=31) -> Partition Selector for pt (dynamic scan id: 1) (cost=10.00..100.00 rows=34 width=4) Partitions selected: 6 (out of 6) -> Dynamic Index Scan on pt (dynamic scan id: 1) (cost=0.00..54.00 rows=3 width=31) Index Cond: pt.ptid = t1.tid -> Hash (cost=431.00..431.00 rows=1 width=19) -> Gather Motion 3:1 (slice3; segments: 3) (cost=0.00..431.00 rows=2 width=19) -> Table Scan on t (cost=0.00..431.00 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; optimizer_segments=2 Optimizer status: PQO version 2.5.0 (17 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 3:1 (slice2; segments: 3) (cost=0.00..485.01 rows=18 width=50) Rows out: 18 rows at destination with 2.244 ms to first row, 2.260 ms to end, start offset by 0.607 ms. -> Nested Loop (cost=0.00..485.01 rows=6 width=50) Join Filter: true Rows out: Avg 6.0 rows x 3 workers. Max 9 rows (seg0) with 0.604 ms to first row, 1.744 ms to end, start offset by 1.011 ms. -> Broadcast Motion 3:3 (slice1; segments: 3) (cost=0.00..431.00 rows=2 width=19) Rows out: Avg 2.0 rows x 3 workers at destination. Max 2 rows (seg0) with 0.338 ms to first row, 1.292 ms to end, start offset by 1.011 ms. -> Table Scan on t (cost=0.00..431.00 rows=1 width=19) Rows out: 2 rows (seg0) with 0.038 ms to first row, 0.041 ms to end, start offset by 1.206 ms. -> Sequence (cost=0.00..54.00 rows=3 width=31) Rows out: Avg 6.0 rows x 3 workers. Max 9 rows (seg0) with 0.260 ms to first row, 0.428 ms to end of 2 scans, start offset by 1.642 ms. -> Partition Selector for pt (dynamic scan id: 1) (cost=10.00..100.00 rows=34 width=4) Partitions selected: 6 (out of 6) Rows out: 0 rows (seg0) with 0.022 ms to end, start offset by 1.630 ms. -> Dynamic Index Scan on pt (dynamic scan id: 1) (cost=0.00..54.00 rows=3 width=31) Index Cond: pt.ptid = t.tid Rows out: Avg 6.0 rows x 3 workers. Max 9 rows (seg0) with 0.247 ms to first row, 0.409 ms to end of 2 scans, start offset by 1.645 ms. Partitions scanned: Avg 6.0 (out of 6) x 3 workers of 2 scans. Max 6 parts (seg0). Slice statistics: (slice0) Executor memory: 394K bytes. (slice1) Executor memory: 261K bytes avg x 3 workers, 271K bytes max (seg0). (slice2) Executor memory: 301K bytes avg x 3 workers, 301K bytes max (seg0). 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; optimizer_segments=2 Optimizer status: PQO version 2.5.0 Total runtime: 3.447 ms (27 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 3:1 (slice2; segments: 3) (cost=0.00..434.02 rows=9 width=62) Merge Key: pt1.dist -> Sort (cost=0.00..434.01 rows=3 width=62) Sort Key: pt1.dist -> Hash Join (cost=0.00..434.01 rows=3 width=62) Hash Cond: pt1.ptid = pt.ptid -> Dynamic Table Scan on pt1 (dynamic scan id: 2) (cost=0.00..431.00 rows=18 width=31) -> Hash (cost=100.00..100.00 rows=34 width=4) -> Partition Selector for pt1 (dynamic scan id: 2) (cost=10.00..100.00 rows=34 width=4) Filter: pt1.ptid = pt.ptid -> Broadcast Motion 3:3 (slice1; segments: 3) (cost=0.00..3.00 rows=1 width=31) -> Sequence (cost=0.00..3.00 rows=1 width=31) -> Partition Selector for pt (dynamic scan id: 1) (cost=10.00..100.00 rows=34 width=4) Partitions selected: 6 (out of 6) -> Dynamic Index Scan on pt (dynamic scan id: 1) (cost=0.00..3.00 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; optimizer_segments=2 Optimizer status: PQO version 2.5.0 (18 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..434.01 rows=1 width=8) -> Gather Motion 3:1 (slice2; segments: 3) (cost=0.00..434.01 rows=1 width=8) -> Aggregate (cost=0.00..434.01 rows=1 width=8) -> Hash Join (cost=0.00..434.01 rows=3 width=1) Hash Cond: pt1.ptid = pt.ptid -> Dynamic Table Scan on pt1 (dynamic scan id: 2) (cost=0.00..431.00 rows=18 width=4) -> Hash (cost=100.00..100.00 rows=34 width=4) -> Partition Selector for pt1 (dynamic scan id: 2) (cost=10.00..100.00 rows=34 width=4) Filter: pt1.dist = pt.ptid -> Broadcast Motion 3:3 (slice1; segments: 3) (cost=0.00..3.00 rows=1 width=4) -> Result (cost=0.00..3.00 rows=1 width=4) -> Sequence (cost=0.00..3.00 rows=1 width=11) -> Partition Selector for pt (dynamic scan id: 1) (cost=10.00..100.00 rows=34 width=4) Partitions selected: 6 (out of 6) -> Dynamic Index Scan on pt (dynamic scan id: 1) (cost=0.00..3.00 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; optimizer_segments=2 Optimizer status: PQO version 2.5.0 (18 rows) select count(*) from pt, pt1 where pt.ptid = pt1.ptid and pt.pt1 = 'hello0'; count ------- 9 (1 row) -- -- Partition Selector under Material in NestLoopJoin inner side -- drop table if exists pt; drop table if exists t; create table t(id int, a int); NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'id' 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 table pt(id int, b int) DISTRIBUTED BY (id) PARTITION BY RANGE(b) (START (0) END (5) EVERY (1)); NOTICE: CREATE TABLE will create partition "pt_1_prt_1" 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" insert into t select i, i from generate_series(0,4) i; insert into pt select i, i from generate_series(0,4) i; analyze t; analyze pt; begin; set enable_hashjoin=off; set enable_seqscan=on; set enable_nestloop=on; explain select * from t, pt where a = b; QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Gather Motion 3:1 (slice3; segments: 3) (cost=0.00..862.00 rows=5 width=16) -> Hash Join (cost=0.00..862.00 rows=2 width=16) Hash Cond: t.a = pt.b -> Redistribute Motion 3:3 (slice1; segments: 3) (cost=0.00..431.00 rows=2 width=8) Hash Key: t.a -> Table Scan on t (cost=0.00..431.00 rows=2 width=8) -> Hash (cost=431.00..431.00 rows=2 width=8) -> Redistribute Motion 3:3 (slice2; segments: 3) (cost=0.00..431.00 rows=2 width=8) Hash Key: pt.b -> Sequence (cost=0.00..431.00 rows=2 width=8) -> Partition Selector for pt (dynamic scan id: 1) (cost=10.00..100.00 rows=34 width=4) Partitions selected: 5 (out of 5) -> Dynamic Table Scan on pt (dynamic scan id: 1) (cost=0.00..431.00 rows=2 width=8) Settings: enable_bitmapscan=off; enable_hashjoin=off; enable_indexscan=off; enable_mergejoin=off; enable_nestloop=on; enable_seqscan=on; gp_segments_for_planner=2; optimizer=on; optimizer_segments=2 Optimizer status: PQO version 2.26.0 (15 rows) select * from t, pt where a = b; id | a | id | b ----+---+----+--- 0 | 0 | 0 | 0 1 | 1 | 1 | 1 2 | 2 | 2 | 2 3 | 3 | 3 | 3 4 | 4 | 4 | 4 (5 rows) rollback; -- -- 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 ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Sort (cost=0.00..862.00 rows=1 width=38) Sort Key: fact1.u -> Hash Join (cost=0.00..862.00 rows=1 width=38) Hash Cond: dim1.pid = fact1.pid AND dim1.code = fact1.code -> Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..431.00 rows=1 width=18) -> Table Scan on dim1 (cost=0.00..431.00 rows=1 width=18) -> Hash (cost=431.00..431.00 rows=1 width=20) -> Gather Motion 3:1 (slice2; segments: 3) (cost=0.00..431.00 rows=1 width=20) -> Sequence (cost=0.00..431.00 rows=1 width=20) -> Partition Selector for fact1 (dynamic scan id: 1) (cost=10.00..100.00 rows=34 width=4) Partitions selected: 12 (out of 12) -> Dynamic Table Scan on fact1 (dynamic scan id: 1) (cost=0.00..431.00 rows=1 width=20) 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; optimizer_segments=2 Optimizer status: PQO version 2.5.0 (14 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 ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Sort (cost=0.00..862.00 rows=1 width=38) Sort Key: fact1.u -> Hash Join (cost=0.00..862.00 rows=1 width=38) Hash Cond: dim1.pid = fact1.pid AND dim1.code = fact1.code -> Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..431.00 rows=1 width=18) -> Table Scan on dim1 (cost=0.00..431.00 rows=1 width=18) -> Hash (cost=431.00..431.00 rows=1 width=20) -> Gather Motion 3:1 (slice2; segments: 3) (cost=0.00..431.00 rows=1 width=20) -> Sequence (cost=0.00..431.00 rows=1 width=20) -> Partition Selector for fact1 (dynamic scan id: 1) (cost=10.00..100.00 rows=34 width=4) Partitions selected: 12 (out of 12) -> Dynamic Table Scan on fact1 (dynamic scan id: 1) (cost=0.00..431.00 rows=1 width=20) 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; optimizer_segments=2 Optimizer status: PQO version 2.5.0 (14 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 ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Sort (cost=0.00..862.00 rows=1 width=38) Sort Key: fact1.u -> Hash Join (cost=0.00..862.00 rows=1 width=38) Hash Cond: dim1.pid = fact1.pid -> Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..431.00 rows=1 width=18) -> Table Scan on dim1 (cost=0.00..431.00 rows=1 width=18) -> Hash (cost=431.00..431.00 rows=1 width=20) -> Gather Motion 3:1 (slice2; segments: 3) (cost=0.00..431.00 rows=1 width=20) -> Sequence (cost=0.00..431.00 rows=1 width=20) -> Partition Selector for fact1 (dynamic scan id: 1) (cost=10.00..100.00 rows=34 width=4) Partitions selected: 12 (out of 12) -> Dynamic Table Scan on fact1 (dynamic scan id: 1) (cost=0.00..431.00 rows=1 width=20) 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; optimizer_segments=2 Optimizer status: PQO version 2.5.0 (14 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 ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Sort (cost=0.00..862.00 rows=1 width=38) Sort Key: fact1.u -> Hash Join (cost=0.00..862.00 rows=1 width=38) Hash Cond: dim1.pid = fact1.pid -> Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..431.00 rows=1 width=18) -> Table Scan on dim1 (cost=0.00..431.00 rows=1 width=18) -> Hash (cost=431.00..431.00 rows=1 width=20) -> Gather Motion 3:1 (slice2; segments: 3) (cost=0.00..431.00 rows=1 width=20) -> Sequence (cost=0.00..431.00 rows=1 width=20) -> Partition Selector for fact1 (dynamic scan id: 1) (cost=10.00..100.00 rows=34 width=4) Partitions selected: 12 (out of 12) -> Dynamic Table Scan on fact1 (dynamic scan id: 1) (cost=0.00..431.00 rows=1 width=20) 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; optimizer_segments=2 Optimizer status: PQO version 2.5.0 (14 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 ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Sort (cost=0.00..862.00 rows=1 width=38) Sort Key: fact1.u -> Hash Join (cost=0.00..862.00 rows=1 width=38) Hash Cond: dim1.pid = fact1.pid -> Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..431.00 rows=1 width=18) -> Table Scan on dim1 (cost=0.00..431.00 rows=1 width=18) -> Hash (cost=431.00..431.00 rows=1 width=20) -> Gather Motion 3:1 (slice2; segments: 3) (cost=0.00..431.00 rows=1 width=20) -> Sequence (cost=0.00..431.00 rows=1 width=20) -> Partition Selector for fact1 (dynamic scan id: 1) (cost=10.00..100.00 rows=34 width=4) Filter: fact1.code = 'OH'::text Partitions selected: 4 (out of 12) -> Dynamic Table Scan on fact1 (dynamic scan id: 1) (cost=0.00..431.00 rows=1 width=20) Filter: code = 'OH'::text 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; optimizer_segments=2 Optimizer status: PQO version 2.5.0 (16 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 ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Sort (cost=0.00..862.00 rows=1 width=38) Sort Key: fact1.u -> Hash Join (cost=0.00..862.00 rows=1 width=38) Hash Cond: dim1.pid = fact1.pid -> Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..431.00 rows=1 width=18) -> Table Scan on dim1 (cost=0.00..431.00 rows=1 width=18) -> Hash (cost=431.00..431.00 rows=1 width=20) -> Gather Motion 3:1 (slice2; segments: 3) (cost=0.00..431.00 rows=1 width=20) -> Sequence (cost=0.00..431.00 rows=1 width=20) -> Partition Selector for fact1 (dynamic scan id: 1) (cost=10.00..100.00 rows=34 width=4) Filter: fact1.code = 'OH'::text Partitions selected: 4 (out of 12) -> Dynamic Table Scan on fact1 (dynamic scan id: 1) (cost=0.00..431.00 rows=1 width=20) Filter: code = 'OH'::text 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; optimizer_segments=2 Optimizer status: PQO version 2.5.0 (16 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 3:1 (slice3; segments: 3) (cost=0.00..862.00 rows=1 width=16) Merge Key: fact1.code -> GroupAggregate (cost=0.00..862.00 rows=1 width=16) Group By: fact1.code -> Sort (cost=0.00..862.00 rows=1 width=16) Sort Key: fact1.code -> Redistribute Motion 3:3 (slice2; segments: 3) (cost=0.00..862.00 rows=1 width=16) Hash Key: fact1.code -> Result (cost=0.00..862.00 rows=1 width=16) -> GroupAggregate (cost=0.00..862.00 rows=1 width=16) Group By: fact1.code -> Sort (cost=0.00..862.00 rows=1 width=8) Sort Key: fact1.code -> Hash Join (cost=0.00..862.00 rows=1 width=8) Hash Cond: fact1.pid = dim1.pid -> Dynamic Table Scan on fact1 (dynamic scan id: 1) (cost=0.00..431.00 rows=1 width=12) -> Hash (cost=100.00..100.00 rows=34 width=4) -> Partition Selector for fact1 (dynamic scan id: 1) (cost=10.00..100.00 rows=34 width=4) Filter: fact1.dist = dim1.pid -> Broadcast Motion 3:3 (slice1; segments: 3) (cost=0.00..431.00 rows=1 width=4) -> Table Scan on dim1 (cost=0.00..431.00 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; optimizer_segments=2 Optimizer status: PQO version 2.5.0 (23 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 3:1 (slice3; segments: 3) (cost=0.00..862.00 rows=1 width=16) Merge Key: fact1.code -> GroupAggregate (cost=0.00..862.00 rows=1 width=16) Group By: fact1.code -> Sort (cost=0.00..862.00 rows=1 width=16) Sort Key: fact1.code -> Redistribute Motion 3:3 (slice2; segments: 3) (cost=0.00..862.00 rows=1 width=16) Hash Key: fact1.code -> Result (cost=0.00..862.00 rows=1 width=16) -> GroupAggregate (cost=0.00..862.00 rows=1 width=16) Group By: fact1.code -> Sort (cost=0.00..862.00 rows=1 width=8) Sort Key: fact1.code -> Hash Join (cost=0.00..862.00 rows=1 width=8) Hash Cond: fact1.pid = dim1.pid -> Dynamic Table Scan on fact1 (dynamic scan id: 1) (cost=0.00..431.00 rows=1 width=12) -> Hash (cost=100.00..100.00 rows=34 width=4) -> Partition Selector for fact1 (dynamic scan id: 1) (cost=10.00..100.00 rows=34 width=4) Filter: fact1.dist = dim1.pid -> Broadcast Motion 3:3 (slice1; segments: 3) (cost=0.00..431.00 rows=1 width=4) -> Table Scan on dim1 (cost=0.00..431.00 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; optimizer_segments=2 Optimizer status: PQO version 2.5.0 (23 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; -- ORCA doesn't do multi-attribute partitioning currently,so this falls -- back to the Postgres planner explain select * from dim inner join malp on (dim.i = malp.i); QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Gather Motion 3:1 (slice2; segments: 3) (cost=1.04..4.19 rows=6 width=23) -> Hash Join (cost=1.04..4.19 rows=2 width=23) Hash Cond: dpe_malp.malp.i = dim.i -> Append (cost=0.00..3.06 rows=2 width=15) -> Result (cost=0.00..1.03 rows=1 width=15) One-Time Filter: PartSelected -> Seq Scan on malp_1_prt_p1 malp (cost=0.00..1.03 rows=1 width=15) -> Result (cost=0.00..1.02 rows=1 width=15) One-Time Filter: PartSelected -> 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: PartSelected -> 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) -> Partition Selector for malp (dynamic scan id: 1) (cost=0.00..1.03 rows=1 width=8) Filter: dim.i -> Redistribute Motion 3:3 (slice1; segments: 3) (cost=0.00..1.03 rows=1 width=8) Hash Key: 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; optimizer_segments=2 Optimizer status: legacy query optimizer (21 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) -- -- Plan where the Append that the PartitionSelector affects is not the immediate child -- of the join. -- create table apart (id int4, t text) partition by range (id) (start (1) end (1000) every (200)); NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'id' 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 "apart_1_prt_1" for table "apart" NOTICE: CREATE TABLE will create partition "apart_1_prt_2" for table "apart" NOTICE: CREATE TABLE will create partition "apart_1_prt_3" for table "apart" NOTICE: CREATE TABLE will create partition "apart_1_prt_4" for table "apart" NOTICE: CREATE TABLE will create partition "apart_1_prt_5" for table "apart" create table b (id int4, t text); NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'id' 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 table c (id int4, t text); NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'id' 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 apart select g, g from generate_series(1, 999) g; insert into b select g, g from generate_series(1, 5) g; insert into c select g, g from generate_series(1, 20) g; analyze apart; analyze b; analyze c; set gp_dynamic_partition_pruning = off; explain select * from apart as a, b, c where a.t = b.t and a.id = c.id; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Gather Motion 3:1 (slice2; segments: 3) (cost=0.00..1293.11 rows=1 width=19) -> Hash Join (cost=0.00..1293.11 rows=1 width=19) Hash Cond: apart.id = c.id -> Hash Join (cost=0.00..862.11 rows=2 width=13) Hash Cond: apart.t = b.t -> Dynamic Table Scan on apart (dynamic scan id: 1) (cost=0.00..431.01 rows=333 width=7) -> Hash (cost=431.00..431.00 rows=4 width=6) -> Broadcast Motion 3:3 (slice1; segments: 3) (cost=0.00..431.00 rows=4 width=6) -> Table Scan on b (cost=0.00..431.00 rows=2 width=6) -> Hash (cost=100.00..100.00 rows=34 width=4) -> Partition Selector for apart (dynamic scan id: 1) (cost=10.00..100.00 rows=34 width=4) Filter: c.id = c.id -> Table Scan on c (cost=0.00..431.00 rows=7 width=6) 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; optimizer_segments=2 Optimizer status: PQO version 2.13.0 (15 rows) select * from apart as a, b, c where a.t = b.t and a.id = c.id; id | t | id | t | id | t ----+---+----+---+----+--- 3 | 3 | 3 | 3 | 3 | 3 4 | 4 | 4 | 4 | 4 | 4 5 | 5 | 5 | 5 | 5 | 5 1 | 1 | 1 | 1 | 1 | 1 2 | 2 | 2 | 2 | 2 | 2 (5 rows) set gp_dynamic_partition_pruning = on; explain select * from apart as a, b, c where a.t = b.t and a.id = c.id; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Gather Motion 3:1 (slice2; segments: 3) (cost=0.00..1293.11 rows=1 width=19) -> Hash Join (cost=0.00..1293.11 rows=1 width=19) Hash Cond: apart.id = c.id -> Hash Join (cost=0.00..862.11 rows=2 width=13) Hash Cond: apart.t = b.t -> Dynamic Table Scan on apart (dynamic scan id: 1) (cost=0.00..431.01 rows=333 width=7) -> Hash (cost=431.00..431.00 rows=4 width=6) -> Broadcast Motion 3:3 (slice1; segments: 3) (cost=0.00..431.00 rows=4 width=6) -> Table Scan on b (cost=0.00..431.00 rows=2 width=6) -> Hash (cost=100.00..100.00 rows=34 width=4) -> Partition Selector for apart (dynamic scan id: 1) (cost=10.00..100.00 rows=34 width=4) Filter: c.id = c.id -> Table Scan on c (cost=0.00..431.00 rows=7 width=6) 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; optimizer_segments=2 Optimizer status: PQO version 2.13.0 (15 rows) select * from apart as a, b, c where a.t = b.t and a.id = c.id; id | t | id | t | id | t ----+---+----+---+----+--- 3 | 3 | 3 | 3 | 3 | 3 4 | 4 | 4 | 4 | 4 | 4 5 | 5 | 5 | 5 | 5 | 5 1 | 1 | 1 | 1 | 1 | 1 2 | 2 | 2 | 2 | 2 | 2 (5 rows) -- -- DPE: assertion failed with window function -- 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..862.00 rows=1 width=20) Hash Cond: jpat.b = pat.b -> Window (cost=0.00..431.00 rows=1 width=16) Order By: jpat.a -> Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..431.00 rows=1 width=8) Merge Key: jpat.a -> Sort (cost=0.00..431.00 rows=1 width=8) Sort Key: jpat.a -> Table Scan on jpat (cost=0.00..431.00 rows=1 width=8) -> Hash (cost=431.00..431.00 rows=1 width=8) -> Gather Motion 3:1 (slice2; segments: 3) (cost=0.00..431.00 rows=1 width=8) -> Sequence (cost=0.00..431.00 rows=1 width=8) -> Partition Selector for pat (dynamic scan id: 1) (cost=10.00..100.00 rows=34 width=4) Partitions selected: 5 (out of 5) -> Dynamic Table Scan on pat (dynamic scan id: 1) (cost=0.00..431.00 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; optimizer_segments=2 Optimizer status: PQO version 2.5.0 (17 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)