path: root/src/test/regress/expected/partition_prune.out

--
-- Test partitioning planner code
--
create table lp (a char) partition by list (a);
create table lp_default partition of lp default;
create table lp_ef partition of lp for values in ('e', 'f');
create table lp_ad partition of lp for values in ('a', 'd');
create table lp_bc partition of lp for values in ('b', 'c');
create table lp_g partition of lp for values in ('g');
create table lp_null partition of lp for values in (null);
explain (costs off) select * from lp;
          QUERY PLAN          
------------------------------
 Append
   ->  Seq Scan on lp_ad
   ->  Seq Scan on lp_bc
   ->  Seq Scan on lp_ef
   ->  Seq Scan on lp_g
   ->  Seq Scan on lp_null
   ->  Seq Scan on lp_default
(7 rows)

explain (costs off) select * from lp where a > 'a' and a < 'd';
                        QUERY PLAN                         
-----------------------------------------------------------
 Append
   ->  Seq Scan on lp_bc
         Filter: ((a > 'a'::bpchar) AND (a < 'd'::bpchar))
   ->  Seq Scan on lp_default
         Filter: ((a > 'a'::bpchar) AND (a < 'd'::bpchar))
(5 rows)

explain (costs off) select * from lp where a > 'a' and a <= 'd';
                         QUERY PLAN                         
------------------------------------------------------------
 Append
   ->  Seq Scan on lp_ad
         Filter: ((a > 'a'::bpchar) AND (a <= 'd'::bpchar))
   ->  Seq Scan on lp_bc
         Filter: ((a > 'a'::bpchar) AND (a <= 'd'::bpchar))
   ->  Seq Scan on lp_default
         Filter: ((a > 'a'::bpchar) AND (a <= 'd'::bpchar))
(7 rows)

explain (costs off) select * from lp where a = 'a';
            QUERY PLAN             
-----------------------------------
 Append
   ->  Seq Scan on lp_ad
         Filter: (a = 'a'::bpchar)
(3 rows)

explain (costs off) select * from lp where 'a' = a;	/* commuted */
            QUERY PLAN             
-----------------------------------
 Append
   ->  Seq Scan on lp_ad
         Filter: ('a'::bpchar = a)
(3 rows)

explain (costs off) select * from lp where a is not null;
           QUERY PLAN            
---------------------------------
 Append
   ->  Seq Scan on lp_ad
         Filter: (a IS NOT NULL)
   ->  Seq Scan on lp_bc
         Filter: (a IS NOT NULL)
   ->  Seq Scan on lp_ef
         Filter: (a IS NOT NULL)
   ->  Seq Scan on lp_g
         Filter: (a IS NOT NULL)
   ->  Seq Scan on lp_default
         Filter: (a IS NOT NULL)
(11 rows)

explain (costs off) select * from lp where a is null;
         QUERY PLAN          
-----------------------------
 Append
   ->  Seq Scan on lp_null
         Filter: (a IS NULL)
(3 rows)

explain (costs off) select * from lp where a = 'a' or a = 'c';
                        QUERY PLAN                        
----------------------------------------------------------
 Append
   ->  Seq Scan on lp_ad
         Filter: ((a = 'a'::bpchar) OR (a = 'c'::bpchar))
   ->  Seq Scan on lp_bc
         Filter: ((a = 'a'::bpchar) OR (a = 'c'::bpchar))
(5 rows)

explain (costs off) select * from lp where a is not null and (a = 'a' or a = 'c');
                                   QUERY PLAN                                   
--------------------------------------------------------------------------------
 Append
   ->  Seq Scan on lp_ad
         Filter: ((a IS NOT NULL) AND ((a = 'a'::bpchar) OR (a = 'c'::bpchar)))
   ->  Seq Scan on lp_bc
         Filter: ((a IS NOT NULL) AND ((a = 'a'::bpchar) OR (a = 'c'::bpchar)))
(5 rows)

explain (costs off) select * from lp where a <> 'g';
             QUERY PLAN             
------------------------------------
 Append
   ->  Seq Scan on lp_ad
         Filter: (a <> 'g'::bpchar)
   ->  Seq Scan on lp_bc
         Filter: (a <> 'g'::bpchar)
   ->  Seq Scan on lp_ef
         Filter: (a <> 'g'::bpchar)
   ->  Seq Scan on lp_default
         Filter: (a <> 'g'::bpchar)
(9 rows)

explain (costs off) select * from lp where a <> 'a' and a <> 'd';
                         QUERY PLAN                          
-------------------------------------------------------------
 Append
   ->  Seq Scan on lp_bc
         Filter: ((a <> 'a'::bpchar) AND (a <> 'd'::bpchar))
   ->  Seq Scan on lp_ef
         Filter: ((a <> 'a'::bpchar) AND (a <> 'd'::bpchar))
   ->  Seq Scan on lp_g
         Filter: ((a <> 'a'::bpchar) AND (a <> 'd'::bpchar))
   ->  Seq Scan on lp_default
         Filter: ((a <> 'a'::bpchar) AND (a <> 'd'::bpchar))
(9 rows)

explain (costs off) select * from lp where a not in ('a', 'd');
                   QUERY PLAN                   
------------------------------------------------
 Append
   ->  Seq Scan on lp_bc
         Filter: (a <> ALL ('{a,d}'::bpchar[]))
   ->  Seq Scan on lp_ef
         Filter: (a <> ALL ('{a,d}'::bpchar[]))
   ->  Seq Scan on lp_g
         Filter: (a <> ALL ('{a,d}'::bpchar[]))
   ->  Seq Scan on lp_default
         Filter: (a <> ALL ('{a,d}'::bpchar[]))
(9 rows)

-- collation matches the partitioning collation, pruning works
create table coll_pruning (a text collate "C") partition by list (a);
create table coll_pruning_a partition of coll_pruning for values in ('a');
create table coll_pruning_b partition of coll_pruning for values in ('b');
create table coll_pruning_def partition of coll_pruning default;
explain (costs off) select * from coll_pruning where a collate "C" = 'a' collate "C";
                 QUERY PLAN                  
---------------------------------------------
 Append
   ->  Seq Scan on coll_pruning_a
         Filter: (a = 'a'::text COLLATE "C")
(3 rows)

-- collation doesn't match the partitioning collation, no pruning occurs
explain (costs off) select * from coll_pruning where a collate "POSIX" = 'a' collate "POSIX";
                       QUERY PLAN                        
---------------------------------------------------------
 Append
   ->  Seq Scan on coll_pruning_a
         Filter: ((a)::text = 'a'::text COLLATE "POSIX")
   ->  Seq Scan on coll_pruning_b
         Filter: ((a)::text = 'a'::text COLLATE "POSIX")
   ->  Seq Scan on coll_pruning_def
         Filter: ((a)::text = 'a'::text COLLATE "POSIX")
(7 rows)

create table rlp (a int, b varchar) partition by range (a);
create table rlp_default partition of rlp default partition by list (a);
create table rlp_default_default partition of rlp_default default;
create table rlp_default_10 partition of rlp_default for values in (10);
create table rlp_default_30 partition of rlp_default for values in (30);
create table rlp_default_null partition of rlp_default for values in (null);
create table rlp1 partition of rlp for values from (minvalue) to (1);
create table rlp2 partition of rlp for values from (1) to (10);
create table rlp3 (b varchar, a int) partition by list (b varchar_ops);
create table rlp3_default partition of rlp3 default;
create table rlp3abcd partition of rlp3 for values in ('ab', 'cd');
create table rlp3efgh partition of rlp3 for values in ('ef', 'gh');
create table rlp3nullxy partition of rlp3 for values in (null, 'xy');
alter table rlp attach partition rlp3 for values from (15) to (20);
create table rlp4 partition of rlp for values from (20) to (30) partition by range (a);
create table rlp4_default partition of rlp4 default;
create table rlp4_1 partition of rlp4 for values from (20) to (25);
create table rlp4_2 partition of rlp4 for values from (25) to (29);
create table rlp5 partition of rlp for values from (31) to (maxvalue) partition by range (a);
create table rlp5_default partition of rlp5 default;
create table rlp5_1 partition of rlp5 for values from (31) to (40);
explain (costs off) select * from rlp where a < 1;
       QUERY PLAN        
-------------------------
 Append
   ->  Seq Scan on rlp1
         Filter: (a < 1)
(3 rows)

explain (costs off) select * from rlp where 1 > a;	/* commuted */
       QUERY PLAN        
-------------------------
 Append
   ->  Seq Scan on rlp1
         Filter: (1 > a)
(3 rows)

explain (costs off) select * from rlp where a <= 1;
        QUERY PLAN        
--------------------------
 Append
   ->  Seq Scan on rlp1
         Filter: (a <= 1)
   ->  Seq Scan on rlp2
         Filter: (a <= 1)
(5 rows)

explain (costs off) select * from rlp where a = 1;
       QUERY PLAN        
-------------------------
 Append
   ->  Seq Scan on rlp2
         Filter: (a = 1)
(3 rows)

explain (costs off) select * from rlp where a = 1::bigint;		/* same as above */
            QUERY PLAN             
-----------------------------------
 Append
   ->  Seq Scan on rlp2
         Filter: (a = '1'::bigint)
(3 rows)

explain (costs off) select * from rlp where a = 1::numeric;		/* no pruning */
                  QUERY PLAN                   
-----------------------------------------------
 Append
   ->  Seq Scan on rlp1
         Filter: ((a)::numeric = '1'::numeric)
   ->  Seq Scan on rlp2
         Filter: ((a)::numeric = '1'::numeric)
   ->  Seq Scan on rlp3abcd
         Filter: ((a)::numeric = '1'::numeric)
   ->  Seq Scan on rlp3efgh
         Filter: ((a)::numeric = '1'::numeric)
   ->  Seq Scan on rlp3nullxy
         Filter: ((a)::numeric = '1'::numeric)
   ->  Seq Scan on rlp3_default
         Filter: ((a)::numeric = '1'::numeric)
   ->  Seq Scan on rlp4_1
         Filter: ((a)::numeric = '1'::numeric)
   ->  Seq Scan on rlp4_2
         Filter: ((a)::numeric = '1'::numeric)
   ->  Seq Scan on rlp4_default
         Filter: ((a)::numeric = '1'::numeric)
   ->  Seq Scan on rlp5_1
         Filter: ((a)::numeric = '1'::numeric)
   ->  Seq Scan on rlp5_default
         Filter: ((a)::numeric = '1'::numeric)
   ->  Seq Scan on rlp_default_10
         Filter: ((a)::numeric = '1'::numeric)
   ->  Seq Scan on rlp_default_30
         Filter: ((a)::numeric = '1'::numeric)
   ->  Seq Scan on rlp_default_null
         Filter: ((a)::numeric = '1'::numeric)
   ->  Seq Scan on rlp_default_default
         Filter: ((a)::numeric = '1'::numeric)
(31 rows)

explain (costs off) select * from rlp where a <= 10;
              QUERY PLAN               
---------------------------------------
 Append
   ->  Seq Scan on rlp1
         Filter: (a <= 10)
   ->  Seq Scan on rlp2
         Filter: (a <= 10)
   ->  Seq Scan on rlp_default_10
         Filter: (a <= 10)
   ->  Seq Scan on rlp_default_default
         Filter: (a <= 10)
(9 rows)

explain (costs off) select * from rlp where a > 10;
              QUERY PLAN               
---------------------------------------
 Append
   ->  Seq Scan on rlp3abcd
         Filter: (a > 10)
   ->  Seq Scan on rlp3efgh
         Filter: (a > 10)
   ->  Seq Scan on rlp3nullxy
         Filter: (a > 10)
   ->  Seq Scan on rlp3_default
         Filter: (a > 10)
   ->  Seq Scan on rlp4_1
         Filter: (a > 10)
   ->  Seq Scan on rlp4_2
         Filter: (a > 10)
   ->  Seq Scan on rlp4_default
         Filter: (a > 10)
   ->  Seq Scan on rlp5_1
         Filter: (a > 10)
   ->  Seq Scan on rlp5_default
         Filter: (a > 10)
   ->  Seq Scan on rlp_default_30
         Filter: (a > 10)
   ->  Seq Scan on rlp_default_default
         Filter: (a > 10)
(23 rows)

explain (costs off) select * from rlp where a < 15;
              QUERY PLAN               
---------------------------------------
 Append
   ->  Seq Scan on rlp1
         Filter: (a < 15)
   ->  Seq Scan on rlp2
         Filter: (a < 15)
   ->  Seq Scan on rlp_default_10
         Filter: (a < 15)
   ->  Seq Scan on rlp_default_default
         Filter: (a < 15)
(9 rows)

explain (costs off) select * from rlp where a <= 15;
              QUERY PLAN               
