[
https://issues.apache.org/jira/browse/HIVE-30036?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18114123#comment-18114123
]
Thomas Rebele commented on HIVE-30036:
--------------------------------------
Here a reduced example:
{code:java}
> create table tab1 (a int, b int);
{code}
For a one-sided range predicate within an OR filtering on >10, >20, >30, a
common prefilter >10 is pushed down:
{code}
> explain cbo select * from tab1 t, tab1 s where s.a=t.b and ((t.a > 10 and
> s.b=1) or (t.a > 20 and s.b=2) or (t.a > 30 and s.b=3));
| HiveProject(t.a=[$0], t.b=[$1], s.a=[$5], s.b=[$6]) |
| HiveJoin(condition=[AND(=($5, $1), OR(AND($7, $2), AND($3, $8), AND($4,
$9)))], joinType=[inner], algorithm=[none], cost=[not available]) |
| HiveProject(a=[$0], b=[$1], EXPR$0=[IS NOT NULL($0)], EXPR$1=[>($0, 20)],
EXPR$2=[>($0, 30)]) |
| HiveFilter(condition=[AND(>($0, 10), IS NOT NULL($1))]) |
| HiveTableScan(table=[[db, tab1]], table:alias=[t]) |
| HiveProject(a=[$0], b=[$1], EXPR$0=[=($1, 1)], EXPR$1=[=($1, 2)],
EXPR$2=[=($1, 3)]) |
| HiveFilter(condition=[AND(IN($1, 1, 2, 3), IS NOT NULL($0))]) |
| HiveTableScan(table=[[db, tab1]], table:alias=[s]) |
{code}
For a two-sided range predicate within an OR with the same upper bound, a
common prefilter is pushed down:
{code}
> explain cbo select * from tab1 t, tab1 s where s.a=t.b and ((t.a between 10
> and 100 and s.b=1) or (t.a between 20 and 100 and s.b=2) or (t.a between 30
> and 100 and s.b=3));
| HiveProject(t.a=[$0], t.b=[$1], s.a=[$5], s.b=[$6]) |
| HiveJoin(condition=[AND(=($5, $1), OR(AND($2, $7), AND($3, $8), AND($4,
$9)))], joinType=[inner], algorithm=[none], cost=[not available]) |
| HiveProject(a=[$0], b=[$1], EXPR$0=[<=(10, $0)], EXPR$1=[<=(20, $0)],
EXPR$2=[<=(30, $0)]) |
| HiveFilter(condition=[AND(BETWEEN(false, $0, 10, 100), IS NOT
NULL($1))]) |
| HiveTableScan(table=[[db, tab1]], table:alias=[t]) |
| HiveProject(a=[$0], b=[$1], EXPR$0=[=($1, 1)], EXPR$1=[=($1, 2)],
EXPR$2=[=($1, 3)]) |
| HiveFilter(condition=[AND(IN($1, 1, 2, 3), IS NOT NULL($0))]) |
| HiveTableScan(table=[[db, tab1]], table:alias=[s]) |
{code}
As soon as one of the range predicates has a slightly different upper bound,
there is no pushdown:
{code}
> explain cbo select * from tab1 t, tab1 s where s.a=t.b and ((t.a between 10
> and 100 and s.b=1) or (t.a between 20 and 100 and s.b=2) or (t.a between 30
> and 101 and s.b=3));
| HiveProject(t.a=[$0], t.b=[$1], s.a=[$5], s.b=[$6]) |
| HiveJoin(condition=[AND(=($5, $1), OR(AND($2, $7), AND($3, $8), AND($4,
$9)))], joinType=[inner], algorithm=[none], cost=[not available]) |
| HiveProject(a=[$0], b=[$1], EXPR$0=[BETWEEN(false, $0, 10, 100)],
EXPR$1=[BETWEEN(false, $0, 20, 100)], EXPR$2=[BETWEEN(false, $0, 30, 101)]) |
| HiveFilter(condition=[AND(IS NOT NULL($0), IS NOT NULL($1))]) |
| HiveTableScan(table=[[db, tab1]], table:alias=[t]) |
| HiveProject(a=[$0], b=[$1], EXPR$0=[=($1, 1)], EXPR$1=[=($1, 2)],
EXPR$2=[=($1, 3)]) |
| HiveFilter(condition=[AND(IN($1, 1, 2, 3), IS NOT NULL($0))]) |
| HiveTableScan(table=[[db, tab1]], table:alias=[s]) |
{code}
Same behavior when using field >= lower AND field <= upper. With a common upper
bound, a prefilter is pushed down:
{code}
> explain cbo select * from tab1 t, tab1 s where s.a=t.b and ((t.a >= 10 and
> t.a <= 100 and s.b=1) or (t.a >= 20 and t.a <= 100 and s.b=2) or (t.a >= 30
> and t.a <= 100 and s.b=3));
| HiveProject(t.a=[$0], t.b=[$1], s.a=[$5], s.b=[$6]) |
| HiveJoin(condition=[AND(=($5, $1), OR(AND($2, $7), AND($3, $8), AND($4,
$9)))], joinType=[inner], algorithm=[none], cost=[not available]) |
| HiveProject(a=[$0], b=[$1], EXPR$0=[>=($0, 10)], EXPR$1=[>=($0, 20)],
EXPR$2=[>=($0, 30)]) |
| HiveFilter(condition=[AND(BETWEEN(false, $0, 10, 100), IS NOT
NULL($1))]) |
| HiveTableScan(table=[[db, tab1]], table:alias=[t]) |
| HiveProject(a=[$0], b=[$1], EXPR$0=[=($1, 1)], EXPR$1=[=($1, 2)],
EXPR$2=[=($1, 3)]) |
| HiveFilter(condition=[AND(IN($1, 1, 2, 3), IS NOT NULL($0))]) |
| HiveTableScan(table=[[db, tab1]], table:alias=[s]) |
{code}
No prefilter pushdown if one of the upper bounds is slightly different:
{code}
> explain cbo select * from tab1 t, tab1 s where s.a=t.b and ((t.a >= 10 and
> t.a <= 100 and s.b=1) or (t.a >= 20 and t.a <= 100 and s.b=2) or (t.a >= 30
> and t.a <= 101 and s.b=3));
| HiveProject(t.a=[$0], t.b=[$1], s.a=[$5], s.b=[$6]) |
| HiveJoin(condition=[AND(=($5, $1), OR(AND($2, $7), AND($3, $8), AND($4,
$9)))], joinType=[inner], algorithm=[none], cost=[not available]) |
| HiveProject(a=[$0], b=[$1], EXPR$0=[BETWEEN(false, $0, 10, 100)],
EXPR$1=[BETWEEN(false, $0, 20, 100)], EXPR$2=[BETWEEN(false, $0, 30, 101)]) |
| HiveFilter(condition=[AND(IS NOT NULL($0), IS NOT NULL($1))]) |
| HiveTableScan(table=[[db, tab1]], table:alias=[t]) |
| HiveProject(a=[$0], b=[$1], EXPR$0=[=($1, 1)], EXPR$1=[=($1, 2)],
EXPR$2=[=($1, 3)]) |
| HiveFilter(condition=[AND(IN($1, 1, 2, 3), IS NOT NULL($0))]) |
| HiveTableScan(table=[[db, tab1]], table:alias=[s]) |
{code}
> Prefilter pushdown for range predicates
> ---------------------------------------
>
> Key: HIVE-30036
> URL: https://issues.apache.org/jira/browse/HIVE-30036
> Project: Hive
> Issue Type: Improvement
> Reporter: Thomas Rebele
> Priority: Major
>
> I noticed that in query13.q for most tables a prefilter is extracted from the
> complex {{AND(OR(AND(...), ...), OR(AND(...), ...)}} filter of the
> selectivity. However, for table store_sales [only some IS NULL
> filters|https://github.com/apache/hive/blob/2be30b71b6a112edde13d2ea1a11144b5abf1c04/ql/src/test/results/clientpositive/perf/tpcds30tb/tez/cbo_query13.q.out#L9]
> are extracted, even though it would be possible to combine the predicates on
> ss_sales_price and ss_net_profit to ss_sales_price BETWEEN 50.00 AND 200.00
> and ss_net_profit BETWEEN 50 AND 300. Especially the latter can reduce the
> amount of processed rows.
> I replaced the {{field BETWEEN a_1 AND b_1}} with {{{}field >= a_1 AND field
> <= b_1{}}}, which is not pushed down either. When I remove the upper bound,
> i.e., {{{}field >= a_1{}}}, then the common part {{field >= min(a_1, a_2,
> ...)}} is pushed down as a prefilter.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
