Horiguchi-san,
> -----Original Message-----
> From: Kyotaro HORIGUCHI [mailto:horiguchi.kyot...@lab.ntt.co.jp]
> Sent: Tuesday, April 09, 2019 5:37 PM
> To: hosoya.yuz...@lab.ntt.co.jp
> Cc: langote_amit...@lab.ntt.co.jp; thibaut.madela...@dalibo.com;
> imai.yoshik...@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.yuz...@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