---------------------------------------
 Append
   ->  Seq Scan on rlp1
         Filter: (a <= 15)
   ->  Seq Scan on rlp2
         Filter: (a <= 15)
   ->  Seq Scan on rlp3abcd
         Filter: (a <= 15)
   ->  Seq Scan on rlp3efgh
         Filter: (a <= 15)
   ->  Seq Scan on rlp3nullxy
         Filter: (a <= 15)
   ->  Seq Scan on rlp3_default
         Filter: (a <= 15)
   ->  Seq Scan on rlp_default_10
         Filter: (a <= 15)
   ->  Seq Scan on rlp_default_default
         Filter: (a <= 15)
(17 rows)

explain (costs off) select * from rlp where a > 15 and b = 'ab';
                       QUERY PLAN                        
---------------------------------------------------------
 Append
   ->  Seq Scan on rlp3abcd
         Filter: ((a > 15) AND ((b)::text = 'ab'::text))
   ->  Seq Scan on rlp4_1
         Filter: ((a > 15) AND ((b)::text = 'ab'::text))
   ->  Seq Scan on rlp4_2
         Filter: ((a > 15) AND ((b)::text = 'ab'::text))
   ->  Seq Scan on rlp4_default
         Filter: ((a > 15) AND ((b)::text = 'ab'::text))
   ->  Seq Scan on rlp5_1
         Filter: ((a > 15) AND ((b)::text = 'ab'::text))
   ->  Seq Scan on rlp5_default
         Filter: ((a > 15) AND ((b)::text = 'ab'::text))
   ->  Seq Scan on rlp_default_30
         Filter: ((a > 15) AND ((b)::text = 'ab'::text))
   ->  Seq Scan on rlp_default_default
         Filter: ((a > 15) AND ((b)::text = 'ab'::text))
(17 rows)

explain (costs off) select * from rlp where a = 16;
           QUERY PLAN           
--------------------------------
 Append
   ->  Seq Scan on rlp3abcd
         Filter: (a = 16)
   ->  Seq Scan on rlp3efgh
         Filter: (a = 16)
   ->  Seq Scan on rlp3nullxy
         Filter: (a = 16)
   ->  Seq Scan on rlp3_default
         Filter: (a = 16)
(9 rows)

explain (costs off) select * from rlp where a = 16 and b in ('not', 'in', 'here');
                                 QUERY PLAN                                 
----------------------------------------------------------------------------
 Append
   ->  Seq Scan on rlp3_default
         Filter: ((a = 16) AND ((b)::text = ANY ('{not,in,here}'::text[])))
(3 rows)

explain (costs off) select * from rlp where a = 16 and b < 'ab';
                       QUERY PLAN                        
---------------------------------------------------------
 Append
   ->  Seq Scan on rlp3_default
         Filter: (((b)::text < 'ab'::text) AND (a = 16))
(3 rows)

explain (costs off) select * from rlp where a = 16 and b <= 'ab';
                        QUERY PLAN                        
----------------------------------------------------------
 Append
   ->  Seq Scan on rlp3abcd
         Filter: (((b)::text <= 'ab'::text) AND (a = 16))
   ->  Seq Scan on rlp3_default
         Filter: (((b)::text <= 'ab'::text) AND (a = 16))
(5 rows)

explain (costs off) select * from rlp where a = 16 and b is null;
                 QUERY PLAN                 
--------------------------------------------
 Append
   ->  Seq Scan on rlp3nullxy
         Filter: ((b IS NULL) AND (a = 16))
(3 rows)

explain (costs off) select * from rlp where a = 16 and b is not null;
                   QUERY PLAN                   
------------------------------------------------
 Append
   ->  Seq Scan on rlp3abcd
         Filter: ((b IS NOT NULL) AND (a = 16))
   ->  Seq Scan on rlp3efgh
         Filter: ((b IS NOT NULL) AND (a = 16))
   ->  Seq Scan on rlp3nullxy
         Filter: ((b IS NOT NULL) AND (a = 16))
   ->  Seq Scan on rlp3_default
         Filter: ((b IS NOT NULL) AND (a = 16))
(9 rows)

explain (costs off) select * from rlp where a is null;
             QUERY PLAN             
------------------------------------
 Append
   ->  Seq Scan on rlp_default_null
         Filter: (a IS NULL)
(3 rows)

explain (costs off) select * from rlp where a is not null;
              QUERY PLAN               
---------------------------------------
 Append
   ->  Seq Scan on rlp1
         Filter: (a IS NOT NULL)
   ->  Seq Scan on rlp2
         Filter: (a IS NOT NULL)
   ->  Seq Scan on rlp3abcd
         Filter: (a IS NOT NULL)
   ->  Seq Scan on rlp3efgh
         Filter: (a IS NOT NULL)
   ->  Seq Scan on rlp3nullxy
         Filter: (a IS NOT NULL)
   ->  Seq Scan on rlp3_default
         Filter: (a IS NOT NULL)
   ->  Seq Scan on rlp4_1
         Filter: (a IS NOT NULL)
   ->  Seq Scan on rlp4_2
         Filter: (a IS NOT NULL)
   ->  Seq Scan on rlp4_default
         Filter: (a IS NOT NULL)
   ->  Seq Scan on rlp5_1
         Filter: (a IS NOT NULL)
   ->  Seq Scan on rlp5_default
         Filter: (a IS NOT NULL)
   ->  Seq Scan on rlp_default_10
         Filter: (a IS NOT NULL)
   ->  Seq Scan on rlp_default_30
         Filter: (a IS NOT NULL)
   ->  Seq Scan on rlp_default_default
         Filter: (a IS NOT NULL)
(29 rows)

explain (costs off) select * from rlp where a > 30;
              QUERY PLAN               
---------------------------------------
 Append
   ->  Seq Scan on rlp5_1
         Filter: (a > 30)
   ->  Seq Scan on rlp5_default
         Filter: (a > 30)
   ->  Seq Scan on rlp_default_default
         Filter: (a > 30)
(7 rows)

explain (costs off) select * from rlp where a = 30;	/* only default is scanned */
            QUERY PLAN            
----------------------------------
 Append
   ->  Seq Scan on rlp_default_30
         Filter: (a = 30)
(3 rows)

explain (costs off) select * from rlp where a <= 31;
              QUERY PLAN               
---------------------------------------
 Append
   ->  Seq Scan on rlp1
         Filter: (a <= 31)
   ->  Seq Scan on rlp2
         Filter: (a <= 31)
   ->  Seq Scan on rlp3abcd
         Filter: (a <= 31)
   ->  Seq Scan on rlp3efgh
         Filter: (a <= 31)
   ->  Seq Scan on rlp3nullxy
         Filter: (a <= 31)
   ->  Seq Scan on rlp3_default
         Filter: (a <= 31)
   ->  Seq Scan on rlp4_1
         Filter: (a <= 31)
   ->  Seq Scan on rlp4_2
         Filter: (a <= 31)
   ->  Seq Scan on rlp4_default
         Filter: (a <= 31)
   ->  Seq Scan on rlp5_1
         Filter: (a <= 31)
   ->  Seq Scan on rlp5_default
         Filter: (a <= 31)
   ->  Seq Scan on rlp_default_10
         Filter: (a <= 31)
   ->  Seq Scan on rlp_default_30
         Filter: (a <= 31)
   ->  Seq Scan on rlp_default_default
         Filter: (a <= 31)
(29 rows)

explain (costs off) select * from rlp where a = 1 or a = 7;
              QUERY PLAN              
--------------------------------------
 Append
   ->  Seq Scan on rlp2
         Filter: ((a = 1) OR (a = 7))
(3 rows)

explain (costs off) select * from rlp where a = 1 or b = 'ab';
                      QUERY PLAN                       
-------------------------------------------------------
 Append
   ->  Seq Scan on rlp1
         Filter: ((a = 1) OR ((b)::text = 'ab'::text))
   ->  Seq Scan on rlp2
         Filter: ((a = 1) OR ((b)::text = 'ab'::text))
   ->  Seq Scan on rlp3abcd
         Filter: ((a = 1) OR ((b)::text = 'ab'::text))
   ->  Seq Scan on rlp4_1
         Filter: ((a = 1) OR ((b)::text = 'ab'::text))
   ->  Seq Scan on rlp4_2
         Filter: ((a = 1) OR ((b)::text = 'ab'::text))
   ->  Seq Scan on rlp4_default
         Filter: ((a = 1) OR ((b)::text = 'ab'::text))
   ->  Seq Scan on rlp5_1
         Filter: ((a = 1) OR ((b)::text = 'ab'::text))
   ->  Seq Scan on rlp5_default
         Filter: ((a = 1) OR ((b)::text = 'ab'::text))
   ->  Seq Scan on rlp_default_10
         Filter: ((a = 1) OR ((b)::text = 'ab'::text))
   ->  Seq Scan on rlp_default_30
         Filter: ((a = 1) OR ((b)::text = 'ab'::text))
   ->  Seq Scan on rlp_default_null
         Filter: ((a = 1) OR ((b)::text = 'ab'::text))
   ->  Seq Scan on rlp_default_default
         Filter: ((a = 1) OR ((b)::text = 'ab'::text))
(25 rows)

explain (costs off) select * from rlp where a > 20 and a < 27;
               QUERY PLAN                
-----------------------------------------
 Append
   ->  Seq Scan on rlp4_1
         Filter: ((a > 20) AND (a < 27))
   ->  Seq Scan on rlp4_2
         Filter: ((a > 20) AND (a < 27))
   ->  Seq Scan on rlp4_default
         Filter: ((a > 20) AND (a < 27))
   ->  Seq Scan on rlp_default_default
         Filter: ((a > 20) AND (a < 27))
(9 rows)

explain (costs off) select * from rlp where a = 29;
           QUERY PLAN           
--------------------------------
 Append
   ->  Seq Scan on rlp4_default
         Filter: (a = 29)
(3 rows)

explain (costs off) select * from rlp where a >= 29;
              QUERY PLAN               
---------------------------------------
 Append
   ->  Seq Scan on rlp4_default
         Filter: (a >= 29)
   ->  Seq Scan on rlp5_1
         Filter: (a >= 29)
   ->  Seq Scan on rlp5_default
         Filter: (a >= 29)
   ->  Seq Scan on rlp_default_30
         Filter: (a >= 29)
   ->  Seq Scan on rlp_default_default
         Filter: (a >= 29)
(11 rows)

-- redundant clauses are eliminated
explain (costs off) select * from rlp where a > 1 and a = 10;	/* only default */
               QUERY PLAN               
----------------------------------------
 Append
   ->  Seq Scan on rlp_default_10
         Filter: ((a > 1) AND (a = 10))
(3 rows)

explain (costs off) select * from rlp where a > 1 and a >=15;	/* rlp3 onwards, including default */
               QUERY PLAN                
-----------------------------------------
 Append
   ->  Seq Scan on rlp3abcd
         Filter: ((a > 1) AND (a >= 15))
   ->  Seq Scan on rlp3efgh
         Filter: ((a > 1) AND (a >= 15))
   ->  Seq Scan on rlp3nullxy
         Filter: ((a > 1) AND (a >= 15))
   ->  Seq Scan on rlp3_default
         Filter: ((a > 1) AND (a >= 15))
   ->  Seq Scan on rlp4_1
         Filter: ((a > 1) AND (a >= 15))
   ->  Seq Scan on rlp4_2
         Filter: ((a > 1) AND (a >= 15))
   ->  Seq Scan on rlp4_default
         Filter: ((a > 1) AND (a >= 15))
   ->  Seq Scan on rlp5_1
         Filter: ((a > 1) AND (a >= 15))
   ->  Seq Scan on rlp5_default
         Filter: ((a > 1) AND (a >= 15))
   ->  Seq Scan on rlp_default_30
         Filter: ((a > 1) AND (a >= 15))
   ->  Seq Scan on rlp_default_default
         Filter: ((a > 1) AND (a >= 15))
(23 rows)

explain (costs off) select * from rlp where a = 1 and a = 3;	/* empty */
        QUERY PLAN        
--------------------------
 Result
   One-Time Filter: false
(2 rows)

explain (costs off) select * from rlp where (a = 1 and a = 3) or (a > 1 and a = 15);
                            QUERY PLAN                             
-------------------------------------------------------------------
 Append
   ->  Seq Scan on rlp2
         Filter: (((a = 1) AND (a = 3)) OR ((a > 1) AND (a = 15)))
   ->  Seq Scan on rlp3abcd
         Filter: (((a = 1) AND (a = 3)) OR ((a > 1) AND (a = 15)))
   ->  Seq Scan on rlp3efgh
         Filter: (((a = 1) AND (a = 3)) OR ((a > 1) AND (a = 15)))
   ->  Seq Scan on rlp3nullxy
         Filter: (((a = 1) AND (a = 3)) OR ((a > 1) AND (a = 15)))
   ->  Seq Scan on rlp3_default
         Filter: (((a = 1) AND (a = 3)) OR ((a > 1) AND (a = 15)))
