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. Creating a NULL policy entry.
insert into pt1 select * from pt;
insert into pt1 select dist, pt1, pt2, pt3, ptid-100 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)
               ->  Seq 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: ptid = $0
 Optimizer: PQO version 2.64.0
(11 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..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)
               ->  Seq 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: ptid = ($0 + 1)
 Optimizer: PQO version 2.64.0
(11 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)
               ->  Seq 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: ptid = $0
 Optimizer: PQO version 2.64.0
(12 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)
               ->  Seq 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: pt1 = $1
                     Filter: ptid = $0
 Optimizer: PQO version 2.64.0
(12 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 Semi Join  (cost=0.00..862.01 rows=3 width=31)
         Hash Cond: pt.ptid = t.tid
         ->  Dynamic Seq 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)
                           ->  Seq 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 Semi Join  (cost=0.00..862.01 rows=3 width=31)
         Hash Cond: pt.ptid = t.tid
         ->  Dynamic Seq 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)
                                       ->  Seq 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)
                           ->  Seq 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: ptid = $0
 Optimizer: PQO version 2.64.0
(13 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)
                           ->  Seq 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: ptid = $0
 Optimizer: PQO version 2.64.0
(16 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..886.02 rows=36 width=50)
   ->  Append  (cost=0.00..886.01 rows=12 width=50)
         ->  Nested Loop  (cost=0.00..443.01 rows=6 width=50)
               Join Filter: true
               ->  Broadcast Motion 3:3  (slice1; segments: 3)  (cost=0.00..431.00 rows=2 width=19)
                     ->  Seq Scan on t  (cost=0.00..431.00 rows=1 width=19)
               ->  Sequence  (cost=0.00..12.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..12.00 rows=3 width=31)
                           Index Cond: ptid = $0
         ->  Nested Loop  (cost=0.00..443.01 rows=6 width=50)
               Join Filter: true
               ->  Broadcast Motion 3:3  (slice2; segments: 3)  (cost=0.00..431.00 rows=2 width=19)
                     ->  Seq Scan on t t_1  (cost=0.00..431.00 rows=1 width=19)
               ->  Sequence  (cost=0.00..12.00 rows=3 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 pt_1 (dynamic scan id: 2)  (cost=0.00..12.00 rows=3 width=31)
                           Index Cond: ptid = ($1 + 2)
 Optimizer: PQO version 2.74.0
(21 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..886.01 rows=1 width=8)
   ->  Gather Motion 3:1  (slice3; segments: 3)  (cost=0.00..886.01 rows=1 width=8)
         ->  Aggregate  (cost=0.00..886.01 rows=1 width=8)
               ->  Append  (cost=0.00..886.01 rows=12 width=1)
                     ->  Nested Loop  (cost=0.00..443.01 rows=6 width=50)
                           Join Filter: true
                           ->  Broadcast Motion 3:3  (slice1; segments: 3)  (cost=0.00..431.00 rows=2 width=19)
                                 ->  Seq Scan on t  (cost=0.00..431.00 rows=1 width=19)
                           ->  Sequence  (cost=0.00..12.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..12.00 rows=3 width=31)
                                       Index Cond: ptid = $0
                     ->  Nested Loop  (cost=0.00..443.01 rows=6 width=50)
                           Join Filter: true
                           ->  Broadcast Motion 3:3  (slice2; segments: 3)  (cost=0.00..431.00 rows=2 width=19)
                                 ->  Seq Scan on t t_1  (cost=0.00..431.00 rows=1 width=19)
                           ->  Sequence  (cost=0.00..12.00 rows=3 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 pt_1 (dynamic scan id: 2)  (cost=0.00..12.00 rows=3 width=31)
                                       Index Cond: ptid = ($1 + 2)
 Optimizer: PQO version 2.74.0
(23 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)
               ->  Seq 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: ptid = $0
 Optimizer: PQO version 2.64.0
(11 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)

--
-- index scan
--
set enable_nestloop=on;
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  (slice3; segments: 3)  (cost=0.00..437.00 rows=1 width=50)
   ->  Hash Join  (cost=0.00..437.00 rows=1 width=50)
         Hash Cond: t.tid = pt.ptid
         ->  Redistribute Motion 3:3  (slice1; segments: 3)  (cost=0.00..431.00 rows=1 width=19)
               Hash Key: t.tid
               ->  Seq Scan on t  (cost=0.00..431.00 rows=1 width=19)
         ->  Hash  (cost=6.00..6.00 rows=1 width=31)
               ->  Redistribute Motion 3:3  (slice2; segments: 3)  (cost=0.00..6.00 rows=1 width=31)
                     Hash Key: pt.ptid
                     ->  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: pt1 = 'hello0'::text
 Optimizer: PQO version 2.73.0
(15 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)
               ->  Seq 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: ptid = $0
 Optimizer: PQO version 2.64.0
(11 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)
               ->  Seq 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: pt1 = $0
 Optimizer: PQO version 2.64.0
(11 rows)

select * from t, pt where t1 = pt1;
 dist | tid |   t1   | t2  | dist |  pt1   |  pt2  |    pt3    | ptid 
------+-----+--------+-----+------+--------+-------+-----------+------
    1 |   1 | hello1 | bar |    1 | hello1 | world | drop this |    1
    0 |   0 | hello0 | bar |    0 | hello0 | world | drop this |    0
(2 rows)

explain select * from t, pt where tid < ptid;
                                               QUERY PLAN                                               
--------------------------------------------------------------------------------------------------------
 Gather Motion 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)
               ->  Seq 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: ptid > $0
 Optimizer: PQO version 2.64.0
(11 rows)

select * from t, pt where tid < ptid;
 dist | tid |   t1   | t2  | dist |   pt1   |  pt2  |    pt3    | ptid 
------+-----+--------+-----+------+---------+-------+-----------+------
    1 |   1 | hello1 | bar |    2 | hello2  | world | drop this |    2
    0 |   0 | hello0 | bar |    2 | hello2  | world | drop this |    2
    1 |   1 | hello1 | bar |    8 | hello8  | world | drop this |    2
    0 |   0 | hello0 | bar |    8 | hello8  | world | drop this |    2
    1 |   1 | hello1 | bar |   14 | hello14 | world | drop this |    2
    0 |   0 | hello0 | bar |   14 | hello14 | world | drop this |    2
    1 |   1 | hello1 | bar |   20 | hello20 | world | drop this |    2
    0 |   0 | hello0 | bar |   20 | hello20 | world | drop this |    2
    1 |   1 | hello1 | bar |   26 | hello26 | world | drop this |    2
    0 |   0 | hello0 | bar |   26 | hello26 | world | drop this |    2
    1 |   1 | hello1 | bar |   32 | hello32 | world | drop this |    2
    0 |   0 | hello0 | bar |   32 | hello32 | world | drop this |    2
    1 |   1 | hello1 | bar |   38 | hello38 | world | drop this |    2
    0 |   0 | hello0 | bar |   38 | hello38 | world | drop this |    2
    1 |   1 | hello1 | bar |   44 | hello44 | world | drop this |    2
    0 |   0 | hello0 | bar |   44 | hello44 | world | drop this |    2
    1 |   1 | hello1 | bar |   50 | hello50 | world | drop this |    2
    0 |   0 | hello0 | bar |   50 | hello50 | world | drop this |    2
    1 |   1 | hello1 | bar |    4 | hello4  | world | drop this |    4
    0 |   0 | hello0 | bar |    4 | hello4  | world | drop this |    4
    1 |   1 | hello1 | bar |   10 | hello10 | world | drop this |    4
    0 |   0 | hello0 | bar |   10 | hello10 | world | drop this |    4
    1 |   1 | hello1 | bar |   16 | hello16 | world | drop this |    4
    0 |   0 | hello0 | bar |   16 | hello16 | world | drop this |    4
    1 |   1 | hello1 | bar |   22 | hello22 | world | drop this |    4
    0 |   0 | hello0 | bar |   22 | hello22 | world | drop this |    4
    1 |   1 | hello1 | bar |   28 | hello28 | world | drop this |    4
    0 |   0 | hello0 | bar |   28 | hello28 | world | drop this |    4
    1 |   1 | hello1 | bar |   34 | hello34 | world | drop this |    4
    0 |   0 | hello0 | bar |   34 | hello34 | world | drop this |    4
    1 |   1 | hello1 | bar |   40 | hello40 | world | drop this |    4
    0 |   0 | hello0 | bar |   40 | hello40 | world | drop this |    4
    1 |   1 | hello1 | bar |   46 | hello46 | world | drop this |    4
    0 |   0 | hello0 | bar |   46 | hello46 | world | drop this |    4
    1 |   1 | hello1 | bar |   52 | hello52 | world | drop this |    4
    0 |   0 | hello0 | bar |   52 | hello52 | world | drop this |    4
    1 |   1 | hello1 | bar |    5 | hello5  | world | drop this |    5
    0 |   0 | hello0 | bar |    5 | hello5  | world | drop this |    5
    1 |   1 | hello1 | bar |   11 | hello11 | world | drop this |    5
    0 |   0 | hello0 | bar |   11 | hello11 | world | drop this |    5
    1 |   1 | hello1 | bar |   17 | hello17 | world | drop this |    5
    0 |   0 | hello0 | bar |   17 | hello17 | world | drop this |    5
    1 |   1 | hello1 | bar |   23 | hello23 | world | drop this |    5
    0 |   0 | hello0 | bar |   23 | hello23 | world | drop this |    5
    1 |   1 | hello1 | bar |   29 | hello29 | world | drop this |    5
    0 |   0 | hello0 | bar |   29 | hello29 | world | drop this |    5
    1 |   1 | hello1 | bar |   35 | hello35 | world | drop this |    5
    0 |   0 | hello0 | bar |   35 | hello35 | world | drop this |    5
    1 |   1 | hello1 | bar |   41 | hello41 | world | drop this |    5
    0 |   0 | hello0 | bar |   41 | hello41 | world | drop this |    5
    1 |   1 | hello1 | bar |   47 | hello47 | world | drop this |    5
    0 |   0 | hello0 | bar |   47 | hello47 | world | drop this |    5
    1 |   1 | hello1 | bar |   53 | hello53 | world | drop this |    5
    0 |   0 | hello0 | bar |   53 | hello53 | world | drop this |    5
    0 |   0 | hello0 | bar |    1 | hello1  | world | drop this |    1
    0 |   0 | hello0 | bar |    7 | hello7  | world | drop this |    1
    0 |   0 | hello0 | bar |   13 | hello13 | world | drop this |    1
    0 |   0 | hello0 | bar |   19 | hello19 | world | drop this |    1
    0 |   0 | hello0 | bar |   25 | hello25 | world | drop this |    1
    0 |   0 | hello0 | bar |   31 | hello31 | world | drop this |    1
    0 |   0 | hello0 | bar |   37 | hello37 | world | drop this |    1
    0 |   0 | hello0 | bar |   43 | hello43 | world | drop this |    1
    0 |   0 | hello0 | bar |   49 | hello49 | world | drop this |    1
    1 |   1 | hello1 | bar |    3 | hello3  | world | drop this |    3
    0 |   0 | hello0 | bar |    3 | hello3  | world | drop this |    3
    1 |   1 | hello1 | bar |    9 | hello9  | world | drop this |    3
    0 |   0 | hello0 | bar |    9 | hello9  | world | drop this |    3
    1 |   1 | hello1 | bar |   15 | hello15 | world | drop this |    3
    0 |   0 | hello0 | bar |   15 | hello15 | world | drop this |    3
    1 |   1 | hello1 | bar |   21 | hello21 | world | drop this |    3
    0 |   0 | hello0 | bar |   21 | hello21 | world | drop this |    3
    1 |   1 | hello1 | bar |   27 | hello27 | world | drop this |    3
    0 |   0 | hello0 | bar |   27 | hello27 | world | drop this |    3
    1 |   1 | hello1 | bar |   33 | hello33 | world | drop this |    3
    0 |   0 | hello0 | bar |   33 | hello33 | world | drop this |    3
    1 |   1 | hello1 | bar |   39 | hello39 | world | drop this |    3
    0 |   0 | hello0 | bar |   39 | hello39 | world | drop this |    3
    1 |   1 | hello1 | bar |   45 | hello45 | world | drop this |    3
    0 |   0 | hello0 | bar |   45 | hello45 | world | drop this |    3
    1 |   1 | hello1 | bar |   51 | hello51 | world | drop this |    3
    0 |   0 | hello0 | bar |   51 | hello51 | world | drop this |    3
(81 rows)

--
-- cascading joins
--
explain select * from t, t1, pt where t1.t2 = t.t2 and t1.tid = ptid;
                                                  QUERY PLAN                                                  
--------------------------------------------------------------------------------------------------------------
 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)
                     ->  Seq 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: ptid = $0
   ->  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)
               ->  Seq Scan on t  (cost=0.00..431.00 rows=1 width=19)
 Optimizer: PQO version 2.55.21
