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: Tue, 9 Apr 2019 16:41:47 +0900
Message-ID: <00cf01d4eea7$afa43370$0eec9a50$@lab.ntt.co.jp> (raw)
In-Reply-To: <20190409.103317.20446083.horiguchi.kyotaro@lab.ntt.co.jp>
References: <9dc22159-4869-dd2b-feb5-c39af3c01a7e@lab.ntt.co.jp>
<00c101d4ede0$babd4390$3037cab0$@lab.ntt.co.jp>
<20190409.102848.252476604.horiguchi.kyotaro@lab.ntt.co.jp>
<20190409.103317.20446083.horiguchi.kyotaro@lab.ntt.co.jp>
Horiguchi-san,
Thanks for your comments.
> -----Original Message-----
> From: Kyotaro HORIGUCHI [mailto:horiguchi.kyotaro@lab.ntt.co.jp]
> Sent: Tuesday, April 09, 2019 10:33 AM
> 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
>
> Sigh..
>
> At Tue, 09 Apr 2019 10:28:48 +0900 (Tokyo Standard Time), Kyotaro HORIGUCHI
> <horiguchi.kyotaro@lab.ntt.co.jp> wrote in
> <20190409.102848.252476604.horiguchi.kyotaro@lab.ntt.co.jp>
> > As the second thought. Partition constraint is not constraint
> > expression so that's fair to apply partqual ignoring
> > constraint_exclusion. The variable is set false to skip useless
> > expression evaluation on all partitions, but partqual should be
> > evaluated just once. Sorry for my confusion.
> >
> > So still it is wrong that the new code is added in
> > gen_partprune_steps_internal.
>
> 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
"if (partqual)" block to the beginning of the loop you mentioned.
I'm attaching the latest version. Could you please check it again?
Best regards,
Yuzuko Hosoya
Attachments:
[application/octet-stream] v4_ignore_contradictory_where_clauses_at_partprune_step.patch (4.2K, ../00cf01d4eea7$afa43370$0eec9a50$@lab.ntt.co.jp/2-v4_ignore_contradictory_where_clauses_at_partprune_step.patch)
download | inline diff:diff --git a/src/backend/partitioning/partprune.c b/src/backend/partitioning/partprune.cindex 5cd740a..11e3e7a 100644--- a/src/backend/partitioning/partprune.c+++ b/src/backend/partitioning/partprune.c@@ -850,32 +850,13 @@ gen_partprune_steps_internal(GeneratePruningStepsContext *context,
* an arg. To indicate that to the pruning code, we
* must construct a dummy PartitionPruneStepCombine
* whose source_stepids is set to an empty List.
- * However, if we can prove using constraint exclusion- * that the clause refutes the table's partition- * constraint (if it's sub-partitioned), we need not- * bother with that. That is, we effectively ignore- * this OR arm.
*/
- List *partconstr = rel->partition_qual;
PartitionPruneStep *orstep;
/* Just ignore this argument. */
if (arg_contradictory)
continue;
- if (partconstr)- {- partconstr = (List *)- expression_planner((Expr *) partconstr);- if (rel->relid != 1)- ChangeVarNodes((Node *) partconstr, 1,- rel->relid, 0);- if (predicate_refuted_by(partconstr,- list_make1(arg),- false))- continue;- }-
orstep = gen_prune_step_combine(context, NIL,
PARTPRUNE_COMBINE_UNION);
arg_stepids = lappend_int(arg_stepids, orstep->step_id);
@@ -951,6 +932,30 @@ gen_partprune_steps_internal(GeneratePruningStepsContext *context,
bool clause_is_not_null = false;
PartClauseInfo *pc = NULL;
List *clause_steps = NIL;
+ List *partconstr = rel->partition_qual;++ /*+ * If this clause can be proved false for this partition,+ * given its partition constraint, we can ignore it,+ * that is not try to pass it to the pruning code.+ * We should do that especially to avoid pruning code+ * wrongly failing to prune the default partition.+ */+ if (partconstr)+ {+ partconstr = (List *)+ expression_planner((Expr *) partconstr);+ if (rel->relid != 1)+ ChangeVarNodes((Node *) partconstr, 1,+ rel->relid, 0);+ if (predicate_refuted_by(partconstr,+ list_make1(clause),+ false))+ {+ *contradictory = true;+ return NIL;+ }+ }
switch (match_clause_to_partition_key(rel, context,
clause, partkey, i,
diff --git a/src/test/regress/expected/partition_prune.out b/src/test/regress/expected/partition_prune.outindex b46951c..716155d 100644--- a/src/test/regress/expected/partition_prune.out+++ b/src/test/regress/expected/partition_prune.out@@ -592,6 +592,24 @@ explain (costs off) select * from rlp where a < 1 or (a > 20 and a < 25);
Filter: ((a < 1) OR ((a > 20) AND (a < 25)))
(5 rows)
+-- where clause contradicts sub-partition's constraint +explain (costs off) select * from rlp where a = 20 or a = 40;+ QUERY PLAN +----------------------------------------+ Append+ -> Seq Scan on rlp4_1+ Filter: ((a = 20) OR (a = 40))+ -> Seq Scan on rlp5_default+ Filter: ((a = 20) OR (a = 40))+(5 rows)++explain (costs off) select * from rlp3 where a = 20; /* empty */+ QUERY PLAN +--------------------------+ Result+ One-Time Filter: false+(2 rows)+
-- redundant clauses are eliminated
explain (costs off) select * from rlp where a > 1 and a = 10; /* only default */
QUERY PLAN
diff --git a/src/test/regress/sql/partition_prune.sql b/src/test/regress/sql/partition_prune.sqlindex abc6d9e..a5e8109 100644--- a/src/test/regress/sql/partition_prune.sql+++ b/src/test/regress/sql/partition_prune.sql@@ -85,6 +85,10 @@ explain (costs off) select * from rlp where a = 29;
explain (costs off) select * from rlp where a >= 29;
explain (costs off) select * from rlp where a < 1 or (a > 20 and a < 25);
+-- where clause contradicts sub-partition's constraint+explain (costs off) select * from rlp where a = 20 or a = 40;+explain (costs off) select * from rlp3 where a = 20; /* empty */+
-- redundant clauses are eliminated
explain (costs off) select * from rlp where a > 1 and a = 10; /* only default */
explain (costs off) select * from rlp where a > 1 and a >=15; /* rlp3 onwards, including default */
=
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: <00cf01d4eea7$afa43370$0eec9a50$@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