(11 rows)

-- multi-column keys
create table mc3p (a int, b int, c int) partition by range (a, abs(b), c);
create table mc3p_default partition of mc3p default;
create table mc3p0 partition of mc3p for values from (minvalue, minvalue, minvalue) to (1, 1, 1);
create table mc3p1 partition of mc3p for values from (1, 1, 1) to (10, 5, 10);
create table mc3p2 partition of mc3p for values from (10, 5, 10) to (10, 10, 10);
create table mc3p3 partition of mc3p for values from (10, 10, 10) to (10, 10, 20);
create table mc3p4 partition of mc3p for values from (10, 10, 20) to (10, maxvalue, maxvalue);
create table mc3p5 partition of mc3p for values from (11, 1, 1) to (20, 10, 10);
create table mc3p6 partition of mc3p for values from (20, 10, 10) to (20, 20, 20);
create table mc3p7 partition of mc3p for values from (20, 20, 20) to (maxvalue, maxvalue, maxvalue);
explain (costs off) select * from mc3p where a = 1;
           QUERY PLAN           
--------------------------------
 Append
   ->  Seq Scan on mc3p0
         Filter: (a = 1)
   ->  Seq Scan on mc3p1
         Filter: (a = 1)
   ->  Seq Scan on mc3p_default
         Filter: (a = 1)
(7 rows)

explain (costs off) select * from mc3p where a = 1 and abs(b) < 1;
                 QUERY PLAN                 
--------------------------------------------
 Append
   ->  Seq Scan on mc3p0
         Filter: ((a = 1) AND (abs(b) < 1))
   ->  Seq Scan on mc3p_default
         Filter: ((a = 1) AND (abs(b) < 1))
(5 rows)

explain (costs off) select * from mc3p where a = 1 and abs(b) = 1;
                 QUERY PLAN                 
--------------------------------------------
 Append
   ->  Seq Scan on mc3p0
         Filter: ((a = 1) AND (abs(b) = 1))
   ->  Seq Scan on mc3p1
         Filter: ((a = 1) AND (abs(b) = 1))
   ->  Seq Scan on mc3p_default
         Filter: ((a = 1) AND (abs(b) = 1))
(7 rows)

explain (costs off) select * from mc3p where a = 1 and abs(b) = 1 and c < 8;
                       QUERY PLAN                       
--------------------------------------------------------
 Append
   ->  Seq Scan on mc3p0
         Filter: ((c < 8) AND (a = 1) AND (abs(b) = 1))
   ->  Seq Scan on mc3p1
         Filter: ((c < 8) AND (a = 1) AND (abs(b) = 1))
(5 rows)

explain (costs off) select * from mc3p where a = 10 and abs(b) between 5 and 35;
                           QUERY PLAN                            
-----------------------------------------------------------------
 Append
   ->  Seq Scan on mc3p1
         Filter: ((a = 10) AND (abs(b) >= 5) AND (abs(b) <= 35))
   ->  Seq Scan on mc3p2
         Filter: ((a = 10) AND (abs(b) >= 5) AND (abs(b) <= 35))
   ->  Seq Scan on mc3p3
         Filter: ((a = 10) AND (abs(b) >= 5) AND (abs(b) <= 35))
   ->  Seq Scan on mc3p4
         Filter: ((a = 10) AND (abs(b) >= 5) AND (abs(b) <= 35))
   ->  Seq Scan on mc3p_default
         Filter: ((a = 10) AND (abs(b) >= 5) AND (abs(b) <= 35))
(11 rows)

explain (costs off) select * from mc3p where a > 10;
           QUERY PLAN           
--------------------------------
 Append
   ->  Seq Scan on mc3p5
         Filter: (a > 10)
   ->  Seq Scan on mc3p6
         Filter: (a > 10)
   ->  Seq Scan on mc3p7
         Filter: (a > 10)
   ->  Seq Scan on mc3p_default
         Filter: (a > 10)
(9 rows)

explain (costs off) select * from mc3p where a >= 10;
           QUERY PLAN           
--------------------------------
 Append
   ->  Seq Scan on mc3p1
         Filter: (a >= 10)
   ->  Seq Scan on mc3p2
         Filter: (a >= 10)
   ->  Seq Scan on mc3p3
         Filter: (a >= 10)
   ->  Seq Scan on mc3p4
         Filter: (a >= 10)
   ->  Seq Scan on mc3p5
         Filter: (a >= 10)
   ->  Seq Scan on mc3p6
         Filter: (a >= 10)
   ->  Seq Scan on mc3p7
         Filter: (a >= 10)
   ->  Seq Scan on mc3p_default
         Filter: (a >= 10)
(17 rows)

explain (costs off) select * from mc3p where a < 10;
           QUERY PLAN           
--------------------------------
 Append
   ->  Seq Scan on mc3p0
         Filter: (a < 10)
   ->  Seq Scan on mc3p1
         Filter: (a < 10)
   ->  Seq Scan on mc3p_default
         Filter: (a < 10)
(7 rows)

explain (costs off) select * from mc3p where a <= 10 and abs(b) < 10;
                  QUERY PLAN                   
-----------------------------------------------
 Append
   ->  Seq Scan on mc3p0
         Filter: ((a <= 10) AND (abs(b) < 10))
   ->  Seq Scan on mc3p1
         Filter: ((a <= 10) AND (abs(b) < 10))
   ->  Seq Scan on mc3p2
         Filter: ((a <= 10) AND (abs(b) < 10))
   ->  Seq Scan on mc3p_default
         Filter: ((a <= 10) AND (abs(b) < 10))
(9 rows)

explain (costs off) select * from mc3p where a = 11 and abs(b) = 0;
                 QUERY PLAN                  
---------------------------------------------
 Append
   ->  Seq Scan on mc3p_default
         Filter: ((a = 11) AND (abs(b) = 0))
(3 rows)

explain (costs off) select * from mc3p where a = 20 and abs(b) = 10 and c = 100;
                         QUERY PLAN                         
------------------------------------------------------------
 Append
   ->  Seq Scan on mc3p6
         Filter: ((a = 20) AND (c = 100) AND (abs(b) = 10))
(3 rows)

explain (costs off) select * from mc3p where a > 20;
           QUERY PLAN           
--------------------------------
 Append
   ->  Seq Scan on mc3p7
         Filter: (a > 20)
   ->  Seq Scan on mc3p_default
         Filter: (a > 20)
(5 rows)

explain (costs off) select * from mc3p where a >= 20;
           QUERY PLAN           
--------------------------------
 Append
   ->  Seq Scan on mc3p5
         Filter: (a >= 20)
   ->  Seq Scan on mc3p6
         Filter: (a >= 20)
   ->  Seq Scan on mc3p7
         Filter: (a >= 20)
   ->  Seq Scan on mc3p_default
         Filter: (a >= 20)
(9 rows)

explain (costs off) select * from mc3p where (a = 1 and abs(b) = 1 and c = 1) or (a = 10 and abs(b) = 5 and c = 10) or (a > 11 and a < 20);
                                                           QUERY PLAN                                                            
---------------------------------------------------------------------------------------------------------------------------------
 Append
   ->  Seq Scan on mc3p1
         Filter: (((a = 1) AND (abs(b) = 1) AND (c = 1)) OR ((a = 10) AND (abs(b) = 5) AND (c = 10)) OR ((a > 11) AND (a < 20)))
   ->  Seq Scan on mc3p2
         Filter: (((a = 1) AND (abs(b) = 1) AND (c = 1)) OR ((a = 10) AND (abs(b) = 5) AND (c = 10)) OR ((a > 11) AND (a < 20)))
   ->  Seq Scan on mc3p5
         Filter: (((a = 1) AND (abs(b) = 1) AND (c = 1)) OR ((a = 10) AND (abs(b) = 5) AND (c = 10)) OR ((a > 11) AND (a < 20)))
   ->  Seq Scan on mc3p_default
         Filter: (((a = 1) AND (abs(b) = 1) AND (c = 1)) OR ((a = 10) AND (abs(b) = 5) AND (c = 10)) OR ((a > 11) AND (a < 20)))
(9 rows)

explain (costs off) select * from mc3p where (a = 1 and abs(b) = 1 and c = 1) or (a = 10 and abs(b) = 5 and c = 10) or (a > 11 and a < 20) or a < 1;
                                                                 QUERY PLAN                                                                 
--------------------------------------------------------------------------------------------------------------------------------------------
 Append
   ->  Seq Scan on mc3p0
         Filter: (((a = 1) AND (abs(b) = 1) AND (c = 1)) OR ((a = 10) AND (abs(b) = 5) AND (c = 10)) OR ((a > 11) AND (a < 20)) OR (a < 1))
   ->  Seq Scan on mc3p1
         Filter: (((a = 1) AND (abs(b) = 1) AND (c = 1)) OR ((a = 10) AND (abs(b) = 5) AND (c = 10)) OR ((a > 11) AND (a < 20)) OR (a < 1))
   ->  Seq Scan on mc3p2
         Filter: (((a = 1) AND (abs(b) = 1) AND (c = 1)) OR ((a = 10) AND (abs(b) = 5) AND (c = 10)) OR ((a > 11) AND (a < 20)) OR (a < 1))
   ->  Seq Scan on mc3p5
         Filter: (((a = 1) AND (abs(b) = 1) AND (c = 1)) OR ((a = 10) AND (abs(b) = 5) AND (c = 10)) OR ((a > 11) AND (a < 20)) OR (a < 1))
   ->  Seq Scan on mc3p_default
         Filter: (((a = 1) AND (abs(b) = 1) AND (c = 1)) OR ((a = 10) AND (abs(b) = 5) AND (c = 10)) OR ((a > 11) AND (a < 20)) OR (a < 1))
(11 rows)

explain (costs off) select * from mc3p where (a = 1 and abs(b) = 1 and c = 1) or (a = 10 and abs(b) = 5 and c = 10) or (a > 11 and a < 20) or a < 1 or a = 1;
                                                                      QUERY PLAN                                                                       
-------------------------------------------------------------------------------------------------------------------------------------------------------
 Append
   ->  Seq Scan on mc3p0
         Filter: (((a = 1) AND (abs(b) = 1) AND (c = 1)) OR ((a = 10) AND (abs(b) = 5) AND (c = 10)) OR ((a > 11) AND (a < 20)) OR (a < 1) OR (a = 1))
   ->  Seq Scan on mc3p1
         Filter: (((a = 1) AND (abs(b) = 1) AND (c = 1)) OR ((a = 10) AND (abs(b) = 5) AND (c = 10)) OR ((a > 11) AND (a < 20)) OR (a < 1) OR (a = 1))
   ->  Seq Scan on mc3p2
         Filter: (((a = 1) AND (abs(b) = 1) AND (c = 1)) OR ((a = 10) AND (abs(b) = 5) AND (c = 10)) OR ((a > 11) AND (a < 20)) OR (a < 1) OR (a = 1))
   ->  Seq Scan on mc3p5
         Filter: (((a = 1) AND (abs(b) = 1) AND (c = 1)) OR ((a = 10) AND (abs(b) = 5) AND (c = 10)) OR ((a > 11) AND (a < 20)) OR (a < 1) OR (a = 1))
   ->  Seq Scan on mc3p_default
         Filter: (((a = 1) AND (abs(b) = 1) AND (c = 1)) OR ((a = 10) AND (abs(b) = 5) AND (c = 10)) OR ((a > 11) AND (a < 20)) OR (a < 1) OR (a = 1))
(11 rows)

explain (costs off) select * from mc3p where a = 1 or abs(b) = 1 or c = 1;
                      QUERY PLAN                      
------------------------------------------------------
 Append
   ->  Seq Scan on mc3p0
         Filter: ((a = 1) OR (abs(b) = 1) OR (c = 1))
   ->  Seq Scan on mc3p1
         Filter: ((a = 1) OR (abs(b) = 1) OR (c = 1))
   ->  Seq Scan on mc3p2
         Filter: ((a = 1) OR (abs(b) = 1) OR (c = 1))
   ->  Seq Scan on mc3p3
         Filter: ((a = 1) OR (abs(b) = 1) OR (c = 1))
   ->  Seq Scan on mc3p4
         Filter: ((a = 1) OR (abs(b) = 1) OR (c = 1))
   ->  Seq Scan on mc3p5
         Filter: ((a = 1) OR (abs(b) = 1) OR (c = 1))
   ->  Seq Scan on mc3p6
         Filter: ((a = 1) OR (abs(b) = 1) OR (c = 1))
   ->  Seq Scan on mc3p7
         Filter: ((a = 1) OR (abs(b) = 1) OR (c = 1))
   ->  Seq Scan on mc3p_default
         Filter: ((a = 1) OR (abs(b) = 1) OR (c = 1))
