explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 4VwM

Settings
# exclusive inclusive rows x rows loops node
1. 1,712.638 19,323.288 ↑ 1.0 20 1

Limit (cost=1,040,787.21..1,040,787.26 rows=20 width=1,532) (actual time=19,177.975..19,323.288 rows=20 loops=1)

  • '_', home_logo.logo_id) ELSE NULL::text END), events.event_status, events.event_time, (COALESCE(home.team_short, events.home_short)), (COALESCE(away.team_short, events.away_short)), (CASE WHEN (home.team_id IS NOT NULL) THEN home.team_city ELSE events.home_team END), home.team_name, (CASE WHEN (away.team_id IS NOT NULL) THEN away.team_city ELSE events.away_team END), away.team_name, users.user_id
  • rows=600,352 loops=3)
  • rows=600,352 loops=3)
  • JIT:
  • Functions: 277
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
  • Timing: Generation 29.156 ms, Inlining 199.932 ms, Optimization 2903.074 ms, Emission 2031.910 ms, Total 5164.073 ms
2. 4.319 17,610.650 ↑ 40,267.3 20 1

Sort (cost=1,040,787.21..1,042,800.58 rows=805,347 width=1,532) (actual time=17,465.340..17,610.650 rows=20 loops=1)

  • Sort Key: (CASE WHEN (chatter.chatter_reply_id IS NULL) THEN 0 ELSE 1 END), chatter.chatter_added DESC
  • Sort Method: top-N heapsort Memory: 56kB
3. 3.949 17,606.331 ↑ 337.4 2,387 1

Finalize GroupAggregate (cost=890,720.03..1,019,357.22 rows=805,347 width=1,532) (actual time=17,390.819..17,606.331 rows=2,387 loops=1)

  • Group Key: chatter.chatter_id, images.image_file, (CASE WHEN (away_logo.logo_id IS NOT NULL) THEN concat(away_logo.logo_sport, '_', away_logo.logo_id) ELSE NULL::text END), (CASE WHEN (home_logo.logo_id IS NOT NULL) THEN concat(home_logo.logo_sport, '_', home_logo.logo_id) ELSE NULL::text END), events.event_status, events.event_time, (COALESCE(home.team_short, events.home_short)), (COALESCE(away.team_short, events.away_short)), (CASE WHEN (home.team_id IS NOT NULL) THEN home.team_city ELSE events.home_team END), home.team_name, (CASE WHEN (away.team_id IS NOT NULL) THEN away.team_city ELSE events.away_team END), away.team_name, users.user_id
4. 245.622 17,602.382 ↑ 280.0 2,397 1

