explain.depesz.com

PostgreSQL's explain analyze made readable

Result: HRR4

Settings
# exclusive inclusive rows x rows loops node
1. 32,124.140 1,321,087.211 ↓ 3,331.0 14,876,252 1

GroupAggregate (cost=4,723,900.65..4,724,503.56 rows=4,466 width=88) (actual time=1,274,397.775..1,321,087.211 rows=14,876,252 loops=1)

  • Group Key: (COALESCE(dd.id, '0'::double precision)), (COALESCE(du.id_user, 0)), (COALESCE(dr.id_region, 0)), (COALESCE(b.id_media, 0)), (COALESCE(b.id_group, 0)), (COALESCE(b.id_label, 0)), ((COALESCE(LEAST(GREATEST((date_part('year'::text, (('now'::cstring)::date)::timestamp without time zone) - CASE WHEN ((a.birth_year)::text ~ '[0-9]{4}'::text) THEN ((a.birth_year)::integer)::double precision ELSE date_part('year'::text, (('now'::cstring)::date)::timestamp without time zone) END), '0'::double precision), '100'::double precision), '0'::double precision))::integer), (COALESCE(ss.id_stream_source, 0)), (COALESCE(os.id_os, 0)), (COALESCE(drt.id_retailer_track, 0))
2. 46,785.513 1,288,963.071 ↓ 4,242.4 18,946,391 1

Sort (cost=4,723,900.65..4,723,911.82 rows=4,466 width=61) (actual time=1,274,397.741..1,288,963.071 rows=18,946,391 loops=1)

  • Sort Key: (COALESCE(dd.id, '0'::double precision)), (COALESCE(du.id_user, 0)), (COALESCE(dr.id_region, 0)), (COALESCE(b.id_media, 0)), (COALESCE(b.id_group, 0)), (COALESCE(b.id_label, 0)), ((COALESCE(LEAST(GREATEST((date_part('year'::text, (('now'::cstring)::date)::timestamp without time zone) - CASE WHEN ((a.birth_year)::text ~ '[0-9]{4}'::text) THEN ((a.birth_year)::integer)::double precision ELSE date_part('year'::text, (('now'::cstring)::date)::timestamp without time zone) END), '0'::double precision), '100'::double precision), '0'::double precision))::integer), (COALESCE(ss.id_stream_source, 0)), (COALESCE(os.id_os, 0)), (COALESCE(drt.id_retailer_track, 0))
  • Sort Method: external merge Disk: 1398784kB
3. 87,396.693 1,242,177.558 ↓ 4,242.4 18,946,391 1

Nested Loop Semi Join (cost=4,136,980.92..4,723,629.91 rows=4,466 width=61) (actual time=90,234.211..1,242,177.558 rows=18,946,391 loops=1)

4. 51,637.980 1,116,862.003 ↓ 4,244.3 18,959,431 1

Hash Join (cost=4,136,980.63..4,722,030.71 rows=4,467 width=54) (actual time=90,232.593..1,116,862.003 rows=18,959,431 loops=1)

  • Hash Cond: ((a.date_trend)::text = to_char((dd.dt_date)::timestamp with time zone, 'YYYYmmdd'::text))
5. 30,526.068 1,065,211.240 ↓ 163,443.4 18,959,431 1

Nested Loop Left Join (cost=4,136,709.03..4,721,380.69 rows=116 width=55) (actual time=90,219.776..1,065,211.240 rows=18,959,431 loops=1)

6. 14,845.909 901,969.155 ↓ 163,443.4 18,959,431 1

Hash Left Join (cost=4,136,708.60..4,721,327.28 rows=116 width=74) (actual time=90,217.392..901,969.155 rows=18,959,431 loops=1)

  • Hash Cond: ((a.os)::text = (os.os)::text)
7. 19,745.610 887,122.668 ↓ 163,443.4 18,959,431 1

Hash Left Join (cost=4,136,707.33..4,721,324.42 rows=116 width=76) (actual time=90,216.801..887,122.668 rows=18,959,431 loops=1)

  • Hash Cond: ((a.stream_source)::text = (ss.stream_source)::text)
8. 273,994.894 867,376.519 ↓ 163,443.4 18,959,431 1

Nested Loop Left Join (cost=4,136,706.09..4,721,321.57 rows=116 width=83) (actual time=90,216.242..867,376.519 rows=18,959,431 loops=1)

9. 21,845.505 498,584.470 ↓ 163,443.4 18,959,431 1

