Обсуждение: [HACKERS] multi-level partitions and partition-wise joins
Hi, I am starting this as a separate thread for this since the declarative partitioning thread has many issues reported and it's better to keep this discussion separate from the issues reported on that thread. While expanding inheritance, any inheritance hierarchy is flattened out including partition hierarchy. Partition-wise joins can be employed if the joining tables have the same partitioning scheme and have equi-join clauses on the partition keys. If two multi-level partitioned tables are joined, the partition-wise join can be percolated down to the levels up to which the partition schemes match and suitable clauses are available. E.g. if two multi-level partitioned table have matching partitioning schemes at the top-most level, but not below that, we may join the topmost level partitions pair-wise, but not partitions on the lower levels. In general, we may use partition-wise join for the matching parts of partition hierarchy and in the parts that do not match, use join between append relations. Not always it will be efficient to execute partition-wise joins upto the last levels of partition hierarchy, even if partition-wise join can be employed. It might be possible that executing partition-wise joins for only certain parts of partition hierarchy is efficient and join of appends is efficient in the rest of the parts. In order to decide whether partition-wise join is efficient for a join between given partitioned partition, we need to identify its subpartitions. Similarly when a join between partitioned partition can not use partition-wise join but some other partitions can, we need to identify the subpartitions of that partition, so that they can be appended together before joining. That information is lost while expanding RTE. It looks like we need to retain partitioning hierarchy in order to implement partition-wise joins between multi-level partitioned tables. An earlier version of Amit's partition support patches had code to retain partitioning hierarchy but it seems it was removed per discussion at [1]. I agree with that decision. [1]. https://www.postgresql.org/message-id/CA%2BTgmobMy%3DrqM%3DMTN_FUEfD-PiWSCSonH%2BZ1_SjL6ZmQ2GGz1w%40mail.gmail.com -- Best Wishes, Ashutosh Bapat EnterpriseDB Corporation The Postgres Database Company
On Wed, Dec 21, 2016 at 6:36 AM, Ashutosh Bapat <ashutosh.bapat@enterprisedb.com> wrote: > I am starting this as a separate thread for this since the declarative > partitioning thread has many issues reported and it's better to keep > this discussion separate from the issues reported on that thread. > > While expanding inheritance, any inheritance hierarchy is flattened > out including partition hierarchy. Partition-wise joins can be > employed if the joining tables have the same partitioning scheme and > have equi-join clauses on the partition keys. If two multi-level > partitioned tables are joined, the partition-wise join can be > percolated down to the levels up to which the partition schemes match > and suitable clauses are available. E.g. if two multi-level > partitioned table have matching partitioning schemes at the top-most > level, but not below that, we may join the topmost level partitions > pair-wise, but not partitions on the lower levels. In general, we may > use partition-wise join for the matching parts of partition hierarchy > and in the parts that do not match, use join between append relations. > Not always it will be efficient to execute partition-wise joins upto > the last levels of partition hierarchy, even if partition-wise join > can be employed. It might be possible that executing partition-wise > joins for only certain parts of partition hierarchy is efficient and > join of appends is efficient in the rest of the parts. > > In order to decide whether partition-wise join is efficient for a join > between given partitioned partition, we need to identify its > subpartitions. Similarly when a join between partitioned partition can > not use partition-wise join but some other partitions can, we need to > identify the subpartitions of that partition, so that they can be > appended together before joining. That information is lost while > expanding RTE. It looks like we need to retain partitioning hierarchy > in order to implement partition-wise joins between multi-level > partitioned tables. > > An earlier version of Amit's partition support patches had code to > retain partitioning hierarchy but it seems it was removed per > discussion at [1]. I agree with that decision. I can't quite figure out what the point of this email is. What did you want to discuss? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On Wed, Dec 21, 2016 at 10:25 PM, Robert Haas <robertmhaas@gmail.com> wrote: > On Wed, Dec 21, 2016 at 6:36 AM, Ashutosh Bapat > <ashutosh.bapat@enterprisedb.com> wrote: >> I am starting this as a separate thread for this since the declarative >> partitioning thread has many issues reported and it's better to keep >> this discussion separate from the issues reported on that thread. >> >> While expanding inheritance, any inheritance hierarchy is flattened >> out including partition hierarchy. Partition-wise joins can be >> employed if the joining tables have the same partitioning scheme and >> have equi-join clauses on the partition keys. If two multi-level >> partitioned tables are joined, the partition-wise join can be >> percolated down to the levels up to which the partition schemes match >> and suitable clauses are available. E.g. if two multi-level >> partitioned table have matching partitioning schemes at the top-most >> level, but not below that, we may join the topmost level partitions >> pair-wise, but not partitions on the lower levels. In general, we may >> use partition-wise join for the matching parts of partition hierarchy >> and in the parts that do not match, use join between append relations. >> Not always it will be efficient to execute partition-wise joins upto >> the last levels of partition hierarchy, even if partition-wise join >> can be employed. It might be possible that executing partition-wise >> joins for only certain parts of partition hierarchy is efficient and >> join of appends is efficient in the rest of the parts. >> >> In order to decide whether partition-wise join is efficient for a join >> between given partitioned partition, we need to identify its >> subpartitions. Similarly when a join between partitioned partition can >> not use partition-wise join but some other partitions can, we need to >> identify the subpartitions of that partition, so that they can be >> appended together before joining. That information is lost while >> expanding RTE. It looks like we need to retain partitioning hierarchy >> in order to implement partition-wise joins between multi-level >> partitioned tables. >> >> An earlier version of Amit's partition support patches had code to >> retain partitioning hierarchy but it seems it was removed per >> discussion at [1]. I agree with that decision. > > I can't quite figure out what the point of this email is. What did > you want to discuss? > Sorry for sending mail before adding points to discuss. Given the scenario described above, it looks like we have to retain partition hierarchy in the form of inheritance hierarchy in order to implement partition-wise joins for multi-leveled partition tables. Is that the right thing to do? PFA a patch retained by Amit Langote to translate partition hierarchy into inheritance hierarchy. Is this something on the right direction? Any other options I can think of like maintaining a tree of partitioning schemes, either means that we can not plan partition-wise joins for part of partition hierarchy e.g. matching whole partitioning scheme tree OR it means that we have to add append plans to partition relations corresponding to partitioned partitions, which is not correct since leaf child relations can not have append paths. Any suggestions? -- Best Wishes, Ashutosh Bapat EnterpriseDB Corporation The Postgres Database Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Вложения
On Wed, Dec 21, 2016 at 11:31 PM, Ashutosh Bapat <ashutosh.bapat@enterprisedb.com> wrote: > Given the scenario described above, it looks like we have to retain > partition hierarchy in the form of inheritance hierarchy in order to > implement partition-wise joins for multi-leveled partition tables. Is > that the right thing to do? PFA a patch retained by Amit Langote to > translate partition hierarchy into inheritance hierarchy. Is this > something on the right direction? I am not sure whether Amit's patch is the right way to go. I don't fully understand it, and I remember complaining about some aspects of it before, such as this unexplained and fairly random-looking exception: + /* + * Do not flatten the inheritance hierarchy if partitioned table, unless + * this is the result relation. + */ However, I think the overall idea of doing flattening later in the process for partitioned tables is probably correct. It's not that we shouldn't do flattening at all -- the final Plan shouldn't involve nested Append nodes -- but maybe we should delay it. Perhaps the Path tree retains the structure and the final Plan flattens it. We might consider doing that way for both inheritance trees and partitioning, just so we don't have two different code paths to validate. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On Thu, Dec 22, 2016 at 10:52 PM, Robert Haas <robertmhaas@gmail.com> wrote: > On Wed, Dec 21, 2016 at 11:31 PM, Ashutosh Bapat > <ashutosh.bapat@enterprisedb.com> wrote: >> Given the scenario described above, it looks like we have to retain >> partition hierarchy in the form of inheritance hierarchy in order to >> implement partition-wise joins for multi-leveled partition tables. Is >> that the right thing to do? PFA a patch retained by Amit Langote to >> translate partition hierarchy into inheritance hierarchy. Is this >> something on the right direction? > > I am not sure whether Amit's patch is the right way to go. I don't > fully understand it, and I remember complaining about some aspects of > it before, such as this unexplained and fairly random-looking > exception: > > + /* > + * Do not flatten the inheritance hierarchy if partitioned table, unless > + * this is the result relation. > + */ > > However, I think the overall idea of doing flattening later in the > process for partitioned tables is probably correct. It's not that we > shouldn't do flattening at all -- the final Plan shouldn't involve > nested Append nodes -- but maybe we should delay it. Perhaps the Path > tree retains the structure and the final Plan flattens it. While creating append paths we flatten any append paths added to the children. > We might > consider doing that way for both inheritance trees and partitioning, > just so we don't have two different code paths to validate. > AFAIU the reason why we chose to flatten the inheritance hierarchy is multiple inheritance. Since the same child can inherit from two parents, in an unflattened version its paths would be included twice. It would be clumsy to keep the inheritance unflattened but not include a relation more than once in the final plan tree. However, for partitioned tables, we are guaranteed that there's only a single parent and thus every child relation will be considered only once. We will need separate code to handle (possible) multiple inheritance and strictly single inheritance imposed by partitioning. -- Best Wishes, Ashutosh Bapat EnterpriseDB Corporation The Postgres Database Company
Another question: do we want to commit the code for creating unflattened hierarchy separately or along with partition-wise join? On Fri, Dec 23, 2016 at 9:58 AM, Ashutosh Bapat <ashutosh.bapat@enterprisedb.com> wrote: > On Thu, Dec 22, 2016 at 10:52 PM, Robert Haas <robertmhaas@gmail.com> wrote: >> On Wed, Dec 21, 2016 at 11:31 PM, Ashutosh Bapat >> <ashutosh.bapat@enterprisedb.com> wrote: >>> Given the scenario described above, it looks like we have to retain >>> partition hierarchy in the form of inheritance hierarchy in order to >>> implement partition-wise joins for multi-leveled partition tables. Is >>> that the right thing to do? PFA a patch retained by Amit Langote to >>> translate partition hierarchy into inheritance hierarchy. Is this >>> something on the right direction? >> >> I am not sure whether Amit's patch is the right way to go. I don't >> fully understand it, and I remember complaining about some aspects of >> it before, such as this unexplained and fairly random-looking >> exception: >> >> + /* >> + * Do not flatten the inheritance hierarchy if partitioned table, unless >> + * this is the result relation. >> + */ >> >> However, I think the overall idea of doing flattening later in the >> process for partitioned tables is probably correct. It's not that we >> shouldn't do flattening at all -- the final Plan shouldn't involve >> nested Append nodes -- but maybe we should delay it. Perhaps the Path >> tree retains the structure and the final Plan flattens it. > > While creating append paths we flatten any append paths added to the children. > >> We might >> consider doing that way for both inheritance trees and partitioning, >> just so we don't have two different code paths to validate. >> > > AFAIU the reason why we chose to flatten the inheritance hierarchy is > multiple inheritance. Since the same child can inherit from two > parents, in an unflattened version its paths would be included twice. > It would be clumsy to keep the inheritance unflattened but not include > a relation more than once in the final plan tree. > > However, for partitioned tables, we are guaranteed that there's only a > single parent and thus every child relation will be considered only > once. We will need separate code to handle (possible) multiple > inheritance and strictly single inheritance imposed by partitioning. > > -- > Best Wishes, > Ashutosh Bapat > EnterpriseDB Corporation > The Postgres Database Company -- Best Wishes, Ashutosh Bapat EnterpriseDB Corporation The Postgres Database Company
On Fri, Dec 23, 2016 at 12:54 AM, Ashutosh Bapat <ashutosh.bapat@enterprisedb.com> wrote: > Another question: do we want to commit the code for creating > unflattened hierarchy separately or along with partition-wise join? Probably separately. I suggest posting a series of two (or perhaps more) patches on the same thread. 'git format-patch' is a useful way to produce a patch series for posting. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company