explain.depesz.com

PostgreSQL's explain analyze made readable

Result: eLj7

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 364.469 ↓ 46.4 13,864 1

Gather (cost=1,929.54..10,637.33 rows=299 width=1,912) (actual time=11.339..364.469 rows=13,864 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
2. 4.540 167.588 ↓ 39.4 6,932 2 / 2

Nested Loop Left Join (cost=929.54..5,465.15 rows=176 width=947) (actual time=8.537..167.588 rows=6,932 loops=2)

3. 4.485 163.048 ↓ 39.4 6,932 2 / 2

Nested Loop Left Join (cost=929.26..5,412.16 rows=176 width=907) (actual time=8.531..163.048 rows=6,932 loops=2)

4. 4.869 158.563 ↓ 39.4 6,932 2 / 2

Nested Loop Left Join (cost=928.98..5,359.39 rows=176 width=872) (actual time=8.524..158.563 rows=6,932 loops=2)

5. 5.855 153.694 ↓ 39.4 6,932 2 / 2

Nested Loop Left Join (cost=928.70..5,306.40 rows=176 width=832) (actual time=8.520..153.694 rows=6,932 loops=2)

  • Join Filter: ((ar.type)::text = 'text'::text)
6. 2.704 140.907 ↓ 39.4 6,932 2 / 2

Hash Left Join (cost=928.29..5,226.84 rows=176 width=838) (actual time=8.486..140.907 rows=6,932 loops=2)

  • Hash Cond: (sr.id = ica.commentable_id)
7. 3.472 137.416 ↓ 39.4 6,932 2 / 2

Hash Left Join (cost=688.09..4,985.31 rows=176 width=798) (actual time=7.657..137.416 rows=6,932 loops=2)

  • Hash Cond: ((w.id = rtl.work_order_id) AND (sp.id = rtl.sample_parameter_id))
8. 9.042 131.386 ↓ 39.4 6,932 2 / 2

Nested Loop Left Join (cost=438.89..4,732.58 rows=176 width=794) (actual time=5.056..131.386 rows=6,932 loops=2)

9. 3.442 115.412 ↓ 39.4 6,932 2 / 2

Hash Join (cost=438.60..4,559.92 rows=176 width=792) (actual time=5.031..115.412 rows=6,932 loops=2)

  • Hash Cond: (w.created_by_id = u.id)
10. 3.265 111.550 ↓ 39.4 6,932 2 / 2

Hash Join (cost=414.82..4,535.69 rows=176 width=778) (actual time=4.515..111.550 rows=6,932 loops=2)

  • Hash Cond: (sr.lab_matrix_id = lm.id)
11. 4.585 107.925 ↓ 39.4 6,932 2 / 2

Hash Join (cost=398.20..4,518.59 rows=176 width=757) (actual time=4.144..107.925 rows=6,932 loops=2)

  • Hash Cond: (sp.functional_structure_id = fsd.functional_structure_id)
12. 5.109 102.648 ↓ 39.4 6,932 2 / 2

Nested Loop (cost=389.76..4,507.73 rows=176 width=190) (actual time=3.442..102.648 rows=6,932 loops=2)

13. 5.938 83.675 ↓ 39.4 6,932 2 / 2

Nested Loop (cost=389.48..4,454.81 rows=176 width=148) (actual time=3.423..83.675 rows=6,932 loops=2)

14. 4.113 63.527 ↓ 37.4 3,553 2 / 2

Nested Loop (cost=389.06..4,396.34 rows=95 width=130) (actual time=3.399..63.527 rows=3,553 loops=2)

15. 4.583 52.309 ↓ 37.4 3,553 2 / 2

Nested Loop (cost=388.78..4,367.98 rows=95 width=74) (actual time=3.384..52.309 rows=3,553 loops=2)

  • Join Filter: (s.registration_id = sr.id)
16. 3.409 40.521 ↓ 34.0 3,603 2 / 2

Nested Loop (cost=388.49..4,333.05 rows=106 width=58) (actual time=3.362..40.521 rows=3,603 loops=2)

17. 2.002 22.702 ↓ 1.1 3,603 2 / 2

Nested Loop (cost=388.07..2,663.97 rows=3,356 width=42) (actual time=3.329..22.702 rows=3,603 loops=2)

18. 5.041 12.566 ↓ 1.1 1,162 2 / 2

Hash Join (cost=387.65..1,982.61 rows=1,016 width=30) (actual time=3.298..12.566 rows=1,162 loops=2)

  • Hash Cond: (s.work_order_id = w.id)
19. 4.466 4.466 ↑ 1.2 25,124 2 / 2

Parallel Seq Scan on samples s (cost=0.00..1,516.99 rows=29,699 width=12) (actual time=0.013..4.466 rows=25,124 loops=2)

20. 0.324 3.059 ↓ 1.3 1,431 2 / 2

Hash (cost=373.74..373.74 rows=1,113 width=22) (actual time=3.059..3.059 rows=1,431 loops=2)

  • Buckets: 2,048 Batches: 1 Memory Usage: 94kB
21. 2.735 2.735 ↓ 1.3 1,431 2 / 2

Index Scan using idx_received_at on work_orders w (cost=0.29..373.74 rows=1,113 width=22) (actual time=0.043..2.735 rows=1,431 loops=2)

  • Index Cond: ((received_at >= '2020-02-29 23:59:59'::timestamp without time zone) AND (received_at <= '2020-06-30 23:59:59'::timestamp without time zone))
  • Filter: active
  • Rows Removed by Filter: 18
22. 8.134 8.134 ↑ 2.0 3 2,324 / 2

Index Scan using index_sample_parameters_on_sample_id_and_position on sample_parameters sp (cost=0.42..0.61 rows=6 width=16) (actual time=0.005..0.007 rows=3 loops=2,324)

  • Index Cond: (sample_id = s.id)
23. 14.410 14.410 ↑ 1.0 1 7,205 / 2

Index Scan using index_parameter_assignments_on_parameter_id_and_registration_id on sample_parameter_assignments spa (cost=0.42..0.50 rows=1 width=16) (actual time=0.004..0.004 rows=1 loops=7,205)

  • Index Cond: ((analytical_parameter_id = sp.parameter_id) AND (sample_registration_id = s.registration_id))
24. 7.205 7.205 ↑ 1.0 1 7,205 / 2

Index Scan using sample_registrations_pkey on sample_registrations sr (cost=0.29..0.32 rows=1 width=28) (actual time=0.002..0.002 rows=1 loops=7,205)

  • Index Cond: (id = spa.sample_registration_id)
  • Filter: active
  • Rows Removed by Filter: 0
25. 7.105 7.105 ↑ 1.0 1 7,105 / 2

Index Scan using analytical_parameters_pkey on analytical_parameters ap (cost=0.28..0.30 rows=1 width=64) (actual time=0.002..0.002 rows=1 loops=7,105)

  • Index Cond: (id = spa.analytical_parameter_id)
26. 14.210 14.210 ↑ 2.5 2 7,105 / 2

Index Scan using index_analytical_results_sample_parameter_id on analytical_results ar (cost=0.42..0.57 rows=5 width=22) (actual time=0.004..0.004 rows=2 loops=7,105)

  • Index Cond: (sample_parameter_id = sp.id)
27. 13.864 13.864 ↑ 1.0 1 13,864 / 2

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

  • Index Cond: (id = ar.definition_id)
28. 0.034 0.692 ↑ 1.0 82 2 / 2

Hash (cost=7.41..7.41 rows=82 width=575) (actual time=0.692..0.692 rows=82 loops=2)

  • Buckets: 1,024 Batches: 1 Memory Usage: 19kB
29. 0.036 0.658 ↑ 1.0 82 2 / 2

Hash Join (cost=5.05..7.41 rows=82 width=575) (actual time=0.603..0.658 rows=82 loops=2)

  • Hash Cond: (fsd.functional_structure_id = fs.id)
30. 0.037 0.411 ↑ 1.0 82 2 / 2

Hash Join (cost=1.20..3.34 rows=82 width=520) (actual time=0.380..0.411 rows=82 loops=2)

  • Hash Cond: (fsd.department_id = d.id)
31. 0.190 0.190 ↑ 1.0 82 2 / 2

Seq Scan on functional_structures_departments fsd (cost=0.00..1.82 rows=82 width=8) (actual time=0.184..0.190 rows=82 loops=2)

32. 0.008 0.184 ↑ 1.0 9 2 / 2

Hash (cost=1.09..1.09 rows=9 width=520) (actual time=0.184..0.184 rows=9 loops=2)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
33. 0.176 0.176 ↑ 1.0 9 2 / 2

Seq Scan on departments d (cost=0.00..1.09 rows=9 width=520) (actual time=0.174..0.176 rows=9 loops=2)

34. 0.023 0.211 ↑ 1.0 82 2 / 2

Hash (cost=2.82..2.82 rows=82 width=55) (actual time=0.211..0.211 rows=82 loops=2)

  • Buckets: 1,024 Batches: 1 Memory Usage: 16kB
35. 0.188 0.188 ↑ 1.0 82 2 / 2

Seq Scan on functional_structures fs (cost=0.00..2.82 rows=82 width=55) (actual time=0.172..0.188 rows=82 loops=2)

36. 0.063 0.360 ↑ 1.0 339 2 / 2

Hash (cost=12.39..12.39 rows=339 width=25) (actual time=0.359..0.360 rows=339 loops=2)

  • Buckets: 1,024 Batches: 1 Memory Usage: 28kB
37. 0.297 0.297 ↑ 1.0 339 2 / 2

Seq Scan on lab_matrices lm (cost=0.00..12.39 rows=339 width=25) (actual time=0.188..0.297 rows=339 loops=2)

38. 0.083 0.420 ↑ 1.0 390 2 / 2

Hash (cost=18.90..18.90 rows=390 width=22) (actual time=0.420..0.420 rows=390 loops=2)

  • Buckets: 1,024 Batches: 1 Memory Usage: 30kB
39. 0.337 0.337 ↑ 1.0 390 2 / 2

Seq Scan on users u (cost=0.00..18.90 rows=390 width=22) (actual time=0.219..0.337 rows=390 loops=2)

40. 6.932 6.932 ↑ 1.0 1 13,864 / 2

Index Scan using patients_pk on patients p (cost=0.29..0.98 rows=1 width=10) (actual time=0.001..0.001 rows=1 loops=13,864)

  • Index Cond: (id = w.patient_id)
41. 1.226 2.558 ↑ 1.0 7,248 2 / 2

Hash (cost=140.48..140.48 rows=7,248 width=12) (actual time=2.558..2.558 rows=7,248 loops=2)

  • Buckets: 8,192 Batches: 1 Memory Usage: 376kB
42. 1.332 1.332 ↑ 1.0 7,248 2 / 2

Seq Scan on responsability_term_lines rtl (cost=0.00..140.48 rows=7,248 width=12) (actual time=0.198..1.332 rows=7,248 loops=2)

43. 0.000 0.787 ↓ 0.0 0 2 / 2

Hash (cost=239.17..239.17 rows=82 width=48) (actual time=0.787..0.787 rows=0 loops=2)

  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
44. 0.000 0.787 ↓ 0.0 0 2 / 2

Nested Loop Left Join (cost=0.28..239.17 rows=82 width=48) (actual time=0.787..0.787 rows=0 loops=2)

45. 0.787 0.787 ↓ 0.0 0 2 / 2

Seq Scan on report_notes ica (cost=0.00..134.27 rows=82 width=8) (actual time=0.786..0.787 rows=0 loops=2)

  • Filter: ((report_note_definition_id = 3) AND ((commentable_type)::text = 'SampleRegistration'::text))
  • Rows Removed by Filter: 2,818
46. 0.000 0.000 ↓ 0.0 0 / 2

Index Scan using pre_defined_texts_pkey on pre_defined_texts pdtica (cost=0.28..1.28 rows=1 width=48) (never executed)

  • Index Cond: (id = ica.pre_defined_text_id)
  • Filter: ((master_type)::text = 'comment'::text)
47. 6.932 6.932 ↓ 0.0 0 13,864 / 2

Index Scan using text_result_values_pkey on text_result_values trv (cost=0.42..0.44 rows=1 width=8) (actual time=0.001..0.001 rows=0 loops=13,864)

  • Index Cond: (id = ar.value_id)
48. 0.000 0.000 ↓ 0.0 0 13,864 / 2

Index Scan using pre_defined_texts_pkey on pre_defined_texts pdticp (cost=0.28..0.30 rows=1 width=48) (actual time=0.000..0.000 rows=0 loops=13,864)

  • Index Cond: (id = spa.clinical_information_pre_defined_text_id)
  • Filter: ((master_type)::text = 'comment'::text)
49. 0.000 0.000 ↓ 0.0 0 13,864 / 2

Index Scan using pre_defined_results_pkey on pre_defined_results pdr (cost=0.28..0.30 rows=1 width=43) (actual time=0.000..0.000 rows=0 loops=13,864)

  • Index Cond: (id = trv.text_result_pre_defined_text_id)
50. 0.000 0.000 ↓ 0.0 0 13,864 / 2

Index Scan using pre_defined_texts_pkey on pre_defined_texts pdtip (cost=0.28..0.30 rows=1 width=48) (actual time=0.000..0.000 rows=0 loops=13,864)

  • Index Cond: (id = spa.interpretation_notes_pre_defined_text_id)
  • Filter: ((master_type)::text = 'interpretation'::text)
51.          

SubPlan (for Gather)

52. 13.864 55.456 ↓ 0.0 0 13,864

Limit (cost=3.77..3.78 rows=1 width=8) (actual time=0.004..0.004 rows=0 loops=13,864)

53. 13.864 41.592 ↓ 0.0 0 13,864

Sort (cost=3.77..3.78 rows=1 width=8) (actual time=0.003..0.003 rows=0 loops=13,864)

  • Sort Key: report_parametro.created_at
  • Sort Method: quicksort Memory: 25kB
54. 27.728 27.728 ↓ 0.0 0 13,864

Index Scan using index_analytical_reports_on_work_order_id_and_preview on analytical_reports report_parametro (cost=0.42..3.76 rows=1 width=8) (actual time=0.002..0.002 rows=0 loops=13,864)

  • Index Cond: ((work_order_id = w.id) AND (preview = false))
  • Filter: ((NOT partial) AND (sample_id = s.id) AND (sample_parameter_id = sp.id))
  • Rows Removed by Filter: 0
55. 0.000 41.592 ↓ 0.0 0 13,864

Limit (cost=3.77..3.77 rows=1 width=8) (actual time=0.003..0.003 rows=0 loops=13,864)

56. 13.864 41.592 ↓ 0.0 0 13,864

Sort (cost=3.77..3.77 rows=1 width=8) (actual time=0.003..0.003 rows=0 loops=13,864)

  • Sort Key: report_amostra.created_at
  • Sort Method: quicksort Memory: 25kB
57. 27.728 27.728 ↓ 0.0 0 13,864

Index Scan using index_analytical_reports_on_work_order_id_and_preview on analytical_reports report_amostra (cost=0.42..3.76 rows=1 width=8) (actual time=0.002..0.002 rows=0 loops=13,864)

  • Index Cond: ((work_order_id = w.id) AND (preview = false))
  • Filter: ((sample_parameter_id IS NULL) AND (NOT partial) AND (sample_id = s.id))
  • Rows Removed by Filter: 0
58. 0.000 41.526 ↓ 0.0 0 13,842

Limit (cost=3.76..3.77 rows=1 width=8) (actual time=0.003..0.003 rows=0 loops=13,842)

59. 13.842 41.526 ↓ 0.0 0 13,842

Sort (cost=3.76..3.77 rows=1 width=8) (actual time=0.003..0.003 rows=0 loops=13,842)

  • Sort Key: report_requisicao.created_at
  • Sort Method: quicksort Memory: 25kB
60. 27.684 27.684 ↓ 0.0 0 13,842

Index Scan using index_analytical_reports_on_work_order_id_and_preview on analytical_reports report_requisicao (cost=0.42..3.75 rows=1 width=8) (actual time=0.002..0.002 rows=0 loops=13,842)

  • Index Cond: ((work_order_id = w.id) AND (preview = false))
  • Filter: ((sample_id IS NULL) AND (sample_parameter_id IS NULL) AND (NOT partial))
  • Rows Removed by Filter: 0
61. 13.864 110.912 ↑ 1.0 1 13,864

Limit (cost=2.52..2.52 rows=1 width=12) (actual time=0.008..0.008 rows=1 loops=13,864)

62. 13.864 97.048 ↑ 1.0 1 13,864

Sort (cost=2.52..2.52 rows=1 width=12) (actual time=0.007..0.007 rows=1 loops=13,864)

  • Sort Key: technical_parameters.lab_matrix_id, technical_parameters.functional_structure_id, technical_parameters.response_time_days
  • Sort Method: quicksort Memory: 25kB
63. 83.184 83.184 ↑ 1.0 1 13,864

Index Only Scan using index_technical_params_on_method_param_matrix_and_fstruct_ids on technical_parameters (cost=0.29..2.51 rows=1 width=12) (actual time=0.004..0.006 rows=1 loops=13,864)

  • Index Cond: ((analytical_method_id = ar.analytical_method_id) AND (analytical_parameter_id = sp.parameter_id))
  • Filter: (((lab_matrix_id = sr.lab_matrix_id) OR (lab_matrix_id IS NULL)) AND ((functional_structure_id = sp.functional_structure_id) OR (functional_structure_id IS NULL)))
  • Rows Removed by Filter: 6
  • Heap Fetches: 102,322