From: Yuzuko Hosoya <hosoya.yuzuko@lab.ntt.co.jp>
To: 'Thibaut' <thibaut.madelaine@dalibo.com>
To: 'Imai, Yoshikazu' <imai.yoshikazu@jp.fujitsu.com>
Cc: 'Amit Langote' <Langote_Amit_f8@lab.ntt.co.jp>
Cc: 'PostgreSQL Hackers' <pgsql-hackers@lists.postgresql.org>
Subject: RE: Problem with default partition pruning
Date: Fri, 15 Mar 2019 15:05:41 +0900
Message-ID: <001901d4daf5$1ef4f640$5cdee2c0$@lab.ntt.co.jp> (raw)
In-Reply-To: <a4968068-6401-7a9c-8bd4-6a3bc9164a86@dalibo.com>
References: <00e601d4ca86$932b8bc0$b982a340$@lab.ntt.co.jp>
<afef4fb8-c448-d54a-9628-7c26277d4895@lab.ntt.co.jp>
<010e01d4ce68$c0427b10$40c77130$@lab.ntt.co.jp>
<0F97FA9ABBDBE54F91744A9B37151A5129A78A@g01jpexmbkw24>
<a4968068-6401-7a9c-8bd4-6a3bc9164a86@dalibo.com>
Hi Thibaut,
Thanks a lot for your test and comments.
>
> Le 28/02/2019 à 09:26, Imai, Yoshikazu a écrit :
> > Hosoya-san
> >
> > On Wed, Feb 27, 2019 at 6:51 AM, Yuzuko Hosoya wrote:
> >>> From: Amit Langote [mailto:Langote_Amit_f8@lab.ntt.co.jp]
> >>> Sent: Wednesday, February 27, 2019 11:22 AM
> >>>
> >>> Hosoya-san,
> >>>
> >>> On 2019/02/22 17:14, Yuzuko Hosoya wrote:
> >>>> Hi,
> >>>>
> >>>> I found the bug of default partition pruning when executing a range
> >> query.
> >>>> -----
> >>>> postgres=# create table test1(id int, val text) partition by range
> >>>> (id); postgres=# create table test1_1 partition of test1 for values
> >>>> from (0) to (100); postgres=# create table test1_2 partition of
> >>>> test1 for values from (150) to (200); postgres=# create table
> >>>> test1_def partition of test1 default;
> >>>>
> >>>> postgres=# explain select * from test1 where id > 0 and id < 30;
> >>>> QUERY PLAN
> >>>> ----------------------------------------------------------------
> >>>> Append (cost=0.00..11.83 rows=59 width=11)
> >>>> -> Seq Scan on test1_1 (cost=0.00..5.00 rows=58 width=11)
> >>>> Filter: ((id > 0) AND (id < 30))
> >>>> -> Seq Scan on test1_def (cost=0.00..6.53 rows=1 width=12)
> >>>> Filter: ((id > 0) AND (id < 30))
> >>>> (5 rows)
> >>>>
> >>>> There is no need to scan the default partition, but it's scanned.
> >>>> -----
> >>>>
> >>>> In the current implement, whether the default partition is scanned
> >>>> or not is determined according to each condition of given WHERE
> >>>> clause at get_matching_range_bounds(). In this example,
> >>>> scan_default is set true according to id > 0 because id >= 200
> >>>> matches the default partition. Similarly, according to id < 30,
> >> scan_default is set true.
> >>>> Then, these results are combined according to AND/OR at
> >> perform_pruning_combine_step().
> >>>> In this case, final result's scan_default is set true.
> >>>>
> >>>> The modifications I made are as follows:
> >>>> - get_matching_range_bounds() determines only offsets of range bounds
> >>>> according to each condition
> >>>> - These results are combined at perform_pruning_combine_step()
> >>>> - Whether the default partition is scanned or not is determined at
> >>>> get_matching_partitions()
> >>>>
> >>>> Attached the patch. Any feedback is greatly appreciated.
> >>> Thank you for reporting. Can you please add this to March CF in
> >>> Bugs category so as not to lose
> >> track
> >>> of this?
> >>>
> >>> I will try to send review comments soon.
> >>>
> >> Thank you for your reply. I added this to March CF.
> > I tested with simple use case and I confirmed it works correctly like below.
> >
> > In case using between clause:
> > postgres=# create table test1(id int, val text) partition by range
> > (id); postgres=# create table test1_1 partition of test1 for values
> > from (0) to (100); postgres=# create table test1_2 partition of test1
> > for values from (150) to (200); postgres=# create table test1_def
> > partition of test1 default;
> >
> > [HEAD]
> > postgres=# explain analyze select * from test1 where id between 0 and 50;
> > QUERY PLAN
> > ----------------------------------------------------------------------
> > -------------------------------------
> > Append (cost=0.00..58.16 rows=12 width=36) (actual time=0.008..0.008 rows=0 loops=1)
> > -> Seq Scan on test1_1 (cost=0.00..29.05 rows=6 width=36) (actual time=0.005..0.005
> rows=0 loops=1)
> > Filter: ((id >= 0) AND (id <= 50))
> > -> Seq Scan on test1_def (cost=0.00..29.05 rows=6 width=36) (actual
> time=0.002..0.002 rows=0 loops=1)
> > Filter: ((id >= 0) AND (id <= 50))
> >
> >
> > [patched]
> > postgres=# explain analyze select * from test1 where id between 0 and 50;
> > QUERY PLAN
> > ----------------------------------------------------------------------
> > -----------------------------------
> > Append (cost=0.00..29.08 rows=6 width=36) (actual time=0.006..0.006 rows=0 loops=1)
> > -> Seq Scan on test1_1 (cost=0.00..29.05 rows=6 width=36) (actual time=0.004..0.005
> rows=0 loops=1)
> > Filter: ((id >= 0) AND (id <= 50))
> >
> >
> >
> > I considered about another use case. If default partition contains rows whose id = 300
> and then we add another partition which have constraints like id >= 300 and id < 400, I thought
> we won't scan the rows anymore. But I noticed we simply can't add such a partition.
> >
> > postgres=# insert into test1 values (300); INSERT 0 1 postgres=#
> > create table test1_3 partition of test1 for values from (300) to
> > (400);
> > ERROR: updated partition constraint for default partition "test1_def"
> > would be violated by some row
> >
> >
> > So I haven't come up with bad cases so far :)
> >
> > --
> > Yoshikazu Imai
>
> Hello Yoshikazu-San,
>
> I tested your patch using some sub-partitions and found a possible problem.
>
> I create a new partitioned partition test1_3 with 2 sub-partitions :
>
> -------------------------
>
> create table test1_3 partition of test1 for values from (200) to (400) partition by range
> (id); create table test1_3_1 partition of test1_3 for values from (200) to (250); create
> table test1_3_2 partition of test1_3 for values from (250) to (350);
>
> # explain select * from test1 where (id > 0 and id < 30);
> QUERY PLAN
> ---------------------------------------------------------------
> Append (cost=0.00..29.08 rows=6 width=36)
> -> Seq Scan on test1_1 (cost=0.00..29.05 rows=6 width=36)
> Filter: ((id > 0) AND (id < 30))
> (3 rows)
>
> # explain select * from test1 where (id > 220 and id < 230);
> QUERY PLAN
> -----------------------------------------------------------------
> Append (cost=0.00..29.08 rows=6 width=36)
> -> Seq Scan on test1_3_1 (cost=0.00..29.05 rows=6 width=36)
> Filter: ((id > 220) AND (id < 230))
> (3 rows)
>
> # explain select * from test1
> where (id > 0 and id < 30) or (id > 220 and id < 230);
> QUERY PLAN
> ---------------------------------------------------------------------------
> Append (cost=0.00..106.40 rows=39 width=36)
> -> Seq Scan on test1_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)))
> -> Seq Scan on test1_3_2 (cost=0.00..35.40 rows=13 width=36)
> Filter: (((id > 0) AND (id < 30)) OR ((id > 220) AND (id < 230)))
> (7 rows)
>
> -----------------
>
> Partition pruning is functioning when only the sub-partition is required. When both the
> partition and the sub-partition is required, there is no pruning on the sub-partition.
>
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?
--
Best regards,
Yuzuko Hosoya
Attachments:
[application/octet-stream] v2_default_partition_pruning.patch (12.9K, ../001901d4daf5$1ef4f640$5cdee2c0$@lab.ntt.co.jp/2-v2_default_partition_pruning.patch)
download | inline diff:diff --git a/src/backend/partitioning/partprune.c b/src/backend/partitioning/partprune.cindex b5c0889..302061e 100644--- a/src/backend/partitioning/partprune.c+++ b/src/backend/partitioning/partprune.c@@ -619,6 +619,7 @@ get_matching_partitions(PartitionPruneContext *context, List *pruning_steps)
PruneStepResult **results,
*final_result;
ListCell *lc;
+ bool scan_default;
/* If there are no pruning steps then all partitions match. */
if (num_steps == 0)
@@ -670,20 +671,23 @@ get_matching_partitions(PartitionPruneContext *context, List *pruning_steps)
Assert(final_result != NULL);
i = -1;
result = NULL;
+ scan_default = final_result->scan_default;
while ((i = bms_next_member(final_result->bound_offsets, i)) >= 0)
{
int partindex = context->boundinfo->indexes[i];
/*
- * In range and hash partitioning cases, some slots may contain -1,+ * In range and hash partitioning cases, some index values may be -1,
* indicating that no partition has been defined to accept a given
- * range of data or for a given remainder, respectively. The default- * partition, if any, in case of range partitioning, will be added to- * the result, because the specified range still satisfies the query's- * conditions.+ * range of values (that is, the bound at given offset is the upper+ * bound of this unassigned range of values) or for a given remainder,+ * respectively. As it's still part of the queried range of values,+ * add the default partition, if any.
*/
if (partindex >= 0)
result = bms_add_member(result, partindex);
+ else+ scan_default |= partition_bound_has_default(context->boundinfo);
}
/* Add the null and/or default partition if needed and if present. */
@@ -693,7 +697,7 @@ get_matching_partitions(PartitionPruneContext *context, List *pruning_steps)
Assert(partition_bound_accepts_nulls(context->boundinfo));
result = bms_add_member(result, context->boundinfo->null_index);
}
- if (final_result->scan_default)+ if (scan_default)
{
Assert(context->strategy == PARTITION_STRATEGY_LIST ||
context->strategy == PARTITION_STRATEGY_RANGE);
@@ -2207,6 +2211,11 @@ get_matching_hash_bounds(PartitionPruneContext *context,
* get_matching_list_bounds
* Determine the offsets of list bounds matching the specified value,
* according to the semantics of the given operator strategy
+ *+ * scan_default will be set in the returned struct, if the default partition+ * needs to be scanned, provided one exists at all. scan_null will be set if+ * the special null-accepting partition needs to be scanned.+ *
* 'opstrategy' if non-zero must be a btree strategy number.
*
* 'value' contains the value to use for pruning.
@@ -2409,8 +2418,17 @@ get_matching_list_bounds(PartitionPruneContext *context,
* Each datum whose offset is in result is to be treated as the upper bound of
* the partition that will contain the desired values.
*
- * If default partition needs to be scanned for given values, set scan_default- * in result if present.+ * scan_default will be set in the returned struct, if the default partition+ * needs to be scanned, provided one exists at all. Although note that we+ * intentionally don't set scan_default in this function if only because the+ * matching set of values, found by comparing the input values using the+ * specified opstrategy, contains unassigned portions of key space, which+ * we normally assume to belong to the default partition. We don't infer+ * that until all steps have been executed, including any combination steps,+ * because even if such unassinged portion of key space appears to be part of+ * the set of queried values based on comparisons in the individual OpExprs,+ * the final set of bounds obtained after combining multiple OpExprs may+ * exclude it.
*
* 'opstrategy' if non-zero must be a btree strategy number.
*
@@ -2436,8 +2454,7 @@ get_matching_range_bounds(PartitionPruneContext *context,
int *partindices = boundinfo->indexes;
int off,
minoff,
- maxoff,- i;+ maxoff;
bool is_equal;
bool inclusive = false;
@@ -2501,11 +2518,7 @@ get_matching_range_bounds(PartitionPruneContext *context,
if (nvalues == partnatts)
{
/* There can only be zero or one matching partition. */
- if (partindices[off + 1] >= 0)- result->bound_offsets = bms_make_singleton(off + 1);- else- result->scan_default =- partition_bound_has_default(boundinfo);+ result->bound_offsets = bms_make_singleton(off + 1);
return result;
}
else
@@ -2593,57 +2606,21 @@ get_matching_range_bounds(PartitionPruneContext *context,
maxoff = off + 1;
}
- /*- * Skip if minoff/maxoff are actually the upper bound of a- * un-assigned portion of values.- */- if (partindices[minoff] < 0 && minoff < boundinfo->ndatums)- minoff++;- if (partindices[maxoff] < 0 && maxoff >= 1)- maxoff--;-- /*- * There may exist a range of values unassigned to any- * non-default partition between the datums at minoff and- * maxoff. Add the default partition in that case.- */- if (partition_bound_has_default(boundinfo))- {- for (i = minoff; i <= maxoff; i++)- {- if (partindices[i] < 0)- {- result->scan_default = true;- break;- }- }- }-
Assert(minoff >= 0 && maxoff >= 0);
result->bound_offsets = bms_add_range(NULL, minoff, maxoff);
}
- else if (off >= 0) /* !is_equal */+ else
{
/*
* The lookup value falls in the range between some bounds in
* boundinfo. 'off' would be the offset of the greatest bound
* that is <= lookup value, so add off + 1 to the result
* instead as the offset of the upper bound of the only
- * partition that may contain the lookup value.+ * partition that may contain the lookup value. If 'off' is+ * -1 indicating that all bounds are greater, then we simply+ * end up adding the first bound's offset, that is, 0.
*/
- if (partindices[off + 1] >= 0)- result->bound_offsets = bms_make_singleton(off + 1);- else- result->scan_default =- partition_bound_has_default(boundinfo);- }- else- {- /*- * off < 0: the lookup value is smaller than all bounds, so- * only the default partition qualifies, if there is one.- */- result->scan_default = partition_bound_has_default(boundinfo);+ result->bound_offsets = bms_make_singleton(off + 1);
}
return result;
@@ -2742,15 +2719,15 @@ get_matching_range_bounds(PartitionPruneContext *context,
nvalues, values,
&is_equal);
if (off < 0)
- {- /*- * All bounds are greater than the key, so we could only- * expect to find the lookup key in the default partition.- */- result->scan_default = partition_bound_has_default(boundinfo);- return result;- }- else+ {+ /* + * All bounds are greater than the key, so we could only + * expect to find the lookup key in the default partition. + */+ result->scan_default = partition_bound_has_default(boundinfo);+ return result;+ }+ else
{
/*
* See the comment above.
@@ -2791,7 +2768,9 @@ 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.+ * 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.
*/
else if (!is_equal || inclusive)
maxoff = off + 1;
@@ -2805,58 +2784,43 @@ get_matching_range_bounds(PartitionPruneContext *context,
break;
}
+ Assert(minoff >= 0 && minoff <= boundinfo->ndatums);+ Assert(maxoff >= 0 && maxoff <= boundinfo->ndatums);+
/*
- * Skip a gap and when doing so, check if the bound contains a finite- * value to decide if we need to add the default partition. If it's an- * infinite bound, we need not add the default partition, as having an- * infinite bound means the partition in question catches any values that- * would otherwise be in the default partition.+ * If the smallest partition to return has MINVALUE (negative infinity) as+ * its lower bound, increment it to point to the next finite bound+ * (supposedly its upper bound), so that we don't advertently end up+ * scanning the default partition.
*/
- if (partindices[minoff] < 0)+ if (minoff < boundinfo->ndatums && partindices[minoff] < 0)
{
int lastkey = nvalues - 1;
- if (minoff >= 0 &&- minoff < boundinfo->ndatums &&- boundinfo->kind[minoff][lastkey] ==- PARTITION_RANGE_DATUM_VALUE)- result->scan_default = partition_bound_has_default(boundinfo);-- minoff++;+ if (boundinfo->kind[minoff][lastkey] ==+ PARTITION_RANGE_DATUM_MINVALUE)+ {+ minoff++;+ Assert(boundinfo->indexes[minoff] >= 0);+ }
}
/*
- * Skip a gap. See the above comment about how we decide whether or not- * to scan the default partition based whether the datum that will become- * the maximum datum is finite or not.+ * If the previous greatest partition has MAXVALUE (positive infinity) as+ * its upper bound (something only possible to do with multi-column range+ * partitioning), we scan switch to it as the greatest partition to+ * return. Again, so that we don't advertently end up scanning the+ * default partition.
*/
if (maxoff >= 1 && partindices[maxoff] < 0)
{
int lastkey = nvalues - 1;
- if (maxoff >= 0 &&- maxoff <= boundinfo->ndatums &&- boundinfo->kind[maxoff - 1][lastkey] ==- PARTITION_RANGE_DATUM_VALUE)- result->scan_default = partition_bound_has_default(boundinfo);-- maxoff--;- }-- if (partition_bound_has_default(boundinfo))- {- /*- * There may exist a range of values unassigned to any non-default- * partition between the datums at minoff and maxoff. Add the default- * partition in that case.- */- for (i = minoff; i <= maxoff; i++)+ if (boundinfo->kind[maxoff - 1][lastkey] ==+ PARTITION_RANGE_DATUM_MAXVALUE)
{
- if (partindices[i] < 0)- {- result->scan_default = true;- break;- }+ maxoff--;+ Assert(boundinfo->indexes[maxoff] >= 0);
}
}
@@ -3136,14 +3100,23 @@ perform_pruning_combine_step(PartitionPruneContext *context,
/*
* A combine step without any source steps is an indication to not perform
- * any partition pruning, we just return all partitions.+ * any partition pruning, we just return all datum offsets.
*/
result = (PruneStepResult *) palloc0(sizeof(PruneStepResult));
if (list_length(cstep->source_stepids) == 0)
{
PartitionBoundInfo boundinfo = context->boundinfo;
- result->bound_offsets = bms_add_range(NULL, 0, boundinfo->ndatums - 1);+ /*+ * Add all valid offsets into the boundinfo->indexes array. For range+ * partitioning, boundinfo->indexes contains (boundinfo->ndatums + 1)+ * valid entries.+ */+ if (context->strategy == PARTITION_STRATEGY_RANGE)+ result->bound_offsets = bms_add_range(NULL, 0, boundinfo->ndatums);+ else+ result->bound_offsets = bms_add_range(NULL, 0,+ boundinfo->ndatums - 1);
result->scan_default = partition_bound_has_default(boundinfo);
result->scan_null = partition_bound_accepts_nulls(boundinfo);
return result;
diff --git a/src/test/regress/expected/partition_prune.out b/src/test/regress/expected/partition_prune.outindex 30946f7..046e722 100644--- a/src/test/regress/expected/partition_prune.out+++ b/src/test/regress/expected/partition_prune.out@@ -517,15 +517,13 @@ explain (costs off) select * from rlp where a <= 31;
Filter: (a <= 31)
-> Seq Scan on rlp5_1
Filter: (a <= 31)
- -> Seq Scan on rlp5_default- Filter: (a <= 31)
-> Seq Scan on rlp_default_10
Filter: (a <= 31)
-> Seq Scan on rlp_default_30
Filter: (a <= 31)
-> Seq Scan on rlp_default_default
Filter: (a <= 31)
-(29 rows)+(27 rows)
explain (costs off) select * from rlp where a = 1 or a = 7;
QUERY PLAN
@@ -573,11 +571,7 @@ explain (costs off) select * from rlp where a > 20 and a < 27;
Filter: ((a > 20) AND (a < 27))
-> Seq Scan on rlp4_2
Filter: ((a > 20) AND (a < 27))
- -> Seq Scan on rlp4_default- Filter: ((a > 20) AND (a < 27))- -> Seq Scan on rlp_default_default- Filter: ((a > 20) AND (a < 27))-(9 rows)+(5 rows)
explain (costs off) select * from rlp where a = 29;
QUERY PLAN
=
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, thibaut.madelaine@dalibo.com, imai.yoshikazu@jp.fujitsu.com, Langote_Amit_f8@lab.ntt.co.jp, pgsql-hackers@lists.postgresql.org
Subject: RE: Problem with default partition pruning
In-Reply-To: <001901d4daf5$1ef4f640$5cdee2c0$@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