(19 rows)

explain (costs off) select * from mc3p where (a = 1 and abs(b) = 1) or (a = 10 and abs(b) = 10);
                                  QUERY PLAN                                  
------------------------------------------------------------------------------
 Append
   ->  Seq Scan on mc3p0
         Filter: (((a = 1) AND (abs(b) = 1)) OR ((a = 10) AND (abs(b) = 10)))
   ->  Seq Scan on mc3p1
         Filter: (((a = 1) AND (abs(b) = 1)) OR ((a = 10) AND (abs(b) = 10)))
   ->  Seq Scan on mc3p2
         Filter: (((a = 1) AND (abs(b) = 1)) OR ((a = 10) AND (abs(b) = 10)))
   ->  Seq Scan on mc3p3
         Filter: (((a = 1) AND (abs(b) = 1)) OR ((a = 10) AND (abs(b) = 10)))
   ->  Seq Scan on mc3p4
         Filter: (((a = 1) AND (abs(b) = 1)) OR ((a = 10) AND (abs(b) = 10)))
   ->  Seq Scan on mc3p_default
         Filter: (((a = 1) AND (abs(b) = 1)) OR ((a = 10) AND (abs(b) = 10)))
(13 rows)

explain (costs off) select * from mc3p where (a = 1 and abs(b) = 1) or (a = 10 and abs(b) = 9);
                                 QUERY PLAN                                  
-----------------------------------------------------------------------------
 Append
   ->  Seq Scan on mc3p0
         Filter: (((a = 1) AND (abs(b) = 1)) OR ((a = 10) AND (abs(b) = 9)))
   ->  Seq Scan on mc3p1
         Filter: (((a = 1) AND (abs(b) = 1)) OR ((a = 10) AND (abs(b) = 9)))
   ->  Seq Scan on mc3p2
         Filter: (((a = 1) AND (abs(b) = 1)) OR ((a = 10) AND (abs(b) = 9)))
   ->  Seq Scan on mc3p_default
         Filter: (((a = 1) AND (abs(b) = 1)) OR ((a = 10) AND (abs(b) = 9)))
(9 rows)

-- a simpler multi-column keys case
create table mc2p (a int, b int) partition by range (a, b);
create table mc2p_default partition of mc2p default;
create table mc2p0 partition of mc2p for values from (minvalue, minvalue) to (1, minvalue);
create table mc2p1 partition of mc2p for values from (1, minvalue) to (1, 1);
create table mc2p2 partition of mc2p for values from (1, 1) to (2, minvalue);
create table mc2p3 partition of mc2p for values from (2, minvalue) to (2, 1);
create table mc2p4 partition of mc2p for values from (2, 1) to (2, maxvalue);
create table mc2p5 partition of mc2p for values from (2, maxvalue) to (maxvalue, maxvalue);
explain (costs off) select * from mc2p where a < 2;
           QUERY PLAN           
--------------------------------
 Append
   ->  Seq Scan on mc2p0
         Filter: (a < 2)
   ->  Seq Scan on mc2p1
         Filter: (a < 2)
   ->  Seq Scan on mc2p2
         Filter: (a < 2)
   ->  Seq Scan on mc2p_default
         Filter: (a < 2)
(9 rows)

explain (costs off) select * from mc2p where a = 2 and b < 1;
              QUERY PLAN               
---------------------------------------
 Append
   ->  Seq Scan on mc2p3
         Filter: ((b < 1) AND (a = 2))
(3 rows)

explain (costs off) select * from mc2p where a > 1;
           QUERY PLAN           
--------------------------------
 Append
   ->  Seq Scan on mc2p2
         Filter: (a > 1)
   ->  Seq Scan on mc2p3
         Filter: (a > 1)
   ->  Seq Scan on mc2p4
         Filter: (a > 1)
   ->  Seq Scan on mc2p5
         Filter: (a > 1)
   ->  Seq Scan on mc2p_default
         Filter: (a > 1)
(11 rows)

explain (costs off) select * from mc2p where a = 1 and b > 1;
              QUERY PLAN               
---------------------------------------
 Append
   ->  Seq Scan on mc2p2
         Filter: ((b > 1) AND (a = 1))
(3 rows)

-- boolean partitioning
create table boolpart (a bool) partition by list (a);
create table boolpart_default partition of boolpart default;
create table boolpart_t partition of boolpart for values in ('true');
create table boolpart_f partition of boolpart for values in ('false');
explain (costs off) select * from boolpart where a in (true, false);
                   QUERY PLAN                   
------------------------------------------------
 Append
   ->  Seq Scan on boolpart_f
         Filter: (a = ANY ('{t,f}'::boolean[]))
   ->  Seq Scan on boolpart_t
         Filter: (a = ANY ('{t,f}'::boolean[]))
(5 rows)

explain (costs off) select * from boolpart where a = false;
          QUERY PLAN          
------------------------------
 Append
   ->  Seq Scan on boolpart_f
         Filter: (NOT a)
(3 rows)

explain (costs off) select * from boolpart where not a = false;
          QUERY PLAN          
------------------------------
 Append
   ->  Seq Scan on boolpart_t
         Filter: a
(3 rows)

explain (costs off) select * from boolpart where a is true or a is not true;
                    QUERY PLAN                    
--------------------------------------------------
 Append
   ->  Seq Scan on boolpart_f
         Filter: ((a IS TRUE) OR (a IS NOT TRUE))
   ->  Seq Scan on boolpart_t
         Filter: ((a IS TRUE) OR (a IS NOT TRUE))
(5 rows)

explain (costs off) select * from boolpart where a is not true;
           QUERY PLAN            
---------------------------------
 Append
   ->  Seq Scan on boolpart_f
         Filter: (a IS NOT TRUE)
(3 rows)

explain (costs off) select * from boolpart where a is not true and a is not false;
        QUERY PLAN        
--------------------------
 Result
   One-Time Filter: false
(2 rows)

explain (costs off) select * from boolpart where a is unknown;
             QUERY PLAN             
------------------------------------
 Append
   ->  Seq Scan on boolpart_f
         Filter: (a IS UNKNOWN)
   ->  Seq Scan on boolpart_t
         Filter: (a IS UNKNOWN)
   ->  Seq Scan on boolpart_default
         Filter: (a IS UNKNOWN)
(7 rows)

explain (costs off) select * from boolpart where a is not unknown;
             QUERY PLAN             
------------------------------------
 Append
   ->  Seq Scan on boolpart_f
         Filter: (a IS NOT UNKNOWN)
   ->  Seq Scan on boolpart_t
         Filter: (a IS NOT UNKNOWN)
   ->  Seq Scan on boolpart_default
         Filter: (a IS NOT UNKNOWN)
(7 rows)

--
-- some more cases
--
--
-- pruning for partitioned table appearing inside a sub-query
--
-- pruning won't work for mc3p, because some keys are Params
explain (costs off) select * from mc2p t1, lateral (select count(*) from mc3p t2 where t2.a = t1.b and abs(t2.b) = 1 and t2.c = 1) s where t1.a = 1;
                              QUERY PLAN                               
-----------------------------------------------------------------------
 Nested Loop
   ->  Append
         ->  Seq Scan on mc2p1 t1
               Filter: (a = 1)
         ->  Seq Scan on mc2p2 t1_1
               Filter: (a = 1)
         ->  Seq Scan on mc2p_default t1_2
               Filter: (a = 1)
   ->  Aggregate
         ->  Append
               ->  Seq Scan on mc3p0 t2
                     Filter: ((a = t1.b) AND (c = 1) AND (abs(b) = 1))
               ->  Seq Scan on mc3p1 t2_1
                     Filter: ((a = t1.b) AND (c = 1) AND (abs(b) = 1))
               ->  Seq Scan on mc3p2 t2_2
                     Filter: ((a = t1.b) AND (c = 1) AND (abs(b) = 1))
               ->  Seq Scan on mc3p3 t2_3
                     Filter: ((a = t1.b) AND (c = 1) AND (abs(b) = 1))
               ->  Seq Scan on mc3p4 t2_4
                     Filter: ((a = t1.b) AND (c = 1) AND (abs(b) = 1))
               ->  Seq Scan on mc3p5 t2_5
                     Filter: ((a = t1.b) AND (c = 1) AND (abs(b) = 1))
               ->  Seq Scan on mc3p6 t2_6
                     Filter: ((a = t1.b) AND (c = 1) AND (abs(b) = 1))
               ->  Seq Scan on mc3p7 t2_7
                     Filter: ((a = t1.b) AND (c = 1) AND (abs(b) = 1))
               ->  Seq Scan on mc3p_default t2_8
                     Filter: ((a = t1.b) AND (c = 1) AND (abs(b) = 1))
(28 rows)

-- pruning should work fine, because values for a prefix of keys (a, b) are
-- available
explain (costs off) select * from mc2p t1, lateral (select count(*) from mc3p t2 where t2.c = t1.b and abs(t2.b) = 1 and t2.a = 1) s where t1.a = 1;
                              QUERY PLAN                               
-----------------------------------------------------------------------
 Nested Loop
   ->  Append
         ->  Seq Scan on mc2p1 t1
               Filter: (a = 1)
         ->  Seq Scan on mc2p2 t1_1
               Filter: (a = 1)
         ->  Seq Scan on mc2p_default t1_2
               Filter: (a = 1)
   ->  Aggregate
         ->  Append
               ->  Seq Scan on mc3p0 t2
                     Filter: ((c = t1.b) AND (a = 1) AND (abs(b) = 1))
               ->  Seq Scan on mc3p1 t2_1
                     Filter: ((c = t1.b) AND (a = 1) AND (abs(b) = 1))
               ->  Seq Scan on mc3p_default t2_2
                     Filter: ((c = t1.b) AND (a = 1) AND (abs(b) = 1))
(16 rows)

-- also here, because values for all keys are provided
explain (costs off) select * from mc2p t1, lateral (select count(*) from mc3p t2 where t2.a = 1 and abs(t2.b) = 1 and t2.c = 1) s where t1.a = 1;
                             QUERY PLAN                             
--------------------------------------------------------------------
 Nested Loop
   ->  Aggregate
         ->  Append
               ->  Seq Scan on mc3p1 t2
                     Filter: ((a = 1) AND (c = 1) AND (abs(b) = 1))
   ->  Append
         ->  Seq Scan on mc2p1 t1
               Filter: (a = 1)
         ->  Seq Scan on mc2p2 t1_1
               Filter: (a = 1)
         ->  Seq Scan on mc2p_default t1_2
               Filter: (a = 1)
(12 rows)

--
-- pruning with clauses containing <> operator
--
-- doesn't prune range partitions
create table rp (a int) partition by range (a);
create table rp0 partition of rp for values from (minvalue) to (1);
create table rp1 partition of rp for values from (1) to (2);
create table rp2 partition of rp for values from (2) to (maxvalue);
explain (costs off) select * from rp where a <> 1;
        QUERY PLAN        
--------------------------
 Append
   ->  Seq Scan on rp0
         Filter: (a <> 1)
   ->  Seq Scan on rp1
         Filter: (a <> 1)
   ->  Seq Scan on rp2
         Filter: (a <> 1)
(7 rows)

explain (costs off) select * from rp where a <> 1 and a <> 2;
               QUERY PLAN                
-----------------------------------------
 Append
   ->  Seq Scan on rp0
         Filter: ((a <> 1) AND (a <> 2))
   ->  Seq Scan on rp1
         Filter: ((a <> 1) AND (a <> 2))
   ->  Seq Scan on rp2
         Filter: ((a <> 1) AND (a <> 2))
(7 rows)

-- null partition should be eliminated due to strict <> clause.
explain (costs off) select * from lp where a <> 'a';
             QUERY PLAN             
------------------------------------
 Append
   ->  Seq Scan on lp_ad
         Filter: (a <> 'a'::bpchar)
   ->  Seq Scan on lp_bc
         Filter: (a <> 'a'::bpchar)
   ->  Seq Scan on lp_ef
         Filter: (a <> 'a'::bpchar)
   ->  Seq Scan on lp_g
         Filter: (a <> 'a'::bpchar)
   ->  Seq Scan on lp_default
         Filter: (a <> 'a'::bpchar)
(11 rows)

-- ensure we detect contradictions in clauses; a can't be NULL and NOT NULL.
explain (costs off) select * from lp where a <> 'a' and a is null;
        QUERY PLAN        
--------------------------
 Result
   One-Time Filter: false
(2 rows)

explain (costs off) select * from lp where (a <> 'a' and a <> 'd') or a is null;
                                  QUERY PLAN                                  
