explain.depesz.com

PostgreSQL's explain analyze made readable

Result: zgJjh

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

"Nested Loop Left Join (cost=4,627.41..4,629.68 rows=100 width=48) (actual rows= loops=)

2. 0.000 0.000 ↓ 0.0

Function Scan on _subs (cost=0.01..1.00 rows=100 width=16) (actual rows= loops=)

3. 0.000 0.000 ↓ 0.0

Materialize (cost=4,627.40..4,627.43 rows=1 width=32) (actual rows= loops=)

4. 0.000 0.000 ↓ 0.0

Aggregate (cost=4,627.40..4,627.41 rows=1 width=32) (actual rows= loops=)

5. 0.000 0.000 ↓ 0.0

Sort (cost=4,627.04..4,627.10 rows=24 width=72) (actual rows= loops=)

  • Sort Key: users.username, chats.created_at
6. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=170.34..4,626.49 rows=24 width=72) (actual rows= loops=)

7. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=162.01..4,425.43 rows=24 width=119) (actual rows= loops=)

8. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=0.86..557.14 rows=24 width=87) (actual rows= loops=)

9. 0.000 0.000 ↓ 0.0

Nested Loop Semi Join (cost=0.71..186.10 rows=24 width=27) (actual rows= loops=)

10. 0.000 0.000 ↓ 0.0

Index Scan using chats_ended_at on chats (cost=0.42..21.05 rows=38 width=27) (actual rows= loops=)

  • Index Cond: (ended_at IS NULL)
11. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.29..4.33 rows=1 width=8) (actual rows= loops=)

12. 0.000 0.000 ↓ 0.0

Index Scan using user_agent_profiles_user_id_key on user_agent_profiles _1__be_1_app_user_agent_profiles (cost=0.15..3.92 rows=1 width=4) (actual rows= loops=)

  • Index Cond: (user_id = chats.latest_agent_id)
  • Filter: ((alternatives: SubPlan 2 or hashed SubPlan 3) OR (id = ANY ('{486}'::integer[])))
13.          

SubPlan (for Index Scan)

14. 0.000 0.000 ↓ 0.0

Seq Scan on agent_team_user_agent_profile_join _2__be_2_app_agent_team_user_agent_profile_join (cost=0.00..18.74 rows=6 width=0) (actual rows= loops=)

  • Filter: (user_agent_profile_id = _1__be_1_app_user_agent_profiles.id)
15. 0.000 0.000 ↓ 0.0

Seq Scan on agent_team_user_agent_profile_join _2__be_2_app_agent_team_user_agent_profile_join_1 (cost=0.00..16.59 rows=859 width=4) (actual rows= loops=)

16. 0.000 0.000 ↓ 0.0

Index Only Scan using users_pkey on users _0__be_0_app_users (cost=0.15..0.41 rows=1 width=4) (actual rows= loops=)

  • Index Cond: (id = _1__be_1_app_user_agent_profiles.user_id)
17. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=0.15..15.44 rows=1 width=64) (actual rows= loops=)

  • Join Filter: (users.id = user_agent_profiles.user_id)
18. 0.000 0.000 ↓ 0.0

Index Scan using users_pkey on users (cost=0.15..8.17 rows=1 width=68) (actual rows= loops=)

  • Index Cond: (chats.latest_agent_id = id)
19. 0.000 0.000 ↓ 0.0

Seq Scan on user_agent_profiles (cost=0.00..7.25 rows=1 width=36) (actual rows= loops=)

  • Filter: (user_id = chats.latest_agent_id)
20.          

SubPlan (for Seq Scan)

21. 0.000 0.000 ↓ 0.0

Result (cost=0.00..0.01 rows=1 width=32) (actual rows= loops=)

22.          

SubPlan (for Nested Loop Left Join)

23. 0.000 0.000 ↓ 0.0

Result (cost=0.00..0.01 rows=1 width=32) (actual rows= loops=)

24. 0.000 0.000 ↓ 0.0

Aggregate (cost=161.15..161.16 rows=1 width=32) (actual rows= loops=)

25. 0.000 0.000 ↓ 0.0

Limit (cost=161.13..161.13 rows=1 width=40) (actual rows= loops=)

26. 0.000 0.000 ↓ 0.0

Sort (cost=161.13..161.33 rows=81 width=40) (actual rows= loops=)

  • Sort Key: messages.platform_timestamp DESC
27. 0.000 0.000 ↓ 0.0

Index Scan using messages_chat_id_platform_sequence_id_key on messages (cost=0.43..160.73 rows=81 width=40) (actual rows= loops=)

  • Index Cond: (chats.id = chat_id)
28.          

SubPlan (for Index Scan)

29. 0.000 0.000 ↓ 0.0

Result (cost=0.00..0.01 rows=1 width=32) (actual rows= loops=)

30. 0.000 0.000 ↓ 0.0

Aggregate (cost=8.33..8.35 rows=1 width=32) (actual rows= loops=)

31. 0.000 0.000 ↓ 0.0

Index Only Scan using chat_id_idx on hints (cost=0.29..8.33 rows=2 width=0) (actual rows= loops=)

  • Index Cond: (chat_id = chats.id)
32.          

SubPlan (for Nested Loop Left Join)

33. 0.000 0.000 ↓ 0.0

Result (cost=0.00..0.01 rows=1 width=32) (actual rows= loops=)