explain.depesz.com

PostgreSQL's explain analyze made readable

Result: pKYp

Settings
# exclusive inclusive rows x rows loops node
1. 0.056 59.950 ↑ 1.0 100 1

Limit (cost=5.17..13,983.19 rows=100 width=1,391) (actual time=0.709..59.950 rows=100 loops=1)

2. 0.386 59.894 ↑ 132.5 100 1

Unique (cost=5.17..1,851,534.04 rows=13,246 width=1,391) (actual time=0.708..59.894 rows=100 loops=1)

3. 4.082 59.508 ↑ 132.5 100 1

Nested Loop Left Join (cost=5.17..1,851,500.92 rows=13,246 width=1,391) (actual time=0.704..59.508 rows=100 loops=1)

4. 0.144 3.326 ↑ 104.5 100 1

Nested Loop Left Join (cost=4.89..39,289.86 rows=10,451 width=1,376) (actual time=0.095..3.326 rows=100 loops=1)

5. 0.123 2.982 ↑ 82.5 100 1

Nested Loop Left Join (cost=4.62..23,944.26 rows=8,246 width=1,363) (actual time=0.091..2.982 rows=100 loops=1)

6. 0.158 2.659 ↑ 82.5 100 1

Nested Loop Left Join (cost=4.48..22,509.53 rows=8,246 width=1,359) (actual time=0.087..2.659 rows=100 loops=1)

7. 0.135 2.201 ↑ 67.1 100 1

Nested Loop Left Join (cost=4.20..6,181.86 rows=6,710 width=512) (actual time=0.078..2.201 rows=100 loops=1)

8. 0.182 1.866 ↑ 67.1 100 1

Nested Loop Left Join (cost=4.06..5,014.38 rows=6,710 width=508) (actual time=0.074..1.866 rows=100 loops=1)

9. 0.248 1.484 ↑ 67.1 100 1

Nested Loop (cost=3.92..3,857.67 rows=6,710 width=500) (actual time=0.069..1.484 rows=100 loops=1)

  • Join Filter: (workorders.workorder_class_id = workorder_classes.id)
  • Rows Removed by Join Filter: 298
10. 0.211 1.136 ↑ 67.1 100 1

Nested Loop (cost=3.92..3,454.02 rows=6,710 width=468) (actual time=0.065..1.136 rows=100 loops=1)

  • Join Filter: (workorders.priority_id = priorities.id)
  • Rows Removed by Join Filter: 200
11. 0.169 0.825 ↑ 67.1 100 1

Nested Loop (cost=3.92..3,151.03 rows=6,710 width=404) (actual time=0.058..0.825 rows=100 loops=1)

12. 0.216 0.456 ↑ 67.1 100 1

Merge Join (cost=3.77..1,983.55 rows=6,710 width=391) (actual time=0.053..0.456 rows=100 loops=1)

  • Merge Cond: (workorders.id = not_to_exceeds.workorder_id)
13. 0.097 0.097 ↑ 134.1 100 1

Index Scan using workorders_pkey on workorders (cost=0.29..985.41 rows=13,413 width=383) (actual time=0.008..0.097 rows=100 loops=1)

14. 0.143 0.143 ↑ 91.0 148 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.008..0.143 rows=148 loops=1)

  • Filter: (active AND ((type)::text = 'Workorders::ClientNotToExceed'::text))
15. 0.200 0.200 ↑ 1.0 1 100

Index Scan using categories_pkey on categories (cost=0.14..0.16 rows=1 width=21) (actual time=0.002..0.002 rows=1 loops=100)

  • Index Cond: (id = workorders.category_id)
16. 0.096 0.100 ↑ 1.0 3 100

Materialize (cost=0.00..1.04 rows=3 width=72) (actual time=0.000..0.001 rows=3 loops=100)

17. 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)

18. 0.098 0.100 ↑ 1.0 4 100

Materialize (cost=0.00..1.06 rows=4 width=40) (actual time=0.000..0.001 rows=4 loops=100)

19. 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.001..0.002 rows=4 loops=1)

20. 0.200 0.200 ↑ 1.0 1 100

Index Scan using users_pkey on users creator_users (cost=0.14..0.16 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=100)

  • Index Cond: (workorders.created_by = id)
