From: Yuzuko Hosoya <hosoya.yuzuko@lab.ntt.co.jp>
To: 'Kyotaro HORIGUCHI' <horiguchi.kyotaro@lab.ntt.co.jp>
Cc: Langote_Amit_f8@lab.ntt.co.jp
Cc: thibaut.madelaine@dalibo.com
Cc: imai.yoshikazu@jp.fujitsu.com
Cc: pgsql-hackers@lists.postgresql.org
Subject: Re: Problem with default partition pruning
Date: Wed, 10 Apr 2019 11:24:11 +0900
Message-ID: <00df01d4ef44$7bb79370$7326ba50$@lab.ntt.co.jp> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: hosoya.yuzuko@lab.ntt.co.jp, horiguchi.kyotaro@lab.ntt.co.jp, 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
In-Reply-To: <00df01d4ef44$7bb79370$7326ba50$@lab.ntt.co.jp>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox