explain.depesz.com

PostgreSQL's explain analyze made readable

Result: CNCG

Settings
# exclusive inclusive rows x rows loops node
1. 0.674 9,092.795 ↑ 1.0 1 1

Aggregate (cost=79,226.76..79,226.77 rows=1 width=8) (actual time=9,092.795..9,092.795 rows=1 loops=1)

2. 2.214 9,092.121 ↓ 11.5 1,217 1

Nested Loop Left Join (cost=44,080.88..79,226.49 rows=106 width=0) (actual time=1,729.584..9,092.121 rows=1,217 loops=1)

3. 1.745 9,080.171 ↓ 11.5 1,217 1

Nested Loop Left Join (cost=44,080.04..77,433.77 rows=106 width=32) (actual time=1,729.568..9,080.171 rows=1,217 loops=1)

4. 1.177 8,356.745 ↓ 11.5 1,217 1

Hash Left Join (cost=44,063.14..75,639.72 rows=106 width=32) (actual time=1,729.053..8,356.745 rows=1,217 loops=1)

  • Hash Cond: (co.conversationownerid = cot.conversationownerid)
  • Join Filter: ((co.ownertype)::text = 'team'::text)
5. 55.464 8,354.138 ↓ 11.5 1,217 1

Hash Join (cost=44,036.59..75,612.76 rows=106 width=53) (actual time=1,727.612..8,354.138 rows=1,217 loops=1)

  • Hash Cond: (co.conversationownerid = coa.conversationownerid)
6. 93.038 8,184.848 ↓ 9.1 140,286 1

Hash Join (cost=41,979.18..73,496.31 rows=15,462 width=37) (actual time=1,608.288..8,184.848 rows=140,286 loops=1)

  • Hash Cond: (conversation.organizationid = organization.organizationid)
7. 132.286 8,091.208 ↓ 9.1 140,286 1

Nested Loop (cost=41,970.58..73,275.11 rows=15,462 width=53) (actual time=1,607.669..8,091.208 rows=140,286 loops=1)

8. 91.138 3,048.912 ↓ 9.1 140,286 1

Nested Loop (cost=41,970.16..58,848.46 rows=15,462 width=69) (actual time=1,607.651..3,048.912 rows=140,286 loops=1)

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

17. 78.874 651.001 ↓ 3.0 163,126 1

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

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

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

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

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

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

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

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

  • Filter: ((channeltype)::text = 'sms'::text)
22. 982.002 982.002 ↑ 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.006..0.007 rows=1 loops=140,286)

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

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

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

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

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

26. 0.707 113.826 ↓ 1.1 1,217 1

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

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

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

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

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

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

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

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

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

31. 1.217 721.681 ↑ 1.0 1 1,217

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

32. 8.519 720.464 ↑ 1.0 1 1,217

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

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

Nested Loop (cost=0.84..16.89 rows=1 width=628) (actual time=0.580..0.585 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.003..0.004 rows=1 loops=1,217)

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

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

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