explain.depesz.com

PostgreSQL's explain analyze made readable

Result: QNGn

Settings
# exclusive inclusive rows x rows loops node
1. 0.401 400.680 ↑ 1.0 1 1

Aggregate (cost=22,085.73..22,085.74 rows=1 width=8) (actual time=400.680..400.680 rows=1 loops=1)

2. 1.984 400.279 ↓ 1,217.0 1,217 1

Nested Loop Left Join (cost=4,161.27..22,085.73 rows=1 width=0) (actual time=16.127..400.279 rows=1,217 loops=1)

3. 1.602 390.993 ↓ 1,217.0 1,217 1

Nested Loop Left Join (cost=4,160.43..22,068.82 rows=1 width=32) (actual time=16.113..390.993 rows=1,217 loops=1)

4. 1.712 374.787 ↓ 1,217.0 1,217 1

Nested Loop Left Join (cost=4,143.53..22,051.89 rows=1 width=32) (actual time=16.081..374.787 rows=1,217 loops=1)

  • Join Filter: ((co.ownertype)::text = 'team'::text)
5. 8.404 371.858 ↓ 1,217.0 1,217 1

Nested Loop (cost=4,143.25..22,051.59 rows=1 width=53) (actual time=16.074..371.858 rows=1,217 loops=1)

6. 3.810 340.303 ↓ 214.4 7,717 1

Nested Loop (cost=4,142.83..22,032.61 rows=36 width=37) (actual time=16.061..340.303 rows=7,717 loops=1)

7. 0.048 0.048 ↑ 1.0 1 1

Seq Scan on organization (cost=0.00..7.00 rows=1 width=16) (actual time=0.035..0.048 rows=1 loops=1)

  • Filter: (organizationid = 'de6cd781-8caa-435b-85a3-c916b9c9397f'::uuid)
  • Rows Removed by Filter: 159
8. 6.158 336.445 ↓ 214.4 7,717 1

Nested Loop (cost=4,142.83..22,025.25 rows=36 width=53) (actual time=16.023..336.445 rows=7,717 loops=1)

  • Join Filter: (conversation.conversationid = p_system.conversationid)
9. 11.154 307.136 ↓ 214.4 7,717 1

Nested Loop (cost=4,142.41..21,999.71 rows=36 width=85) (actual time=16.014..307.136 rows=7,717 loops=1)

10. 10.691 241.963 ↓ 8.7 7,717 1

Nested Loop (cost=4,141.99..21,166.15 rows=891 width=101) (actual time=16.004..241.963 rows=7,717 loops=1)

11. 4.632 208.121 ↓ 2.9 7,717 1

Nested Loop (cost=4,141.57..19,725.05 rows=2,672 width=117) (actual time=15.995..208.121 rows=7,717 loops=1)

12. 5.062 172.621 ↑ 1.0 7,717 1

Nested Loop (cost=4,141.15..15,512.85 rows=8,007 width=101) (actual time=15.982..172.621 rows=7,717 loops=1)

  • Join Filter: (conversation.conversationid = p_candidate.conversationid)
13. 58.043 128.974 ↑ 1.0 7,717 1

Hash Join (cost=4,140.73..9,834.84 rows=7,984 width=69) (actual time=15.956..128.974 rows=7,717 loops=1)

  • Hash Cond: (co.conversationid = conversation.conversationid)
14. 55.063 55.063 ↑ 1.0 162,435 1

Seq Scan on conversationowner co (cost=0.00..5,005.07 rows=162,452 width=37) (actual time=0.010..55.063 rows=162,435 loops=1)

  • Filter: ((ownertype)::text = 'agent'::text)
  • Rows Removed by Filter: 691
15. 2.789 15.868 ↑ 1.0 7,717 1

