explain.depesz.com

PostgreSQL's explain analyze made readable

Result: yYfA

Settings
# exclusive inclusive rows x rows loops node
1. 0.010 3,904.335 ↑ 1.0 100 1

Limit (cost=153,953.45..153,962.95 rows=100 width=2,147) (actual time=3,904.195..3,904.335 rows=100 loops=1)

2. 346.645 3,904.325 ↑ 190.6 100 1

WindowAgg (cost=153,953.45..155,764.15 rows=19,060 width=2,147) (actual time=3,904.195..3,904.325 rows=100 loops=1)

3. 190.973 3,557.680 ↓ 2.8 53,379 1

GroupAggregate (cost=153,953.45..155,382.95 rows=19,060 width=2,135) (actual time=3,199.445..3,557.680 rows=53,379 loops=1)

  • Group Key: workorders.id, categories.category, not_to_exceeds.amount, priorities.color, priorities.name, workorder_classes.name, (concat(assigned_to_address.first_name, ' ', assigned_to_address.last_name)), client_address.company, ((((creator_addresses.first_name)::text || ' '::text) || (creator_addresses.last_name)::text)), statuses.name, workflow_types.color
4. 953.869 3,366.707 ↓ 4.3 82,307 1

Sort (cost=153,953.45..154,001.10 rows=19,060 width=2,055) (actual time=3,199.424..3,366.707 rows=82,307 loops=1)

  • Sort Key: workorders.id, categories.category, not_to_exceeds.amount, priorities.color, priorities.name, workorder_classes.name, (concat(assigned_to_address.first_name, ' ', assigned_to_address.last_name)), client_address.company, ((((creator_addresses.first_name)::text || ' '::text) || (creator_addresses.last_name)::text)), statuses.name, workflow_types.color
  • Sort Method: external merge Disk: 177,536kB
5. 210.702 2,412.838 ↓ 4.3 82,307 1

Nested Loop Left Join (cost=86,700.63..135,658.45 rows=19,060 width=2,055) (actual time=956.815..2,412.838 rows=82,307 loops=1)

6. 60.288 1,956.076 ↓ 4.3 82,020 1

Hash Left Join (cost=86,700.34..127,859.79 rows=19,029 width=2,040) (actual time=956.792..1,956.076 rows=82,020 loops=1)

  • Hash Cond: (workorder_facilities.facility_id = facilities.id)
7. 63.053 1,890.804 ↓ 4.3 82,020 1

Hash Left Join (cost=86,201.35..127,099.22 rows=19,029 width=2,035) (actual time=951.661..1,890.804 rows=82,020 loops=1)

  • Hash Cond: (workorders.id = workorder_facilities.workorder_id)
8. 47.158 1,802.755 ↓ 4.3 82,020 1

Hash Join (cost=84,019.10..124,655.32 rows=19,029 width=2,031) (actual time=925.775..1,802.755 rows=82,020 loops=1)

  • Hash Cond: (statuses.workflow_type_id = workflow_types.id)
9. 55.955 1,755.578 ↓ 4.5 96,472 1

Hash Join (cost=84,017.65..124,383.83 rows=21,268 width=2,003) (actual time=925.750..1,755.578 rows=96,472 loops=1)

  • Hash Cond: (states.status_id = statuses.id)
10. 71.961 1,699.553 ↓ 4.5 96,472 1

Hash Join (cost=84,010.69..124,084.43 rows=21,268 width=1,981) (actual time=925.674..1,699.553 rows=96,472 loops=1)

  • Hash Cond: (not_to_exceeds.workorder_id = states.object_id)
11. 58.472 1,589.280 ↑ 1.7 96,472 1

Hash Left Join (cost=75,075.90..110,219.70 rows=164,079 width=1,981) (actual time=887.222..1,589.280 rows=96,472 loops=1)

  • Hash Cond: (COALESCE(creator_users.person_id, creator_users.organization_id) = creator_addresses.entity_id)
12. 57.410 1,491.282 ↓ 1.3 96,472 1

Hash Left Join (cost=69,994.07..102,664.56 rows=74,001 width=1,960) (actual time=847.209..1,491.282 rows=96,472 loops=1)

  • Hash Cond: (client_role.entity_id = client_address.entity_id)
13. 55.309 1,423.869 ↓ 1.3 96,472 1

Hash Left Join (cost=65,370.94..96,972.41 rows=74,001 width=1,942) (actual time=837.135..1,423.869 rows=96,472 loops=1)

  • Hash Cond: (workorders.placed_for = client_role.id)
14. 62.043 1,360.502 ↓ 1.3 96,472 1

