It sounds useful. What do you think the rule should be called? UnionFilterTransposeRule, perhaps?
A challenge when writing the rule will be to ensure that all of the inputs to the Union are the same. The Volcano framework is not very good at that. You should be careful of the case that the conditions overlap. For example, the rewrite SELECT * FROM Emp WHERE deptno < 30 UNION ALL SELECT * FROM Emp WHERE deptno IN (25, 35, 45) to SELECT * FROM Emp WHERE deptno < 30 OR deptno IN (25, 35, 45) Is not valid, because rows with deptno = 25 will appear twice in the first query, once in the second. Maybe that problem does not occur when applied to UNION than when applied to UNION ALL. There would seem to be analogous rules for INTERSECT (combine the conditions using AND) and EXCEPT (combine the conditions using AND NOT). Perhaps one rule could cover all set operations (see FilterSetOpTransposeRule). Julian > On Jan 19, 2022, at 2:38 AM, Yanjing Wang <[email protected]> wrote: > > A simple example is converting SELECT a, b FROM t WHERE c = 1 UNION ALL > SELECT a, b FROM t WHERE c = 2 to SELECT a, b FROM t WHERE c in (1, 2) > > Yanjing Wang <[email protected] <mailto:[email protected]>> > 于2022年1月19日周三 18:35写道： > Hi, community > > Here I recommend a new rule for converting UNION ALL sub plan to a single > input with an OR filter, the following is its conversion diagram. > > > The conversion prerequisites are > 1. left filter range has no intersection with right filter range. > 2. Project and Input Sub Tree must be identical. > > The rule will be used when Input Sub Tree is a computing-intensive or large > IO operation. > > I don't know whether the community supports it or not, any suggestions will > be appreciated. > >
