Re: [HACKERS] path toward faster partition pruning

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:40 2018
***************
*** 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