explain.depesz.com

PostgreSQL's explain analyze made readable

Result: vx0d

Settings
# exclusive inclusive rows x rows loops node
1. 0.398 2,995.949 ↑ 1.0 1 1

Aggregate (cost=41,933.21..41,933.22 rows=1 width=8) (actual time=2,995.949..2,995.949 rows=1 loops=1)

2. 1.038 2,995.551 ↓ 11.0 1,732 1

Nested Loop Left Join (cost=18,959.13..41,933.13 rows=158 width=0) (actual time=317.442..2,995.551 rows=1,732 loops=1)

3. 0.528 2,984.121 ↓ 11.0 1,732 1

Nested Loop Left Join (cost=18,958.96..40,639.43 rows=158 width=32) (actual time=317.430..2,984.121 rows=1,732 loops=1)

4. 0.587 2,962.809 ↓ 11.0 1,732 1

Nested Loop Left Join (cost=18,950.78..39,345.33 rows=158 width=32) (actual time=317.396..2,962.809 rows=1,732 loops=1)

  • Join Filter: ((co.ownertype)::text = 'team'::text)
5. 34.816 2,958.758 ↓ 11.0 1,732 1

Hash Join (cost=18,950.72..39,335.34 rows=158 width=53) (actual time=317.389..2,958.758 rows=1,732 loops=1)

  • Hash Cond: (co.conversationownerid = coa.conversationownerid)
6. 100.498 2,922.357 ↓ 8.8 150,556 1

Nested Loop (cost=17,122.55..37,493.91 rows=17,037 width=37) (actual time=290.913..2,922.357 rows=150,556 loops=1)

  • Join Filter: (conversation.conversationid = co.conversationid)
7. 47.278 2,367.794 ↓ 9.0 151,355 1

Hash Join (cost=17,122.46..35,129.01 rows=16,862 width=48) (actual time=290.899..2,367.794 rows=151,355 loops=1)

  • Hash Cond: (conversation.organizationid = organization.organizationid)
8. 85.772 2,320.452 ↓ 9.0 151,355 1

Nested Loop (cost=17,111.42..35,054.73 rows=16,862 width=64) (actual time=290.825..2,320.452 rows=151,355 loops=1)

9. 53.669 1,780.615 ↓ 9.0 151,355 1

Nested Loop (cost=17,111.33..30,384.76 rows=16,862 width=80) (actual time=290.813..1,780.615 rows=151,355 loops=1)

  • Join Filter: (conversation.conversationid = p_system.conversationid)
10. 146.143 1,121.526 ↓ 9.0 151,355 1

Nested Loop (cost=17,111.25..26,536.87 rows=16,889 width=64) (actual time=290.792..1,121.526 rows=151,355 loops=1)

11. 118.364 443.897 ↓ 9.0 177,162 1

Hash Join (cost=17,111.16..22,611.13 rows=19,763 width=32) (actual time=290.768..443.897 rows=177,162 loops=1)

  • Hash Cond: (pc.participantid = p_candidate.participantid)
12. 34.860 34.860 ↓ 1.0 177,162 1

Seq Scan on participantcandidate pc (cost=0.00..5,311.14 rows=172,713 width=32) (actual time=0.006..34.860 rows=177,162 loops=1)

13. 45.024 290.673 ↓ 2.9 177,162 1

Hash (cost=16,900.74..16,900.74 rows=60,120 width=48) (actual time=290.673..290.673 rows=177,162 loops=1)

  • Buckets: 262144 (originally 65536) Batches: 1 (originally 1) Memory Usage: 15889kB
14. 79.460 245.649 ↓ 2.9 177,162 1

Hash Join (cost=4,547.24..16,900.74 rows=60,120 width=48) (actual time=71.530..245.649 rows=177,162 loops=1)

  • Hash Cond: (p_candidate.participantid = pcchan.participantid)
15. 94.942 94.942 ↓ 1.0 177,162 1

Seq Scan on participant p_candidate (cost=0.00..12,040.92 rows=176,292 width=32) (actual time=0.007..94.942 rows=177,162 loops=1)

  • Filter: ((role)::text = 'candidate'::text)
  • Rows Removed by Filter: 353646
16. 31.680 71.247 ↑ 1.0 177,162 1

Hash (cost=3,920.12..3,920.12 rows=179,177 width=16) (actual time=71.247..71.247 rows=177,162 loops=1)

  • Buckets: 262144 Batches: 1 Memory Usage: 10353kB
17. 39.567 39.567 ↑ 1.0 177,162 1

Seq Scan on participantchannel pcchan (cost=0.00..3,920.12 rows=179,177 width=16) (actual time=0.008..39.567 rows=177,162 loops=1)

  • Filter: ((channeltype)::text = 'sms'::text)
18. 531.486 531.486 ↑ 1.0 1 177,162

Index Scan using pk_conversation on conversation (cost=0.08..0.20 rows=1 width=32) (actual time=0.003..0.003 rows=1 loops=177,162)

  • Index Cond: (conversationid = p_candidate.conversationid)
  • Filter: ((status)::text = 'active'::text)
  • Rows Removed by Filter: 0