Hash (cost=4,040.52..4,040.52 rows=8,017 width=32) (actual time=15.868..15.868 rows=7,717 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 547kB
16. 12.189 13.079 ↑ 1.0 7,717 1

Bitmap Heap Scan on conversation (cost=216.12..4,040.52 rows=8,017 width=32) (actual time=1.126..13.079 rows=7,717 loops=1)

  • Recheck Cond: (organizationid = 'de6cd781-8caa-435b-85a3-c916b9c9397f'::uuid)
  • Filter: ((status)::text = 'active'::text)
  • Rows Removed by Filter: 1372
  • Heap Blocks: exact=1843
17. 0.890 0.890 ↑ 1.0 9,089 1

Bitmap Index Scan on idx_conversation_2 (cost=0.00..214.12 rows=9,293 width=0) (actual time=0.890..0.890 rows=9,089 loops=1)

  • Index Cond: (organizationid = 'de6cd781-8caa-435b-85a3-c916b9c9397f'::uuid)
18. 38.585 38.585 ↑ 1.0 1 7,717

Index Scan using idx_participant_3 on participant p_candidate (cost=0.42..0.70 rows=1 width=32) (actual time=0.004..0.005 rows=1 loops=7,717)

  • Index Cond: ((conversationid = co.conversationid) AND ((role)::text = 'candidate'::text))
19. 30.868 30.868 ↑ 1.0 1 7,717

Index Scan using idx_participantchannel_2 on participantchannel pcchan (cost=0.42..0.52 rows=1 width=16) (actual time=0.003..0.004 rows=1 loops=7,717)

  • Index Cond: (participantid = p_candidate.participantid)
  • Filter: ((channeltype)::text = 'sms'::text)
20. 23.151 23.151 ↑ 1.0 1 7,717

Index Scan using idx_participantcandidate_1 on participantcandidate pc (cost=0.42..0.53 rows=1 width=32) (actual time=0.003..0.003 rows=1 loops=7,717)

  • Index Cond: (participantid = p_candidate.participantid)
21. 54.019 54.019 ↑ 1.0 1 7,717

Index Scan using idx_candidate_1 on candidate (cost=0.42..0.93 rows=1 width=16) (actual time=0.007..0.007 rows=1 loops=7,717)

  • Index Cond: (candidateid = pc.candidateid)
  • Filter: (organizationid = 'de6cd781-8caa-435b-85a3-c916b9c9397f'::uuid)
22. 23.151 23.151 ↑ 1.0 1 7,717

Index Only Scan using idx_participant_3 on participant p_system (cost=0.42..0.70 rows=1 width=16) (actual time=0.002..0.003 rows=1 loops=7,717)

  • Index Cond: ((conversationid = p_candidate.conversationid) AND (role = 'system'::text))
  • Heap Fetches: 7717
23. 23.151 23.151 ↓ 0.0 0 7,717

Index Scan using idx_conversationowneragent_2 on conversationowneragent coa (cost=0.42..0.52 rows=1 width=32) (actual time=0.003..0.003 rows=0 loops=7,717)

  • Index Cond: (conversationownerid = co.conversationownerid)
  • Filter: (agentid = 'dbb6b117-b9c9-4457-bac9-174c169653df'::uuid)
  • Rows Removed by Filter: 1
24. 1.217 1.217 ↓ 0.0 0 1,217

Index Only Scan using idx_conversationownerteam_2 on conversationownerteam cot (cost=0.28..0.29 rows=1 width=16) (actual time=0.001..0.001 rows=0 loops=1,217)

  • Index Cond: (conversationownerid = co.conversationownerid)
  • Heap Fetches: 0
25. 1.217 14.604 ↑ 1.0 1 1,217

Limit (cost=16.90..16.91 rows=1 width=628) (actual time=0.012..0.012 rows=1 loops=1,217)

26. 2.434 13.387 ↑ 1.0 1 1,217

Sort (cost=16.90..16.91 rows=1 width=628) (actual time=0.011..0.011 rows=1 loops=1,217)

  • Sort Key: _p_agent.lastmessagedatetime DESC
  • Sort Method: quicksort Memory: 25kB
27. 2.434 10.953 ↑ 1.0 1 1,217

Nested Loop (cost=0.84..16.89 rows=1 width=628) (actual time=0.008..0.009 rows=1 loops=1,217)

28. 3.651 3.651 ↑ 1.0 1 1,217

Index Scan using idx_participant_3 on participant _p_agent (cost=0.42..8.44 rows=1 width=24) (actual time=0.003..0.003 rows=1 loops=1,217)

  • Index Cond: ((conversationid = conversation.conversationid) AND ((role)::text = 'agent'::text))
29. 4.868 4.868 ↑ 1.0 1 1,217

Index Only Scan using idx_participantagent_3 on participantagent _pa (cost=0.42..8.44 rows=1 width=16) (actual time=0.004..0.004 rows=1 loops=1,217)

  • Index Cond: (participantid = _p_agent.participantid)
  • Heap Fetches: 1217
30. 0.000 7.302 ↑ 1.0 1 1,217

Limit (cost=0.84..16.89 rows=1 width=628) (actual time=0.006..0.006 rows=1 loops=1,217)

31. 2.434 7.302 ↑ 1.0 1 1,217

Nested Loop (cost=0.84..16.89 rows=1 width=628) (actual time=0.006..0.006 rows=1 loops=1,217)

32. 2.434 2.434 ↑ 1.0 1 1,217

Index Scan using idx_participant_3 on participant _p_agent_1 (cost=0.42..8.44 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=1,217)

  • Index Cond: ((conversationid = conversation.conversationid) AND ((role)::text = 'agent'::text))
33. 2.434 2.434 ↑ 1.0 1 1,217

Index Only Scan using idx_participantagent_3 on participantagent _pa_1 (cost=0.42..8.44 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=1,217)

  • Index Cond: ((participantid = _p_agent_1.participantid) AND (agentid = coa.agentid))
  • Heap Fetches: 1217