explain.depesz.com

PostgreSQL's explain analyze made readable

Result: sfAs

Settings
# exclusive inclusive rows x rows loops node
1. 211.516 6,857.949 ↑ 20.0 5 1

HashAggregate (cost=212,042.60..212,043.85 rows=100 width=64) (actual time=6,856.074..6,857.949 rows=5 loops=1)

  • Group Key: m.market_code
2.          

CTE existing_spots

3. 0.387 20.329 ↓ 68.6 2,676 1

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

4. 0.928 19.942 ↓ 68.6 2,676 1

Sort (cost=7,091.76..7,091.86 rows=39 width=12) (actual time=19.827..19.942 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.047 19.014 ↓ 68.6 2,676 1

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

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

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

7. 2.083 6.484 ↓ 5.9 2,676 1

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

8. 1.725 1.725 ↓ 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.023..1.725 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.003..0.003 rows=1 loops=2,676)

  • Index Cond: ((request_id = 3704) AND (schedule_event_id = sc.schedule_event_id))
11. 2.651 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.025 0.025 ↓ 2.1 15 1

Seq Scan on channels c (cost=0.00..3.83 rows=7 width=2) (actual time=0.011..0.025 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. 159.888 6,590.287 ↓ 1.2 33,985 1

GroupAggregate (cost=175,434.02..204,089.22 rows=27,487 width=313) (actual time=157.226..6,590.287 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.424 143.174 ↓ 1.2 33,985 1

Sort (cost=175,434.02..175,502.74 rows=27,487 width=101) (actual time=136.481..143.174 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 88.750 ↓ 1.2 33,985 1

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

  • Workers Planned: 2
  • Workers Launched: 2
17. 2.829 96.233 ↑ 1.0 11,328 3

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

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

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

19. 2.707 11.230 ↓ 1.4 11,328 3

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

  • Hash Cond: (r.channel_id = c_1.channel_id)
20. 8.308 8.308 ↑ 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.023..8.308 rows=11,328 loops=3)

  • Index Cond: (request_id = 3704)
21. 0.006 0.215 ↑ 1.0 20 3

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

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

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

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

Bitmap Heap Scan on demographic_audience a (cost=4.37..12.94 rows=5 width=8) (actual time=0.111..0.141 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.102 0.102 ↓ 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.102..0.102 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.003 0.037 ↑ 1.0 1 3

Hash (cost=5.38..5.38 rows=1 width=10) (actual time=0.037..0.037 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.016..0.034 rows=1 loops=3)

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

SubPlan (for GroupAggregate)

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

CTE Scan on existing_spots x (cost=0.00..0.98 rows=1 width=32) (actual time=0.182..0.185 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. 8.201 6,646.433 ↓ 2.5 33,985 1

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

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

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

34. 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
35. 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.008..0.008 rows=5 loops=1)

Planning time : 1.466 ms
Execution time : 6,862.347 ms