Re: [HACKERS] path toward faster partition pruning - Mailing list pgsql-hackers

So I pushed this 25 minutes ago, and already there's a couple of
buildfarm members complaining:
https://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=quokka&dt=2018-04-06%2020%3A09%3A52
https://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=termite&dt=2018-04-06%2019%3A55%3A07

Both show exactly the same diff in test partition_prune:

*** /home/pgbuildfarm/buildroot-termite/HEAD/pgsql.build/../pgsql/src/test/regress/expected/partition_prune.out    Fri
Apr 6 15:55:08 2018
 
--- /home/pgbuildfarm/buildroot-termite/HEAD/pgsql.build/src/test/regress/results/partition_prune.out    Fri Apr  6
16:01:402018
 
***************
*** 1348,1357 ****
  ----------+----+-----
   hp0      |    | 
   hp0      |  1 | 
!  hp0      |  1 | xxx
   hp3      | 10 | yyy
!  hp1      |    | xxx
!  hp2      | 10 | xxx
  (6 rows)
  
  -- partial keys won't prune, nor would non-equality conditions
--- 1348,1357 ----
  ----------+----+-----
   hp0      |    | 
   hp0      |  1 | 
!  hp0      | 10 | xxx
!  hp3      |    | xxx
   hp3      | 10 | yyy
!  hp2      |  1 | xxx
  (6 rows)
  
  -- partial keys won't prune, nor would non-equality conditions
***************
*** 1460,1466 ****
                     QUERY PLAN                    
  -------------------------------------------------
   Append
!    ->  Seq Scan on hp0
           Filter: ((a = 1) AND (b = 'xxx'::text))
  (3 rows)
  
--- 1460,1466 ----
                     QUERY PLAN                    
  -------------------------------------------------
   Append
!    ->  Seq Scan on hp2
           Filter: ((a = 1) AND (b = 'xxx'::text))
  (3 rows)
  
***************
*** 1468,1474 ****
                       QUERY PLAN                      
  -----------------------------------------------------
   Append
!    ->  Seq Scan on hp1
           Filter: ((a IS NULL) AND (b = 'xxx'::text))
  (3 rows)
  
--- 1468,1474 ----
                       QUERY PLAN                      
  -----------------------------------------------------
   Append
!    ->  Seq Scan on hp3
           Filter: ((a IS NULL) AND (b = 'xxx'::text))
  (3 rows)
  
***************
*** 1476,1482 ****
                      QUERY PLAN                    
  --------------------------------------------------
   Append
!    ->  Seq Scan on hp2
           Filter: ((a = 10) AND (b = 'xxx'::text))
  (3 rows)
  
--- 1476,1482 ----
                      QUERY PLAN                    
  --------------------------------------------------
   Append
!    ->  Seq Scan on hp0
           Filter: ((a = 10) AND (b = 'xxx'::text))
  (3 rows)
  
***************
*** 1494,1504 ****
   Append
     ->  Seq Scan on hp0
           Filter: (((a = 10) AND (b = 'yyy'::text)) OR ((a = 10) AND (b = 'xxx'::text)) OR ((a IS NULL) AND (b IS
NULL)))
-    ->  Seq Scan on hp2
-          Filter: (((a = 10) AND (b = 'yyy'::text)) OR ((a = 10) AND (b = 'xxx'::text)) OR ((a IS NULL) AND (b IS
NULL)))
     ->  Seq Scan on hp3
           Filter: (((a = 10) AND (b = 'yyy'::text)) OR ((a = 10) AND (b = 'xxx'::text)) OR ((a IS NULL) AND (b IS
NULL)))
! (7 rows)
  
  -- hash partitiong pruning doesn't occur with <> operator clauses
  explain (costs off) select * from hp where a <> 1 and b <> 'xxx';
--- 1494,1502 ----
   Append
     ->  Seq Scan on hp0
           Filter: (((a = 10) AND (b = 'yyy'::text)) OR ((a = 10) AND (b = 'xxx'::text)) OR ((a IS NULL) AND (b IS
NULL)))
     ->  Seq Scan on hp3
           Filter: (((a = 10) AND (b = 'yyy'::text)) OR ((a = 10) AND (b = 'xxx'::text)) OR ((a IS NULL) AND (b IS
NULL)))
! (5 rows)
  
  -- hash partitiong pruning doesn't occur with <> operator clauses
  explain (costs off) select * from hp where a <> 1 and b <> 'xxx';


-- 
Álvaro Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services