Is there any plan for a MultiRange with AND sematics?

[rui-mo]

Hi,

We noticed there were two levels of filters in HiveTableHandle (link), the subfieldFilters and remainingFilter. For subfieldFilters, there were several kinds of ranges in Filter.h. My question is about MultiRange.

MultiRange represents a combination of range filters with OR semantics, therefore in some cases, not all conditions can be pushed down and certain logics were added to distinguish between them in our usage.

For example, in below conditions, if IN can be represented with BigintValues, and GreaterThan can be represented with BigintRange, then a multiRange of AND sematics is needed to push down both of them.

a > x and a in (y, z)

Currently, we have special logics to separate them and push down part of them, eg., only pushing down IN and leaving GreaterThan to the remaining filter. We are wondering if there is any plan for a MultiRange with AND sematics. Or do you have any suggestion for us? Thanks!

[mbasmanova]

@rui-mo Rui, thank you for the question.

a > x and a in (y, z)

Here, a recommendation would be to further analyze the filter and check whether y and z are > x. If they are, then the filter can be simplified to just a in (y, z). If y < x, but z > x, then filter will become a = z.

[rui-mo]

@mbasmanova Masha, thanks for your valuable reply. Does this mean we can try to find the narrowest filter condititon for one field and use a single range to push it down? If so, looks like an optimizer needs to be implemented to narrow down mutiple conditions and returns the strictest one. Do you think this optimization should be conducted during Substrait plan generation (that means in Gluten), or in Velox Substrait-to-Velox conversion?

[mbasmanova]

@rui-mo Hi Rui,

It is an optimizer's job to analyze the filter above table scan, break it down into conjuncts, then analyze individual conjuncts to identify a subset of these which can be represented as range filters over individual columns. This logic doesn't belong to plan conversion, rather to the planner / optimizer itself. In other words, I expect this to do be done before the plan is converted into Substrait or Velox representation.

You can find Presto's implementation of this login in com.facebook.presto.sql.relational.RowExpressionDomainTranslator#fromPredicate

https://github.com/prestodb/presto/blob/master/presto-main/src/main/java/com/facebook/presto/sql/relational/RowExpressionDomainTranslator.java#L124

There is additional information about range filters in Performance Guide into TableScan and Aggregation guide for Prestissimo: https://expert-adventure-b62ae804.pages.github.io/develop/perf-scan-agg.html

[rui-mo]

Thank you! I will take a look.