Re: Eager aggregation, take 3

Wed, 01 Oct 2025 16:55:23 -0700 

[ 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                                              
                                                                                
                                                                                
                                                         │
└──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

Reply via email to