explain.depesz.com

PostgreSQL's explain analyze made readable

Result: EnEF

Settings
# exclusive inclusive rows x rows loops node
1. 0.069 18,082.992 ↓ 100.0 100 1

Limit (cost=88,142.75..101,934.71 rows=1 width=0) (actual time=1,201.854..18,082.992 rows=100 loops=1)

2. 0.680 18,082.923 ↓ 100.0 100 1

Nested Loop (cost=88,142.75..101,934.71 rows=1 width=0) (actual time=1,201.852..18,082.923 rows=100 loops=1)

  • Join Filter: (invoice_status.workflow_type_id = invoice_workflow_type.id)
  • Rows Removed by Join Filter: 1,783
3. 0.562 18,081.843 ↓ 100.0 100 1

Nested Loop (cost=88,142.75..101,933.29 rows=1 width=4) (actual time=1,201.848..18,081.843 rows=100 loops=1)

  • Join Filter: (workorder_status.workflow_type_id = workorder_workflow_type.id)
  • Rows Removed by Join Filter: 1,784
4. 3.285 18,080.781 ↓ 100.0 100 1

Nested Loop (cost=88,142.75..101,931.86 rows=1 width=8) (actual time=1,201.843..18,080.781 rows=100 loops=1)

  • Join Filter: (workorder_state.status_id = workorder_status.id)
  • Rows Removed by Join Filter: 13,385
5. 0.221 18,074.396 ↓ 100.0 100 1

Nested Loop (cost=88,142.75..101,925.80 rows=1 width=8) (actual time=1,201.828..18,074.396 rows=100 loops=1)

6. 0.696 18,073.675 ↓ 100.0 100 1

Nested Loop (cost=88,142.33..101,925.08 rows=1 width=16) (actual time=1,201.819..18,073.675 rows=100 loops=1)

  • Join Filter: (assignment_status.workflow_type_id = assignment_workflow_type.id)
  • Rows Removed by Join Filter: 1,796
7. 3.565 18,072.479 ↓ 100.0 100 1

Nested Loop (cost=88,142.33..101,923.65 rows=1 width=20) (actual time=1,201.807..18,072.479 rows=100 loops=1)

  • Join Filter: (assignment_state.status_id = assignment_status.id)
  • Rows Removed by Join Filter: 13,484
8. 0.069 18,065.714 ↓ 100.0 100 1

Nested Loop (cost=88,142.33..101,917.59 rows=1 width=20) (actual time=1,201.736..18,065.714 rows=100 loops=1)

9. 0.017 0.017 ↑ 1.0 1 1

Index Scan using index_statuses_on_workflow_type_id on statuses invoice_status (cost=0.14..22.52 rows=1 width=12) (actual time=0.017..0.017 rows=1 loops=1)

  • Filter: (id = 187)
  • Rows Removed by Filter: 19
10. 82.080 18,065.628 ↓ 100.0 100 1

Nested Loop (cost=88,142.18..101,895.06 rows=1 width=20) (actual time=1,201.717..18,065.628 rows=100 loops=1)

11. 305.873 17,624.168 ↓ 175.1 89,845 1

Nested Loop (cost=88,141.76..101,510.78 rows=513 width=28) (actual time=872.206..17,624.168 rows=89,845 loops=1)

  • Join Filter: (assignments.workorder_class_id = workorder_classes.id)
  • Rows Removed by Join Filter: 808,598
12. 84.821 17,138.605 ↓ 175.1 89,845 1

Nested Loop (cost=88,141.76..101,432.71 rows=513 width=32) (actual time=872.196..17,138.605 rows=89,845 loops=1)

13. 136.587 16,694.404 ↓ 175.1 89,845 1

Nested Loop (cost=88,141.34..100,178.57 rows=513 width=24) (actual time=872.183..16,694.404 rows=89,845 loops=1)

14. 2,019.522 16,198.437 ↓ 127.1 89,845 1

Nested Loop (cost=88,140.92..99,668.68 rows=707 width=32) (actual time=872.157..16,198.437 rows=89,845 loops=1)

  • Join Filter: (facilities.id = assignments.facility_id)
  • Rows Removed by Join Filter: 8,153,227
15. 0.118 2.104 ↓ 53.0 53 1

Nested Loop (cost=16.62..22.99 rows=1 width=12) (actual time=0.138..2.104 rows=53 loops=1)

16. 0.136 1.138 ↓ 26.5 53 1

Nested Loop (cost=16.34..21.83 rows=2 width=16) (actual time=0.126..1.138 rows=53 loops=1)

17. 0.309 0.631 ↓ 26.5 53 1

Merge Join (cost=16.06..19.68 rows=2 width=16) (actual time=0.119..0.631 rows=53 loops=1)

  • Merge Cond: (facilities.id = store_address.facility_id)
18. 0.117 0.169 ↑ 2.1 53 1

Sort (cost=15.78..16.05 rows=109 width=12) (actual time=0.097..0.169 rows=53 loops=1)

  • Sort Key: facilities.id
  • Sort Method: quicksort Memory: 30kB
