explain.depesz.com

PostgreSQL's explain analyze made readable

Result: lXxq

Settings
# exclusive inclusive rows x rows loops node
1. 3,756.884 242,718.881 ↑ 804.0 3,757,314 1

GroupAggregate (cost=994,970,427.51..1,146,017,866.26 rows=3,020,948,775 width=56) (actual time=236,199.358..242,718.881 rows=3,757,314 loops=1)

  • Group Key: (COALESCE(dd.id, '0'::double precision)), (COALESCE(u.id_user)), (COALESCE(dr.id_region, 0)), (COALESCE(b.id_media, 0)), (COALESCE(b.id_group, 0)), (COALESCE(b.id_label, 0)), (COALESCE(dp.id_playlist, 0)), (COALESCE(ss.id_stream_source, 0)), (COALESCE(os.id_os, 0)), (COALESCE(drt.id_retailer_track, 0))
2. 8,431.411 238,961.997 ↑ 678.4 4,452,747 1

Sort (cost=994,970,427.51..1,002,522,799.45 rows=3,020,948,775 width=84) (actual time=236,199.333..238,961.997 rows=4,452,747 loops=1)

  • Sort Key: (COALESCE(dd.id, '0'::double precision)), (COALESCE(u.id_user)), (COALESCE(dr.id_region, 0)), (COALESCE(b.id_media, 0)), (COALESCE(b.id_group, 0)), (COALESCE(b.id_label, 0)), (COALESCE(dp.id_playlist, 0)), (COALESCE(ss.id_stream_source, 0)), (COALESCE(os.id_os, 0)), (COALESCE(drt.id_retailer_track, 0))
  • Sort Method: external merge Disk: 295904kB
3. 5,495.251 230,530.586 ↑ 678.4 4,452,747 1

Hash Left Join (cost=34,329,471.69..312,776,302.63 rows=3,020,948,775 width=84) (actual time=215,998.761..230,530.586 rows=4,452,747 loops=1)

  • Hash Cond: ((a.track_id)::text = (drt.track_src)::text)
4. 2,500.102 223,820.646 ↑ 678.4 4,452,747 1

Hash Left Join (cost=34,195,077.25..107,952,163.57 rows=3,020,948,775 width=190) (actual time=214,783.408..223,820.646 rows=4,452,747 loops=1)

  • Hash Cond: ((a.os)::text = (os.os)::text)
5. 2,271.679 221,320.520 ↑ 678.4 4,452,747 1

Merge Join (cost=34,195,075.98..94,811,035.13 rows=3,020,948,775 width=304) (actual time=214,783.361..221,320.520 rows=4,452,747 loops=1)

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

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

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

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

8. 2,376.441 219,018.175 ↑ 17.7 4,452,747 1

Materialize (cost=34,194,405.29..34,588,219.50 rows=78,762,841 width=330) (actual time=214,749.565..219,018.175 rows=4,452,747 loops=1)

9. 4,286.563 216,641.734 ↑ 17.7 4,452,747 1

Sort (cost=34,194,405.29..34,391,312.39 rows=78,762,841 width=330) (actual time=214,749.562..216,641.734 rows=4,452,747 loops=1)

  • Sort Key: a.date_trend
  • Sort Method: external merge Disk: 349592kB
10. 5,354.831 212,355.171 ↑ 17.7 4,452,747 1

Merge Join (cost=4,962,704.66..6,557,970.23 rows=78,762,841 width=330) (actual time=182,319.832..212,355.171 rows=4,452,747 loops=1)

  • Merge Cond: (((b.isrc)::text = (a.media_id)::text) AND ((b.upc)::text = (ltrim((a.upc)::text, '0'::text))))
11. 60,003.857 64,450.748 ↓ 3.8 5,544,284 1

Sort (cost=360,213.21..363,868.77 rows=1,462,226 width=248) (actual time=46,944.179..64,450.748 rows=5,544,284 loops=1)

  • Sort Key: b.isrc, b.upc
  • Sort Method: external merge Disk: 250984kB
12. 3,029.215 4,446.891 ↓ 3.8 5,544,413 1

Hash Join (cost=3,601.79..89,105.70 rows=1,462,226 width=248) (actual time=22.138..4,446.891 rows=5,544,413 loops=1)

  • Hash Cond: (b.id_label = dl.id_label)
13. 1,395.706 1,395.706 ↓ 4.1 6,083,998 1

Seq Scan on lookup_song b (cost=0.00..65,370.75 rows=1,469,575 width=248) (actual time=0.007..1,395.706 rows=6,083,998 loops=1)

