Thanks Julian and Justin. What do you think the rule should be called? UnionFilterTransposeRule, > perhaps? > SetOpFilterMergeRule?
Maybe that problem does not occur when applied to UNION than when applied > to UNION ALL. Yes, This is very important. There would seem to be analogous rules for INTERSECT (combine the > conditions using AND) and EXCEPT (combine the conditions using AND NOT). > Excellent extensions, all the three operators process logic are: For UNION: New Filter = left Filter *OR* right Filter. For INTERSECT: New Filter = left Filter *AND* right Filter. For EXCEPT: If left Filter, right Filter have no overlap, transform to left child tree, Otherwise New Filter = left Filter *AND NOT *right Filter. For UNION ALL: Add prerequisites: left Filter, right Filter must have no overlap. For INTERSECT ALL: Add prerequisites: If left Filter, right Filter have no overlap, transform to empty values. Otherwise the rule can't be applied. For EXCEPT ALL: same for EXCEPT. work for N-way Union, not just 2-way Union. > Yes, I will add tests for this. And I think you should make it work whether or not a Project is present. Ok, It seems I need construct several different operand match trees for plan. Hi Julian, what do you think of the above process logic? Julian Hyde <jhyde.apa...@gmail.com> 于2022年1月20日周四 10:18写道： > Justin, > > For planning table or index scans, I would recommend using a single > TableScan with a Filter that uses a Sarg, rather than using multiple > TableScans connected by a Union. So I think this rule will be useful. > > But I do agree that this proposed rule is not a “no brainer”. It may not > do what people want/expect in all cases, and therefore it probably should > not be enabled it by default. > > Julian > > > > > > > On Jan 19, 2022, at 3:38 PM, Justin Swanhart <greenl...@gmail.com> > wrote: > > > > Hi, > > > > Note that this will negate the optimization that one usually is looking > for > > when writing such queries: > > > > Select * from TAB where a = 1 > > UNION ALL > > Select * from TAB where b = 1 > > > > In a database with indexes (most databases) this will allow indexes to be > > used on both the a column and the b column. > > Databases with bitmap indexes or without indexes would benefit from the > > rule. > > > > On Wed, Jan 19, 2022 at 4:32 PM Julian Hyde <jhyde.apa...@gmail.com > <mailto:jhyde.apa...@gmail.com>> wrote: > > > >> Can you log a Jira case for this? > >> > >> I think you should make your rule work for N-way Union, not just 2-way > >> Union. And I think you should make it work whether or not a Project is > >> present. > >> > >>> On Jan 19, 2022, at 1:25 PM, Julian Hyde <jhyde.apa...@gmail.com> > wrote: > >>> > >>> 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 <zhuangzixiao...@gmail.com > >> <mailto:zhuangzixiao...@gmail.com <mailto:zhuangzixiao...@gmail.com>>> > 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 <zhuangzixiao...@gmail.com <mailto: > zhuangzixiao...@gmail.com> <mailto: > >> zhuangzixiao...@gmail.com <mailto:zhuangzixiao...@gmail.com>>> > 于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. > >