19. 0.052 0.052 ↑ 1.0 109 1

Seq Scan on facilities (cost=0.00..12.09 rows=109 width=12) (actual time=0.004..0.052 rows=109 loops=1)

20. 0.153 0.153 ↑ 2.1 53 1

Index Only Scan using idx_addresses_on_type_and_facility_id on addresses store_address (cost=0.28..166.27 rows=109 width=4) (actual time=0.018..0.153 rows=53 loops=1)

  • Index Cond: (type = 'Addresses::StoreAddress'::text)
  • Heap Fetches: 19
21. 0.371 0.371 ↑ 1.0 1 53

Index Only Scan using index_roles_on_id_and_entity_id on roles client_role (cost=0.28..1.07 rows=1 width=12) (actual time=0.006..0.007 rows=1 loops=53)

  • Index Cond: (id = facilities.occupied_by)
  • Heap Fetches: 0
22. 0.848 0.848 ↑ 1.0 1 53

Index Only Scan using idx_addresses_on_type_and_default_address_and_entity_id on addresses client_address (cost=0.28..0.57 rows=1 width=4) (actual time=0.014..0.016 rows=1 loops=53)

  • Index Cond: ((type = 'Addresses::GeneralAddress'::text) AND (default_address = true) AND (entity_id = client_role.entity_id))
  • Filter: default_address
  • Heap Fetches: 0
23. 8,863.645 14,176.811 ↓ 3.2 155,530 53

Hash Right Join (cost=88,124.29..99,042.08 rows=48,289 width=36) (actual time=16.458..267.487 rows=155,530 loops=53)

  • Hash Cond: (not_to_exceeds.assignment_id = assignments.id)
24. 4,441.453 4,441.453 ↑ 1.0 345,979 53

Seq Scan on not_to_exceeds (cost=0.00..9,138.44 rows=346,044 width=4) (actual time=0.003..83.801 rows=345,979 loops=53)

25. 63.614 871.713 ↓ 3.2 155,556 1

Hash (cost=87,520.68..87,520.68 rows=48,289 width=36) (actual time=871.713..871.713 rows=155,556 loops=1)

  • Buckets: 262,144 (originally 65536) Batches: 1 (originally 1) Memory Usage: 12,378kB
26. 119.765 808.099 ↓ 3.2 155,556 1

Hash Join (cost=76,618.21..87,520.68 rows=48,289 width=36) (actual time=503.527..808.099 rows=155,556 loops=1)

  • Hash Cond: (object_invoices.invoiceable_id = assignments.id)
27. 115.426 425.590 ↓ 3.0 155,556 1

Hash Join (cost=23,343.62..33,566.27 rows=52,511 width=16) (actual time=239.850..425.590 rows=155,556 loops=1)

  • Hash Cond: (object_invoices.invoice_id = invoices.id)
28. 70.891 70.891 ↓ 1.0 158,991 1

Seq Scan on object_invoices (cost=0.00..9,103.58 rows=158,393 width=8) (actual time=0.381..70.891 rows=158,991 loops=1)

  • Filter: ((invoiceable_type)::text = 'Workorders::Assignment'::text)
  • Rows Removed by Filter: 165,707
29. 50.940 239.273 ↓ 2.9 155,556 1

Hash (cost=22,666.25..22,666.25 rows=54,189 width=8) (actual time=239.273..239.273 rows=155,556 loops=1)

  • Buckets: 262,144 (originally 65536) Batches: 1 (originally 1) Memory Usage: 8,125kB
30. 81.332 188.333 ↓ 2.9 155,556 1

Hash Join (cost=591.59..22,666.25 rows=54,189 width=8) (actual time=5.354..188.333 rows=155,556 loops=1)

  • Hash Cond: (invoices.payable_to = vendor_role.id)
31. 101.666 101.666 ↑ 1.0 155,556 1

Seq Scan on invoices (cost=0.00..20,949.17 rows=155,695 width=12) (actual time=0.006..101.666 rows=155,556 loops=1)

  • Filter: ((type)::text = 'Invoices::SubcontractorInvoice'::text)
  • Rows Removed by Filter: 7,869
32. 0.290 5.335 ↓ 1.0 994 1

