Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1hDPAT-0007ju-Jg for pgsql-hackers@arkaria.postgresql.org; Mon, 08 Apr 2019 07:58:13 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1hDPAS-0000OG-2i for pgsql-hackers@arkaria.postgresql.org; Mon, 08 Apr 2019 07:58:12 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1hDPAR-0000O9-Oz for pgsql-hackers@lists.postgresql.org; Mon, 08 Apr 2019 07:58:11 +0000 Received: from tama500.ecl.ntt.co.jp ([129.60.39.148]) by magus.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1hDPAO-0001Nu-7n for pgsql-hackers@lists.postgresql.org; Mon, 08 Apr 2019 07:58:11 +0000 Received: from vc1.ecl.ntt.co.jp (vc1.ecl.ntt.co.jp [129.60.86.153]) by tama500.ecl.ntt.co.jp (8.13.8/8.13.8) with ESMTP id x387vxmQ026749; Mon, 8 Apr 2019 16:57:59 +0900 Received: from vc1.ecl.ntt.co.jp (localhost [127.0.0.1]) by vc1.ecl.ntt.co.jp (Postfix) with ESMTP id 2B8DFEA8095; Mon, 8 Apr 2019 16:57:59 +0900 (JST) Received: from jcms-pop11.ecl.ntt.co.jp (jcms-pop11.ecl.ntt.co.jp [129.60.87.132]) by vc1.ecl.ntt.co.jp (Postfix) with ESMTP id 0C82AEA7F02; Mon, 8 Apr 2019 16:57:59 +0900 (JST) Received: from hosoyahp (unknown [129.60.241.74]) by jcms-pop11.ecl.ntt.co.jp (Postfix) with ESMTPSA id EB9FC70C01E1; Mon, 8 Apr 2019 16:57:58 +0900 (JST) From: "Yuzuko Hosoya" References: <00e601d4ca86$932b8bc0$b982a340$@lab.ntt.co.jp> <010e01d4ce68$c0427b10$40c77130$@lab.ntt.co.jp> <0F97FA9ABBDBE54F91744A9B37151A5129A78A@g01jpexmbkw24> <001901d4daf5$1ef4f640$5cdee2c0$@lab.ntt.co.jp> <9bed6b79-f264-6976-b880-e2a5d23e9d85@lab.ntt.co.jp> <003501d4de21$8816f540$9844dfc0$@lab.ntt.co.jp> <9bb31dfe-b0d0-53f3-3ea6-e64b811424cf@lab.ntt.co.jp> <005101d4e074$e24d1e40$a6e75ac0$@lab.ntt.co.jp> <001ea23e-638f-9ab2-143d-2cf1105bf8d6@lab.ntt.co.jp> <008f01d4e911$39aa87a0$acff96e0$@lab.ntt.co.jp> <00a301d4ea9b$01296eb0$037c4c10$@lab.ntt.co.jp> <9dc22159-4869-dd2b-feb5-c39af3c01a7e@lab.ntt.co.jp> In-Reply-To: <9dc22159-4869-dd2b-feb5-c39af3c01a7e@lab.ntt.co.jp> Subject: RE: Problem with default partition pruning Date: Mon, 8 Apr 2019 16:57:35 +0900 Message-ID: <00c101d4ede0$babd4390$3037cab0$@lab.ntt.co.jp> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="----=_NextPart_000_00C2_01D4EE2C.2AA75C90" X-Mailer: Microsoft Outlook 15.0 Content-language: ja Thread-index: AQLwSThp7L0tC61luGAd4dJt3HxY3QJvrgn9AgtGZCUBpM85VAEOZ04CAeo+SK4CDbFrsgGXrNc6AV8LXc4CAoWeVAJ/vK7zAyoITl4B/o26TwH7lh69Aljr3vsB5s7UgwF9mTaRov8CiOA= X-CC-Mail-RelayStamp: 1 To: "'Amit Langote'" , "'Thibaut'" , "'Imai, Yoshikazu'" Cc: "'PostgreSQL Hackers'" X-TM-AS-MML: disable List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multipart message in MIME format. ------=_NextPart_000_00C2_01D4EE2C.2AA75C90 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Amit-san, > -----Original Message----- > From: Amit Langote [mailto:Langote_Amit_f8@lab.ntt.co.jp] > Sent: Friday, April 05, 2019 6:47 PM > To: Yuzuko Hosoya ; 'Thibaut' = ; 'Imai, > Yoshikazu' > Cc: 'PostgreSQL Hackers' > Subject: Re: Problem with default partition pruning >=20 > Hosoya-san, >=20 >=20 > On 2019/04/04 13:00, Yuzuko Hosoya wrote: > > I added some test cases to each patch according to tests discussed = in > > this thread. >=20 > Thanks a lot. >=20 > > However, I found another problem as follows. This query should = output > > "One-Time Filter: false" because rlp3's constraints contradict WHERE > > clause. > > > > ----- > > postgres=3D# \d+ rlp3 > > Partitioned table "public.rlp3" > > Column | Type | Collation | Nullable | Default | = Storage | Stats target | Description > > > = --------+-------------------+-----------+----------+---------+----------+= --------------+--------- > ---- > > b | character varying | | | | = extended | | > > a | integer | | | | plain = | | > > Partition of: rlp FOR VALUES FROM (15) TO (20) Partition constraint: > > ((a IS NOT NULL) AND (a >=3D 15) AND (a < 20)) Partition key: LIST = (b > > varchar_ops) > > Partitions: rlp3abcd FOR VALUES IN ('ab', 'cd'), > > rlp3efgh FOR VALUES IN ('ef', 'gh'), > > rlp3nullxy FOR VALUES IN (NULL, 'xy'), > > rlp3_default DEFAULT > > > > postgres=3D# explain select * from rlp3 where a =3D 2; > > QUERY PLAN > > -------------------------------------------------------------------- > > Append (cost=3D0.00..103.62 rows=3D24 width=3D36) > > -> Seq Scan on rlp3abcd (cost=3D0.00..25.88 rows=3D6 = width=3D36) > > Filter: (a =3D 2) > > -> Seq Scan on rlp3efgh (cost=3D0.00..25.88 rows=3D6 = width=3D36) > > Filter: (a =3D 2) > > -> Seq Scan on rlp3nullxy (cost=3D0.00..25.88 rows=3D6 = width=3D36) > > Filter: (a =3D 2) > > -> Seq Scan on rlp3_default (cost=3D0.00..25.88 rows=3D6 = width=3D36) > > Filter: (a =3D 2) > > (9 rows) > > ----- >=20 > This one too would be solved with the other patch I mentioned to fix > get_relation_info() to load the partition constraint so that = constraint exclusion can use it. > Partition in the earlier example given by Thibaut is a leaf partition, = whereas rlp3 above is a > sub-partitioned partition, but both are partitions nonetheless. >=20 > Fixing partprune.c like we're doing with the > v2_ignore_contradictory_where_clauses_at_partprune_step.patch only = works for the latter, because only > partitioned tables visit partprune.c. >=20 > OTOH, the other patch only applies to situations where = constraint_exclusion =3D on. >=20 I see. I think that following example discussed in this thread before = would also be solved with your patch, not = v2_ignore_contradictory_where_clauses_at_partprune_step.patch. postgres=3D# set constraint_exclusion to on; postgres=3D# explain select * from test2_0_20 where id =3D 25; QUERY PLAN =20 ------------------------------------------ Result (cost=3D0.00..0.00 rows=3D0 width=3D0) One-Time Filter: false (2 rows) > > I think that the place of check contradiction process was wrong At > > ignore_contradictory_where_clauses_at_partprune_step.patch. > > So I fixed it. >=20 > Thanks. Patch contains some whitespace noise: >=20 > $ git diff --check > src/backend/partitioning/partprune.c:790: trailing whitespace. > + * given its partition constraint, we can ignore it, > src/backend/partitioning/partprune.c:791: trailing whitespace. > + * that is not try to pass it to the pruning code. > src/backend/partitioning/partprune.c:792: trailing whitespace. > + * We should do that especially to avoid pruning code > src/backend/partitioning/partprune.c:810: trailing whitespace. > + > src/test/regress/sql/partition_prune.sql:87: trailing whitespace. > +-- where clause contradicts sub-partition's constraint >=20 > Can you please fix it? >=20 Thanks for checking. I'm attaching the latest patch. >=20 > BTW, now I'm a bit puzzled between whether this case should be fixed = by hacking on partprune.c like > this patch does or whether to work on getting the other patch = committed and expect users to set > constraint_exclusion =3D on for this to behave as expected. The = original intention of setting > partition_qual in set_relation_partition_info() was for partprune.c to = use it to remove useless > arguments of OR clauses which otherwise would cause the failure to = correctly prune the default partitions > of sub-partitioned tables. As shown by the examples in this thread, = the original effort was > insufficient, which this patch aims to improve. But, it also expands = the scope of partprune.c's usage > of partition_qual, which is to effectively perform full-blown = constraint exclusion without being > controllable by constraint_exclusion GUC, which may be seen as being = good or bad. The fact that it > helps in getting partition pruning working correctly in more obscure = cases like those discussed in > this thread means it's good maybe. >=20 Umm, even though this modification might be overhead, I think this = problem should be solved without setting constraint_exclusion GUC. But I'm not sure. Best regards, Yuzuko Hosoya ------=_NextPart_000_00C2_01D4EE2C.2AA75C90 Content-Type: application/octet-stream; name="v3_ignore_contradictory_where_clauses_at_partprune_step.patch" Content-Transfer-Encoding: quoted-printable Content-Disposition: attachment; filename="v3_ignore_contradictory_where_clauses_at_partprune_step.patch" diff --git a/src/backend/partitioning/partprune.c = b/src/backend/partitioning/partprune.c=0A= index 5cd740a..f360d82 100644=0A= --- a/src/backend/partitioning/partprune.c=0A= +++ b/src/backend/partitioning/partprune.c=0A= @@ -783,11 +783,35 @@ = gen_partprune_steps_internal(GeneratePruningStepsContext *context,=0A= {=0A= Expr *clause =3D (Expr *) lfirst(lc);=0A= int i;=0A= + List *partconstr =3D rel->partition_qual;=0A= =0A= /* Look through RestrictInfo, if any */=0A= if (IsA(clause, RestrictInfo))=0A= clause =3D ((RestrictInfo *) clause)->clause;=0A= =0A= + /*=0A= + * If this clause can be proved false for this partition,=0A= + * given its partition constraint, we can ignore it,=0A= + * that is not try to pass it to the pruning code.=0A= + * We should do that especially to avoid pruning code=0A= + * wrongly failing to prune the default partition.=0A= + */=0A= + if (partconstr)=0A= + {=0A= + partconstr =3D (List *)=0A= + expression_planner((Expr *) partconstr);=0A= + if (rel->relid !=3D 1)=0A= + ChangeVarNodes((Node *) partconstr, 1,=0A= + rel->relid, 0);=0A= + if (predicate_refuted_by(partconstr,=0A= + list_make1(clause),=0A= + false))=0A= + {=0A= + *contradictory =3D true;=0A= + return NIL;=0A= + }=0A= + }=0A= +=0A= /* Constant-false-or-null is contradictory */=0A= if (IsA(clause, Const) &&=0A= (((Const *) clause)->constisnull ||=0A= @@ -850,32 +874,13 @@ = gen_partprune_steps_internal(GeneratePruningStepsContext *context,=0A= * an arg. To indicate that to the pruning code, we=0A= * must construct a dummy PartitionPruneStepCombine=0A= * whose source_stepids is set to an empty List.=0A= - * However, if we can prove using constraint exclusion=0A= - * that the clause refutes the table's partition=0A= - * constraint (if it's sub-partitioned), we need not=0A= - * bother with that. That is, we effectively ignore=0A= - * this OR arm.=0A= */=0A= - List *partconstr =3D rel->partition_qual;=0A= PartitionPruneStep *orstep;=0A= =0A= /* Just ignore this argument. */=0A= if (arg_contradictory)=0A= continue;=0A= =0A= - if (partconstr)=0A= - {=0A= - partconstr =3D (List *)=0A= - expression_planner((Expr *) partconstr);=0A= - if (rel->relid !=3D 1)=0A= - ChangeVarNodes((Node *) partconstr, 1,=0A= - rel->relid, 0);=0A= - if (predicate_refuted_by(partconstr,=0A= - list_make1(arg),=0A= - false))=0A= - continue;=0A= - }=0A= -=0A= orstep =3D gen_prune_step_combine(context, NIL,=0A= PARTPRUNE_COMBINE_UNION);=0A= arg_stepids =3D lappend_int(arg_stepids, orstep->step_id);=0A= diff --git a/src/test/regress/expected/partition_prune.out = b/src/test/regress/expected/partition_prune.out=0A= index b46951c..716155d 100644=0A= --- a/src/test/regress/expected/partition_prune.out=0A= +++ b/src/test/regress/expected/partition_prune.out=0A= @@ -592,6 +592,24 @@ explain (costs off) select * from rlp where a < 1 = or (a > 20 and a < 25);=0A= Filter: ((a < 1) OR ((a > 20) AND (a < 25)))=0A= (5 rows)=0A= =0A= +-- where clause contradicts sub-partition's constraint =0A= +explain (costs off) select * from rlp where a =3D 20 or a =3D 40;=0A= + QUERY PLAN =0A= +----------------------------------------=0A= + Append=0A= + -> Seq Scan on rlp4_1=0A= + Filter: ((a =3D 20) OR (a =3D 40))=0A= + -> Seq Scan on rlp5_default=0A= + Filter: ((a =3D 20) OR (a =3D 40))=0A= +(5 rows)=0A= +=0A= +explain (costs off) select * from rlp3 where a =3D 20; /* empty */=0A= + QUERY PLAN =0A= +--------------------------=0A= + Result=0A= + One-Time Filter: false=0A= +(2 rows)=0A= +=0A= -- redundant clauses are eliminated=0A= explain (costs off) select * from rlp where a > 1 and a =3D 10; /* only = default */=0A= QUERY PLAN =0A= diff --git a/src/test/regress/sql/partition_prune.sql = b/src/test/regress/sql/partition_prune.sql=0A= index abc6d9e..a5e8109 100644=0A= --- a/src/test/regress/sql/partition_prune.sql=0A= +++ b/src/test/regress/sql/partition_prune.sql=0A= @@ -85,6 +85,10 @@ explain (costs off) select * from rlp where a =3D 29;=0A= explain (costs off) select * from rlp where a >=3D 29;=0A= explain (costs off) select * from rlp where a < 1 or (a > 20 and a < = 25);=0A= =0A= +-- where clause contradicts sub-partition's constraint=0A= +explain (costs off) select * from rlp where a =3D 20 or a =3D 40;=0A= +explain (costs off) select * from rlp3 where a =3D 20; /* empty */=0A= +=0A= -- redundant clauses are eliminated=0A= explain (costs off) select * from rlp where a > 1 and a =3D 10; /* only = default */=0A= explain (costs off) select * from rlp where a > 1 and a >=3D15; /* rlp3 = onwards, including default */=0A= ------=_NextPart_000_00C2_01D4EE2C.2AA75C90--