Hash Left Join (cost=64,540.23..95,124.18 rows=74,001 width=1,938) (actual time=828.857..1,360.502 rows=96,472 loops=1)

  • Hash Cond: (subcontractor_roles.entity_id = subcontractor_addresses.entity_id)
15. 64.805 1,268.974 ↓ 1.3 96,472 1

Hash Left Join (cost=59,839.27..89,313.53 rows=74,001 width=1,920) (actual time=799.244..1,268.974 rows=96,472 loops=1)

  • Hash Cond: (assignments.role_id = subcontractor_roles.id)
16. 312.059 1,197.756 ↓ 1.3 96,472 1

Hash Right Join (cost=59,008.56..87,465.30 rows=74,001 width=1,920) (actual time=792.607..1,197.756 rows=96,472 loops=1)

  • Hash Cond: (assignments.workorder_id = workorders.id)
17. 71.308 159.220 ↑ 1.0 82,404 1

Hash Left Join (cost=5,738.79..18,124.51 rows=82,404 width=24) (actual time=63.062..159.220 rows=82,404 loops=1)

  • Hash Cond: (assignments.id = subcontractor_not_to_exceeds.assignment_id)
18. 25.990 25.990 ↑ 1.0 82,404 1

Seq Scan on assignments (cost=0.00..10,308.04 rows=82,404 width=16) (actual time=0.011..25.990 rows=82,404 loops=1)

19. 23.471 61.922 ↑ 1.0 82,404 1

Hash (cost=4,304.31..4,304.31 rows=82,518 width=12) (actual time=61.922..61.922 rows=82,404 loops=1)

  • Buckets: 131,072 Batches: 2 Memory Usage: 2,793kB
20. 38.451 38.451 ↑ 1.0 82,404 1

Seq Scan on not_to_exceeds subcontractor_not_to_exceeds (cost=0.00..4,304.31 rows=82,518 width=12) (actual time=10.940..38.451 rows=82,404 loops=1)

  • Filter: ((type)::text = 'Workorders::SubcontractorNotToExceed'::text)
  • Rows Removed by Filter: 66,501
21. 247.450 726.477 ↓ 1.1 66,501 1

Hash (cost=38,467.27..38,467.27 rows=59,720 width=1,900) (actual time=726.477..726.477 rows=66,501 loops=1)

  • Buckets: 4,096 Batches: 32 Memory Usage: 3,981kB
22. 35.577 479.027 ↓ 1.1 66,501 1

Hash Left Join (cost=6,452.78..38,467.27 rows=59,720 width=1,900) (actual time=37.863..479.027 rows=66,501 loops=1)

  • Hash Cond: (internal_employee_roles.entity_id = assigned_to_address.entity_id)
23. 36.893 416.186 ↓ 2.2 66,501 1

Hash Left Join (cost=1,369.95..32,454.26 rows=29,598 width=1,875) (actual time=10.538..416.186 rows=66,501 loops=1)

  • Hash Cond: (workorders.created_by = creator_users.id)
24. 33.651 375.587 ↓ 2.2 66,501 1

Hash Left Join (cost=836.50..31,513.84 rows=29,598 width=1,867) (actual time=6.811..375.587 rows=66,501 loops=1)

  • Hash Cond: (workorders.assigned_to = internal_employee_roles.id)
25. 33.197 335.276 ↓ 2.2 66,501 1

Hash Join (cost=5.79..30,411.41 rows=29,598 width=1,863) (actual time=0.113..335.276 rows=66,501 loops=1)

  • Hash Cond: (workorders.workorder_class_id = workorder_classes.id)
26. 34.995 302.068 ↓ 2.2 66,501 1

Hash Join (cost=4.70..30,003.35 rows=29,598 width=1,831) (actual time=0.097..302.068 rows=66,501 loops=1)

  • Hash Cond: (workorders.priority_id = priorities.id)
27. 33.789 267.062 ↓ 2.2 66,501 1

Hash Join (cost=3.47..29,595.15 rows=29,598 width=1,767) (actual time=0.082..267.062 rows=66,501 loops=1)

  • Hash Cond: (workorders.category_id = categories.id)
28. 47.659 233.227 ↓ 2.2 66,501 1

Merge Join (cost=1.10..29,185.81 rows=29,598 width=1,752) (actual time=0.029..233.227 rows=66,501 loops=1)

  • Merge Cond: (workorders.id = not_to_exceeds.workorder_id)
29. 117.443 117.443 ↑ 1.0 66,501 1

Index Scan using workorders_pkey on workorders (cost=0.29..25,617.57 rows=66,501 width=1,740) (actual time=0.014..117.443 rows=66,501 loops=1)

30. 68.125 68.125 ↓ 1.0 66,501 1

