explain.depesz.com

PostgreSQL's explain analyze made readable

Result: MlLn

Settings
# exclusive inclusive rows x rows loops node
1. 1.104 199,927.460 ↑ 1.0 1 1

Aggregate (cost=1,388,171.74..1,388,171.75 rows=1 width=32) (actual time=199,927.460..199,927.460 rows=1 loops=1)

2. 3.651 199,926.356 ↓ 401.0 401 1

Group (cost=1,369,172.43..1,388,171.72 rows=1 width=8) (actual time=4,284.075..199,926.356 rows=401 loops=1)

  • Group Key: d.reveal_deal_id
3.          

Initplan (for Group)

4. 0.069 0.069 ↑ 1.0 1 1

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

5. 0.003 0.003 ↑ 1.0 1 1

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

6. 84,599.872 199,922.633 ↓ 65,431.0 65,431 1

Nested Loop (cost=1,369,172.39..1,388,171.68 rows=1 width=8) (actual time=4,284.074..199,922.633 rows=65,431 loops=1)

  • Join Filter: (s.deal_sk = d.deal_sk)
  • Rows Removed by Join Filter: 1,341,820,133
7. 42.211 42.211 ↓ 1.0 19,962 1

Index Scan using idx_dl_rdlid on dim_deal d (cost=0.29..7,572.65 rows=19,898 width=16) (actual time=0.036..42.211 rows=19,962 loops=1)

  • Filter: (is_guaranteed = 1)
  • Rows Removed by Filter: 7,121
8. 111,034.857 115,280.550 ↓ 67,222.0 67,222 19,962

Materialize (cost=1,369,172.10..1,380,300.57 rows=1 width=24) (actual time=0.199..5.775 rows=67,222 loops=19,962)

9. 49.061 4,245.693 ↓ 67,222.0 67,222 1

Merge Join (cost=1,369,172.10..1,380,300.56 rows=1 width=24) (actual time=3,931.089..4,245.693 rows=67,222 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: 268,670
10. 10.984 2,580.601 ↓ 2.6 22,640 1

Finalize GroupAggregate (cost=1,298,674.45..1,298,934.01 rows=8,652 width=16) (actual time=2,563.767..2,580.601 rows=22,640 loops=1)

  • Group Key: fs.deal_sk
11. 19.901 2,569.617 ↓ 2.9 50,940 1

Sort (cost=1,298,674.45..1,298,717.71 rows=17,304 width=16) (actual time=2,563.763..2,569.617 rows=50,940 loops=1)

  • Sort Key: fs.deal_sk
  • Sort Method: quicksort Memory: 3,754kB
12. 19.125 2,549.716 ↓ 2.9 50,945 1

Gather (cost=1,295,639.43..1,297,456.35 rows=17,304 width=16) (actual time=2,531.372..2,549.716 rows=50,945 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
13. 647.828 2,530.591 ↓ 2.0 16,982 3 / 3

Partial HashAggregate (cost=1,294,639.43..1,294,725.95 rows=8,652 width=16) (actual time=2,527.325..2,530.591 rows=16,982 loops=3)

  • Group Key: fs.deal_sk
14. 1,882.763 1,882.763 ↑ 1.3 3,345,528 3 / 3

Parallel Seq Scan on fact_spot fs (cost=0.00..1,261,827.08 rows=4,374,980 width=12) (actual time=0.005..1,882.763 rows=3,345,528 loops=3)

  • Filter: (is_active_record = 1)
  • Rows Removed by Filter: 2,056,293
15. 46.379 1,616.031 ↓ 67,178.4 335,892 1

Materialize (cost=70,497.65..81,258.29 rows=5 width=24) (actual time=1,367.180..1,616.031 rows=335,892 loops=1)

16. 51.993 1,569.652 ↓ 67,178.4 335,892 1

Merge Join (cost=70,497.65..81,258.28 rows=5 width=24) (actual time=1,367.170..1,569.652 rows=335,892 loops=1)

  • Merge Cond: (fdl.deal_sk = s.deal_sk)
17. 41.451 363.038 ↑ 18.8 16,394 1

GroupAggregate (cost=64,987.84..71,905.04 rows=307,431 width=20) (actual time=292.123..363.038 rows=16,394 loops=1)

  • Group Key: fdl.deal_sk, t.team_id
18. 140.763 321.587 ↑ 1.2 257,125 1

Sort (cost=64,987.84..65,756.42 rows=307,431 width=20) (actual time=292.113..321.587 rows=257,125 loops=1)

  • Sort Key: fdl.deal_sk, t.team_id
  • Sort Method: external merge Disk: 8,704kB
19. 92.307 180.824 ↑ 1.0 296,216 1

Hash Join (cost=10.05..30,658.65 rows=307,431 width=20) (actual time=0.087..180.824 rows=296,216 loops=1)

  • Hash Cond: (fdl.team_sk = t.team_sk)
20. 88.447 88.447 ↑ 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.007..88.447 rows=296,216 loops=1)

  • Filter: ((is_active_record = 1) AND (deal_status_sk = 1))
  • Rows Removed by Filter: 346,911
21. 0.033 0.070 ↑ 1.0 269 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 19kB
22. 0.037 0.037 ↑ 1.0 269 1

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

23. 34.006 1,154.621 ↓ 368,039.0 368,039 1

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

24. 160.255 1,120.615 ↓ 368,039.0 368,039 1

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

  • Sort Key: s.deal_sk
  • Sort Method: external merge Disk: 6,512kB
25. 37.587 960.360 ↓ 368,039.0 368,039 1

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

26. 0.019 0.019 ↑ 1.0 1 1

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

  • Filter: (lower((division_code)::text) = 'ent'::text)
  • Rows Removed by Filter: 11
27. 34.403 922.754 ↓ 184,019.5 368,039 1

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

28. 0.041 0.041 ↑ 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.031..0.041 rows=2 loops=1)

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

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

30. 0.306 0.306 ↑ 2.0 1 2

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

  • Filter: (lower((network_cd)::text) = 'tbs'::text)
  • Rows Removed by Filter: 350
31. 790.244 790.244 ↓ 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.103..395.122 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 : 6.853 ms
Execution time : 199,932.239 ms