explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 86T

Settings
# exclusive inclusive rows x rows loops node
1. 0.655 7,211.592 ↑ 1.0 1 1

Aggregate (cost=79,226.76..79,226.77 rows=1 width=8) (actual time=7,211.591..7,211.592 rows=1 loops=1)

2. 2.286 7,210.937 ↓ 11.5 1,217 1

Nested Loop Left Join (cost=44,080.88..79,226.49 rows=106 width=0) (actual time=1,496.685..7,210.937 rows=1,217 loops=1)

3. 1.629 7,198.915 ↓ 11.5 1,217 1

Nested Loop Left Join (cost=44,080.04..77,433.77 rows=106 width=32) (actual time=1,496.671..7,198.915 rows=1,217 loops=1)

4. 1.234 6,795.676 ↓ 11.5 1,217 1

Hash Left Join (cost=44,063.14..75,639.72 rows=106 width=32) (actual time=1,496.170..6,795.676 rows=1,217 loops=1)

  • Hash Cond: (co.conversationownerid = cot.conversationownerid)
  • Join Filter: ((co.ownertype)::text = 'team'::text)
5. 55.307 6,791.488 ↓ 11.5 1,217 1

Hash Join (cost=44,036.59..75,612.76 rows=106 width=53) (actual time=1,493.206..6,791.488 rows=1,217 loops=1)

  • Hash Cond: (co.conversationownerid = coa.conversationownerid)
6. 96.031 6,630.521 ↓ 9.1 140,286 1

Hash Join (cost=41,979.18..73,496.31 rows=15,462 width=37) (actual time=1,380.731..6,630.521 rows=140,286 loops=1)

  • Hash Cond: (conversation.organizationid = organization.organizationid)
7. 103.309 6,533.943 ↓ 9.1 140,286 1

Nested Loop (cost=41,970.58..73,275.11 rows=15,462 width=53) (actual time=1,380.164..6,533.943 rows=140,286 loops=1)

8. 99.683 2,923.484 ↓ 9.1 140,286 1

Nested Loop (cost=41,970.16..58,848.46 rows=15,462 width=69) (actual time=1,380.148..2,923.484 rows=140,286 loops=1)

  • Join Filter: (conversation.conversationid = p_system.conversationid)
9. 236.941 1,701.513 ↓ 9.0 140,286 1

Hash Join (cost=41,969.74..47,740.58 rows=15,657 width=101) (actual time=1,380.110..1,701.513 rows=140,286 loops=1)

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

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

  • Filter: ((ownertype)::text = 'agent'::text)
  • Rows Removed by Filter: 691
11. 66.979 1,378.213 ↓ 9.0 140,974 1

Hash (cost=41,773.21..41,773.21 rows=15,722 width=64) (actual time=1,378.213..1,378.213 rows=140,974 loops=1)

  • Buckets: 65536 (originally 16384) Batches: 4 (originally 1) Memory Usage: 3809kB
12. 158.320 1,311.234 ↓ 9.0 140,974 1

Hash Join (cost=34,660.59..41,773.21 rows=15,722 width=64) (actual time=929.866..1,311.234 rows=140,974 loops=1)

  • Hash Cond: (conversation.conversationid = p_candidate.conversationid)
13. 224.491 224.491 ↓ 1.0 140,974 1

Seq Scan on conversation (cost=0.00..5,724.07 rows=140,723 width=32) (actual time=0.442..224.491 rows=140,974 loops=1)

  • Filter: ((status)::text = 'active'::text)
  • Rows Removed by Filter: 22152
14. 69.468 928.423 ↓ 9.0 163,126 1

Hash (cost=34,432.78..34,432.78 rows=18,225 width=32) (actual time=928.423..928.423 rows=163,126 loops=1)

  • Buckets: 65536 (originally 32768) Batches: 4 (originally 1) Memory Usage: 3585kB
15. 152.326 858.955 ↓ 9.0 163,126 1

Hash Join (cost=26,170.55..34,432.78 rows=18,225 width=32) (actual time=557.935..858.955 rows=163,126 loops=1)

  • Hash Cond: (pc.participantid = p_candidate.participantid)
16. 149.360 149.360 ↑ 1.0 163,126 1

Seq Scan on participantcandidate pc (cost=0.00..4,756.26 rows=163,126 width=32) (actual time=0.575..149.360 rows=163,126 loops=1)

17. 78.521 557.269 ↓ 3.0 163,126 1