21. 0.200 0.200 ↑ 1.0 1 100

Index Scan using roles_pkey on roles internal_employee_roles (cost=0.14..0.16 rows=1 width=12) (actual time=0.001..0.002 rows=1 loops=100)

  • Index Cond: (id = workorders.assigned_to)
22. 0.300 0.300 ↑ 1.0 1 100

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

  • Index Cond: (entity_id = COALESCE(creator_users.person_id, creator_users.organization_id))
  • Filter: default_address
23. 0.200 0.200 ↑ 1.0 1 100

Index Scan using roles_pkey on roles client_role (cost=0.14..0.16 rows=1 width=12) (actual time=0.001..0.002 rows=1 loops=100)

  • Index Cond: (id = workorders.placed_for)
24. 0.200 0.200 ↑ 1.0 1 100

Index Scan using index_addresses_on_entity_id on addresses assigned_to_address (cost=0.27..1.85 rows=1 width=21) (actual time=0.002..0.002 rows=1 loops=100)

  • Index Cond: (entity_id = internal_employee_roles.entity_id)
25. 0.200 0.200 ↑ 1.0 1 100

Index Scan using index_addresses_on_entity_id on addresses client_address (cost=0.27..1.85 rows=1 width=23) (actual time=0.002..0.002 rows=1 loops=100)

  • Index Cond: (entity_id = client_role.entity_id)
26.          

SubPlan (forNested Loop Left Join)

27. 0.200 0.900 ↑ 1.0 1 100

Aggregate (cost=16.62..16.63 rows=1 width=8) (actual time=0.009..0.009 rows=1 loops=100)

28. 0.088 0.700 ↑ 1.0 1 100

Nested Loop (cost=0.57..16.62 rows=1 width=8) (actual time=0.006..0.007 rows=1 loops=100)

29. 0.300 0.300 ↑ 1.0 1 100

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

  • Index Cond: (workorder_id = workorders.id)
30. 0.312 0.312 ↑ 1.0 1 104

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.003 rows=1 loops=104)

  • Index Cond: (assignment_id = assignments.id)
31. 0.200 23.400 ↑ 1.0 1 100

Unique (cost=27.48..27.49 rows=1 width=933) (actual time=0.233..0.234 rows=1 loops=100)

32. 0.400 23.200 ↓ 2.0 2 100

Sort (cost=27.48..27.49 rows=1 width=933) (actual time=0.232..0.232 rows=2 loops=100)

  • Sort Key: addresses.entity_id
  • Sort Method: quicksort Memory: 29kB
33. 10.037 22.800 ↓ 2.0 2 100

Nested Loop (cost=9.15..27.47 rows=1 width=933) (actual time=0.124..0.228 rows=2 loops=100)

34. 0.520 12.100 ↓ 3.5 7 100

Nested Loop (cost=9.02..26.70 rows=2 width=937) (actual time=0.072..0.121 rows=7 loops=100)

35. 0.560 10.200 ↓ 3.5 7 100

Nested Loop (cost=8.88..26.36 rows=2 width=915) (actual time=0.069..0.102 rows=7 loops=100)

36. 0.384 8.800 ↓ 2.0 2 100

Nested Loop (cost=8.59..15.44 rows=1 width=903) (actual time=0.066..0.088 rows=2 loops=100)

37. 4.085 8.000 ↑ 1.0 1 100

Hash Join (cost=8.31..13.58 rows=1 width=44) (actual time=0.061..0.080 rows=1 loops=100)

  • Hash Cond: (assignment_roles.id = assignments_1.role_id)
38. 3.515 3.515 ↑ 1.0 164 95

Seq Scan on roles assignment_roles (cost=0.00..4.64 rows=164 width=12) (actual time=0.001..0.037 rows=164 loops=95)

39. 0.100 0.400 ↑ 1.0 1 100

Hash (cost=8.30..8.30 rows=1 width=36) (actual time=0.004..0.004 rows=1 loops=100)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
40. 0.300 0.300 ↑ 1.0 1 100

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.003 rows=1 loops=100)

  • Index Cond: (workorder_id = workorders.id)
41. 0.416 0.416 ↓ 2.0 2 104

