You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@spark.apache.org by "Reynold Xin (JIRA)" <ji...@apache.org> on 2015/01/18 00:52:34 UTC
[jira] [Updated] (SPARK-4937) Adding optimization to simplify the
filter condition
[ https://issues.apache.org/jira/browse/SPARK-4937?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Reynold Xin updated SPARK-4937:
-------------------------------
Description:
Adding optimization to simplify the filter condition:
1 condition that can get the boolean result such as:
{code}
a < 3 && a > 5 ---- False
a < 1 || a > 0 ---- True
{code}
2 Simplify And, Or condition, such as the sql (one of hive-testbench):
{code}
select
sum(l_extendedprice* (1 - l_discount)) as revenue
from
lineitem,
part
where
(
p_partkey = l_partkey
and p_brand = 'Brand#32'
and p_container in ('SM CASE', 'SM BOX', 'SM PACK', 'SM PKG')
and l_quantity >= 7 and l_quantity <= 7 + 10
and p_size between 1 and 5
and l_shipmode in ('AIR', 'AIR REG')
and l_shipinstruct = 'DELIVER IN PERSON'
)
or
(
p_partkey = l_partkey
and p_brand = 'Brand#35'
and p_container in ('MED BAG', 'MED BOX', 'MED PKG', 'MED PACK')
and l_quantity >= 15 and l_quantity <= 15 + 10
and p_size between 1 and 10
and l_shipmode in ('AIR', 'AIR REG')
and l_shipinstruct = 'DELIVER IN PERSON'
)
or
(
p_partkey = l_partkey
and p_brand = 'Brand#24'
and p_container in ('LG CASE', 'LG BOX', 'LG PACK', 'LG PKG')
and l_quantity >= 26 and l_quantity <= 26 + 10
and p_size between 1 and 15
and l_shipmode in ('AIR', 'AIR REG')
and l_shipinstruct = 'DELIVER IN PERSON'
);
{code}
Before optimized it is a CartesianProduct, in my locally test this sql hang and can not get result, after optimization the CartesianProduct replaced by ShuffledHashJoin, which only need 20+ seconds to run this sql.
was:
Adding optimization to simplify the filter condition:
1 condition that can get the boolean result such as:
a < 3 && a > 5 ---- False
a < 1 || a > 0 ---- True
2 Simplify And, Or condition, such as the sql (one of hive-testbench
):
select
sum(l_extendedprice* (1 - l_discount)) as revenue
from
lineitem,
part
where
(
p_partkey = l_partkey
and p_brand = 'Brand#32'
and p_container in ('SM CASE', 'SM BOX', 'SM PACK', 'SM PKG')
and l_quantity >= 7 and l_quantity <= 7 + 10
and p_size between 1 and 5
and l_shipmode in ('AIR', 'AIR REG')
and l_shipinstruct = 'DELIVER IN PERSON'
)
or
(
p_partkey = l_partkey
and p_brand = 'Brand#35'
and p_container in ('MED BAG', 'MED BOX', 'MED PKG', 'MED PACK')
and l_quantity >= 15 and l_quantity <= 15 + 10
and p_size between 1 and 10
and l_shipmode in ('AIR', 'AIR REG')
and l_shipinstruct = 'DELIVER IN PERSON'
)
or
(
p_partkey = l_partkey
and p_brand = 'Brand#24'
and p_container in ('LG CASE', 'LG BOX', 'LG PACK', 'LG PKG')
and l_quantity >= 26 and l_quantity <= 26 + 10
and p_size between 1 and 15
and l_shipmode in ('AIR', 'AIR REG')
and l_shipinstruct = 'DELIVER IN PERSON'
);
Before optimized it is a CartesianProduct, in my locally test this sql hang and can not get result, after optimization the CartesianProduct replaced by ShuffledHashJoin, which only need 20+ seconds to run this sql.
> Adding optimization to simplify the filter condition
> ----------------------------------------------------
>
> Key: SPARK-4937
> URL: https://issues.apache.org/jira/browse/SPARK-4937
> Project: Spark
> Issue Type: Improvement
> Components: SQL
> Affects Versions: 1.1.0
> Reporter: wangfei
> Assignee: Cheng Lian
> Fix For: 1.3.0
>
>
> Adding optimization to simplify the filter condition:
> 1 condition that can get the boolean result such as:
> {code}
> a < 3 && a > 5 ---- False
> a < 1 || a > 0 ---- True
> {code}
> 2 Simplify And, Or condition, such as the sql (one of hive-testbench):
> {code}
> select
> sum(l_extendedprice* (1 - l_discount)) as revenue
> from
> lineitem,
> part
> where
> (
> p_partkey = l_partkey
> and p_brand = 'Brand#32'
> and p_container in ('SM CASE', 'SM BOX', 'SM PACK', 'SM PKG')
> and l_quantity >= 7 and l_quantity <= 7 + 10
> and p_size between 1 and 5
> and l_shipmode in ('AIR', 'AIR REG')
> and l_shipinstruct = 'DELIVER IN PERSON'
> )
> or
> (
> p_partkey = l_partkey
> and p_brand = 'Brand#35'
> and p_container in ('MED BAG', 'MED BOX', 'MED PKG', 'MED PACK')
> and l_quantity >= 15 and l_quantity <= 15 + 10
> and p_size between 1 and 10
> and l_shipmode in ('AIR', 'AIR REG')
> and l_shipinstruct = 'DELIVER IN PERSON'
> )
> or
> (
> p_partkey = l_partkey
> and p_brand = 'Brand#24'
> and p_container in ('LG CASE', 'LG BOX', 'LG PACK', 'LG PKG')
> and l_quantity >= 26 and l_quantity <= 26 + 10
> and p_size between 1 and 15
> and l_shipmode in ('AIR', 'AIR REG')
> and l_shipinstruct = 'DELIVER IN PERSON'
> );
> {code}
> Before optimized it is a CartesianProduct, in my locally test this sql hang and can not get result, after optimization the CartesianProduct replaced by ShuffledHashJoin, which only need 20+ seconds to run this sql.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
---------------------------------------------------------------------
To unsubscribe, e-mail: issues-unsubscribe@spark.apache.org
For additional commands, e-mail: issues-help@spark.apache.org