explain.depesz.com

PostgreSQL's explain analyze made readable

Result: JsYt

Settings
# exclusive inclusive rows x rows loops node
1. 0.026 0.608 ↑ 1.0 1 1

Nested Loop Left Join (cost=17.36..226.37 rows=1 width=797) (actual time=0.599..0.608 rows=1 loops=1)

2. 0.002 0.118 ↑ 1.0 1 1

Nested Loop Left Join (cost=9.11..49.56 rows=1 width=461) (actual time=0.109..0.118 rows=1 loops=1)

3. 0.000 0.109 ↑ 1.0 1 1

Nested Loop Left Join (cost=8.94..41.38 rows=1 width=461) (actual time=0.101..0.109 rows=1 loops=1)

4. 0.002 0.082 ↑ 1.0 1 1

Nested Loop Left Join (cost=0.76..33.19 rows=1 width=437) (actual time=0.075..0.082 rows=1 loops=1)

  • Join Filter: ((co.ownertype)::text = 'team'::text)
5. 0.002 0.076 ↑ 1.0 1 1

Nested Loop Left Join (cost=0.70..29.12 rows=1 width=437) (actual time=0.069..0.076 rows=1 loops=1)

  • Join Filter: ((co.ownertype)::text = 'agent'::text)
6. 0.002 0.067 ↑ 1.0 1 1

Nested Loop (cost=0.62..25.03 rows=1 width=421) (actual time=0.061..0.067 rows=1 loops=1)

7. 0.008 0.008 ↑ 1.0 1 1

Index Scan using idx_conversationowner_convid_ownertype on conversationowner co (cost=0.08..4.09 rows=1 width=37) (actual time=0.007..0.008 rows=1 loops=1)

  • Index Cond: (conversationid = 'ffffdb9a-6eac-4514-a6d0-71ce7a1f895d'::uuid)
8. 0.001 0.057 ↑ 1.0 1 1

Nested Loop (cost=0.53..20.94 rows=1 width=400) (actual time=0.052..0.057 rows=1 loops=1)

  • Join Filter: (p_candidate.participantid = pcchan.participantid)
9. 0.000 0.045 ↑ 1.0 1 1

Nested Loop (cost=0.45..20.80 rows=1 width=396) (actual time=0.041..0.045 rows=1 loops=1)

10. 0.003 0.042 ↑ 1.0 1 1

Nested Loop (cost=0.42..16.76 rows=1 width=391) (actual time=0.038..0.042 rows=1 loops=1)

11. 0.001 0.032 ↑ 1.0 1 1

Nested Loop (cost=0.34..16.37 rows=1 width=237) (actual time=0.030..0.032 rows=1 loops=1)

12. 0.002 0.029 ↑ 1.0 1 1

Nested Loop (cost=0.25..12.27 rows=1 width=213) (actual time=0.027..0.029 rows=1 loops=1)

13. 0.002 0.020 ↑ 1.0 1 1

Nested Loop (cost=0.17..8.18 rows=1 width=173) (actual time=0.019..0.020 rows=1 loops=1)

14. 0.007 0.007 ↑ 1.0 1 1

Index Scan using idx_conversation_1 on conversation (cost=0.08..4.09 rows=1 width=149) (actual time=0.006..0.007 rows=1 loops=1)

  • Index Cond: (conversationid = 'ffffdb9a-6eac-4514-a6d0-71ce7a1f895d'::uuid)
15. 0.011 0.011 ↑ 1.0 1 1

Index Scan using idx_participant_3 on participant p_candidate (cost=0.08..4.09 rows=1 width=40) (actual time=0.011..0.011 rows=1 loops=1)

  • Index Cond: ((conversationid = 'ffffdb9a-6eac-4514-a6d0-71ce7a1f895d'::uuid) AND ((role)::text = 'candidate'::text))
16. 0.007 0.007 ↑ 1.0 1 1

Index Scan using idx_participantcandidate_3 on participantcandidate pc (cost=0.08..4.09 rows=1 width=40) (actual time=0.006..0.007 rows=1 loops=1)

  • Index Cond: (participantid = p_candidate.participantid)
17. 0.002 0.002 ↑ 1.0 1 1

