qp_orca_fallback.out

-- Test the optimizer_enable_dml_constraints GUC, which forces GPORCA to fall back when there
-- are NULL or CHECK constraints on a table.
set optimizer_trace_fallback = on;
DROP TABLE IF EXISTS constr_tab;
NOTICE:  table "constr_tab" does not exist, skipping
CREATE TABLE constr_tab ( a int check (a>0) , b int, c int, d int, CHECK (a+b>5)) DISTRIBUTED BY (a);
set optimizer_enable_dml_constraints = off;
explain insert into constr_tab values (1,2,3);
                   QUERY PLAN                   
------------------------------------------------
 Insert on constr_tab  (cost=0.00..0.01 rows=1 width=0)
   ->  Result  (cost=0.00..0.01 rows=1 width=0)
 Optimizer status: Postgres query optimizer
(3 rows)

set optimizer_enable_dml_constraints=on;
explain insert into constr_tab values (1,2,3);
                   QUERY PLAN                   
------------------------------------------------
 Insert on constr_tab  (cost=0.00..0.01 rows=1 width=0)
   ->  Result  (cost=0.00..0.01 rows=1 width=0)
 Optimizer status: Postgres query optimizer
(3 rows)

-- The remaining tests require a row in the table.
INSERT INTO constr_tab VALUES(1,5,3,4);
set optimizer_enable_dml_constraints=off;
explain update constr_tab set a = 10;
                                       QUERY PLAN                                        
-----------------------------------------------------------------------------------------
 Update on constr_tab  (cost=0.00..1.01 rows=1 width=22)
   ->  Redistribute Motion 3:3  (slice1; segments: 3)  (cost=0.00..1.01 rows=1 width=22)
         Hash Key: a
         ->  Split  (cost=0.00..1.01 rows=1 width=22)
               ->  Seq Scan on constr_tab  (cost=0.00..1.01 rows=1 width=22)
 Optimizer: Postgres query optimizer
(6 rows)

explain update constr_tab set b = 10;
                           QUERY PLAN                            
-----------------------------------------------------------------
 Update on constr_tab  (cost=0.00..1.01 rows=1 width=22)
   ->  Seq Scan on constr_tab  (cost=0.00..1.01 rows=1 width=22)
 Optimizer status: Postgres query optimizer
(3 rows)

set optimizer_enable_dml_constraints=on;
explain update constr_tab set b = 10;
                           QUERY PLAN                            
-----------------------------------------------------------------
 Update on constr_tab  (cost=0.00..1.01 rows=1 width=22)
   ->  Seq Scan on constr_tab  (cost=0.00..1.01 rows=1 width=22)
 Optimizer status: Postgres query optimizer
(3 rows)

-- Same, with NOT NULL constraint.
DROP TABLE IF EXISTS constr_tab;
CREATE TABLE constr_tab ( a int NOT NULL, b int, c int, d int, CHECK (a+b>5)) DISTRIBUTED BY (a);
INSERT INTO constr_tab VALUES(1,5,3,4);
set optimizer_enable_dml_constraints=off;
explain update constr_tab set a = 10;
                                       QUERY PLAN                                        
-----------------------------------------------------------------------------------------
 Update on constr_tab  (cost=0.00..1.01 rows=1 width=22)
   ->  Redistribute Motion 3:3  (slice1; segments: 3)  (cost=0.00..1.01 rows=1 width=22)
         Hash Key: a
         ->  Split  (cost=0.00..1.01 rows=1 width=22)
               ->  Seq Scan on constr_tab  (cost=0.00..1.01 rows=1 width=22)
 Optimizer: Postgres query optimizer
(6 rows)

DROP TABLE IF EXISTS constr_tab;
CREATE TABLE constr_tab ( a int NOT NULL, b int NOT NULL, c int NOT NULL, d int NOT NULL) DISTRIBUTED BY (a,b);
INSERT INTO constr_tab VALUES(1,5,3,4);
INSERT INTO constr_tab VALUES(1,5,3,4);
set optimizer_enable_dml_constraints=off;
explain update constr_tab set b = 10;
                                       QUERY PLAN                                        
