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

Hi. (The thread seems broken for Thunderbird)

At Wed, 10 Apr 2019 11:24:11 +0900, "Yuzuko Hosoya" <hosoya.yuzuko@lab.ntt.co.jp> wrote in
<00df01d4ef44$7bb79370$7326ba50$@lab.ntt.co.jp>
> > 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)

I think this is problematic.

> 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.

If that is the only issue here, doesn't Amit's proposal work?

And that doesn't seem to justify rechecking key clauses to
partquals for every leaf node in an expression tree. I thought
that you are trying to resolve is the issue on non-key caluses
that contradicts to partition constraints?

> > 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.

-- 
Kyotaro Horiguchi
NTT Open Source Software Center