Hash (cost=25,008.00..25,008.00 rows=54,604 width=48) (actual time=557.269..557.269 rows=163,126 loops=1)

  • Buckets: 65536 (originally 65536) Batches: 4 (originally 2) Memory Usage: 3691kB
18. 145.186 478.748 ↓ 3.0 163,126 1

Hash Join (cost=7,355.15..25,008.00 rows=54,604 width=48) (actual time=224.253..478.748 rows=163,126 loops=1)

  • Hash Cond: (p_candidate.participantid = pcchan.participantid)
19. 109.452 109.452 ↑ 1.0 163,126 1

Seq Scan on participant p_candidate (cost=0.00..13,458.31 rows=163,599 width=32) (actual time=0.010..109.452 rows=163,126 loops=1)

  • Filter: ((role)::text = 'candidate'::text)
  • Rows Removed by Filter: 325619
20. 60.377 224.110 ↑ 1.0 163,126 1

Hash (cost=4,519.07..4,519.07 rows=163,126 width=16) (actual time=224.110..224.110 rows=163,126 loops=1)

  • Buckets: 131072 Batches: 4 Memory Usage: 2931kB
21. 163.733 163.733 ↑ 1.0 163,126 1

Seq Scan on participantchannel pcchan (cost=0.00..4,519.07 rows=163,126 width=16) (actual time=0.460..163.733 rows=163,126 loops=1)

  • Filter: ((channeltype)::text = 'sms'::text)
22. 1,122.288 1,122.288 ↑ 1.0 1 140,286

Index Only Scan using idx_participant_3 on participant p_system (cost=0.42..0.70 rows=1 width=16) (actual time=0.007..0.008 rows=1 loops=140,286)

  • Index Cond: ((conversationid = p_candidate.conversationid) AND (role = 'system'::text))
  • Heap Fetches: 140286
23. 3,507.150 3,507.150 ↑ 1.0 1 140,286

Index Only Scan using idx_candidate_1 on candidate (cost=0.42..0.92 rows=1 width=16) (actual time=0.024..0.025 rows=1 loops=140,286)

  • Index Cond: (candidateid = pc.candidateid)
  • Heap Fetches: 140286
24. 0.049 0.547 ↑ 1.0 160 1

Hash (cost=6.60..6.60 rows=160 width=16) (actual time=0.547..0.547 rows=160 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 16kB
25. 0.498 0.498 ↑ 1.0 160 1

Seq Scan on organization (cost=0.00..6.60 rows=160 width=16) (actual time=0.007..0.498 rows=160 loops=1)

26. 0.619 105.660 ↓ 1.1 1,217 1

Hash (cost=2,043.40..2,043.40 rows=1,121 width=32) (actual time=105.660..105.660 rows=1,217 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 93kB
27. 102.238 105.041 ↓ 1.1 1,217 1

Bitmap Heap Scan on conversationowneragent coa (cost=29.11..2,043.40 rows=1,121 width=32) (actual time=3.312..105.041 rows=1,217 loops=1)

  • Recheck Cond: (agentid = 'dbb6b117-b9c9-4457-bac9-174c169653df'::uuid)
  • Heap Blocks: exact=220
28. 2.803 2.803 ↓ 1.1 1,217 1

Bitmap Index Scan on idx_conversationowneragent_3 (cost=0.00..28.83 rows=1,121 width=0) (actual time=2.803..2.803 rows=1,217 loops=1)

  • Index Cond: (agentid = 'dbb6b117-b9c9-4457-bac9-174c169653df'::uuid)
29. 0.222 2.954 ↑ 1.0 691 1

Hash (cost=17.91..17.91 rows=691 width=16) (actual time=2.954..2.954 rows=691 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 41kB
30. 2.732 2.732 ↑ 1.0 691 1

Seq Scan on conversationownerteam cot (cost=0.00..17.91 rows=691 width=16) (actual time=0.456..2.732 rows=691 loops=1)

31. 2.434 401.610 ↑ 1.0 1 1,217

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

32. 4.868 399.176 ↑ 1.0 1 1,217

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

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

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

34. 7.302 7.302 ↑ 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.005..0.006 rows=1 loops=1,217)

  • Index Cond: ((conversationid = conversation.conversationid) AND ((role)::text = 'agent'::text))
35. 384.572 384.572 ↑ 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.314..0.316 rows=1 loops=1,217)

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

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

37. 3.651 9.736 ↑ 1.0 1 1,217

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

38. 3.651 3.651 ↑ 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.003..0.003 rows=1 loops=1,217)

  • Index Cond: ((conversationid = conversation.conversationid) AND ((role)::text = 'agent'::text))
39. 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