[
https://issues.apache.org/jira/browse/IMPALA-11679?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Csaba Ringhofer updated IMPALA-11679:
-------------------------------------
Description:
In case of unpartitioned row_number() having a <= predicate on row number and
limit means the same, so these two queries should lead to an equivalent plan:
a:
select * from (select l_orderkey, row_number() OVER (ORDER by l_orderkey) as
rnum from tpch_parquet.lineitem) s
where rnum <= 10000;
b:
select * from (select l_orderkey, row_number() OVER (ORDER by l_orderkey) as
rnum from
tpch_parquet.lineitem) s
limit 10000;
Currently a. will use to a top-n node while b. will use a sort node.
For rnum <= 1000 a. will also use a top-n node
Meanwhile if there is also a rnum > X clause (essentially an OFFSET), then
limit has lower bounds for using top-n:
c:
select * from (select l_orderkey, row_number() OVER (ORDER by l_orderkey) as
rnum fromtpch_parquet.lineitem) s
where rnum > 900 and rnum <= 1000
d:
select * from (select l_orderkey, row_number() OVER (ORDER by l_orderkey) as
rnum from tpch_parquet.lineitem) s
where rnum > 900 limit 1000
c. will use a top-n node while d. will use a sort node
Besides not using the more optimal top-n (for low limits) another problem is
that the analyitic-eval-node will process all rows, even when all further rows
will be dropped by the predicate on row_number(). This is problematic as it
runs on a single node/thread.
A solution could be to recognize < and > predicates on unpartitioned
row_number() as limit and offset.
was:
In case of row_number() having a <= predicate on row number and limit means the
same, so these two queries should lead to an equivalent plan:
a:
select * from (select l_orderkey, row_number() OVER (ORDER by l_orderkey) as
rnum from tpch_parquet.lineitem) s
where rnum <= 10000;
b:
select * from (select l_orderkey, row_number() OVER (ORDER by l_orderkey) as
rnum from
tpch_parquet.lineitem) s
limit 10000;
Currently a. will use to a top-n node while b. will use a sort node.
For rnum <= 1000 a. will also use a top-n node
Meanwhile if there is also a rnum > X clause (essentially an OFFSET), then
limit has lower bounds for using top-n:
c:
select * from (select l_orderkey, row_number() OVER (ORDER by l_orderkey) as
rnum fromtpch_parquet.lineitem) s
where rnum > 900 and rnum <= 1000
d:
select * from (select l_orderkey, row_number() OVER (ORDER by l_orderkey) as
rnum from tpch_parquet.lineitem) s
where rnum > 900 limit 1000
c. will use a top-n node while d. will use a sort node
Besides not using the more optimal top-n (for low limits) another problem is
that the analyitic-eval-node will process all rows, even when all further rows
will be dropped by the predicate on row_number(). This is problematic as it
runs on a single node/thread.
A solution could be to recognize < and > predicates on unpartitioned
row_number() as limit and offset.
> Inconsistent push down of limit with unpartitioned row_number()
> ---------------------------------------------------------------
>
> Key: IMPALA-11679
> URL: https://issues.apache.org/jira/browse/IMPALA-11679
> Project: IMPALA
> Issue Type: Improvement
> Components: Frontend
> Reporter: Csaba Ringhofer
> Priority: Major
>
> In case of unpartitioned row_number() having a <= predicate on row number and
> limit means the same, so these two queries should lead to an equivalent plan:
> a:
> select * from (select l_orderkey, row_number() OVER (ORDER by l_orderkey) as
> rnum from tpch_parquet.lineitem) s
> where rnum <= 10000;
> b:
> select * from (select l_orderkey, row_number() OVER (ORDER by l_orderkey) as
> rnum from
> tpch_parquet.lineitem) s
> limit 10000;
> Currently a. will use to a top-n node while b. will use a sort node.
> For rnum <= 1000 a. will also use a top-n node
> Meanwhile if there is also a rnum > X clause (essentially an OFFSET), then
> limit has lower bounds for using top-n:
> c:
> select * from (select l_orderkey, row_number() OVER (ORDER by l_orderkey) as
> rnum fromtpch_parquet.lineitem) s
> where rnum > 900 and rnum <= 1000
> d:
> select * from (select l_orderkey, row_number() OVER (ORDER by l_orderkey) as
> rnum from tpch_parquet.lineitem) s
> where rnum > 900 limit 1000
> c. will use a top-n node while d. will use a sort node
> Besides not using the more optimal top-n (for low limits) another problem is
> that the analyitic-eval-node will process all rows, even when all further
> rows will be dropped by the predicate on row_number(). This is problematic as
> it runs on a single node/thread.
> A solution could be to recognize < and > predicates on unpartitioned
> row_number() as limit and offset.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
