explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ppd6

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 1,163.716 ↓ 1,019.1 43,822 1

Gather (cost=1,399.21..7,312.76 rows=43 width=1,910) (actual time=11.360..1,163.716 rows=43,822 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
2. 9.876 491.221 ↓ 811.5 14,607 3 / 3

Nested Loop Left Join (cost=399.21..5,713.37 rows=18 width=945) (actual time=5.864..491.221 rows=14,607 loops=3)

3. 9.448 481.345 ↓ 811.5 14,607 3 / 3

Nested Loop Left Join (cost=398.93..5,707.95 rows=18 width=905) (actual time=5.855..481.345 rows=14,607 loops=3)

4. 9.707 471.897 ↓ 811.5 14,607 3 / 3

Nested Loop Left Join (cost=398.65..5,702.61 rows=18 width=872) (actual time=5.850..471.897 rows=14,607 loops=3)

5. 10.666 462.190 ↓ 811.5 14,607 3 / 3

Nested Loop Left Join (cost=398.37..5,680.53 rows=18 width=832) (actual time=5.843..462.190 rows=14,607 loops=3)

6. 6.253 451.524 ↓ 811.5 14,607 3 / 3

Hash Left Join (cost=398.09..5,675.11 rows=18 width=792) (actual time=5.835..451.524 rows=14,607 loops=3)

  • Hash Cond: (sr.id = ica.commentable_id)
7. 6.002 444.209 ↓ 811.5 14,607 3 / 3

Nested Loop Left Join (cost=261.23..5,538.11 rows=18 width=792) (actual time=4.755..444.209 rows=14,607 loops=3)

  • Join Filter: ((ar.type)::text = 'text'::text)
8. 8.626 408.992 ↓ 811.5 14,607 3 / 3

Hash Left Join (cost=260.81..5,529.97 rows=18 width=798) (actual time=4.706..408.992 rows=14,607 loops=3)

  • Hash Cond: ((w.id = rtl.work_order_id) AND (sp.id = rtl.sample_parameter_id))
9. 7.364 397.222 ↓ 811.3 14,603 3 / 3

Nested Loop Left Join (cost=11.18..5,279.97 rows=18 width=794) (actual time=1.516..397.222 rows=14,603 loops=3)

10. 9.378 360.653 ↓ 811.3 14,603 3 / 3

Nested Loop (cost=10.90..5,247.06 rows=18 width=792) (actual time=1.488..360.653 rows=14,603 loops=3)

11. 7.946 322.070 ↓ 811.3 14,603 3 / 3

Hash Join (cost=10.62..5,237.53 rows=18 width=778) (actual time=1.460..322.070 rows=14,603 loops=3)

  • Hash Cond: (fsd.department_id = d.id)
12. 18.733 313.826 ↓ 811.3 14,603 3 / 3

Nested Loop (cost=9.42..5,236.26 rows=18 width=266) (actual time=1.039..313.826 rows=14,603 loops=3)

13. 9.709 280.490 ↓ 811.3 14,603 3 / 3

Hash Join (cost=9.27..5,233.29 rows=18 width=245) (actual time=1.011..280.490 rows=14,603 loops=3)

  • Hash Cond: (sp.functional_structure_id = fsd.functional_structure_id)
14. 15.586 270.198 ↓ 811.3 14,603 3 / 3

Nested Loop (cost=2.41..5,226.17 rows=18 width=190) (actual time=0.416..270.198 rows=14,603 loops=3)

15. 5.931 225.407 ↓ 811.3 14,603 3 / 3

Nested Loop (cost=2.12..5,220.76 rows=18 width=148) (actual time=0.393..225.407 rows=14,603 loops=3)

16. 12.483 170.398 ↓ 818.0 8,180 3 / 3

Nested Loop (cost=1.70..5,214.02 rows=10 width=130) (actual time=0.349..170.398 rows=8,180 loops=3)

17. 6.994 141.556 ↓ 818.0 8,180 3 / 3

Nested Loop (cost=1.42..5,211.03 rows=10 width=74) (actual time=0.327..141.556 rows=8,180 loops=3)

  • Join Filter: (s.registration_id = sr.id)
18. 13.624 109.678 ↓ 754.1 8,295 3 / 3

Nested Loop (cost=1.13..5,207.41 rows=11 width=58) (actual time=0.293..109.678 rows=8,295 loops=3)

19. 4.957 62.875 ↓ 24.3 8,295 3 / 3

Nested Loop (cost=0.71..5,037.66 rows=341 width=42) (actual time=0.261..62.875 rows=8,295 loops=3)

20. 2.588 33.034 ↓ 24.3 8,295 3 / 3

Nested Loop (cost=0.42..4,923.93 rows=341 width=38) (actual time=0.232..33.034 rows=8,295 loops=3)

21. 10.444 10.444 ↓ 27.1 1,818 3 / 3

Parallel Seq Scan on work_orders w (cost=0.00..4,252.79 rows=67 width=22) (actual time=0.173..10.444 rows=1,818 loops=3)

  • Filter: (active AND ((received_at)::date >= '2020-01-01'::date) AND ((received_at)::date <= '2020-01-31'::date))
  • Rows Removed by Filter: 9,053
22. 20.002 20.002 ↑ 2.2 5 5,455 / 3

Index Scan using index_sample_parameters_work_order_id on sample_parameters sp (cost=0.42..9.91 rows=11 width=20) (actual time=0.006..0.011 rows=5 loops=5,455)

  • Index Cond: (work_order_id = w.id)
23. 24.884 24.884 ↑ 1.0 1 24,884 / 3

Index Scan using samples_pkey on samples s (cost=0.29..0.33 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=24,884)

  • Index Cond: (id = sp.sample_id)
24. 33.179 33.179 ↑ 1.0 1 24,884 / 3

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=24,884)

  • Index Cond: ((analytical_parameter_id = sp.parameter_id) AND (sample_registration_id = s.registration_id))
