explain.depesz.com

PostgreSQL's explain analyze made readable

Result: XQNm

Settings
# exclusive inclusive rows x rows loops node
1. 0.007 17,550.301 ↑ 1.0 1 1

Nested Loop Left Join (cost=135,722.78..223,762.43 rows=1 width=1,301) (actual time=3,982.605..17,550.301 rows=1 loops=1)

2. 0.004 17,550.289 ↑ 1.0 1 1

Nested Loop Left Join (cost=135,722.49..223,754.12 rows=1 width=1,273) (actual time=3,982.595..17,550.289 rows=1 loops=1)

3. 0.367 17,550.239 ↑ 1.0 1 1

Nested Loop Left Join (cost=135,721.06..223,751.20 rows=1 width=1,241) (actual time=3,982.546..17,550.239 rows=1 loops=1)

  • Join Filter: (a_2.agent_id = issue_detail.reporter_id)
  • Rows Removed by Join Filter: 1,284
4. 0.007 17,539.031 ↑ 1.0 1 1

Nested Loop Left Join (cost=135,720.63..207,279.86 rows=1 width=1,209) (actual time=3,971.341..17,539.031 rows=1 loops=1)

5. 0.445 17,539.013 ↑ 1.0 1 1

Nested Loop Left Join (cost=135,720.35..207,271.55 rows=1 width=1,190) (actual time=3,971.324..17,539.013 rows=1 loops=1)

  • Join Filter: (a_1.agent_id = issue_detail.submitted_by)
  • Rows Removed by Join Filter: 1,284
6. 0.001 12.191 ↑ 1.0 1 1

Nested Loop Left Join (cost=1.84..17,187.48 rows=1 width=1,094) (actual time=11.466..12.191 rows=1 loops=1)

7. 0.003 12.183 ↑ 1.0 1 1

Nested Loop Left Join (cost=1.55..17,179.17 rows=1 width=1,075) (actual time=11.462..12.183 rows=1 loops=1)

8. 0.003 12.174 ↑ 1.0 1 1

Nested Loop Left Join (cost=1.27..17,170.86 rows=1 width=1,056) (actual time=11.456..12.174 rows=1 loops=1)

9. 0.386 12.171 ↑ 1.0 1 1

Nested Loop Left Join (cost=0.99..17,162.55 rows=1 width=1,037) (actual time=11.454..12.171 rows=1 loops=1)

  • Join Filter: (a.agent_id = issue_detail.assign_to)
  • Rows Removed by Join Filter: 1,284
10. 0.004 1.114 ↑ 1.0 1 1

Nested Loop Left Join (cost=0.56..691.21 rows=1 width=1,005) (actual time=0.403..1.114 rows=1 loops=1)

11. 1.102 1.102 ↑ 1.0 1 1

Index Scan using idx_issue_detail_reporting_agency on issue_detail (cost=0.28..682.90 rows=1 width=986) (actual time=0.393..1.102 rows=1 loops=1)

  • Filter: (issue_detail_id = 3,552)
  • Rows Removed by Filter: 3,551
12. 0.008 0.008 ↑ 1.0 1 1

Index Scan using idx_ctct_referral_type_id on look_up status (cost=0.28..8.30 rows=1 width=27) (actual time=0.007..0.008 rows=1 loops=1)

  • Index Cond: (look_up_id = issue_detail.status_id)
13. 6.288 10.671 ↑ 1.6 1,285 1

Nested Loop Left Join (cost=0.43..16,425.31 rows=2,046 width=48) (actual time=0.044..10.671 rows=1,285 loops=1)

14. 0.528 0.528 ↑ 1.6 1,285 1

Seq Scan on agent a (cost=0.00..178.46 rows=2,046 width=16) (actual time=0.005..0.528 rows=1,285 loops=1)

15. 3.855 3.855 ↑ 1.0 1 1,285

Index Scan using contact_pkey on contact (cost=0.43..7.41 rows=1 width=48) (actual time=0.002..0.003 rows=1 loops=1,285)

  • Index Cond: (contact_id = a.contact_id)
16. 0.000 0.000 ↓ 0.0 0 1

Index Scan using idx_ctct_referral_type_id on look_up priority (cost=0.28..8.30 rows=1 width=27) (actual time=0.000..0.000 rows=0 loops=1)

  • Index Cond: (look_up_id = issue_detail.priority_id)
17. 0.006 0.006 ↑ 1.0 1 1