14. 6.737 21.970 ↑ 3.8 24,629 1

Hash (cost=2,435.32..2,435.32 rows=93,317 width=4) (actual time=21.970..21.970 rows=24,629 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 1890kB
15. 15.233 15.233 ↑ 3.8 24,629 1

Seq Scan on dim_label dl (cost=0.00..2,435.32 rows=93,317 width=4) (actual time=0.006..15.233 rows=24,629 loops=1)

  • Filter: ((label_name)::text <> 'Reverb Nation'::text)
  • Rows Removed by Filter: 55890
16. 2,140.562 142,549.592 ↓ 2.1 4,456,882 1

Materialize (cost=4,602,491.46..4,613,264.46 rows=2,154,601 width=554) (actual time=135,316.393..142,549.592 rows=4,456,882 loops=1)

17. 18,986.068 140,409.030 ↓ 2.1 4,456,882 1

Sort (cost=4,602,491.46..4,607,877.96 rows=2,154,601 width=554) (actual time=135,316.388..140,409.030 rows=4,456,882 loops=1)

  • Sort Key: a.media_id, (ltrim((a.upc)::text, '0'::text))
  • Sort Method: external merge Disk: 459968kB
18. 11,508.897 121,422.962 ↓ 2.1 4,456,882 1

Hash Left Join (cost=896,949.37..3,991,838.32 rows=2,154,601 width=554) (actual time=18,639.409..121,422.962 rows=4,456,882 loops=1)

  • Hash Cond: (a.user_id = (u.user_src)::text)
19. 7,302.615 91,534.968 ↓ 2.1 4,456,882 1

Hash Left Join (cost=36,277.65..2,447,379.58 rows=2,154,601 width=582) (actual time=246.602..91,534.968 rows=4,456,882 loops=1)

  • Hash Cond: ((a.playlist)::text = (dp.playlist_src)::text)
20. 4,033.690 83,986.882 ↓ 2.1 4,456,882 1

Hash Left Join (cost=1.77..1,768,125.93 rows=2,154,601 width=1,094) (actual time=0.893..83,986.882 rows=4,456,882 loops=1)

  • Hash Cond: ((a.stream_source)::text = (ss.stream_source)::text)
21. 54,403.702 79,953.167 ↓ 2.1 4,456,882 1

Nested Loop Left Join (cost=0.52..1,758,859.90 rows=2,154,601 width=1,208) (actual time=0.850..79,953.167 rows=4,456,882 loops=1)

22. 7,721.937 7,721.937 ↓ 2.1 4,456,882 1

Seq Scan on stg_sp_trends a (cost=0.00..568,424.85 rows=2,154,601 width=1,334) (actual time=0.736..7,721.937 rows=4,456,882 loops=1)

  • Filter: ((playlist)::text <> ''::text)
  • Rows Removed by Filter: 14510391
23. 17,827.528 17,827.528 ↑ 1.0 1 4,456,882

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=4,456,882)

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

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
25. 0.011 0.011 ↑ 1.0 11 1

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

26. 136.980 245.471 ↓ 1.0 364,694 1

Hash (cost=7,518.39..7,518.39 rows=364,439 width=520) (actual time=245.471..245.471 rows=364,694 loops=1)

  • Buckets: 131072 Batches: 4 Memory Usage: 8703kB
27. 108.491 108.491 ↓ 1.0 364,694 1

Seq Scan on dim_playlist dp (cost=0.00..7,518.39 rows=364,439 width=520) (actual time=1.217..108.491 rows=364,694 loops=1)

28. 10,494.993 18,379.097 ↓ 2.5 25,928,115 1

Hash (cost=422,533.32..422,533.32 rows=10,243,232 width=222) (actual time=18,379.097..18,379.097 rows=25,928,115 loops=1)

  • Buckets: 262144 Batches: 64 Memory Usage: 41948kB
29. 7,884.104 7,884.104 ↓ 2.5 25,928,115 1

Seq Scan on dim_users u (cost=0.00..422,533.32 rows=10,243,232 width=222) (actual time=12.688..7,884.104 rows=25,928,115 loops=1)

30. 0.008 0.024 ↑ 1.1 11 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
31. 0.016 0.016 ↑ 1.0 12 1

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

32. 718.663 1,214.689 ↓ 1.0 2,235,187 1

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

  • Buckets: 262144 Batches: 16 Memory Usage: 10042kB
33. 496.026 496.026 ↓ 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.007..496.026 rows=2,235,187 loops=1)

Planning time : 2.894 ms
Execution time : 243,880.889 ms