Re: [HACKERS] path toward faster partition pruning

Thank you Alvaro for rest of the cleanup and committing.

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

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