------------------------------------------------------------------------------
 Append
   ->  Seq Scan on lp_bc
         Filter: (((a <> 'a'::bpchar) AND (a <> 'd'::bpchar)) OR (a IS NULL))
   ->  Seq Scan on lp_ef
         Filter: (((a <> 'a'::bpchar) AND (a <> 'd'::bpchar)) OR (a IS NULL))
   ->  Seq Scan on lp_g
         Filter: (((a <> 'a'::bpchar) AND (a <> 'd'::bpchar)) OR (a IS NULL))
   ->  Seq Scan on lp_null
         Filter: (((a <> 'a'::bpchar) AND (a <> 'd'::bpchar)) OR (a IS NULL))
   ->  Seq Scan on lp_default
         Filter: (((a <> 'a'::bpchar) AND (a <> 'd'::bpchar)) OR (a IS NULL))
(11 rows)

-- check that it also works for a partitioned table that's not root,
-- which in this case are partitions of rlp that are themselves
-- list-partitioned on b
explain (costs off) select * from rlp where a = 15 and b <> 'ab' and b <> 'cd' and b <> 'xy' and b is not null;
                                                                QUERY PLAN                                                                
------------------------------------------------------------------------------------------------------------------------------------------
 Append
   ->  Seq Scan on rlp3efgh
         Filter: ((b IS NOT NULL) AND ((b)::text <> 'ab'::text) AND ((b)::text <> 'cd'::text) AND ((b)::text <> 'xy'::text) AND (a = 15))
   ->  Seq Scan on rlp3_default
         Filter: ((b IS NOT NULL) AND ((b)::text <> 'ab'::text) AND ((b)::text <> 'cd'::text) AND ((b)::text <> 'xy'::text) AND (a = 15))
(5 rows)

--
-- different collations for different keys with same expression
--
create table coll_pruning_multi (a text) partition by range (substr(a, 1) collate "POSIX", substr(a, 1) collate "C");
create table coll_pruning_multi1 partition of coll_pruning_multi for values from ('a', 'a') to ('a', 'e');
create table coll_pruning_multi2 partition of coll_pruning_multi for values from ('a', 'e') to ('a', 'z');
create table coll_pruning_multi3 partition of coll_pruning_multi for values from ('b', 'a') to ('b', 'e');
-- no pruning, because no value for the leading key
explain (costs off) select * from coll_pruning_multi where substr(a, 1) = 'e' collate "C";
                       QUERY PLAN                       
--------------------------------------------------------
 Append
   ->  Seq Scan on coll_pruning_multi1
         Filter: (substr(a, 1) = 'e'::text COLLATE "C")
   ->  Seq Scan on coll_pruning_multi2
         Filter: (substr(a, 1) = 'e'::text COLLATE "C")
   ->  Seq Scan on coll_pruning_multi3
         Filter: (substr(a, 1) = 'e'::text COLLATE "C")
(7 rows)

-- pruning, with a value provided for the leading key
explain (costs off) select * from coll_pruning_multi where substr(a, 1) = 'a' collate "POSIX";
                         QUERY PLAN                         
------------------------------------------------------------
 Append
   ->  Seq Scan on coll_pruning_multi1
         Filter: (substr(a, 1) = 'a'::text COLLATE "POSIX")
   ->  Seq Scan on coll_pruning_multi2
         Filter: (substr(a, 1) = 'a'::text COLLATE "POSIX")
(5 rows)

-- pruning, with values provided for both keys
explain (costs off) select * from coll_pruning_multi where substr(a, 1) = 'e' collate "C" and substr(a, 1) = 'a' collate "POSIX";
                                               QUERY PLAN                                                
---------------------------------------------------------------------------------------------------------
 Append
   ->  Seq Scan on coll_pruning_multi2
         Filter: ((substr(a, 1) = 'e'::text COLLATE "C") AND (substr(a, 1) = 'a'::text COLLATE "POSIX"))
(3 rows)

--
-- LIKE operators don't prune
--
create table like_op_noprune (a text) partition by list (a);
create table like_op_noprune1 partition of like_op_noprune for values in ('ABC');
create table like_op_noprune2 partition of like_op_noprune for values in ('BCD');
explain (costs off) select * from like_op_noprune where a like '%BC';
             QUERY PLAN             
------------------------------------
 Append
   ->  Seq Scan on like_op_noprune1
         Filter: (a ~~ '%BC'::text)
   ->  Seq Scan on like_op_noprune2
         Filter: (a ~~ '%BC'::text)
(5 rows)

--
-- tests wherein clause value requires a cross-type comparison function
--
create table lparted_by_int2 (a smallint) partition by list (a);
create table lparted_by_int2_1 partition of lparted_by_int2 for values in (1);
create table lparted_by_int2_16384 partition of lparted_by_int2 for values in (16384);
explain (costs off) select * from lparted_by_int2 where a = 100000000000000;
        QUERY PLAN        
--------------------------
 Result
   One-Time Filter: false
(2 rows)

create table rparted_by_int2 (a smallint) partition by range (a);
create table rparted_by_int2_1 partition of rparted_by_int2 for values from (1) to (10);
create table rparted_by_int2_16384 partition of rparted_by_int2 for values from (10) to (16384);
-- all partitions pruned
explain (costs off) select * from rparted_by_int2 where a > 100000000000000;
        QUERY PLAN        
--------------------------
 Result
   One-Time Filter: false
(2 rows)

create table rparted_by_int2_maxvalue partition of rparted_by_int2 for values from (16384) to (maxvalue);
-- all partitions but rparted_by_int2_maxvalue pruned
explain (costs off) select * from rparted_by_int2 where a > 100000000000000;
                   QUERY PLAN                    
-------------------------------------------------
 Append
   ->  Seq Scan on rparted_by_int2_maxvalue
         Filter: (a > '100000000000000'::bigint)
(3 rows)

drop table lp, coll_pruning, rlp, mc3p, mc2p, boolpart, rp, coll_pruning_multi, like_op_noprune, lparted_by_int2, rparted_by_int2;
--
-- Test runtime partition pruning
--
create table ab (a int not null, b int not null) partition by list (a);
create table ab_a2 partition of ab for values in(2) partition by list (b);
create table ab_a2_b1 partition of ab_a2 for values in (1);
create table ab_a2_b2 partition of ab_a2 for values in (2);
create table ab_a2_b3 partition of ab_a2 for values in (3);
create table ab_a1 partition of ab for values in(1) partition by list (b);
create table ab_a1_b1 partition of ab_a1 for values in (1);
create table ab_a1_b2 partition of ab_a1 for values in (2);
create table ab_a1_b3 partition of ab_a1 for values in (3);
create table ab_a3 partition of ab for values in(3) partition by list (b);
create table ab_a3_b1 partition of ab_a3 for values in (1);
create table ab_a3_b2 partition of ab_a3 for values in (2);
create table ab_a3_b3 partition of ab_a3 for values in (3);
prepare ab_q1 (int, int, int) as
select * from ab where a between $1 and $2 and b <= $3;
-- Execute query 5 times to allow choose_custom_plan
-- to start considering a generic plan.
execute ab_q1 (1, 8, 3);
 a | b 
---+---
(0 rows)

execute ab_q1 (1, 8, 3);
 a | b 
---+---
(0 rows)

execute ab_q1 (1, 8, 3);
 a | b 
---+---
(0 rows)

execute ab_q1 (1, 8, 3);
 a | b 
---+---
(0 rows)

execute ab_q1 (1, 8, 3);
 a | b 
---+---
(0 rows)

explain (analyze, costs off, summary off, timing off) execute ab_q1 (2, 2, 3);
                       QUERY PLAN                        
---------------------------------------------------------
 Append (actual rows=0 loops=1)
   Subplans Removed: 6
   ->  Seq Scan on ab_a2_b1 (actual rows=0 loops=1)
         Filter: ((a >= $1) AND (a <= $2) AND (b <= $3))
   ->  Seq Scan on ab_a2_b2 (actual rows=0 loops=1)
         Filter: ((a >= $1) AND (a <= $2) AND (b <= $3))
   ->  Seq Scan on ab_a2_b3 (actual rows=0 loops=1)
         Filter: ((a >= $1) AND (a <= $2) AND (b <= $3))
(8 rows)

explain (analyze, costs off, summary off, timing off) execute ab_q1 (1, 2, 3);
                       QUERY PLAN                        
---------------------------------------------------------
 Append (actual rows=0 loops=1)
   Subplans Removed: 3
   ->  Seq Scan on ab_a1_b1 (actual rows=0 loops=1)
         Filter: ((a >= $1) AND (a <= $2) AND (b <= $3))
   ->  Seq Scan on ab_a1_b2 (actual rows=0 loops=1)
         Filter: ((a >= $1) AND (a <= $2) AND (b <= $3))
   ->  Seq Scan on ab_a1_b3 (actual rows=0 loops=1)
         Filter: ((a >= $1) AND (a <= $2) AND (b <= $3))
   ->  Seq Scan on ab_a2_b1 (actual rows=0 loops=1)
         Filter: ((a >= $1) AND (a <= $2) AND (b <= $3))
   ->  Seq Scan on ab_a2_b2 (actual rows=0 loops=1)
         Filter: ((a >= $1) AND (a <= $2) AND (b <= $3))
   ->  Seq Scan on ab_a2_b3 (actual rows=0 loops=1)
         Filter: ((a >= $1) AND (a <= $2) AND (b <= $3))
(14 rows)

deallocate ab_q1;
-- Runtime pruning after optimizer pruning
prepare ab_q1 (int, int) as
select a from ab where a between $1 and $2 and b < 3;
-- Execute query 5 times to allow choose_custom_plan
-- to start considering a generic plan.
execute ab_q1 (1, 8);
 a 
---
(0 rows)

execute ab_q1 (1, 8);
 a 
---
(0 rows)

execute ab_q1 (1, 8);
 a 
---
(0 rows)

execute ab_q1 (1, 8);
 a 
---
(0 rows)

execute ab_q1 (1, 8);
 a 
---
(0 rows)

explain (analyze, costs off, summary off, timing off) execute ab_q1 (2, 2);
                      QUERY PLAN                       
-------------------------------------------------------
 Append (actual rows=0 loops=1)
   Subplans Removed: 4
   ->  Seq Scan on ab_a2_b1 (actual rows=0 loops=1)
         Filter: ((a >= $1) AND (a <= $2) AND (b < 3))
   ->  Seq Scan on ab_a2_b2 (actual rows=0 loops=1)
         Filter: ((a >= $1) AND (a <= $2) AND (b < 3))
(6 rows)

explain (analyze, costs off, summary off, timing off) execute ab_q1 (2, 4);
                      QUERY PLAN                       
-------------------------------------------------------
 Append (actual rows=0 loops=1)
   Subplans Removed: 2
   ->  Seq Scan on ab_a2_b1 (actual rows=0 loops=1)
         Filter: ((a >= $1) AND (a <= $2) AND (b < 3))
   ->  Seq Scan on ab_a2_b2 (actual rows=0 loops=1)
         Filter: ((a >= $1) AND (a <= $2) AND (b < 3))
   ->  Seq Scan on ab_a3_b1 (actual rows=0 loops=1)
         Filter: ((a >= $1) AND (a <= $2) AND (b < 3))
   ->  Seq Scan on ab_a3_b2 (actual rows=0 loops=1)
         Filter: ((a >= $1) AND (a <= $2) AND (b < 3))
(10 rows)

-- Ensure a mix of external and exec params work together at different
-- levels of partitioning.
prepare ab_q2 (int, int) as
select a from ab where a between $1 and $2 and b < (select 3);
execute ab_q2 (1, 8);
 a 
---
(0 rows)

execute ab_q2 (1, 8);
 a 
---
(0 rows)

execute ab_q2 (1, 8);
 a 
---
(0 rows)

execute ab_q2 (1, 8);
 a 
---
(0 rows)

execute ab_q2 (1, 8);
 a 
---
(0 rows)

explain (analyze, costs off, summary off, timing off) execute ab_q2 (2, 2);
                       QUERY PLAN                       
--------------------------------------------------------
 Append (actual rows=0 loops=1)
   InitPlan 1 (returns $0)
     ->  Result (actual rows=1 loops=1)
   Subplans Removed: 6
   ->  Seq Scan on ab_a2_b1 (actual rows=0 loops=1)
         Filter: ((a >= $1) AND (a <= $2) AND (b < $0))
   ->  Seq Scan on ab_a2_b2 (actual rows=0 loops=1)
         Filter: ((a >= $1) AND (a <= $2) AND (b < $0))
   ->  Seq Scan on ab_a2_b3 (never executed)
         Filter: ((a >= $1) AND (a <= $2) AND (b < $0))
(10 rows)

-- As above, but with swap the exec param to the first partition level
prepare ab_q3 (int, int) as
select a from ab where b between $1 and $2 and a < (select 3);
execute ab_q3 (1, 8);
 a 
---
(0 rows)

execute ab_q3 (1, 8);
 a 
---
(0 rows)

execute ab_q3 (1, 8);
 a 
