public inbox for [email protected]
help / color / mirror / Atom feedFrom: Matheus Alcantara <[email protected]>
To: Richard Guo <[email protected]>
To: Robert Haas <[email protected]>
Cc: Tom Lane <[email protected]>
Cc: Tender Wang <[email protected]>
Cc: Paul George <[email protected]>
Cc: Andy Fan <[email protected]>
Cc: PostgreSQL-development <[email protected]>
Cc: [email protected]
Cc: Matheus Alcantara <[email protected]>
Subject: Re: Eager aggregation, take 3
Date: Wed, 1 Oct 2025 20:54:32 -0300
Message-ID: <CAFY6G8f0gY1PVQaBFA=8eAL=guZsOV-8YuWGnA2BtuS=ySBRbw@mail.gmail.com> (raw)
In-Reply-To: <CAMbWs48W80HFm9b+yZPKER=MA5M_bveYvBx1AwOrxdPYbLmYmQ@mail.gmail.com>
References: <CAMbWs48jzLrPt1J_00ZcPZXWUQKawQOFE8ROc-ADiYqsqrpBNw@mail.gmail.com>
<[email protected]>
<CAMbWs49=eAd2W9jCtGhaZPPp+SOC_2rg16RTG74xAht=hkr5JQ@mail.gmail.com>
<CAMbWs49Nc4M3H+eCf1+8w8piDyEECjRb-gK_JMF4VvcyWwGEVQ@mail.gmail.com>
<CAMbWs49E_dR0nobsExsyetpnBpHObLTsQLsEbWKQLkh0omPxNg@mail.gmail.com>
<CAMbWs49B_qUiHvu2EqLHZRpLr3p_+QPBs50n2=L5ibYzniwTzA@mail.gmail.com>
<CAMbWs48KCQtDymnYi4M=Vz+WMzo3fkBxffJsyk6VX6hOXXv+VA@mail.gmail.com>
<CAMbWs49sv_MuOYqqrtmBN_oYf8VSQ2BXDwXaTpJTn_YfwyYdWQ@mail.gmail.com>
<CAMbWs49U8Sddx_fGszPdvA3jp_nheynxaqm5Y4NqMV21VBYAuQ@mail.gmail.com>
<CAMbWs4-LwyOg9ga+NVF7yQbMi0ZsZdN1G_sO2v=YJHV18=19+A@mail.gmail.com>
<CALA8mJquG_zCJXfVwash5LKqHGtZXQmq7RfTSaRDUzGYeW=7Rw@mail.gmail.com>
<CAMbWs4_EjgcBib5+y1LYcGB3EK3Y6R+OOxGKfJo42fDovadk1g@mail.gmail.com>
<CALA8mJqe0anNM8_V6cOeOQnCHUTQggn7iOQNyQr1VaN_xMjz+w@mail.gmail.com>
<CAMbWs48eE-s-jCicC8pSVfXk8Ws-ZvUKnsw8qH-DkVBdYv0eJQ@mail.gmail.com>
<CAMbWs483a7-8M0pDttG44r-+8Gevn9VG0xNceE3WpkEQxJXPZw@mail.gmail.com>
<CAHewXNmYM6DvR_kaxDL0w0fz9BwKbac+TSU3QS10aA3cXHyMmA@mail.gmail.com>
<CA+TgmoaxH=P63hLYgyJJcEbMRnw3xi16d=HxFi1j-m7MhH6W_w@mail.gmail.com>
<CAMbWs4_cOnpGsywj9Jt1WAgzJLW9Rxt5X13cfGz4iN2qvZQ68g@mail.gmail.com>
<CA+Tgmob0q7bRbsFTVDMjxHE6zA4uDQLQa-s0CtwUw49V53UL_A@mail.gmail.com>
<CAMbWs4-Xru_eKBeRHFduigSGihdixFWVTR8A+dtMw7Mao+RkJA@mail.gmail.com>
<CAMbWs49dLjSSQRWeud+KSN0G531ciZdYoLBd5qktXA+3JQm_UQ@mail.gmail.com>
<CAMbWs48LXGC-Y63YtzEeM-3f0NUXWCUEMs7XwGzywXTjUNMcxQ@mail.gmail.com>
<CAMbWs48XdzvnwfTHWxQ7qK-yjvdrbwsPpqhJBuKDnO+hcbsVwA@mail.gmail.com>
<CA+TgmoaO-7RHdyJuizWChXZm7EJGvDcfoePDDEyUA-y8vTB1tg@mail.gmail.com>
<CAMbWs4-+jXRpKuFMZa08bS34-TBka3qqjVMAUjF=-1RA9BKvgg@mail.gmail.com>
<CA+TgmoZapU1y59-s3o8oPt7Hv+cxRh_34FMu6MXumomLe+U1Cw@mail.gmail.com>
<CAMbWs4_sEeeBmucBzbamBMfA9uLxVmOc_MV=ZpSyDbTcrUO_XQ@mail.gmail.com>
<CA+Tgmob4fnv57PQB0Oox86mHSJQ0vVL249eT=gqPvrMkG7h1zw@mail.gmail.com>
<CAMbWs489NYyTcCTbrUi7hPXKtNY5vHrrFcHyMRAv=CA5WsszVw@mail.gmail.com>
<CA+TgmoazmDdcc7NeTo3WM5HW3DASNP4rfZw6X+2nnQKHampOng@mail.gmail.com>
<CAMbWs49bYr-ULhA+-At0iQ+NaFKy72AWB6jzughk8MPTiY+gMQ@mail.gmail.com>
<CA+TgmoYa-zexdbc5nO_D6oxPMZYs06hkYwZK5Dufq+4Hhe6uNQ@mail.gmail.com>
<CAMbWs4_aji0kME490phz6nTXnPToddUn19OF3rLm1g4TbNkuzQ@mail.gmail.com>
<CA+Tgmoa3+G_=8XuQWN+0ugv6r-WV6ruFESpOxpXAAKrne3oVDQ@mail.gmail.com>
<CAMbWs49qiox13EKb7bqgLu7Gu9oar+xe6KMwBjgFwod3JzPfUw@mail.gmail.com>
<CAMbWs48F8WGA-Lzj1Dk76mFqRFxPEwG2_9Zb7+pFs8oi6ew2pw@mail.gmail.com>
<CAMbWs484ms=WRZamOyWnVditREKFqipLsdaQjcv2uKur8SZuqw@mail.gmail.com>
<CAMbWs49bL2ZMSc0W4G8=R7bjaa-vO6grucEOFYLZFUZE7+nzrQ@mail.gmail.com>
<CA+TgmobqbeJ9iRQO4ym6OiHt71sSv2eai=01kOZjxhdof9K4Mw@mail.gmail.com>
<CAMbWs4_2BzuAX+BSO1p7rtUwmQjORrG-b906Cw-RkfRjFP0oSQ@mail.gmail.com>
<CA+TgmoYbkvYwLa+1vOP7RDY7kO2=A7rppoPusoRXe44VDOGBPg@mail.gmail.com>
<CAMbWs4_aezTYOZSj7v+aypLo0dnjAierJtdx2gf6se28p88WHg@mail.gmail.com>
<CA+TgmoaY6E5-UFTWp5BtAjBO=tDQd=UVAgeJ3dRbFFzhnP5NHg@mail.gmail.com>
<CAMbWs484dnecwXT2WzWFvzEmWPzC3U9F8SRDXg-SEegTYUFyXQ@mail.gmail.com>
<CAMbWs4-2cVfBk1HNGtqV1QFo2yKnzdxLy0BAqQaJHBt+8+kspw@mail.gmail.com>
<CAMbWs48W80HFm9b+yZPKER=MA5M_bveYvBx1AwOrxdPYbLmYmQ@mail.gmail.com>
[ getting back to testing this patch ...]
On my last email you replied:
>> Debugging this query shows that all if conditions on
>> setup_eager_aggregation() returns false and create_agg_clause_infos()
>> and create_grouping_expr_infos() are called. The RelAggInfo->agg_useful
>> is also being set to true so I would expect to see Finalize and Partial
>> agg nodes, is this correct or am I missing something here?
>
> Well, just because eager aggregation *can* be applied does not mean
> that it *will* be; it depends on whether it produces a lower-cost
> execution plan. This transformation is cost-based, so it's not the
> right mindset to assume that it will always be applied when possible.
>
Sorry for the noise here. I didn't consider the costs.
On Sun Sep 28, 2025 at 11:09 PM -03, Richard Guo wrote:
> On Thu, Sep 25, 2025 at 1:23 PM Richard Guo <[email protected]> wrote:
>> Attached is an updated version of the patch with these optimizations
>> applied.
>
> FWIW, I plan to do another self-review of this patch soon, with the
> goal of assessing whether it's ready to be pushed. If anyone has any
> concerns about any part of the patch or would like to review it, I
> would greatly appreciate hearing from you.
>
I spent some time testing patch v23 using the TPC-DS benchmark and am
seeing worse execution times when using eager aggregation.
The most interesting cases are:
Query | planning time | execution time |
query 31 | -2.03% │ -99.56% │
query 71 | -15.51% │ -68.88% │
query 20 | -10.77% │ -32.40% │
query 26 | -28.01% │ -32.35% │
query 85 | -10.57% │ -31.91% │
query 77 | -30.07% │ -31.38% │
query 69 | -32.79% │ -29.21% │
query 32 | -68.48% │ -27.89% │
query 57 | -7.99% │ -27.32% │
query 91 | -24.81% │ -26.20% │
query 23 | -11.72% │ -18.24% │
The query 31 seems bad, I don't know if I'm doing something completely
wrong but I've just setup a TPC-DS database and then executed the query
on master and with the v23 patch and I got these results:
Master:
Planning Time: 3.191 ms
Execution Time: 16950.619 ms
Patch:
Planning Time: 3.257 ms
Execution Time: 3848355.646 ms
Note that I've executed ANALYZE before running the queries on both
scenarios (master and patched).
I'm attaching an EXPLAIN(ANALYZE) output for the query 31 from master
and with the patch applied.
Please let me know if there is any other test that I can run to
benchmark this patch.
--
Matheus Alcantara
│ Sort (cost=656889.77..656889.77 rows=1 width=210) (actual time=17164.506..17164.519 rows=43.00 loops=1) │
│ Sort Key: ((ss3.store_sales / ss2.store_sales)) │
│ Sort Method: quicksort Memory: 28kB │
│ Buffers: shared hit=6533 read=69203, temp read=4343 written=12055 │
│ CTE ss │
│ -> HashAggregate (cost=323021.86..377372.99 rows=1476800 width=54) (actual time=3389.564..3677.220 rows=35136.00 loops=1) │
│ Group Key: customer_address.ca_county, date_dim.d_qoy, date_dim.d_year │
│ Planned Partitions: 64 Batches: 65 Memory Usage: 8209kB Disk Usage: 56840kB │
│ Buffers: shared hit=3408 read=50944, temp read=3962 written=10947 │
│ -> Hash Join (cost=5328.60..100701.93 rows=2625180 width=28) (actual time=46.394..2034.907 rows=2685273.00 loops=1) │
│ Hash Cond: (store_sales.ss_sold_date_sk = date_dim.d_date_sk) │
│ Buffers: shared hit=3408 read=50944 │
│ -> Hash Join (cost=2261.00..90416.35 rows=2749551 width=24) (actual time=18.753..1396.048 rows=2750429.00 loops=1) │
│ Hash Cond: (store_sales.ss_addr_sk = customer_address.ca_address_sk) │
│ Buffers: shared hit=1984 read=50944 │
│ -> Seq Scan on store_sales (cost=0.00..80594.17 rows=2880217 width=14) (actual time=0.063..228.063 rows=2880404.00 loops=1) │
│ Buffers: shared hit=848 read=50944 │
│ -> Hash (cost=1636.00..1636.00 rows=50000 width=18) (actual time=18.651..18.651 rows=50000.00 loops=1) │
│ Buckets: 65536 Batches: 1 Memory Usage: 3052kB │
│ Buffers: shared hit=1136 │
│ -> Seq Scan on customer_address (cost=0.00..1636.00 rows=50000 width=18) (actual time=0.005..9.555 rows=50000.00 loops=1) │
│ Buffers: shared hit=1136
│ -> Hash (cost=2154.49..2154.49 rows=73049 width=12) (actual time=27.627..27.629 rows=73049.00 loops=1) │
│ Buckets: 131072 Batches: 1 Memory Usage: 4163kB │
│ Buffers: shared hit=1424 │
│ -> Seq Scan on date_dim (cost=0.00..2154.49 rows=73049 width=12) (actual time=0.009..15.154 rows=73049.00 loops=1) │
│ Buffers: shared hit=1424 │
│ CTE ws │
│ -> HashAggregate (cost=96009.03..114825.35 rows=718952 width=54) (actual time=977.215..1014.889 rows=23320.00 loops=1) │
│ Group Key: customer_address_1.ca_county, date_dim_1.d_qoy, date_dim_1.d_year │
│ Planned Partitions: 32 Batches: 33 Memory Usage: 8209kB Disk Usage: 6032kB │
│ Buffers: shared hit=3125 read=18259, temp read=381 written=1108 │
│ -> Hash Join (cost=5328.60..35122.78 rows=718952 width=28) (actual time=46.623..611.054 rows=719118.00 loops=1) │
│ Hash Cond: (web_sales.ws_bill_addr_sk = customer_address_1.ca_address_sk)
│ Buffers: shared hit=3125 read=18259 │
│ -> Hash Join (cost=3067.60..30973.94 rows=719120 width=18) (actual time=27.691..424.273 rows=719195.00 loops=1) │
│ Hash Cond: (web_sales.ws_sold_date_sk = date_dim_1.d_date_sk) │
│ Buffers: shared hit=1989 read=18259 │
│ -> Seq Scan on web_sales (cost=0.00..26017.84 rows=719384 width=14) (actual time=0.082..63.389 rows=719384.00 loops=1) │
│ Buffers: shared hit=565 read=18259 │
│ -> Hash (cost=2154.49..2154.49 rows=73049 width=12) (actual time=27.538..27.538 rows=73049.00 loops=1) │
│ Buckets: 131072 Batches: 1 Memory Usage: 4163kB │
│ Buffers: shared hit=1424 │
│ -> Seq Scan on date_dim date_dim_1 (cost=0.00..2154.49 rows=73049 width=12) (actual time=0.006..14.914 rows=73049.00 loops=1) │
│ Buffers: shared hit=1424 │
│ -> Hash (cost=1636.00..1636.00 rows=50000 width=18) (actual time=18.902..18.902 rows=50000.00 loops=1) │
│ Buckets: 65536 Batches: 1 Memory Usage: 3052kB │
│ Buffers: shared hit=1136 │
│ -> Seq Scan on customer_address customer_address_1 (cost=0.00..1636.00 rows=50000 width=18) (actual time=0.008..9.727 rows=50000.00 loops=1) │
│ Buffers: shared hit=1136 │
│ -> Nested Loop (cost=0.00..164691.41 rows=1 width=210) (actual time=4817.695..17164.430 rows=43.00 loops=1) │
│ Join Filter: (((ss1.ca_county)::text = (ws2.ca_county)::text) AND (CASE WHEN (ws1.web_sales > '0'::numeric) THEN (ws2.web_sales / ws1.web_sales) ELSE NULL::numeric END > CASE WHEN (ss1.store_sales > '0'::numeric) THEN (ss2.store_sales / ss1.store_sales) ELSE NULL::numeric END) AND (CASE WHEN (ws2.web_sales > '0'::numeric) THEN (ws3.web_sales / ws2.web_sales) ELSE NULL::numeric END > CASE WHEN (ss2.store_sales > '0'::numeric) THEN (ss3.store_sales / ss2.store_sales) ELSE NULL::numeric END)) │
│ Rows Removed by Join Filter: 527207 │
│ Buffers: shared hit=6533 read=69203, temp read=4343 written=12055 │
│ -> Nested Loop (cost=0.00..146716.93 rows=1 width=554) (actual time=4671.968..15501.760 rows=570.00 loops=1) │
│ Join Filter: ((ss1.ca_county)::text = (ss3.ca_county)::text) │
│ Rows Removed by Join Filter: 1038674 │
│ Buffers: shared hit=6533 read=69203, temp read=4343 written=12055 │
│ -> Nested Loop (cost=0.00..109796.47 rows=1 width=444) (actual time=4669.164..12922.095 rows=578.00 loops=1)
│ Join Filter: ((ss1.ca_county)::text = (ss2.ca_county)::text) │
│ Rows Removed by Join Filter: 1008217 │
│ Buffers: shared hit=6533 read=69203, temp read=3559 written=12055 │
│ -> Nested Loop (cost=0.00..72876.00 rows=1 width=334) (actual time=4666.835..10231.481 rows=617.00 loops=1) │
│ Join Filter: ((ss1.ca_county)::text = (ws1.ca_county)::text) │
│ Rows Removed by Join Filter: 1089697 │
│ Buffers: shared hit=6533 read=69203, temp read=3559 written=12055 │
│ -> Nested Loop (cost=0.00..35954.71 rows=2 width=220) (actual time=1031.594..3687.112 rows=662.00 loops=1) │
│ Join Filter: ((ws1.ca_county)::text = (ws3.ca_county)::text) │
│ Rows Removed by Join Filter: 1148109 │
│ Buffers: shared hit=3125 read=18259, temp read=381 written=1108 │
│ -> CTE Scan on ws ws1 (cost=0.00..17973.80 rows=18 width=110) (actual time=977.224..980.082 rows=911.00 loops=1)
│ Filter: ((d_qoy = 1) AND (d_year = 1999)) │
│ Rows Removed by Filter: 22409 │
│ Storage: Memory Maximum Storage: 1700kB │
│ Buffers: shared hit=3125 read=18259, temp written=1107 │
│ -> CTE Scan on ws ws3 (cost=0.00..17973.80 rows=18 width=110) (actual time=0.005..2.857 rows=1261.00 loops=911) │
│ Filter: ((d_year = 1999) AND (d_qoy = 3)) │
│ Rows Removed by Filter: 22059 │
│ Storage: Memory Maximum Storage: 1700kB │
│ Buffers: temp read=381 written=1 │
│ -> CTE Scan on ss ss1 (cost=0.00..36920.00 rows=37 width=114) (actual time=5.121..9.740 rows=1647.00 loops=662) │
│ Filter: ((d_qoy = 1) AND (d_year = 1999)) │
│ Rows Removed by Filter: 33489 │
│ Storage: Memory Maximum Storage: 2636kB │
│ Buffers: shared hit=3408 read=50944, temp read=3178 written=10947 │
│ -> CTE Scan on ss ss2 (cost=0.00..36920.00 rows=37 width=110) (actual time=0.001..4.216 rows=1635.00 loops=617) │
│ Filter: ((d_year = 1999) AND (d_qoy = 2)) │
│ Rows Removed by Filter: 33501 │
│ Storage: Memory Maximum Storage: 2636kB │
│ -> CTE Scan on ss ss3 (cost=0.00..36920.00 rows=37 width=110) (actual time=0.006..4.305 rows=1798.00 loops=578) │
│ Filter: ((d_year = 1999) AND (d_qoy = 3)) │
│ Rows Removed by Filter: 33338 │
│ Storage: Memory Maximum Storage: 2636kB │
│ Buffers: temp read=784 │
│ -> CTE Scan on ws ws2 (cost=0.00..17973.80 rows=18 width=110) (actual time=0.001..2.810 rows=925.00 loops=570) │
│ Filter: ((d_year = 1999) AND (d_qoy = 2))
│ Rows Removed by Filter: 22395 │
│ Storage: Memory Maximum Storage: 1700kB │
│ Planning: │
│ Buffers: shared hit=12 │
│ Planning Time: 2.180 ms │
│ Execution Time: 17166.558 ms │
└────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
│ Sort (cost=302668.66..302668.66 rows=1 width=210) (actual time=3825537.172..3825541.540 rows=43.00 loops=1) │
│ Sort Key: ((ss3.store_sales / ss2.store_sales)) │
│ Sort Method: quicksort Memory: 28kB │
│ Buffers: shared hit=21757 read=69012, temp read=14486 written=25552 │
│ CTE ss │
│ -> Finalize GroupAggregate (cost=178135.51..215272.86 rows=262517 width=54) (actual time=1471.638..1733.635 rows=35117.00 loops=1) │
│ Group Key: customer_address.ca_county, date_dim.d_qoy, date_dim.d_year │
│ Buffers: shared hit=3533 read=50849, temp read=14486 written=25552 │
│ -> Gather Merge (cost=178135.51..208709.94 rows=262517 width=54) (actual time=1471.627..1586.417 rows=234867.00 loops=1) │
│ Workers Planned: 2 │
│ Workers Launched: 2 │
│ Buffers: shared hit=3533 read=50849, temp read=14486 written=25552 │
│ -> Sort (cost=177135.48..177408.94 rows=109382 width=54) (actual time=1463.292..1497.110 rows=78658.67 loops=3) │
│ Sort Key: customer_address.ca_county, date_dim.d_qoy, date_dim.d_year │
│ Sort Method: external merge Disk: 7944kB │
│ Buffers: shared hit=3533 read=50849, temp read=14486 written=25552 │
│ Worker 0: Sort Method: external merge Disk: 8000kB │
│ Worker 1: Sort Method: external merge Disk: 7928kB │
│ -> Parallel Hash Join (cost=147862.49..164239.25 rows=109382 width=54) (actual time=839.965..1235.101 rows=80523.33 loops=3) │
│ Hash Cond: (store_sales.ss_sold_date_sk = date_dim.d_date_sk) │
│ Buffers: shared hit=3503 read=50849, temp read=11502 written=22562 │
│ -> Parallel Hash Join (cost=145471.66..161547.68 rows=114565 width=50) (actual time=820.740..1192.922 rows=96392.00 loops=3) │
│ Hash Cond: (store_sales.ss_addr_sk = customer_address.ca_address_sk) │
│ Buffers: shared hit=2079 read=50849, temp read=11502 written=22562 │
│ -> Partial HashAggregate (cost=143673.89..158993.80 rows=288022 width=40) (actual time=810.581..1155.245 rows=98213.67 loops=3) │
│ Group Key: store_sales.ss_sold_date_sk, store_sales.ss_addr_sk │
│ Planned Partitions: 16 Batches: 17 Memory Usage: 8337kB Disk Usage: 31640kB │
│ Buffers: shared hit=943 read=50849, temp read=11502 written=22562 │
│ Worker 0: Batches: 17 Memory Usage: 8337kB Disk Usage: 31760kB │
│ Worker 1: Batches: 17 Memory Usage: 8337kB Disk Usage: 31640kB │
│ -> Parallel Seq Scan on store_sales (cost=0.00..63792.90 rows=1200090 width=14) (actual time=0.126..79.442 rows=960134.67 loops=3) │
│ Buffers: shared hit=943 read=50849 │
│ -> Parallel Hash (cost=1430.12..1430.12 rows=29412 width=18) (actual time=10.036..10.038 rows=16666.67 loops=3) │
│ Buckets: 65536 Batches: 1 Memory Usage: 3264kB │
│ Buffers: shared hit=1136 │
│ -> Parallel Seq Scan on customer_address (cost=0.00..1430.12 rows=29412 width=18) (actual time=0.007..5.102 rows=16666.67 loops=3) │
│ Buffers: shared hit=1136 │
│ -> Parallel Hash (cost=1853.70..1853.70 rows=42970 width=12) (actual time=19.092..19.094 rows=24349.67 loops=3) │
│ Buckets: 131072 Batches: 1 Memory Usage: 4512kB │
│ Buffers: shared hit=1424 │
│ -> Parallel Seq Scan on date_dim (cost=0.00..1853.70 rows=42970 width=12) (actual time=0.012..10.264 rows=24349.67 loops=3) │
│ Buffers: shared hit=1424 │
│ CTE ws │
│ -> Finalize GroupAggregate (cost=52144.19..62314.79 rows=71894 width=54) (actual time=275.121..340.107 rows=23312.00 loops=1) │
│ Group Key: customer_address_1.ca_county, date_dim_1.d_qoy, date_dim_1.d_year │
│ Buffers: shared hit=18224 read=18163 │
│ -> Gather Merge (cost=52144.19..60517.44 rows=71894 width=54) (actual time=275.107..297.072 rows=60190.00 loops=1) │
│ Workers Planned: 2 │
│ Workers Launched: 2 │
│ Buffers: shared hit=18224 read=18163 │
│ -> Sort (cost=51144.17..51219.06 rows=29956 width=54) (actual time=271.870..272.906 rows=20293.33 loops=3) │
│ Sort Key: customer_address_1.ca_county, date_dim_1.d_qoy, date_dim_1.d_year │
│ Sort Method: quicksort Memory: 2931kB │
│ Buffers: shared hit=18224 read=18163 │
│ Worker 0: Sort Method: quicksort Memory: 2938kB │
│ Worker 1: Sort Method: quicksort Memory: 2955kB │
│ -> Nested Loop (cost=43571.15..48916.86 rows=29956 width=54) (actual time=184.657..215.740 rows=20419.67 loops=3) │
│ Buffers: shared hit=18194 read=18163 │
│ -> Parallel Hash Join (cost=43570.84..47586.10 rows=29967 width=50) (actual time=184.630..201.358 rows=20451.00 loops=3) │
│ Hash Cond: (web_sales.ws_bill_addr_sk = customer_address_1.ca_address_sk) │
│ Buffers: shared hit=1797 read=18163 │
│ -> Partial HashAggregate (cost=41773.08..45599.48 rows=71938 width=40) (actual time=177.706..188.464 rows=20477.33 loops=3) │
│ Group Key: web_sales.ws_sold_date_sk, web_sales.ws_bill_addr_sk │
│ Planned Partitions: 4 Batches: 1 Memory Usage: 7953kB │
│ Buffers: shared hit=661 read=18163 │
│ Worker 0: Batches: 1 Memory Usage: 7953kB │
│ Worker 1: Batches: 1 Memory Usage: 7953kB │
│ -> Parallel Seq Scan on web_sales (cost=0.00..21821.43 rows=299743 width=14) (actual time=0.106..23.122 rows=239794.67 loops=3) │
│ Buffers: shared hit=661 read=18163 │
│ -> Parallel Hash (cost=1430.12..1430.12 rows=29412 width=18) (actual time=6.846..6.847 rows=16666.67 loops=3) │
│ Buckets: 65536 Batches: 1 Memory Usage: 3264kB │
│ Buffers: shared hit=1136 │
│ -> Parallel Seq Scan on customer_address customer_address_1 (cost=0.00..1430.12 rows=29412 width=18) (actual time=0.008..3.586 rows=16666.67 loops=3) │
│ Buffers: shared hit=1136 │
│ -> Memoize (cost=0.30..0.33 rows=1 width=12) (actual time=0.000..0.000 rows=1.00 loops=61353) │
│ Cache Key: web_sales.ws_sold_date_sk │
│ Cache Mode: logical │
│ Estimates: capacity=1822 distinct keys=1822 lookups=29967 hit percent=93.92% │
│ Hits: 18542 Misses: 1824 Evictions: 0 Overflows: 0 Memory Usage: 200kB │
│ Buffers: shared hit=16397 │
│ Worker 0: Hits: 18589 Misses: 1821 Evictions: 0 Overflows: 0 Memory Usage: 200kB │
│ Worker 1: Hits: 18754 Misses: 1823 Evictions: 0 Overflows: 0 Memory Usage: 200kB │
│ -> Index Scan using date_dim_pkey on date_dim date_dim_1 (cost=0.29..0.32 rows=1 width=12) (actual time=0.002..0.002 rows=1.00 loops=5468) │
│ Index Cond: (d_date_sk = web_sales.ws_sold_date_sk) │
│ Index Searches: 5465 │
│ Buffers: shared hit=16397 │
│ -> Nested Loop (cost=0.00..25081.00 rows=1 width=210) (actual time=43808.287..3825536.966 rows=43.00 loops=1) │
│ Join Filter: (((ss1.ca_county)::text = (ss2.ca_county)::text) AND (CASE WHEN (ws1.web_sales > '0'::numeric) THEN (ws2.web_sales / ws1.web_sales) ELSE NULL::numeric END > CASE WHEN (ss1.store_sales > '0'::numeric) THEN (ss2.store_sales / ss1.store_sales) ELSE NULL::numeric END)) │
│ Rows Removed by Join Filter: 226832 │
│ Buffers: shared hit=7500 read=22936, temp read=4819 written=8505 │
│ -> Merge Join (cost=0.00..8360.31 rows=1 width=224) (actual time=1747.759..1760.887 rows=825.00 loops=1) │
│ Merge Cond: ((ss1.ca_county)::text = (ws1.ca_county)::text) │
│ Buffers: shared hit=7500 read=22936, temp read=4321 written=8505 │
│ -> CTE Scan on ss ss1 (cost=0.00..6562.93 rows=7 width=114) (actual time=1471.648..1477.297 rows=1647.00 loops=1) │
│ Filter: ((d_qoy = 1) AND (d_year = 1999)) │
│ Rows Removed by Filter: 33470 │
│ Storage: Memory Maximum Storage: 2635kB │
│ Buffers: shared hit=1278 read=16903, temp read=4321 written=8505 │
│ -> Materialize (cost=0.00..1797.36 rows=2 width=110) (actual time=275.335..280.952 rows=911.00 loops=1) │
│ Storage: Memory Maximum Storage: 17kB │
│ Buffers: shared hit=6222 read=6033 │
│ -> CTE Scan on ws ws1 (cost=0.00..1797.35 rows=2 width=110) (actual time=275.333..279.774 rows=911.00 loops=1) │
│ Filter: ((d_qoy = 1) AND (d_year = 1999)) │
│ Rows Removed by Filter: 22390 │
│ Storage: Memory Maximum Storage: 1700kB │
│ Buffers: shared hit=6222 read=6033 │
│ -> Nested Loop (cost=0.00..16720.65 rows=1 width=440) (actual time=5.913..4634.838 rows=275.00 loops=825) │
│ Join Filter: (((ss2.ca_county)::text = (ss3.ca_county)::text) AND (CASE WHEN (ws2.web_sales > '0'::numeric) THEN (ws3.web_sales / ws2.web_sales) ELSE NULL::numeric END > CASE WHEN (ss2.store_sales > '0'::numeric) THEN (ss3.store_sales / ss2.store_sales) ELSE NULL::numeric END)) │
│ Rows Removed by Join Filter: 1037001 │
│ Buffers: temp read=498 │
│ -> Merge Join (cost=0.00..8360.31 rows=1 width=220) (actual time=0.001..5.266 rows=844.00 loops=825) │
│ Merge Cond: ((ss2.ca_county)::text = (ws2.ca_county)::text) │
│ -> CTE Scan on ss ss2 (cost=0.00..6562.93 rows=7 width=110) (actual time=0.001..4.131 rows=1634.00 loops=825) │
│ Filter: ((d_year = 1999) AND (d_qoy = 2)) │
│ Rows Removed by Filter: 33468 │
│ Storage: Memory Maximum Storage: 2635kB │
│ -> Materialize (cost=0.00..1797.36 rows=2 width=110) (actual time=0.000..0.053 rows=925.00 loops=825) │
│ Storage: Memory Maximum Storage: 74kB │
│ -> CTE Scan on ws ws2 (cost=0.00..1797.35 rows=2 width=110) (actual time=0.001..2.784 rows=925.00 loops=1) │
│ Filter: ((d_year = 1999) AND (d_qoy = 2)) │
│ Rows Removed by Filter: 22382 │
│ Storage: Memory Maximum Storage: 1700kB │
│ -> Merge Join (cost=0.00..8360.31 rows=1 width=220) (actual time=0.002..5.383 rows=1229.00 loops=696300) │
│ Merge Cond: ((ss3.ca_county)::text = (ws3.ca_county)::text) │
│ Buffers: temp read=498 │
│ -> CTE Scan on ss ss3 (cost=0.00..6562.93 rows=7 width=110) (actual time=0.001..4.051 rows=1796.00 loops=696300) │
│ Filter: ((d_year = 1999) AND (d_qoy = 3)) │
│ Rows Removed by Filter: 33292 │
│ Storage: Memory Maximum Storage: 2635kB │
│ Buffers: temp read=498 │
│ -> Materialize (cost=0.00..1797.36 rows=2 width=110) (actual time=0.000..0.047 rows=1261.00 loops=696300) │
│ Storage: Memory Maximum Storage: 95kB │
│ -> CTE Scan on ws ws3 (cost=0.00..1797.35 rows=2 width=110) (actual time=0.001..74.725 rows=1261.00 loops=1) │
│ Filter: ((d_year = 1999) AND (d_qoy = 3)) │
│ Rows Removed by Filter: 22051 │
│ Storage: Memory Maximum Storage: 1700kB │
│ Planning: │
│ Buffers: shared hit=12 │
│ Planning Time: 4.951 ms │
│ Execution Time: 3825542.556 ms │
└──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
Attachments:
[text/plain] query-31.master.explain (50.6K, 2-query-31.master.explain)
download | inline:
│ Sort (cost=656889.77..656889.77 rows=1 width=210) (actual time=17164.506..17164.519 rows=43.00 loops=1) │
│ Sort Key: ((ss3.store_sales / ss2.store_sales)) │
│ Sort Method: quicksort Memory: 28kB │
│ Buffers: shared hit=6533 read=69203, temp read=4343 written=12055 │
│ CTE ss │
│ -> HashAggregate (cost=323021.86..377372.99 rows=1476800 width=54) (actual time=3389.564..3677.220 rows=35136.00 loops=1) │
│ Group Key: customer_address.ca_county, date_dim.d_qoy, date_dim.d_year │
│ Planned Partitions: 64 Batches: 65 Memory Usage: 8209kB Disk Usage: 56840kB │
│ Buffers: shared hit=3408 read=50944, temp read=3962 written=10947 │
│ -> Hash Join (cost=5328.60..100701.93 rows=2625180 width=28) (actual time=46.394..2034.907 rows=2685273.00 loops=1) │
│ Hash Cond: (store_sales.ss_sold_date_sk = date_dim.d_date_sk) │
│ Buffers: shared hit=3408 read=50944 │
│ -> Hash Join (cost=2261.00..90416.35 rows=2749551 width=24) (actual time=18.753..1396.048 rows=2750429.00 loops=1) │
│ Hash Cond: (store_sales.ss_addr_sk = customer_address.ca_address_sk) │
│ Buffers: shared hit=1984 read=50944 │
│ -> Seq Scan on store_sales (cost=0.00..80594.17 rows=2880217 width=14) (actual time=0.063..228.063 rows=2880404.00 loops=1) │
│ Buffers: shared hit=848 read=50944 │
│ -> Hash (cost=1636.00..1636.00 rows=50000 width=18) (actual time=18.651..18.651 rows=50000.00 loops=1) │
│ Buckets: 65536 Batches: 1 Memory Usage: 3052kB │
│ Buffers: shared hit=1136 │
│ -> Seq Scan on customer_address (cost=0.00..1636.00 rows=50000 width=18) (actual time=0.005..9.555 rows=50000.00 loops=1) │
│ Buffers: shared hit=1136
│ -> Hash (cost=2154.49..2154.49 rows=73049 width=12) (actual time=27.627..27.629 rows=73049.00 loops=1) │
│ Buckets: 131072 Batches: 1 Memory Usage: 4163kB │
│ Buffers: shared hit=1424 │
│ -> Seq Scan on date_dim (cost=0.00..2154.49 rows=73049 width=12) (actual time=0.009..15.154 rows=73049.00 loops=1) │
│ Buffers: shared hit=1424 │
│ CTE ws │
│ -> HashAggregate (cost=96009.03..114825.35 rows=718952 width=54) (actual time=977.215..1014.889 rows=23320.00 loops=1) │
│ Group Key: customer_address_1.ca_county, date_dim_1.d_qoy, date_dim_1.d_year │
│ Planned Partitions: 32 Batches: 33 Memory Usage: 8209kB Disk Usage: 6032kB │
│ Buffers: shared hit=3125 read=18259, temp read=381 written=1108 │
│ -> Hash Join (cost=5328.60..35122.78 rows=718952 width=28) (actual time=46.623..611.054 rows=719118.00 loops=1) │
│ Hash Cond: (web_sales.ws_bill_addr_sk = customer_address_1.ca_address_sk)
│ Buffers: shared hit=3125 read=18259 │
│ -> Hash Join (cost=3067.60..30973.94 rows=719120 width=18) (actual time=27.691..424.273 rows=719195.00 loops=1) │
│ Hash Cond: (web_sales.ws_sold_date_sk = date_dim_1.d_date_sk) │
│ Buffers: shared hit=1989 read=18259 │
│ -> Seq Scan on web_sales (cost=0.00..26017.84 rows=719384 width=14) (actual time=0.082..63.389 rows=719384.00 loops=1) │
│ Buffers: shared hit=565 read=18259 │
│ -> Hash (cost=2154.49..2154.49 rows=73049 width=12) (actual time=27.538..27.538 rows=73049.00 loops=1) │
│ Buckets: 131072 Batches: 1 Memory Usage: 4163kB │
│ Buffers: shared hit=1424 │
│ -> Seq Scan on date_dim date_dim_1 (cost=0.00..2154.49 rows=73049 width=12) (actual time=0.006..14.914 rows=73049.00 loops=1) │
│ Buffers: shared hit=1424 │
│ -> Hash (cost=1636.00..1636.00 rows=50000 width=18) (actual time=18.902..18.902 rows=50000.00 loops=1) │
│ Buckets: 65536 Batches: 1 Memory Usage: 3052kB │
│ Buffers: shared hit=1136 │
│ -> Seq Scan on customer_address customer_address_1 (cost=0.00..1636.00 rows=50000 width=18) (actual time=0.008..9.727 rows=50000.00 loops=1) │
│ Buffers: shared hit=1136 │
│ -> Nested Loop (cost=0.00..164691.41 rows=1 width=210) (actual time=4817.695..17164.430 rows=43.00 loops=1) │
│ Join Filter: (((ss1.ca_county)::text = (ws2.ca_county)::text) AND (CASE WHEN (ws1.web_sales > '0'::numeric) THEN (ws2.web_sales / ws1.web_sales) ELSE NULL::numeric END > CASE WHEN (ss1.store_sales > '0'::numeric) THEN (ss2.store_sales / ss1.store_sales) ELSE NULL::numeric END) AND (CASE WHEN (ws2.web_sales > '0'::numeric) THEN (ws3.web_sales / ws2.web_sales) ELSE NULL::numeric END > CASE WHEN (ss2.store_sales > '0'::numeric) THEN (ss3.store_sales / ss2.store_sales) ELSE NULL::numeric END)) │
│ Rows Removed by Join Filter: 527207 │
│ Buffers: shared hit=6533 read=69203, temp read=4343 written=12055 │
│ -> Nested Loop (cost=0.00..146716.93 rows=1 width=554) (actual time=4671.968..15501.760 rows=570.00 loops=1) │
│ Join Filter: ((ss1.ca_county)::text = (ss3.ca_county)::text) │
│ Rows Removed by Join Filter: 1038674 │
│ Buffers: shared hit=6533 read=69203, temp read=4343 written=12055 │
│ -> Nested Loop (cost=0.00..109796.47 rows=1 width=444) (actual time=4669.164..12922.095 rows=578.00 loops=1)
│ Join Filter: ((ss1.ca_county)::text = (ss2.ca_county)::text) │
│ Rows Removed by Join Filter: 1008217 │
│ Buffers: shared hit=6533 read=69203, temp read=3559 written=12055 │
│ -> Nested Loop (cost=0.00..72876.00 rows=1 width=334) (actual time=4666.835..10231.481 rows=617.00 loops=1) │
│ Join Filter: ((ss1.ca_county)::text = (ws1.ca_county)::text) │
│ Rows Removed by Join Filter: 1089697 │
│ Buffers: shared hit=6533 read=69203, temp read=3559 written=12055 │
│ -> Nested Loop (cost=0.00..35954.71 rows=2 width=220) (actual time=1031.594..3687.112 rows=662.00 loops=1) │
│ Join Filter: ((ws1.ca_county)::text = (ws3.ca_county)::text) │
│ Rows Removed by Join Filter: 1148109 │
│ Buffers: shared hit=3125 read=18259, temp read=381 written=1108 │
│ -> CTE Scan on ws ws1 (cost=0.00..17973.80 rows=18 width=110) (actual time=977.224..980.082 rows=911.00 loops=1)
│ Filter: ((d_qoy = 1) AND (d_year = 1999)) │
│ Rows Removed by Filter: 22409 │
│ Storage: Memory Maximum Storage: 1700kB │
│ Buffers: shared hit=3125 read=18259, temp written=1107 │
│ -> CTE Scan on ws ws3 (cost=0.00..17973.80 rows=18 width=110) (actual time=0.005..2.857 rows=1261.00 loops=911) │
│ Filter: ((d_year = 1999) AND (d_qoy = 3)) │
│ Rows Removed by Filter: 22059 │
│ Storage: Memory Maximum Storage: 1700kB │
│ Buffers: temp read=381 written=1 │
│ -> CTE Scan on ss ss1 (cost=0.00..36920.00 rows=37 width=114) (actual time=5.121..9.740 rows=1647.00 loops=662) │
│ Filter: ((d_qoy = 1) AND (d_year = 1999)) │
│ Rows Removed by Filter: 33489 │
│ Storage: Memory Maximum Storage: 2636kB │
│ Buffers: shared hit=3408 read=50944, temp read=3178 written=10947 │
│ -> CTE Scan on ss ss2 (cost=0.00..36920.00 rows=37 width=110) (actual time=0.001..4.216 rows=1635.00 loops=617) │
│ Filter: ((d_year = 1999) AND (d_qoy = 2)) │
│ Rows Removed by Filter: 33501 │
│ Storage: Memory Maximum Storage: 2636kB │
│ -> CTE Scan on ss ss3 (cost=0.00..36920.00 rows=37 width=110) (actual time=0.006..4.305 rows=1798.00 loops=578) │
│ Filter: ((d_year = 1999) AND (d_qoy = 3)) │
│ Rows Removed by Filter: 33338 │
│ Storage: Memory Maximum Storage: 2636kB │
│ Buffers: temp read=784 │
│ -> CTE Scan on ws ws2 (cost=0.00..17973.80 rows=18 width=110) (actual time=0.001..2.810 rows=925.00 loops=570) │
│ Filter: ((d_year = 1999) AND (d_qoy = 2))
│ Rows Removed by Filter: 22395 │
│ Storage: Memory Maximum Storage: 1700kB │
│ Planning: │
│ Buffers: shared hit=12 │
│ Planning Time: 2.180 ms │
│ Execution Time: 17166.558 ms │
└────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
[text/plain] query-31.patch.explain (42.0K, 3-query-31.patch.explain)
download | inline:
│ Sort (cost=302668.66..302668.66 rows=1 width=210) (actual time=3825537.172..3825541.540 rows=43.00 loops=1) │
│ Sort Key: ((ss3.store_sales / ss2.store_sales)) │
│ Sort Method: quicksort Memory: 28kB │
│ Buffers: shared hit=21757 read=69012, temp read=14486 written=25552 │
│ CTE ss │
│ -> Finalize GroupAggregate (cost=178135.51..215272.86 rows=262517 width=54) (actual time=1471.638..1733.635 rows=35117.00 loops=1) │
│ Group Key: customer_address.ca_county, date_dim.d_qoy, date_dim.d_year │
│ Buffers: shared hit=3533 read=50849, temp read=14486 written=25552 │
│ -> Gather Merge (cost=178135.51..208709.94 rows=262517 width=54) (actual time=1471.627..1586.417 rows=234867.00 loops=1) │
│ Workers Planned: 2 │
│ Workers Launched: 2 │
│ Buffers: shared hit=3533 read=50849, temp read=14486 written=25552 │
│ -> Sort (cost=177135.48..177408.94 rows=109382 width=54) (actual time=1463.292..1497.110 rows=78658.67 loops=3) │
│ Sort Key: customer_address.ca_county, date_dim.d_qoy, date_dim.d_year │
│ Sort Method: external merge Disk: 7944kB │
│ Buffers: shared hit=3533 read=50849, temp read=14486 written=25552 │
│ Worker 0: Sort Method: external merge Disk: 8000kB │
│ Worker 1: Sort Method: external merge Disk: 7928kB │
│ -> Parallel Hash Join (cost=147862.49..164239.25 rows=109382 width=54) (actual time=839.965..1235.101 rows=80523.33 loops=3) │
│ Hash Cond: (store_sales.ss_sold_date_sk = date_dim.d_date_sk) │
│ Buffers: shared hit=3503 read=50849, temp read=11502 written=22562 │
│ -> Parallel Hash Join (cost=145471.66..161547.68 rows=114565 width=50) (actual time=820.740..1192.922 rows=96392.00 loops=3) │
│ Hash Cond: (store_sales.ss_addr_sk = customer_address.ca_address_sk) │
│ Buffers: shared hit=2079 read=50849, temp read=11502 written=22562 │
│ -> Partial HashAggregate (cost=143673.89..158993.80 rows=288022 width=40) (actual time=810.581..1155.245 rows=98213.67 loops=3) │
│ Group Key: store_sales.ss_sold_date_sk, store_sales.ss_addr_sk │
│ Planned Partitions: 16 Batches: 17 Memory Usage: 8337kB Disk Usage: 31640kB │
│ Buffers: shared hit=943 read=50849, temp read=11502 written=22562 │
│ Worker 0: Batches: 17 Memory Usage: 8337kB Disk Usage: 31760kB │
│ Worker 1: Batches: 17 Memory Usage: 8337kB Disk Usage: 31640kB │
│ -> Parallel Seq Scan on store_sales (cost=0.00..63792.90 rows=1200090 width=14) (actual time=0.126..79.442 rows=960134.67 loops=3) │
│ Buffers: shared hit=943 read=50849 │
│ -> Parallel Hash (cost=1430.12..1430.12 rows=29412 width=18) (actual time=10.036..10.038 rows=16666.67 loops=3) │
│ Buckets: 65536 Batches: 1 Memory Usage: 3264kB │
│ Buffers: shared hit=1136 │
│ -> Parallel Seq Scan on customer_address (cost=0.00..1430.12 rows=29412 width=18) (actual time=0.007..5.102 rows=16666.67 loops=3) │
│ Buffers: shared hit=1136 │
│ -> Parallel Hash (cost=1853.70..1853.70 rows=42970 width=12) (actual time=19.092..19.094 rows=24349.67 loops=3) │
│ Buckets: 131072 Batches: 1 Memory Usage: 4512kB │
│ Buffers: shared hit=1424 │
│ -> Parallel Seq Scan on date_dim (cost=0.00..1853.70 rows=42970 width=12) (actual time=0.012..10.264 rows=24349.67 loops=3) │
│ Buffers: shared hit=1424 │
│ CTE ws │
│ -> Finalize GroupAggregate (cost=52144.19..62314.79 rows=71894 width=54) (actual time=275.121..340.107 rows=23312.00 loops=1) │
│ Group Key: customer_address_1.ca_county, date_dim_1.d_qoy, date_dim_1.d_year │
│ Buffers: shared hit=18224 read=18163 │
│ -> Gather Merge (cost=52144.19..60517.44 rows=71894 width=54) (actual time=275.107..297.072 rows=60190.00 loops=1) │
│ Workers Planned: 2 │
│ Workers Launched: 2 │
│ Buffers: shared hit=18224 read=18163 │
│ -> Sort (cost=51144.17..51219.06 rows=29956 width=54) (actual time=271.870..272.906 rows=20293.33 loops=3) │
│ Sort Key: customer_address_1.ca_county, date_dim_1.d_qoy, date_dim_1.d_year │
│ Sort Method: quicksort Memory: 2931kB │
│ Buffers: shared hit=18224 read=18163 │
│ Worker 0: Sort Method: quicksort Memory: 2938kB │
│ Worker 1: Sort Method: quicksort Memory: 2955kB │
│ -> Nested Loop (cost=43571.15..48916.86 rows=29956 width=54) (actual time=184.657..215.740 rows=20419.67 loops=3) │
│ Buffers: shared hit=18194 read=18163 │
│ -> Parallel Hash Join (cost=43570.84..47586.10 rows=29967 width=50) (actual time=184.630..201.358 rows=20451.00 loops=3) │
│ Hash Cond: (web_sales.ws_bill_addr_sk = customer_address_1.ca_address_sk) │
│ Buffers: shared hit=1797 read=18163 │
│ -> Partial HashAggregate (cost=41773.08..45599.48 rows=71938 width=40) (actual time=177.706..188.464 rows=20477.33 loops=3) │
│ Group Key: web_sales.ws_sold_date_sk, web_sales.ws_bill_addr_sk │
│ Planned Partitions: 4 Batches: 1 Memory Usage: 7953kB │
│ Buffers: shared hit=661 read=18163 │
│ Worker 0: Batches: 1 Memory Usage: 7953kB │
│ Worker 1: Batches: 1 Memory Usage: 7953kB │
│ -> Parallel Seq Scan on web_sales (cost=0.00..21821.43 rows=299743 width=14) (actual time=0.106..23.122 rows=239794.67 loops=3) │
│ Buffers: shared hit=661 read=18163 │
│ -> Parallel Hash (cost=1430.12..1430.12 rows=29412 width=18) (actual time=6.846..6.847 rows=16666.67 loops=3) │
│ Buckets: 65536 Batches: 1 Memory Usage: 3264kB │
│ Buffers: shared hit=1136 │
│ -> Parallel Seq Scan on customer_address customer_address_1 (cost=0.00..1430.12 rows=29412 width=18) (actual time=0.008..3.586 rows=16666.67 loops=3) │
│ Buffers: shared hit=1136 │
│ -> Memoize (cost=0.30..0.33 rows=1 width=12) (actual time=0.000..0.000 rows=1.00 loops=61353) │
│ Cache Key: web_sales.ws_sold_date_sk │
│ Cache Mode: logical │
│ Estimates: capacity=1822 distinct keys=1822 lookups=29967 hit percent=93.92% │
│ Hits: 18542 Misses: 1824 Evictions: 0 Overflows: 0 Memory Usage: 200kB │
│ Buffers: shared hit=16397 │
│ Worker 0: Hits: 18589 Misses: 1821 Evictions: 0 Overflows: 0 Memory Usage: 200kB │
│ Worker 1: Hits: 18754 Misses: 1823 Evictions: 0 Overflows: 0 Memory Usage: 200kB │
│ -> Index Scan using date_dim_pkey on date_dim date_dim_1 (cost=0.29..0.32 rows=1 width=12) (actual time=0.002..0.002 rows=1.00 loops=5468) │
│ Index Cond: (d_date_sk = web_sales.ws_sold_date_sk) │
│ Index Searches: 5465 │
│ Buffers: shared hit=16397 │
│ -> Nested Loop (cost=0.00..25081.00 rows=1 width=210) (actual time=43808.287..3825536.966 rows=43.00 loops=1) │
│ Join Filter: (((ss1.ca_county)::text = (ss2.ca_county)::text) AND (CASE WHEN (ws1.web_sales > '0'::numeric) THEN (ws2.web_sales / ws1.web_sales) ELSE NULL::numeric END > CASE WHEN (ss1.store_sales > '0'::numeric) THEN (ss2.store_sales / ss1.store_sales) ELSE NULL::numeric END)) │
│ Rows Removed by Join Filter: 226832 │
│ Buffers: shared hit=7500 read=22936, temp read=4819 written=8505 │
│ -> Merge Join (cost=0.00..8360.31 rows=1 width=224) (actual time=1747.759..1760.887 rows=825.00 loops=1) │
│ Merge Cond: ((ss1.ca_county)::text = (ws1.ca_county)::text) │
│ Buffers: shared hit=7500 read=22936, temp read=4321 written=8505 │
│ -> CTE Scan on ss ss1 (cost=0.00..6562.93 rows=7 width=114) (actual time=1471.648..1477.297 rows=1647.00 loops=1) │
│ Filter: ((d_qoy = 1) AND (d_year = 1999)) │
│ Rows Removed by Filter: 33470 │
│ Storage: Memory Maximum Storage: 2635kB │
│ Buffers: shared hit=1278 read=16903, temp read=4321 written=8505 │
│ -> Materialize (cost=0.00..1797.36 rows=2 width=110) (actual time=275.335..280.952 rows=911.00 loops=1) │
│ Storage: Memory Maximum Storage: 17kB │
│ Buffers: shared hit=6222 read=6033 │
│ -> CTE Scan on ws ws1 (cost=0.00..1797.35 rows=2 width=110) (actual time=275.333..279.774 rows=911.00 loops=1) │
│ Filter: ((d_qoy = 1) AND (d_year = 1999)) │
│ Rows Removed by Filter: 22390 │
│ Storage: Memory Maximum Storage: 1700kB │
│ Buffers: shared hit=6222 read=6033 │
│ -> Nested Loop (cost=0.00..16720.65 rows=1 width=440) (actual time=5.913..4634.838 rows=275.00 loops=825) │
│ Join Filter: (((ss2.ca_county)::text = (ss3.ca_county)::text) AND (CASE WHEN (ws2.web_sales > '0'::numeric) THEN (ws3.web_sales / ws2.web_sales) ELSE NULL::numeric END > CASE WHEN (ss2.store_sales > '0'::numeric) THEN (ss3.store_sales / ss2.store_sales) ELSE NULL::numeric END)) │
│ Rows Removed by Join Filter: 1037001 │
│ Buffers: temp read=498 │
│ -> Merge Join (cost=0.00..8360.31 rows=1 width=220) (actual time=0.001..5.266 rows=844.00 loops=825) │
│ Merge Cond: ((ss2.ca_county)::text = (ws2.ca_county)::text) │
│ -> CTE Scan on ss ss2 (cost=0.00..6562.93 rows=7 width=110) (actual time=0.001..4.131 rows=1634.00 loops=825) │
│ Filter: ((d_year = 1999) AND (d_qoy = 2)) │
│ Rows Removed by Filter: 33468 │
│ Storage: Memory Maximum Storage: 2635kB │
│ -> Materialize (cost=0.00..1797.36 rows=2 width=110) (actual time=0.000..0.053 rows=925.00 loops=825) │
│ Storage: Memory Maximum Storage: 74kB │
│ -> CTE Scan on ws ws2 (cost=0.00..1797.35 rows=2 width=110) (actual time=0.001..2.784 rows=925.00 loops=1) │
│ Filter: ((d_year = 1999) AND (d_qoy = 2)) │
│ Rows Removed by Filter: 22382 │
│ Storage: Memory Maximum Storage: 1700kB │
│ -> Merge Join (cost=0.00..8360.31 rows=1 width=220) (actual time=0.002..5.383 rows=1229.00 loops=696300) │
│ Merge Cond: ((ss3.ca_county)::text = (ws3.ca_county)::text) │
│ Buffers: temp read=498 │
│ -> CTE Scan on ss ss3 (cost=0.00..6562.93 rows=7 width=110) (actual time=0.001..4.051 rows=1796.00 loops=696300) │
│ Filter: ((d_year = 1999) AND (d_qoy = 3)) │
│ Rows Removed by Filter: 33292 │
│ Storage: Memory Maximum Storage: 2635kB │
│ Buffers: temp read=498 │
│ -> Materialize (cost=0.00..1797.36 rows=2 width=110) (actual time=0.000..0.047 rows=1261.00 loops=696300) │
│ Storage: Memory Maximum Storage: 95kB │
│ -> CTE Scan on ws ws3 (cost=0.00..1797.35 rows=2 width=110) (actual time=0.001..74.725 rows=1261.00 loops=1) │
│ Filter: ((d_year = 1999) AND (d_qoy = 3)) │
│ Rows Removed by Filter: 22051 │
│ Storage: Memory Maximum Storage: 1700kB │
│ Planning: │
│ Buffers: shared hit=12 │
│ Planning Time: 4.951 ms │
│ Execution Time: 3825542.556 ms │
└──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
view thread (75+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: Eager aggregation, take 3
In-Reply-To: <CAFY6G8f0gY1PVQaBFA=8eAL=guZsOV-8YuWGnA2BtuS=ySBRbw@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox