Re: Problem with default partition pruning

Horiguchi-san,

> -----Original Message-----
> From: Kyotaro HORIGUCHI [mailto:horiguchi(dot)kyotaro(at)lab(dot)ntt(dot)co(dot)jp]
> Sent: Tuesday, April 09, 2019 5:37 PM
> To: hosoya(dot)yuzuko(at)lab(dot)ntt(dot)co(dot)jp
> Cc: Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp; thibaut(dot)madelaine(at)dalibo(dot)com;
> imai(dot)yoshikazu(at)jp(dot)fujitsu(dot)com; pgsql-hackers(at)lists(dot)postgresql(dot)org
> Subject: Re: Problem with default partition pruning
>
> Hi.
>
> At Tue, 9 Apr 2019 16:41:47 +0900, "Yuzuko Hosoya"
> <hosoya(dot)yuzuko(at)lab(dot)ntt(dot)co(dot)jp> wrote in
> <00cf01d4eea7$afa43370$0eec9a50$(at)lab(dot)ntt(dot)co(dot)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