Index Scan using idx_participant_3 on participant p_system (cost=0.08..4.09 rows=1 width=40) (actual time=0.002..0.002 rows=1 loops=1)

  • Index Cond: ((conversationid = 'ffffdb9a-6eac-4514-a6d0-71ce7a1f895d'::uuid) AND ((role)::text = 'system'::text))
18. 0.007 0.007 ↑ 1.0 1 1

Index Scan using idx_candidate_1 on candidate (cost=0.08..0.39 rows=1 width=170) (actual time=0.007..0.007 rows=1 loops=1)

  • Index Cond: (candidateid = pc.candidateid)
19. 0.003 0.003 ↑ 1.0 1 1

Index Scan using idx_organization_3 on organization (cost=0.03..4.03 rows=1 width=21) (actual time=0.002..0.003 rows=1 loops=1)

  • Index Cond: (organizationid = conversation.organizationid)
20. 0.011 0.011 ↑ 1.0 1 1

Index Scan using idx_participantchannel_2 on participantchannel pcchan (cost=0.08..0.14 rows=1 width=36) (actual time=0.010..0.011 rows=1 loops=1)

  • Index Cond: (participantid = pc.participantid)
  • Filter: ((channeltype)::text = 'sms'::text)
21. 0.007 0.007 ↑ 1.0 1 1

Index Scan using idx_conversationowneragent_2 on conversationowneragent coa (cost=0.08..4.09 rows=1 width=32) (actual time=0.006..0.007 rows=1 loops=1)

  • Index Cond: (conversationownerid = co.conversationownerid)
22. 0.004 0.004 ↓ 0.0 0 1

Index Scan using idx_conversationownerteam_2 on conversationownerteam cot (cost=0.06..4.06 rows=1 width=32) (actual time=0.004..0.004 rows=0 loops=1)

  • Index Cond: (conversationownerid = co.conversationownerid)
23. 0.004 0.028 ↑ 1.0 1 1

Limit (cost=8.18..8.18 rows=1 width=628) (actual time=0.026..0.028 rows=1 loops=1)

24. 0.009 0.024 ↑ 1.0 1 1

Sort (cost=8.18..8.18 rows=1 width=628) (actual time=0.024..0.024 rows=1 loops=1)

  • Sort Key: _p_agent.lastmessagedatetime DESC
  • Sort Method: quicksort Memory: 25kB
25. 0.002 0.015 ↑ 1.0 1 1

Nested Loop (cost=0.17..8.18 rows=1 width=628) (actual time=0.014..0.015 rows=1 loops=1)

26. 0.003 0.003 ↑ 1.0 1 1

Index Scan using idx_participant_3 on participant _p_agent (cost=0.08..4.09 rows=1 width=24) (actual time=0.003..0.003 rows=1 loops=1)

  • Index Cond: ((conversationid = conversation.conversationid) AND ((role)::text = 'agent'::text))
27. 0.010 0.010 ↑ 1.0 1 1

Index Only Scan using idx_participantagent_3 on participantagent _pa (cost=0.08..4.09 rows=1 width=16) (actual time=0.009..0.010 rows=1 loops=1)

  • Index Cond: (participantid = _p_agent.participantid)
  • Heap Fetches: 1
28. 0.001 0.007 ↑ 1.0 1 1

Limit (cost=0.17..8.18 rows=1 width=628) (actual time=0.006..0.007 rows=1 loops=1)

29. 0.000 0.006 ↑ 1.0 1 1

Nested Loop (cost=0.17..8.18 rows=1 width=628) (actual time=0.006..0.006 rows=1 loops=1)

30. 0.003 0.003 ↑ 1.0 1 1

Index Scan using idx_participant_3 on participant _p_agent_1 (cost=0.08..4.09 rows=1 width=16) (actual time=0.003..0.003 rows=1 loops=1)

  • Index Cond: ((conversationid = conversation.conversationid) AND ((role)::text = 'agent'::text))
31. 0.003 0.003 ↑ 1.0 1 1

Index Only Scan using idx_participantagent_3 on participantagent _pa_1 (cost=0.08..4.09 rows=1 width=16) (actual time=0.003..0.003 rows=1 loops=1)

  • Index Cond: ((participantid = _p_agent_1.participantid) AND (agentid = coa.agentid))
  • Heap Fetches: 1
32. 0.009 0.027 ↑ 1.0 1 1