Index Scan using index_addresses_on_entity_id on addresses (cost=0.27..1.85 rows=1 width=859) (actual time=0.003..0.004 rows=2 loops=104)

  • Index Cond: (entity_id = assignment_roles.entity_id)
  • Filter: default_address
  • Rows Removed by Filter: 0
42. 0.840 0.840 ↓ 1.5 3 210

Index Scan using index_states_on_object_id on states (cost=0.29..10.90 rows=2 width=16) (actual time=0.002..0.004 rows=3 loops=210)

  • Index Cond: (object_id = assignments_1.id)
43. 1.380 1.380 ↑ 1.0 1 690

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

  • Index Cond: (id = states.status_id)
  • Filter: (workflow_type_id <> 7)
  • Rows Removed by Filter: 0
44. 0.663 0.663 ↓ 0.0 0 663

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

  • Index Cond: (id = statuses.workflow_id)
  • Filter: ((object_type)::text = 'Workorders::Assignment'::text)
  • Rows Removed by Filter: 1
45. 0.200 0.200 ↓ 0.0 0 100

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.002..0.002 rows=0 loops=100)

  • Index Cond: (workorders.id = workorder_id)
  • Filter: (active AND ((type)::text = 'Workorders::ClientPurchaseOrderNumber'::text))
46. 0.200 7.700 ↑ 1.0 1 100

Unique (cost=14.82..14.82 rows=1 width=899) (actual time=0.076..0.077 rows=1 loops=100)

47. 0.300 7.500 ↑ 1.0 1 100

Sort (cost=14.82..14.82 rows=1 width=899) (actual time=0.075..0.075 rows=1 loops=100)

  • Sort Key: addresses_1.facility_id
  • Sort Method: quicksort Memory: 27kB
48. 3.900 7.200 ↑ 1.0 1 100

Nested Loop (cost=8.59..14.81 rows=1 width=899) (actual time=0.061..0.072 rows=1 loops=100)

  • Join Filter: (workorder_facilities.facility_id = addresses_1.facility_id)
49. 1.500 3.000 ↑ 1.0 1 100

Hash Join (cost=8.31..10.94 rows=1 width=44) (actual time=0.020..0.030 rows=1 loops=100)

  • Hash Cond: (facilities.id = workorder_facilities.facility_id)
50. 1.100 1.100 ↑ 1.0 45 100

Seq Scan on facilities (cost=0.00..2.45 rows=45 width=40) (actual time=0.001..0.011 rows=45 loops=100)

51. 0.100 0.400 ↑ 1.0 1 100

Hash (cost=8.30..8.30 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=100)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
52. 0.300 0.300 ↑ 1.0 1 100

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=100)

  • Index Cond: (workorder_id = workorders.id)
53. 0.300 0.300 ↑ 1.0 1 100

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=100)

  • Index Cond: (facility_id = facilities.id)
  • Filter: default_address
54. 0.100 8.600 ↑ 1.0 1 100

Limit (cost=31.26..31.26 rows=1 width=1,043) (actual time=0.086..0.086 rows=1 loops=100)

55. 0.300 8.500 ↑ 2.0 1 100

Sort (cost=31.26..31.26 rows=2 width=1,043) (actual time=0.085..0.085 rows=1 loops=100)

  • Sort Key: notes.created_at DESC
  • Sort Method: quicksort Memory: 25kB
56. 5.854 8.200 ↑ 1.0 2 100

Nested Loop (cost=4.99..31.25 rows=2 width=1,043) (actual time=0.030..0.082 rows=2 loops=100)

57. 0.154 2.000 ↑ 1.0 2 100

Nested Loop (cost=4.71..26.37 rows=2 width=196) (actual time=0.010..0.020 rows=2 loops=100)

58. 0.554 1.500 ↑ 1.0 2 100

Nested Loop (cost=4.57..25.93 rows=2 width=192) (actual time=0.008..0.015 rows=2 loops=100)

59. 0.200 0.600 ↑ 1.0 2 100

Bitmap Heap Scan on object_notes (cost=4.30..9.33 rows=2 width=4) (actual time=0.005..0.006 rows=2 loops=100)

  • Recheck Cond: (((notable_type)::text = 'Workorders::Workorder'::text) AND (notable_id = workorders.id))
  • Heap Blocks: exact=93