25. 24.884 24.884 ↑ 1.0 1 24,884 / 3

Index Scan using sample_registrations_pkey on sample_registrations sr (cost=0.29..0.32 rows=1 width=28) (actual time=0.003..0.003 rows=1 loops=24,884)

  • Index Cond: (id = spa.sample_registration_id)
  • Filter: active
  • Rows Removed by Filter: 0
26. 16.359 16.359 ↑ 1.0 1 24,539 / 3

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=24,539)

  • Index Cond: (id = spa.analytical_parameter_id)
27. 49.078 49.078 ↑ 2.5 2 24,539 / 3

Index Scan using index_analytical_results_sample_parameter_id on analytical_results ar (cost=0.42..0.62 rows=5 width=22) (actual time=0.005..0.006 rows=2 loops=24,539)

  • Index Cond: (sample_parameter_id = sp.id)
28. 29.205 29.205 ↑ 1.0 1 43,808 / 3

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=43,808)

  • Index Cond: (id = ar.definition_id)
29. 0.024 0.583 ↑ 1.0 81 3 / 3

Hash (cost=5.86..5.86 rows=81 width=63) (actual time=0.583..0.583 rows=81 loops=3)

  • Buckets: 1,024 Batches: 1 Memory Usage: 16kB
30. 0.037 0.559 ↑ 1.0 81 3 / 3

Hash Join (cost=3.82..5.86 rows=81 width=63) (actual time=0.533..0.559 rows=81 loops=3)

  • Hash Cond: (fsd.functional_structure_id = fs.id)
31. 0.254 0.254 ↑ 1.0 81 3 / 3

Seq Scan on functional_structures_departments fsd (cost=0.00..1.81 rows=81 width=8) (actual time=0.249..0.254 rows=81 loops=3)

32. 0.023 0.268 ↑ 1.0 81 3 / 3

Hash (cost=2.81..2.81 rows=81 width=55) (actual time=0.268..0.268 rows=81 loops=3)

  • Buckets: 1,024 Batches: 1 Memory Usage: 16kB
33. 0.245 0.245 ↑ 1.0 81 3 / 3

Seq Scan on functional_structures fs (cost=0.00..2.81 rows=81 width=55) (actual time=0.230..0.245 rows=81 loops=3)

34. 14.603 14.603 ↑ 1.0 1 43,808 / 3

Index Scan using lab_matrices_pkey on lab_matrices lm (cost=0.15..0.17 rows=1 width=25) (actual time=0.001..0.001 rows=1 loops=43,808)

  • Index Cond: (id = sr.lab_matrix_id)
35. 0.018 0.298 ↑ 1.0 9 3 / 3

Hash (cost=1.09..1.09 rows=9 width=520) (actual time=0.298..0.298 rows=9 loops=3)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
36. 0.280 0.280 ↑ 1.0 9 3 / 3

Seq Scan on departments d (cost=0.00..1.09 rows=9 width=520) (actual time=0.278..0.280 rows=9 loops=3)

37. 29.205 29.205 ↑ 1.0 1 43,808 / 3

Index Scan using users_pkey on users u (cost=0.27..0.53 rows=1 width=22) (actual time=0.002..0.002 rows=1 loops=43,808)

  • Index Cond: (id = w.created_by_id)
38. 29.205 29.205 ↑ 1.0 1 43,808 / 3

Index Scan using patients_pk on patients p (cost=0.29..1.83 rows=1 width=10) (actual time=0.002..0.002 rows=1 loops=43,808)

  • Index Cond: (id = w.patient_id)