Aggregate (cost=8.25..8.25 rows=1 width=64) (actual time=0.027..0.027 rows=1 loops=1)

33. 0.000 0.018 ↑ 1.0 1 1

Nested Loop (cost=0.22..8.24 rows=1 width=32) (actual time=0.017..0.018 rows=1 loops=1)

34. 0.000 0.006 ↑ 1.0 1 1

Nested Loop (cost=0.17..8.18 rows=1 width=16) (actual time=0.006..0.006 rows=1 loops=1)

35. 0.003 0.003 ↑ 1.0 1 1

Index Scan using idx_participant_3 on participant _p_agent_2 (cost=0.08..4.09 rows=1 width=16) (actual time=0.003..0.003 rows=1 loops=1)

  • Index Cond: ((conversationid = conversation.conversationid) AND ((role)::text = 'agent'::text))
36. 0.003 0.003 ↑ 1.0 1 1

Index Only Scan using idx_participantagent_3 on participantagent _pa_2 (cost=0.08..4.09 rows=1 width=32) (actual time=0.003..0.003 rows=1 loops=1)

  • Index Cond: (participantid = _p_agent_2.participantid)
  • Heap Fetches: 1
37. 0.012 0.012 ↑ 1.0 1 1

Index Scan using idx_agent_1 on agent _a (cost=0.06..0.06 rows=1 width=32) (actual time=0.011..0.012 rows=1 loops=1)

  • Index Cond: (agentid = _pa_2.agentid)
38.          

SubPlan (forNested Loop Left Join)

39. 0.001 0.253 ↑ 1.0 1 1

Aggregate (cost=123.61..123.62 rows=1 width=8) (actual time=0.253..0.253 rows=1 loops=1)

40. 0.015 0.252 ↓ 0.0 0 1

Nested Loop (cost=2.20..123.61 rows=2 width=0) (actual time=0.252..0.252 rows=0 loops=1)

41. 0.036 0.047 ↑ 1.1 19 1

Bitmap Heap Scan on conversationmessage cm (cost=2.12..41.73 rows=20 width=16) (actual time=0.021..0.047 rows=19 loops=1)

  • Recheck Cond: (conversationid = conversation.conversationid)
  • Heap Blocks: exact=11
42. 0.011 0.011 ↑ 1.1 19 1

Bitmap Index Scan on idx_conversationmessage_convoid_partid (cost=0.00..2.11 rows=20 width=0) (actual time=0.011..0.011 rows=19 loops=1)

  • Index Cond: (conversationid = conversation.conversationid)
43. 0.190 0.190 ↓ 0.0 0 19

Index Scan using idx_message_1 on message m (cost=0.09..4.09 rows=1 width=16) (actual time=0.010..0.010 rows=0 loops=19)

  • Index Cond: (messageid = cm.messageid)
  • Filter: (((sourcerole)::text = ANY ('{candidate,system}'::text[])) AND ((createdatetime > conversation.lastmessageviewdatetime) OR (conversation.lastmessageviewdatetime IS NULL)))
  • Rows Removed by Filter: 1
44. 0.003 0.080 ↑ 1.0 1 1

Aggregate (cost=8.18..8.19 rows=1 width=8) (actual time=0.080..0.080 rows=1 loops=1)

45. 0.003 0.077 ↓ 8.0 8 1

Nested Loop (cost=0.17..8.18 rows=1 width=0) (actual time=0.052..0.077 rows=8 loops=1)

46. 0.010 0.010 ↓ 8.0 8 1

Index Scan using idx_conversationmessage_convoid_partid on conversationmessage cm_1 (cost=0.08..4.09 rows=1 width=16) (actual time=0.005..0.010 rows=8 loops=1)

  • Index Cond: ((conversationid = conversation.conversationid) AND (participantid = p_candidate.participantid))
47. 0.064 0.064 ↑ 1.0 1 8

Index Only Scan using idx_message_1 on message m_1 (cost=0.09..4.09 rows=1 width=16) (actual time=0.008..0.008 rows=1 loops=8)

  • Index Cond: (messageid = cm_1.messageid)
  • Heap Fetches: 8
48. 0.006 0.006 ↓ 0.0 0 1

Index Scan using idx_candidatejob_3 on candidatejob cj (cost=0.08..6.09 rows=2 width=16) (actual time=0.006..0.006 rows=0 loops=1)

  • Index Cond: (candidate.candidateid = candidateid)
