explain.depesz.com

PostgreSQL's explain analyze made readable

Result: lDuh

Settings
# exclusive inclusive rows x rows loops node
1. 1.008 241,533.224 ↑ 1.0 1 1

Aggregate (cost=1,400,472.30..1,400,472.31 rows=1 width=32) (actual time=241,533.224..241,533.224 rows=1 loops=1)

2. 3.445 241,532.216 ↓ 334.0 334 1

Group (cost=1,384,269.53..1,400,472.29 rows=1 width=8) (actual time=4,167.217..241,532.216 rows=334 loops=1)

  • Group Key: d.reveal_deal_id
3.          

Initplan (for Group)

4. 0.012 0.012 ↑ 1.0 1 1

Result (cost=0.00..0.02 rows=1 width=4) (actual time=0.011..0.012 rows=1 loops=1)

5. 0.002 0.002 ↑ 1.0 1 1

Result (cost=0.00..0.02 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=1)

6. 102,746.019 241,528.757 ↓ 60,801.0 60,801 1

Nested Loop (cost=1,384,269.49..1,400,472.24 rows=1 width=8) (actual time=4,167.215..241,528.757 rows=60,801 loops=1)

  • Join Filter: (s.deal_sk = d.deal_sk)
  • Rows Removed by Join Filter: 1,646,612,682
7. 36.529 36.529 ↓ 1.0 27,083 1

Index Scan using idx_dl_rdlid on dim_deal d (cost=0.29..7,505.10 rows=27,018 width=16) (actual time=0.028..36.529 rows=27,083 loops=1)

8. 134,893.079 138,746.209 ↓ 60,801.0 60,801 27,083

Materialize (cost=1,384,269.20..1,392,561.88 rows=1 width=24) (actual time=0.133..5.123 rows=60,801 loops=27,083)

9. 47.437 3,853.130 ↓ 60,801.0 60,801 1

Merge Join (cost=1,384,269.20..1,392,561.87 rows=1 width=24) (actual time=3,554.501..3,853.130 rows=60,801 loops=1)

  • Merge Cond: (fs.deal_sk = s.deal_sk)
  • Join Filter: ((LEAST((min(fs.broadcast_week_date_sk)), (min(fdl.proposal_start_date_sk))) <= $0) AND (GREATEST((max(fs.broadcast_week_date_sk)), (max(fdl.proposal_end_date_sk))) >= $1))
  • Rows Removed by Join Filter: 259,807
10. 10.830 2,164.617 ↓ 2.5 22,037 1

Finalize GroupAggregate (cost=1,320,498.73..1,320,758.29 rows=8,652 width=16) (actual time=2,148.054..2,164.617 rows=22,037 loops=1)

  • Group Key: fs.deal_sk
11. 18.959 2,153.787 ↓ 2.8 48,844 1

Sort (cost=1,320,498.73..1,320,541.99 rows=17,304 width=16) (actual time=2,148.050..2,153.787 rows=48,844 loops=1)

  • Sort Key: fs.deal_sk
  • Sort Method: quicksort Memory: 3,655kB
12. 21.476 2,134.828 ↓ 2.8 48,846 1

