Lei Chang created HAWQ-1255:
-------------------------------
Summary: Looks "segment size with penalty" number in "explain
analyze" not correct
Key: HAWQ-1255
URL: https://issues.apache.org/jira/browse/HAWQ-1255
Project: Apache HAWQ
Issue Type: Bug
Components: Query Execution
Reporter: Lei Chang
Assignee: Lei Chang
"segment size" is about 500MB, while "segment size with penalty" is about
100MB. Looks not reasonable.
How to reproduce:
on laptop, 1G tpch data, lineitem table is created as hash distributed with 2
buckets, and orders table is randomly.
```
postgres=# explain analyze SELECT l_orderkey, count(l_quantity)
FROM
lineitem_b2, orders
WHERE
l_orderkey = o_orderkey
GROUP BY
l_orderkey;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Gather Motion 2:1 (slice2; segments: 2) (cost=291580.96..318527.67
rows=1230576 width=16)
Rows out: Avg 1500000.0 rows x 1 workers at destination.
Max/Last(seg-1:changlei.local/seg-1:changlei.local) 1500000/1500000 rows with
2209/2209 ms to first row, 2577/2577 ms to end, start offset by 1.429/1.429 ms.
-> HashAggregate (cost=291580.96..318527.67 rows=615288 width=16)
Group By: lineitem_b2.l_orderkey
Rows out: Avg 750000.0 rows x 2 workers.
Max/Last(seg1:changlei.local/seg1:changlei.local) 750000/750000 rows with
2243/2243 ms to first row, 2498/2498 ms to end, start offset by 2.615/2.615 ms.
Executor memory: 56282K bytes avg, 56282K bytes max
(seg1:changlei.local).
-> Hash Join (cost=70069.00..250010.38 rows=3000608 width=15)
Hash Cond: lineitem_b2.l_orderkey = orders.o_orderkey
Rows out: Avg 3000607.5 rows x 2 workers.
Max/Last(seg0:changlei.local/seg1:changlei.local) 3001300/2999915 rows with
350/350 ms to first row, 1611/1645 ms to end, start offset by 3.819/3.816 ms.
Executor memory: 49153K bytes avg, 49153K bytes max
(seg1:changlei.local).
Work_mem used: 23438K bytes avg, 23438K bytes max
(seg1:changlei.local). Workfile: (0 spilling, 0 reused)
(seg0) Hash chain length 1.7 avg, 3 max, using 434205 of
524341 buckets.
-> Append-only Scan on lineitem_b2 (cost=0.00..89923.15
rows=3000608 width=15)
Rows out: Avg 3000607.5 rows x 2 workers.
Max/Last(seg0:changlei.local/seg1:changlei.local) 3001300/2999915 rows with
4.460/4.757 ms to first row, 546/581 ms to end, start offset by 350/349 ms.
-> Hash (cost=51319.00..51319.00 rows=750000 width=8)
Rows in: Avg 750000.0 rows x 2 workers.
Max/Last(seg1:changlei.local/seg0:changlei.local) 750000/750000 rows with
341/344 ms to end, start offset by 8.114/5.610 ms.
-> Redistribute Motion 2:2 (slice1; segments: 2)
(cost=0.00..51319.00 rows=750000 width=8)
Hash Key: orders.o_orderkey
Rows out: Avg 750000.0 rows x 2 workers at
destination. Max/Last(seg1:changlei.local/seg0:changlei.local) 750000/750000
rows with 0.052/2.461 ms to first row, 207/207 ms to end, start offset by
8.114/5.611 ms.
-> Append-only Scan on orders (cost=0.00..21319.00
rows=750000 width=8)
Rows out: Avg 750000.0 rows x 2 workers.
Max/Last(seg1:changlei.local/seg0:changlei.local) 750000/750000 rows with
4.773/4.987 ms to first row, 166/171 ms to end, start offset by 2.911/2.697 ms.
Slice statistics:
(slice0) Executor memory: 281K bytes.
(slice1) Executor memory: 319K bytes avg x 2 workers, 319K bytes max
(seg1:changlei.local).
(slice2) Executor memory: 105773K bytes avg x 2 workers, 105773K bytes
max (seg1:changlei.local). Work_mem: 23438K bytes max.
Statement statistics:
Memory used: 262144K bytes
Settings: default_hash_table_bucket_number=2
Dispatcher statistics:
executors used(total/cached/new connection): (4/4/0); dispatcher
time(total/connection/dispatch data): (0.171 ms/0.000 ms/0.048 ms).
dispatch data time(max/min/avg): (0.030 ms/0.004 ms/0.011 ms); consume
executor data time(max/min/avg): (0.014 ms/0.009 ms/0.011 ms); free executor
time(max/min/avg): (0.000 ms/0.000 ms/0.000 ms).
Data locality statistics:
data locality ratio: 1.000; virtual segment number: 2; different host
number: 1; virtual segment number per host(avg/min/max): (2/2/2); segment
size(avg/min/max): (593580028.000 B/593495232 B/593664824 B); segment size with
penalty(avg/min/max): (103526336.000 B/103517072 B/103535600 B);
continuity(avg/min/max): (1.000/1.000/1.000); DFS metadatacache: 0.094 ms;
resource allocation: 4.503 ms; datalocality calculation: 1.493 ms.
Total runtime: 2660.146 ms
(34 rows)
```
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
