Hosoya-san,
On 2019/03/15 15:05, Yuzuko Hosoya wrote:
> Indeed, it's problematic. I also did test and I found that
> this problem was occurred when any partition didn't match
> WHERE clauses. So following query didn't work correctly.
>
> # explain select * from test1_3 where (id > 0 and id < 30);
> QUERY PLAN
> -----------------------------------------------------------------
> Append (cost=0.00..58.16 rows=12 width=36)
> -> Seq Scan on test1_3_1 (cost=0.00..29.05 rows=6 width=36)
> Filter: ((id > 0) AND (id < 30))
> -> Seq Scan on test1_3_2 (cost=0.00..29.05 rows=6 width=36)
> Filter: ((id > 0) AND (id < 30))
> (5 rows)
>
> I created a new patch to handle this problem, and confirmed
> the query you mentioned works as expected
>
> # explain select * from test1 where (id > 0 and id < 30) or (id > 220 and id
> < 230);
> QUERY PLAN
> ---------------------------------------------------------------------------
> Append (cost=0.00..70.93 rows=26 width=36)
> -> Seq Scan on test1_1_1 (cost=0.00..35.40 rows=13 width=36)
> Filter: (((id > 0) AND (id < 30)) OR ((id > 220) AND (id < 230)))
> -> Seq Scan on test1_3_1 (cost=0.00..35.40 rows=13 width=36)
> Filter: (((id > 0) AND (id < 30)) OR ((id > 220) AND (id < 230)))
> (5 rows)
>
> v2 patch attached.
> Could you please check it again?
I think the updated patch breaks the promise that
get_matching_range_bounds won't set scan_default based on individual
pruning value comparisons. How about the attached delta patch that
applies on top of your earlier v1 patch, which fixes the issue reported by
Thibaut?
Thanks,
Amit
diff --git a/src/backend/partitioning/partprune.c
b/src/backend/partitioning/partprune.c
index 8317318156..e64fd1cea6 100644
--- a/src/backend/partitioning/partprune.c
+++ b/src/backend/partitioning/partprune.c
@@ -2759,15 +2759,19 @@ get_matching_range_bounds(PartitionPruneContext
*context,
* instead as the offset of the upper bound of
the greatest
* partition that may contain lookup value. If
the lookup
* value had exactly matched the bound, but it
isn't
- * inclusive, no need add the adjacent
partition. If 'off' is
- * -1 indicating that all bounds are greater,
then we simply
- * end up adding the first bound's offset, that
is, 0.
+ * inclusive, no need add the adjacent
partition.
*/
- else if (off < 0 || !is_equal || inclusive)
+ else if (!is_equal || inclusive)
maxoff = off + 1;
else
maxoff = off;
}
+ else
+ /*
+ * 'off' is -1 indicating that all bounds are
greater, so just
+ * set the first bound's offset as maxoff.
+ */
+ maxoff = off + 1;
break;
default:
