Return-Path: X-Original-To: archive-asf-public-internal@cust-asf2.ponee.io Delivered-To: archive-asf-public-internal@cust-asf2.ponee.io Received: from cust-asf.ponee.io (cust-asf.ponee.io [163.172.22.183]) by cust-asf2.ponee.io (Postfix) with ESMTP id AE15F200B82 for ; Fri, 16 Sep 2016 17:09:05 +0200 (CEST) Received: by cust-asf.ponee.io (Postfix) id AC91B160AC4; Fri, 16 Sep 2016 15:09:05 +0000 (UTC) Delivered-To: archive-asf-public@cust-asf.ponee.io Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by cust-asf.ponee.io (Postfix) with SMTP id AE75A160AB7 for ; Fri, 16 Sep 2016 17:09:03 +0200 (CEST) Received: (qmail 64915 invoked by uid 500); 16 Sep 2016 15:09:02 -0000 Mailing-List: contact dev-help@hawq.incubator.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: dev@hawq.incubator.apache.org Delivered-To: mailing list dev@hawq.incubator.apache.org Delivered-To: moderator for dev@hawq.incubator.apache.org Received: (qmail 26537 invoked by uid 99); 15 Sep 2016 10:32:25 -0000 X-Virus-Scanned: Debian amavisd-new at spamd2-us-west.apache.org X-Spam-Flag: NO X-Spam-Score: 2.662 X-Spam-Level: ** X-Spam-Status: No, score=2.662 tagged_above=-999 required=6.31 tests=[DKIM_SIGNED=0.1, DKIM_VALID=-0.1, DKIM_VALID_AU=-0.1, HTML_MESSAGE=2, MPART_ALT_DIFF_COUNT=1.483, RCVD_IN_DNSWL_LOW=-0.7, RCVD_IN_MSPIKE_H3=-0.01, RCVD_IN_MSPIKE_WL=-0.01, SPF_PASS=-0.001] autolearn=disabled Authentication-Results: spamd2-us-west.apache.org (amavisd-new); dkim=pass (1024-bit key) header.d=axiomatics.com DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=axiomatics.com; s=google; h=from:subject:message-id:date:to:mime-version; bh=ZzLdZUOi9mn7zVpdz3i8fYLtFaCHIkWW/LN6Nq3pvxA=; b=Cx1DJodeFlCNZ3iO1+NzAQDW2Jm9BSoudqx/x1OF1ebc7/PYNkxCXRhIB+D8nYkNqs areNssiaCl8XqgGvwocDK0RBt7wD3I6odh8jLOobTitDwDMCVKvYp0BZ40KACR9Yb150 E6HaG5pmTRAUvGWQfCA5NRt5XWvy7fyGcAi5o= X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20130820; h=x-gm-message-state:from:subject:message-id:date:to:mime-version; bh=ZzLdZUOi9mn7zVpdz3i8fYLtFaCHIkWW/LN6Nq3pvxA=; b=f/MQd93RD1wDUInQlSUl8lj1RuuFNjyslih+tJo5xL8b66q+7Hun7zsyOLMB5ve0U4 0RmGAywecAgGZJx/Sjf/Q2cAzA+c0Utsq+N8jRD/ZsIVCHJZVSjnuc3YoeOFwEDrDGB6 9InlAfNNDYfLDcTAydk3zW9Y1aC/M/uWfvg9s6kAfZNxHj/EG7oTuqohAMgwp15iXhQY cMWzIhEed7VcYFKVPTah6UzeZAGPbWDUehhuBqWY6HcfRzFAqiZr1vHuQuqk/EoA9e8Z Cfdlz24xqp/0FA8Xzr2rxksAFppH41yr+3XT1N79bjNMNh8X4GhgHfH1M/jW2QjSzfsp nOsw== X-Gm-Message-State: AE9vXwN5mHiEOFnoq7GDcmnxTOC0/uAurMgdIAKue/nU60vroNxr0e1zlDrn3xzZP2dSeg== X-Received: by 10.25.74.138 with SMTP id x132mr3417038lfa.154.1473935538488; Thu, 15 Sep 2016 03:32:18 -0700 (PDT) From: Saravana kumar Sankaramoorthy Content-Type: multipart/alternative; boundary="Apple-Mail=_A50CD217-5F6D-4F40-AE8B-176A326EDA68" Subject: GpOrca behaviour on TPC-H query 21 Message-Id: <2A5FF1BF-519A-4BB4-B4E2-7ED9296B5C71@axiomatics.com> Date: Thu, 15 Sep 2016 12:32:15 +0200 To: dev@hawq.incubator.apache.org Mime-Version: 1.0 (Mac OS X Mail 8.2 \(2104\)) X-Mailer: Apple Mail (2.2104) archived-at: Fri, 16 Sep 2016 15:09:05 -0000 --Apple-Mail=_A50CD217-5F6D-4F40-AE8B-176A326EDA68 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=us-ascii Hi, We are using HAWQ 2.0.0 in one of our product evaluation and we are = considerably new with the technology. The HAWQ uses gporca 1.627 to come = up with the execution plan. We tried analysing Query 21 of TPC-H = benchmarking downloaded from here = . We used TPC-H scale factor 1 and run it against a 5 node docker = cluster. In Query 21, the table lineitem is referred three times. We = expect gporca to apply the Common Subexpression elimination as mentioned = in this video = . But it = did not apply. We manually modified the query to use CTE and found that = it executes faster than the original one. I have attached both the = queries and the execution plan generated for them.=20 Why did gporca not apply the Common subexpression elimination? If it is because of the higher cost when using CTE, expanding the = definition inline will lead to the original query and cheaper cost. The = gporca should result in the original execution plan for the modified = query too. But it is not. I would like to understand why it is not = happening. I will be very glad if someone can clarify why gporca behaves like this. = I hope it is the correct forum to raise the question. If it is not, = please direct me where to raise the question. Thanks in advance. Best Regards, Saravana Technical Lead Axiomatics AB --Apple-Mail=_A50CD217-5F6D-4F40-AE8B-176A326EDA68 Content-Type: multipart/mixed; boundary="Apple-Mail=_1FC0A6AE-1726-494A-8FD3-1ADA9012683E" --Apple-Mail=_1FC0A6AE-1726-494A-8FD3-1ADA9012683E Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=us-ascii Hi,

  =  We are using HAWQ 2.0.0 in one of our product evaluation and we = are considerably new with the technology. The HAWQ uses gporca 1.627 to come up with = the execution plan. We tried analysing Query 21 of TPC-H benchmarking = downloaded from here. We used TPC-H scale factor 1 and run = it against a 5 node docker cluster. In Query 21, the table lineitem is referred three times. We expect = gporca to apply the Common = Subexpression elimination as mentioned in this video. But it did not apply. We manually = modified the query to use CTE and found that it executes faster than the = original one. I have attached both the queries and the execution plan = generated for them. 

  • Why did gporca not = apply the Common subexpression elimination?
  • If it is = because of the higher cost when using CTE, expanding the definition = inline will lead to the original query and cheaper cost. The gporca = should result in the original execution plan for the modified query too. = But it is not. I would like to understand why it is not = happening.

