[
https://issues.apache.org/jira/browse/PHOENIX-5242?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
chuxiao reopened PHOENIX-5242:
------------------------------
> Physical execution plan issue and optimization
> ----------------------------------------------
>
> Key: PHOENIX-5242
> URL: https://issues.apache.org/jira/browse/PHOENIX-5242
> Project: Phoenix
> Issue Type: Bug
> Affects Versions: 4.11.0, 5.0.0
> Reporter: chuxiao
> Priority: Major
> Attachments: PHOENIX-5242.patch
>
>
> 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
(v8.3.4#803005)