-----------------------------------------------------------------------------------------
 Update on constr_tab  (cost=0.00..1.01 rows=1 width=22)
   ->  Redistribute Motion 3:3  (slice1; segments: 3)  (cost=0.00..1.01 rows=1 width=22)
         Hash Key: a, b
         ->  Split  (cost=0.00..1.01 rows=1 width=22)
               ->  Seq Scan on constr_tab  (cost=0.00..1.01 rows=1 width=22)
 Optimizer: Postgres query optimizer
(6 rows)

DROP TABLE IF EXISTS constr_tab;
CREATE TABLE constr_tab ( a int, b int, c int, d int) DISTRIBUTED BY (a);
INSERT INTO constr_tab VALUES(1,5,3,4);
INSERT INTO constr_tab VALUES(1,5,3,4);
set optimizer_enable_dml_constraints=off;
explain update constr_tab set a = 10;
                                       QUERY PLAN                                        
-----------------------------------------------------------------------------------------
 Update on constr_tab  (cost=0.00..1.01 rows=1 width=22)
   ->  Redistribute Motion 3:3  (slice1; segments: 3)  (cost=0.00..1.01 rows=1 width=22)
         Hash Key: a
         ->  Split  (cost=0.00..1.01 rows=1 width=22)
               ->  Seq Scan on constr_tab  (cost=0.00..1.01 rows=1 width=22)
 Optimizer: Postgres query optimizer
(6 rows)

-- Test ORCA fallback on "FROM ONLY"
CREATE TABLE homer (a int, b int, c int)
DISTRIBUTED BY (a)
PARTITION BY range(b)
    SUBPARTITION BY range(c)
        SUBPARTITION TEMPLATE (
            START(40) END(46) EVERY(3)
        )
(START(0) END(4) EVERY(2));
INSERT INTO homer VALUES (1,0,40),(2,1,43),(3,2,41),(4,3,44);
SELECT * FROM ONLY homer;
 a | b | c 
---+---+---
(0 rows)

SELECT * FROM ONLY homer_1_prt_1;
 a | b | c 
---+---+---
(0 rows)

UPDATE ONLY homer SET c = c + 1;
SELECT * FROM homer;
 a | b | c  
---+---+----
 1 | 0 | 40
 2 | 1 | 43
 3 | 2 | 41
 4 | 3 | 44
(4 rows)

DELETE FROM ONLY homer WHERE a = 3;
SELECT * FROM homer;
 a | b | c  
---+---+----
 1 | 0 | 40
 2 | 1 | 43
 3 | 2 | 41
 4 | 3 | 44
(4 rows)

-- ORCA should not fallback just because external tables are in FROM clause
-- start_ignore
CREATE TABLE heap_t1 (a int, b int) DISTRIBUTED BY (b);
CREATE EXTERNAL TABLE ext_table_no_fallback (a int, b int) LOCATION ('gpfdist://myhost:8080/test.csv') FORMAT 'CSV';
-- end_ignore
EXPLAIN SELECT * FROM ext_table_no_fallback;
                                       QUERY PLAN                                        
-----------------------------------------------------------------------------------------
 Gather Motion 3:1  (slice1; segments: 3)  (cost=0.00..11000.00 rows=1000000 width=8)
   ->  External Scan on ext_table_no_fallback  (cost=0.00..11000.00 rows=333334 width=8)
 Optimizer: Postgres query optimizer
(3 rows)

EXPLAIN SELECT * FROM ONLY ext_table_no_fallback;
                                       QUERY PLAN                                        
-----------------------------------------------------------------------------------------
 Gather Motion 3:1  (slice1; segments: 3)  (cost=0.00..11000.00 rows=1000000 width=8)
   ->  External Scan on ext_table_no_fallback  (cost=0.00..11000.00 rows=333334 width=8)
 Optimizer: Postgres query optimizer
(3 rows)

EXPLAIN INSERT INTO heap_t1 SELECT * FROM ONLY ext_table_no_fallback;
                                           QUERY PLAN                                            
-------------------------------------------------------------------------------------------------
 Insert on heap_t1  (cost=0.00..11000.00 rows=333334 width=8)
   ->  Redistribute Motion 3:3  (slice1; segments: 3)  (cost=0.00..11000.00 rows=333334 width=8)
         Hash Key: ext_table_no_fallback.b
         ->  External Scan on ext_table_no_fallback  (cost=0.00..11000.00 rows=333334 width=8)
 Optimizer: Postgres query optimizer
(5 rows)