Gather Merge (cost=890,720.03..984,123.31 rows=671,122 width=1,528) (actual time=17,390.796..17,602.382 rows=2,397 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
5. 11.165 17,356.760 ↑ 420.0 799 3 / 3

Partial GroupAggregate (cost=889,720.01..905,659.15 rows=335,561 width=1,528) (actual time=17,329.301..17,356.760 rows=799 loops=3)

  • Group Key: chatter.chatter_id, images.image_file, (CASE WHEN (away_logo.logo_id IS NOT NULL) THEN concat(away_logo.logo_sport, '_', away_logo.logo_id) ELSE NULL::text END), (CASE WHEN (home_logo.logo_id IS NOT NULL) THEN concat(home_logo.logo_sport,
6. 0.000 17,345.595 ↑ 11.4 29,380 3 / 3

Sort (cost=889,720.01..890,558.91 rows=335,561 width=1,528) (actual time=17,329.259..17,345.595 rows=29,380 loops=3)

  • Sort Key: chatter.chatter_id, images.image_file, (CASE WHEN (away_logo.logo_id IS NOT NULL) THEN concat(away_logo.logo_sport, '_', away_logo.logo_id) ELSE NULL::text END), (CASE WHEN (home_logo.logo_id IS NOT NULL) THEN concat(home_logo.logo_sport, '_', home_logo.logo_id) ELSE NULL::text END), events.event_status, events.event_time, (COALESCE(home.team_short, events.home_short)), (COALESCE(away.team_short, events.away_short)), (CASE WHEN (home.team_id IS NOT NULL) THEN home.team_city ELSE events.home_team END), home.team_name, (CASE WHEN (away.team_id IS NOT NULL) THEN away.team_city ELSE events.away_team END), away.team_name, users.user_id
  • Sort Method: external merge Disk: 5,632kB
  • Worker 0: Sort Method: external merge Disk: 5,424kB
  • Worker 1: Sort Method: external merge Disk: 45,536kB
  • Worker 1: Sort Method: external merge Disk: 45,536kB
7. 5,023.793 17,069.045 ↑ 11.4 29,380 3 / 3

Parallel Hash Left Join (cost=8,009.02..413,903.86 rows=335,561 width=1,528) (actual time=15,701.618..17,069.045 rows=29,380 loops=3)

  • Hash Cond: (chatter.event_id = events.event_id)
  • Filter: ((chatter.event_id IS NULL) OR ((events.event_status)::text <> ALL ('{Final,Final/OT,Final/2OT,Final/3OT,Final/SO,Final/4OT}'::text[])))
  • Rows Removed by Filter: 570,972
8. 597.056 10,291.347 ↓ 1.5 600,352 3 / 3

Hash Left Join (cost=645.35..328,971.66 rows=394,857 width=722) (actual time=123.813..10,291.347 rows=600,352 loops=3)

  • Hash Cond: (users.image_id = images.image_id)
  • -> Hash Join (cost=394.65..327683.82 rows=394,857 width=670) (actual time=114.237..10057.726
  • Hash Cond: (chatter.user_id = users.user_id)
9. 531.506 9,682.409 ↓ 3.8 1,696,766 3 / 3

Merge Left Join (cost=0.86..326,102.49 rows=452,092 width=617) (actual time=7.326..9,682.409 rows=1,696,766 loops=3)

  • Merge Cond: (chatter.chatter_id = likes.chatter_id)
10. 6,239.549 6,239.549 ↓ 2.2 678,225 3 / 3

Parallel Index Scan using chatter_pkey on chatter (cost=0.43..202,395.51 rows=308,544 width=609) (actual time=0.953..6,239.549 rows=678,225 loops=3)

  • Index Cond: (chatter_id > 220000)
  • Filter: (length(chatter_message) > 50)
  • Rows Removed by Filter: 62,530
11. 2,911.354 2,911.354 ↑ 1.0 3,288,019 3 / 3

Index Scan using likes_chatter_id_idx on likes (cost=0.43..110,194.54 rows=3,288,062 width=16) (actual time=0.020..2,911.354 rows=3,288,019 loops=3)

12. 1.123 2.335 ↓ 1.0 5,504 3 / 3

Hash (cost=325.58..325.58 rows=5,457 width=53) (actual time=2.334..2.335 rows=5,504 loops=3)

  • Buckets: 8,192 Batches: 1 Memory Usage: 396kB
13. 1.212 1.212 ↓ 1.0 5,504 3 / 3

Seq Scan on users (cost=0.00..325.58 rows=5,457 width=53) (actual time=0.015..1.212 rows=5,504 loops=3)

  • Filter: ((user_flag IS NULL) OR (user_flag = 1) OR (user_flag = 2) OR (user_flag = 4) OR (user_flag = 6) OR (user_flag = 7))
  • Rows Removed by Filter: 744
14. 1.278 9.547 ↓ 1.0 5,854 3 / 3

Hash (cost=177.53..177.53 rows=5,853 width=68) (actual time=9.546..9.547 rows=5,854 loops=3)

  • Buckets: 8,192 Batches: 1 Memory Usage: 668kB
15. 8.269 8.269 ↓ 1.0 5,854 3 / 3

Seq Scan on images (cost=0.00..177.53 rows=5,853 width=68) (actual time=0.030..8.269 rows=5,854 loops=3)

16. 1,753.905 1,753.905 ↑ 1.8 16,429 3 / 3

Parallel Hash (cost=4,028.27..4,028.27 rows=28,992 width=814) (actual time=1,753.896..1,753.905 rows=16,429 loops=3)

  • Buckets: 8,192 Batches: 16 Memory Usage: 512kB
17. 5,023.793 17,069.045 ↑ 11.4 29,380 3 / 3

Parallel Hash Left Join (cost=8,009.02..413,903.86 rows=335,561 width=1,528) (actual time=15,701.618..17,069.045 rows=29,380 loops=3)

  • Hash Cond: (chatter.event_id = events.event_id)
  • Filter: ((chatter.event_id IS NULL) OR ((events.event_status)::text <> ALL ('{Final,Final/OT,Final/2OT,Final/3OT,Final/SO,Final/4OT}'::text[])))
  • Rows Removed by Filter: 570,972
18. 597.056 10,291.347 ↓ 1.5 600,352 3 / 3

Hash Left Join (cost=645.35..328,971.66 rows=394,857 width=722) (actual time=123.813..10,291.347 rows=600,352 loops=3)

  • Hash Cond: (users.image_id = images.image_id)
  • -> Hash Join (cost=394.65..327683.82 rows=394,857 width=670) (actual time=114.237..10057.726
  • Hash Cond: (chatter.user_id = users.user_id)
19. 531.506 9,682.409 ↓ 3.8 1,696,766 3 / 3

Merge Left Join (cost=0.86..326,102.49 rows=452,092 width=617) (actual time=7.326..9,682.409 rows=1,696,766 loops=3)

  • Merge Cond: (chatter.chatter_id = likes.chatter_id)
20. 6,239.549 6,239.549 ↓ 2.2 678,225 3 / 3

Parallel Index Scan using chatter_pkey on chatter (cost=0.43..202,395.51 rows=308,544 width=609) (actual time=0.953..6,239.549 rows=678,225 loops=3)

  • Index Cond: (chatter_id > 220000)
  • Filter: (length(chatter_message) > 50)
  • Rows Removed by Filter: 62,530
21. 2,911.354 2,911.354 ↑ 1.0 3,288,019 3 / 3

Index Scan using likes_chatter_id_idx on likes (cost=0.43..110,194.54 rows=3,288,062 width=16) (actual time=0.020..2,911.354 rows=3,288,019 loops=3)

22. 1.123 2.335 ↓ 1.0 5,504 3 / 3

Hash (cost=325.58..325.58 rows=5,457 width=53) (actual time=2.334..2.335 rows=5,504 loops=3)

  • Buckets: 8,192 Batches: 1 Memory Usage: 396kB
23. 1.212 1.212 ↓ 1.0 5,504 3 / 3

Seq Scan on users (cost=0.00..325.58 rows=5,457 width=53) (actual time=0.015..1.212 rows=5,504 loops=3)

  • Filter: ((user_flag IS NULL) OR (user_flag = 1) OR (user_flag = 2) OR (user_flag = 4) OR (user_flag = 6) OR (user_flag = 7))
  • Rows Removed by Filter: 744
24. 1.278 9.547 ↓ 1.0 5,854 3 / 3

Hash (cost=177.53..177.53 rows=5,853 width=68) (actual time=9.546..9.547 rows=5,854 loops=3)

  • Buckets: 8,192 Batches: 1 Memory Usage: 668kB
25. 8.269 8.269 ↓ 1.0 5,854 3 / 3

Seq Scan on images (cost=0.00..177.53 rows=5,853 width=68) (actual time=0.030..8.269 rows=5,854 loops=3)

26. 530.774 1,753.905 ↑ 1.8 16,429 3 / 3

Parallel Hash (cost=4,028.27..4,028.27 rows=28,992 width=814) (actual time=1,753.896..1,753.905 rows=16,429 loops=3)

  • Buckets: 8,192 Batches: 16 Memory Usage: 512kB
27. 4.009 1,223.131 ↑ 1.8 16,429 3 / 3

Hash Left Join (cost=805.49..4,028.27 rows=28,992 width=814) (actual time=1,152.577..1,223.131 rows=16,429 loops=3)

  • Hash Cond: (events.home_id = home_user.team_id)
28. 7.651 1,217.661 ↑ 1.8 16,429 3 / 3

Hash Left Join (cost=479.91..3,041.75 rows=28,992 width=822) (actual time=1,151.090..1,217.661 rows=16,429 loops=3)

  • Hash Cond: (home.logo_id = home_logo.logo_id)
29. 4.279 1,209.883 ↑ 1.8 16,429 3 / 3

Hash Left Join (cost=465.24..2,950.69 rows=28,992 width=798) (actual time=1,150.951..1,209.883 rows=16,429 loops=3)

  • Hash Cond: (events.away_id = away_user.team_id)
30. 9.018 1,199.253 ↑ 1.8 16,429 3 / 3

Hash Left Join (cost=139.66..1,965.51 rows=28,992 width=806) (actual time=1,144.582..1,199.253 rows=16,429 loops=3)

  • Hash Cond: (away.logo_id = away_logo.logo_id)
31. 4.351 1,190.042 ↑ 1.8 16,429 3 / 3

Hash Left Join (cost=125.00..1,874.45 rows=28,992 width=782) (actual time=1,144.324..1,190.042 rows=16,429 loops=3)

  • Hash Cond: (events.home_id = home.team_id)
32. 5.943 1,185.119 ↑ 1.8 16,429 3 / 3

Hash Left Join (cost=62.50..1,735.69 rows=28,992 width=424) (actual time=1,143.739..1,185.119 rows=16,429 loops=3)

  • Hash Cond: (events.away_id = away.team_id)
33. 35.552 35.552 ↑ 1.8 16,429 3 / 3

Parallel Seq Scan on events (cost=0.00..1,596.92 rows=28,992 width=66) (actual time=0.091..35.552 rows=16,429 loops=3)

34. 0.385 1,143.624 ↑ 1.0 1,711 3 / 3

Hash (cost=41.11..41.11 rows=1,711 width=40) (actual time=1,143.624..1,143.624 rows=1,711 loops=3)

  • Buckets: 2,048 Batches: 1 Memory Usage: 128kB
35. 1,143.239 1,143.239 ↑ 1.0 1,711 3 / 3

Seq Scan on teams away (cost=0.00..41.11 rows=1,711 width=40) (actual time=1,142.554..1,143.239 rows=1,711 loops=3)

36. 0.341 0.572 ↑ 1.0 1,711 3 / 3

Hash (cost=41.11..41.11 rows=1,711 width=40) (actual time=0.571..0.572 rows=1,711 loops=3)

  • Buckets: 2,048 Batches: 1 Memory Usage: 128kB
37. 0.231 0.231 ↑ 1.0 1,711 3 / 3

Seq Scan on teams home (cost=0.00..41.11 rows=1,711 width=40) (actual time=0.008..0.231 rows=1,711 loops=3)

38. 0.066 0.193 ↑ 1.0 474 3 / 3

Hash (cost=8.74..8.74 rows=474 width=12) (actual time=0.193..0.193 rows=474 loops=3)

  • Buckets: 1,024 Batches: 1 Memory Usage: 29kB
39. 0.127 0.127 ↑ 1.0 474 3 / 3

Seq Scan on team_logos away_logo (cost=0.00..8.74 rows=474 width=12) (actual time=0.039..0.127 rows=474 loops=3)

40. 0.396 6.351 ↑ 8.5 731 3 / 3

Hash (cost=247.48..247.48 rows=6,248 width=8) (actual time=6.350..6.351 rows=731 loops=3)

  • Buckets: 8,192 Batches: 1 Memory Usage: 93kB
41. 5.955 5.955 ↑ 1.0 6,248 3 / 3

Seq Scan on users away_user (cost=0.00..247.48 rows=6,248 width=8) (actual time=0.029..5.955 rows=6,248 loops=3)

42. 0.066 0.127 ↑ 1.0 474 3 / 3

Hash (cost=8.74..8.74 rows=474 width=12) (actual time=0.126..0.127 rows=474 loops=3)

  • Buckets: 1,024 Batches: 1 Memory Usage: 29kB
43. 0.061 0.061 ↑ 1.0 474 3 / 3

Seq Scan on team_logos home_logo (cost=0.00..8.74 rows=474 width=12) (actual time=0.010..0.061 rows=474 loops=3)

44. 0.371 1.461 ↑ 8.5 731 3 / 3

Hash (cost=247.48..247.48 rows=6,248 width=8) (actual time=1.460..1.461 rows=731 loops=3)

  • Buckets: 8,192 Batches: 1 Memory Usage: 93kB
45. 1.090 1.090 ↑ 1.0 6,248 3 / 3

Seq Scan on users home_user (cost=0.00..247.48 rows=6,248 width=8) (actual time=0.007..1.090 rows=6,248 loops=3)

Planning time : 25.041 ms
Execution time : 19,368.459 ms