explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Y1nDv

Settings
# exclusive inclusive rows x rows loops node
1. 77.879 5,063.102 ↓ 0.0 0 1

Gather (cost=23,645.50..69,110.36 rows=63,709 width=1,119) (actual time=5,044.398..5,063.102 rows=0 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
2. 4.564 4,985.223 ↓ 0.0 0 3

Hash Left Join (cost=22,645.50..61,739.46 rows=26,545 width=1,119) (actual time=4,985.222..4,985.223 rows=0 loops=3)

  • Hash Cond: ("to".id = to__social_profile.user_id)
  • Filter: (to_session__gcm_device.active OR ("to".is_eligible AND "to".is_active AND ("to".gcm_devices_count > 0) AND to__social_profile.chat_notification))
  • Rows Removed by Filter: 905
3. 5.839 4,782.427 ↑ 35.5 905 3

Hash Left Join (cost=21,829.82..60,839.38 rows=32,146 width=1,071) (actual time=3,283.914..4,782.427 rows=905 loops=3)

  • Hash Cond: (to_session.gcm_device_id = to_session__gcm_device.id)
4. 0.000 4,646.510 ↑ 35.5 905 3

Hash Left Join (cost=20,130.80..59,055.98 rows=32,146 width=876) (actual time=3,153.686..4,646.510 rows=905 loops=3)

  • Hash Cond: (message.chatroom_id = chatroom.id)
5. 4.939 4,637.701 ↑ 35.5 905 3

Hash Left Join (cost=20,118.56..58,957.10 rows=32,146 width=798) (actual time=3,149.136..4,637.701 rows=905 loops=3)

  • Hash Cond: ("user".id = user__social_profile.user_id)
6. 46.460 4,434.227 ↑ 35.5 905 3

Hash Left Join (cost=19,302.87..58,057.03 rows=32,146 width=750) (actual time=2,950.309..4,434.227 rows=905 loops=3)

  • Hash Cond: (message.to_id = "to".id)
7. 43.871 3,850.328 ↑ 35.5 905 3

Hash Join (cost=16,193.12..49,925.88 rows=32,146 width=489) (actual time=2,412.607..3,850.328 rows=905 loops=3)

  • Hash Cond: (message.user_id = "user".id)
8. 199.632 3,471.041 ↑ 33.7 1,298 3

Hash Left Join (cost=13,389.69..43,646.72 rows=43,700 width=228) (actual time=2,076.632..3,471.041 rows=1,298 loops=3)

  • Hash Cond: (message.to_session_id = to_session.id)
9. 177.997 2,197.984 ↑ 33.7 1,298 3

Hash Left Join (cost=6,694.84..33,112.17 rows=43,700 width=195) (actual time=999.106..2,197.984 rows=1,298 loops=3)

  • Hash Cond: (message.session_id = session.id)
10. 1,022.718 1,022.718 ↑ 33.7 1,298 3

Parallel Seq Scan on social_message message (cost=0.00..22,917.61 rows=43,700 width=162) (actual time=1.234..1,022.718 rows=1,298 loops=3)

  • Filter: ((NOT emailed) AND (NOT read) AND (NOT deleted) AND (NOT moderator_deleted) AND ((to_id IS NOT NULL) OR (to_session_id IS NOT NULL)))
  • Rows Removed by Filter: 263471
11. 567.295 997.269 ↑ 1.0 170,778 3

Hash (cost=3,224.93..3,224.93 rows=170,793 width=33) (actual time=997.269..997.269 rows=170,778 loops=3)

  • Buckets: 65536 Batches: 4 Memory Usage: 3044kB
12. 429.974 429.974 ↑ 1.0 170,778 3

Seq Scan on blocking_session session (cost=0.00..3,224.93 rows=170,793 width=33) (actual time=0.027..429.974 rows=170,778 loops=3)

13. 642.593 1,073.425 ↑ 1.0 170,778 3

Hash (cost=3,224.93..3,224.93 rows=170,793 width=33) (actual time=1,073.425..1,073.425 rows=170,778 loops=3)

  • Buckets: 65536 Batches: 4 Memory Usage: 3044kB
14. 430.832 430.832 ↑ 1.0 170,778 3

Seq Scan on blocking_session to_session (cost=0.00..3,224.93 rows=170,793 width=33) (actual time=0.015..430.832 rows=170,778 loops=3)

15. 168.081 335.416 ↑ 1.0 17,818 3

Hash (cost=1,951.00..1,951.00 rows=17,875 width=261) (actual time=335.416..335.416 rows=17,818 loops=3)

  • Buckets: 16384 Batches: 2 Memory Usage: 2457kB
16. 167.335 167.335 ↑ 1.0 17,818 3

Seq Scan on account_user "user" (cost=0.00..1,951.00 rows=17,875 width=261) (actual time=0.036..167.335 rows=17,818 loops=3)

  • Filter: (is_eligible AND is_active)
  • Rows Removed by Filter: 6482
17. 317.261 537.439 ↑ 1.0 24,300 3

Hash (cost=1,951.00..1,951.00 rows=24,300 width=261) (actual time=537.439..537.439 rows=24,300 loops=3)

  • Buckets: 16384 Batches: 2 Memory Usage: 3345kB
18. 220.178 220.178 ↑ 1.0 24,300 3

Seq Scan on account_user "to" (cost=0.00..1,951.00 rows=24,300 width=261) (actual time=0.016..220.178 rows=24,300 loops=3)

19. 120.226 198.535 ↓ 1.0 24,300 3

Hash (cost=511.97..511.97 rows=24,297 width=48) (actual time=198.534..198.535 rows=24,300 loops=3)

  • Buckets: 32768 Batches: 1 Memory Usage: 2189kB
20. 78.309 78.309 ↓ 1.0 24,300 3

Seq Scan on social_profile user__social_profile (cost=0.00..511.97 rows=24,297 width=48) (actual time=0.050..78.309 rows=24,300 loops=3)

21. 4.510 4.510 ↑ 1.0 144 3

Hash (cost=10.44..10.44 rows=144 width=78) (actual time=4.510..4.510 rows=144 loops=3)

22. 0.242 4.510 ↑ 1.0 144 3

Hash (cost=10.44..10.44 rows=144 width=78) (actual time=4.510..4.510 rows=144 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 25kB
23. 4.268 4.268 ↑ 1.0 144 3

Seq Scan on social_chatroom chatroom (cost=0.00..10.44 rows=144 width=78) (actual time=0.031..4.268 rows=144 loops=3)

24. 66.263 130.078 ↓ 1.0 13,632 3

Hash (cost=1,529.01..1,529.01 rows=13,601 width=195) (actual time=130.078..130.078 rows=13,632 loops=3)

  • Buckets: 16384 Batches: 1 Memory Usage: 3200kB
25. 63.815 63.815 ↓ 1.0 13,632 3

Seq Scan on push_notifications_gcmdevice to_session__gcm_device (cost=0.00..1,529.01 rows=13,601 width=195) (actual time=0.036..63.815 rows=13,632 loops=3)

26. 123.270 198.232 ↓ 1.0 24,300 3

Hash (cost=511.97..511.97 rows=24,297 width=48) (actual time=198.232..198.232 rows=24,300 loops=3)

  • Buckets: 32768 Batches: 1 Memory Usage: 2189kB
27. 74.962 74.962 ↓ 1.0 24,300 3

Seq Scan on social_profile to__social_profile (cost=0.00..511.97 rows=24,297 width=48) (actual time=0.021..74.962 rows=24,300 loops=3)

Planning time : 17.474 ms
Execution time : 5,065.831 ms