explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 2OOA

Settings
# exclusive inclusive rows x rows loops node
1. 844.446 21,384.196 ↓ 440.2 1,761 1

Result (cost=10,199.77..10,205.92 rows=4 width=676) (actual time=20,537.219..21,384.196 rows=1,761 loops=1)

2. 30.220 20,539.750 ↓ 440.2 1,761 1

Sort (cost=10,199.77..10,199.78 rows=4 width=1,049) (actual time=20,536.121..20,539.750 rows=1,761 loops=1)

  • Sort Key: w.number, sp.id DESC
  • Sort Method: quicksort Memory: 1,628kB
3. 22.102 20,509.530 ↓ 440.2 1,761 1

Nested Loop Left Join (cost=6.59..10,199.73 rows=4 width=1,049) (actual time=7,286.643..20,509.530 rows=1,761 loops=1)

  • Join Filter: (notas_requisicao.functional_structure_id = sp.functional_structure_id)
4. 17.695 20,471.579 ↓ 440.2 1,761 1

Nested Loop Left Join (cost=6.31..10,196.14 rows=4 width=910) (actual time=7,286.598..20,471.579 rows=1,761 loops=1)

  • Join Filter: (notas_amostra.functional_structure_id = sp.functional_structure_id)
5. 5,438.966 20,415.142 ↓ 440.2 1,761 1

Nested Loop Left Join (cost=6.03..10,194.86 rows=4 width=701) (actual time=7,286.540..20,415.142 rows=1,761 loops=1)

  • Join Filter: (acond.commentable_id = sr.id)
  • Rows Removed by Join Filter: 3,254,720
6. 12.588 9,927.389 ↓ 440.2 1,761 1

Nested Loop Left Join (cost=6.03..9,528.03 rows=4 width=607) (actual time=7,269.924..9,927.389 rows=1,761 loops=1)

7. 14.367 9,902.474 ↓ 440.2 1,761 1

Nested Loop Left Join (cost=5.61..9,525.99 rows=4 width=592) (actual time=7,269.902..9,902.474 rows=1,761 loops=1)

8. 12.847 9,875.780 ↓ 440.2 1,761 1

Nested Loop Left Join (cost=5.19..9,523.96 rows=4 width=577) (actual time=7,269.811..9,875.780 rows=1,761 loops=1)

9. 14.702 9,848.845 ↓ 440.2 1,761 1

Nested Loop Left Join (cost=4.77..9,521.93 rows=4 width=562) (actual time=7,269.790..9,848.845 rows=1,761 loops=1)

10. 663.941 9,800.684 ↓ 440.2 1,761 1

Nested Loop Left Join (cost=4.35..9,519.89 rows=4 width=547) (actual time=7,269.751..9,800.684 rows=1,761 loops=1)

  • Join Filter: (mu.id = ar.unit_id)
  • Rows Removed by Join Filter: 401,666
11. 12.867 8,515.110 ↓ 440.2 1,761 1

Nested Loop Left Join (cost=4.35..9,487.92 rows=4 width=542) (actual time=7,269.091..8,515.110 rows=1,761 loops=1)

12. 16.263 8,474.067 ↓ 440.2 1,761 1

Nested Loop Left Join (cost=3.93..9,486.14 rows=4 width=542) (actual time=7,269.058..8,474.067 rows=1,761 loops=1)

13. 577.024 8,426.106 ↓ 440.2 1,761 1

Nested Loop Left Join (cost=3.51..9,484.36 rows=4 width=529) (actual time=7,269.013..8,426.106 rows=1,761 loops=1)

  • Join Filter: (u.id = ar.value_by_id)
  • Rows Removed by Join Filter: 346,234
14. 40.361 7,310.216 ↓ 440.2 1,761 1

Nested Loop (cost=3.51..9,425.87 rows=4 width=515) (actual time=7,268.899..7,310.216 rows=1,761 loops=1)

  • Join Filter: (fsd.department_id = d.id)
  • Rows Removed by Join Filter: 14,088
15. 0.060 0.060 ↑ 1.0 9 1

Seq Scan on departments d (cost=0.00..1.09 rows=9 width=47) (actual time=0.030..0.060 rows=9 loops=1)

16. 31.934 7,269.795 ↓ 440.2 1,761 9

Materialize (cost=3.51..9,424.25 rows=4 width=476) (actual time=628.277..807.755 rows=1,761 loops=9)

17. 5.734 7,237.861 ↓ 440.2 1,761 1

Nested Loop (cost=3.51..9,424.23 rows=4 width=476) (actual time=5,654.452..7,237.861 rows=1,761 loops=1)

18. 0.014 0.078 ↑ 1.0 1 1

Nested Loop (cost=0.29..3.63 rows=1 width=63) (actual time=0.056..0.078 rows=1 loops=1)

