> IIUC, this seems like a combination of 2 and 3 above: > > So, we have the following list partitions (as read from the catalog) > > Table 1: p1 {'b', 'f'}, p2 {'c', 'd'}, p3 {'a', 'e'} > Table 2: p1 {'c', 'd'}, p2 {'a', 'e'}, p3 {'b', 'f'} > > By applying the method of 3: > > Table 1: p3 {'a', 'e'}, p2 {'b', 'f'}, p1 {'c', 'd'} > Table 2: p2 {'a', 'e'}, p1 {'b', 'f'}, p3 {'c', 'd'} > > Then applying 2: > > Table 1: ['a', 'b', 'c', 'd', 'e', 'f'], [1, 2, 3, 3, 1, 2], [3, 1, 2] > Table 2: ['a', 'b', 'c', 'd', 'e', 'f'], [1, 2, 3, 3, 1, 2], [2, 3, 1] >
We should arrange the OID arrays to follow canonical order. Assuming that OIDs of p1, pt2, and p3 of table 1 are t1o1, t1o2 and t1o3 respectively, and those of p1, p2 and p3 of table 2 are t2o1, t2o2, t2o3 resp. the last arrays should be [t1o3, t1o2, t1o1] and [t2o2, t2o1, t2o3].Thus the last arrays from both representation give the OIDs of children that should be joined pair-wise. IOW, OID array should just follow the canonical order instead of specification order. AFAIU, your patch arranges the range partition OIDs in the canonical order and not specification order. > > This is user-specification independent representation wherein the > partition numbers in the 2nd array are based on canonical representation > (ordered lists). To check pairwise join compatibility, simply compare the > first two arrays. As to which partitions (think OIDs, RTEs whatever) pair > with each other, simply pair corresponding elements of the 3rd array which > are original partitions numbers (or OIDs). Also when routing a tuple, we > find partition number in the array 2 and then look up the array 3 to get > the actual partition to insert the tuple. > > Neither of these representations make the logic of checking pairwise-join > compatibility and pairing a subset of partitions (on either side) any > easier, although I haven't given it a good thought yet. > > With the small change suggested above, it should be easy to check partition-wise join compatibilty for simplest case. I agree that for generic case it will be difficult. E.g. Table 1#: p3 {'a', 'e'}, p4{'a#', 'l'}, p2 {'b', 'f'}, p1 {'c', 'd'} being (INNER) joined with Table 2: p2 {'a', 'e'}, p1 {'b', 'f'}, p3 {'c', 'd'} assuming 'a'< 'a#' < 'b'; in this case, we should be able to match p3-p2, p2-p1, p1-p3 for partition-wise join, even though canonical representations of both partitions differ, because of an extra partition in between. -- Best Wishes, Ashutosh Bapat EnterpriseDB Corporation The Postgres Database Company