explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ILPG

Settings
# exclusive inclusive rows x rows loops node
1. 0.042 6,711.149 ↑ 1.0 100 1

Limit (cost=1,798,692.86..1,798,693.11 rows=100 width=684) (actual time=6,711.074..6,711.149 rows=100 loops=1)

2.          

CTE query_object

3. 10.474 6,536.149 ↓ 1.0 13,413 1

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

4. 215.059 6,525.675 ↓ 1.0 13,413 1

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

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

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

  • Hash Cond: (workorders_1.placed_for = client_role.id)
6. 10.826 140.090 ↓ 1.3 13,413 1

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

  • Hash Cond: (internal_employee_roles.entity_id = assigned_to_address.entity_id)
7. 15.138 129.083 ↓ 1.6 13,413 1

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

  • Hash Cond: (COALESCE(creator_users.person_id, creator_users.organization_id) = creator_addresses.entity_id)
8. 10.743 113.599 ↓ 2.0 13,413 1

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

  • Hash Cond: (workorders_1.assigned_to = internal_employee_roles.id)
9. 12.542 102.755 ↓ 2.0 13,413 1

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

  • Hash Cond: (workorders_1.created_by = creator_users.id)
10. 13.025 90.112 ↓ 2.0 13,413 1

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

  • Hash Cond: (workorders_1.category_id = categories.id)
11. 17.673 76.947 ↓ 2.0 13,413 1

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

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

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

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

14. 20.365 20.365 ↑ 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..20.365 rows=13,461 loops=1)

  • Filter: (active AND ((type)::text = 'Workorders::ClientNotToExceed'::text))
  • Rows Removed by Filter: 13446
15. 0.015 0.029 ↑ 1.0 12 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
16. 0.002 0.014 ↑ 1.0 12 1

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

17. 0.004 0.004 ↑ 1.0 4 1

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

18. 0.004 0.008 ↑ 1.0 3 4

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

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

20. 0.075 0.140 ↑ 1.0 207 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 20kB
21. 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)

22. 0.037 0.101 ↑ 1.0 120 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
23. 0.064 0.064 ↑ 1.0 120 1

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

24. 0.050 0.101 ↑ 1.0 164 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 16kB
25. 0.051 0.051 ↑ 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.051 rows=164 loops=1)

26. 0.132 0.346 ↑ 1.2 210 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 106kB
27. 0.214 0.214 ↑ 1.0 255 1

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

  • Filter: default_address
  • Rows Removed by Filter: 8
28. 0.083 0.181 ↑ 1.2 218 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 19kB
29. 0.098 0.098 ↑ 1.0 263 1

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

30. 0.063 0.382 ↑ 1.0 208 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 18kB
31. 0.155 0.319 ↑ 1.0 208 1

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

  • Hash Cond: (client_address.entity_id = client_role.entity_id)
32. 0.067 0.067 ↑ 1.0 263 1

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

33. 0.051 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
34. 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.002..0.046 rows=164 loops=1)

35.          

SubPlan (forHash Left Join)

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

37. 26.850 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)

38. 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.002..0.003 rows=1 loops=13,413)

  • Index Cond: (workorder_id = workorders_1.id)
39. 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)
40. 26.826 3,380.076 ↑ 1.0 1 13,413

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

41. 67.065 3,353.250 ↓ 2.0 2 13,413

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

  • Sort Key: addresses.entity_id
  • Sort Method: quicksort Memory: 29kB
42. 1,506.068 3,286.185 ↓ 2.0 2 13,413

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

43. 68.461 1,649.799 ↓ 2.5 5 13,413

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

44. 63.864 1,448.604 ↓ 2.5 5 13,413

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

45. 53.717 1,287.648 ↓ 2.0 2 13,413

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

46. 618.293 1,166.931 ↑ 1.0 1 13,413

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

  • Hash Cond: (assignment_roles.id = assignments_1.role_id)
47. 494.986 494.986 ↑ 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.037 rows=164 loops=13,378)

48. 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
49. 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)
50. 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
51. 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)
52. 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
53. 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
54. 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))
55. 26.826 1,059.627 ↑ 1.0 1 13,413

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

56. 40.239 1,032.801 ↑ 1.0 1 13,413

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

  • Sort Key: addresses_1.facility_id
  • Sort Method: quicksort Memory: 27kB
57. 523.107 992.562 ↑ 1.0 1 13,413

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

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

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

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

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

60. 13.413 80.478 ↑ 1.0 1 13,413

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
61. 67.065 67.065 ↑ 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.004..0.005 rows=1 loops=13,413)

  • Index Cond: (workorder_id = workorders_1.id)
62. 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
63. 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)

64. 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
65. 25.580 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)

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

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

68. 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
69. 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))
70. 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)
71. 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)
72. 1.246 1.246 ↑ 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.002 rows=1 loops=623)

  • Index Cond: (entity_id = COALESCE(note_creator_users.person_id, note_creator_users.organization_id))
  • Filter: default_address
73. 147.543 777.954 ↑ 1.0 1 13,413

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

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

75. 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)
76. 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)

77. 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
78. 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)
79. 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
80. 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)
81. 25.215 134.130 ↓ 0.0 0 13,413

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

82. 0.000 107.304 ↓ 0.0 0 13,413

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

83. 11.557 107.304 ↓ 0.0 0 13,413

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

84. 40.263 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)

85. 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)
86. 13.401 26.802 ↓ 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.002 rows=0 loops=13,401)

  • Recheck Cond: (assignment_id = assignments_2.id)
  • Heap Blocks: exact=435
87. 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)
88. 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)
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. 19.350 6,711.107 ↑ 132.5 100 1

Sort (cost=1,879.10..1,912.21 rows=13,246 width=684) (actual time=6,711.072..6,711.107 rows=100 loops=1)

  • Sort Key: query_object.created_at DESC
  • Sort Method: top-N heapsort Memory: 802kB
92. 17.323 6,691.757 ↓ 1.0 13,413 1

Hash Join (cost=925.79..1,372.85 rows=13,246 width=684) (actual time=6,504.480..6,691.757 rows=13,413 loops=1)

  • Hash Cond: (query_object.id = workorders.id)
93. 6,666.184 6,666.184 ↓ 1.0 13,413 1

CTE Scan on query_object (cost=0.00..264.92 rows=13,246 width=684) (actual time=6,496.150..6,666.184 rows=13,413 loops=1)

94. 4.055 8.250 ↑ 1.0 13,413 1

Hash (cost=758.13..758.13 rows=13,413 width=8) (actual time=8.250..8.250 rows=13,413 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 652kB
95. 4.195 4.195 ↑ 1.0 13,413 1

Seq Scan on workorders (cost=0.00..758.13 rows=13,413 width=8) (actual time=0.009..4.195 rows=13,413 loops=1)