src/test/regress/expected/qp_query_params_optimizer.out

-- Regression tests for prepareable statements
-- Force a generic plan to specifically test parameters
set plan_cache_mode=force_generic_plan;
SET optimizer_trace_fallback to on;
create schema qp_query_params;
set search_path=qp_query_params;
create table t1 (a int, b 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.
insert into t1 select i, i from generate_series(1,4)i;
analyze t1;
create table t2(a int, b 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.
insert into t2 values (1,1);
analyze t2;
CREATE TABLE part (
    a int ,
    b int,
    c text,
    d numeric)
DISTRIBUTED BY (b)
partition by range(a) (
    start(1) end(6) every(2),
    default partition def);
insert into part select i,i,'abc',i*1.01 from generate_series(1,10)i;
analyze part;
-- Should simplify to false, Orca does not
PREPARE q1 as SELECT * from t1 where a=$1 and a!=$1;
explain (costs off) execute q1(3);
                QUERY PLAN                
------------------------------------------
 Gather Motion 3:1  (slice1; segments: 3)
   ->  Seq Scan on t1
         Filter: ((a = $1) AND (a <> $1))
 Optimizer: GPORCA
(4 rows)

execute q1(3);
 a | b 
---+---
(0 rows)

deallocate q1;
-- Should simplify to false, currently does not
PREPARE q1 as SELECT * from t1 where $1!=$1;
explain (costs off) execute q1(4);
                QUERY PLAN                
------------------------------------------
 Gather Motion 3:1  (slice1; segments: 3)
   ->  Seq Scan on t1
         Filter: ($1 <> $1)
 Optimizer: GPORCA
(4 rows)

execute q1(4);
 a | b 
---+---
(0 rows)

deallocate q1;
-- Should perform static elimination at execution time, Orca does not do this currently
PREPARE q1 as SELECT * from part where a=$1;
explain (costs off) EXECUTE q1(2);
                     QUERY PLAN                     
----------------------------------------------------
 Gather Motion 3:1  (slice1; segments: 3)
   ->  Dynamic Seq Scan on part
         Number of partitions to scan: 4 (out of 4)
         Filter: (a = $1)
 Optimizer: GPORCA
(5 rows)

execute q1(2);
 a | b |  c  |  d   
---+---+-----+------
 2 | 2 | abc | 2.02
(1 row)

-- Ensure default partition is scanned
explain (costs off) EXECUTE q1(9);
                     QUERY PLAN                     
----------------------------------------------------
 Gather Motion 3:1  (slice1; segments: 3)
   ->  Dynamic Seq Scan on part
         Number of partitions to scan: 4 (out of 4)
         Filter: (a = $1)
 Optimizer: GPORCA
(5 rows)

execute q1(9);
 a | b |  c  |  d   
---+---+-----+------
 9 | 9 | abc | 9.09
(1 row)

deallocate q1;
-- Test multiple query params
PREPARE q1 as SELECT * from t1 where a=$1 and b=$2;
explain (costs off) execute q1(4,4);
                QUERY PLAN                
------------------------------------------
 Gather Motion 3:1  (slice1; segments: 3)
   ->  Seq Scan on t1
         Filter: ((a = $1) AND (b = $2))
 Optimizer: GPORCA
(4 rows)

execute q1(4,4);
 a | b 
---+---
 4 | 4
(1 row)

deallocate q1;
-- Test param op param
PREPARE q1 as SELECT * from t1 where $1=$2;
explain (costs off) execute q1(4,5);
                QUERY PLAN                
------------------------------------------
 Gather Motion 3:1  (slice1; segments: 3)
   ->  Seq Scan on t1
         Filter: ($1 = $2)
 Optimizer: GPORCA
(4 rows)

execute q1(4,5);
 a | b 
---+---
(0 rows)

deallocate q1;
-- Should NOT produce a direct dispatch plan!
PREPARE q1 as SELECT * from t1 where a=$1;
explain (costs off) execute q1(5);
                QUERY PLAN                
------------------------------------------
 Gather Motion 3:1  (slice1; segments: 3)
   ->  Seq Scan on t1
         Filter: (a = $1)
 Optimizer: GPORCA
(4 rows)

execute q1(5);
 a | b 
---+---
(0 rows)

deallocate q1;
-- Should NOT do direct dispatch with delete. Explain doesn't show this, so we must verify results
PREPARE q1 as DELETE from t1 where a=$1;
explain (costs off) execute q1(1);
        QUERY PLAN        
--------------------------
 Delete on t1
   ->  Seq Scan on t1
         Filter: (a = $1)
 Optimizer: GPORCA
(4 rows)

execute q1(1);
deallocate q1;
select count(*) from t1;
 count 
-------
     3
(1 row)

-- Should NOT do direct dispatch with insert. Explain doesn't show this, so we must verify results
PREPARE q1 as INSERT into t1 values ($1, $2);
explain (costs off) execute q1(1,3);
        QUERY PLAN        
--------------------------
 Insert on t1
   ->  Result
         ->  Result
               ->  Result
 Optimizer: GPORCA
(5 rows)

execute q1(1,3);
deallocate q1;
select count(*) from t1;
 count 
-------
     4
(1 row)

-- Test index with parameter
CREATE INDEX idx on t1(b);
PREPARE q1 as select * from t1 where b=$1;
explain (costs off) execute q1(3);
                QUERY PLAN                
------------------------------------------
 Gather Motion 3:1  (slice1; segments: 3)
   ->  Index Scan using idx on t1
         Index Cond: (b = $1)
 Optimizer: GPORCA
(4 rows)

execute q1(3);
 a | b 
---+---
 3 | 3
 1 | 3
(2 rows)

deallocate q1;
drop index idx;
-- Test limit with parameter
PREPARE q1 as select * from t1 order by a limit $1;
explain (costs off) execute q1(4);
                   QUERY PLAN                   
------------------------------------------------
 Limit
   ->  Gather Motion 3:1  (slice1; segments: 3)
         Merge Key: a
         ->  Sort
               Sort Key: a
               ->  Seq Scan on t1
 Optimizer: GPORCA
(7 rows)

execute q1(4);
 a | b 
---+---
 1 | 3
 2 | 2
 3 | 3
 4 | 4
(4 rows)

deallocate q1;
-- Test normalization with params
PREPARE q1 as select count(*)+$1+$2 from t1 group by t1.a;
explain (verbose, costs off) execute q1(4,1);
                    QUERY PLAN                    
--------------------------------------------------
 Gather Motion 3:1  (slice1; segments: 3)
   Output: (((count(*) + $1) + $2))
   ->  GroupAggregate
         Output: ((count(*) + $1) + $2)
         Group Key: t1.a
         ->  Sort
               Output: a
               Sort Key: t1.a
               ->  Seq Scan on qp_query_params.t1
                     Output: a
 Optimizer: GPORCA
 Settings: plan_cache_mode = 'force_generic_plan'
(12 rows)

execute q1(4,1);
 ?column? 
----------
        6
        6
        6
        6
(4 rows)

deallocate q1;
PREPARE q1 as select count(*)+$1+$2 from t1 group by t1.a+$2;
explain (verbose, costs off) execute q1(3,2);
                            QUERY PLAN                            
------------------------------------------------------------------
 Gather Motion 3:1  (slice1; segments: 3)
   Output: (((count(*) + $1) + $2))
   ->  GroupAggregate
         Output: ((count(*) + $1) + $2)
         Group Key: ((t1.a + $2))
         ->  Sort
               Output: ((a + $2))
               Sort Key: ((t1.a + $2))
               ->  Redistribute Motion 3:3  (slice2; segments: 3)
                     Output: ((a + $2))
                     Hash Key: ((a + $2))
                     ->  Seq Scan on qp_query_params.t1
                           Output: (a + $2)
 Optimizer: GPORCA
 Settings: plan_cache_mode = 'force_generic_plan'
(15 rows)

execute q1(3,2);
 ?column? 
----------
        6
        6
        6
        6
(4 rows)

deallocate q1;
PREPARE q1 as select sum(b)+$1 from t1 group by(b) having count(*) > $2;
explain (verbose, costs off) execute q1(4,0);
                               QUERY PLAN                               
------------------------------------------------------------------------
 Gather Motion 3:1  (slice1; segments: 3)
   Output: (((sum(b)) + $1))
   ->  Result
         Output: ((sum(b)) + $1)
         Filter: ((count(*)) > $2)
         ->  GroupAggregate
               Output: sum(b), count(*), b
               Group Key: t1.b
               ->  Sort
                     Output: b
                     Sort Key: t1.b
                     ->  Redistribute Motion 3:3  (slice2; segments: 3)
                           Output: b
                           Hash Key: b
                           ->  Seq Scan on qp_query_params.t1
                                 Output: b
 Optimizer: GPORCA
 Settings: plan_cache_mode = 'force_generic_plan'
(18 rows)

execute q1(4,0);
 ?column? 
----------
        6
       10
        8
(3 rows)

deallocate q1;
-- Test subplans with params
PREPARE q1 as  select $1::int, (select b from t2 where b=t1.a and b!=$1) from t1;
explain (verbose, costs off) execute q1(4);
                              QUERY PLAN                               
-----------------------------------------------------------------------
 Gather Motion 3:1  (slice1; segments: 3)
   Output: ($1), ((SubPlan 1))
   ->  Seq Scan on qp_query_params.t1
         Output: $1, (SubPlan 1)
         SubPlan 1
           ->  Result
                 Output: t2.b
                 Filter: (t2.b = t1.a)
                 ->  Materialize
                       Output: t2.b
                       ->  Broadcast Motion 3:3  (slice2; segments: 3)
                             Output: t2.b
                             ->  Seq Scan on qp_query_params.t2
                                   Output: t2.b
                                   Filter: (t2.b <> $1)
 Optimizer: GPORCA
 Settings: plan_cache_mode = 'force_generic_plan'
(17 rows)

execute q1(4);
 int4 | b 
------+---
    4 |  
    4 |  
    4 |  
    4 | 1
(4 rows)

deallocate q1;