49. 0.007 0.007 ↓ 0.0 0 1

Index Only Scan using idx_taggeditem_3 on taggeditem _ti (cost=0.08..6.09 rows=2 width=16) (actual time=0.007..0.007 rows=0 loops=1)

  • Index Cond: ((refkey = candidate.candidateid) AND (reftype = 'Candidate'::text))
  • Heap Fetches: 0
50. 0.000 0.047 ↑ 1.0 1 1

Limit (cost=8.19..8.19 rows=1 width=134) (actual time=0.047..0.047 rows=1 loops=1)

51. 0.006 0.047 ↑ 1.0 1 1

Sort (cost=8.19..8.19 rows=1 width=134) (actual time=0.047..0.047 rows=1 loops=1)

  • Sort Key: m_2.createdatetime DESC
  • Sort Method: top-N heapsort Memory: 25kB
52. 0.001 0.041 ↓ 11.0 11 1

Nested Loop (cost=0.17..8.18 rows=1 width=134) (actual time=0.007..0.041 rows=11 loops=1)

53. 0.007 0.007 ↓ 11.0 11 1

Index Scan using idx_conversationmessage_convoid_partid on conversationmessage cm_2 (cost=0.08..4.09 rows=1 width=16) (actual time=0.003..0.007 rows=11 loops=1)

  • Index Cond: ((conversationid = conversation.conversationid) AND (participantid = _p_agent.participantid))
54. 0.033 0.033 ↑ 1.0 1 11

Index Scan using idx_message_1 on message m_2 (cost=0.09..4.09 rows=1 width=150) (actual time=0.003..0.003 rows=1 loops=11)

  • Index Cond: (messageid = cm_2.messageid)
55. 0.001 0.039 ↑ 1.0 1 1

Limit (cost=8.19..8.19 rows=1 width=134) (actual time=0.039..0.039 rows=1 loops=1)

56. 0.009 0.038 ↑ 1.0 1 1

Sort (cost=8.19..8.19 rows=1 width=134) (actual time=0.038..0.038 rows=1 loops=1)

  • Sort Key: m_3.createdatetime DESC
  • Sort Method: top-N heapsort Memory: 25kB
57. 0.006 0.029 ↓ 8.0 8 1

Nested Loop (cost=0.17..8.18 rows=1 width=134) (actual time=0.006..0.029 rows=8 loops=1)

58. 0.007 0.007 ↓ 8.0 8 1

Index Scan using idx_conversationmessage_convoid_partid on conversationmessage cm_3 (cost=0.08..4.09 rows=1 width=16) (actual time=0.003..0.007 rows=8 loops=1)

  • Index Cond: ((conversationid = conversation.conversationid) AND (participantid = p_candidate.participantid))
59. 0.016 0.016 ↑ 1.0 1 8

Index Scan using idx_message_1 on message m_3 (cost=0.09..4.09 rows=1 width=150) (actual time=0.002..0.002 rows=1 loops=8)

  • Index Cond: (messageid = cm_3.messageid)
60. 0.000 0.005 ↓ 0.0 0 1

Limit (cost=8.19..8.19 rows=1 width=134) (actual time=0.005..0.005 rows=0 loops=1)

61. 0.003 0.005 ↓ 0.0 0 1

Sort (cost=8.19..8.19 rows=1 width=134) (actual time=0.005..0.005 rows=0 loops=1)

  • Sort Key: m_4.createdatetime DESC
  • Sort Method: quicksort Memory: 25kB
62. 0.000 0.002 ↓ 0.0 0 1

Nested Loop (cost=0.17..8.18 rows=1 width=134) (actual time=0.002..0.002 rows=0 loops=1)

63. 0.002 0.002 ↓ 0.0 0 1

Index Scan using idx_conversationmessage_convoid_partid on conversationmessage cm_4 (cost=0.08..4.09 rows=1 width=16) (actual time=0.002..0.002 rows=0 loops=1)

  • Index Cond: ((conversationid = conversation.conversationid) AND (participantid = p_system.participantid))
64. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_message_1 on message m_4 (cost=0.09..4.09 rows=1 width=150) (never executed)

  • Index Cond: (messageid = cm_4.messageid)