dpe_optimizer.out

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                                                     
--------------------------------------------------------------------------------------------------------------------
 WindowAgg  (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;
--
-- partition selector with 0 tuples and 0 matched partitions
--
drop table if exists t;
drop table if exists pt;
create table t(a int);
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.
create table pt(b int) DISTRIBUTED BY (b) 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"
begin;
set enable_hashjoin=off; -- foring nestloop join
set enable_nestloop=on;
set enable_seqscan=on;
-- 7 in seg1, 8 in seg2, no data in seg0
insert into t select i from generate_series(7,8) i;
-- 0~2 in seg0, 3~4 in seg 1, no data in seg2
insert into pt select i from generate_series(0,4) i;
analyze t;
analyze pt;
explain select * from t, pt where a = b;
                                                                                               QUERY PLAN                                                                                                
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Gather Motion 3:1  (slice1; segments: 3)  (cost=0.00..862.00 rows=1 width=8)
   ->  Hash Join  (cost=0.00..862.00 rows=1 width=8)
         Hash Cond: pt.b = t.a
         ->  Dynamic Table Scan on pt (dynamic scan id: 1)  (cost=0.00..431.00 rows=2 width=4)
         ->  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.b = t.a
                     ->  Table Scan on t  (cost=0.00..431.00 rows=1 width=4)
 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.29.0
(10 rows)

select * from t, pt where a = b;
 a | b 
---+---
(0 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
   ->  WindowAgg  (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)