chuxiao created PHOENIX-5242:
--------------------------------
Summary: Physical execution plan issues and optimization
Key: PHOENIX-5242
URL: https://issues.apache.org/jira/browse/PHOENIX-5242
Project: Phoenix
Issue Type: Bug
Affects Versions: 5.0.0, 4.11.0
Reporter: chuxiao
tableA is 16salted，hfile TB level. look at this sql：
{{SELECT SUM(col1) AS col1 FROM tableA}}
{{ }}{{WHERE (( rowkey1 IN (}}{{"ws08"}}{{, }}{{"webx"}}{{)}}
{{AND rowkey2 = }}{{1}}
{{AND (rowkey3 = }}{{132}}
{{AND rowkey4 = }}{{6}} {{)}}
{{AND rowkey5 >= }}{{1544198400000}}
{{AND rowkey5 < }}{{1544281200000}}
{{AND rowkey8 IN (}}{{"ws0850"}}{{)))}}
{{run error:}}
{{Error: Task
org.apache.phoenix.job.JobManager$InstrumentedJobFutureTask}}{{@504e1599}}
{{ }}{{rejected from
org.apache.phoenix.job.JobManager$}}{{1}}{{@71f96dfb}}{{[Running,}}
{{ }}{{pool size = }}{{128}}{{, active threads = }}{{128}}{{, queued tasks =
}}{{4999}}{{, completed tasks = }}{{2469}}{{]
(state=}}{{08000}}{{,code=}}{{101}}{{)}}
{{look at explain，140000 chunks:}}
{{explain SELECT SUM(col1) AS col1 FROM tableA}}
{{ }}{{WHERE (( rowkey1 IN ( }}{{"ws08"}}{{, }}{{"webx"}}{{)}}
{{AND rowkey2 = }}{{1}}
{{AND ( rowkey3 = }}{{132}}
{{AND rowkey4 = }}{{6}}{{)}}
{{AND rowkey5 >= }}{{1544198400000}}
{{AND rowkey5 < }}{{1544281200000}}
{{AND rowkey8 IN (}}{{"ws0850"}}{{)));}}
{{+-----------------------------------------------------------------------------+
}}
{{| CLIENT }}{{14000}}{{-CHUNK}}
{{ }}{{1119777113}}{{ROWS}}
{{ }}{{5872026761963}}{{BYTES}}
{{ }}{{PARALLEL }}{{16}}{{-WAY}}
{{ }}{{SKIP SCAN ON }}{{32}}{{RANGES OVER indexB}}
{{
}}{{[}}{{0}}{{,}}{{'webx'}}{{,}}{{1}}{{,}}{{132}}{{,}}{{6}}{{,}}{{1544198400000}}{{]
-
[}}{{15}}{{,}}{{'ws08'}}{{,}}{{1}}{{,}}{{132}}{{,}}{{6}}{{,}}{{1544281200000}}{{]
}}
{{| SERVER FILTER BY }}{{"rowkey8"}}{{= }}{{'ws0850'}}
{{| SERVER AGGREGATE INTO SINGLE ROW
}}
{{+-----------------------------------------------------------------------------+}}
{{3}}{{rows selected (}}{{0.101}}{{seconds)}}
{{becase }}
{{rowkey1 IN (}}{{'ws08'}}{{, }}{{'webx'}}{{) = rowkey1 IN (}}{{'ws08'}}{{) +
rowkey1 IN (}}{{'webx'}}{{)，}}{{look at two sqls：}}
{{explain SELECT SUM(col1) AS col1 FROM tableA}}
{{ }}{{WHERE (( rowkey1 IN ( }}{{"ws08"}}{{)}}
{{AND rowkey2 = }}{{1}}
{{AND ( rowkey3 = }}{{132}}
{{AND rowkey4 = }}{{6}}{{)}}
{{AND rowkey5 >= }}{{1544198400000}}
{{AND rowkey5 < }}{{1544281200000}}
{{AND rowkey8 IN ( }}{{"ws0850"}}{{)));}}
{{+--------------------------------------------------------------------+
}}
{{| CLIENT }}{{16}}{{-CHUNK}}
{{ }}{{943087}}{{ROWS}}
{{ }}{{6710887793}}{{BYTES}}
{{ }}{{PARALLEL }}{{16}}{{-WAY RANGE SCAN OVER indexB}}
{{
}}{{[}}{{0}}{{,}}{{'ws08'}}{{,}}{{1}}{{,}}{{132}}{{,}}{{6}}{{,}}{{1544198400000}}{{]
-
[}}{{15}}{{,}}{{'ws08'}}{{,}}{{1}}{{,}}{{132}}{{,}}{{6}}{{,}}{{1544281200000}}{{]
}}
{{| SERVER FILTER BY }}{{"rowkey8"}}{{= }}{{'ws0850'}}
{{| SERVER AGGREGATE INTO SINGLE ROW
}}
{{+---------------------------------------------------------------------+}}
{{3}}{{rows selected (}}{{0.027}}{{seconds)}}
{{explain SELECT SUM(col1) AS col1 FROM tableA}}
{{ }}{{WHERE (( rowkey1 IN (}}{{"webx"}}{{)}}
{{AND rowkey2 = }}{{1}}
{{AND ( rowkey3 = }}{{132}}
{{AND rowkey4 = }}{{6}}{{)}}
{{AND rowkey5 >= }}{{1544198400000}}
{{AND rowkey5 < }}{{1544281200000}}
{{AND rowkey8 IN ( }}{{"ws0850"}}{{) ));}}
{{+------------------------------------------------------------------------+
}}
{{+}}
{{| CLIENT }}{{16}}{{-CHUNK}}
{{ }}{{1680043}}{{ROWS}}
{{ }}{{6710887653}}{{BYTES}}
{{ }}{{PARALLEL }}{{16}}{{-WAY RANGE SCAN OVER indexB}}
{{
}}{{[}}{{0}}{{,}}{{'webx'}}{{,}}{{1}}{{,}}{{132}}{{,}}{{6}}{{,}}{{1544198400000}}{{]
-
[}}{{15}}{{,}}{{'webx'}}{{,}}{{1}}{{,}}{{132}}{{,}}{{6}}{{,}}{{1544281200000}}{{]}}
{{| SERVER FILTER BY }}{{"rowkey8"}}{{= }}{{'webx'}}
{{| SERVER AGGREGATE INTO SINGLE ROW }}
{{+------------------------------------------------------------------------+}}
{{3}}{{rows selected (}}{{0.024}}{{seconds)}}
{{why 16 + 16 = 140000?}}
{{remove a filter, rowkey8 = 'webx':}}
{{explain SELECT SUM(col1) AS col1 FROM tableA}}
{{WHERE ( rowkey5 >= }}{{1544198400000}}
{{AND rowkey5 < }}{{1544281200000}}
{{AND ( rowkey3 = }}{{132}}
{{AND rowkey2 = }}{{1}}
{{AND ( rowkey1 IN ( }}{{'ws08'}}{{, }}{{'webx'}}{{)}}
{{AND rowkey4 = }}{{6}}{{))); }}
{{+--------------------------------------------------------------------+}}
{{| CLIENT }}{{32}}{{-CHUNK}}
{{ }}{{3665266}}{{ROWS}}
{{ }}{{13421775379}}{{BYTES PARALLEL}}
{{ }}{{16}}{{-WAY SKIP SCAN ON }}{{32}}{{RANGES OVER indexB}}
{{
}}{{[}}{{0}}{{,}}{{'webx'}}{{,}}{{1}}{{,}}{{132}}{{,}}{{6}}{{,}}{{1544198400000}}{{]
-
[}}{{15}}{{,}}{{'ws08'}}{{,}}{{1}}{{,}}{{132}}{{,}}{{6}}{{,}}{{1544281200000}}{{]}}
{{| SERVER AGGREGATE INTO SINGLE ROW }}
{{+---------------------------------------------------------------------+}}
{{2}}{{rows selected (}}{{0.685}}{{seconds)}}
{{ok,find it.look at code,which is the intersect method of the SkipScanFilter
class:}}
|{{else}}{{if}}{{(endCode == ReturnCode.SEEK_NEXT_USING_HINT) {}}
{{ }}{{// The upperExclusive key is smaller than the slots stored in the
position. Check if it's the same position}}
{{ }}{{// as the slots for lowerInclusive. If so, there is no intersection.}}
{{ }}{{if}}{{(Arrays.equals(lowerPosition, position) &&
areSlotsSingleKey(}}{{0}}{{, position.length-}}{{1}}{{)) {}}
{{ }}{{return}}{{false}}{{;}}
{{ }}{{}}}
{{}}}|
Assuming that the i-th scanRanges in the slots does not satisfy the upper and
lower bound constraints, the areSlotsSingleKey method only needs to determine
whether the corresponding position of the first i scanRanges is a SingleKey。
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
