Re: Problem with default partition pruning

Horiguchi-san,

> -----Original Message-----
> From: Kyotaro HORIGUCHI [mailto:horiguchi.kyotaro@lab.ntt.co.jp]
> Sent: Tuesday, April 09, 2019 5:37 PM
> To: hosoya.yuzuko@lab.ntt.co.jp
> Cc: Langote_Amit_f8@lab.ntt.co.jp; thibaut.madelaine@dalibo.com; 
> imai.yoshikazu@jp.fujitsu.com; pgsql-hackers@lists.postgresql.org
> Subject: Re: Problem with default partition pruning
> 
> Hi.
> 
> At Tue, 9 Apr 2019 16:41:47 +0900, "Yuzuko Hosoya" 
> <hosoya.yuzuko@lab.ntt.co.jp> wrote in 
> <00cf01d4eea7$afa43370$0eec9a50$@lab.ntt.co.jp>
> > > So still it is wrong that the new code is added at the beginning 
> > > of the loop on clauses in gen_partprune_steps_internal.
> > >
> > > >                               If partqual results true and the 
> > > > clause is long, the partqual is evaluated uselessly at every recursion.
> > > >
> > > > Maybe we should do that when we find that the current clause 
> > > > doesn't match part attributes. Specifically just after the for 
> > > > loop "for (i =
> > > > 0 ; i < part_scheme->partnattrs; i++)".
> > >
> > I think we should check whether WHERE clause contradicts partition 
> > constraint even when the clause matches part attributes.  So I moved
> 
> Why?  If clauses contains a clause on a partition key, the clause is 
> involved in determination of whether a partition survives or not in 
> ordinary way. Could you show how or on what configuration (tables and
> query) it happens that such a matching clause needs to be checked against partqual?
> 
We found that partition pruning didn't work as expect when we scanned a sub-partition using WHERE
clause which contradicts the sub-partition's constraint by Thibaut tests.
The example discussed in this thread as follows.

postgres=# \d+ test2
                             Partitioned table "public.test2"
 Column |  Type   | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+---------+-----------+----------+---------+----------+--------------+-------------
 id     | integer |           |          |         | plain    |              | 
 val    | text    |           |          |         | extended |              | 
Partition key: RANGE (id)
Partitions: test2_0_20 FOR VALUES FROM (0) TO (20), PARTITIONED,
            test2_20_plus_def DEFAULT

postgres=# \d+ test2_0_20
                           Partitioned table "public.test2_0_20"
 Column |  Type   | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+---------+-----------+----------+---------+----------+--------------+-------------
 id     | integer |           |          |         | plain    |              | 
 val    | text    |           |          |         | extended |              | 
Partition of: test2 FOR VALUES FROM (0) TO (20) Partition constraint: ((id IS NOT NULL) AND (id >=
0) AND (id < 20)) Partition key: RANGE (id)
Partitions: test2_0_10 FOR VALUES FROM (0) TO (10),
            test2_10_20_def DEFAULT

postgres=# explain (costs off) select * from test2 where id=5 or id=20;
               QUERY PLAN                
-----------------------------------------
 Append
   ->  Seq Scan on test2_0_10
         Filter: ((id = 5) OR (id = 20))
   ->  Seq Scan on test2_10_20_def
         Filter: ((id = 5) OR (id = 20))
   ->  Seq Scan on test2_20_plus_def
         Filter: ((id = 5) OR (id = 20))
(7 rows)

postgres=# explain (costs off) select * from test2_0_20 where id=25;
         QUERY PLAN          
-----------------------------
 Seq Scan on test2_10_20_def
   Filter: (id = 25)
(2 rows)

So I think we have to check if WHERE clause contradicts sub-partition's constraint regardless of
whether the clause matches part attributes or not.

> The "if (partconstr)" block uselessly runs for every clause in the clause tree other than
BoolExpr.
> If we want do that, isn't just doing predicate_refuted_by(partconstr, 
> clauses, false) sufficient before looping over clauses?
Yes, I tried doing that in the original patch.

> 
> 
> > "if (partqual)" block to the beginning of the loop you mentioned.
> >
> > I'm attaching the latest version.  Could you please check it again?
> 
> regards.
> 
> --
> Kyotaro Horiguchi
> NTT Open Source Software Center

Best regards,
Yuzuko Hosoya