(15 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) (actual time=0.750..0.755 rows=18 loops=1)
   ->  Nested Loop  (cost=0.00..485.01 rows=6 width=50) (actual time=0.313..0.524 rows=9 loops=1)
         Join Filter: true
         ->  Broadcast Motion 3:3  (slice1; segments: 3)  (cost=0.00..431.00 rows=2 width=19) (actual time=0.208..0.358 rows=2 loops=1)
               ->  Seq Scan on t  (cost=0.00..431.00 rows=1 width=19) (actual time=0.020..0.021 rows=2 loops=1)
         ->  Sequence  (cost=0.00..54.00 rows=3 width=31) (actual time=0.048..0.075 rows=4 loops=2)
               ->  Partition Selector for pt (dynamic scan id: 1)  (cost=10.00..100.00 rows=34 width=4) (never executed)
                     Partitions selected: 6 (out of 6)
               ->  Dynamic Index Scan on pt (dynamic scan id: 1)  (cost=0.00..54.00 rows=3 width=31) (actual time=0.046..0.073 rows=4 loops=2)
                     Index Cond: ptid = $0
                     Partitions scanned:  Avg 6.0 (out of 6) x 3 workers of 2 scans.  Max 6 parts (seg0).
   (slice0)    Executor memory: 386K bytes.
   (slice1)    Executor memory: 60K bytes avg x 3 workers, 62K bytes max (seg0).
   (slice2)    Executor memory: 155K bytes avg x 3 workers, 155K bytes max (seg0).
   (slice3)    
 Memory used:  128000kB
 Optimizer: PQO version 2.55.21
 Total runtime: 2.810 ms