I will be very glad if someone can clarify why gporca behaves = like this. I hope it is the correct forum to raise the question. If it = is not, please direct me where to raise the question. Thanks in = advance.

Best = Regards,
Saravana
Technical = Lead
Axiomatics AB

=
= --Apple-Mail=_1FC0A6AE-1726-494A-8FD3-1ADA9012683E Content-Disposition: attachment; filename=modified_query.sql Content-Type: application/octet-stream; name="modified_query.sql" Content-Transfer-Encoding: 7bit with lineitem_aux as (select * from dbo.lineitem) select s_name, count(*) as numwait from dbo.supplier, lineitem_aux l1, dbo.orders, dbo.nation where s_suppkey = l1.l_suppkey and o_orderkey = l1.l_orderkey and o_orderstatus = 'F' and l1.l_receiptdate > l1.l_commitdate and exists ( select * from lineitem_aux l2 where l2.l_orderkey = l1.l_orderkey and l2.l_suppkey <> l1.l_suppkey ) and not exists ( select * from lineitem_aux l3 where l3.l_orderkey = l1.l_orderkey and l3.l_suppkey <> l1.l_suppkey and l3.l_receiptdate > l3.l_commitdate ) and s_nationkey = n_nationkey and n_name = 'SAUDI ARABIA' group by s_name order by numwait desc, s_name; --Apple-Mail=_1FC0A6AE-1726-494A-8FD3-1ADA9012683E Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=us-ascii
=
= --Apple-Mail=_1FC0A6AE-1726-494A-8FD3-1ADA9012683E Content-Disposition: attachment; filename=original_query.sql Content-Type: application/octet-stream; name="original_query.sql" Content-Transfer-Encoding: 7bit select s_name, count(*) as numwait from dbo.supplier, dbo.lineitem l1, dbo.orders, dbo.nation where s_suppkey = l1.l_suppkey and o_orderkey = l1.l_orderkey and o_orderstatus = 'F' and l1.l_receiptdate > l1.l_commitdate and exists ( select * from dbo.lineitem l2 where l2.l_orderkey = l1.l_orderkey and l2.l_suppkey <> l1.l_suppkey ) and not exists ( select * from dbo.lineitem l3 where l3.l_orderkey = l1.l_orderkey and l3.l_suppkey <> l1.l_suppkey and l3.l_receiptdate > l3.l_commitdate ) and s_nationkey = n_nationkey and n_name = 'SAUDI ARABIA' group by s_name order by numwait desc, s_name; --Apple-Mail=_1FC0A6AE-1726-494A-8FD3-1ADA9012683E Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=us-ascii
=
= --Apple-Mail=_1FC0A6AE-1726-494A-8FD3-1ADA9012683E Content-Disposition: attachment; filename=modified_query_exec_plan.txt Content-Type: text/plain; name="modified_query_exec_plan.txt" Content-Transfer-Encoding: quoted-printable Gather Motion 18:1 (slice6; segments: 18) (cost=3D0.00..3421.09 = rows=3D400 width=3D37) Merge Key: numwait, supplier.s_name Rows out: Avg 411.0 rows x 1 workers at destination. = Max/Last(seg-1:hawq/seg-1:hawq) 411/411 rows with 8116/8116 ms to first = row, 8117/8117 ms to end, start offset by 911/911 ms. -> Sort (cost=3D0.00..3421.05 rows=3D23 width=3D37) Sort Key: numwait, supplier.s_name Rows out: Avg 22.8 rows x 18 workers. = Max/Last(seg13:slave3/seg2:slave1) 31/12 rows with 7744/8035 ms to end, = start offset by 1273/985 ms. Executor memory: 58K bytes avg, 58K bytes max (seg17:slave3). Work_mem used: 58K bytes avg, 58K bytes max (seg17:slave3). = Workfile: (0 spilling, 0 reused) -> Sequence (cost=3D0.00..3421.03 rows=3D23 width=3D37) Rows out: Avg 22.8 rows x 18 workers. = Max/Last(seg13:slave3/seg2:slave1) 31/12 rows with 7724/8028 ms to first = row, 7730/8034 ms to end, start offset by 1286/986 ms. -> Shared Scan (share slice:id 6:0) (cost=3D0.00..534.34 = rows=3D333401 width=3D1) Rows out: Avg 333400.8 rows x 18 workers. = Max/Last(seg8:slave2/seg2:slave1) 335120/333470 rows with 4202/6574 ms = to first row, 4471/6795 ms to end, start offset by 1316/986 ms. -> Materialize (cost=3D0.00..534.34 rows=3D333401 = width=3D1) Rows out: Avg 0.0 rows x 0 workers. = Max/Last(seg17:slave3/seg2:slave1) 0/0 rows with 6574/6574 ms to end. Work_mem used: 14976K bytes avg, 14976K bytes = max (seg17:slave3). Workfile: (0 spilling, 0 reused) Work_mem wanted: 51343K bytes avg, 51616K = bytes max (seg8:slave3) to lessen workfile I/O affecting 18 workers. -> Table Scan on lineitem (cost=3D0.00..456.49= rows=3D333401 width=3D125) Rows out: Avg 333400.8 rows x 18 = workers. Max/Last(seg8:slave2/seg5:slave1) 335120/333720 rows with = 1231/1795 ms to first row, 2463/3870 ms to end, start offset by = 1340/1244 ms. -> HashAggregate (cost=3D0.00..2886.70 rows=3D23 = width=3D37) Group By: supplier.s_name Rows out: Avg 22.8 rows x 18 workers. = Max/Last(seg13:slave3/seg17:slave3) 31/16 rows with 3886/4480 ms to = first row, 3892/4482 ms to end, start offset by 5125/4542 ms. Executor memory: 4185K bytes avg, 4185K bytes max = (seg17:slave3). -> Redistribute Motion 18:18 (slice5; segments: = 18) (cost=3D0.00..2886.69 rows=3D23 width=3D37) Hash Key: supplier.s_name Rows out: Avg 176.3 rows x 18 workers at = destination. Max/Last(seg13:slave3/seg17:slave3) 263/119 rows with = 3761/4367 ms to first row, 3845/4461 ms to end, start offset by = 5161/4559 ms. -> Result (cost=3D0.00..2886.69 rows=3D23 = width=3D37) Rows out: Avg 176.3 rows x 18 workers. = Max/Last(seg11:slave2/seg1:slave1) 188/182 rows with 7669/7961 ms to = first row, 7673/7964 ms to end, start offset by 1262/987 ms. -> HashAggregate (cost=3D0.00..2886.69 = rows=3D23 width=3D37) Group By: supplier.s_name Rows out: Avg 176.3 rows x 18 = workers. Max/Last(seg11:slave2/seg1:slave1) 188/182 rows with 7669/7961 = ms to first row, 7673/7964 ms to end, start offset by 1262/987 ms. Executor memory: 4185K bytes avg, = 4185K bytes max (seg17:slave2). -> Hash Join (cost=3D0.00..2886.65= rows=3D319 width=3D29) Hash Cond: orders.o_orderkey = =3D "inner".l_orderkey::bigint AND orders.o_orderkey =3D = "inner".l_orderkey::bigint Rows out: Avg 230.1 rows x = 18 workers. Max/Last(seg14:slave3/seg1:slave1) 257/223 rows with = 7520/7748 ms to first row, 7638/7847 ms to end, start offset by = 1336/1100 ms. Executor memory: 2113K = bytes avg, 2113K bytes max (seg17:slave3). Work_mem used: 26K bytes = avg, 28K bytes max (seg9:slave3). Workfile: (0 spilling, 0 reused) (seg9) Hash chain length = 1.0 avg, 2 max, using 497 of 131111 buckets. (seg14) Hash chain length = 1.0 avg, 2 max, using 482 of 131111 buckets. -> Redistribute Motion = 18:18 (slice1; segments: 18) (cost=3D0.00..441.52 rows=3D40736 = width=3D8) Hash Key: = orders.o_orderkey Rows out: Avg 40522.9 = rows x 18 workers at destination. Max/Last(seg5:slave1/seg5:slave1) = 40736/40736 rows with 0.216/0.216 ms to first row, 106/106 ms to end, = start offset by 8848/8848 ms. -> Table Scan on = orders (cost=3D0.00..440.50 rows=3D40736 width=3D8) Filter: = o_orderstatus =3D 'F'::bpchar Rows out: Avg = 40522.9 rows x 18 workers. Max/Last(seg13:slave3/seg8:slave2) = 40795/40413 rows with 819/1789 ms to first row, 1183/2134 ms to end, = start offset by 1186/1338 ms. -> Hash = (cost=3D2437.39..2437.39 rows=3D499 width=3D33) Rows in: Avg 464.3 = rows x 18 workers. Max/Last(seg9:slave2/seg1:slave1) 498/443 rows with = 7350/7722 ms to end, start offset by 1498/1126 ms. -> Redistribute = Motion 18:18 (slice4; segments: 18) (cost=3D0.00..2437.39 rows=3D499 = width=3D33) Hash Key: = l_orderkey::bigint Rows out: Avg = 464.3 rows x 18 workers at destination. = Max/Last(seg9:slave2/seg1:slave1) 498/443 rows with 7085/6955 ms to = first row, 7350/7721 ms to end, start offset by 1498/1126 ms. -> Hash EXISTS = Join (cost=3D0.00..2437.34 rows=3D499 width=3D33) Hash Cond: = "outer".l_orderkey =3D "inner".l_orderkey Join = Filter: "inner".l_suppkey <> "outer".l_suppkey Rows out: = Avg 464.3 rows x 18 workers. Max/Last(seg9:slave2/seg3:slave1) 498/475 = rows with 856/2469 ms to first row, 1226/3294 ms to end, start offset by = 7531/4889 ms. Executor = memory: 69633K bytes avg, 69633K bytes max (seg17:slave2). Work_mem = used: 39975K bytes avg, 39975K bytes max (seg13:slave2). Workfile: (18 = spilling, 0 reused) Work_mem = wanted: 55078K bytes avg, 55358K bytes max (seg8:slave2) to lessen = workfile I/O affecting 18 workers. (seg8) = Initial batch 0: (seg8) = Wrote 25776K bytes to inner workfile. (seg8) = Wrote 16K bytes to outer workfile. (seg8) = Overflow batch 1: (seg8) = Read 51555K bytes from inner workfile. (seg8) = Read 34K bytes from outer workfile. (seg8) = Hash chain length 4.2 avg, 20 max, using 78935 of 524302 buckets. (seg9) = Initial batch 0: (seg9) = Wrote 25488K bytes to inner workfile. (seg9) = Wrote 16K bytes to outer workfile. (seg9) = Overflow batch 1: (seg9) = Read 50978K bytes from inner workfile. (seg9) = Read 37K bytes from outer workfile. (seg9) = Hash chain length 4.2 avg, 20 max, using 78620 of 524302 buckets. -> Hash = Left Anti Semi Join (cost=3D0.00..1871.84 rows=3D2130 width=3D37) Hash = Cond: "outer".l_orderkey =3D "inner".l_orderkey Join = Filter: "inner".l_suppkey <> "outer".l_suppkey Rows = out: Avg 769.9 rows x 18 workers. Max/Last(seg9:slave2/seg1:slave1) = 817/767 rows with 327/1125 ms to first row, 587/1960 ms to end, start = offset by 8057/6213 ms. = Executor memory: 20481K bytes avg, 20481K bytes max (seg17:slave2). = Work_mem used: 6586K bytes avg, 6613K bytes max (seg6:slave2). = Workfile: (0 spilling, 0 reused) = (seg9) Hash chain length 3.1 avg, 16 max, using 68633 of 262151 = buckets. -> = Hash Join (cost=3D0.00..1352.91 rows=3D5324 width=3D37) = Hash Cond: "outer".l_suppkey =3D supplier.s_suppkey = Rows out: Avg 8707.7 rows x 18 workers. = Max/Last(seg10:slave2/seg17:slave3) 8855/8748 rows with 1.391/35 ms to = first row, 250/1049 ms to end, start offset by 8000/5366 ms. = Executor memory: 1089K bytes avg, 1089K bytes max (seg17:slave2). = Work_mem used: 23K bytes avg, 23K bytes max (seg17:slave2). Workfile: = (0 spilling, 0 reused) = (seg10) Hash chain length 1.0 avg, 1 max, using 411 of 65539 buckets. = -> Result (cost=3D0.00..465.79 rows=3D133361 width=3D8) = Filter: l_receiptdate > l_commitdate = Rows out: Avg 210738.7 rows x 18 workers. = Max/Last(seg6:slave2/seg17:slave3) 211597/210433 rows with 0.204/6.360 = ms to first row, 387/788 ms to end, start offset by 6557/5395 ms. = -> Shared Scan (share slice:id 4:0) (cost=3D0.00..443.86 = rows=3D333401 width=3D16) = Rows out: Avg 333400.8 rows x 18 workers. = Max/Last(seg8:slave2/seg17:slave3) 335120/332409 rows with 0.340/6.347 = ms to first row, 218/325 ms to end, start offset by 6918/5395 ms. = -> Hash (cost=3D862.26..862.26 rows=3D400 width=3D33) = Rows in: Avg 411.0 rows x 18 workers. = Max/Last(seg17:slave3/seg7:slave2) 411/411 rows with 1.038/1.073 ms to = end, start offset by 5394/6708 ms. = -> Broadcast Motion 18:18 (slice3; segments: 18) = (cost=3D0.00..862.26 rows=3D400 width=3D33) = Rows out: Avg 411.0 rows x 18 workers at destination. = Max/Last(seg17:slave3/seg17:slave3) 411/411 rows with 0.173/0.173 ms to = first row, 0.576/0.576 ms to end, start offset by 5394/5394 ms. = -> Hash Join (cost=3D0.00..862.20 rows=3D23 width=3D33) = Hash Cond: supplier.s_nationkey =3D = nation.n_nationkey = Rows out: Avg 22.8 rows x 18 workers. = Max/Last(seg5:slave1/seg17:slave3) 31/23 rows with 2546/2701 ms to first = row, 2547/2702 ms to end, start offset by 1163/1126 ms. = Executor memory: 2065K bytes avg, 2065K bytes max = (seg17:slave1). = Work_mem used: 1K bytes avg, 1K bytes max = (seg17:slave1). Workfile: (0 spilling, 0 reused) = (seg5) Hash chain length 1.0 avg, 1 max, using 1 of = 131111 buckets. = -> Table Scan on supplier (cost=3D0.00..431.05 = rows=3D556 width=3D37) = Rows out: Avg 555.6 rows x 18 workers. = Max/Last(seg3:slave1/seg3:slave1) 592/592 rows with 934/934 ms to first = row, 935/935 ms to end, start offset by 2937/2937 ms. = -> Hash (cost=3D431.00..431.00 rows=3D1 width=3D4) = Rows in: Avg 1.0 rows x 18 workers. = Max/Last(seg17:slave3/seg10:slave2) 1/1 rows with 1793/1953 ms to end, = start offset by 1155/1035 ms. = -> Broadcast Motion 18:18 (slice2; segments: = 18) (cost=3D0.00..431.00 rows=3D1 width=3D4) = Rows out: Avg 1.0 rows x 18 workers at = destination. Max/Last(seg17:slave3/seg10:slave2) 1/1 rows with = 1338/1457 ms to first row, 1793/1953 ms to end, start offset by = 1155/1035 ms. = -> Table Scan on nation = (cost=3D0.00..431.00 rows=3D1 width=3D4) = Filter: n_name =3D 'SAUDI = ARABIA'::bpchar = Rows out: Avg 1.0 rows x 1 = workers. Max/Last(seg13:slave3/seg9:slave2) 1/0 rows with 1157/1584 ms = to end, start offset by 1333/1352 ms. -> = Hash (cost=3D465.79..465.79 rows=3D133361 width=3D8) = Rows in: Avg 210738.7 rows x 18 workers. = Max/Last(seg6:slave2/seg3:slave1) 211597/210425 rows with 759/1107 ms to = end, start offset by 5797/6219 ms. = -> Result (cost=3D0.00..465.79 rows=3D133361 width=3D8) = Filter: l_receiptdate > l_commitdate = Rows out: Avg 210738.7 rows x 18 workers. = Max/Last(seg6:slave2/seg1:slave1) 211597/210178 rows with 0.565/0.279 ms = to first row, 487/639 ms to end, start offset by 5797/6231 ms. = -> Shared Scan (share slice:id 4:0) (cost=3D0.00..443.86 = rows=3D333401 width=3D16) = Rows out: Avg 333400.8 rows x 18 workers. = Max/Last(seg8:slave2/seg1:slave1) 335120/332862 rows with 0.253/0.258 ms = to first row, 140/300 ms to end, start offset by 6319/6231 ms. -> Hash = (cost=3D437.43..437.43 rows=3D333401 width=3D8) Rows = in: Avg 166795.2 rows x 18 workers. Max/Last(seg7:slave2/seg10:slave2) = 168209/167397 rows with 695/1813 ms to end, start offset by 5634/5469 = ms. -> = Shared Scan (share slice:id 4:0) (cost=3D0.00..437.43 rows=3D333401 = width=3D8) = Rows out: Avg 333400.8 rows x 18 workers. = Max/Last(seg8:slave2/seg10:slave2) 335120/334310 rows with 13/0.485 ms = to first row, 132/366 ms to end, start offset by 5529/5469 ms. Slice statistics: (slice0) Executor memory: 766K bytes. (slice1) Executor memory: 2265K bytes avg x 18 workers, 2267K bytes = max (seg7:slave2). (slice2) Executor memory: 686K bytes avg x 18 workers, 769K bytes = max (seg13:slave3). (slice3) Executor memory: 2873K bytes avg x 18 workers, 2874K bytes = max (seg3:slave1). Work_mem: 1K bytes max. (slice4) * Executor memory: 92006K bytes avg x 18 workers, 92006K = bytes max (seg17:slave3). Work_mem: 39975K bytes max, 55358K bytes = wanted. (slice5) Executor memory: 6938K bytes avg x 18 workers, 6938K bytes = max (seg17:slave3). Work_mem: 28K bytes max. (slice6) * Executor memory: 12412K bytes avg x 18 workers, 12415K = bytes max (seg0:slave1). Work_mem: 14976K bytes max, 51616K bytes = wanted. Statement statistics: Memory used: 262144K bytes Memory wanted: 743137K bytes Settings: default_hash_table_bucket_number=3D18 Optimizer status: PQO version 1.627 Dispatcher statistics: executors used(total/cached/new connection): (108/0/108); dispatcher = time(total/connection/dispatch data): (935.780 ms/902.604 ms/31.910 ms). dispatch data time(max/min/avg): (6.210 ms/0.045 ms/0.243 ms); consume = executor data time(max/min/avg): (0.066 ms/0.008 ms/0.028 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: 18; different host = number: 3; virtual segment number per host(avg/min/max): (6/6/6); = segment size(avg/min/max): (21012431.667 B/20917791 B/21111680 B); = segment size with penalty(avg/min/max): (0.000 B/0 B/0 B); = continuity(avg/min/max): (1.000/1.000/1.000); DFS metadatacache: 2.763 = ms; resource allocation: 3.692 ms; datalocality calculation: 0.888 ms. Total runtime: 6824.472 ms= --Apple-Mail=_1FC0A6AE-1726-494A-8FD3-1ADA9012683E Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=us-ascii
=
= --Apple-Mail=_1FC0A6AE-1726-494A-8FD3-1ADA9012683E Content-Disposition: attachment; filename=original_query_exec_plan.txt Content-Type: text/plain; name="original_query_exec_plan.txt" Content-Transfer-Encoding: quoted-printable Gather Motion 18:1 (slice6; segments: 18) (cost=3D0.00..2948.02 = rows=3D10000 width=3D37) Merge Key: numwait, supplier.s_name Rows out: Avg 411.0 rows x 1 workers at destination. = Max/Last(seg-1:hawq/seg-1:hawq) 411/411 rows with 8637/8637 ms to end, = start offset by 2.383/2.383 ms. -> Sort (cost=3D0.00..2947.11 rows=3D556 width=3D37) Sort Key: numwait, supplier.s_name Rows out: Avg 22.8 rows x 18 workers. = Max/Last(seg13:slave3/seg12:slave3) 31/21 rows with 8568/8628 ms to end, = start offset by 68/10 ms. Executor memory: 58K bytes avg, 58K bytes max (seg17:slave3). Work_mem used: 58K bytes avg, 58K bytes max (seg17:slave3). = Workfile: (0 spilling, 0 reused) -> HashAggregate (cost=3D0.00..2946.05 rows=3D556 width=3D37) Group By: supplier.s_name Rows out: Avg 22.8 rows x 18 workers. = Max/Last(seg13:slave3/seg12:slave3) 31/21 rows with 8560/8614 ms to = first row, 8568/8628 ms to end, start offset by 68/11 ms. Executor memory: 4185K bytes avg, 4185K bytes max = (seg17:slave3). -> Redistribute Motion 18:18 (slice5; segments: 18) = (cost=3D0.00..2945.97 rows=3D556 width=3D37) Hash Key: supplier.s_name Rows out: Avg 176.3 rows x 18 workers at = destination. Max/Last(seg13:slave3/seg12:slave3) 263/172 rows with = 8453/8523 ms to first row, 8538/8607 ms to end, start offset by 88/15 = ms. -> Result (cost=3D0.00..2945.91 rows=3D556 = width=3D37) Rows out: Avg 176.3 rows x 18 workers. = Max/Last(seg11:slave2/seg9:slave2) 188/185 rows with 8377/8576 ms to = first row, 8378/8577 ms to end, start offset by 234/42 ms. -> HashAggregate (cost=3D0.00..2945.91 = rows=3D556 width=3D37) Group By: supplier.s_name Rows out: Avg 176.3 rows x 18 workers. = Max/Last(seg11:slave2/seg9:slave2) 188/185 rows with 8377/8576 ms to = first row, 8378/8577 ms to end, start offset by 234/42 ms. Executor memory: 4185K bytes avg, 4185K = bytes max (seg17:slave2). -> Hash Join (cost=3D0.00..2945.72 = rows=3D1360 width=3D29) Hash Cond: orders.o_orderkey =3D = dbo.lineitem.l_orderkey::bigint AND orders.o_orderkey =3D = dbo.lineitem.l_orderkey::bigint Rows out: Avg 230.1 rows x 18 = workers. Max/Last(seg14:slave3/seg9:slave2) 257/237 rows with 8328/8453 = ms to first row, 8415/8571 ms to end, start offset by 172/46 ms. Executor memory: 2113K bytes avg, = 2113K bytes max (seg17:slave3). Work_mem used: 26K bytes avg, 28K = bytes max (seg9:slave3). Workfile: (0 spilling, 0 reused) (seg9) Hash chain length 1.0 = avg, 2 max, using 497 of 131111 buckets. (seg14) Hash chain length 1.0 = avg, 2 max, using 482 of 131111 buckets. -> Redistribute Motion 18:18 = (slice1; segments: 18) (cost=3D0.00..441.52 rows=3D40736 width=3D8) Hash Key: orders.o_orderkey Rows out: Avg 40522.9 rows = x 18 workers at destination. Max/Last(seg5:slave1/seg6:slave2) = 40736/40678 rows with 0.166/0.168 ms to first row, 64/77 ms to end, = start offset by 8504/8507 ms. -> Table Scan on orders = (cost=3D0.00..440.50 rows=3D40736 width=3D8) Filter: o_orderstatus = =3D 'F'::bpchar Rows out: Avg 40522.9 = rows x 18 workers. Max/Last(seg13:slave3/seg9:slave2) 40795/40696 rows = with 1022/1439 ms to first row, 1361/1850 ms to end, start offset by = 68/46 ms. -> Hash (cost=3D2494.97..2494.97 = rows=3D2130 width=3D33) Rows in: Avg 464.3 rows x = 18 workers. Max/Last(seg9:slave2/seg10:slave2) 498/497 rows with = 8451/8454 ms to end, start offset by 49/56 ms. -> Redistribute Motion = 18:18 (slice4; segments: 18) (cost=3D0.00..2494.97 rows=3D2130 = width=3D33) Hash Key: = dbo.lineitem.l_orderkey::bigint Rows out: Avg 464.3 = rows x 18 workers at destination. Max/Last(seg9:slave2/seg10:slave2) = 498/497 rows with 6876/7289 ms to first row, 8450/8454 ms to end, start = offset by 49/56 ms. -> Hash EXISTS Join = (cost=3D0.00..2494.75 rows=3D2130 width=3D33) Hash Cond: = dbo.lineitem.l_orderkey =3D dbo.lineitem.l_orderkey Join Filter: = dbo.lineitem.l_suppkey <> dbo.lineitem.l_suppkey Rows out: Avg = 464.3 rows x 18 workers. Max/Last(seg9:slave2/seg12:slave3) 498/456 = rows with 6012/6758 ms to first row, 8129/8316 ms to end, start offset = by 145/113 ms. Executor memory: = 20481K bytes avg, 20481K bytes max (seg17:slave2). Work_mem used: = 10419K bytes avg, 10473K bytes max (seg8:slave2). Workfile: (0 spilling, = 0 reused) (seg9) Hash = chain length 4.5 avg, 20 max, using 74114 of 262151 buckets. -> Hash Left = Anti Semi Join (cost=3D0.00..1905.04 rows=3D2130 width=3D37) Hash Cond: = dbo.lineitem.l_orderkey =3D dbo.lineitem.l_orderkey Join = Filter: dbo.lineitem.l_suppkey <> dbo.lineitem.l_suppkey Rows out: = Avg 769.9 rows x 18 workers. Max/Last(seg9:slave2/seg8:slave2) 817/769 = rows with 3122/3052 ms to first row, 5198/5352 ms to end, start offset = by 3032/2951 ms. Executor = memory: 20481K bytes avg, 20481K bytes max (seg17:slave2). Work_mem = used: 6586K bytes avg, 6613K bytes max (seg6:slave2). Workfile: (0 = spilling, 0 reused) (seg9) = Hash chain length 3.1 avg, 16 max, using 68633 of 262151 buckets. -> Hash = Join (cost=3D0.00..1369.51 rows=3D5324 width=3D37) Hash = Cond: dbo.lineitem.l_suppkey =3D supplier.s_suppkey Rows = out: Avg 8707.7 rows x 18 workers. Max/Last(seg10:slave2/seg4:slave1) = 8855/8666 rows with 479/475 ms to first row, 2303/2631 ms to end, start = offset by 6086/5659 ms. = Executor memory: 2113K bytes avg, 2113K bytes max (seg17:slave2). = Work_mem used: 23K bytes avg, 23K bytes max (seg17:slave2). Workfile: = (0 spilling, 0 reused) = (seg10) Hash chain length 1.0 avg, 1 max, using 411 of 131111 buckets. -> = Table Scan on lineitem (cost=3D0.00..482.40 rows=3D133361 width=3D8) = Filter: l_receiptdate > l_commitdate = Rows out: Avg 210738.7 rows x 18 workers. = Max/Last(seg6:slave2/seg2:slave1) 211597/211087 rows with 334/586 ms to = first row, 2235/2538 ms to end, start offset by 5826/5605 ms. -> = Hash (cost=3D862.26..862.26 rows=3D400 width=3D33) = Rows in: Avg 411.0 rows x 18 workers. = Max/Last(seg17:slave3/seg8:slave2) 411/411 rows with 0.373/0.420 ms to = end, start offset by 5451/5682 ms. = -> Broadcast Motion 18:18 (slice3; segments: 18) (cost=3D0.00..862.26 = rows=3D400 width=3D33) = Rows out: Avg 411.0 rows x 18 workers at destination. = Max/Last(seg17:slave3/seg2:slave1) 411/411 rows with 0.027/0.023 ms to = first row, 0.254/0.299 ms to end, start offset by 5451/5604 ms. = -> Hash Join (cost=3D0.00..862.20 rows=3D23 width=3D33) = Hash Cond: supplier.s_nationkey =3D nation.n_nationkey = Rows out: Avg 22.8 rows x 18 workers. = Max/Last(seg5:slave1/seg7:slave2) 31/24 rows with 1753/2117 ms to first = row, 1754/2119 ms to end, start offset by 246/21 ms. = Executor memory: 4113K bytes avg, 4113K bytes max = (seg17:slave1). = Work_mem used: 1K bytes avg, 1K bytes max (seg17:slave1). = Workfile: (0 spilling, 0 reused) = (seg5) Hash chain length 1.0 avg, 1 max, using 1 of = 262151 buckets. = -> Table Scan on supplier (cost=3D0.00..431.05 rows=3D556 = width=3D37) = Rows out: Avg 555.6 rows x 18 workers. = Max/Last(seg3:slave1/seg13:slave3) 592/587 rows with 560/857 ms to first = row, 561/858 ms to end, start offset by 1292/1285 ms. = -> Hash (cost=3D431.00..431.00 rows=3D1 width=3D4) = Rows in: Avg 1.0 rows x 18 workers. = Max/Last(seg17:slave3/seg12:slave3) 1/1 rows with 1095/1262 ms to end, = start offset by 193/24 ms. = -> Broadcast Motion 18:18 (slice2; segments: 18) = (cost=3D0.00..431.00 rows=3D1 width=3D4) = Rows out: Avg 1.0 rows x 18 workers at = destination. Max/Last(seg17:slave3/seg12:slave3) 1/1 rows with = 1095/1262 ms to end, start offset by 193/24 ms. = -> Table Scan on nation (cost=3D0.00..431.00 = rows=3D1 width=3D4) = Filter: n_name =3D 'SAUDI ARABIA'::bpchar = Rows out: Avg 1.0 rows x 1 workers. = Max/Last(seg13:slave3/seg13:slave3) 1/1 rows with 1218/1218 ms to end, = start offset by 66/66 ms. -> Hash = (cost=3D482.40..482.40 rows=3D133361 width=3D8) Rows = in: Avg 210738.7 rows x 18 workers. Max/Last(seg6:slave2/seg12:slave3) = 211597/209751 rows with 2747/3194 ms to end, start offset by 3077/3171 = ms. -> = Table Scan on lineitem (cost=3D0.00..482.40 rows=3D133361 width=3D8) = Filter: l_receiptdate > l_commitdate = Rows out: Avg 210738.7 rows x 18 workers. = Max/Last(seg6:slave2/seg12:slave3) 211597/209751 rows with 375/516 ms to = first row, 2504/2885 ms to end, start offset by 3077/3171 ms. -> Hash = (cost=3D456.49..456.49 rows=3D333401 width=3D8) Rows in: = Avg 333400.8 rows x 18 workers. Max/Last(seg8:slave2/seg3:slave1) = 335120/333946 rows with 2858/3426 ms to end, start offset by 93/269 ms. -> Table = Scan on lineitem (cost=3D0.00..456.49 rows=3D333401 width=3D8) Rows = out: Avg 333400.8 rows x 18 workers. Max/Last(seg8:slave2/seg5:slave1) = 335120/333720 rows with 1022/1346 ms to first row, 2442/3039 ms to end, = start offset by 93/353 ms. Slice statistics: (slice0) Executor memory: 759K bytes. (slice1) Executor memory: 2009K bytes avg x 18 workers, 2011K bytes = max (seg7:slave2). (slice2) Executor memory: 523K bytes avg x 18 workers, 523K bytes = max (seg17:slave3). (slice3) Executor memory: 4664K bytes avg x 18 workers, 4666K bytes = max (seg3:slave1). Work_mem: 1K bytes max. (slice4) Executor memory: 45288K bytes avg x 18 workers, 45289K = bytes max (seg0:slave1). Work_mem: 10473K bytes max. (slice5) Executor memory: 6938K bytes avg x 18 workers, 6938K bytes = max (seg17:slave3). Work_mem: 28K bytes max. (slice6) Executor memory: 4599K bytes avg x 18 workers, 4599K bytes = max (seg17:slave3). Work_mem: 58K bytes max. Statement statistics: Memory used: 262144K bytes Settings: default_hash_table_bucket_number=3D18 Optimizer status: PQO version 1.627 Dispatcher statistics: executors used(total/cached/new connection): (108/108/0); dispatcher = time(total/connection/dispatch data): (2.685 ms/0.000 ms/1.983 ms). dispatch data time(max/min/avg): (0.103 ms/0.015 ms/0.026 ms); consume = executor data time(max/min/avg): (0.041 ms/0.008 ms/0.022 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: 18; different host = number: 3; virtual segment number per host(avg/min/max): (6/6/6); = segment size(avg/min/max): (21012431.667 B/20917791 B/21111680 B); = segment size with penalty(avg/min/max): (0.000 B/0 B/0 B); = continuity(avg/min/max): (1.000/1.000/1.000); DFS metadatacache: 0.713 = ms; resource allocation: 0.796 ms; datalocality calculation: 0.246 ms. Total runtime: 8641.214 ms= --Apple-Mail=_1FC0A6AE-1726-494A-8FD3-1ADA9012683E Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=us-ascii
=
= --Apple-Mail=_1FC0A6AE-1726-494A-8FD3-1ADA9012683E-- --Apple-Mail=_A50CD217-5F6D-4F40-AE8B-176A326EDA68--