---
(0 rows)

execute ab_q3 (1, 8);
 a 
---
(0 rows)

execute ab_q3 (1, 8);
 a 
---
(0 rows)

explain (analyze, costs off, summary off, timing off) execute ab_q3 (2, 2);
                       QUERY PLAN                       
--------------------------------------------------------
 Append (actual rows=0 loops=1)
   InitPlan 1 (returns $0)
     ->  Result (actual rows=1 loops=1)
   Subplans Removed: 6
   ->  Seq Scan on ab_a1_b2 (actual rows=0 loops=1)
         Filter: ((b >= $1) AND (b <= $2) AND (a < $0))
   ->  Seq Scan on ab_a2_b2 (actual rows=0 loops=1)
         Filter: ((b >= $1) AND (b <= $2) AND (a < $0))
   ->  Seq Scan on ab_a3_b2 (never executed)
         Filter: ((b >= $1) AND (b <= $2) AND (a < $0))
(10 rows)

-- Parallel append
prepare ab_q4 (int, int) as
select avg(a) from ab where a between $1 and $2 and b < 4;
-- Encourage use of parallel plans
set parallel_setup_cost = 0;
set parallel_tuple_cost = 0;
set min_parallel_table_scan_size = 0;
set max_parallel_workers_per_gather = 2;
-- Execute query 5 times to allow choose_custom_plan
-- to start considering a generic plan.
execute ab_q4 (1, 8);
 avg 
-----
    
(1 row)

execute ab_q4 (1, 8);
 avg 
-----
    
(1 row)

execute ab_q4 (1, 8);
 avg 
-----
    
(1 row)

execute ab_q4 (1, 8);
 avg 
-----
    
(1 row)

execute ab_q4 (1, 8);
 avg 
-----
    
(1 row)

explain (analyze, costs off, summary off, timing off) execute ab_q4 (2, 2);
                                  QUERY PLAN                                   
-------------------------------------------------------------------------------
 Finalize Aggregate (actual rows=1 loops=1)
   ->  Gather (actual rows=3 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         ->  Partial Aggregate (actual rows=1 loops=3)
               ->  Parallel Append (actual rows=0 loops=3)
                     Subplans Removed: 6
                     ->  Parallel Seq Scan on ab_a2_b1 (actual rows=0 loops=1)
                           Filter: ((a >= $1) AND (a <= $2) AND (b < 4))
                     ->  Parallel Seq Scan on ab_a2_b2 (actual rows=0 loops=1)
                           Filter: ((a >= $1) AND (a <= $2) AND (b < 4))
                     ->  Parallel Seq Scan on ab_a2_b3 (actual rows=0 loops=1)
                           Filter: ((a >= $1) AND (a <= $2) AND (b < 4))
(13 rows)

-- Test run-time pruning with IN lists.
prepare ab_q5 (int, int, int) as
select avg(a) from ab where a in($1,$2,$3) and b < 4;
-- Execute query 5 times to allow choose_custom_plan
-- to start considering a generic plan.
execute ab_q5 (1, 2, 3);
 avg 
-----
    
(1 row)

execute ab_q5 (1, 2, 3);
 avg 
-----
    
(1 row)

execute ab_q5 (1, 2, 3);
 avg 
-----
    
(1 row)

execute ab_q5 (1, 2, 3);
 avg 
-----
    
(1 row)

execute ab_q5 (1, 2, 3);
 avg 
-----
    
(1 row)

explain (analyze, costs off, summary off, timing off) execute ab_q5 (1, 1, 1);
                                  QUERY PLAN                                   
-------------------------------------------------------------------------------
 Finalize Aggregate (actual rows=1 loops=1)
   ->  Gather (actual rows=3 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         ->  Partial Aggregate (actual rows=1 loops=3)
               ->  Parallel Append (actual rows=0 loops=3)
                     Subplans Removed: 6
                     ->  Parallel Seq Scan on ab_a1_b1 (actual rows=0 loops=1)
                           Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3])))
                     ->  Parallel Seq Scan on ab_a1_b2 (actual rows=0 loops=1)
                           Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3])))
                     ->  Parallel Seq Scan on ab_a1_b3 (actual rows=0 loops=1)
                           Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3])))
(13 rows)

explain (analyze, costs off, summary off, timing off) execute ab_q5 (2, 3, 3);
                                  QUERY PLAN                                   
-------------------------------------------------------------------------------
 Finalize Aggregate (actual rows=1 loops=1)
   ->  Gather (actual rows=3 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         ->  Partial Aggregate (actual rows=1 loops=3)
               ->  Parallel Append (actual rows=0 loops=3)
                     Subplans Removed: 3
                     ->  Parallel Seq Scan on ab_a2_b1 (actual rows=0 loops=1)
                           Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3])))
                     ->  Parallel Seq Scan on ab_a2_b2 (actual rows=0 loops=1)
                           Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3])))
                     ->  Parallel Seq Scan on ab_a2_b3 (actual rows=0 loops=1)
                           Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3])))
                     ->  Parallel Seq Scan on ab_a3_b1 (actual rows=0 loops=1)
                           Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3])))
                     ->  Parallel Seq Scan on ab_a3_b2 (actual rows=0 loops=1)
                           Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3])))
                     ->  Parallel Seq Scan on ab_a3_b3 (actual rows=0 loops=1)
                           Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3])))
(19 rows)

-- Try some params whose values do not belong to any partition.
-- We'll still get a single subplan in this case, but it should not be scanned.
explain (analyze, costs off, summary off, timing off) execute ab_q5 (33, 44, 55);
                                  QUERY PLAN                                   
-------------------------------------------------------------------------------
 Finalize Aggregate (actual rows=1 loops=1)
   ->  Gather (actual rows=3 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         ->  Partial Aggregate (actual rows=1 loops=3)
               ->  Parallel Append (actual rows=0 loops=3)
                     Subplans Removed: 8
                     ->  Parallel Seq Scan on ab_a1_b1 (never executed)
                           Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3])))
(9 rows)

-- Test parallel Append with IN list and parameterized nested loops
create table lprt_a (a int not null);
-- Insert some values we won't find in ab
insert into lprt_a select 0 from generate_series(1,100);
-- and insert some values that we should find.
insert into lprt_a values(1),(1);
analyze lprt_a;
create index ab_a2_b1_a_idx on ab_a2_b1 (a);
create index ab_a2_b2_a_idx on ab_a2_b2 (a);
create index ab_a2_b3_a_idx on ab_a2_b3 (a);
create index ab_a1_b1_a_idx on ab_a1_b1 (a);
create index ab_a1_b2_a_idx on ab_a1_b2 (a);
create index ab_a1_b3_a_idx on ab_a1_b3 (a);
create index ab_a3_b1_a_idx on ab_a3_b1 (a);
create index ab_a3_b2_a_idx on ab_a3_b2 (a);
create index ab_a3_b3_a_idx on ab_a3_b3 (a);
set enable_hashjoin = 0;
set enable_mergejoin = 0;
prepare ab_q6 (int, int, int) as
select avg(ab.a) from ab inner join lprt_a a on ab.a = a.a where a.a in($1,$2,$3);
execute ab_q6 (1, 2, 3);
 avg 
-----
    
(1 row)

execute ab_q6 (1, 2, 3);
 avg 
-----
    
(1 row)

execute ab_q6 (1, 2, 3);
 avg 
-----
    
(1 row)

execute ab_q6 (1, 2, 3);
 avg 
-----
    
(1 row)

execute ab_q6 (1, 2, 3);
 avg 
-----
    
(1 row)

explain (analyze, costs off, summary off, timing off) execute ab_q6 (0, 0, 1);
                                               QUERY PLAN                                               
--------------------------------------------------------------------------------------------------------
 Finalize Aggregate (actual rows=1 loops=1)
   ->  Gather (actual rows=2 loops=1)
         Workers Planned: 1
         Workers Launched: 1
         ->  Partial Aggregate (actual rows=1 loops=2)
               ->  Nested Loop (actual rows=0 loops=2)
                     ->  Parallel Seq Scan on lprt_a a (actual rows=51 loops=2)
                           Filter: (a = ANY ('{0,0,1}'::integer[]))
                     ->  Append (actual rows=0 loops=102)
                           ->  Index Only Scan using ab_a1_b1_a_idx on ab_a1_b1 (actual rows=0 loops=2)
                                 Index Cond: (a = a.a)
                                 Heap Fetches: 0
                           ->  Index Only Scan using ab_a1_b2_a_idx on ab_a1_b2 (actual rows=0 loops=2)
                                 Index Cond: (a = a.a)
                                 Heap Fetches: 0
                           ->  Index Only Scan using ab_a1_b3_a_idx on ab_a1_b3 (actual rows=0 loops=2)
                                 Index Cond: (a = a.a)
                                 Heap Fetches: 0
                           ->  Index Only Scan using ab_a2_b1_a_idx on ab_a2_b1 (never executed)
                                 Index Cond: (a = a.a)
                                 Heap Fetches: 0
                           ->  Index Only Scan using ab_a2_b2_a_idx on ab_a2_b2 (never executed)
                                 Index Cond: (a = a.a)
                                 Heap Fetches: 0
                           ->  Index Only Scan using ab_a2_b3_a_idx on ab_a2_b3 (never executed)
                                 Index Cond: (a = a.a)
                                 Heap Fetches: 0
                           ->  Index Only Scan using ab_a3_b1_a_idx on ab_a3_b1 (never executed)
                                 Index Cond: (a = a.a)
                                 Heap Fetches: 0
                           ->  Index Only Scan using ab_a3_b2_a_idx on ab_a3_b2 (never executed)
                                 Index Cond: (a = a.a)
                                 Heap Fetches: 0
                           ->  Index Only Scan using ab_a3_b3_a_idx on ab_a3_b3 (never executed)
                                 Index Cond: (a = a.a)
                                 Heap Fetches: 0
(36 rows)

insert into lprt_a values(3),(3);
explain (analyze, costs off, summary off, timing off) execute ab_q6 (1, 0, 3);
                                               QUERY PLAN                                               
--------------------------------------------------------------------------------------------------------
 Finalize Aggregate (actual rows=1 loops=1)
   ->  Gather (actual rows=2 loops=1)
         Workers Planned: 1
         Workers Launched: 1
         ->  Partial Aggregate (actual rows=1 loops=2)
               ->  Nested Loop (actual rows=0 loops=2)
                     ->  Parallel Seq Scan on lprt_a a (actual rows=52 loops=2)
                           Filter: (a = ANY ('{1,0,3}'::integer[]))
                     ->  Append (actual rows=0 loops=104)
                           ->  Index Only Scan using ab_a1_b1_a_idx on ab_a1_b1 (actual rows=0 loops=2)
                                 Index Cond: (a = a.a)
                                 Heap Fetches: 0
                           ->  Index Only Scan using ab_a1_b2_a_idx on ab_a1_b2 (actual rows=0 loops=2)
                                 Index Cond: (a = a.a)
                                 Heap Fetches: 0
                           ->  Index Only Scan using ab_a1_b3_a_idx on ab_a1_b3 (actual rows=0 loops=2)
                                 Index Cond: (a = a.a)
                                 Heap Fetches: 0
                           ->  Index Only Scan using ab_a2_b1_a_idx on ab_a2_b1 (never executed)
                                 Index Cond: (a = a.a)
                                 Heap Fetches: 0
                           ->  Index Only Scan using ab_a2_b2_a_idx on ab_a2_b2 (never executed)
                                 Index Cond: (a = a.a)
                                 Heap Fetches: 0
                           ->  Index Only Scan using ab_a2_b3_a_idx on ab_a2_b3 (never executed)
                                 Index Cond: (a = a.a)
                                 Heap Fetches: 0
                           ->  Index Only Scan using ab_a3_b1_a_idx on ab_a3_b1 (actual rows=0 loops=2)
                                 Index Cond: (a = a.a)
                                 Heap Fetches: 0
                           ->  Index Only Scan using ab_a3_b2_a_idx on ab_a3_b2 (actual rows=0 loops=2)
                                 Index Cond: (a = a.a)
                                 Heap Fetches: 0
                           ->  Index Only Scan using ab_a3_b3_a_idx on ab_a3_b3 (actual rows=0 loops=2)
                                 Index Cond: (a = a.a)
                                 Heap Fetches: 0
(36 rows)

explain (analyze, costs off, summary off, timing off) execute ab_q6 (1, 0, 0);
                                               QUERY PLAN                                               
