explain.depesz.com

PostgreSQL's explain analyze made readable

Result: G2Z

Settings
# exclusive inclusive rows x rows loops node
1. 0.034 6,549.813 ↑ 1.0 100 1
→

Limit (cost=1,798,636.99..1,798,637.24 rows=100 width=783) (actual time=6,549.750..6,549.813 rows=100 loops=1)

2. 16.365 6,549.779 ↑ 132.5 100 1
→

Sort (cost=1,798,636.99..1,798,670.10 rows=13,246 width=783) (actual time=6,549.749..6,549.779 rows=100 loops=1)

  • Sort Key: workorders_1.created_at DESC
  • Sort Method: top-N heapsort Memory: 802kB
3. 15.877 6,533.414 ↓ 1.0 13,413 1
→

Merge Join (cost=1,796,747.82..1,798,130.74 rows=13,246 width=783) (actual time=6,476.174..6,533.414 rows=13,413 loops=1)

  • Merge Cond: (workorders_1.id = workorders.id)
4. 10.119 6,511.323 ↓ 1.0 13,413 1
→

Unique (cost=1,796,747.53..1,796,813.76 rows=13,246 width=1,391) (actual time=6,476.143..6,511.323 rows=13,413 loops=1)

5. 213.941 6,501.204 ↓ 1.0 13,413 1
→

Sort (cost=1,796,747.53..1,796,780.65 rows=13,246 width=1,391) (actual time=6,476.135..6,501.204 rows=13,413 loops=1)

  • Sort Key: workorders_1.id
  • Sort Method: external sort Disk: 64952kB
6. 512.716 6,287.263 ↓ 1.0 13,413 1
→

Hash Left Join (cost=226.95..1,795,840.63 rows=13,246 width=1,391) (actual time=2.128..6,287.263 rows=13,413 loops=1)

  • Hash Cond: (workorders_1.placed_for = client_role.id)
7. 10.128 140.697 ↓ 1.3 13,413 1
→

Hash Left Join (cost=154.96..2,834.90 rows=10,451 width=1,372) (actual time=1.042..140.697 rows=13,413 loops=1)

  • Hash Cond: (internal_employee_roles.entity_id = assigned_to_address.entity_id)
8. 14.198 130.393 ↓ 1.6 13,413 1
→

Hash Left Join (cost=92.04..2,585.01 rows=8,246 width=1,359) (actual time=0.852..130.393 rows=13,413 loops=1)

  • Hash Cond: (COALESCE(creator_users.person_id, creator_users.organization_id) = creator_addresses.entity_id)
9. 11.173 115.848 ↓ 2.0 13,413 1
→

Hash Left Join (cost=29.23..2,372.63 rows=6,710 width=512) (actual time=0.490..115.848 rows=13,413 loops=1)

  • Hash Cond: (workorders_1.assigned_to = internal_employee_roles.id)
10. 12.747 104.572 ↓ 2.0 13,413 1
→

Hash Left Join (cost=22.54..2,337.82 rows=6,710 width=508) (actual time=0.378..104.572 rows=13,413 loops=1)

  • Hash Cond: (workorders_1.created_by = creator_users.id)
11. 12.997 91.722 ↓ 2.0 13,413 1
→

Hash Join (cost=13.84..2,236.86 rows=6,710 width=500) (actual time=0.263..91.722 rows=13,413 loops=1)

  • Hash Cond: (workorders_1.category_id = categories.id)
12. 17.732 78.581 ↓ 2.0 13,413 1
→

Hash Join (cost=6.18..2,136.94 rows=6,710 width=487) (actual time=0.105..78.581 rows=13,413 loops=1)

  • Hash Cond: ((workorders_1.priority_id = priorities.id) AND (workorders_1.workorder_class_id = workorder_classes.id))
13. 29.426 60.817 ↓ 2.0 13,413 1
→

Merge Join (cost=3.77..1,983.55 rows=6,710 width=391) (actual time=0.058..60.817 rows=13,413 loops=1)

  • Merge Cond: (workorders_1.id = not_to_exceeds.workorder_id)
