You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@ignite.apache.org by "Alexander Belyak (Jira)" <ji...@apache.org> on 2023/06/01 12:48:00 UTC
[jira] [Updated] (IGNITE-19621) Slow query planning
[ https://issues.apache.org/jira/browse/IGNITE-19621?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Alexander Belyak updated IGNITE-19621:
--------------------------------------
Description:
Query from the TPC-H benchmark took 13 to 18 seconds to plan (try to execute on an empty TPCH tables)
Problem query is:
{code:java}
select sum(case when nation = 'aaaa' then volume else 0 end) / sum(volume) as mkt_share , o_year
from (
select floor(o_orderdate / (cast (365 as bigint) * 86400000)) as o_year,
l_extendedprice * (1 - l_discount) as volume,
n2.n_name as nation
from part, supplier, lineitem, orders, customer, nation n1, nation n2, region
where p_partkey = l_partkey and s_suppkey = l_suppkey and l_orderkey = o_orderkey and o_custkey = c_custkey and c_nationkey = n1.n_nationkey
and n1.n_regionkey = r_regionkey and r_name = 'rrr2' and s_nationkey = n2.n_nationkey
and o_orderdate between 788904000000 and 851972400000
and p_type = 1111
) as all_nations
group by o_year
order by o_year
{code}
Second run took about 50ms (query cache works fine).
See ddl in attachment.
was:
Query from the TPC-H benchmark took 13 to 18 seconds to plan (try to execute on an empty TPCH tables)
Problem query is:
{code:java}
select sum(case when nation = 'aaaa' then volume else 0 end) / sum(volume) as mkt_share , o_year
from (
select floor(o_orderdate / (cast (365 as bigint) * 86400000)) as o_year,
l_extendedprice * (1 - l_discount) as volume,
n2.n_name as nation
from part, supplier, lineitem, orders, customer, nation n1, nation n2, region
where p_partkey = l_partkey and s_suppkey = l_suppkey and l_orderkey = o_orderkey and o_custkey = c_custkey and c_nationkey = n1.n_nationkey
and n1.n_regionkey = r_regionkey and r_name = 'rrr2' and s_nationkey = n2.n_nationkey
and o_orderdate between 788904000000 and 851972400000
and p_type = 1111
) as all_nations
group by o_year
order by o_year
{code}
Second run took about 50ms (query cache works fine).
> Slow query planning
> -------------------
>
> Key: IGNITE-19621
> URL: https://issues.apache.org/jira/browse/IGNITE-19621
> Project: Ignite
> Issue Type: Bug
> Components: sql
> Affects Versions: 3.0
> Reporter: Alexander Belyak
> Priority: Major
> Labels: ignite-3
> Attachments: ddl-ignite3.sql
>
>
> Query from the TPC-H benchmark took 13 to 18 seconds to plan (try to execute on an empty TPCH tables)
> Problem query is:
> {code:java}
> select sum(case when nation = 'aaaa' then volume else 0 end) / sum(volume) as mkt_share , o_year
> from (
> select floor(o_orderdate / (cast (365 as bigint) * 86400000)) as o_year,
> l_extendedprice * (1 - l_discount) as volume,
> n2.n_name as nation
> from part, supplier, lineitem, orders, customer, nation n1, nation n2, region
> where p_partkey = l_partkey and s_suppkey = l_suppkey and l_orderkey = o_orderkey and o_custkey = c_custkey and c_nationkey = n1.n_nationkey
> and n1.n_regionkey = r_regionkey and r_name = 'rrr2' and s_nationkey = n2.n_nationkey
> and o_orderdate between 788904000000 and 851972400000
> and p_type = 1111
> ) as all_nations
> group by o_year
> order by o_year
> {code}
> Second run took about 50ms (query cache works fine).
> See ddl in attachment.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)