explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 2Jai

Settings
# exclusive inclusive rows x rows loops node
1. 24.825 7,036.006 ↑ 20.0 5 1

Sort (cost=212,047.17..212,047.42 rows=100 width=64) (actual time=7,033.862..7,036.006 rows=5 loops=1)

  • Sort Key: m.market_code
  • Sort Method: external merge Disk: 13192kB
2.          

CTE existing_spots

3. 0.401 20.234 ↓ 68.6 2,676 1

Unique (cost=7,091.76..7,092.15 rows=39 width=12) (actual time=19.719..20.234 rows=2,676 loops=1)

4. 0.895 19.833 ↓ 68.6 2,676 1

Sort (cost=7,091.76..7,091.86 rows=39 width=12) (actual time=19.718..19.833 rows=2,676 loops=1)

  • Sort Key: sc.schedule_event_id, s.spot_id, s.spot_type_id
  • Sort Method: quicksort Memory: 222kB
5. 2.042 18.938 ↓ 68.6 2,676 1

Nested Loop (cost=1.42..7,090.73 rows=39 width=12) (actual time=0.062..18.938 rows=2,676 loops=1)

  • Join Filter: (ir.channel_id = c.channel_id)
  • Rows Removed by Join Filter: 37464
6. 0.000 14.220 ↓ 10.8 2,676 1

Nested Loop (cost=1.42..7,060.85 rows=248 width=14) (actual time=0.045..14.220 rows=2,676 loops=1)

7. 2.118 6.492 ↓ 5.9 2,676 1

Nested Loop (cost=0.85..5,418.45 rows=457 width=12) (actual time=0.031..6.492 rows=2,676 loops=1)

8. 1.698 1.698 ↓ 5.9 2,676 1

Index Scan using spot_campaign_option_id_idx on spot s (cost=0.43..1,610.52 rows=457 width=8) (actual time=0.020..1.698 rows=2,676 loops=1)

  • Index Cond: (campaign_option_id = 764301)
9. 2.676 2.676 ↑ 1.0 1 2,676

Index Scan using spot_channel_spot_id_pkey on spot_channel sc (cost=0.43..8.32 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=2,676)

  • Index Cond: (spot_id = s.spot_id)
10. 8.028 8.028 ↑ 1.0 1 2,676

Index Scan using mediawise_inventory_rates_req_sch_event_id_idx on mediawise_inventory_rates ir (cost=0.56..3.58 rows=1 width=6) (actual time=0.002..0.003 rows=1 loops=2,676)

  • Index Cond: ((request_id = 3704) AND (schedule_event_id = sc.schedule_event_id))
11. 2.653 2.676 ↓ 2.1 15 2,676

Materialize (cost=0.00..3.86 rows=7 width=2) (actual time=0.000..0.001 rows=15 loops=2,676)

12. 0.023 0.023 ↓ 2.1 15 1

Seq Scan on channels c (cost=0.00..3.83 rows=7 width=2) (actual time=0.010..0.023 rows=15 loops=1)

  • Filter: ((active_program_channel = 1) AND ((mediawise_market_code)::text = ANY ('{0201,0301,0701,0801,0901}'::text[])))
  • Rows Removed by Filter: 29
13.          

CTE market_inventory

14. 179.717 6,745.133 ↓ 1.2 33,985 1

GroupAggregate (cost=175,434.02..204,089.22 rows=27,487 width=313) (actual time=156.107..6,745.133 rows=33,985 loops=1)

  • Group Key: r.spot_type_code, r.schedule_event_id, r.station_code, r.week_commencing, r.start_time_sec, r.end_time_sec, r.days_zero_one, r.program_name, r.event_grade, r.free_time, r.is_available, r.final_rate, c_1.mediawise_market_code
15. 54.707 142.251 ↓ 1.2 33,985 1

Sort (cost=175,434.02..175,502.74 rows=27,487 width=101) (actual time=135.405..142.251 rows=33,985 loops=1)

  • Sort Key: r.spot_type_code, r.schedule_event_id, r.station_code, r.week_commencing, r.start_time_sec, r.end_time_sec, r.days_zero_one, r.program_name, r.event_grade, r.free_time, r.is_available, r.final_rate, c_1.mediawise_market_code
  • Sort Method: external sort Disk: 4072kB
16. 0.000 87.544 ↓ 1.2 33,985 1

