explain.depesz.com

PostgreSQL's explain analyze made readable

Result: tKGu

Settings
# exclusive inclusive rows x rows loops node
1. 32,327.030 717,909.256 ↑ 898.3 14,876,252 1

GroupAggregate (cost=6,087,278,846.51..7,891,386,281.68 rows=13,363,758,779 width=88) (actual time=670,931.127..717,909.256 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. 39,270.252 685,582.226 ↑ 705.3 18,946,391 1

Sort (cost=6,087,278,846.51..6,120,688,243.46 rows=13,363,758,779 width=210) (actual time=670,931.097..685,582.226 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: 1398744kB
3. 38,860.063 646,311.974 ↑ 705.3 18,946,391 1

Merge Join (cost=167,403,882.15..903,281,748.41 rows=13,363,758,779 width=210) (actual time=589,641.359..646,311.974 rows=18,946,391 loops=1)

  • Merge Cond: ((to_char((dd.dt_date)::timestamp with time zone, 'YYYYmmdd'::text)) = (a.date_trend)::text)
4. 22.675 31.072 ↑ 1.1 7,143 1

Sort (cost=670.69..689.87 rows=7,671 width=12) (actual time=29.539..31.072 rows=7,143 loops=1)

  • Sort Key: (to_char((dd.dt_date)::timestamp with time zone, 'YYYYmmdd'::text))
  • Sort Method: quicksort Memory: 792kB
5. 8.397 8.397 ↑ 1.0 7,671 1

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

6. 9,637.550 607,420.839 ↑ 18.4 18,946,391 1

Materialize (cost=167,403,211.46..169,145,325.75 rows=348,422,859 width=342) (actual time=589,607.392..607,420.839 rows=18,946,391 loops=1)

7. 18,876.272 597,783.289 ↑ 18.4 18,946,391 1

Sort (cost=167,403,211.46..168,274,268.60 rows=348,422,859 width=342) (actual time=589,607.389..597,783.289 rows=18,946,391 loops=1)

  • Sort Key: a.date_trend
  • Sort Method: external merge Disk: 1245784kB
8. 15,583.941 578,907.017 ↑ 18.4 18,946,391 1

Merge Right Join (cost=23,736,230.94..39,709,471.24 rows=348,422,859 width=342) (actual time=457,127.087..578,907.017 rows=18,946,391 loops=1)

  • Merge Cond: (((b.isrc)::text = (a.media_id)::text) AND ((b.upc)::text = (ltrim((a.upc)::text, '0'::text))))
  • Filter: (NOT (hashed SubPlan 1))
  • Rows Removed by Filter: 20882
9. 3,088.649 3,088.649 ↓ 4.1 6,083,765 1

Index Scan using idx_ods_lookup_song_isrc_upc on lookup_song b (cost=0.55..235,772.18 rows=1,469,575 width=248) (actual time=0.029..3,088.649 rows=6,083,765 loops=1)

10. 9,516.539 560,210.417 ↓ 1.0 18,967,273 1

Materialize (cost=23,733,793.89..23,828,630.25 rows=18,967,272 width=566) (actual time=457,065.735..560,210.417 rows=18,967,273 loops=1)

11. 139,568.686 550,693.878 ↓ 1.0 18,967,273 1

Sort (cost=23,733,793.89..23,781,212.07 rows=18,967,272 width=566) (actual time=457,065.733..550,693.878 rows=18,967,273 loops=1)

  • Sort Key: a.media_id, (ltrim((a.upc)::text, '0'::text))
  • Sort Method: external merge Disk: 1758568kB
12. 31,906.652 411,125.192 ↓ 1.0 18,967,273 1

Hash Left Join (cost=134,398.60..18,014,229.38 rows=18,967,272 width=566) (actual time=1,243.897..411,125.192 rows=18,967,273 loops=1)

  • Hash Cond: ((a.track_id)::text = (drt.track_src)::text)
13. 14,176.017 377,975.881 ↓ 1.0 18,967,273 1

Hash Left Join (cost=4.16..14,267,658.86 rows=18,967,272 width=680) (actual time=0.143..377,975.881 rows=18,967,273 loops=1)

  • Hash Cond: ((a.os)::text = (os.os)::text)
14. 16,346.932 363,799.851 ↓ 1.0 18,967,273 1

Hash Left Join (cost=2.89..14,185,149.96 rows=18,967,272 width=794) (actual time=0.126..363,799.851 rows=18,967,273 loops=1)

  • Hash Cond: ((a.stream_source)::text = (ss.stream_source)::text)
15. 214,114.731 347,452.902 ↓ 1.0 18,967,273 1

Nested Loop Left Join (cost=1.65..14,103,589.44 rows=18,967,272 width=908) (actual time=0.102..347,452.902 rows=18,967,273 loops=1)

16. 32,091.955 57,469.079 ↓ 1.0 18,967,273 1

Merge Right Join (cost=1.12..3,624,153.66 rows=18,967,272 width=1,034) (actual time=0.052..57,469.079 rows=18,967,273 loops=1)

  • Merge Cond: ((du.user_src)::text = a.user_id)
17. 14,861.376 14,861.376 ↓ 2.5 25,928,115 1

Index Scan using idx_dim_users_unq on dim_users du (cost=0.56..1,551,611.04 rows=10,243,232 width=222) (actual time=0.023..14,861.376 rows=25,928,115 loops=1)

18. 10,515.748 10,515.748 ↓ 1.0 18,967,273 1

Index Scan using idx_stg_sp_trends_user on stg_sp_trends a (cost=0.56..1,809,843.64 rows=18,967,272 width=1,062) (actual time=0.026..10,515.748 rows=18,967,273 loops=1)

19. 75,869.092 75,869.092 ↑ 1.0 1 18,967,273

Index Scan using idx_dim_region_unq on dim_region dr (cost=0.52..0.54 rows=1 width=54) (actual time=0.004..0.004 rows=1 loops=18,967,273)

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

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
21. 0.010 0.010 ↑ 1.0 11 1

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

22. 0.003 0.013 ↑ 1.1 11 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
23. 0.010 0.010 ↑ 1.0 12 1

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

24. 710.360 1,242.659 ↓ 1.0 2,235,187 1

Hash (cost=38,787.75..38,787.75 rows=2,235,175 width=222) (actual time=1,242.659..1,242.659 rows=2,235,187 loops=1)

  • Buckets: 262144 Batches: 16 Memory Usage: 10042kB
25. 532.299 532.299 ↓ 1.0 2,235,187 1

Seq Scan on dim_retailer_track drt (cost=0.00..38,787.75 rows=2,235,175 width=222) (actual time=0.010..532.299 rows=2,235,187 loops=1)

26.          

SubPlan (forMerge Right Join)

27. 24.010 24.010 ↓ 119.2 55,890 1

Seq Scan on dim_label (cost=0.00..2,435.32 rows=469 width=4) (actual time=0.652..24.010 rows=55,890 loops=1)

  • Filter: ((label_name)::text = 'Reverb Nation'::text)
  • Rows Removed by Filter: 24629
Planning time : 2.762 ms
Execution time : 722,025.641 ms