Nested Loop Left Join (cost=4,136,705.56..4,721,257.48 rows=116 width=87) (actual time=90,212.709..498,584.470 rows=18,959,431 loops=1)

10. 20,016.281 192,347.500 ↓ 163,443.4 18,959,431 1

Merge Join (cost=4,136,705.00..4,721,180.78 rows=116 width=148) (actual time=90,209.093..192,347.500 rows=18,959,431 loops=1)

  • Merge Cond: (((b.isrc)::text = (a.media_id)::text) AND ((b.upc)::text = (ltrim((a.upc)::text, '0'::text))))
11. 9,405.884 9,405.884 ↑ 1.0 6,083,765 1

Index Scan using idx_ods_lookup_song_isrc_upc on lookup_song b (cost=0.56..303,853.20 rows=6,083,998 width=37) (actual time=4.977..9,405.884 rows=6,083,765 loops=1)

12. 18,122.130 162,925.335 ↑ 1.0 18,967,273 1

Materialize (cost=4,135,285.78..4,230,122.92 rows=18,967,428 width=162) (actual time=89,643.355..162,925.335 rows=18,967,273 loops=1)

13. 131,774.316 144,803.205 ↑ 1.0 18,967,273 1

Sort (cost=4,135,285.78..4,182,704.35 rows=18,967,428 width=162) (actual time=89,643.348..144,803.205 rows=18,967,273 loops=1)

  • Sort Key: a.media_id, (ltrim((a.upc)::text, '0'::text))
  • Sort Method: external merge Disk: 3455072kB
14. 13,028.889 13,028.889 ↑ 1.0 18,967,273 1

Seq Scan on stg_sp_trends a (cost=0.00..731,031.28 rows=18,967,428 width=162) (actual time=1.394..13,028.889 rows=18,967,273 loops=1)

15. 284,391.465 284,391.465 ↑ 1.0 1 18,959,431

Index Scan using idx_dim_users_unq on dim_users du (cost=0.56..0.65 rows=1 width=222) (actual time=0.015..0.015 rows=1 loops=18,959,431)

  • Index Cond: (a.user_id = (user_src)::text)
16. 94,797.155 94,797.155 ↑ 1.0 1 18,959,431

Index Scan using idx_dim_region_unq on dim_region dr (cost=0.52..0.54 rows=1 width=9) (actual time=0.004..0.005 rows=1 loops=18,959,431)

  • Index Cond: (((a.country_code)::text = (country_code)::text) AND ((COALESCE(isnumeric((a.region)::text), '0'::character varying))::text = (region_code)::text))
17. 0.017 0.539 ↑ 1.1 10 1

Hash (cost=1.11..1.11 rows=11 width=222) (actual time=0.539..0.539 rows=10 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
18. 0.522 0.522 ↑ 1.0 11 1

Seq Scan on dim_stream_source ss (cost=0.00..1.11 rows=11 width=222) (actual time=0.517..0.522 rows=11 loops=1)

19. 0.008 0.578 ↑ 1.1 11 1

Hash (cost=1.12..1.12 rows=12 width=222) (actual time=0.578..0.578 rows=11 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
20. 0.570 0.570 ↑ 1.0 12 1

Seq Scan on dim_os os (cost=0.00..1.12 rows=12 width=222) (actual time=0.568..0.570 rows=12 loops=1)

21. 132,716.017 132,716.017 ↑ 1.0 1 18,959,431

Index Scan using idx_dim_retailer_track_unq on dim_retailer_track drt (cost=0.43..0.45 rows=1 width=27) (actual time=0.007..0.007 rows=1 loops=18,959,431)

  • Index Cond: ((a.track_id)::text = (track_src)::text)
22. 7.763 12.783 ↑ 1.0 7,671 1

Hash (cost=175.71..175.71 rows=7,671 width=12) (actual time=12.783..12.783 rows=7,671 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 394kB
23. 5.020 5.020 ↑ 1.0 7,671 1

Seq Scan on dim_date dd (cost=0.00..175.71 rows=7,671 width=12) (actual time=0.836..5.020 rows=7,671 loops=1)

24. 37,918.862 37,918.862 ↑ 1.0 1 18,959,431

Index Scan using dim_label_test_pkey on dim_label (cost=0.29..0.31 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=18,959,431)

  • Index Cond: (id_label = b.id_label)
  • Filter: ((label_name)::text <> 'Reverb Nation'::text)
  • Rows Removed by Filter: 0
Planning time : 27.234 ms
Execution time : 1,325,218.552 ms