You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@tajo.apache.org by "Jaehwa Jung (JIRA)" <ji...@apache.org> on 2014/07/21 04:38:38 UTC
[jira] [Updated] (TAJO-964) Scan filter was removed occasionally.
[ https://issues.apache.org/jira/browse/TAJO-964?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Jaehwa Jung updated TAJO-964:
-----------------------------
Description:
I ran TPCH Q19 query. But it didn't run as expected. Because there was no scan filer in optimized plan as follows:
* Q19
{code:xml}
select
sum(l_extendedprice * (1 - l_discount) ) as revenue
from
lineitem l join part p
on
p.p_partkey = l.l_partkey
where
(
p_brand = 'Brand#12'
and p_container REGEXP 'SM CASE||SM BOX||SM PACK||SM PKG'
and l_quantity >= 1 and l_quantity <= 11
and p_size >= 1 and p_size <= 5
and l_shipmode REGEXP 'AIR||AIR REG'
and l_shipinstruct = 'DELIVER IN PERSON'
)
or
(
p_brand = 'Brand#23'
and p_container REGEXP 'MED BAG||MED BOX||MED PKG||MED PACK'
and l_quantity >= 10 and l_quantity <= 20
and p_size >= 1 and p_size <= 10
and l_shipmode REGEXP 'AIR||AIR REG'
and l_shipinstruct = 'DELIVER IN PERSON'
)
or
(
p_brand = 'Brand#34'
and p_container REGEXP 'LG CASE||LG BOX||LG PACK||LG PKG'
and l_quantity >= 20 and l_quantity <= 30
and p_size >= 1 and p_size <= 15
and l_shipmode REGEXP 'AIR||AIR REG'
and l_shipinstruct = 'DELIVER IN PERSON'
)
{code}
* Non optimized plan
{code:xml}
PROJECTION(4)
=> Targets: revenue (FLOAT8)
=> out schema: {(1) revenue (FLOAT8)}
=> in schema: {(1) revenue (FLOAT8)}
GROUP_BY(5)()
=> exprs: (sum(?multiply (FLOAT8)))
=> target list: revenue (FLOAT8)
=> out schema:{(1) revenue (FLOAT8)}
=> in schema:{(28) default.l.l_orderkey (INT4),default.l.l_partkey (INT4),default.l.l_suppkey (INT4),default.l.l_linenumber (INT4),default.l.l_quantity (FLOAT8),default.l.l_extendedprice (FLOAT8),default.l.l_discount (FLOAT8),default.l.l_tax (FLOAT8),default.l.l_returnflag (TEXT),default.l.l_linestatus (TEXT),default.l.l_shipdate (TEXT),default.l.l_commitdate (TEXT),default.l.l_receiptdate (TEXT),default.l.l_shipinstruct (TEXT),default.l.l_shipmode (TEXT),default.l.l_comment (TEXT),?multiply (FLOAT8),default.p.p_partkey (INT4),default.p.p_name (TEXT),default.p.p_mfgr (TEXT),default.p.p_brand (TEXT),default.p.p_type (TEXT),default.p.p_size (INT4),default.p.p_container (TEXT),default.p.p_retailprice (FLOAT8),default.p.p_comment (TEXT),?or_1 (BOOLEAN),?equals_2 (BOOLEAN)}
SELECTION(3)
=> Search Cond: default.p.p_brand (TEXT) = Brand#12 AND default.p.p_container (TEXT)~'SM CASE||SM BOX||SM PACK||SM PKG' AND default.l.l_quantity (FLOAT8) >= 1.0 AND default.l.l_quantity (FLOAT8) <= 11.0 AND default.p.p_size (INT4) >= 1 AND default.p.p_size (INT4) <= 5 AND default.l.l_shipmode (TEXT)~'AIR||AIR REG' AND default.l.l_shipinstruct (TEXT) = DELIVER IN PERSON OR default.p.p_brand (TEXT) = Brand#23 AND default.p.p_container (TEXT)~'MED BAG||MED BOX||MED PKG||MED PACK' AND default.l.l_quantity (FLOAT8) >= 10.0 AND default.l.l_quantity (FLOAT8) <= 20.0 AND default.p.p_size (INT4) >= 1 AND default.p.p_size (INT4) <= 10 AND default.l.l_shipmode (TEXT)~'AIR||AIR REG' AND default.l.l_shipinstruct (TEXT) = DELIVER IN PERSON OR default.p.p_brand (TEXT) = Brand#34 AND default.p.p_container (TEXT)~'LG CASE||LG BOX||LG PACK||LG PKG' AND default.l.l_quantity (FLOAT8) >= 20.0 AND default.l.l_quantity (FLOAT8) <= 30.0 AND default.p.p_size (INT4) >= 1 AND default.p.p_size (INT4) <= 15 AND default.l.l_shipmode (TEXT)~'AIR||AIR REG' AND default.l.l_shipinstruct (TEXT) = DELIVER IN PERSON
JOIN(2)(INNER)
=> Join Cond: default.p.p_partkey (INT4) = default.l.l_partkey (INT4)
=> target list: default.l.l_orderkey (INT4), default.l.l_partkey (INT4), default.l.l_suppkey (INT4), default.l.l_linenumber (INT4), default.l.l_quantity (FLOAT8), default.l.l_extendedprice (FLOAT8), default.l.l_discount (FLOAT8), default.l.l_tax (FLOAT8), default.l.l_returnflag (TEXT), default.l.l_linestatus (TEXT), default.l.l_shipdate (TEXT), default.l.l_commitdate (TEXT), default.l.l_receiptdate (TEXT), default.l.l_shipinstruct (TEXT), default.l.l_shipmode (TEXT), default.l.l_comment (TEXT), ?multiply (FLOAT8), default.p.p_partkey (INT4), default.p.p_name (TEXT), default.p.p_mfgr (TEXT), default.p.p_brand (TEXT), default.p.p_type (TEXT), default.p.p_size (INT4), default.p.p_container (TEXT), default.p.p_retailprice (FLOAT8), default.p.p_comment (TEXT), default.p.p_brand (TEXT) = Brand#12 AND default.p.p_container (TEXT)~'SM CASE||SM BOX||SM PACK||SM PKG' AND default.l.l_quantity (FLOAT8) >= 1.0 AND default.l.l_quantity (FLOAT8) <= 11.0 AND default.p.p_size (INT4) >= 1 AND default.p.p_size (INT4) <= 5 AND default.l.l_shipmode (TEXT)~'AIR||AIR REG' AND default.l.l_shipinstruct (TEXT) = DELIVER IN PERSON OR default.p.p_brand (TEXT) = Brand#23 AND default.p.p_container (TEXT)~'MED BAG||MED BOX||MED PKG||MED PACK' AND default.l.l_quantity (FLOAT8) >= 10.0 AND default.l.l_quantity (FLOAT8) <= 20.0 AND default.p.p_size (INT4) >= 1 AND default.p.p_size (INT4) <= 10 AND default.l.l_shipmode (TEXT)~'AIR||AIR REG' AND default.l.l_shipinstruct (TEXT) = DELIVER IN PERSON OR default.p.p_brand (TEXT) = Brand#34 AND default.p.p_container (TEXT)~'LG CASE||LG BOX||LG PACK||LG PKG' AND default.l.l_quantity (FLOAT8) >= 20.0 AND default.l.l_quantity (FLOAT8) <= 30.0 AND default.p.p_size (INT4) >= 1 AND default.p.p_size (INT4) <= 15 AND default.l.l_shipmode (TEXT)~'AIR||AIR REG' AND default.l.l_shipinstruct (TEXT) = DELIVER IN PERSON as ?or_1, default.p.p_partkey (INT4) = default.l.l_partkey (INT4) as ?equals_2
=> out schema: {(28) default.l.l_orderkey (INT4),default.l.l_partkey (INT4),default.l.l_suppkey (INT4),default.l.l_linenumber (INT4),default.l.l_quantity (FLOAT8),default.l.l_extendedprice (FLOAT8),default.l.l_discount (FLOAT8),default.l.l_tax (FLOAT8),default.l.l_returnflag (TEXT),default.l.l_linestatus (TEXT),default.l.l_shipdate (TEXT),default.l.l_commitdate (TEXT),default.l.l_receiptdate (TEXT),default.l.l_shipinstruct (TEXT),default.l.l_shipmode (TEXT),default.l.l_comment (TEXT),?multiply (FLOAT8),default.p.p_partkey (INT4),default.p.p_name (TEXT),default.p.p_mfgr (TEXT),default.p.p_brand (TEXT),default.p.p_type (TEXT),default.p.p_size (INT4),default.p.p_container (TEXT),default.p.p_retailprice (FLOAT8),default.p.p_comment (TEXT),?or_1 (BOOLEAN),?equals_2 (BOOLEAN)}
=> in schema: {(26) default.l.l_orderkey (INT4),default.l.l_partkey (INT4),default.l.l_suppkey (INT4),default.l.l_linenumber (INT4),default.l.l_quantity (FLOAT8),default.l.l_extendedprice (FLOAT8),default.l.l_discount (FLOAT8),default.l.l_tax (FLOAT8),default.l.l_returnflag (TEXT),default.l.l_linestatus (TEXT),default.l.l_shipdate (TEXT),default.l.l_commitdate (TEXT),default.l.l_receiptdate (TEXT),default.l.l_shipinstruct (TEXT),default.l.l_shipmode (TEXT),default.l.l_comment (TEXT),?multiply (FLOAT8),default.p.p_partkey (INT4),default.p.p_name (TEXT),default.p.p_mfgr (TEXT),default.p.p_brand (TEXT),default.p.p_type (TEXT),default.p.p_size (INT4),default.p.p_container (TEXT),default.p.p_retailprice (FLOAT8),default.p.p_comment (TEXT)}
SCAN(1) on default.part as p
=> target list: default.p.p_partkey (INT4), default.p.p_name (TEXT), default.p.p_mfgr (TEXT), default.p.p_brand (TEXT), default.p.p_type (TEXT), default.p.p_size (INT4), default.p.p_container (TEXT), default.p.p_retailprice (FLOAT8), default.p.p_comment (TEXT)
=> out schema: {(9) default.p.p_partkey (INT4),default.p.p_name (TEXT),default.p.p_mfgr (TEXT),default.p.p_brand (TEXT),default.p.p_type (TEXT),default.p.p_size (INT4),default.p.p_container (TEXT),default.p.p_retailprice (FLOAT8),default.p.p_comment (TEXT)}
=> in schema: {(9) default.p.p_partkey (INT4),default.p.p_name (TEXT),default.p.p_mfgr (TEXT),default.p.p_brand (TEXT),default.p.p_type (TEXT),default.p.p_size (INT4),default.p.p_container (TEXT),default.p.p_retailprice (FLOAT8),default.p.p_comment (TEXT)}
SCAN(0) on default.lineitem as l
=> target list: default.l.l_orderkey (INT4), default.l.l_partkey (INT4), default.l.l_suppkey (INT4), default.l.l_linenumber (INT4), default.l.l_quantity (FLOAT8), default.l.l_extendedprice (FLOAT8), default.l.l_discount (FLOAT8), default.l.l_tax (FLOAT8), default.l.l_returnflag (TEXT), default.l.l_linestatus (TEXT), default.l.l_shipdate (TEXT), default.l.l_commitdate (TEXT), default.l.l_receiptdate (TEXT), default.l.l_shipinstruct (TEXT), default.l.l_shipmode (TEXT), default.l.l_comment (TEXT), default.l.l_extendedprice (FLOAT8) * 1.0 - default.l.l_discount (FLOAT8) as ?multiply
=> out schema: {(17) default.l.l_orderkey (INT4),default.l.l_partkey (INT4),default.l.l_suppkey (INT4),default.l.l_linenumber (INT4),default.l.l_quantity (FLOAT8),default.l.l_extendedprice (FLOAT8),default.l.l_discount (FLOAT8),default.l.l_tax (FLOAT8),default.l.l_returnflag (TEXT),default.l.l_linestatus (TEXT),default.l.l_shipdate (TEXT),default.l.l_commitdate (TEXT),default.l.l_receiptdate (TEXT),default.l.l_shipinstruct (TEXT),default.l.l_shipmode (TEXT),default.l.l_comment (TEXT),?multiply (FLOAT8)}
=> in schema: {(16) default.l.l_orderkey (INT4),default.l.l_partkey (INT4),default.l.l_suppkey (INT4),default.l.l_linenumber (INT4),default.l.l_quantity (FLOAT8),default.l.l_extendedprice (FLOAT8),default.l.l_discount (FLOAT8),default.l.l_tax (FLOAT8),default.l.l_returnflag (TEXT),default.l.l_linestatus (TEXT),default.l.l_shipdate (TEXT),default.l.l_commitdate (TEXT),default.l.l_receiptdate (TEXT),default.l.l_shipinstruct (TEXT),default.l.l_shipmode (TEXT),default.l.l_comment (TEXT)}
{code}
* optimized plan
{code:xml}
GROUP_BY(5)()
=> exprs: (sum(?multiply (FLOAT8)))
=> target list: revenue (FLOAT8)
=> out schema:{(1) revenue (FLOAT8)}
=> in schema:{(1) ?multiply (FLOAT8)}
JOIN(7)(INNER)
=> Join Cond: tpch100.p.p_partkey (INT8) = tpch100.l.l_partkey (INT8)
=> target list: ?multiply (FLOAT8)
=> out schema: {(1) ?multiply (FLOAT8)}
=> in schema: {(3) tpch100.l.l_partkey (INT8),?multiply (FLOAT8),tpch100.p.p_partkey (INT8)}
SCAN(1) on tpch100.part as p
=> target list: tpch100.p.p_partkey (INT8)
=> out schema: {(1) tpch100.p.p_partkey (INT8)}
=> in schema: {(9) tpch100.p.p_partkey (INT8),tpch100.p.p_name (TEXT),tpch100.p.p_mfgr (TEXT),tpch100.p.p_brand (TEXT),tpch100.p.p_type (TEXT),tpch100.p.p_size (INT4),tpch100.p.p_container (TEXT),tpch100.p.p_retailprice (FLOAT8),tpch100.p.p_comment (TEXT)}
SCAN(0) on tpch100.lineitem as l
=> target list: tpch100.l.l_partkey (INT8), tpch100.l.l_extendedprice (FLOAT8) * 1.0 - tpch100.l.l_discount (FLOAT8) as ?multiply
=> out schema: {(2) tpch100.l.l_partkey (INT8),?multiply (FLOAT8)}
=> in schema: {(16) tpch100.l.l_orderkey (INT8),tpch100.l.l_partkey (INT8),tpch100.l.l_suppkey (INT8),tpch100.l.l_linenumber (INT8),tpch100.l.l_quantity (FLOAT8),tpch100.l.l_extendedprice (FLOAT8),tpch100.l.l_discount (FLOAT8),tpch100.l.l_tax (FLOAT8),tpch100.l.l_returnflag (TEXT),tpch100.l.l_linestatus (TEXT),tpch100.l.l_shipdate (TEXT),tpch100.l.l_commitdate (TEXT),tpch100.l.l_receiptdate (TEXT),tpch100.l.l_shipinstruct (TEXT),tpch100.l.l_shipmode (TEXT),tpch100.l.l_comment (TEXT)}
{code}
was:
I ran TPCH Q19 query. But it didn't run as expected. Because there was no scan filer in optimized plan as follows:
* Q19
{code:xml}
select
sum(l_extendedprice * (1 - l_discount) ) as revenue
from
lineitem l join part p
on
p.p_partkey = l.l_partkey
where
(
p_brand = 'Brand#12'
and p_container REGEXP 'SM CASE||SM BOX||SM PACK||SM PKG'
and l_quantity >= 1 and l_quantity <= 11
and p_size >= 1 and p_size <= 5
and l_shipmode REGEXP 'AIR||AIR REG'
and l_shipinstruct = 'DELIVER IN PERSON'
)
or
(
p_brand = 'Brand#23'
and p_container REGEXP 'MED BAG||MED BOX||MED PKG||MED PACK'
and l_quantity >= 10 and l_quantity <= 20
and p_size >= 1 and p_size <= 10
and l_shipmode REGEXP 'AIR||AIR REG'
and l_shipinstruct = 'DELIVER IN PERSON'
)
or
(
p_brand = 'Brand#34'
and p_container REGEXP 'LG CASE||LG BOX||LG PACK||LG PKG'
and l_quantity >= 20 and l_quantity <= 30
and p_size >= 1 and p_size <= 15
and l_shipmode REGEXP 'AIR||AIR REG'
and l_shipinstruct = 'DELIVER IN PERSON'
)
{code}
* optimized plan
{code:xml}
-----------------------------
Query Block Graph
-----------------------------
|-#ROOT
-----------------------------
Optimization Log:
[LogicalPlan]
> ProjectionNode is eliminated.
[#ROOT]
> Non-optimized join order: (tpch100.lineitem ⋈θ tpch100.part) (cost: 1.95870973182278272E18)
> Optimized join order : (tpch100.lineitem ⋈θ tpch100.part) (cost: 1.9587097318227825E19)
-----------------------------
GROUP_BY(5)()
=> exprs: (sum(?multiply (FLOAT8)))
=> target list: revenue (FLOAT8)
=> out schema:{(1) revenue (FLOAT8)}
=> in schema:{(1) ?multiply (FLOAT8)}
JOIN(7)(INNER)
=> Join Cond: tpch100.p.p_partkey (INT8) = tpch100.l.l_partkey (INT8)
=> target list: ?multiply (FLOAT8)
=> out schema: {(1) ?multiply (FLOAT8)}
=> in schema: {(3) tpch100.l.l_partkey (INT8),?multiply (FLOAT8),tpch100.p.p_partkey (INT8)}
SCAN(1) on tpch100.part as p
=> target list: tpch100.p.p_partkey (INT8)
=> out schema: {(1) tpch100.p.p_partkey (INT8)}
=> in schema: {(9) tpch100.p.p_partkey (INT8),tpch100.p.p_name (TEXT),tpch100.p.p_mfgr (TEXT),tpch100.p.p_brand (TEXT),tpch100.p.p_type (TEXT),tpch100.p.p_size (INT4),tpch100.p.p_container (TEXT),tpch100.p.p_retailprice (FLOAT8),tpch100.p.p_comment (TEXT)}
SCAN(0) on tpch100.lineitem as l
=> target list: tpch100.l.l_partkey (INT8), tpch100.l.l_extendedprice (FLOAT8) * 1.0 - tpch100.l.l_discount (FLOAT8) as ?multiply
=> out schema: {(2) tpch100.l.l_partkey (INT8),?multiply (FLOAT8)}
=> in schema: {(16) tpch100.l.l_orderkey (INT8),tpch100.l.l_partkey (INT8),tpch100.l.l_suppkey (INT8),tpch100.l.l_linenumber (INT8),tpch100.l.l_quantity (FLOAT8),tpch100.l.l_extendedprice (FLOAT8),tpch100.l.l_discount (FLOAT8),tpch100.l.l_tax (FLOAT8),tpch100.l.l_returnflag (TEXT),tpch100.l.l_linestatus (TEXT),tpch100.l.l_shipdate (TEXT),tpch100.l.l_commitdate (TEXT),tpch100.l.l_receiptdate (TEXT),tpch100.l.l_shipinstruct (TEXT),tpch100.l.l_shipmode (TEXT),tpch100.l.l_comment (TEXT)}
{code}
> Scan filter was removed occasionally.
> --------------------------------------
>
> Key: TAJO-964
> URL: https://issues.apache.org/jira/browse/TAJO-964
> Project: Tajo
> Issue Type: Bug
> Components: planner/optimizer
> Reporter: Jaehwa Jung
> Assignee: Jaehwa Jung
>
> I ran TPCH Q19 query. But it didn't run as expected. Because there was no scan filer in optimized plan as follows:
> * Q19
> {code:xml}
> select
> sum(l_extendedprice * (1 - l_discount) ) as revenue
> from
> lineitem l join part p
> on
> p.p_partkey = l.l_partkey
> where
> (
> p_brand = 'Brand#12'
> and p_container REGEXP 'SM CASE||SM BOX||SM PACK||SM PKG'
> and l_quantity >= 1 and l_quantity <= 11
> and p_size >= 1 and p_size <= 5
> and l_shipmode REGEXP 'AIR||AIR REG'
> and l_shipinstruct = 'DELIVER IN PERSON'
> )
> or
> (
> p_brand = 'Brand#23'
> and p_container REGEXP 'MED BAG||MED BOX||MED PKG||MED PACK'
> and l_quantity >= 10 and l_quantity <= 20
> and p_size >= 1 and p_size <= 10
> and l_shipmode REGEXP 'AIR||AIR REG'
> and l_shipinstruct = 'DELIVER IN PERSON'
> )
> or
> (
> p_brand = 'Brand#34'
> and p_container REGEXP 'LG CASE||LG BOX||LG PACK||LG PKG'
> and l_quantity >= 20 and l_quantity <= 30
> and p_size >= 1 and p_size <= 15
> and l_shipmode REGEXP 'AIR||AIR REG'
> and l_shipinstruct = 'DELIVER IN PERSON'
> )
> {code}
> * Non optimized plan
> {code:xml}
> PROJECTION(4)
> => Targets: revenue (FLOAT8)
> => out schema: {(1) revenue (FLOAT8)}
> => in schema: {(1) revenue (FLOAT8)}
> GROUP_BY(5)()
> => exprs: (sum(?multiply (FLOAT8)))
> => target list: revenue (FLOAT8)
> => out schema:{(1) revenue (FLOAT8)}
> => in schema:{(28) default.l.l_orderkey (INT4),default.l.l_partkey (INT4),default.l.l_suppkey (INT4),default.l.l_linenumber (INT4),default.l.l_quantity (FLOAT8),default.l.l_extendedprice (FLOAT8),default.l.l_discount (FLOAT8),default.l.l_tax (FLOAT8),default.l.l_returnflag (TEXT),default.l.l_linestatus (TEXT),default.l.l_shipdate (TEXT),default.l.l_commitdate (TEXT),default.l.l_receiptdate (TEXT),default.l.l_shipinstruct (TEXT),default.l.l_shipmode (TEXT),default.l.l_comment (TEXT),?multiply (FLOAT8),default.p.p_partkey (INT4),default.p.p_name (TEXT),default.p.p_mfgr (TEXT),default.p.p_brand (TEXT),default.p.p_type (TEXT),default.p.p_size (INT4),default.p.p_container (TEXT),default.p.p_retailprice (FLOAT8),default.p.p_comment (TEXT),?or_1 (BOOLEAN),?equals_2 (BOOLEAN)}
> SELECTION(3)
> => Search Cond: default.p.p_brand (TEXT) = Brand#12 AND default.p.p_container (TEXT)~'SM CASE||SM BOX||SM PACK||SM PKG' AND default.l.l_quantity (FLOAT8) >= 1.0 AND default.l.l_quantity (FLOAT8) <= 11.0 AND default.p.p_size (INT4) >= 1 AND default.p.p_size (INT4) <= 5 AND default.l.l_shipmode (TEXT)~'AIR||AIR REG' AND default.l.l_shipinstruct (TEXT) = DELIVER IN PERSON OR default.p.p_brand (TEXT) = Brand#23 AND default.p.p_container (TEXT)~'MED BAG||MED BOX||MED PKG||MED PACK' AND default.l.l_quantity (FLOAT8) >= 10.0 AND default.l.l_quantity (FLOAT8) <= 20.0 AND default.p.p_size (INT4) >= 1 AND default.p.p_size (INT4) <= 10 AND default.l.l_shipmode (TEXT)~'AIR||AIR REG' AND default.l.l_shipinstruct (TEXT) = DELIVER IN PERSON OR default.p.p_brand (TEXT) = Brand#34 AND default.p.p_container (TEXT)~'LG CASE||LG BOX||LG PACK||LG PKG' AND default.l.l_quantity (FLOAT8) >= 20.0 AND default.l.l_quantity (FLOAT8) <= 30.0 AND default.p.p_size (INT4) >= 1 AND default.p.p_size (INT4) <= 15 AND default.l.l_shipmode (TEXT)~'AIR||AIR REG' AND default.l.l_shipinstruct (TEXT) = DELIVER IN PERSON
> JOIN(2)(INNER)
> => Join Cond: default.p.p_partkey (INT4) = default.l.l_partkey (INT4)
> => target list: default.l.l_orderkey (INT4), default.l.l_partkey (INT4), default.l.l_suppkey (INT4), default.l.l_linenumber (INT4), default.l.l_quantity (FLOAT8), default.l.l_extendedprice (FLOAT8), default.l.l_discount (FLOAT8), default.l.l_tax (FLOAT8), default.l.l_returnflag (TEXT), default.l.l_linestatus (TEXT), default.l.l_shipdate (TEXT), default.l.l_commitdate (TEXT), default.l.l_receiptdate (TEXT), default.l.l_shipinstruct (TEXT), default.l.l_shipmode (TEXT), default.l.l_comment (TEXT), ?multiply (FLOAT8), default.p.p_partkey (INT4), default.p.p_name (TEXT), default.p.p_mfgr (TEXT), default.p.p_brand (TEXT), default.p.p_type (TEXT), default.p.p_size (INT4), default.p.p_container (TEXT), default.p.p_retailprice (FLOAT8), default.p.p_comment (TEXT), default.p.p_brand (TEXT) = Brand#12 AND default.p.p_container (TEXT)~'SM CASE||SM BOX||SM PACK||SM PKG' AND default.l.l_quantity (FLOAT8) >= 1.0 AND default.l.l_quantity (FLOAT8) <= 11.0 AND default.p.p_size (INT4) >= 1 AND default.p.p_size (INT4) <= 5 AND default.l.l_shipmode (TEXT)~'AIR||AIR REG' AND default.l.l_shipinstruct (TEXT) = DELIVER IN PERSON OR default.p.p_brand (TEXT) = Brand#23 AND default.p.p_container (TEXT)~'MED BAG||MED BOX||MED PKG||MED PACK' AND default.l.l_quantity (FLOAT8) >= 10.0 AND default.l.l_quantity (FLOAT8) <= 20.0 AND default.p.p_size (INT4) >= 1 AND default.p.p_size (INT4) <= 10 AND default.l.l_shipmode (TEXT)~'AIR||AIR REG' AND default.l.l_shipinstruct (TEXT) = DELIVER IN PERSON OR default.p.p_brand (TEXT) = Brand#34 AND default.p.p_container (TEXT)~'LG CASE||LG BOX||LG PACK||LG PKG' AND default.l.l_quantity (FLOAT8) >= 20.0 AND default.l.l_quantity (FLOAT8) <= 30.0 AND default.p.p_size (INT4) >= 1 AND default.p.p_size (INT4) <= 15 AND default.l.l_shipmode (TEXT)~'AIR||AIR REG' AND default.l.l_shipinstruct (TEXT) = DELIVER IN PERSON as ?or_1, default.p.p_partkey (INT4) = default.l.l_partkey (INT4) as ?equals_2
> => out schema: {(28) default.l.l_orderkey (INT4),default.l.l_partkey (INT4),default.l.l_suppkey (INT4),default.l.l_linenumber (INT4),default.l.l_quantity (FLOAT8),default.l.l_extendedprice (FLOAT8),default.l.l_discount (FLOAT8),default.l.l_tax (FLOAT8),default.l.l_returnflag (TEXT),default.l.l_linestatus (TEXT),default.l.l_shipdate (TEXT),default.l.l_commitdate (TEXT),default.l.l_receiptdate (TEXT),default.l.l_shipinstruct (TEXT),default.l.l_shipmode (TEXT),default.l.l_comment (TEXT),?multiply (FLOAT8),default.p.p_partkey (INT4),default.p.p_name (TEXT),default.p.p_mfgr (TEXT),default.p.p_brand (TEXT),default.p.p_type (TEXT),default.p.p_size (INT4),default.p.p_container (TEXT),default.p.p_retailprice (FLOAT8),default.p.p_comment (TEXT),?or_1 (BOOLEAN),?equals_2 (BOOLEAN)}
> => in schema: {(26) default.l.l_orderkey (INT4),default.l.l_partkey (INT4),default.l.l_suppkey (INT4),default.l.l_linenumber (INT4),default.l.l_quantity (FLOAT8),default.l.l_extendedprice (FLOAT8),default.l.l_discount (FLOAT8),default.l.l_tax (FLOAT8),default.l.l_returnflag (TEXT),default.l.l_linestatus (TEXT),default.l.l_shipdate (TEXT),default.l.l_commitdate (TEXT),default.l.l_receiptdate (TEXT),default.l.l_shipinstruct (TEXT),default.l.l_shipmode (TEXT),default.l.l_comment (TEXT),?multiply (FLOAT8),default.p.p_partkey (INT4),default.p.p_name (TEXT),default.p.p_mfgr (TEXT),default.p.p_brand (TEXT),default.p.p_type (TEXT),default.p.p_size (INT4),default.p.p_container (TEXT),default.p.p_retailprice (FLOAT8),default.p.p_comment (TEXT)}
> SCAN(1) on default.part as p
> => target list: default.p.p_partkey (INT4), default.p.p_name (TEXT), default.p.p_mfgr (TEXT), default.p.p_brand (TEXT), default.p.p_type (TEXT), default.p.p_size (INT4), default.p.p_container (TEXT), default.p.p_retailprice (FLOAT8), default.p.p_comment (TEXT)
> => out schema: {(9) default.p.p_partkey (INT4),default.p.p_name (TEXT),default.p.p_mfgr (TEXT),default.p.p_brand (TEXT),default.p.p_type (TEXT),default.p.p_size (INT4),default.p.p_container (TEXT),default.p.p_retailprice (FLOAT8),default.p.p_comment (TEXT)}
> => in schema: {(9) default.p.p_partkey (INT4),default.p.p_name (TEXT),default.p.p_mfgr (TEXT),default.p.p_brand (TEXT),default.p.p_type (TEXT),default.p.p_size (INT4),default.p.p_container (TEXT),default.p.p_retailprice (FLOAT8),default.p.p_comment (TEXT)}
> SCAN(0) on default.lineitem as l
> => target list: default.l.l_orderkey (INT4), default.l.l_partkey (INT4), default.l.l_suppkey (INT4), default.l.l_linenumber (INT4), default.l.l_quantity (FLOAT8), default.l.l_extendedprice (FLOAT8), default.l.l_discount (FLOAT8), default.l.l_tax (FLOAT8), default.l.l_returnflag (TEXT), default.l.l_linestatus (TEXT), default.l.l_shipdate (TEXT), default.l.l_commitdate (TEXT), default.l.l_receiptdate (TEXT), default.l.l_shipinstruct (TEXT), default.l.l_shipmode (TEXT), default.l.l_comment (TEXT), default.l.l_extendedprice (FLOAT8) * 1.0 - default.l.l_discount (FLOAT8) as ?multiply
> => out schema: {(17) default.l.l_orderkey (INT4),default.l.l_partkey (INT4),default.l.l_suppkey (INT4),default.l.l_linenumber (INT4),default.l.l_quantity (FLOAT8),default.l.l_extendedprice (FLOAT8),default.l.l_discount (FLOAT8),default.l.l_tax (FLOAT8),default.l.l_returnflag (TEXT),default.l.l_linestatus (TEXT),default.l.l_shipdate (TEXT),default.l.l_commitdate (TEXT),default.l.l_receiptdate (TEXT),default.l.l_shipinstruct (TEXT),default.l.l_shipmode (TEXT),default.l.l_comment (TEXT),?multiply (FLOAT8)}
> => in schema: {(16) default.l.l_orderkey (INT4),default.l.l_partkey (INT4),default.l.l_suppkey (INT4),default.l.l_linenumber (INT4),default.l.l_quantity (FLOAT8),default.l.l_extendedprice (FLOAT8),default.l.l_discount (FLOAT8),default.l.l_tax (FLOAT8),default.l.l_returnflag (TEXT),default.l.l_linestatus (TEXT),default.l.l_shipdate (TEXT),default.l.l_commitdate (TEXT),default.l.l_receiptdate (TEXT),default.l.l_shipinstruct (TEXT),default.l.l_shipmode (TEXT),default.l.l_comment (TEXT)}
> {code}
> * optimized plan
> {code:xml}
> GROUP_BY(5)()
> => exprs: (sum(?multiply (FLOAT8)))
> => target list: revenue (FLOAT8)
> => out schema:{(1) revenue (FLOAT8)}
> => in schema:{(1) ?multiply (FLOAT8)}
> JOIN(7)(INNER)
> => Join Cond: tpch100.p.p_partkey (INT8) = tpch100.l.l_partkey (INT8)
> => target list: ?multiply (FLOAT8)
> => out schema: {(1) ?multiply (FLOAT8)}
> => in schema: {(3) tpch100.l.l_partkey (INT8),?multiply (FLOAT8),tpch100.p.p_partkey (INT8)}
> SCAN(1) on tpch100.part as p
> => target list: tpch100.p.p_partkey (INT8)
> => out schema: {(1) tpch100.p.p_partkey (INT8)}
> => in schema: {(9) tpch100.p.p_partkey (INT8),tpch100.p.p_name (TEXT),tpch100.p.p_mfgr (TEXT),tpch100.p.p_brand (TEXT),tpch100.p.p_type (TEXT),tpch100.p.p_size (INT4),tpch100.p.p_container (TEXT),tpch100.p.p_retailprice (FLOAT8),tpch100.p.p_comment (TEXT)}
> SCAN(0) on tpch100.lineitem as l
> => target list: tpch100.l.l_partkey (INT8), tpch100.l.l_extendedprice (FLOAT8) * 1.0 - tpch100.l.l_discount (FLOAT8) as ?multiply
> => out schema: {(2) tpch100.l.l_partkey (INT8),?multiply (FLOAT8)}
> => in schema: {(16) tpch100.l.l_orderkey (INT8),tpch100.l.l_partkey (INT8),tpch100.l.l_suppkey (INT8),tpch100.l.l_linenumber (INT8),tpch100.l.l_quantity (FLOAT8),tpch100.l.l_extendedprice (FLOAT8),tpch100.l.l_discount (FLOAT8),tpch100.l.l_tax (FLOAT8),tpch100.l.l_returnflag (TEXT),tpch100.l.l_linestatus (TEXT),tpch100.l.l_shipdate (TEXT),tpch100.l.l_commitdate (TEXT),tpch100.l.l_receiptdate (TEXT),tpch100.l.l_shipinstruct (TEXT),tpch100.l.l_shipmode (TEXT),tpch100.l.l_comment (TEXT)}
> {code}
--
This message was sent by Atlassian JIRA
(v6.2#6252)