[
https://issues.apache.org/jira/browse/HIVE-30036?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18113912#comment-18113912
]
Thomas Rebele edited comment on HIVE-30036 at 9/10/26 5:18 PM:
---------------------------------------------------------------
-In the similar query 48, a prefilter has been [pushed
down|https://github.com/apache/hive/blob/2be30b71b6a112edde13d2ea1a11144b5abf1c04/ql/src/test/results/clientpositive/perf/tpcds30tb/tez/cbo_query48.q.out#L14]
for ss_sales_price, but not for ss_net_profit.-
Update: the predicate for ss_sales_price is (P AND ss_sales_price BETWEEN 50
and 100) OR (P AND ss_sales_price BETWEEN 100 AND 150) OR ..., which is
simplified to P AND (ss_sales_price BETWEEN 50 AND 100 OR ss_sales_price
BETWEEN 100 and 150 OR ...). So this is an example for a filter pushdown, not
prefilter pushdown.
was (Author: thomas.rebele):
In the similar query 48, a prefilter has been [pushed
down|https://github.com/apache/hive/blob/2be30b71b6a112edde13d2ea1a11144b5abf1c04/ql/src/test/results/clientpositive/perf/tpcds30tb/tez/cbo_query48.q.out#L14]
for ss_sales_price, but not for ss_net_profit.
> 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)