--------------------------------------------------------------------------------------------------------
 Finalize Aggregate (actual rows=1 loops=1)
   ->  Gather (actual rows=2 loops=1)
         Workers Planned: 1
         Workers Launched: 1
         ->  Partial Aggregate (actual rows=1 loops=2)
               ->  Nested Loop (actual rows=0 loops=2)
                     ->  Parallel Seq Scan on lprt_a a (actual rows=51 loops=2)
                           Filter: (a = ANY ('{1,0,0}'::integer[]))
                           Rows Removed by Filter: 1
                     ->  Append (actual rows=0 loops=102)
                           ->  Index Only Scan using ab_a1_b1_a_idx on ab_a1_b1 (actual rows=0 loops=2)
                                 Index Cond: (a = a.a)
                                 Heap Fetches: 0
                           ->  Index Only Scan using ab_a1_b2_a_idx on ab_a1_b2 (actual rows=0 loops=2)
                                 Index Cond: (a = a.a)
                                 Heap Fetches: 0
                           ->  Index Only Scan using ab_a1_b3_a_idx on ab_a1_b3 (actual rows=0 loops=2)
                                 Index Cond: (a = a.a)
                                 Heap Fetches: 0
                           ->  Index Only Scan using ab_a2_b1_a_idx on ab_a2_b1 (never executed)
                                 Index Cond: (a = a.a)
                                 Heap Fetches: 0
                           ->  Index Only Scan using ab_a2_b2_a_idx on ab_a2_b2 (never executed)
                                 Index Cond: (a = a.a)
                                 Heap Fetches: 0
                           ->  Index Only Scan using ab_a2_b3_a_idx on ab_a2_b3 (never executed)
                                 Index Cond: (a = a.a)
                                 Heap Fetches: 0
                           ->  Index Only Scan using ab_a3_b1_a_idx on ab_a3_b1 (never executed)
                                 Index Cond: (a = a.a)
                                 Heap Fetches: 0
                           ->  Index Only Scan using ab_a3_b2_a_idx on ab_a3_b2 (never executed)
                                 Index Cond: (a = a.a)
                                 Heap Fetches: 0
                           ->  Index Only Scan using ab_a3_b3_a_idx on ab_a3_b3 (never executed)
                                 Index Cond: (a = a.a)
                                 Heap Fetches: 0
(37 rows)

delete from lprt_a where a = 1;
explain (analyze, costs off, summary off, timing off) execute ab_q6 (1, 0, 0);
                                           QUERY PLAN                                            
-------------------------------------------------------------------------------------------------
 Finalize Aggregate (actual rows=1 loops=1)
   ->  Gather (actual rows=2 loops=1)
         Workers Planned: 1
         Workers Launched: 1
         ->  Partial Aggregate (actual rows=1 loops=2)
               ->  Nested Loop (actual rows=0 loops=2)
                     ->  Parallel Seq Scan on lprt_a a (actual rows=50 loops=2)
                           Filter: (a = ANY ('{1,0,0}'::integer[]))
                           Rows Removed by Filter: 1
                     ->  Append (actual rows=0 loops=100)
                           ->  Index Only Scan using ab_a1_b1_a_idx on ab_a1_b1 (never executed)
                                 Index Cond: (a = a.a)
                                 Heap Fetches: 0
                           ->  Index Only Scan using ab_a1_b2_a_idx on ab_a1_b2 (never executed)
                                 Index Cond: (a = a.a)
                                 Heap Fetches: 0
                           ->  Index Only Scan using ab_a1_b3_a_idx on ab_a1_b3 (never executed)
                                 Index Cond: (a = a.a)
                                 Heap Fetches: 0
                           ->  Index Only Scan using ab_a2_b1_a_idx on ab_a2_b1 (never executed)
                                 Index Cond: (a = a.a)
                                 Heap Fetches: 0
                           ->  Index Only Scan using ab_a2_b2_a_idx on ab_a2_b2 (never executed)
                                 Index Cond: (a = a.a)
                                 Heap Fetches: 0
                           ->  Index Only Scan using ab_a2_b3_a_idx on ab_a2_b3 (never executed)
                                 Index Cond: (a = a.a)
                                 Heap Fetches: 0
                           ->  Index Only Scan using ab_a3_b1_a_idx on ab_a3_b1 (never executed)
                                 Index Cond: (a = a.a)
                                 Heap Fetches: 0
                           ->  Index Only Scan using ab_a3_b2_a_idx on ab_a3_b2 (never executed)
                                 Index Cond: (a = a.a)
                                 Heap Fetches: 0
                           ->  Index Only Scan using ab_a3_b3_a_idx on ab_a3_b3 (never executed)
                                 Index Cond: (a = a.a)
                                 Heap Fetches: 0
(37 rows)

reset enable_hashjoin;
reset enable_mergejoin;
reset parallel_setup_cost;
reset parallel_tuple_cost;
reset min_parallel_table_scan_size;
reset max_parallel_workers_per_gather;
-- Test run-time partition pruning with an initplan
explain (analyze, costs off, summary off, timing off)
select * from ab where a = (select max(a) from lprt_a) and b = (select max(a)-1 from lprt_a);
                               QUERY PLAN                                
-------------------------------------------------------------------------
 Append (actual rows=0 loops=1)
   InitPlan 1 (returns $0)
     ->  Aggregate (actual rows=1 loops=1)
           ->  Seq Scan on lprt_a (actual rows=102 loops=1)
   InitPlan 2 (returns $1)
     ->  Aggregate (actual rows=1 loops=1)
           ->  Seq Scan on lprt_a lprt_a_1 (actual rows=102 loops=1)
   ->  Bitmap Heap Scan on ab_a1_b1 (never executed)
         Recheck Cond: (a = $0)
         Filter: (b = $1)
         ->  Bitmap Index Scan on ab_a1_b1_a_idx (never executed)
               Index Cond: (a = $0)
   ->  Bitmap Heap Scan on ab_a1_b2 (never executed)
         Recheck Cond: (a = $0)
         Filter: (b = $1)
         ->  Bitmap Index Scan on ab_a1_b2_a_idx (never executed)
               Index Cond: (a = $0)
   ->  Bitmap Heap Scan on ab_a1_b3 (never executed)
         Recheck Cond: (a = $0)
         Filter: (b = $1)
         ->  Bitmap Index Scan on ab_a1_b3_a_idx (never executed)
               Index Cond: (a = $0)
   ->  Bitmap Heap Scan on ab_a2_b1 (never executed)
         Recheck Cond: (a = $0)
         Filter: (b = $1)
         ->  Bitmap Index Scan on ab_a2_b1_a_idx (never executed)
               Index Cond: (a = $0)
   ->  Bitmap Heap Scan on ab_a2_b2 (never executed)
         Recheck Cond: (a = $0)
         Filter: (b = $1)
         ->  Bitmap Index Scan on ab_a2_b2_a_idx (never executed)
               Index Cond: (a = $0)
   ->  Bitmap Heap Scan on ab_a2_b3 (never executed)
         Recheck Cond: (a = $0)
         Filter: (b = $1)
         ->  Bitmap Index Scan on ab_a2_b3_a_idx (never executed)
               Index Cond: (a = $0)
   ->  Bitmap Heap Scan on ab_a3_b1 (never executed)
         Recheck Cond: (a = $0)
         Filter: (b = $1)
         ->  Bitmap Index Scan on ab_a3_b1_a_idx (never executed)
               Index Cond: (a = $0)
   ->  Bitmap Heap Scan on ab_a3_b2 (actual rows=0 loops=1)
         Recheck Cond: (a = $0)
         Filter: (b = $1)
         ->  Bitmap Index Scan on ab_a3_b2_a_idx (actual rows=0 loops=1)
               Index Cond: (a = $0)
   ->  Bitmap Heap Scan on ab_a3_b3 (never executed)
         Recheck Cond: (a = $0)
         Filter: (b = $1)
         ->  Bitmap Index Scan on ab_a3_b3_a_idx (never executed)
               Index Cond: (a = $0)
(52 rows)

deallocate ab_q1;
deallocate ab_q2;
deallocate ab_q3;
deallocate ab_q4;
deallocate ab_q5;
deallocate ab_q6;
drop table ab, lprt_a;
-- Join
create table tbl1(col1 int);
insert into tbl1 values (501), (505);
-- Basic table
create table tprt (col1 int) partition by range (col1);
create table tprt_1 partition of tprt for values from (1) to (501);
create table tprt_2 partition of tprt for values from (501) to (1001);
create table tprt_3 partition of tprt for values from (1001) to (2001);
create table tprt_4 partition of tprt for values from (2001) to (3001);
create table tprt_5 partition of tprt for values from (3001) to (4001);
create table tprt_6 partition of tprt for values from (4001) to (5001);
create index tprt1_idx on tprt_1 (col1);
create index tprt2_idx on tprt_2 (col1);
create index tprt3_idx on tprt_3 (col1);
create index tprt4_idx on tprt_4 (col1);
create index tprt5_idx on tprt_5 (col1);
create index tprt6_idx on tprt_6 (col1);
insert into tprt values (10), (20), (501), (502), (505), (1001), (4500);
set enable_hashjoin = off;
set enable_mergejoin = off;
explain (analyze, costs off, summary off, timing off)
select * from tbl1 join tprt on tbl1.col1 > tprt.col1;
                                  QUERY PLAN                                   
-------------------------------------------------------------------------------
 Nested Loop (actual rows=6 loops=1)
   ->  Seq Scan on tbl1 (actual rows=2 loops=1)
   ->  Append (actual rows=3 loops=2)
         ->  Index Only Scan using tprt1_idx on tprt_1 (actual rows=2 loops=2)
               Index Cond: (col1 < tbl1.col1)
               Heap Fetches: 4
         ->  Index Only Scan using tprt2_idx on tprt_2 (actual rows=2 loops=1)
               Index Cond: (col1 < tbl1.col1)
               Heap Fetches: 2
         ->  Index Only Scan using tprt3_idx on tprt_3 (never executed)
               Index Cond: (col1 < tbl1.col1)
               Heap Fetches: 0
         ->  Index Only Scan using tprt4_idx on tprt_4 (never executed)
               Index Cond: (col1 < tbl1.col1)
               Heap Fetches: 0
         ->  Index Only Scan using tprt5_idx on tprt_5 (never executed)
               Index Cond: (col1 < tbl1.col1)
               Heap Fetches: 0
         ->  Index Only Scan using tprt6_idx on tprt_6 (never executed)
               Index Cond: (col1 < tbl1.col1)
               Heap Fetches: 0
(21 rows)

explain (analyze, costs off, summary off, timing off)
select * from tbl1 join tprt on tbl1.col1 = tprt.col1;
                                  QUERY PLAN                                   
-------------------------------------------------------------------------------
 Nested Loop (actual rows=2 loops=1)
   ->  Seq Scan on tbl1 (actual rows=2 loops=1)
   ->  Append (actual rows=1 loops=2)
         ->  Index Only Scan using tprt1_idx on tprt_1 (never executed)
               Index Cond: (col1 = tbl1.col1)
               Heap Fetches: 0
         ->  Index Only Scan using tprt2_idx on tprt_2 (actual rows=1 loops=2)
               Index Cond: (col1 = tbl1.col1)
               Heap Fetches: 2
         ->  Index Only Scan using tprt3_idx on tprt_3 (never executed)
               Index Cond: (col1 = tbl1.col1)
               Heap Fetches: 0
         ->  Index Only Scan using tprt4_idx on tprt_4 (never executed)
               Index Cond: (col1 = tbl1.col1)
               Heap Fetches: 0
         ->  Index Only Scan using tprt5_idx on tprt_5 (never executed)
               Index Cond: (col1 = tbl1.col1)
               Heap Fetches: 0
         ->  Index Only Scan using tprt6_idx on tprt_6 (never executed)
               Index Cond: (col1 = tbl1.col1)
               Heap Fetches: 0
(21 rows)

select tbl1.col1, tprt.col1 from tbl1
inner join tprt on tbl1.col1 > tprt.col1
order by tbl1.col1, tprt.col1;
 col1 | col1 
------+------
  501 |   10
  501 |   20
  505 |   10
  505 |   20
  505 |  501
  505 |  502
(6 rows)

select tbl1.col1, tprt.col1 from tbl1
inner join tprt on tbl1.col1 = tprt.col1
order by tbl1.col1, tprt.col1;
 col1 | col1 
------+------
  501 |  501
  505 |  505
(2 rows)

-- Multiple partitions
insert into tbl1 values (1001), (1010), (1011);
explain (analyze, costs off, summary off, timing off)
select * from tbl1 inner join tprt on tbl1.col1 > tprt.col1;
                                  QUERY PLAN                                   