60. 0.400 0.400 ↑ 1.0 2 100

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.004..0.004 rows=2 loops=100)

  • Index Cond: (((notable_type)::text = 'Workorders::Workorder'::text) AND (notable_id = workorders.id))
61. 0.346 0.346 ↑ 1.0 1 173

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

  • Index Cond: (id = object_notes.note_id)
62. 0.346 0.346 ↑ 1.0 1 173

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=173)

  • Index Cond: (id = notes.created_by)
63. 0.346 0.346 ↑ 1.0 1 173

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.002 rows=1 loops=173)

  • Index Cond: (entity_id = COALESCE(note_creator_users.person_id, note_creator_users.organization_id))
  • Filter: default_address
64. 1.000 6.300 ↑ 1.0 1 100

Nested Loop (cost=11.19..13.94 rows=1 width=78) (actual time=0.050..0.063 rows=1 loops=100)

65. 0.566 5.100 ↑ 1.0 1 100

Nested Loop (cost=11.06..13.59 rows=1 width=42) (actual time=0.039..0.051 rows=1 loops=100)

66. 2.000 4.200 ↓ 1.5 3 100

Hash Join (cost=10.92..12.87 rows=2 width=46) (actual time=0.019..0.042 rows=3 loops=100)

  • Hash Cond: (statuses_1.id = states_1.status_id)
67. 1.500 1.500 ↑ 1.0 62 100

Seq Scan on statuses statuses_1 (cost=0.00..1.62 rows=62 width=30) (actual time=0.001..0.015 rows=62 loops=100)

68. 0.200 0.700 ↓ 1.5 3 100

Hash (cost=10.90..10.90 rows=2 width=20) (actual time=0.007..0.007 rows=3 loops=100)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
69. 0.500 0.500 ↓ 1.5 3 100

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.005 rows=3 loops=100)

  • Index Cond: (object_id = workorders.id)
70. 0.334 0.334 ↓ 0.0 0 334

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

  • Index Cond: (id = statuses_1.workflow_id)
  • Filter: ((object_type)::text = 'Workorders::Workorder'::text)
  • Rows Removed by Filter: 1
71. 0.200 0.200 ↑ 1.0 1 100

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

  • Index Cond: (id = statuses_1.workflow_type_id)
72. 2.000 4.800 ↑ 1.0 1 100

Nested Loop (cost=5.13..22.88 rows=1 width=66) (actual time=0.033..0.048 rows=1 loops=100)

73. 0.300 2.500 ↓ 3.0 3 100

Nested Loop (cost=4.99..22.51 rows=1 width=70) (actual time=0.013..0.025 rows=3 loops=100)

74. 0.164 1.600 ↓ 3.0 3 100

Nested Loop (cost=4.85..22.34 rows=1 width=44) (actual time=0.011..0.016 rows=3 loops=100)

75. 0.384 1.000 ↑ 1.0 1 100

Nested Loop (cost=4.57..16.61 rows=1 width=40) (actual time=0.008..0.010 rows=1 loops=100)

76. 0.200 0.200 ↑ 1.0 1 100

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=100)

  • Index Cond: (workorder_id = workorders.id)
77. 0.208 0.416 ↑ 1.0 1 104

Bitmap Heap Scan on work_efforts visits (cost=4.28..8.29 rows=1 width=40) (actual time=0.003..0.004 rows=1 loops=104)

  • Recheck Cond: (assignment_id = assignments_2.id)
  • Heap Blocks: exact=99
78. 0.208 0.208 ↑ 1.0 1 104

Bitmap Index Scan on index_work_efforts_on_assignment_id (cost=0.00..4.28 rows=1 width=0) (actual time=0.002..0.002 rows=1 loops=104)

  • Index Cond: (assignment_id = assignments_2.id)
79. 0.436 0.436 ↓ 1.5 3 109

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=109)

  • Index Cond: (object_id = visits.id)
80. 0.600 0.600 ↑ 1.0 1 300

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

  • Index Cond: (id = states_2.status_id)
81. 0.300 0.300 ↓ 0.0 0 300

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=300)

  • Index Cond: (id = statuses_2.workflow_id)
  • Filter: ((object_type)::text = 'Workorders::Visit'::text)
  • Rows Removed by Filter: 1