explain.depesz.com

PostgreSQL's explain analyze made readable

Result: quSr

Settings
# exclusive inclusive rows x rows loops node
1. 0.035 6,568.458 ↑ 1.0 100 1

Limit (cost=1,787,395.34..1,787,395.59 rows=100 width=783) (actual time=6,568.391..6,568.458 rows=100 loops=1)

2. 16.045 6,568.423 ↑ 132.5 100 1

Sort (cost=1,787,395.34..1,787,428.45 rows=13,246 width=783) (actual time=6,568.389..6,568.423 rows=100 loops=1)

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

Merge Join (cost=1,785,506.17..1,786,889.09 rows=13,246 width=783) (actual time=6,495.297..6,552.378 rows=13,413 loops=1)

  • Merge Cond: (workorders_1.id = workorders.id)
4. 9.084 6,530.231 ↓ 1.0 13,413 1

Unique (cost=1,785,505.88..1,785,572.11 rows=13,246 width=1,391) (actual time=6,495.268..6,530.231 rows=13,413 loops=1)

5. 212.223 6,521.147 ↓ 1.0 13,413 1

Sort (cost=1,785,505.88..1,785,539.00 rows=13,246 width=1,391) (actual time=6,495.261..6,521.147 rows=13,413 loops=1)

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

Hash Left Join (cost=226.95..1,784,598.98 rows=13,246 width=1,391) (actual time=1.905..6,308.924 rows=13,413 loops=1)

  • Hash Cond: (workorders_1.placed_for = client_role.id)
7. 10.720 145.296 ↓ 1.3 13,413 1

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

  • Hash Cond: (internal_employee_roles.entity_id = assigned_to_address.entity_id)
8. 14.446 134.400 ↓ 1.6 13,413 1

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

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

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

  • Hash Cond: (workorders_1.assigned_to = internal_employee_roles.id)
10. 12.941 109.005 ↓ 2.0 13,413 1

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

  • Hash Cond: (workorders_1.created_by = creator_users.id)
11. 13.007 95.967 ↓ 2.0 13,413 1

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

  • Hash Cond: (workorders_1.category_id = categories.id)
12. 16.857 82.829 ↓ 2.0 13,413 1

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

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

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

  • Merge Cond: (workorders_1.id = not_to_exceeds.workorder_id)
14. 8.817 8.817 ↑ 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.007..8.817 rows=13,413 loops=1)

15. 28.082 28.082 ↑ 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.009..28.082 rows=13,461 loops=1)

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

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
17. 0.008 0.022 ↑ 1.0 12 1

Nested Loop (cost=0.00..2.23 rows=12 width=112) (actual time=0.007..0.022 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.007 0.012 ↑ 1.0 3 4

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

20. 0.005 0.005 ↑ 1.0 3 1

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

21. 0.079 0.131 ↑ 1.0 207 1

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

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

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

23. 0.036 0.097 ↑ 1.0 120 1

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

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

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

25. 0.052 0.100 ↑ 1.0 164 1

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

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

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

27. 0.105 0.313 ↑ 1.2 210 1

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

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

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

  • Filter: default_address
  • Rows Removed by Filter: 8
29. 0.083 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.093 0.093 ↑ 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.093 rows=263 loops=1)

31. 0.070 0.379 ↑ 1.0 208 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 18kB
32. 0.154 0.309 ↑ 1.0 208 1

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

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

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

34. 0.048 0.094 ↑ 1.0 164 1

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

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

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

36.          

SubPlan (forHash Left Join)

37. 26.826 120.717 ↑ 1.0 1 13,413

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

38. 40.263 93.891 ↑ 1.0 1 13,413

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

39. 26.826 26.826 ↑ 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.002..0.002 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,286.185 ↑ 1.0 1 13,413

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

42. 67.065 3,259.359 ↓ 2.0 2 13,413

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

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

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

44. 68.461 1,636.386 ↓ 2.5 5 13,413

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

45. 58.326 1,435.191 ↓ 2.5 5 13,413

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

46. 53.717 1,247.409 ↓ 2.0 2 13,413

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

47. 564.676 1,126.692 ↑ 1.0 1 13,413

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

  • Hash Cond: (assignment_roles.id = assignments_1.role_id)
48. 508.364 508.364 ↑ 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.038 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. 129.456 129.456 ↑ 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.004 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. 65.159 65.159 ↓ 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.001 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. 26.826 1,032.801 ↑ 1.0 1 13,413

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

57. 40.239 1,005.975 ↑ 1.0 1 13,413

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

  • Sort Key: addresses_1.facility_id
  • Sort Method: quicksort Memory: 27kB
58. 509.694 965.736 ↑ 1.0 1 13,413

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

  • Join Filter: (workorder_facilities.facility_id = addresses_1.facility_id)
59. 201.195 415.803 ↑ 1.0 1 13,413

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

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

Seq Scan on facilities (cost=0.00..2.45 rows=45 width=40) (actual time=0.001..0.012 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. 12.167 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.246 1.246 ↑ 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.002 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. 147.543 777.954 ↑ 1.0 1 13,413

Nested Loop (cost=11.19..13.94 rows=1 width=78) (actual time=0.032..0.058 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.020..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.010..0.039 rows=2 loops=13,413)

  • Hash Cond: (statuses_1.id = states_1.status_id)
77. 201.195 201.195 ↑ 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.015 rows=62 loops=13,413)

78. 13.413 67.065 ↑ 1.0 2 13,413

Hash (cost=10.90..10.90 rows=2 width=20) (actual time=0.005..0.005 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.002..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.001..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. 25.215 120.717 ↓ 0.0 0 13,413

Nested Loop (cost=1.12..22.03 rows=1 width=66) (actual time=0.008..0.009 rows=0 loops=13,413)

83. 11.802 93.891 ↓ 0.0 0 13,413

Nested Loop (cost=0.99..21.66 rows=1 width=70) (actual time=0.007..0.007 rows=0 loops=13,413)

84. 11.557 80.478 ↓ 0.0 0 13,413

Nested Loop (cost=0.85..21.50 rows=1 width=44) (actual time=0.006..0.006 rows=0 loops=13,413)

85. 26.838 67.065 ↓ 0.0 0 13,413

Nested Loop (cost=0.56..16.60 rows=1 width=40) (actual time=0.005..0.005 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. 13.401 13.401 ↓ 0.0 0 13,401

Index Scan using index_work_efforts_on_assignment_id on work_efforts visits (cost=0.27..8.29 rows=1 width=40) (actual time=0.001..0.001 rows=0 loops=13,401)

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

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

  • Index Cond: (object_id = visits.id)
89. 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)
90. 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
91. 6.171 6.171 ↑ 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.020..6.171 rows=13,413 loops=1)

  • Heap Fetches: 13413