(17 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.02 rows=3 width=62)
         Sort Key: pt1.dist
         ->  Hash Join  (cost=0.00..434.02 rows=3 width=62)
               Hash Cond: pt1.ptid = pt.ptid
               ->  Dynamic Seq Scan on pt1 (dynamic scan id: 2)  (cost=0.00..431.00 rows=36 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 Seq Scan on pt1 (dynamic scan id: 2)  (cost=0.00..431.00 rows=36 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)
                                 ->  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
               ->  Seq 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 Seq 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 Seq 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
                     ->  Seq 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_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" for table "fact1"
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" for table "fact1"
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" for table "fact1"
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)
               ->  Seq 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 Seq 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)
               ->  Seq 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 Seq 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)
               ->  Seq 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 Seq 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)
               ->  Seq 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 Seq 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)
               ->  Seq 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 Seq 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)
               ->  Seq 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 Seq 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 Key: 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 Key: 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 Seq 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)
                                                               ->  Seq 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 Key: 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 Key: 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 Seq 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)
                                                               ->  Seq 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.18 rows=6 width=23)
   ->  Hash Join  (cost=1.04..4.18 rows=2 width=23)
         Hash Cond: malp_1_prt_p1.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  (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  (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  (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)
 Optimizer: legacy query optimizer
(20 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 Seq 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)
                           ->  Seq 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
                     ->  Seq 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 Seq 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)
                           ->  Seq 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
                     ->  Seq 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:  count(*) OVER (?)
         ->  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
                     ->  Seq 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 Seq 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)