explain.depesz.com

PostgreSQL's explain analyze made readable

Result: fcZ9

Settings
# exclusive inclusive rows x rows loops node
1. 0.470 366.796 ↑ 1.0 1 1

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

2. 1.734 366.326 ↓ 1,217.0 1,217 1

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

3. 1.249 357.290 ↓ 1,217.0 1,217 1

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

4. 1.681 340.220 ↓ 1,217.0 1,217 1

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

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

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

6. 3.787 306.744 ↓ 214.4 7,717 1

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

7. 0.030 0.030 ↑ 1.0 1 1

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

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

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

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

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

10. 10.609 235.556 ↓ 8.7 7,717 1

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

11. 11.518 201.796 ↓ 2.9 7,717 1

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

12. 10.668 167.127 ↑ 1.0 7,717 1

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

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

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

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

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

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

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

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

Bitmap Heap Scan on conversation (cost=216.12..4,040.52 rows=8,017 width=32) (actual time=0.995..6.419 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.736 0.736 ↑ 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.736..0.736 rows=9,089 loops=1)

  • Index Cond: (organizationid = 'de6cd781-8caa-435b-85a3-c916b9c9397f'::uuid)
18. 30.868 30.868 ↑ 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.004 rows=1 loops=7,717)

  • Index Cond: ((conversationid = co.conversationid) AND ((role)::text = 'candidate'::text))
19. 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)
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. 30.868 30.868 ↑ 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.004 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 15.821 ↑ 1.0 1 1,217

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

26. 2.434 14.604 ↑ 1.0 1 1,217

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

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

Nested Loop (cost=0.84..16.89 rows=1 width=628) (actual time=0.008..0.010 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. 6.085 6.085 ↑ 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.005 rows=1 loops=1,217)

  • Index Cond: (participantid = _p_agent.participantid)
  • Heap Fetches: 1217
30. 1.217 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. 1.217 6.085 ↑ 1.0 1 1,217

Nested Loop (cost=0.84..16.89 rows=1 width=628) (actual time=0.005..0.005 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