Index Scan using idx_ctct_referral_type_id on look_up issue_module_type (cost=0.28..8.30 rows=1 width=27) (actual time=0.004..0.006 rows=1 loops=1)

  • Index Cond: (look_up_id = issue_detail.issue_module_type_id)
18. 0.007 0.007 ↑ 1.0 1 1

Index Scan using idx_ctct_referral_type_id on look_up severity (cost=0.28..8.30 rows=1 width=27) (actual time=0.003..0.007 rows=1 loops=1)

  • Index Cond: (look_up_id = issue_detail.severity_id)
19. 304.343 17,526.377 ↑ 1.6 1,285 1

Merge Right Join (cost=135,718.51..190,038.03 rows=2,046 width=112) (actual time=3,959.853..17,526.377 rows=1,285 loops=1)

  • Merge Cond: (phone_number.contact_id = contact_1.contact_id)
20. 9,245.668 12,554.012 ↓ 8.0 1,007,448 1

GroupAggregate (cost=72,916.06..107,126.80 rows=125,544 width=40) (actual time=2,905.839..12,554.012 rows=1,007,448 loops=1)

  • Group Key: phone_number.contact_id
21. 1,511.409 3,308.344 ↓ 8.8 1,105,749 1

Sort (cost=72,916.06..73,229.92 rows=125,544 width=40) (actual time=2,905.768..3,308.344 rows=1,105,749 loops=1)

  • Sort Key: phone_number.contact_id
  • Sort Method: external sort Disk: 48,512kB
22. 617.676 1,796.935 ↓ 8.8 1,107,873 1

Hash Join (cost=360.69..58,850.34 rows=125,544 width=40) (actual time=4.857..1,796.935 rows=1,107,873 loops=1)

  • Hash Cond: (phone_number.phone_number_type_id = look_up.look_up_id)
23. 1,174.433 1,174.433 ↓ 8.8 1,185,351 1

Seq Scan on phone_number (cost=0.00..56,731.80 rows=133,976 width=29) (actual time=0.011..1,174.433 rows=1,185,351 loops=1)

  • Filter: ((contact_id IS NOT NULL) AND (('now'::cstring)::date >= COALESCE(starts, '1901-01-01'::date)) AND (('now'::cstring)::date <= COALESCE(expires, '9999-12-31'::date)))
  • Rows Removed by Filter: 71,408
24. 2.294 4.826 ↓ 1.0 7,365 1