14. 9.178 9.178 ↑ 1.0 13,413 1
→

Index Scan using workorders_pkey on workorders workorders_1 (cost=0.29..985.41 rows=13,413 width=383) (actual time=0.012..9.178 rows=13,413 loops=1)

15. 22.213 22.213 ↑ 1.0 13,461 1
→

Index Scan using index_not_to_exceeds_on_workorder_id on not_to_exceeds (cost=0.29..1,760.16 rows=13,461 width=12) (actual time=0.011..22.213 rows=13,461 loops=1)

  • Filter: (active AND ((type)::text = 'Workorders::ClientNotToExceed'::text))
  • Rows Removed by Filter: 13446
16. 0.003 0.032 ↑ 1.0 12 1
→

Hash (cost=2.23..2.23 rows=12 width=112) (actual time=0.032..0.032 rows=12 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
17. 0.007 0.029 ↑ 1.0 12 1
→

Nested Loop (cost=0.00..2.23 rows=12 width=112) (actual time=0.012..0.029 rows=12 loops=1)

18. 0.002 0.002 ↑ 1.0 4 1
→

Seq Scan on workorder_classes (cost=0.00..1.04 rows=4 width=40) (actual time=0.002..0.002 rows=4 loops=1)

19. 0.016 0.020 ↑ 1.0 3 4
→

Materialize (cost=0.00..1.04 rows=3 width=72) (actual time=0.002..0.005 rows=3 loops=4)

20. 0.004 0.004 ↑ 1.0 3 1
→

Seq Scan on priorities (cost=0.00..1.03 rows=3 width=72) (actual time=0.002..0.004 rows=3 loops=1)

21. 0.079 0.144 ↑ 1.0 207 1
→

Hash (cost=5.07..5.07 rows=207 width=21) (actual time=0.144..0.144 rows=207 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 20kB
22. 0.065 0.065 ↑ 1.0 207 1
→

Seq Scan on categories (cost=0.00..5.07 rows=207 width=21) (actual time=0.005..0.065 rows=207 loops=1)

23. 0.040 0.103 ↑ 1.0 120 1
→

Hash (cost=7.20..7.20 rows=120 width=16) (actual time=0.103..0.103 rows=120 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
24. 0.063 0.063 ↑ 1.0 120 1
→

Seq Scan on users creator_users (cost=0.00..7.20 rows=120 width=16) (actual time=0.003..0.063 rows=120 loops=1)

25. 0.050 0.103 ↑ 1.0 164 1
→

Hash (cost=4.64..4.64 rows=164 width=12) (actual time=0.103..0.103 rows=164 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 16kB
26. 0.053 0.053 ↑ 1.0 164 1
→

Seq Scan on roles internal_employee_roles (cost=0.00..4.64 rows=164 width=12) (actual time=0.003..0.053 rows=164 loops=1)

27. 0.124 0.347 ↑ 1.2 210 1
→

Hash (cost=59.63..59.63 rows=255 width=859) (actual time=0.347..0.347 rows=210 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 106kB
28. 0.223 0.223 ↑ 1.0 255 1
→

Seq Scan on addresses creator_addresses (cost=0.00..59.63 rows=255 width=859) (actual time=0.021..0.223 rows=255 loops=1)

  • Filter: default_address
  • Rows Removed by Filter: 8
29. 0.087 0.176 ↑ 1.2 218 1
→

Hash (cost=59.63..59.63 rows=263 width=21) (actual time=0.176..0.176 rows=218 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 19kB
30. 0.089 0.089 ↑ 1.0 263 1
→

Seq Scan on addresses assigned_to_address (cost=0.00..59.63 rows=263 width=21) (actual time=0.005..0.089 rows=263 loops=1)

31. 0.064 0.390 ↑ 1.0 208 1
→

Hash (cost=69.39..69.39 rows=208 width=27) (actual time=0.390..0.390 rows=208 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 18kB
32. 0.159 0.326 ↑ 1.0 208 1
→

Hash Right Join (cost=6.69..69.39 rows=208 width=27) (actual time=0.110..0.326 rows=208 loops=1)

  • Hash Cond: (client_address.entity_id = client_role.entity_id)
33. 0.070 0.070 ↑ 1.0 263 1
→

Seq Scan on addresses client_address (cost=0.00..59.63 rows=263 width=23) (actual time=0.003..0.070 rows=263 loops=1)

34. 0.054 0.097 ↑ 1.0 164 1
→

Hash (cost=4.64..4.64 rows=164 width=12) (actual time=0.097..0.097 rows=164 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 16kB
35. 0.043 0.043 ↑ 1.0 164 1
→

Seq Scan on roles client_role (cost=0.00..4.64 rows=164 width=12) (actual time=0.002..0.043 rows=164 loops=1)

36.          

SubPlan (forHash Left Join)
37. 26.826 134.130 ↑ 1.0 1 13,413
→

Aggregate (cost=16.62..16.63 rows=1 width=8) (actual time=0.010..0.010 rows=1 loops=13,413)

38. 40.263 107.304 ↑ 1.0 1 13,413
→

Nested Loop (cost=0.57..16.62 rows=1 width=8) (actual time=0.007..0.008 rows=1 loops=13,413)

39. 40.239 40.239 ↑ 1.0 1 13,413
→

Index Scan using index_assignments_on_workorder_id on assignments (cost=0.29..8.30 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=13,413)

  • Index Cond: (workorder_id = workorders_1.id)
40. 26.802 26.802 ↑ 1.0 1 13,401
→

Index Scan using index_not_to_exceeds_on_assignment_id on not_to_exceeds subcontractor_not_to_exceeds (cost=0.29..8.30 rows=1 width=12) (actual time=0.002..0.002 rows=1 loops=13,401)

  • Index Cond: (assignment_id = assignments.id)
41. 26.826 3,272.772 ↑ 1.0 1 13,413
→

Unique (cost=27.48..27.49 rows=1 width=933) (actual time=0.243..0.244 rows=1 loops=13,413)

42. 67.065 3,245.946 ↓ 2.0 2 13,413
→

Sort (cost=27.48..27.49 rows=1 width=933) (actual time=0.241..0.242 rows=2 loops=13,413)

  • Sort Key: addresses.entity_id
  • Sort Method: quicksort Memory: 29kB
43. 1,425.590 3,178.881 ↓ 2.0 2 13,413
→

Nested Loop (cost=9.15..27.47 rows=1 width=933) (actual time=0.110..0.237 rows=2 loops=13,413)

44. 68.461 1,622.973 ↓ 2.5 5 13,413
→

Nested Loop (cost=9.02..26.70 rows=2 width=937) (actual time=0.061..0.121 rows=5 loops=13,413)

45. 63.864 1,421.778 ↓ 2.5 5 13,413
→

Nested Loop (cost=8.88..26.36 rows=2 width=915) (actual time=0.058..0.106 rows=5 loops=13,413)

46. 53.717 1,260.822 ↓ 2.0 2 13,413
→

Nested Loop (cost=8.59..15.44 rows=1 width=903) (actual time=0.054..0.094 rows=2 loops=13,413)

47. 564.711 1,140.105 ↑ 1.0 1 13,413
→

Hash Join (cost=8.31..13.58 rows=1 width=44) (actual time=0.049..0.085 rows=1 loops=13,413)

  • Hash Cond: (assignment_roles.id = assignments_1.role_id)
48. 521.742 521.742 ↑ 1.0 164 13,378
→

Seq Scan on roles assignment_roles (cost=0.00..4.64 rows=164 width=12) (actual time=0.001..0.039 rows=164 loops=13,378)

49. 26.826 53.652 ↑ 1.0 1 13,413
→

Hash (cost=8.30..8.30 rows=1 width=36) (actual time=0.004..0.004 rows=1 loops=13,413)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
50. 26.826 26.826 ↑ 1.0 1 13,413
→

Index Scan using index_assignments_on_workorder_id on assignments assignments_1 (cost=0.29..8.30 rows=1 width=36) (actual time=0.002..0.002 rows=1 loops=13,413)

  • Index Cond: (workorder_id = workorders_1.id)
51. 67.000 67.000 ↓ 2.0 2 13,400
→

Index Scan using index_addresses_on_entity_id on addresses (cost=0.27..1.85 rows=1 width=859) (actual time=0.003..0.005 rows=2 loops=13,400)

  • Index Cond: (entity_id = assignment_roles.entity_id)
  • Filter: default_address
  • Rows Removed by Filter: 0
52. 97.092 97.092 ↑ 1.0 2 32,364
→

Index Scan using index_states_on_object_id on states (cost=0.29..10.90 rows=2 width=16) (actual time=0.002..0.003 rows=2 loops=32,364)

  • Index Cond: (object_id = assignments_1.id)
53. 132.734 132.734 ↑ 1.0 1 66,367
→

Index Scan using statuses_pkey on statuses (cost=0.14..0.16 rows=1 width=26) (actual time=0.002..0.002 rows=1 loops=66,367)

  • Index Cond: (id = states.status_id)
  • Filter: (workflow_type_id <> 7)
  • Rows Removed by Filter: 0
54. 130.318 130.318 ↓ 0.0 0 65,159
→

Index Scan using workflows_pkey on workflows (cost=0.13..0.37 rows=1 width=8) (actual time=0.001..0.002 rows=0 loops=65,159)

  • Index Cond: (id = statuses.workflow_id)
  • Filter: ((object_type)::text = 'Workorders::Assignment'::text)
  • Rows Removed by Filter: 1
55. 40.239 40.239 ↑ 1.0 1 13,413
→

Index Scan using index_purchase_order_numbers_on_workorder_id on purchase_order_numbers client_po_numbers (cost=0.29..8.30 rows=1 width=11) (actual time=0.003..0.003 rows=1 loops=13,413)

  • Index Cond: (workorders_1.id = workorder_id)
  • Filter: (active AND ((type)::text = 'Workorders::ClientPurchaseOrderNumber'::text))
56. 13.413 1,113.279 ↑ 1.0 1 13,413
→

Unique (cost=14.82..14.82 rows=1 width=899) (actual time=0.083..0.083 rows=1 loops=13,413)

57. 40.239 1,099.866 ↑ 1.0 1 13,413
→

Sort (cost=14.82..14.82 rows=1 width=899) (actual time=0.082..0.082 rows=1 loops=13,413)

  • Sort Key: addresses_1.facility_id
  • Sort Method: quicksort Memory: 27kB
58. 590.172 1,059.627 ↑ 1.0 1 13,413
→

Nested Loop (cost=8.59..14.81 rows=1 width=899) (actual time=0.065..0.079 rows=1 loops=13,413)

  • Join Filter: (workorder_facilities.facility_id = addresses_1.facility_id)
59. 187.782 429.216 ↑ 1.0 1 13,413
→

Hash Join (cost=8.31..10.94 rows=1 width=44) (actual time=0.019..0.032 rows=1 loops=13,413)

  • Hash Cond: (facilities.id = workorder_facilities.facility_id)
60. 187.782 187.782 ↑ 1.0 45 13,413
→

Seq Scan on facilities (cost=0.00..2.45 rows=45 width=40) (actual time=0.001..0.014 rows=45 loops=13,413)

61. 13.413 53.652 ↑ 1.0 1 13,413
→

Hash (cost=8.30..8.30 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=13,413)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
62. 40.239 40.239 ↑ 1.0 1 13,413
→

Index Scan using index_workorder_facilities_on_workorder_id on workorder_facilities (cost=0.29..8.30 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=13,413)

  • Index Cond: (workorder_id = workorders_1.id)
63. 40.239 40.239 ↑ 1.0 1 13,413
→

Index Scan using index_addresses_on_facility_id on addresses addresses_1 (cost=0.27..3.85 rows=1 width=859) (actual time=0.003..0.003 rows=1 loops=13,413)

  • Index Cond: (facility_id = facilities.id)
  • Filter: default_address
64. 13.413 147.543 ↓ 0.0 0 13,413
→

Limit (cost=31.26..31.26 rows=1 width=1,043) (actual time=0.011..0.011 rows=0 loops=13,413)

65. 26.826 134.130 ↓ 0.0 0 13,413
→

Sort (cost=31.26..31.26 rows=2 width=1,043) (actual time=0.010..0.010 rows=0 loops=13,413)

  • Sort Key: notes.created_at DESC
  • Sort Method: quicksort Memory: 25kB
66. 24.957 107.304 ↓ 0.0 0 13,413
→

Nested Loop (cost=4.99..31.25 rows=2 width=1,043) (actual time=0.007..0.008 rows=0 loops=13,413)

67. 12.167 80.478 ↓ 0.0 0 13,413
→

Nested Loop (cost=4.71..26.37 rows=2 width=196) (actual time=0.006..0.006 rows=0 loops=13,413)

68. 11.544 67.065 ↓ 0.0 0 13,413
→

Nested Loop (cost=4.57..25.93 rows=2 width=192) (actual time=0.005..0.005 rows=0 loops=13,413)

69. 13.413 53.652 ↓ 0.0 0 13,413
→

Bitmap Heap Scan on object_notes (cost=4.30..9.33 rows=2 width=4) (actual time=0.004..0.004 rows=0 loops=13,413)

  • Recheck Cond: (((notable_type)::text = 'Workorders::Workorder'::text) AND (notable_id = workorders_1.id))
  • Heap Blocks: exact=362
70. 40.239 40.239 ↓ 0.0 0 13,413
→

Bitmap Index Scan on index_object_notes_on_notable_type_and_notable_id (cost=0.00..4.29 rows=2 width=0) (actual time=0.003..0.003 rows=0 loops=13,413)

  • Index Cond: (((notable_type)::text = 'Workorders::Workorder'::text) AND (notable_id = workorders_1.id))
71. 1.869 1.869 ↑ 1.0 1 623
→

Index Scan using notes_pkey on notes (cost=0.28..8.29 rows=1 width=192) (actual time=0.002..0.003 rows=1 loops=623)

  • Index Cond: (id = object_notes.note_id)
72. 1.246 1.246 ↑ 1.0 1 623
→

Index Scan using users_pkey on users note_creator_users (cost=0.14..0.21 rows=1 width=16) (actual time=0.001..0.002 rows=1 loops=623)

  • Index Cond: (id = notes.created_by)
73. 1.869 1.869 ↑ 1.0 1 623
→

Index Scan using index_addresses_on_entity_id on addresses note_creator_addresses (cost=0.27..2.42 rows=1 width=859) (actual time=0.002..0.003 rows=1 loops=623)

  • Index Cond: (entity_id = COALESCE(note_creator_users.person_id, note_creator_users.organization_id))
  • Filter: default_address
74. 160.956 791.367 ↑ 1.0 1 13,413
→

Nested Loop (cost=11.19..13.94 rows=1 width=78) (actual time=0.033..0.059 rows=1 loops=13,413)

75. 25.226 603.585 ↑ 1.0 1 13,413
→

Nested Loop (cost=11.06..13.59 rows=1 width=42) (actual time=0.021..0.045 rows=1 loops=13,413)

76. 254.847 523.107 ↑ 1.0 2 13,413
→

Hash Join (cost=10.92..12.87 rows=2 width=46) (actual time=0.011..0.039 rows=2 loops=13,413)

  • Hash Cond: (statuses_1.id = states_1.status_id)
77. 187.782 187.782 ↑ 1.0 62 13,413
→

Seq Scan on statuses statuses_1 (cost=0.00..1.62 rows=62 width=30) (actual time=0.001..0.014 rows=62 loops=13,413)

78. 26.826 80.478 ↑ 1.0 2 13,413
→

Hash (cost=10.90..10.90 rows=2 width=20) (actual time=0.006..0.006 rows=2 loops=13,413)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
79. 53.652 53.652 ↑ 1.0 2 13,413
→

Index Scan using index_states_on_object_id on states states_1 (cost=0.29..10.90 rows=2 width=20) (actual time=0.003..0.004 rows=2 loops=13,413)

  • Index Cond: (object_id = workorders_1.id)
80. 55.252 55.252 ↓ 0.0 0 27,626
→

Index Scan using workflows_pkey on workflows workflows_1 (cost=0.13..0.35 rows=1 width=8) (actual time=0.002..0.002 rows=0 loops=27,626)

  • Index Cond: (id = statuses_1.workflow_id)
  • Filter: ((object_type)::text = 'Workorders::Workorder'::text)
  • Rows Removed by Filter: 1
81. 26.826 26.826 ↑ 1.0 1 13,413
→

Index Scan using workflow_types_pkey on workflow_types (cost=0.14..0.35 rows=1 width=40) (actual time=0.001..0.002 rows=1 loops=13,413)

  • Index Cond: (id = statuses_1.workflow_type_id)
82. 11.802 134.130 ↓ 0.0 0 13,413
→

Nested Loop (cost=5.13..22.88 rows=1 width=66) (actual time=0.010..0.010 rows=0 loops=13,413)

83. 11.802 120.717 ↓ 0.0 0 13,413
→

Nested Loop (cost=4.99..22.51 rows=1 width=70) (actual time=0.008..0.009 rows=0 loops=13,413)

84. 11.557 107.304 ↓ 0.0 0 13,413
→

Nested Loop (cost=4.85..22.34 rows=1 width=44) (actual time=0.008..0.008 rows=0 loops=13,413)

85. 26.862 93.891 ↓ 0.0 0 13,413
→

Nested Loop (cost=4.57..16.61 rows=1 width=40) (actual time=0.007..0.007 rows=0 loops=13,413)

86. 26.826 26.826 ↑ 1.0 1 13,413
→

Index Scan using index_assignments_on_workorder_id on assignments assignments_2 (cost=0.29..8.30 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=13,413)

  • Index Cond: (workorder_id = workorders_1.id)
87. 26.802 40.203 ↓ 0.0 0 13,401
→

Bitmap Heap Scan on work_efforts visits (cost=4.28..8.29 rows=1 width=40) (actual time=0.002..0.003 rows=0 loops=13,401)

  • Recheck Cond: (assignment_id = assignments_2.id)
  • Heap Blocks: exact=435
88. 13.401 13.401 ↓ 0.0 0 13,401
→

Bitmap Index Scan on index_work_efforts_on_assignment_id (cost=0.00..4.28 rows=1 width=0) (actual time=0.001..0.001 rows=0 loops=13,401)

  • Index Cond: (assignment_id = assignments_2.id)
89. 1.856 1.856 ↓ 1.5 3 464
→

Index Scan using index_states_on_object_id on states states_2 (cost=0.29..5.72 rows=2 width=8) (actual time=0.002..0.004 rows=3 loops=464)

  • Index Cond: (object_id = visits.id)
90. 1.611 1.611 ↑ 1.0 1 1,611
→

Index Scan using statuses_pkey on statuses statuses_2 (cost=0.14..0.16 rows=1 width=30) (actual time=0.001..0.001 rows=1 loops=1,611)

  • Index Cond: (id = states_2.status_id)
91. 1.611 1.611 ↓ 0.0 0 1,611
→

Index Scan using workflows_pkey on workflows workflows_2 (cost=0.13..0.35 rows=1 width=8) (actual time=0.001..0.001 rows=0 loops=1,611)

  • Index Cond: (id = statuses_2.workflow_id)
  • Filter: ((object_type)::text = 'Workorders::Visit'::text)
  • Rows Removed by Filter: 1
92. 6.214 6.214 ↑ 1.0 13,413 1
→

Index Only Scan using workorders_pkey on workorders (cost=0.29..985.41 rows=13,413 width=8) (actual time=0.023..6.214 rows=13,413 loops=1)

  • Heap Fetches: 13413