explain.depesz.com

PostgreSQL's explain analyze made readable

Result: gxah

Settings
# exclusive inclusive rows x rows loops node
1. 0.777 13,664.399 ↑ 1.0 1 1

Aggregate (cost=79,226.76..79,226.77 rows=1 width=8) (actual time=13,664.398..13,664.399 rows=1 loops=1)

2. 2.178 13,663.622 ↓ 11.5 1,217 1

Nested Loop Left Join (cost=44,080.88..79,226.49 rows=106 width=0) (actual time=2,419.418..13,663.622 rows=1,217 loops=1)

3. 1.688 13,650.491 ↓ 11.5 1,217 1

Nested Loop Left Join (cost=44,080.04..77,433.77 rows=106 width=32) (actual time=2,419.401..13,650.491 rows=1,217 loops=1)

4. 1.304 12,467.096 ↓ 11.5 1,217 1

Hash Left Join (cost=44,063.14..75,639.72 rows=106 width=32) (actual time=2,417.761..12,467.096 rows=1,217 loops=1)

  • Hash Cond: (co.conversationownerid = cot.conversationownerid)
  • Join Filter: ((co.ownertype)::text = 'team'::text)
5. 58.735 12,464.133 ↓ 11.5 1,217 1

Hash Join (cost=44,036.59..75,612.76 rows=106 width=53) (actual time=2,416.085..12,464.133 rows=1,217 loops=1)

  • Hash Cond: (co.conversationownerid = coa.conversationownerid)
6. 100.816 12,184.506 ↓ 9.1 140,286 1

Hash Join (cost=41,979.18..73,496.31 rows=15,462 width=37) (actual time=2,090.142..12,184.506 rows=140,286 loops=1)

  • Hash Cond: (conversation.organizationid = organization.organizationid)
7. 140.717 12,083.043 ↓ 9.1 140,286 1

Nested Loop (cost=41,970.58..73,275.11 rows=15,462 width=53) (actual time=2,089.457..12,083.043 rows=140,286 loops=1)

8. 99.558 4,647.454 ↓ 9.1 140,286 1

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

  • Join Filter: (conversation.conversationid = p_system.conversationid)
9. 260.240 2,443.606 ↓ 9.0 140,286 1

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

17. 73.265 1,071.259 ↓ 3.0 163,126 1

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

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

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

  • Hash Cond: (p_candidate.participantid = pcchan.participantid)
19. 613.088 613.088 ↑ 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.015..613.088 rows=163,126 loops=1)

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

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

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

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

  • Filter: ((channeltype)::text = 'sms'::text)
22. 2,104.290 2,104.290 ↑ 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.014..0.015 rows=1 loops=140,286)

  • Index Cond: ((conversationid = p_candidate.conversationid) AND (role = 'system'::text))
  • Heap Fetches: 140286
23. 7,294.872 7,294.872 ↑ 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.052..0.052 rows=1 loops=140,286)

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

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

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

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

26. 0.758 220.892 ↓ 1.1 1,217 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 93kB
27. 217.350 220.134 ↓ 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.335..220.134 rows=1,217 loops=1)

  • Recheck Cond: (agentid = 'dbb6b117-b9c9-4457-bac9-174c169653df'::uuid)
  • Heap Blocks: exact=220
28. 2.784 2.784 ↓ 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.784..2.784 rows=1,217 loops=1)

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

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

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

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

31. 2.434 1,181.707 ↑ 1.0 1 1,217

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

32. 6.085 1,179.273 ↑ 1.0 1 1,217

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

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

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

34. 4.868 4.868 ↑ 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.004..0.004 rows=1 loops=1,217)

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

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

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

37. 1.217 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. 4.868 4.868 ↑ 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.004..0.004 rows=1 loops=1,217)

  • Index Cond: ((conversationid = conversation.conversationid) AND ((role)::text = 'agent'::text))
39. 3.651 3.651 ↑ 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.003..0.003 rows=1 loops=1,217)

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