Gather (cost=1,023.87..173,407.34 rows=27,487 width=101) (actual time=1.340..87.544 rows=33,985 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
17. 2.909 95.699 ↑ 1.0 11,328 3

Hash Left Join (cost=23.87..169,658.64 rows=11,453 width=101) (actual time=0.378..95.699 rows=11,328 loops=3)

  • Hash Cond: (pr.demo_id = dem.demo_id)
18. 81.468 92.752 ↑ 1.0 11,328 3

Nested Loop Left Join (cost=18.48..169,495.78 rows=11,453 width=95) (actual time=0.325..92.752 rows=11,328 loops=3)

19. 2.728 11.277 ↓ 1.4 11,328 3

Hash Join (cost=17.92..107,003.01 rows=8,254 width=89) (actual time=0.278..11.277 rows=11,328 loops=3)

  • Hash Cond: (r.channel_id = c_1.channel_id)
20. 8.328 8.328 ↑ 1.6 11,328 3

Parallel Index Scan using mediawise_inventory_rates_req_sch_event_id_idx on mediawise_inventory_rates r (cost=0.56..106,835.02 rows=18,158 width=82) (actual time=0.022..8.328 rows=11,328 loops=3)

  • Index Cond: (request_id = 3704)
21. 0.007 0.221 ↑ 1.0 20 3

Hash (cost=17.11..17.11 rows=20 width=11) (actual time=0.221..0.221 rows=20 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
22. 0.029 0.214 ↑ 1.0 20 3

Hash Right Join (cost=8.33..17.11 rows=20 width=11) (actual time=0.192..0.214 rows=20 loops=3)

  • Hash Cond: (a.market_id = c_1.market_id)
23. 0.034 0.142 ↓ 1.2 6 3

Bitmap Heap Scan on demographic_audience a (cost=4.37..12.94 rows=5 width=8) (actual time=0.118..0.142 rows=6 loops=3)

  • Recheck Cond: ((demographic_id = 729) AND (to_char((CURRENT_DATE)::timestamp with time zone, 'YYYYMMDD'::text) >= (effective_date)::text) AND (to_char((CURRENT_DATE)::timestamp with time zone, 'YYYYMMDD'::text) <= (until_date)::text))
  • Heap Blocks: exact=6
24. 0.108 0.108 ↓ 1.2 6 3

Bitmap Index Scan on demographic_audience_demographic_id_idx (cost=0.00..4.37 rows=5 width=0) (actual time=0.108..0.108 rows=6 loops=3)

  • Index Cond: ((demographic_id = 729) AND (to_char((CURRENT_DATE)::timestamp with time zone, 'YYYYMMDD'::text) >= (effective_date)::text) AND (to_char((CURRENT_DATE)::timestamp with time zone, 'YYYYMMDD'::text) <= (until_date)::text))
25. 0.006 0.043 ↑ 1.0 20 3

Hash (cost=3.71..3.71 rows=20 width=9) (actual time=0.043..0.043 rows=20 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
26. 0.037 0.037 ↑ 1.0 20 3

Seq Scan on channels c_1 (cost=0.00..3.71 rows=20 width=9) (actual time=0.019..0.037 rows=20 loops=3)

  • Filter: ((mediawise_market_code)::text = ANY ('{0201,0301,0701,0801,0901}'::text[]))
  • Rows Removed by Filter: 24
27. 0.007 0.007 ↑ 1.0 1 33,985

Index Scan using afora_predicted_ratings_pk on afora_predicted_ratings pr (cost=0.56..7.57 rows=1 width=14) (actual time=0.007..0.007 rows=1 loops=33,985)

  • Index Cond: ((schedule_event_id = r.schedule_event_id) AND (demo_id = 729))
28. 0.004 0.038 ↑ 1.0 1 3

Hash (cost=5.38..5.38 rows=1 width=10) (actual time=0.038..0.038 rows=1 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
29. 0.034 0.034 ↑ 1.0 1 3

Seq Scan on demos dem (cost=0.00..5.38 rows=1 width=10) (actual time=0.015..0.034 rows=1 loops=3)

  • Filter: (demo_id = 729)
  • Rows Removed by Filter: 189
30.          

SubPlan (for GroupAggregate)

31. 6,423.165 6,423.165 ↓ 0.0 0 33,985

CTE Scan on existing_spots x (cost=0.00..0.98 rows=1 width=32) (actual time=0.187..0.189 rows=0 loops=33,985)

  • Filter: ((r.schedule_event_id = schedule_event_id) AND (r.spot_type_code = spot_type_id))
  • Rows Removed by Filter: 2676
32. 210.206 7,011.181 ↑ 20.0 5 1

HashAggregate (cost=861.23..862.48 rows=100 width=64) (actual time=7,009.345..7,011.181 rows=5 loops=1)

  • Group Key: m.market_code
33. 8.596 6,800.975 ↓ 2.5 33,985 1

Hash Join (cost=2.25..792.51 rows=13,744 width=98) (actual time=156.141..6,800.975 rows=33,985 loops=1)

  • Hash Cond: ((inv.mediawise_market_code)::text = m.market_code)
34. 6,792.369 6,792.369 ↓ 1.2 33,985 1

CTE Scan on market_inventory inv (cost=0.00..549.74 rows=27,487 width=108) (actual time=156.118..6,792.369 rows=33,985 loops=1)

35. 0.002 0.010 ↑ 20.0 5 1

Hash (cost=1.00..1.00 rows=100 width=32) (actual time=0.010..0.010 rows=5 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
36. 0.008 0.008 ↑ 20.0 5 1

Function Scan on unnest m (cost=0.00..1.00 rows=100 width=32) (actual time=0.007..0.008 rows=5 loops=1)

Planning time : 1.662 ms
Execution time : 7,042.746 ms