src/test/regress/expected/external_table_union_all_optimizer.out

SET optimizer_trace_fallback=on;
-- Test external table as left child of union all with replicated table
\getenv abs_srcdir PG_ABS_SRCDIR
\getenv hostname PG_HOSTNAME
\set location1_file 'file://' :hostname :abs_srcdir '/data/location1.csv'
\set location2_file 'file://' :hostname :abs_srcdir '/data/location2.csv'
\set location3_file 'file://' :hostname :abs_srcdir '/data/location3.csv'
CREATE EXTERNAL TABLE multilocation_external_table(a INTEGER)
location (:'location1_file', :'location2_file', :'location3_file')
ON ALL FORMAT 'text';
CREATE EXTERNAL TABLE one_external_table(a INTEGER)
location (:'location2_file')
ON SEGMENT 2 FORMAT 'text';
CREATE TABLE simple_replicated_table(a integer) DISTRIBUTED REPLICATED;
INSERT INTO simple_replicated_table VALUES (1);
CREATE TABLE simple_distributed_table(a integer) DISTRIBUTED BY (a);
INSERT INTO simple_distributed_table VALUES (2);
EXPLAIN SELECT A FROM multilocation_external_table UNION ALL SELECT A FROM simple_replicated_table;
                                            QUERY PLAN                                             
---------------------------------------------------------------------------------------------------
 Gather Motion 3:1  (slice1; segments: 3)  (cost=0.00..886.95 rows=1000001 width=4)
   ->  Append  (cost=0.00..872.05 rows=333334 width=4)
         ->  Foreign Scan on multilocation_external_table  (cost=0.00..437.23 rows=333334 width=4)
         ->  Result  (cost=0.00..431.00 rows=1 width=4)
               One-Time Filter: (gp_execution_segment() = 2)
               ->  Seq Scan on simple_replicated_table  (cost=0.00..431.00 rows=1 width=4)
 Optimizer: Pivotal Optimizer (GPORCA)
(7 rows)

SELECT A FROM multilocation_external_table UNION ALL SELECT A FROM simple_replicated_table;
 a 
---
 3
 1
 1
 2
(4 rows)

EXPLAIN SELECT A FROM simple_replicated_table UNION ALL SELECT A FROM multilocation_external_table;
                                                  QUERY PLAN                                                   
---------------------------------------------------------------------------------------------------
 Gather Motion 3:1  (slice1; segments: 3)  (cost=0.00..886.95 rows=1000001 width=4)
   ->  Append  (cost=0.00..872.05 rows=333334 width=4)
         ->  Result  (cost=0.00..431.00 rows=1 width=4)
               One-Time Filter: (gp_execution_segment() = 1)
               ->  Seq Scan on simple_replicated_table  (cost=0.00..431.00 rows=1 width=4)
         ->  Foreign Scan on multilocation_external_table  (cost=0.00..437.23 rows=333334 width=4)
 Optimizer: GPORCA
(7 rows)

SELECT A FROM simple_replicated_table UNION ALL SELECT A FROM multilocation_external_table;
 a 
---
 1
 2
 3
 1
(4 rows)

EXPLAIN SELECT A FROM simple_replicated_table UNION ALL SELECT a FROM multilocation_external_table UNION ALL SELECT A FROM simple_distributed_table;
                                                  QUERY PLAN                                                   
---------------------------------------------------------------------------------------------------
 Gather Motion 3:1  (slice1; segments: 3)  (cost=0.00..1317.95 rows=1000002 width=4)
   ->  Append  (cost=0.00..1303.05 rows=333334 width=4)
         ->  Result  (cost=0.00..431.00 rows=1 width=4)
               One-Time Filter: (gp_execution_segment() = 2)
               ->  Seq Scan on simple_replicated_table  (cost=0.00..431.00 rows=1 width=4)
         ->  Foreign Scan on multilocation_external_table  (cost=0.00..437.23 rows=333334 width=4)
         ->  Seq Scan on simple_distributed_table  (cost=0.00..431.00 rows=1 width=4)
 Optimizer: GPORCA
(8 rows)

SELECT A FROM simple_replicated_table UNION ALL SELECT a FROM multilocation_external_table UNION ALL SELECT A FROM simple_distributed_table;
 a 
---
 1
 2
 3
 1
 2
(5 rows)

EXPLAIN SELECT A FROM simple_distributed_table UNION ALL SELECT a FROM multilocation_external_table UNION ALL SELECT A FROM simple_replicated_table;
                                            QUERY PLAN                                             
---------------------------------------------------------------------------------------------------
 Gather Motion 3:1  (slice1; segments: 3)  (cost=0.00..1317.95 rows=1000002 width=4)
   ->  Append  (cost=0.00..1303.05 rows=333334 width=4)
         ->  Seq Scan on simple_distributed_table  (cost=0.00..431.00 rows=1 width=4)
         ->  Foreign Scan on multilocation_external_table  (cost=0.00..437.23 rows=333334 width=4)
         ->  Result  (cost=0.00..431.00 rows=1 width=4)
               One-Time Filter: (gp_execution_segment() = 0)
               ->  Seq Scan on simple_replicated_table  (cost=0.00..431.00 rows=1 width=4)
 Optimizer: Pivotal Optimizer (GPORCA)
(8 rows)

SELECT A FROM simple_distributed_table UNION ALL SELECT a FROM multilocation_external_table UNION ALL SELECT A FROM simple_replicated_table;
 a 
---
 2
 1
 2
 1
 3
(5 rows)

EXPLAIN SELECT * FROM one_external_table UNION ALL SELECT a FROM simple_replicated_table;
                                        QUERY PLAN                                         
-------------------------------------------------------------------------------------------
 Gather Motion 3:1  (slice1; segments: 3)  (cost=0.00..886.95 rows=1000001 width=4)
   ->  Append  (cost=0.00..872.05 rows=333334 width=4)
         ->  Foreign Scan on one_external_table  (cost=0.00..437.23 rows=333334 width=4)
         ->  Result  (cost=0.00..431.00 rows=1 width=4)
               One-Time Filter: (gp_execution_segment() = 2)
               ->  Seq Scan on simple_replicated_table  (cost=0.00..431.00 rows=1 width=4)
 Optimizer: Pivotal Optimizer (GPORCA)
(7 rows)

SELECT * FROM one_external_table UNION ALL SELECT a FROM simple_replicated_table;
 a 
---
 2
 1
(2 rows)