Hash (cost=579.62..579.62 rows=958 width=8) (actual time=5.335..5.335 rows=994 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 47kB
33. 0.971 5.045 ↓ 1.0 994 1

Hash Join (cost=459.20..579.62 rows=958 width=8) (actual time=3.269..5.045 rows=994 loops=1)

  • Hash Cond: (vendor_role.entity_id = vendor_entity.id)
34. 0.820 0.820 ↑ 1.0 2,679 1

Seq Scan on roles vendor_role (cost=0.00..100.79 rows=2,679 width=12) (actual time=0.002..0.820 rows=2,679 loops=1)

35. 0.279 3.254 ↓ 1.0 994 1

Hash (cost=447.21..447.21 rows=959 width=12) (actual time=3.254..3.254 rows=994 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 51kB
36. 1.001 2.975 ↓ 1.0 994 1

Hash Join (cost=354.70..447.21 rows=959 width=12) (actual time=1.329..2.975 rows=994 loops=1)

  • Hash Cond: (vendor_entity.id = vendor_address.entity_id)
37. 0.665 0.665 ↑ 1.0 2,681 1

Seq Scan on entities vendor_entity (cost=0.00..62.81 rows=2,681 width=12) (actual time=0.003..0.665 rows=2,681 loops=1)

38. 0.285 1.309 ↓ 1.0 994 1

Hash (cost=342.33..342.33 rows=990 width=4) (actual time=1.309..1.309 rows=994 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 43kB
39. 0.912 1.024 ↓ 1.0 994 1

Bitmap Heap Scan on addresses vendor_address (cost=35.95..342.33 rows=990 width=4) (actual time=0.139..1.024 rows=994 loops=1)

  • Recheck Cond: ((type)::text = 'Addresses::BillingAddress'::text)
  • Filter: default_address
  • Heap Blocks: exact=246
40. 0.112 0.112 ↓ 1.0 994 1

Bitmap Index Scan on index_addresses_on_type (cost=0.00..35.71 rows=990 width=0) (actual time=0.112..0.112 rows=994 loops=1)

  • Index Cond: ((type)::text = 'Addresses::BillingAddress'::text)
41. 65.306 262.744 ↓ 1.0 172,964 1

Hash (cost=51,112.60..51,112.60 rows=172,960 width=20) (actual time=262.744..262.744 rows=172,964 loops=1)

  • Buckets: 262,144 Batches: 1 Memory Usage: 10,832kB
42. 197.438 197.438 ↓ 1.0 172,964 1

Seq Scan on assignments (cost=0.00..51,112.60 rows=172,960 width=20) (actual time=0.005..197.438 rows=172,964 loops=1)

43. 359.380 359.380 ↑ 1.0 1 89,845

Index Scan using index_states_on_object_id on states assignment_state (cost=0.42..0.71 rows=1 width=8) (actual time=0.003..0.004 rows=1 loops=89,845)

  • Index Cond: (object_id = assignments.id)
  • Filter: ((object_type)::text = 'Workorders::Assignment'::text)
  • Rows Removed by Filter: 1
44. 359.380 359.380 ↑ 1.0 1 89,845

Index Scan using workorders_pkey on workorders (cost=0.42..2.43 rows=1 width=16) (actual time=0.004..0.004 rows=1 loops=89,845)

  • Index Cond: (id = assignments.workorder_id)
45. 179.687 179.690 ↑ 1.0 10 89,845

Materialize (cost=0.00..1.15 rows=10 width=8) (actual time=0.000..0.002 rows=10 loops=89,845)

46. 0.003 0.003 ↑ 1.0 10 1

Seq Scan on workorder_classes (cost=0.00..1.10 rows=10 width=8) (actual time=0.003..0.003 rows=10 loops=1)

47. 359.380 359.380 ↓ 0.0 0 89,845

Index Scan using index_states_on_object_id on states invoice_state (cost=0.42..0.74 rows=1 width=8) (actual time=0.004..0.004 rows=0 loops=89,845)

  • Index Cond: (object_id = object_invoices.invoice_id)
  • Filter: (((object_type)::text = 'Invoices::Invoice'::text) AND (status_id = 187))
  • Rows Removed by Filter: 2
48. 3.200 3.200 ↑ 1.0 136 100

Seq Scan on statuses assignment_status (cost=0.00..4.36 rows=136 width=12) (actual time=0.002..0.032 rows=136 loops=100)

49. 0.500 0.500 ↑ 1.0 19 100

Seq Scan on workflow_types assignment_workflow_type (cost=0.00..1.19 rows=19 width=8) (actual time=0.001..0.005 rows=19 loops=100)

50. 0.500 0.500 ↑ 1.0 1 100

Index Scan using index_states_on_object_id on states workorder_state (cost=0.42..0.71 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=100)

  • Index Cond: (object_id = assignments.workorder_id)
  • Filter: ((object_type)::text = 'Workorders::Workorder'::text)
  • Rows Removed by Filter: 0
51. 3.100 3.100 ↑ 1.0 135 100

Seq Scan on statuses workorder_status (cost=0.00..4.36 rows=136 width=12) (actual time=0.001..0.031 rows=135 loops=100)

52. 0.500 0.500 ↑ 1.0 19 100

Seq Scan on workflow_types workorder_workflow_type (cost=0.00..1.19 rows=19 width=8) (actual time=0.001..0.005 rows=19 loops=100)

53. 0.400 0.400 ↑ 1.0 19 100

Seq Scan on workflow_types invoice_workflow_type (cost=0.00..1.19 rows=19 width=8) (actual time=0.001..0.004 rows=19 loops=100)

Planning time : 38.436 ms
Execution time : 18,083.423 ms