explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Xxug

Settings
# exclusive inclusive rows x rows loops node
1. 0.451 258.963 ↑ 1.0 1 1

Aggregate (cost=9,730.52..9,730.53 rows=1 width=8) (actual time=258.963..258.963 rows=1 loops=1)

2. 1.805 258.512 ↓ 1,217.0 1,217 1

Nested Loop Left Join (cost=3,666.89..9,730.52 rows=1 width=0) (actual time=15.808..258.512 rows=1,217 loops=1)

3. 1.337 249.405 ↓ 1,217.0 1,217 1

Nested Loop Left Join (cost=3,666.05..9,713.61 rows=1 width=32) (actual time=15.796..249.405 rows=1,217 loops=1)

4. 1.638 233.464 ↓ 1,217.0 1,217 1

Nested Loop Left Join (cost=3,649.15..9,696.68 rows=1 width=32) (actual time=15.752..233.464 rows=1,217 loops=1)

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

Nested Loop (cost=3,648.88..9,696.37 rows=1 width=53) (actual time=15.746..230.609 rows=1,217 loops=1)

6. 3.785 201.202 ↓ 3,858.5 7,717 1

Nested Loop (cost=3,648.46..9,692.70 rows=2 width=37) (actual time=15.601..201.202 rows=7,717 loops=1)

7. 0.042 0.042 ↑ 1.0 1 1

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

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

Nested Loop (cost=3,648.46..9,685.68 rows=2 width=53) (actual time=15.582..197.375 rows=7,717 loops=1)

  • Join Filter: (conversation.conversationid = p_system.conversationid)
9. 11.481 167.907 ↓ 3,858.5 7,717 1

Nested Loop (cost=3,648.03..9,684.26 rows=2 width=85) (actual time=15.575..167.907 rows=7,717 loops=1)

10. 8.601 133.275 ↓ 154.3 7,717 1

Nested Loop (cost=3,647.61..9,637.49 rows=50 width=101) (actual time=15.566..133.275 rows=7,717 loops=1)

11. 10.571 101.523 ↓ 51.8 7,717 1

Nested Loop (cost=3,647.19..9,557.13 rows=149 width=117) (actual time=15.559..101.523 rows=7,717 loops=1)

12. 8.476 67.801 ↓ 17.3 7,717 1

Nested Loop (cost=3,646.77..9,322.50 rows=446 width=101) (actual time=15.548..67.801 rows=7,717 loops=1)

  • Join Filter: (conversation.conversationid = p_candidate.conversationid)
13. 6.655 28.457 ↓ 17.3 7,717 1

Hash Join (cost=3,646.35..7,505.26 rows=445 width=69) (actual time=15.531..28.457 rows=7,717 loops=1)

  • Hash Cond: (conversation.conversationid = co.conversationid)
14. 6.529 7.329 ↑ 1.0 7,717 1

Bitmap Heap Scan on conversation (cost=216.12..4,040.52 rows=8,017 width=32) (actual time=1.034..7.329 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
15. 0.800 0.800 ↑ 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.800..0.800 rows=9,089 loops=1)

  • Index Cond: (organizationid = 'de6cd781-8caa-435b-85a3-c916b9c9397f'::uuid)
16. 3.376 14.473 ↓ 1.0 9,089 1

Hash (cost=3,317.12..3,317.12 rows=9,049 width=37) (actual time=14.473..14.473 rows=9,089 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 750kB
17. 5.203 11.097 ↓ 1.0 9,089 1

Bitmap Heap Scan on conversationowner co (cost=214.83..3,317.12 rows=9,049 width=37) (actual time=6.058..11.097 rows=9,089 loops=1)

  • Recheck Cond: (orgid = 'de6cd781-8caa-435b-85a3-c916b9c9397f'::uuid)
  • Filter: ((ownertype)::text = 'agent'::text)
  • Heap Blocks: exact=1320
18. 5.894 5.894 ↓ 1.0 9,089 1

Bitmap Index Scan on idx_conversationowner_1 (cost=0.00..212.56 rows=9,086 width=0) (actual time=5.894..5.894 rows=9,089 loops=1)

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

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

  • Index Cond: ((conversationid = co.conversationid) AND ((role)::text = 'candidate'::text))
20. 23.151 23.151 ↑ 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.003 rows=1 loops=7,717)

  • Index Cond: (participantid = p_candidate.participantid)
  • Filter: ((channeltype)::text = 'sms'::text)
21. 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)
22. 23.151 23.151 ↑ 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.003..0.003 rows=1 loops=7,717)

  • Index Cond: (candidateid = pc.candidateid)
  • Filter: (organizationid = 'de6cd781-8caa-435b-85a3-c916b9c9397f'::uuid)
23. 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
24. 23.151 23.151 ↓ 0.0 0 7,717

Index Scan using idx_conversationowneragent_2 on conversationowneragent coa (cost=0.42..1.82 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
25. 1.217 1.217 ↓ 0.0 0 1,217

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

  • Index Cond: (conversationownerid = co.conversationownerid)
  • Heap Fetches: 0
26. 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)

27. 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
28. 2.434 10.953 ↑ 1.0 1 1,217

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

29. 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))
30. 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.003..0.004 rows=1 loops=1,217)

  • Index Cond: (participantid = _p_agent.participantid)
  • Heap Fetches: 1217
31. 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)

32. 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)

33. 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))
34. 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