Index Scan using index_not_to_exceeds_on_workorder_id on not_to_exceeds (cost=0.42..6,800.37 rows=66,387 width=12) (actual time=0.012..68.125 rows=66,501 loops=1)

  • Filter: (active AND ((type)::text = 'Workorders::ClientNotToExceed'::text))
  • Rows Removed by Filter: 82,404
31. 0.010 0.046 ↑ 1.0 61 1

Hash (cost=1.61..1.61 rows=61 width=23) (actual time=0.046..0.046 rows=61 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 12kB
32. 0.036 0.036 ↑ 1.0 61 1

Seq Scan on categories (cost=0.00..1.61 rows=61 width=23) (actual time=0.028..0.036 rows=61 loops=1)

33. 0.004 0.011 ↑ 1.0 10 1

Hash (cost=1.10..1.10 rows=10 width=72) (actual time=0.011..0.011 rows=10 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
34. 0.007 0.007 ↑ 1.0 10 1

Seq Scan on priorities (cost=0.00..1.10 rows=10 width=72) (actual time=0.005..0.007 rows=10 loops=1)

35. 0.002 0.011 ↑ 1.0 4 1

Hash (cost=1.04..1.04 rows=4 width=40) (actual time=0.011..0.011 rows=4 loops=1)

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

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

37. 3.360 6.660 ↑ 1.0 18,565 1

Hash (cost=598.65..598.65 rows=18,565 width=12) (actual time=6.660..6.660 rows=18,565 loops=1)

  • Buckets: 32,768 Batches: 1 Memory Usage: 1,054kB
38. 3.300 3.300 ↑ 1.0 18,565 1

Seq Scan on roles internal_employee_roles (cost=0.00..598.65 rows=18,565 width=12) (actual time=0.016..3.300 rows=18,565 loops=1)

39. 1.374 3.706 ↑ 1.0 7,131 1

Hash (cost=444.31..444.31 rows=7,131 width=16) (actual time=3.706..3.706 rows=7,131 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 399kB
40. 2.332 2.332 ↑ 1.0 7,131 1

Seq Scan on users creator_users (cost=0.00..444.31 rows=7,131 width=16) (actual time=0.006..2.332 rows=7,131 loops=1)

41. 11.598 27.264 ↑ 1.3 37,420 1

Hash (cost=4,461.26..4,461.26 rows=49,726 width=33) (actual time=27.264..27.264 rows=37,420 loops=1)

  • Buckets: 65,536 Batches: 1 Memory Usage: 2,102kB
42. 15.666 15.666 ↑ 1.0 49,726 1

Seq Scan on addresses assigned_to_address (cost=0.00..4,461.26 rows=49,726 width=33) (actual time=0.036..15.666 rows=49,726 loops=1)

43. 3.223 6.413 ↑ 1.0 18,565 1

Hash (cost=598.65..598.65 rows=18,565 width=12) (actual time=6.413..6.413 rows=18,565 loops=1)

  • Buckets: 32,768 Batches: 1 Memory Usage: 1,054kB
44. 3.190 3.190 ↑ 1.0 18,565 1

Seq Scan on roles subcontractor_roles (cost=0.00..598.65 rows=18,565 width=12) (actual time=0.007..3.190 rows=18,565 loops=1)

45. 2.933 29.485 ↓ 1.0 9,359 1

Hash (cost=4,585.57..4,585.57 rows=9,231 width=26) (actual time=29.485..29.485 rows=9,359 loops=1)

  • Buckets: 16,384 Batches: 1 Memory Usage: 698kB
46. 26.552 26.552 ↓ 1.0 9,359 1

Seq Scan on addresses subcontractor_addresses (cost=0.00..4,585.57 rows=9,231 width=26) (actual time=1.037..26.552 rows=9,359 loops=1)

  • Filter: (default_address AND ((type)::text = 'Addresses::DispatchAddress'::text))
  • Rows Removed by Filter: 40,367
47. 3.998 8.058 ↑ 1.0 18,565 1

Hash (cost=598.65..598.65 rows=18,565 width=12) (actual time=8.058..8.058 rows=18,565 loops=1)

  • Buckets: 32,768 Batches: 1 Memory Usage: 1,054kB
48. 4.060 4.060 ↑ 1.0 18,565 1

Seq Scan on roles client_role (cost=0.00..598.65 rows=18,565 width=12) (actual time=0.008..4.060 rows=18,565 loops=1)

49. 2.527 10.003 ↑ 1.0 7,845 1

Hash (cost=4,524.66..4,524.66 rows=7,877 width=26) (actual time=10.003..10.003 rows=7,845 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 519kB
50. 6.254 7.476 ↑ 1.0 7,845 1

Bitmap Heap Scan on addresses client_address (cost=457.56..4,524.66 rows=7,877 width=26) (actual time=1.492..7.476 rows=7,845 loops=1)

  • Recheck Cond: ((type)::text = 'Addresses::GeneralAddress'::text)
  • Filter: default_address
  • Heap Blocks: exact=1,422
51. 1.222 1.222 ↑ 1.0 7,845 1

Bitmap Index Scan on index_addresses_on_type (cost=0.00..455.59 rows=7,890 width=0) (actual time=1.222..1.222 rows=7,845 loops=1)

  • Index Cond: ((type)::text = 'Addresses::GeneralAddress'::text)
52. 12.078 39.526 ↑ 1.3 37,420 1

Hash (cost=4,461.26..4,461.26 rows=49,646 width=33) (actual time=39.526..39.526 rows=37,420 loops=1)

  • Buckets: 65,536 Batches: 1 Memory Usage: 2,102kB
53. 27.448 27.448 ↑ 1.0 49,642 1

Seq Scan on addresses creator_addresses (cost=0.00..4,461.26 rows=49,646 width=33) (actual time=0.016..27.448 rows=49,642 loops=1)

  • Filter: default_address
  • Rows Removed by Filter: 84
54. 16.326 38.312 ↑ 1.0 66,501 1

Hash (cost=8,101.82..8,101.82 rows=66,637 width=8) (actual time=38.312..38.312 rows=66,501 loops=1)

  • Buckets: 131,072 Batches: 1 Memory Usage: 3,622kB
55. 15.056 21.986 ↑ 1.0 66,501 1

Bitmap Heap Scan on states (cost=1,832.86..8,101.82 rows=66,637 width=8) (actual time=7.123..21.986 rows=66,501 loops=1)

  • Recheck Cond: ((object_type)::text = 'Workorders::Workorder'::text)
  • Heap Blocks: exact=1,488
56. 6.930 6.930 ↑ 1.0 66,501 1

Bitmap Index Scan on index_states_on_object_type (cost=0.00..1,816.20 rows=66,637 width=0) (actual time=6.930..6.930 rows=66,501 loops=1)

  • Index Cond: ((object_type)::text = 'Workorders::Workorder'::text)
57. 0.032 0.070 ↑ 1.0 176 1

Hash (cost=4.76..4.76 rows=176 width=34) (actual time=0.070..0.070 rows=176 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 21kB
58. 0.038 0.038 ↑ 1.0 176 1

Seq Scan on statuses (cost=0.00..4.76 rows=176 width=34) (actual time=0.006..0.038 rows=176 loops=1)

59. 0.006 0.019 ↑ 1.0 17 1

Hash (cost=1.24..1.24 rows=17 width=40) (actual time=0.019..0.019 rows=17 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
60. 0.013 0.013 ↑ 1.0 17 1

Seq Scan on workflow_types (cost=0.00..1.24 rows=17 width=40) (actual time=0.009..0.013 rows=17 loops=1)

  • Filter: (id <> ALL ('{7,12}'::bigint[]))
  • Rows Removed by Filter: 2
61. 13.463 24.996 ↑ 1.0 66,500 1

Hash (cost=1,351.00..1,351.00 rows=66,500 width=8) (actual time=24.996..24.996 rows=66,500 loops=1)

  • Buckets: 131,072 Batches: 1 Memory Usage: 3,622kB
62. 11.533 11.533 ↑ 1.0 66,500 1

Seq Scan on workorder_facilities (cost=0.00..1,351.00 rows=66,500 width=8) (actual time=0.013..11.533 rows=66,500 loops=1)

63. 2.501 4.984 ↑ 1.0 12,222 1

Hash (cost=346.22..346.22 rows=12,222 width=17) (actual time=4.984..4.984 rows=12,222 loops=1)

  • Buckets: 16,384 Batches: 1 Memory Usage: 784kB
64. 2.483 2.483 ↑ 1.0 12,222 1

Seq Scan on facilities (cost=0.00..346.22 rows=12,222 width=17) (actual time=0.012..2.483 rows=12,222 loops=1)

65. 246.060 246.060 ↑ 1.0 1 82,020

Index Scan using index_purchase_order_numbers_on_workorder_id on purchase_order_numbers client_purchase_order_numbers (cost=0.29..0.39 rows=1 width=13) (actual time=0.003..0.003 rows=1 loops=82,020)

  • Index Cond: (workorder_id = workorders.id)
  • Filter: ((type)::text = 'Workorders::ClientPurchaseOrderNumber'::text)
Planning time : 38.152 ms
Execution time : 4,059.519 ms