19. 0.042 0.042 ↑ 1.0 1 1

Index Only Scan using pk_functional_structures_departments on functional_structures_departments fsd (cost=0.14..1.26 rows=1 width=8) (actual time=0.035..0.042 rows=1 loops=1)

  • Index Cond: (functional_structure_id = 28)
  • Heap Fetches: 1
20. 0.022 0.022 ↑ 1.0 1 1

Index Scan using functional_structures_pkey on functional_structures fs (cost=0.14..2.36 rows=1 width=55) (actual time=0.012..0.022 rows=1 loops=1)

  • Index Cond: (id = 28)
21. 1,162.763 7,232.049 ↓ 440.2 1,761 1

Nested Loop (cost=3.22..9,420.56 rows=4 width=421) (actual time=5,654.388..7,232.049 rows=1,761 loops=1)

  • Join Filter: (sr.lab_matrix_id = lm.id)
  • Rows Removed by Join Filter: 598,740
22. 1.191 1.191 ↓ 1.0 341 1

Seq Scan on lab_matrices lm (cost=0.00..19.39 rows=339 width=26) (actual time=0.016..1.191 rows=341 loops=1)

23. 949.262 6,068.095 ↓ 440.2 1,761 341

Materialize (cost=3.22..9,380.84 rows=4 width=403) (actual time=2.553..17.795 rows=1,761 loops=341)

24. 11.462 5,118.833 ↓ 440.2 1,761 1

Nested Loop (cost=3.22..9,380.82 rows=4 width=403) (actual time=870.139..5,118.833 rows=1,761 loops=1)

25. 11.122 5,095.044 ↓ 440.2 1,761 1

Nested Loop Left Join (cost=2.94..9,379.61 rows=4 width=361) (actual time=870.111..5,095.044 rows=1,761 loops=1)

26. 12.166 5,073.356 ↓ 440.2 1,761 1

Nested Loop (cost=2.66..9,378.43 rows=4 width=326) (actual time=870.079..5,073.356 rows=1,761 loops=1)

27. 473.235 5,048.863 ↓ 440.2 1,761 1

Nested Loop Left Join (cost=2.38..9,377.24 rows=4 width=284) (actual time=870.024..5,048.863 rows=1,761 loops=1)

  • Join Filter: (apl.id = spa.analytical_parameter_list_id)
  • Rows Removed by Join Filter: 287,715
28. 12.251 4,133.617 ↓ 440.2 1,761 1

Nested Loop (cost=2.38..9,323.33 rows=4 width=245) (actual time=866.580..4,133.617 rows=1,761 loops=1)

29. 1,172.584 4,109.039 ↓ 440.2 1,761 1

Nested Loop Left Join (cost=2.10..9,319.81 rows=4 width=213) (actual time=866.548..4,109.039 rows=1,761 loops=1)

  • Join Filter: (sre.id = sr.sample_reference_id)
  • Rows Removed by Join Filter: 700,255
30. 14.000 1,844.635 ↓ 440.2 1,761 1

Nested Loop (cost=2.10..9,262.96 rows=4 width=187) (actual time=862.591..1,844.635 rows=1,761 loops=1)

31. 477.154 1,812.731 ↓ 746.0 1,492 1

Nested Loop Left Join (cost=1.68..9,254.67 rows=2 width=154) (actual time=862.550..1,812.731 rows=1,492 loops=1)

  • Join Filter: (srg.id = sr.sample_reference_group_id)
  • Rows Removed by Join Filter: 282,790
32. 23.662 895.437 ↓ 746.0 1,492 1

Nested Loop (cost=1.68..9,235.98 rows=2 width=131) (actual time=860.190..895.437 rows=1,492 loops=1)

  • Join Filter: (sp.sample_type_id = st.id)
  • Rows Removed by Join Filter: 10,444
33. 0.039 0.039 ↑ 1.0 8 1

Seq Scan on sample_types st (cost=0.00..1.08 rows=8 width=22) (actual time=0.011..0.039 rows=8 loops=1)

34. 23.505 871.736 ↓ 746.0 1,492 8

Materialize (cost=1.68..9,234.67 rows=2 width=117) (actual time=3.349..108.967 rows=1,492 loops=8)

35. 9.708 848.231 ↓ 746.0 1,492 1

Nested Loop (cost=1.68..9,234.66 rows=2 width=117) (actual time=26.762..848.231 rows=1,492 loops=1)

  • Join Filter: (s.registration_id = sr.id)
36. 18.990 829.571 ↓ 746.0 1,492 1

Nested Loop (cost=1.26..9,233.74 rows=2 width=69) (actual time=26.733..829.571 rows=1,492 loops=1)

  • Join Filter: (sp.parameter_id = spa.analytical_parameter_id)
  • Rows Removed by Join Filter: 5,866
