From fb9884419dd05f0e774cb193ed4e8950783ebbeb Mon Sep 17 00:00:00 2001 From: Etsuro Fujita Date: Fri, 22 Nov 2019 21:56:40 +0900 Subject: [PATCH 2/2] Modify merge_null_partitions() --- src/backend/partitioning/partbounds.c | 490 ++++++++++--------- src/test/regress/expected/partition_join.out | 292 ++++++----- src/test/regress/sql/partition_join.sql | 5 +- 3 files changed, 427 insertions(+), 360 deletions(-) diff --git a/src/backend/partitioning/partbounds.c b/src/backend/partitioning/partbounds.c index a362998e57..494632fc7c 100644 --- a/src/backend/partitioning/partbounds.c +++ b/src/backend/partitioning/partbounds.c @@ -141,7 +141,7 @@ static int map_and_merge_partitions(PartitionMap *outer_map, PartitionMap *inner int outer_part, int inner_part, int *next_index); static int merge_partition_with_dummy(PartitionMap *map, int index, int *next_index); -static bool process_outer_partition(PartitionMap *outer_map, +static int process_outer_partition(PartitionMap *outer_map, PartitionMap *inner_map, bool outer_has_default, bool inner_has_default, @@ -149,9 +149,8 @@ static bool process_outer_partition(PartitionMap *outer_map, int inner_default, JoinType jointype, int *next_index, - int *default_index, - int *merged_index); -static bool process_inner_partition(PartitionMap *outer_map, + int *default_index); +static int process_inner_partition(PartitionMap *outer_map, PartitionMap *inner_map, bool outer_has_default, bool inner_has_default, @@ -159,8 +158,7 @@ static bool process_inner_partition(PartitionMap *outer_map, int outer_default, JoinType jointype, int *next_index, - int *default_index, - int *merged_index); + int *default_index); static void fix_merged_indexes(PartitionMap *outer_map, PartitionMap *inner_map, int nmerged, List *merged_indexes); static void generate_matching_part_pairs(RelOptInfo *outer_rel, @@ -200,21 +198,24 @@ static void add_merged_range_bounds(int partnatts, FmgrInfo *partsupfuncs, List **merged_datums, List **merged_kinds, List **merged_indexes); -static bool merge_default_partitions(PartitionMap *outer_map, +static void merge_default_partitions(PartitionMap *outer_map, PartitionMap *inner_map, bool outer_has_default, - int outer_default, bool inner_has_default, + int outer_default, int inner_default, JoinType jointype, int *next_index, int *default_index); -static bool merge_null_partitions(PartitionBoundInfo outer_bi, PartitionBoundInfo inner_bi, - PartitionMap *outer_map, PartitionMap *inner_map, - bool outer_has_default, bool inner_has_default, - bool outer_has_null, bool inner_has_null, - JoinType jointype, int *next_index, - int *default_index, int *null_index); +static void merge_null_partitions(PartitionMap *outer_map, + PartitionMap *inner_map, + bool outer_has_null, + bool inner_has_null, + int outer_null, + int inner_null, + JoinType jointype, + int *next_index, + int *null_index); /* * get_qual_from_partbound @@ -3554,11 +3555,11 @@ partition_range_bounds_merge(int partnatts, FmgrInfo *partsupfuncs, if (ub_cmpval > 0 && inner_part >= 0 && compare_range_bounds(partnatts, partsupfuncs, partcollations, &save_outer_ub, &inner_lb) > 0) - return NULL; + goto cleanup; if (ub_cmpval < 0 && outer_part >= 0 && compare_range_bounds(partnatts, partsupfuncs, partcollations, &outer_lb, &save_inner_ub) < 0) - return NULL; + goto cleanup; /* * A row from a non-overlapping portion (if any) of a partition @@ -3568,7 +3569,7 @@ partition_range_bounds_merge(int partnatts, FmgrInfo *partsupfuncs, */ if ((outer_has_default && (lb_cmpval > 0 || ub_cmpval < 0)) || (inner_has_default && (lb_cmpval < 0 || ub_cmpval > 0))) - return NULL; + goto cleanup; } else if (ub_cmpval < 0) { @@ -3579,17 +3580,17 @@ partition_range_bounds_merge(int partnatts, FmgrInfo *partsupfuncs, if (inner_has_default || IS_OUTER_JOIN(jointype)) { - if (!process_outer_partition(&outer_map, - &inner_map, - outer_has_default, - inner_has_default, - outer_part, - inner_default, - jointype, - &next_index, - &default_index, - &merged_index)) - return NULL; + merged_index = process_outer_partition(&outer_map, + &inner_map, + outer_has_default, + inner_has_default, + outer_part, + inner_default, + jointype, + &next_index, + &default_index); + if (merged_index == -1) + goto cleanup; } /* Move to the next partition on the outer side. */ @@ -3606,17 +3607,17 @@ partition_range_bounds_merge(int partnatts, FmgrInfo *partsupfuncs, if (outer_has_default || jointype == JOIN_FULL) { - if (!process_inner_partition(&outer_map, - &inner_map, - outer_has_default, - inner_has_default, - inner_part, - outer_default, - jointype, - &next_index, - &default_index, - &merged_index)) - return NULL; + merged_index = process_inner_partition(&outer_map, + &inner_map, + outer_has_default, + inner_has_default, + inner_part, + outer_default, + jointype, + &next_index, + &default_index); + if (merged_index == -1) + goto cleanup; } /* Move to the next partition on the inner side. */ @@ -3624,7 +3625,7 @@ partition_range_bounds_merge(int partnatts, FmgrInfo *partsupfuncs, &inner_lb, &inner_ub); } - if (merged_index >= 0) + if (merged_index >= 0 && merged_index != default_index) { /* Add the range bounds of the merged partition. */ add_merged_range_bounds(partnatts, partsupfuncs, partcollations, @@ -3636,18 +3637,10 @@ partition_range_bounds_merge(int partnatts, FmgrInfo *partsupfuncs, /* Merge default partitions if any. */ if (outer_has_default || inner_has_default) - { - if (!merge_default_partitions(&outer_map, - &inner_map, - outer_has_default, - outer_default, - inner_has_default, - inner_default, - jointype, - &next_index, - &default_index)) - return NULL; - } + merge_default_partitions(&outer_map, &inner_map, + outer_has_default, inner_has_default, + outer_default, inner_default, + jointype, &next_index, &default_index); else Assert(default_index == -1); @@ -3675,7 +3668,8 @@ partition_range_bounds_merge(int partnatts, FmgrInfo *partsupfuncs, -1, default_index); } - /* Free any memory we used in this function. */ +cleanup: + /* Free local memory before returning. */ free_partition_map(&outer_map); free_partition_map(&inner_map); list_free(merged_datums); @@ -3789,8 +3783,8 @@ partition_list_bounds_merge(FmgrInfo *partsupfunc, Oid *partcollation, o_index, i_index, &next_index); - if (merged_index < 0) - return NULL; + if (merged_index == -1) + goto cleanup; /* Move to the next pair of bounds. */ cnto++; @@ -3808,17 +3802,17 @@ partition_list_bounds_merge(FmgrInfo *partsupfunc, Oid *partcollation, int o_index = outer_bi->indexes[cnto]; Assert(o_index >= 0); - if (!process_outer_partition(&outer_map, - &inner_map, - outer_has_default, - inner_has_default, - o_index, - inner_default, - jointype, - &next_index, - &default_index, - &merged_index)) - return NULL; + merged_index = process_outer_partition(&outer_map, + &inner_map, + outer_has_default, + inner_has_default, + o_index, + inner_default, + jointype, + &next_index, + &default_index); + if (merged_index == -1) + goto cleanup; } /* Move to the next datum on the outer side. */ @@ -3837,17 +3831,17 @@ partition_list_bounds_merge(FmgrInfo *partsupfunc, Oid *partcollation, int i_index = inner_bi->indexes[cnti]; Assert(i_index >= 0); - if (!process_inner_partition(&outer_map, - &inner_map, - outer_has_default, - inner_has_default, - i_index, - outer_default, - jointype, - &next_index, - &default_index, - &merged_index)) - return NULL; + merged_index = process_inner_partition(&outer_map, + &inner_map, + outer_has_default, + inner_has_default, + i_index, + outer_default, + jointype, + &next_index, + &default_index); + if (merged_index == -1) + goto cleanup; } /* Move to the next datum on the inner side. */ @@ -3858,7 +3852,7 @@ partition_list_bounds_merge(FmgrInfo *partsupfunc, Oid *partcollation, * Add the list value with appropriate index in the list of datums, if * we have associated a partition with this list value. */ - if (merged_index >= 0) + if (merged_index >= 0 && merged_index != default_index) { merged_indexes = lappend_int(merged_indexes, merged_index); merged_datums = lappend(merged_datums, merged_datum); @@ -3867,27 +3861,19 @@ partition_list_bounds_merge(FmgrInfo *partsupfunc, Oid *partcollation, /* Merge null partitions if any. */ if (outer_has_null || inner_has_null) - { - if (!merge_null_partitions(outer_bi, inner_bi, - &outer_map, &inner_map, - outer_has_default, inner_has_default, - outer_has_null, inner_has_null, - jointype, &next_index, &default_index, - &null_index)) - return NULL; - } + merge_null_partitions(&outer_map, &inner_map, + outer_has_null, inner_has_null, + outer_bi->null_index, inner_bi->null_index, + jointype, &next_index, &null_index); else Assert(null_index == -1); /* Merge default partitions if any. */ if (outer_has_default || inner_has_default) - { - if (!merge_default_partitions(&outer_map, &inner_map, - outer_has_default, outer_default, - inner_has_default, inner_default, - jointype, &next_index, &default_index)) - return NULL; - } + merge_default_partitions(&outer_map, &inner_map, + outer_has_default, inner_has_default, + outer_default, inner_default, + jointype, &next_index, &default_index); else Assert(default_index == -1); @@ -3916,7 +3902,8 @@ partition_list_bounds_merge(FmgrInfo *partsupfunc, Oid *partcollation, null_index, default_index); } - /* Free up all extra memory before returning from this function. */ +cleanup: + /* Free local memory before returning. */ free_partition_map(&outer_map); free_partition_map(&inner_map); list_free(merged_datums); @@ -4099,7 +4086,7 @@ merge_partition_with_dummy(PartitionMap *map, int index, int *next_index) * *next_index is incremented when creating a new merged partition associated * with the given outer partition. */ -static bool +static int process_outer_partition(PartitionMap *outer_map, PartitionMap *inner_map, bool outer_has_default, @@ -4108,9 +4095,10 @@ process_outer_partition(PartitionMap *outer_map, int inner_default, JoinType jointype, int *next_index, - int *default_index, - int *merged_index) + int *default_index) { + int merged_index = -1; + Assert(outer_index >= 0); /* @@ -4131,13 +4119,13 @@ process_outer_partition(PartitionMap *outer_map, * up on it. */ if (outer_has_default) - return false; + return -1; - *merged_index = map_and_merge_partitions(outer_map, inner_map, - outer_index, inner_default, - next_index); - if (*merged_index == -1) - return false; + merged_index = map_and_merge_partitions(outer_map, inner_map, + outer_index, inner_default, + next_index); + if (merged_index == -1) + return -1; /* * If this is a FULL join, the merged partition would act as the @@ -4147,11 +4135,9 @@ process_outer_partition(PartitionMap *outer_map, if (jointype == JOIN_FULL) { if (*default_index == -1) - *default_index = *merged_index; + *default_index = merged_index; else - Assert(*merged_index == *default_index); - /* Don't add this index to the list of merged indexes. */ - *merged_index = -1; + Assert(*default_index == merged_index); } } else @@ -4159,13 +4145,12 @@ process_outer_partition(PartitionMap *outer_map, Assert(IS_OUTER_JOIN(jointype)); Assert(jointype != JOIN_RIGHT); - if (outer_map->merged_indexes[outer_index] >= 0) - *merged_index = outer_map->merged_indexes[outer_index]; - else - *merged_index = merge_partition_with_dummy(outer_map, outer_index, - next_index); + merged_index = outer_map->merged_indexes[outer_index]; + if (merged_index == -1) + merged_index = merge_partition_with_dummy(outer_map, outer_index, + next_index); } - return true; + return merged_index; } /* @@ -4176,7 +4161,7 @@ process_outer_partition(PartitionMap *outer_map, * *next_index is incremented when creating a new merged partition associated * with the given inner partition. */ -static bool +static int process_inner_partition(PartitionMap *outer_map, PartitionMap *inner_map, bool outer_has_default, @@ -4185,9 +4170,10 @@ process_inner_partition(PartitionMap *outer_map, int outer_default, JoinType jointype, int *next_index, - int *default_index, - int *merged_index) + int *default_index) { + int merged_index = -1; + Assert(inner_index >= 0); /* @@ -4208,13 +4194,13 @@ process_inner_partition(PartitionMap *outer_map, * up on it. */ if (inner_has_default) - return false; + return -1; - *merged_index = map_and_merge_partitions(outer_map, inner_map, - outer_default, inner_index, - next_index); - if (*merged_index == -1) - return false; + merged_index = map_and_merge_partitions(outer_map, inner_map, + outer_default, inner_index, + next_index); + if (merged_index == -1) + return -1; /* * If this is an outer join, the merged partition would act as the @@ -4225,24 +4211,21 @@ process_inner_partition(PartitionMap *outer_map, { Assert(jointype != JOIN_RIGHT); if (*default_index == -1) - *default_index = *merged_index; + *default_index = merged_index; else - Assert(*merged_index == *default_index); - /* Don't add this index to the list of merged indexes. */ - *merged_index = -1; + Assert(*default_index == merged_index); } } else { Assert(jointype == JOIN_FULL); - if (inner_map->merged_indexes[inner_index] >= 0) - *merged_index = inner_map->merged_indexes[inner_index]; - else - *merged_index = merge_partition_with_dummy(inner_map, inner_index, - next_index); + merged_index = inner_map->merged_indexes[inner_index]; + if (merged_index == -1) + merged_index = merge_partition_with_dummy(inner_map, inner_index, + next_index); } - return true; + return merged_index; } /* @@ -4430,175 +4413,200 @@ build_merged_partition_bounds(char strategy, List *merged_datums, } /* - * Merge default partitions from both sides, if any, and assign the default - * partition for the join result, if necessary. - * - * If both the relations have default partitions, try mapping those to each - * other. If the mapping succeeds corresponding merged partition will act as - * the default partition of the join result. + * merge_default_partitions + * Merge the default partitions from both sides, if any * - * If inner side of the join has default but not the outer side, rows in it - * won't appear in the join result. So don't create a default partition. If - * outer side of the join has default but not the inner side, rows in it will - * appear in the join result, so create a default merged partition. + * *default_index is set to the index of the default partition of the joinrel, + * if appropriate. */ -static bool -merge_default_partitions(PartitionMap *outer_map, PartitionMap *inner_map, - bool outer_has_default, int outer_default, - bool inner_has_default, int inner_default, - JoinType jointype, int *next_index, +static void +merge_default_partitions(PartitionMap *outer_map, + PartitionMap *inner_map, + bool outer_has_default, + bool inner_has_default, + int outer_default, + int inner_default, + JoinType jointype, + int *next_index, int *default_index) { + int outer_merged_index = -1; + int inner_merged_index = -1; + Assert(outer_has_default || inner_has_default); + if (outer_has_default) + { + Assert(outer_default >= 0 && outer_default < outer_map->nparts); + outer_merged_index = outer_map->merged_indexes[outer_default]; + } + if (inner_has_default) + { + Assert(inner_default >= 0 && inner_default < inner_map->nparts); + inner_merged_index = inner_map->merged_indexes[inner_default]; + } + if (outer_has_default && !inner_has_default) { + /* + * If this is an outer join, assign a new merged partition if not + * already done. The resulting partition should be the default + * partition of the joinrel. + */ if (IS_OUTER_JOIN(jointype)) { - int merged_index; - Assert(jointype != JOIN_RIGHT); - Assert(outer_default >= 0 && outer_default < outer_map->nparts); - merged_index = outer_map->merged_indexes[outer_default]; - if (merged_index == -1) - { - Assert(*default_index == -1); + if (outer_merged_index == -1) *default_index = merge_partition_with_dummy(outer_map, outer_default, next_index); - } else - Assert(*default_index == merged_index); + Assert(*default_index == outer_merged_index); } else - Assert(*default_index < 0); + Assert(*default_index == -1); } else if (!outer_has_default && inner_has_default) { + /* + * If this is a FULL join, assign a new merged partition if not + * already done. The resulting partition should be the default + * partition of the joinrel. + */ if (jointype == JOIN_FULL) { - int merged_index; - - Assert(inner_default >= 0 && inner_default < inner_map->nparts); - merged_index = inner_map->merged_indexes[inner_default]; - if (merged_index == -1) - { - Assert(*default_index == -1); + if (inner_merged_index == -1) *default_index = merge_partition_with_dummy(inner_map, inner_default, next_index); - } else - Assert(*default_index == merged_index); + Assert(*default_index == inner_merged_index); } else - Assert(*default_index < 0); + Assert(*default_index == -1); } else { Assert(outer_has_default && inner_has_default); + Assert(outer_merged_index == -1); + Assert(inner_merged_index == -1); + Assert(*default_index == -1); + /* + * Merge the default partitions. Both partitions aren't merged yet, + * so the partitions should be merged successfully. The resulting + * partition should be the default partition of the joinrel. + */ *default_index = map_and_merge_partitions(outer_map, inner_map, outer_default, inner_default, next_index); - if (*default_index == -1) - return false; + Assert(*default_index >= 0); } - - return true; } /* * merge_null_partitions + * Merge the NULL partitions from both sides, if any * - * Merge NULL partitions, i.e. a partition that can hold NULL values for a list - * partitioned table, if any. Find the index of merged partition to which the - * NULL values would belong in the join result. If one joining relation has a - * NULL partition but not the other, try matching it with the default partition - * from the other relation since the default partition may have rows with NULL - * partition key. We can eliminate a NULL partition when it appears only on the - * inner side of the join and the outer side doesn't have a default partition. - * - * When the equality operator used for join is strict, two NULL values will not - * be considered as equal, and thus a NULL partition can be eliminated for an - * inner join. But we don't check the strictness operator here. + * *null_index is set to the index of the NULL partition of the joinrel, if + * appropriate. */ -static bool -merge_null_partitions(PartitionBoundInfo outer_bi, PartitionBoundInfo inner_bi, - PartitionMap *outer_map, PartitionMap *inner_map, - bool outer_has_default, bool inner_has_default, - bool outer_has_null, bool inner_has_null, - JoinType jointype, int *next_index, - int *default_index, int *null_index) +static void +merge_null_partitions(PartitionMap *outer_map, + PartitionMap *inner_map, + bool outer_has_null, + bool inner_has_null, + int outer_null, + int inner_null, + JoinType jointype, + int *next_index, + int *null_index) { + bool merge_outer_null = false; + bool merge_inner_null = false; + Assert(outer_has_null || inner_has_null); Assert(*null_index == -1); - if (outer_has_null && !inner_has_null) + /* + * If this is an INNER or SEMI join, two NULL values won't be considered + * to be equal, in which case we don't need to do anything. + */ + if (jointype == JOIN_INNER || jointype == JOIN_SEMI) + return; + + Assert(IS_OUTER_JOIN(jointype)); + Assert(jointype != JOIN_RIGHT); + + if (outer_has_null) { - int merged_index = -1; + /* + * Since this is an outer join, if the NULL partition of the outer + * side isn't merged yet, it means that the partition contains only + * NULL values as the key values, in which case we need to create a + * merged partition below. + */ + Assert(outer_null >= 0 && outer_null < outer_map->nparts); + if (outer_map->merged_indexes[outer_null] == -1) + merge_outer_null = true; + } + if (inner_has_null) + { + /* + * If this is a FULL join, and the NULL partition of the inner side + * isn't merged yet, it means that the partition contains only NULL + * values as the key values, in which case we need to create a merged + * partition below. + */ + Assert(inner_null >= 0 && inner_null < inner_map->nparts); + if (jointype == JOIN_FULL && + inner_map->merged_indexes[inner_null] == -1) + merge_inner_null = true; + } + + if (!merge_outer_null && !merge_inner_null) + return; + + if (merge_outer_null && !merge_inner_null) + { + Assert(outer_has_null); /* - * If the NULL partition was missing from the inner side of the join, - * the partition of the join to which the NULL partition matches will - * contain the NULL values and thus become the NULL partition of the - * the join. + * Assign a new merged partition. The resulting partition should be + * the NULL partition of the joinrel. */ - if (inner_has_default || IS_OUTER_JOIN(jointype)) - { - if (!process_outer_partition(outer_map, - inner_map, - outer_has_default, - inner_has_default, - outer_bi->null_index, - inner_bi->default_index, - jointype, - next_index, - default_index, - &merged_index)) - return false; - } - *null_index = merged_index; + *null_index = merge_partition_with_dummy(outer_map, outer_null, + next_index); } - else if (!outer_has_null && inner_has_null) + else if (!merge_outer_null && merge_inner_null) { - int merged_index = -1; + Assert(inner_has_null); + Assert(jointype == JOIN_FULL); /* - * If the NULL partition was missing from the outer side of the join, - * the partition of the join to which the NULL partition matches will - * contain the NULL values and thus become the NULL partition of the - * the join. + * Assign a new merged partition. The resulting partition should be + * the NULL partition of the joinrel. */ - if (outer_has_default || jointype == JOIN_FULL) - { - if (!process_inner_partition(outer_map, - inner_map, - outer_has_default, - inner_has_default, - inner_bi->null_index, - outer_bi->default_index, - jointype, - next_index, - default_index, - &merged_index)) - return false; - } - *null_index = merged_index; + *null_index = merge_partition_with_dummy(inner_map, inner_null, + next_index); } else { - /* Both the relations have NULL partitions, try merging them. */ - *null_index = map_and_merge_partitions(outer_map, - inner_map, - outer_bi->null_index, - inner_bi->null_index, + Assert(merge_outer_null && merge_inner_null); + Assert(outer_has_null); + Assert(inner_has_null); + Assert(jointype == JOIN_FULL); + + /* + * Merge the NULL partitions. Both partitions aren't merged yet, so + * the partitions should be merged successfully. The resulting + * partition should be the NULL partition of the joinrel. + */ + *null_index = map_and_merge_partitions(outer_map, inner_map, + outer_null, inner_null, next_index); - if (*null_index == -1) - return false; + Assert(*null_index >= 0); } - - return true; } diff --git a/src/test/regress/expected/partition_join.out b/src/test/regress/expected/partition_join.out index 1675abb1e7..25249adf9c 100644 --- a/src/test/regress/expected/partition_join.out +++ b/src/test/regress/expected/partition_join.out @@ -3866,10 +3866,7 @@ select t1.a, t1.b, t1.c from plt2 t1 where not exists (select 1 from plt1 t2 WHE 470 | 0 | (1 row) --- partition have a NULL on both side with different partition bounds w.r.t other side --- NULL when NULL comparision is not strict i.e. NULL=NULL allowed --- Partition-wise join can not handle the case when one partition from one side --- matches with multiple partitions on the other side +-- test the null-partition handling in the new partition-matching algorithm DROP TABLE plt1_p3; CREATE TABLE plt1_p3 PARTITION OF plt1 FOR VALUES IN (NULL,'0008','0009'); INSERT INTO plt1 SELECT i, i % :cond_mod, case when i % :part_mod = 7 then NULL else to_char(i % :part_mod, 'FM0000') end FROM generate_series(0, :num_rows) i WHERE i % :part_mod IN (7,8,9); @@ -3877,94 +3874,138 @@ ANALYZE plt1; -- inner join EXPLAIN (COSTS OFF) SELECT t1.a, t1.c, t2.a, t2.c FROM plt1 t1 INNER JOIN plt2 t2 ON t1.c = t2.c WHERE t1.b + t2.b = 0 ORDER BY t1.a; - QUERY PLAN --------------------------------------------------- + QUERY PLAN +------------------------------------------------------------ Sort Sort Key: t1.a - -> Hash Join - Hash Cond: ((t2.c)::text = (t1.c)::text) - Join Filter: ((t1.b + t2.b) = 0) - -> Append + -> Append + -> Hash Join + Hash Cond: ((t2.c)::text = (t1.c)::text) + Join Filter: ((t1.b + t2.b) = 0) -> Seq Scan on plt2_p4 t2 - -> Seq Scan on plt2_p1 t2_1 - -> Seq Scan on plt2_p2 t2_2 - -> Seq Scan on plt2_p3 t2_3 - -> Hash - -> Append + -> Hash -> Seq Scan on plt1_p4 t1 + -> Hash Join + Hash Cond: ((t2_1.c)::text = (t1_1.c)::text) + Join Filter: ((t1_1.b + t2_1.b) = 0) + -> Seq Scan on plt2_p1 t2_1 + -> Hash -> Seq Scan on plt1_p1 t1_1 - -> Seq Scan on plt1_p2 t1_2 - -> Seq Scan on plt1_p3 t1_3 -(16 rows) + -> Hash Join + Hash Cond: ((t1_2.c)::text = (t2_2.c)::text) + Join Filter: ((t1_2.b + t2_2.b) = 0) + -> Seq Scan on plt1_p2 t1_2 + -> Hash + -> Seq Scan on plt2_p2 t2_2 + -> Hash Join + Hash Cond: ((t1_3.c)::text = (t2_3.c)::text) + Join Filter: ((t1_3.b + t2_3.b) = 0) + -> Seq Scan on plt1_p3 t1_3 + -> Hash + -> Seq Scan on plt2_p3 t2_3 +(27 rows) -- left join EXPLAIN (COSTS OFF) SELECT t1.a, t1.c, t2.a, t2.c FROM plt1 t1 LEFT JOIN plt2 t2 ON t1.c = t2.c WHERE t1.b + coalesce(t2.b, 0) = 0 ORDER BY t1.a; - QUERY PLAN --------------------------------------------------- + QUERY PLAN +------------------------------------------------------------ Sort Sort Key: t1.a - -> Hash Right Join - Hash Cond: ((t2.c)::text = (t1.c)::text) - Filter: ((t1.b + COALESCE(t2.b, 0)) = 0) - -> Append + -> Append + -> Hash Right Join + Hash Cond: ((t2.c)::text = (t1.c)::text) + Filter: ((t1.b + COALESCE(t2.b, 0)) = 0) -> Seq Scan on plt2_p4 t2 - -> Seq Scan on plt2_p1 t2_1 - -> Seq Scan on plt2_p2 t2_2 - -> Seq Scan on plt2_p3 t2_3 - -> Hash - -> Append + -> Hash -> Seq Scan on plt1_p4 t1 + -> Hash Right Join + Hash Cond: ((t2_1.c)::text = (t1_1.c)::text) + Filter: ((t1_1.b + COALESCE(t2_1.b, 0)) = 0) + -> Seq Scan on plt2_p1 t2_1 + -> Hash -> Seq Scan on plt1_p1 t1_1 - -> Seq Scan on plt1_p2 t1_2 - -> Seq Scan on plt1_p3 t1_3 -(16 rows) + -> Hash Left Join + Hash Cond: ((t1_2.c)::text = (t2_2.c)::text) + Filter: ((t1_2.b + COALESCE(t2_2.b, 0)) = 0) + -> Seq Scan on plt1_p2 t1_2 + -> Hash + -> Seq Scan on plt2_p2 t2_2 + -> Hash Left Join + Hash Cond: ((t1_3.c)::text = (t2_3.c)::text) + Filter: ((t1_3.b + COALESCE(t2_3.b, 0)) = 0) + -> Seq Scan on plt1_p3 t1_3 + -> Hash + -> Seq Scan on plt2_p3 t2_3 +(27 rows) -- right join EXPLAIN (COSTS OFF) SELECT t1.a, t1.c, t2.a, t2.c FROM plt1 t1 RIGHT JOIN plt2 t2 ON t1.c = t2.c WHERE coalesce(t1.b, 0) + t2.b = 0 ORDER BY t2.a; - QUERY PLAN --------------------------------------------------- + QUERY PLAN +------------------------------------------------------------ Sort Sort Key: t2.a - -> Hash Left Join - Hash Cond: ((t2.c)::text = (t1.c)::text) - Filter: ((COALESCE(t1.b, 0) + t2.b) = 0) - -> Append + -> Append + -> Hash Left Join + Hash Cond: ((t2.c)::text = (t1.c)::text) + Filter: ((COALESCE(t1.b, 0) + t2.b) = 0) -> Seq Scan on plt2_p4 t2 - -> Seq Scan on plt2_p1 t2_1 - -> Seq Scan on plt2_p2 t2_2 - -> Seq Scan on plt2_p3 t2_3 - -> Hash - -> Append + -> Hash -> Seq Scan on plt1_p4 t1 + -> Hash Left Join + Hash Cond: ((t2_1.c)::text = (t1_1.c)::text) + Filter: ((COALESCE(t1_1.b, 0) + t2_1.b) = 0) + -> Seq Scan on plt2_p1 t2_1 + -> Hash -> Seq Scan on plt1_p1 t1_1 + -> Hash Left Join + Hash Cond: ((t2_2.c)::text = (t1_2.c)::text) + Filter: ((COALESCE(t1_2.b, 0) + t2_2.b) = 0) + -> Seq Scan on plt2_p2 t2_2 + -> Hash -> Seq Scan on plt1_p2 t1_2 - -> Seq Scan on plt1_p3 t1_3 -(16 rows) + -> Hash Right Join + Hash Cond: ((t1_3.c)::text = (t2_3.c)::text) + Filter: ((COALESCE(t1_3.b, 0) + t2_3.b) = 0) + -> Seq Scan on plt1_p3 t1_3 + -> Hash + -> Seq Scan on plt2_p3 t2_3 +(27 rows) -- full join EXPLAIN (COSTS OFF) SELECT t1.a, t1.c, t2.a, t2.c FROM plt1 t1 FULL JOIN plt2 t2 ON t1.c = t2.c WHERE coalesce(t1.b, 0) + coalesce(t2.b, 0) = 0 ORDER BY t1.a, t2.a; - QUERY PLAN ---------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------------- Sort Sort Key: t1.a, t2.a - -> Hash Full Join - Hash Cond: ((t2.c)::text = (t1.c)::text) - Filter: ((COALESCE(t1.b, 0) + COALESCE(t2.b, 0)) = 0) - -> Append + -> Append + -> Hash Full Join + Hash Cond: ((t2.c)::text = (t1.c)::text) + Filter: ((COALESCE(t1.b, 0) + COALESCE(t2.b, 0)) = 0) -> Seq Scan on plt2_p4 t2 - -> Seq Scan on plt2_p1 t2_1 - -> Seq Scan on plt2_p2 t2_2 - -> Seq Scan on plt2_p3 t2_3 - -> Hash - -> Append + -> Hash -> Seq Scan on plt1_p4 t1 + -> Hash Full Join + Hash Cond: ((t2_1.c)::text = (t1_1.c)::text) + Filter: ((COALESCE(t1_1.b, 0) + COALESCE(t2_1.b, 0)) = 0) + -> Seq Scan on plt2_p1 t2_1 + -> Hash -> Seq Scan on plt1_p1 t1_1 - -> Seq Scan on plt1_p2 t1_2 - -> Seq Scan on plt1_p3 t1_3 -(16 rows) + -> Hash Full Join + Hash Cond: ((t1_2.c)::text = (t2_2.c)::text) + Filter: ((COALESCE(t1_2.b, 0) + COALESCE(t2_2.b, 0)) = 0) + -> Seq Scan on plt1_p2 t1_2 + -> Hash + -> Seq Scan on plt2_p2 t2_2 + -> Hash Full Join + Hash Cond: ((t1_3.c)::text = (t2_3.c)::text) + Filter: ((COALESCE(t1_3.b, 0) + COALESCE(t2_3.b, 0)) = 0) + -> Seq Scan on plt1_p3 t1_3 + -> Hash + -> Seq Scan on plt2_p3 t2_3 +(27 rows) -- semi join EXPLAIN (COSTS OFF) @@ -3973,27 +4014,34 @@ select t1.a, t1.b, t1.c from plt1 t1 where exists (select 1 from plt2 t2 WHERE t -------------------------------------------------------------- Sort Sort Key: t1.a, t1.c - -> Hash Join - Hash Cond: ((t1.c)::text = (t2.c)::text) - -> Append - -> Seq Scan on plt1_p4 t1 - Filter: (b = 0) - -> Seq Scan on plt1_p1 t1_1 - Filter: (b = 0) + -> Append + -> Nested Loop + Join Filter: ((t1.c)::text = (t2.c)::text) + -> HashAggregate + Group Key: (t2.c)::text + -> Seq Scan on plt2_p4 t2 + -> Materialize + -> Seq Scan on plt1_p4 t1 + Filter: (b = 0) + -> Nested Loop + Join Filter: ((t1_1.c)::text = (t2_1.c)::text) + -> HashAggregate + Group Key: (t2_1.c)::text + -> Seq Scan on plt2_p1 t2_1 + -> Materialize + -> Seq Scan on plt1_p1 t1_1 + Filter: (b = 0) + -> Nested Loop Semi Join + Join Filter: ((t1_2.c)::text = (t2_2.c)::text) -> Seq Scan on plt1_p2 t1_2 Filter: (b = 0) + -> Seq Scan on plt2_p2 t2_2 + -> Nested Loop Semi Join + Join Filter: ((t1_3.c)::text = (t2_3.c)::text) -> Seq Scan on plt1_p3 t1_3 Filter: (b = 0) - -> Hash - -> HashAggregate - Group Key: (t2.c)::text - -> Result - -> Append - -> Seq Scan on plt2_p4 t2 - -> Seq Scan on plt2_p1 t2_1 - -> Seq Scan on plt2_p2 t2_2 - -> Seq Scan on plt2_p3 t2_3 -(22 rows) + -> Seq Scan on plt2_p3 t2_3 +(29 rows) EXPLAIN (COSTS OFF) select t1.a, t1.b, t1.c from plt2 t1 where exists (select 1 from plt1 t2 WHERE t1.c = t2.c) and t1.b = 0 order by t1.a, t1.b, t1.c; @@ -4001,78 +4049,92 @@ select t1.a, t1.b, t1.c from plt2 t1 where exists (select 1 from plt1 t2 WHERE t -------------------------------------------------------------- Sort Sort Key: t1.a, t1.c - -> Hash Join - Hash Cond: ((t1.c)::text = (t2.c)::text) - -> Append - -> Seq Scan on plt2_p4 t1 - Filter: (b = 0) + -> Append + -> Nested Loop + Join Filter: ((t1.c)::text = (t2.c)::text) + -> HashAggregate + Group Key: (t2.c)::text + -> Seq Scan on plt1_p4 t2 + -> Materialize + -> Seq Scan on plt2_p4 t1 + Filter: (b = 0) + -> Nested Loop Semi Join + Join Filter: ((t1_1.c)::text = (t2_1.c)::text) -> Seq Scan on plt2_p1 t1_1 Filter: (b = 0) + -> Seq Scan on plt1_p1 t2_1 + -> Nested Loop Semi Join + Join Filter: ((t1_2.c)::text = (t2_2.c)::text) -> Seq Scan on plt2_p2 t1_2 Filter: (b = 0) + -> Seq Scan on plt1_p2 t2_2 + -> Nested Loop Semi Join + Join Filter: ((t1_3.c)::text = (t2_3.c)::text) -> Seq Scan on plt2_p3 t1_3 Filter: (b = 0) - -> Hash - -> HashAggregate - Group Key: (t2.c)::text - -> Result - -> Append - -> Seq Scan on plt1_p4 t2 - -> Seq Scan on plt1_p1 t2_1 - -> Seq Scan on plt1_p2 t2_2 - -> Seq Scan on plt1_p3 t2_3 -(22 rows) + -> Seq Scan on plt1_p3 t2_3 +(26 rows) -- anti join EXPLAIN (COSTS OFF) select t1.a, t1.b, t1.c from plt1 t1 where not exists (select 1 from plt2 t2 WHERE t1.c = t2.c) and t1.b = 0 order by t1.a, t1.b, t1.c; - QUERY PLAN --------------------------------------------------- + QUERY PLAN +-------------------------------------------------------------- Sort Sort Key: t1.a, t1.c - -> Hash Anti Join - Hash Cond: ((t1.c)::text = (t2.c)::text) - -> Append + -> Append + -> Nested Loop Anti Join + Join Filter: ((t1.c)::text = (t2.c)::text) -> Seq Scan on plt1_p4 t1 Filter: (b = 0) + -> Seq Scan on plt2_p4 t2 + -> Hash Anti Join + Hash Cond: ((t1_1.c)::text = (t2_1.c)::text) -> Seq Scan on plt1_p1 t1_1 Filter: (b = 0) + -> Hash + -> Seq Scan on plt2_p1 t2_1 + -> Nested Loop Anti Join + Join Filter: ((t1_2.c)::text = (t2_2.c)::text) -> Seq Scan on plt1_p2 t1_2 Filter: (b = 0) + -> Seq Scan on plt2_p2 t2_2 + -> Nested Loop Anti Join + Join Filter: ((t1_3.c)::text = (t2_3.c)::text) -> Seq Scan on plt1_p3 t1_3 Filter: (b = 0) - -> Hash - -> Append - -> Seq Scan on plt2_p4 t2 - -> Seq Scan on plt2_p1 t2_1 - -> Seq Scan on plt2_p2 t2_2 - -> Seq Scan on plt2_p3 t2_3 -(19 rows) + -> Seq Scan on plt2_p3 t2_3 +(24 rows) EXPLAIN (COSTS OFF) select t1.a, t1.b, t1.c from plt2 t1 where not exists (select 1 from plt1 t2 WHERE t1.c = t2.c) and t1.b = 0 order by t1.a, t1.b, t1.c; - QUERY PLAN --------------------------------------------------- + QUERY PLAN +-------------------------------------------------------------- Sort Sort Key: t1.a, t1.c - -> Hash Anti Join - Hash Cond: ((t1.c)::text = (t2.c)::text) - -> Append + -> Append + -> Hash Anti Join + Hash Cond: ((t1.c)::text = (t2.c)::text) -> Seq Scan on plt2_p4 t1 Filter: (b = 0) + -> Hash + -> Seq Scan on plt1_p4 t2 + -> Nested Loop Anti Join + Join Filter: ((t1_1.c)::text = (t2_1.c)::text) -> Seq Scan on plt2_p1 t1_1 Filter: (b = 0) + -> Seq Scan on plt1_p1 t2_1 + -> Nested Loop Anti Join + Join Filter: ((t1_2.c)::text = (t2_2.c)::text) -> Seq Scan on plt2_p2 t1_2 Filter: (b = 0) + -> Seq Scan on plt1_p2 t2_2 + -> Nested Loop Anti Join + Join Filter: ((t1_3.c)::text = (t2_3.c)::text) -> Seq Scan on plt2_p3 t1_3 Filter: (b = 0) - -> Hash - -> Append - -> Seq Scan on plt1_p4 t2 - -> Seq Scan on plt1_p1 t2_1 - -> Seq Scan on plt1_p2 t2_2 - -> Seq Scan on plt1_p3 t2_3 -(19 rows) + -> Seq Scan on plt1_p3 t2_3 +(24 rows) -- joins where one of the relations is proven empty EXPLAIN (COSTS OFF) diff --git a/src/test/regress/sql/partition_join.sql b/src/test/regress/sql/partition_join.sql index d8434f6b1a..a552874742 100644 --- a/src/test/regress/sql/partition_join.sql +++ b/src/test/regress/sql/partition_join.sql @@ -561,10 +561,7 @@ EXPLAIN (COSTS OFF) select t1.a, t1.b, t1.c from plt2 t1 where not exists (select 1 from plt1 t2 WHERE t1.c = t2.c) and t1.b = 0 order by t1.a, t1.b, t1.c; select t1.a, t1.b, t1.c from plt2 t1 where not exists (select 1 from plt1 t2 WHERE t1.c = t2.c) and t1.b = 0 order by t1.a, t1.b, t1.c; --- partition have a NULL on both side with different partition bounds w.r.t other side --- NULL when NULL comparision is not strict i.e. NULL=NULL allowed --- Partition-wise join can not handle the case when one partition from one side --- matches with multiple partitions on the other side +-- test the null-partition handling in the new partition-matching algorithm DROP TABLE plt1_p3; CREATE TABLE plt1_p3 PARTITION OF plt1 FOR VALUES IN (NULL,'0008','0009'); INSERT INTO plt1 SELECT i, i % :cond_mod, case when i % :part_mod = 7 then NULL else to_char(i % :part_mod, 'FM0000') end FROM generate_series(0, :num_rows) i WHERE i % :part_mod IN (7,8,9); -- 2.19.2