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

Thank you Alvaro for rest of the cleanup and committing.

On Sat, Apr 7, 2018 at 5:28 AM, Alvaro Herrera <alvherre@alvh.no-ip.org> wrote:
> 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)

So this same failure occurs on (noting the architecture):

ppc64:
https://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=quokka&dt=2018-04-06%2020%3A09%3A52

ia64:
https://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=anole&dt=2018-04-06%2022%3A32%3A24

ppc64 (POWER7):
https://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=tern&dt=2018-04-06%2022%3A58%3A13

ppc64 (POWER7):
https://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=hornet&dt=2018-04-06%2023%3A02%3A13

powerpc:
https://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=prairiedog&dt=2018-04-06%2023%3A05%3A08

powerpc:
https://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=locust&dt=2018-04-06%2023%3A13%3A23

powerpc 32-bit userspace on ppc64 host:
https://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=termite&dt=2018-04-06%2023%3A40%3A07

Seems to be due to that the hashing function used in partitioning
gives different answer for a given set of partition key values than
others.

Thanks,
Amit