37. 9.681 786.709 ↓ 41.4 1,492 1

Nested Loop (cost=0.84..9,210.89 rows=36 width=53) (actual time=26.628..786.709 rows=1,492 loops=1)

38. 184.492 768.076 ↓ 41.4 1,492 1

Nested Loop (cost=0.42..9,156.33 rows=36 width=41) (actual time=26.582..768.076 rows=1,492 loops=1)

39. 181.032 181.032 ↓ 109.6 44,728 1

Seq Scan on work_orders w (cost=0.00..5,897.14 rows=408 width=13) (actual time=0.025..181.032 rows=44,728 loops=1)

  • Filter: (active AND ((received_at)::date >= '2020-04-01'::date) AND ((received_at)::date <= '2020-06-29'::date))
  • Rows Removed by Filter: 37,762
40. 402.552 402.552 ↓ 0.0 0 44,728

Index Scan using index_sample_parameters_work_order_id on sample_parameters sp (cost=0.42..7.98 rows=1 width=32) (actual time=0.009..0.009 rows=0 loops=44,728)

  • Index Cond: (work_order_id = w.id)
  • Filter: (functional_structure_id = 28)
  • Rows Removed by Filter: 2
41. 8.952 8.952 ↑ 1.0 1 1,492

Index Scan using samples_pkey on samples s (cost=0.42..1.52 rows=1 width=16) (actual time=0.006..0.006 rows=1 loops=1,492)

  • Index Cond: (id = sp.sample_id)
42. 23.872 23.872 ↑ 1.0 5 1,492

Index Scan using index_parameter_assignments_on_registration_id on sample_parameter_assignments spa (cost=0.42..0.57 rows=5 width=16) (actual time=0.007..0.016 rows=5 loops=1,492)

  • Index Cond: (sample_registration_id = s.registration_id)
43. 8.952 8.952 ↑ 1.0 1 1,492

Index Scan using sample_registrations_pkey on sample_registrations sr (cost=0.42..0.45 rows=1 width=56) (actual time=0.006..0.006 rows=1 loops=1,492)

  • Index Cond: (id = spa.sample_registration_id)
  • Filter: active
44. 439.478 440.140 ↑ 1.7 190 1,492

Materialize (cost=0.00..9.83 rows=322 width=31) (actual time=0.002..0.295 rows=190 loops=1,492)

45. 0.662 0.662 ↓ 1.0 333 1

Seq Scan on sample_reference_groups srg (cost=0.00..8.22 rows=322 width=31) (actual time=0.034..0.662 rows=333 loops=1)

46. 17.904 17.904 ↑ 5.0 1 1,492

Index Scan using index_analytical_results_sample_parameter_id on analytical_results ar (cost=0.42..4.09 rows=5 width=37) (actual time=0.009..0.012 rows=1 loops=1,492)

  • Index Cond: (sample_parameter_id = sp.id)
47. 1,090.733 1,091.820 ↑ 1.7 398 1,761

Materialize (cost=0.00..18.90 rows=660 width=34) (actual time=0.002..0.620 rows=398 loops=1,761)

48. 1.087 1.087 ↑ 1.2 549 1

Seq Scan on sample_references sre (cost=0.00..15.60 rows=660 width=34) (actual time=0.022..1.087 rows=549 loops=1)

49. 12.327 12.327 ↑ 1.0 1 1,761

Index Scan using entities_pkey on entities e (cost=0.28..0.88 rows=1 width=40) (actual time=0.007..0.007 rows=1 loops=1,761)

  • Index Cond: (id = w.store_id)
50. 441.134 442.011 ↑ 2.3 164 1,761

Materialize (cost=0.00..31.78 rows=385 width=47) (actual time=0.002..0.251 rows=164 loops=1,761)

51. 0.877 0.877 ↓ 1.0 396 1

Seq Scan on analytical_parameter_lists apl (cost=0.00..29.85 rows=385 width=47) (actual time=0.018..0.877 rows=396 loops=1)

52. 12.327 12.327 ↑ 1.0 1 1,761

Index Scan using analytical_parameters_pkey on analytical_parameters ap (cost=0.28..0.30 rows=1 width=54) (actual time=0.007..0.007 rows=1 loops=1,761)

  • Index Cond: (id = spa.analytical_parameter_id)
53. 10.566 10.566 ↑ 1.0 1 1,761

Index Scan using analytical_methods_pkey on analytical_methods am (cost=0.28..0.30 rows=1 width=43) (actual time=0.006..0.006 rows=1 loops=1,761)

  • Index Cond: (id = spa.analytical_method_id)
54. 12.327 12.327 ↑ 1.0 1 1,761