-------------------------------------------------------------------------------
 Nested Loop (actual rows=23 loops=1)
   ->  Seq Scan on tbl1 (actual rows=5 loops=1)
   ->  Append (actual rows=5 loops=5)
         ->  Index Only Scan using tprt1_idx on tprt_1 (actual rows=2 loops=5)
               Index Cond: (col1 < tbl1.col1)
               Heap Fetches: 10
         ->  Index Only Scan using tprt2_idx on tprt_2 (actual rows=3 loops=4)
               Index Cond: (col1 < tbl1.col1)
               Heap Fetches: 11
         ->  Index Only Scan using tprt3_idx on tprt_3 (actual rows=1 loops=2)
               Index Cond: (col1 < tbl1.col1)
               Heap Fetches: 2
         ->  Index Only Scan using tprt4_idx on tprt_4 (never executed)
               Index Cond: (col1 < tbl1.col1)
               Heap Fetches: 0
         ->  Index Only Scan using tprt5_idx on tprt_5 (never executed)
               Index Cond: (col1 < tbl1.col1)
               Heap Fetches: 0
         ->  Index Only Scan using tprt6_idx on tprt_6 (never executed)
               Index Cond: (col1 < tbl1.col1)
               Heap Fetches: 0
(21 rows)

explain (analyze, costs off, summary off, timing off)
select * from tbl1 inner join tprt on tbl1.col1 = tprt.col1;
                                  QUERY PLAN                                   
-------------------------------------------------------------------------------
 Nested Loop (actual rows=3 loops=1)
   ->  Seq Scan on tbl1 (actual rows=5 loops=1)
   ->  Append (actual rows=1 loops=5)
         ->  Index Only Scan using tprt1_idx on tprt_1 (never executed)
               Index Cond: (col1 = tbl1.col1)
               Heap Fetches: 0
         ->  Index Only Scan using tprt2_idx on tprt_2 (actual rows=1 loops=2)
               Index Cond: (col1 = tbl1.col1)
               Heap Fetches: 2
         ->  Index Only Scan using tprt3_idx on tprt_3 (actual rows=0 loops=3)
               Index Cond: (col1 = tbl1.col1)
               Heap Fetches: 1
         ->  Index Only Scan using tprt4_idx on tprt_4 (never executed)
               Index Cond: (col1 = tbl1.col1)
               Heap Fetches: 0
         ->  Index Only Scan using tprt5_idx on tprt_5 (never executed)
               Index Cond: (col1 = tbl1.col1)
               Heap Fetches: 0
         ->  Index Only Scan using tprt6_idx on tprt_6 (never executed)
               Index Cond: (col1 = tbl1.col1)
               Heap Fetches: 0
(21 rows)

select tbl1.col1, tprt.col1 from tbl1
inner join tprt on tbl1.col1 > tprt.col1
order by tbl1.col1, tprt.col1;
 col1 | col1 
------+------
  501 |   10
  501 |   20
  505 |   10
  505 |   20
  505 |  501
  505 |  502
 1001 |   10
 1001 |   20
 1001 |  501
 1001 |  502
 1001 |  505
 1010 |   10
 1010 |   20
 1010 |  501
 1010 |  502
 1010 |  505
 1010 | 1001
 1011 |   10
 1011 |   20
 1011 |  501
 1011 |  502
 1011 |  505
 1011 | 1001
(23 rows)

select tbl1.col1, tprt.col1 from tbl1
inner join tprt on tbl1.col1 = tprt.col1
order by tbl1.col1, tprt.col1;
 col1 | col1 
------+------
  501 |  501
  505 |  505
 1001 | 1001
(3 rows)

-- Last partition
delete from tbl1;
insert into tbl1 values (4400);
explain (analyze, costs off, summary off, timing off)
select * from tbl1 join tprt on tbl1.col1 < tprt.col1;
                                  QUERY PLAN                                   
-------------------------------------------------------------------------------
 Nested Loop (actual rows=1 loops=1)
   ->  Seq Scan on tbl1 (actual rows=1 loops=1)
   ->  Append (actual rows=1 loops=1)
         ->  Index Only Scan using tprt1_idx on tprt_1 (never executed)
               Index Cond: (col1 > tbl1.col1)
               Heap Fetches: 0
         ->  Index Only Scan using tprt2_idx on tprt_2 (never executed)
               Index Cond: (col1 > tbl1.col1)
               Heap Fetches: 0
         ->  Index Only Scan using tprt3_idx on tprt_3 (never executed)
               Index Cond: (col1 > tbl1.col1)
               Heap Fetches: 0
         ->  Index Only Scan using tprt4_idx on tprt_4 (never executed)
               Index Cond: (col1 > tbl1.col1)
               Heap Fetches: 0
         ->  Index Only Scan using tprt5_idx on tprt_5 (never executed)
               Index Cond: (col1 > tbl1.col1)
               Heap Fetches: 0
         ->  Index Only Scan using tprt6_idx on tprt_6 (actual rows=1 loops=1)
               Index Cond: (col1 > tbl1.col1)
               Heap Fetches: 1
(21 rows)

select tbl1.col1, tprt.col1 from tbl1
inner join tprt on tbl1.col1 < tprt.col1
order by tbl1.col1, tprt.col1;
 col1 | col1 
------+------
 4400 | 4500
(1 row)

-- No matching partition
delete from tbl1;
insert into tbl1 values (10000);
explain (analyze, costs off, summary off, timing off)
select * from tbl1 join tprt on tbl1.col1 = tprt.col1;
                               QUERY PLAN                               
------------------------------------------------------------------------
 Nested Loop (actual rows=0 loops=1)
   ->  Seq Scan on tbl1 (actual rows=1 loops=1)
   ->  Append (actual rows=0 loops=1)
         ->  Index Only Scan using tprt1_idx on tprt_1 (never executed)
               Index Cond: (col1 = tbl1.col1)
               Heap Fetches: 0
         ->  Index Only Scan using tprt2_idx on tprt_2 (never executed)
               Index Cond: (col1 = tbl1.col1)
               Heap Fetches: 0
         ->  Index Only Scan using tprt3_idx on tprt_3 (never executed)
               Index Cond: (col1 = tbl1.col1)
               Heap Fetches: 0
         ->  Index Only Scan using tprt4_idx on tprt_4 (never executed)
               Index Cond: (col1 = tbl1.col1)
               Heap Fetches: 0
         ->  Index Only Scan using tprt5_idx on tprt_5 (never executed)
               Index Cond: (col1 = tbl1.col1)
               Heap Fetches: 0
         ->  Index Only Scan using tprt6_idx on tprt_6 (never executed)
               Index Cond: (col1 = tbl1.col1)
               Heap Fetches: 0
(21 rows)

select tbl1.col1, tprt.col1 from tbl1
inner join tprt on tbl1.col1 = tprt.col1
order by tbl1.col1, tprt.col1;
 col1 | col1 
------+------
(0 rows)

drop table tbl1, tprt;
-- Test with columns defined in varying orders between each level
create table part_abc (a int not null, b int not null, c int not null) partition by list (a);
create table part_bac (b int not null, a int not null, c int not null) partition by list (b);
create table part_cab (c int not null, a int not null, b int not null) partition by list (c);
create table part_abc_p1 (a int not null, b int not null, c int not null);
alter table part_abc attach partition part_bac for values in(1);
alter table part_bac attach partition part_cab for values in(2);
alter table part_cab attach partition part_abc_p1 for values in(3);
prepare part_abc_q1 (int, int, int) as
select * from part_abc where a = $1 and b = $2 and c = $3;
-- Execute query 5 times to allow choose_custom_plan
-- to start considering a generic plan.
execute part_abc_q1 (1, 2, 3);
 a | b | c 
---+---+---
(0 rows)

execute part_abc_q1 (1, 2, 3);
 a | b | c 
---+---+---
(0 rows)

execute part_abc_q1 (1, 2, 3);
 a | b | c 
---+---+---
(0 rows)

execute part_abc_q1 (1, 2, 3);
 a | b | c 
---+---+---
(0 rows)

execute part_abc_q1 (1, 2, 3);
 a | b | c 
---+---+---
(0 rows)

-- Single partition should be scanned.
explain (analyze, costs off, summary off, timing off) execute part_abc_q1 (1, 2, 3);
                      QUERY PLAN                       
-------------------------------------------------------
 Append (actual rows=0 loops=1)
   ->  Seq Scan on part_abc_p1 (actual rows=0 loops=1)
         Filter: ((a = $1) AND (b = $2) AND (c = $3))
(3 rows)

deallocate part_abc_q1;
drop table part_abc;
-- Ensure that an Append node properly handles a sub-partitioned table
-- matching without any of its leaf partitions matching the clause.
create table listp (a int, b int) partition by list (a);
create table listp_1 partition of listp for values in(1) partition by list (b);
create table listp_1_1 partition of listp_1 for values in(1);
create table listp_2 partition of listp for values in(2) partition by list (b);
create table listp_2_1 partition of listp_2 for values in(2);
select * from listp where b = 1;
 a | b 
---+---
(0 rows)

-- Ensure that an Append node properly can handle selection of all first level
-- partitions before finally detecting the correct set of 2nd level partitions
-- which match the given parameter.
prepare q1 (int,int) as select * from listp where b in ($1,$2);
execute q1 (1,2);
 a | b 
---+---
(0 rows)

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

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

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

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

explain (analyze, costs off, summary off, timing off)  execute q1 (1,1);
                     QUERY PLAN                      
-----------------------------------------------------
 Append (actual rows=0 loops=1)
   Subplans Removed: 1
   ->  Seq Scan on listp_1_1 (actual rows=0 loops=1)
         Filter: (b = ANY (ARRAY[$1, $2]))
(4 rows)

explain (analyze, costs off, summary off, timing off)  execute q1 (2,2);
                     QUERY PLAN                      
-----------------------------------------------------
 Append (actual rows=0 loops=1)
   Subplans Removed: 1
   ->  Seq Scan on listp_2_1 (actual rows=0 loops=1)
         Filter: (b = ANY (ARRAY[$1, $2]))
(4 rows)

-- Try with no matching partitions. One subplan should remain in this case,
-- but it shouldn't be executed.
explain (analyze, costs off, summary off, timing off)  execute q1 (0,0);
                  QUERY PLAN                  
----------------------------------------------
 Append (actual rows=0 loops=1)
   Subplans Removed: 1
   ->  Seq Scan on listp_1_1 (never executed)
         Filter: (b = ANY (ARRAY[$1, $2]))
(4 rows)

deallocate q1;
-- Test more complex cases where a not-equal condition further eliminates partitions.
prepare q1 (int,int,int,int) as select * from listp where b in($1,$2) and $3 <> b and $4 <> b;
execute q1 (1,2,3,4);
 a | b 
---+---
(0 rows)

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

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

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

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

-- Both partitions allowed by IN clause, but one disallowed by <> clause
explain (analyze, costs off, summary off, timing off)  execute q1 (1,2,2,0);
                               QUERY PLAN                                
-------------------------------------------------------------------------
 Append (actual rows=0 loops=1)
   Subplans Removed: 1
   ->  Seq Scan on listp_1_1 (actual rows=0 loops=1)
         Filter: ((b = ANY (ARRAY[$1, $2])) AND ($3 <> b) AND ($4 <> b))
(4 rows)

-- Both partitions allowed by IN clause, then both excluded again by <> clauses.
-- One subplan will remain in this case, but it should not be executed.
explain (analyze, costs off, summary off, timing off)  execute q1 (1,2,2,1);
                               QUERY PLAN                                
-------------------------------------------------------------------------
 Append (actual rows=0 loops=1)
   Subplans Removed: 1
   ->  Seq Scan on listp_1_1 (never executed)
         Filter: ((b = ANY (ARRAY[$1, $2])) AND ($3 <> b) AND ($4 <> b))
(4 rows)

drop table listp;
-- Ensure runtime pruning works with initplans params with boolean types
create table boolvalues (value bool not null);
insert into boolvalues values('t'),('f');
create table boolp (a bool) partition by list (a);
create table boolp_t partition of boolp for values in('t');
create table boolp_f partition of boolp for values in('f');
explain (analyze, costs off, summary off, timing off)
select * from boolp where a = (select value from boolvalues where value);
                       QUERY PLAN                       
--------------------------------------------------------
 Append (actual rows=0 loops=1)
   InitPlan 1 (returns $0)
     ->  Seq Scan on boolvalues (actual rows=1 loops=1)
           Filter: value
           Rows Removed by Filter: 1
   ->  Seq Scan on boolp_f (never executed)
         Filter: (a = $0)
   ->  Seq Scan on boolp_t (actual rows=0 loops=1)
         Filter: (a = $0)
(9 rows)

explain (analyze, costs off, summary off, timing off)
select * from boolp where a = (select value from boolvalues where not value);
                       QUERY PLAN                       
--------------------------------------------------------
 Append (actual rows=0 loops=1)
   InitPlan 1 (returns $0)
     ->  Seq Scan on boolvalues (actual rows=1 loops=1)
           Filter: (NOT value)
           Rows Removed by Filter: 1
   ->  Seq Scan on boolp_f (actual rows=0 loops=1)
         Filter: (a = $0)
   ->  Seq Scan on boolp_t (never executed)
         Filter: (a = $0)
(9 rows)

drop table boolp;