Re: Problem with default partition pruning - Mailing list pgsql-hackers

Hi Alvaro,

Thanks for reviewing.
The modification you made seems correct to me.

However, I'm still concerned that the block
-----
  if (partconstr)
 {
            partconstr = (List *)
                         expression_planner((Expr *) partconstr);
            if (context->rel->relid != 1)
                         ChangeVarNodes((Node *) partconstr, 1,
                                                    context->rel->relid, 0);
            if (predicate_refuted_by(partconstr,
                                                    list_make1(clause),
                                                    false))
            {
                           context->contradictory = true;
                           return NIL;
            }
 }
-----
is written in the right place as Amit explained [1].

At first, we tried to fix the following problematic query
which was reported by Thibaut before:

create table p (a int) partition by range (a);
create table p1 partition of p for values from (0) to (20) partition
by range (a);
create table p11 partition of p1 for values from (0) to (10);
create table p1_def partition of p1 default;
explain select * from p1 where a = 25 or a = 5;
                          QUERY PLAN
──────────────────────────────────────
 Append  (cost=0.00..96.75 rows=50 width=4)
   ->  Seq Scan on p11  (cost=0.00..48.25 rows=25 width=4)
         Filter: ((a = 25) OR (a = 5))
   ->  Seq Scan on p1_def  (cost=0.00..48.25 rows=25 width=4)
         Filter: ((a = 25) OR (a = 5))
(5 rows)

And Amit proposed the patch to fix this problem[2].
In this patch, the above if() block was written in another place.
After that, I found the following query also doesn't work correctly:

explain select * from p1 where a = 25;
                          QUERY PLAN
───────────────────────────────────────
 Append  (cost=0.00..41.94 rows=13 width=4)
   ->  Seq Scan on p1_def  (cost=0.00..41.88 rows=13 width=4)
         Filter: (a = 25)
(3 rows)

So I proposed moving the if() block to the current place.
The latest patch can solve both queries but I found the latter
problem can be solved by setting constraint_exclusion = on.

Which approach will be suitable?


[1] https://www.postgresql.org/message-id/CA%2BHiwqG%2BnSD0vcJacArYgYcFVtpTJQ0fx1gBgoZkA_isKd6Z2w%40mail.gmail.com
[2] https://www.postgresql.org/message-id/9bb31dfe-b0d0-53f3-3ea6-e64b811424cf%40lab.ntt.co.jp

Best regards,

Yuzuko Hosoya
NTT Open Source Software Center

On Mon, Aug 5, 2019 at 11:03 AM Alvaro Herrera <alvherre@2ndquadrant.com> wrote:
>
> I propose the comment rewordings as attached.  Mostly, this updates the
> comment atop the function to cover the case being modified, and then the
> new comment just refers to the new explicitly stated policy, so it
> bcomes simpler.
>
> --
> Álvaro Herrera                https://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services



--
Best regards,
Yuzuko Hosoya
NTT Open Source Software Center