Index Scan using result_definitions_pkey on result_definitions rd (cost=0.28..0.30 rows=1 width=50) (actual time=0.007..0.007 rows=1 loops=1,761)

  • Index Cond: (id = ar.definition_id)
55. 537.957 538.866 ↑ 2.0 197 1,761

Materialize (cost=0.00..35.90 rows=393 width=22) (actual time=0.002..0.306 rows=197 loops=1,761)

56. 0.909 0.909 ↑ 1.0 393 1

Seq Scan on users u (cost=0.00..33.93 rows=393 width=22) (actual time=0.035..0.909 rows=393 loops=1)

57. 31.698 31.698 ↑ 1.0 1 1,761

Index Scan using text_result_values_pkey on text_result_values trv (cost=0.42..0.45 rows=1 width=17) (actual time=0.018..0.018 rows=1 loops=1,761)

  • Index Cond: (id = ar.value_id)
58. 28.176 28.176 ↑ 1.0 1 1,761

Index Scan using numeric_result_values_pkey on numeric_result_values nrv (cost=0.42..0.45 rows=1 width=8) (actual time=0.016..0.016 rows=1 loops=1,761)

  • Index Cond: (id = ar.value_id)
59. 620.870 621.633 ↑ 1.6 229 1,761

Materialize (cost=0.00..10.58 rows=372 width=13) (actual time=0.002..0.353 rows=229 loops=1,761)

60. 0.763 0.763 ↓ 1.0 380 1

Seq Scan on measurement_units mu (cost=0.00..8.72 rows=372 width=13) (actual time=0.013..0.763 rows=380 loops=1)

61. 33.459 33.459 ↑ 1.0 1 1,761

Index Scan using index_field_values_on_target_id_and_target_type_and_field_id on extension_field_values tc (cost=0.42..0.51 rows=1 width=19) (actual time=0.019..0.019 rows=1 loops=1,761)

  • Index Cond: ((extension_target_id = sr.id) AND ((extension_target_type)::text = 'SampleRegistration'::text) AND (field_id = 46))
62. 14.088 14.088 ↑ 1.0 1 1,761

Index Scan using index_field_values_on_target_id_and_target_type_and_field_id on extension_field_values tr (cost=0.42..0.51 rows=1 width=19) (actual time=0.008..0.008 rows=1 loops=1,761)

  • Index Cond: ((extension_target_id = sr.id) AND ((extension_target_type)::text = 'SampleRegistration'::text) AND (field_id = 45))
63. 12.327 12.327 ↑ 1.0 1 1,761

Index Scan using index_field_values_on_target_id_and_target_type_and_field_id on extension_field_values lc (cost=0.42..0.51 rows=1 width=19) (actual time=0.007..0.007 rows=1 loops=1,761)

  • Index Cond: ((extension_target_id = sr.id) AND ((extension_target_type)::text = 'SampleRegistration'::text) AND (field_id = 37))
64. 12.327 12.327 ↑ 1.0 1 1,761

Index Scan using index_field_values_on_target_id_and_target_type_and_field_id on extension_field_values pc (cost=0.42..0.51 rows=1 width=19) (actual time=0.007..0.007 rows=1 loops=1,761)

  • Index Cond: ((extension_target_id = sr.id) AND ((extension_target_type)::text = 'SampleRegistration'::text) AND (field_id = 30))
65. 5,039.571 5,048.787 ↓ 3.5 1,849 1,761

Materialize (cost=0.00..636.65 rows=525 width=102) (actual time=0.002..2.867 rows=1,849 loops=1,761)

66. 9.216 9.216 ↓ 3.5 1,849 1

Seq Scan on report_notes acond (cost=0.00..634.02 rows=525 width=102) (actual time=0.024..9.216 rows=1,849 loops=1)

  • Filter: ((report_note_definition_id = 2) AND ((commentable_type)::text = 'SampleRegistration'::text))
  • Rows Removed by Filter: 11,707
67. 38.742 38.742 ↓ 0.0 0 1,761

Index Scan using idx_fs_type on functional_structures_notes notas_amostra (cost=0.28..0.31 rows=1 width=221) (actual time=0.021..0.022 rows=0 loops=1,761)

  • Index Cond: (((type)::text = 'sample'::text) AND (type_id = s.id) AND (functional_structure_id = 28))
68. 15.849 15.849 ↓ 0.0 0 1,761

Index Scan using idx_fs_type on functional_structures_notes notas_requisicao (cost=0.28..0.87 rows=1 width=221) (actual time=0.009..0.009 rows=0 loops=1,761)

  • Index Cond: (((type)::text = 'workOrder'::text) AND (type_id = w.id) AND (functional_structure_id = 28))
Planning time : 68.187 ms
Execution time : 21,392.486 ms