Hash (cost=268.64..268.64 rows=7,364 width=27) (actual time=4.826..4.826 rows=7,365 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 514kB
25. 2.532 2.532 ↓ 1.0 7,365 1

Seq Scan on look_up (cost=0.00..268.64 rows=7,364 width=27) (actual time=0.003..2.532 rows=7,365 loops=1)

26. 0.910 4,668.022 ↑ 1.6 1,285 1

Materialize (cost=62,802.45..80,270.80 rows=2,046 width=88) (actual time=1,053.981..4,668.022 rows=1,285 loops=1)

27. 118.612 4,667.112 ↑ 1.6 1,285 1

Merge Left Join (cost=62,802.45..80,265.68 rows=2,046 width=88) (actual time=1,053.979..4,667.112 rows=1,285 loops=1)

  • Merge Cond: (contact_1.contact_id = email.contact_id)
28. 1.225 7.072 ↑ 1.6 1,285 1

Sort (cost=15,473.91..15,479.02 rows=2,046 width=56) (actual time=6.534..7.072 rows=1,285 loops=1)

  • Sort Key: contact_1.contact_id
  • Sort Method: quicksort Memory: 226kB
29. 1.494 5.847 ↑ 1.6 1,285 1

Nested Loop Left Join (cost=0.43..15,361.39 rows=2,046 width=56) (actual time=0.029..5.847 rows=1,285 loops=1)

30. 0.498 0.498 ↑ 1.6 1,285 1

Seq Scan on agent a_1 (cost=0.00..178.46 rows=2,046 width=16) (actual time=0.008..0.498 rows=1,285 loops=1)

31. 3.855 3.855 ↑ 1.0 1 1,285

Index Scan using contact_pkey on contact contact_1 (cost=0.43..7.41 rows=1 width=48) (actual time=0.002..0.003 rows=1 loops=1,285)

  • Index Cond: (contact_id = a_1.contact_id)
32. 204.844 4,541.428 ↓ 6.1 363,263 1

Materialize (cost=47,328.55..64,631.91 rows=59,460 width=40) (actual time=1,047.440..4,541.428 rows=363,263 loops=1)

33. 3,127.656 4,336.584 ↓ 6.1 363,263 1

GroupAggregate (cost=47,328.55..63,888.66 rows=59,460 width=40) (actual time=1,047.438..4,336.584 rows=363,263 loops=1)

  • Group Key: email.contact_id
34. 418.504 1,208.928 ↓ 6.0 364,749 1

Sort (cost=47,328.55..47,480.60 rows=60,821 width=33) (actual time=1,047.355..1,208.928 rows=364,749 loops=1)

  • Sort Key: email.contact_id
  • Sort Method: external merge Disk: 9,736kB
35. 235.025 790.424 ↓ 6.0 365,380 1

Hash Join (cost=360.69..40,829.62 rows=60,821 width=33) (actual time=4.803..790.424 rows=365,380 loops=1)

  • Hash Cond: (email.email_type_id = look_up_1.look_up_id)
36. 550.627 550.627 ↓ 8.7 530,689 1

Seq Scan on email (cost=0.00..39,772.48 rows=60,821 width=22) (actual time=0.018..550.627 rows=530,689 loops=1)

  • Filter: ((contact_id IS NOT NULL) AND (('now'::cstring)::date >= COALESCE(starts, '1901-01-01'::date)) AND (('now'::cstring)::date <= COALESCE(expires, '9999-12-31'::date)))
  • Rows Removed by Filter: 332,173
37. 2.286 4.772 ↓ 1.0 7,365 1

Hash (cost=268.64..268.64 rows=7,364 width=27) (actual time=4.772..4.772 rows=7,365 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 514kB
38. 2.486 2.486 ↓ 1.0 7,365 1

Seq Scan on look_up look_up_1 (cost=0.00..268.64 rows=7,364 width=27) (actual time=0.005..2.486 rows=7,365 loops=1)

39. 0.011 0.011 ↑ 1.0 1 1

Index Scan using idx_ctct_referral_type_id on look_up issue_detail_type (cost=0.28..8.30 rows=1 width=27) (actual time=0.011..0.011 rows=1 loops=1)

  • Index Cond: (look_up_id = issue_detail.issue_type_id)
40. 6.470 10.841 ↑ 1.6 1,285 1

Nested Loop Left Join (cost=0.43..16,425.31 rows=2,046 width=48) (actual time=0.034..10.841 rows=1,285 loops=1)

41. 0.516 0.516 ↑ 1.6 1,285 1

Seq Scan on agent a_2 (cost=0.00..178.46 rows=2,046 width=16) (actual time=0.005..0.516 rows=1,285 loops=1)

42. 3.855 3.855 ↑ 1.0 1 1,285

Index Scan using contact_pkey on contact contact_2 (cost=0.43..7.41 rows=1 width=48) (actual time=0.002..0.003 rows=1 loops=1,285)

  • Index Cond: (contact_id = a_2.contact_id)
43. 0.005 0.046 ↑ 1.0 1 1

GroupAggregate (cost=1.44..2.89 rows=1 width=40) (actual time=0.045..0.046 rows=1 loops=1)

  • Group Key: aa.issue_detail_id
44. 0.021 0.041 ↑ 1.0 1 1

Hash Join (cost=1.44..2.87 rows=1 width=24) (actual time=0.040..0.041 rows=1 loops=1)

  • Hash Cond: (bb.watcher_id = aa.watcher_id)
45. 0.009 0.009 ↓ 1.0 32 1

Seq Scan on watcher bb (cost=0.00..1.31 rows=31 width=24) (actual time=0.003..0.009 rows=32 loops=1)

46. 0.002 0.011 ↑ 1.0 1 1

Hash (cost=1.43..1.43 rows=1 width=16) (actual time=0.011..0.011 rows=1 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
47. 0.009 0.009 ↑ 1.0 1 1

Seq Scan on issue_detail_watchers aa (cost=0.00..1.43 rows=1 width=16) (actual time=0.008..0.009 rows=1 loops=1)

  • Filter: (issue_detail_id = issue_detail.issue_detail_id)
  • Rows Removed by Filter: 34
48. 0.005 0.005 ↑ 1.0 1 1

Index Scan using agency_pkey on agency reporting_agency (cost=0.29..8.30 rows=1 width=36) (actual time=0.005..0.005 rows=1 loops=1)

  • Index Cond: (agency_id = issue_detail.reporting_agency)
Planning time : 6.972 ms
Execution time : 17,559.551 ms