Gather (cost=1,317,463.71..1,319,280.63 rows=17,304 width=16) (actual time=2,114.819..2,134.828 rows=48,846 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
13. 388.758 2,113.352 ↓ 1.9 16,282 3 / 3

Partial HashAggregate (cost=1,316,463.71..1,316,550.23 rows=8,652 width=16) (actual time=2,110.196..2,113.352 rows=16,282 loops=3)

  • Group Key: fs.deal_sk
14. 1,724.594 1,724.594 ↑ 1.4 1,950,102 3 / 3

Parallel Seq Scan on fact_spot fs (cost=0.00..1,296,505.11 rows=2,661,147 width=12) (actual time=0.005..1,724.594 rows=1,950,102 loops=3)

  • Filter: ((is_active_record = 1) AND (order_status_sk = 3) AND (is_ud = 0))
  • Rows Removed by Filter: 3,451,719
15. 45.140 1,641.076 ↓ 106,869.3 320,608 1

Materialize (cost=63,770.47..71,695.36 rows=3 width=24) (actual time=1,406.162..1,641.076 rows=320,608 loops=1)

16. 51.225 1,595.936 ↓ 106,869.3 320,608 1

Merge Join (cost=63,770.47..71,695.35 rows=3 width=24) (actual time=1,406.156..1,595.936 rows=320,608 loops=1)

  • Merge Cond: (fdl.deal_sk = s.deal_sk)
17. 33.926 387.491 ↑ 23.9 9,492 1

GroupAggregate (cost=58,260.66..63,354.97 rows=226,414 width=20) (actual time=328.954..387.491 rows=9,492 loops=1)

  • Group Key: fdl.deal_sk, t.team_id
18. 118.661 353.565 ↑ 1.1 208,519 1

Sort (cost=58,260.66..58,826.69 rows=226,414 width=20) (actual time=328.945..353.565 rows=208,519 loops=1)

  • Sort Key: fdl.deal_sk, t.team_id
  • Sort Method: external merge Disk: 7,232kB
19. 43.464 234.904 ↓ 1.1 246,091 1

Hash Join (cost=2,238.50..33,478.22 rows=226,414 width=20) (actual time=10.703..234.904 rows=246,091 loops=1)

  • Hash Cond: (fdl.team_sk = t.team_sk)
20. 87.938 191.366 ↓ 1.1 246,091 1

Hash Join (cost=2,228.45..32,864.46 rows=226,414 width=20) (actual time=10.617..191.366 rows=246,091 loops=1)

  • Hash Cond: (fdl.deal_sk = rvdl.deal_sk)
21. 92.845 92.845 ↑ 1.0 296,216 1

Seq Scan on fact_deal_line fdl (cost=0.00..29,828.88 rows=307,431 width=20) (actual time=0.004..92.845 rows=296,216 loops=1)

  • Filter: ((is_active_record = 1) AND (deal_status_sk = 1))
  • Rows Removed by Filter: 346,911
22. 2.640 10.583 ↓ 1.0 19,962 1

Hash (cost=1,979.72..1,979.72 rows=19,898 width=8) (actual time=10.583..10.583 rows=19,962 loops=1)

  • Buckets: 32,768 Batches: 1 Memory Usage: 1,036kB
23. 7.943 7.943 ↓ 1.0 19,962 1

Seq Scan on dim_deal rvdl (cost=0.00..1,979.72 rows=19,898 width=8) (actual time=0.006..7.943 rows=19,962 loops=1)

  • Filter: (is_guaranteed = 1)
  • Rows Removed by Filter: 7,121
24. 0.033 0.074 ↑ 1.0 269 1

Hash (cost=6.69..6.69 rows=269 width=8) (actual time=0.074..0.074 rows=269 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 19kB
25. 0.041 0.041 ↑ 1.0 269 1

Seq Scan on dim_team t (cost=0.00..6.69 rows=269 width=8) (actual time=0.009..0.041 rows=269 loops=1)

26. 34.080 1,157.220 ↓ 368,039.0 368,039 1

Materialize (cost=5,509.81..5,509.82 rows=1 width=8) (actual time=1,077.173..1,157.220 rows=368,039 loops=1)

27. 156.079 1,123.140 ↓ 368,039.0 368,039 1

Sort (cost=5,509.81..5,509.81 rows=1 width=8) (actual time=1,077.167..1,123.140 rows=368,039 loops=1)

  • Sort Key: s.deal_sk
  • Sort Method: external merge Disk: 6,512kB
28. 35.994 967.061 ↓ 368,039.0 368,039 1

Nested Loop (cost=0.85..5,509.80 rows=1 width=8) (actual time=0.221..967.061 rows=368,039 loops=1)

29. 0.017 0.017 ↑ 1.0 1 1

Seq Scan on dim_division divs (cost=0.00..1.18 rows=1 width=4) (actual time=0.013..0.017 rows=1 loops=1)

  • Filter: (lower((division_code)::text) = 'ent'::text)
  • Rows Removed by Filter: 11
30. 34.924 931.050 ↓ 184,019.5 368,039 1

Nested Loop (cost=0.85..5,508.60 rows=2 width=12) (actual time=0.206..931.050 rows=368,039 loops=1)

31. 0.026 0.026 ↑ 1.0 2 1

Index Scan using dim_selling_title_selling_title_id_key on dim_selling_title st (cost=0.29..12.62 rows=2 width=4) (actual time=0.015..0.026 rows=2 loops=1)

  • Index Cond: (selling_title_id = ANY ('{20580,20581}'::integer[]))
32. 96.738 896.100 ↓ 686.6 184,020 2

Nested Loop (cost=0.56..2,745.31 rows=268 width=16) (actual time=0.117..448.050 rows=184,020 loops=2)

33. 0.312 0.312 ↑ 2.0 1 2

Seq Scan on dim_network n (cost=0.00..10.27 rows=2 width=4) (actual time=0.006..0.156 rows=1 loops=2)

  • Filter: (lower((network_cd)::text) = 'tbs'::text)
  • Rows Removed by Filter: 350
34. 799.050 799.050 ↓ 1,373.3 184,020 2

Index Scan using idx_fact_spot_matching_estimate on fact_spot s (cost=0.56..1,366.18 rows=134 width=20) (actual time=0.108..399.525 rows=184,020 loops=2)

  • Index Cond: ((division_sk = divs.division_sk) AND (network_sk = n.network_sk) AND (selling_title_sk = st.selling_title_sk))
  • Filter: ((is_active_record = 1) AND (order_status_sk = 3) AND (is_ud = 0))
  • Rows Removed by Filter: 318,053
Planning time : 4.694 ms
Execution time : 241,537.656 ms