1/1 EXPLAIN SELECT SUM(L_EXTENDEDPRICE) / 7.0 AS AVG_YEARLY
FROM
TPCH.LINEITEM,
TPCH.PART
WHERE
P_PARTKEY = L_PARTKEY
AND P_BRAND = 'BRAND#23'
AND P_CONTAINER = 'MED BOX'
AND L_QUANTITY < (
SELECT 0.2 * AVG(L_QUANTITY)
FROM
TPCH.LINEITEM
WHERE
L_PARTKEY = P_PARTKEY
)
;
+--------------------------------------------------------------------------------------------------------------+
| PLAN
|
+--------------------------------------------------------------------------------------------------------------+
| CLIENT 1422-CHUNK 1186588769 ROWS 436312507108 BYTES PARALLEL 1-WAY FULL SCAN
OVER TPCH.LINEITEM |
| SERVER AGGREGATE INTO SINGLE ROW
|
| PARALLEL INNER-JOIN TABLE 0
|
| CLIENT 28-CHUNK 19045177 ROWS 7549747697 BYTES PARALLEL 1-WAY ROUND
ROBIN FULL SCAN OVER TPCH.PART |
| SERVER FILTER BY (P_BRAND = 'BRAND#23' AND P_CONTAINER = 'MED
BOX') |
| PARALLEL INNER-JOIN TABLE 1(DELAYED EVALUATION)
|
| CLIENT 1422-CHUNK 1186588769 ROWS 436312507108 BYTES PARALLEL
1422-WAY FULL SCAN OVER TPCH.LINEITEM |
| SERVER AGGREGATE INTO DISTINCT ROWS BY [L_PARTKEY]
|
| CLIENT MERGE SORT
|
| AFTER-JOIN SERVER FILTER BY TPCH.LINEITEM.L_QUANTITY < $1.$3
|
+--------------------------------------------------------------------------------------------------------------+
> On Aug 25, 2016, at 14:23, Maryann Xue <[email protected]> wrote:
>
> Hi John,
>
> Would you mind sharing the query plan for this query (by running "EXPLAIN
> <query>")?
>
>
> Thanks,
> Maryann
>
> On Thu, Aug 25, 2016 at 11:19 AM, John Leach <[email protected]> wrote:
> Yeah, this query.
>
> QUERY:
> SELECT SUM(L_EXTENDEDPRICE) / 7.0 AS AVG_YEARLY
> FROM
> TPCH.LINEITEM,
> TPCH.PART
> WHERE
> P_PARTKEY = L_PARTKEY
> AND P_BRAND = 'BRAND#23'
> AND P_CONTAINER = 'MED BOX'
> AND L_QUANTITY < (
> SELECT 0.2 * AVG(L_QUANTITY)
> FROM
> TPCH.LINEITEM
> WHERE
> L_PARTKEY = P_PARTKEY
> );
>
> ROW COUNTS:
> TPCH.REGION 5
> TPCH.NATION 25
> TPCH.SUPPLIER 1000000
> TPCH.PART 20000000
> TPCH.PARTSUPP 80000000
> TPCH.ORDERS 150000000
> TPCH.CUSTOMER 15000000
> TPCH.LINEITEM 600037902
>
> Does the client have to sort all of LINEITEM and PART?
>
> Thanks James.
>
> Regards,
> John
>
>
>
>> On Aug 25, 2016, at 1:16 PM, James Taylor <[email protected]> wrote:
>>
>> The client typically does a merge sort when combining results from paralllel
>> scans. Not sure if this would explain the CPU/memory usage you're seeing.
>> Can you narrow it down to a particular query that's causing the issue?
>>
>> Thanks,
>> James
>>
>> On Thu, Aug 25, 2016 at 6:49 AM, John Leach <[email protected]> wrote:
>> Can anyone explain why the client would be burning so much CPU and memory if
>> the result is a single row?
>>
>> I suspect we configured something wrong on Phoenix but we are having a hard
>> time figuring it out.
>>
>> Thanks in advance.
>>
>> Regards,
>> John
>>
>> > On Aug 24, 2016, at 9:54 AM, Aaron Molitor <[email protected]>
>> > wrote:
>> >
>> > Seeing higher than expected CPU/MEM usage for the java process started by
>> > the sqlline.py client.
>> >
>> > From top:
>> > top - 14:37:32 up 7 days, 22:15, 2 users, load average: 25.52, 9.74, 7.89
>> > Tasks: 509 total, 1 running, 508 sleeping, 0 stopped, 0 zombie
>> > Cpu(s): 61.2%us, 6.3%sy, 0.0%ni, 31.0%id, 0.5%wa, 0.0%hi, 1.0%si,
>> > 0.0%st
>> > Mem: 65920564k total, 31913580k used, 34006984k free, 647004k buffers
>> > Swap: 33030140k total, 0k used, 33030140k free, 10464056k cached
>> >
>> > PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
>> > 8729 splice 20 0 29.9g 14g 889m S 1616.8 22.4 26:19.21 java
>> >
>> > Why is a the JDBC client using so much memory/cpu? The expected result is
>> > only a single row.
>> >
>> > QUERY:
>> > SELECT SUM(L_EXTENDEDPRICE) / 7.0 AS AVG_YEARLY
>> > FROM
>> > TPCH.LINEITEM,
>> > TPCH.PART
>> > WHERE
>> > P_PARTKEY = L_PARTKEY
>> > AND P_BRAND = 'BRAND#23'
>> > AND P_CONTAINER = 'MED BOX'
>> > AND L_QUANTITY < (
>> > SELECT 0.2 * AVG(L_QUANTITY)
>> > FROM
>> > TPCH.LINEITEM
>> > WHERE
>> > L_PARTKEY = P_PARTKEY
>> > );
>> >
>> > ROW COUNTS:
>> > TPCH.REGION 5
>> > TPCH.NATION 25
>> > TPCH.SUPPLIER 1000000
>> > TPCH.PART 20000000
>> > TPCH.PARTSUPP 80000000
>> > TPCH.ORDERS 150000000
>> > TPCH.CUSTOMER 15000000
>> > TPCH.LINEITEM 600037902
>> >
>> > Thanks,
>> > Aaron
>>
>>
>
>