19. 605.420 605.420 ↑ 1.0 1 151,355

Index Only Scan using idx_participant_3 on participant p_system (cost=0.08..0.22 rows=1 width=16) (actual time=0.004..0.004 rows=1 loops=151,355)

  • Index Cond: ((conversationid = p_candidate.conversationid) AND (role = 'system'::text))
  • Heap Fetches: 130020
20. 454.065 454.065 ↑ 1.0 1 151,355

Index Only Scan using idx_candidate_1 on candidate (cost=0.08..0.27 rows=1 width=16) (actual time=0.003..0.003 rows=1 loops=151,355)

  • Index Cond: (candidateid = pc.candidateid)
  • Heap Fetches: 64315
21. 0.020 0.064 ↓ 1.0 166 1

Hash (cost=10.48..10.48 rows=161 width=16) (actual time=0.064..0.064 rows=166 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 16kB
22. 0.044 0.044 ↓ 1.0 166 1

Seq Scan on organization (cost=0.00..10.48 rows=161 width=16) (actual time=0.006..0.044 rows=166 loops=1)

23. 454.065 454.065 ↑ 1.0 1 151,355

Index Scan using idx_conversationowner_2 on conversationowner co (cost=0.08..0.14 rows=1 width=37) (actual time=0.003..0.003 rows=1 loops=151,355)

  • Index Cond: (conversationid = p_candidate.conversationid)
  • Filter: ((ownertype)::text = 'agent'::text)
  • Rows Removed by Filter: 0
24. 0.270 1.585 ↓ 1.0 1,732 1

Hash (cost=1,822.36..1,822.36 rows=1,661 width=32) (actual time=1.585..1.585 rows=1,732 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 125kB
25. 1.023 1.315 ↓ 1.0 1,732 1

Bitmap Heap Scan on conversationowneragent coa (cost=20.66..1,822.36 rows=1,661 width=32) (actual time=0.340..1.315 rows=1,732 loops=1)

  • Recheck Cond: (agentid = 'dbb6b117-b9c9-4457-bac9-174c169653df'::uuid)
  • Heap Blocks: exact=328
26. 0.292 0.292 ↓ 1.0 1,732 1

Bitmap Index Scan on idx_conversationowneragent_3 (cost=0.00..20.58 rows=1,661 width=0) (actual time=0.292..0.292 rows=1,732 loops=1)

  • Index Cond: (agentid = 'dbb6b117-b9c9-4457-bac9-174c169653df'::uuid)
27. 3.464 3.464 ↓ 0.0 0 1,732

Index Only Scan using idx_conversationownerteam_2 on conversationownerteam cot (cost=0.06..0.06 rows=1 width=16) (actual time=0.002..0.002 rows=0 loops=1,732)

  • Index Cond: (conversationownerid = co.conversationownerid)
  • Heap Fetches: 0
28. 1.732 20.784 ↑ 1.0 1 1,732

Limit (cost=8.18..8.18 rows=1 width=628) (actual time=0.011..0.012 rows=1 loops=1,732)

29. 3.464 19.052 ↑ 1.0 1 1,732

Sort (cost=8.18..8.18 rows=1 width=628) (actual time=0.011..0.011 rows=1 loops=1,732)

  • Sort Key: _p_agent.lastmessagedatetime DESC
  • Sort Method: quicksort Memory: 25kB
30. 1.732 15.588 ↑ 1.0 1 1,732

Nested Loop (cost=0.17..8.18 rows=1 width=628) (actual time=0.008..0.009 rows=1 loops=1,732)

31. 5.196 5.196 ↑ 1.0 1 1,732

Index Scan using idx_participant_3 on participant _p_agent (cost=0.08..4.09 rows=1 width=24) (actual time=0.003..0.003 rows=1 loops=1,732)

  • Index Cond: ((conversationid = conversation.conversationid) AND ((role)::text = 'agent'::text))
32. 8.660 8.660 ↑ 1.0 1 1,732

Index Only Scan using idx_participantagent_3 on participantagent _pa (cost=0.08..4.09 rows=1 width=16) (actual time=0.004..0.005 rows=1 loops=1,732)

  • Index Cond: (participantid = _p_agent.participantid)
  • Heap Fetches: 1732
33. 0.000 10.392 ↑ 1.0 1 1,732

Limit (cost=0.17..8.18 rows=1 width=628) (actual time=0.006..0.006 rows=1 loops=1,732)

34. 3.464 10.392 ↑ 1.0 1 1,732

Nested Loop (cost=0.17..8.18 rows=1 width=628) (actual time=0.006..0.006 rows=1 loops=1,732)

35. 3.464 3.464 ↑ 1.0 1 1,732

Index Scan using idx_participant_3 on participant _p_agent_1 (cost=0.08..4.09 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=1,732)

  • Index Cond: ((conversationid = conversation.conversationid) AND ((role)::text = 'agent'::text))
36. 3.464 3.464 ↑ 1.0 1 1,732

Index Only Scan using idx_participantagent_3 on participantagent _pa_1 (cost=0.08..4.09 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=1,732)

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