39. 1.564 3.144 ↑ 1.0 7,265 3 / 3

Hash (cost=140.65..140.65 rows=7,265 width=12) (actual time=3.144..3.144 rows=7,265 loops=3)

  • Buckets: 8,192 Batches: 1 Memory Usage: 377kB
40. 1.580 1.580 ↑ 1.0 7,265 3 / 3

Seq Scan on responsability_term_lines rtl (cost=0.00..140.65 rows=7,265 width=12) (actual time=0.243..1.580 rows=7,265 loops=3)

41. 29.215 29.215 ↓ 0.0 0 43,822 / 3

Index Scan using text_result_values_pkey on text_result_values trv (cost=0.42..0.44 rows=1 width=8) (actual time=0.002..0.002 rows=0 loops=43,822)

  • Index Cond: (id = ar.value_id)
42. 0.013 1.062 ↑ 6.8 14 3 / 3

Hash (cost=135.68..135.68 rows=95 width=8) (actual time=1.061..1.062 rows=14 loops=3)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
43. 1.049 1.049 ↑ 6.8 14 3 / 3

Seq Scan on report_notes ica (cost=0.00..135.68 rows=95 width=8) (actual time=1.037..1.049 rows=14 loops=3)

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

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=43,822)

  • Index Cond: (id = spa.clinical_information_pre_defined_text_id)
  • Filter: ((master_type)::text = 'comment'::text)
45. 0.000 0.000 ↓ 0.0 0 43,822 / 3

Index Scan using pre_defined_texts_pkey on pre_defined_texts pdtica (cost=0.28..1.23 rows=1 width=48) (actual time=0.000..0.000 rows=0 loops=43,822)

  • Index Cond: (id = ica.pre_defined_text_id)
  • Filter: ((master_type)::text = 'comment'::text)
46. 0.000 0.000 ↓ 0.0 0 43,822 / 3

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

  • Index Cond: (id = trv.text_result_pre_defined_text_id)
47. 0.000 0.000 ↓ 0.0 0 43,822 / 3

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=43,822)

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

SubPlan (for Gather)

49. 43.822 262.932 ↓ 0.0 0 43,822

Limit (cost=3.77..3.77 rows=1 width=8) (actual time=0.006..0.006 rows=0 loops=43,822)

50. 43.822 219.110 ↓ 0.0 0 43,822

Sort (cost=3.77..3.77 rows=1 width=8) (actual time=0.005..0.005 rows=0 loops=43,822)

  • Sort Key: report_parametro.created_at
  • Sort Method: quicksort Memory: 25kB
51. 175.288 175.288 ↓ 0.0 0 43,822

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.004..0.004 rows=0 loops=43,822)

  • 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: 2
52. 0.000 219.110 ↓ 0.0 0 43,822

Limit (cost=3.76..3.77 rows=1 width=8) (actual time=0.005..0.005 rows=0 loops=43,822)

53. 43.822 219.110 ↓ 0.0 0 43,822

Sort (cost=3.76..3.77 rows=1 width=8) (actual time=0.005..0.005 rows=0 loops=43,822)

  • Sort Key: report_amostra.created_at
  • Sort Method: quicksort Memory: 25kB
54. 175.288 175.288 ↓ 0.0 0 43,822

Index Scan using index_analytical_reports_on_work_order_id_and_preview on analytical_reports report_amostra (cost=0.42..3.75 rows=1 width=8) (actual time=0.003..0.004 rows=0 loops=43,822)

  • 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: 2
55. 0.000 136.432 ↓ 0.0 0 34,108

Limit (cost=3.76..3.76 rows=1 width=8) (actual time=0.004..0.004 rows=0 loops=34,108)

56. 34.108 136.432 ↓ 0.0 0 34,108

Sort (cost=3.76..3.76 rows=1 width=8) (actual time=0.004..0.004 rows=0 loops=34,108)

  • Sort Key: report_requisicao.created_at
  • Sort Method: quicksort Memory: 25kB
57. 102.324 102.324 ↓ 0.0 0 34,108

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.003..0.003 rows=0 loops=34,108)

  • 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: 1
58. 0.000 350.576 ↑ 1.0 1 43,822

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

59. 43.822 350.576 ↑ 1.0 1 43,822

Sort (cost=2.52..2.52 rows=1 width=12) (actual time=0.008..0.008 rows=1 loops=43,822)

  • Sort Key: technical_parameters.lab_matrix_id, technical_parameters.functional_structure_id, technical_parameters.response_time_days
  • Sort Method: quicksort Memory: 25kB
60. 306.754 306.754 ↑ 1.0 1 43,822

Index 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.007 rows=1 loops=43,822)

  • 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