explain.depesz.com

PostgreSQL's explain analyze made readable

Result: KtTd

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 1,250.806 ↓ 30.5 43,818 1

Gather (cost=7,881.30..33,743.79 rows=1,435 width=1,912) (actual time=126.378..1,250.806 rows=43,818 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
2. 7.318 574.912 ↓ 26.0 21,909 2 / 2

Hash Left Join (cost=6,881.30..12,720.13 rows=844 width=947) (actual time=91.000..574.912 rows=21,909 loops=2)

  • Hash Cond: (spa.interpretation_notes_pre_defined_text_id = pdtip.id)
3. 7.654 566.755 ↓ 26.0 21,909 2 / 2

Hash Left Join (cost=6,691.10..12,527.71 rows=844 width=907) (actual time=90.117..566.755 rows=21,909 loops=2)

  • Hash Cond: (spa.clinical_information_pre_defined_text_id = pdticp.id)
4. 14.135 557.346 ↓ 26.0 21,909 2 / 2

Nested Loop Left Join (cost=6,509.54..12,343.94 rows=844 width=867) (actual time=88.349..557.346 rows=21,909 loops=2)

5. 6.908 543.211 ↓ 26.0 21,909 2 / 2

Nested Loop Left Join (cost=6,509.26..12,090.98 rows=844 width=832) (actual time=88.338..543.211 rows=21,909 loops=2)

  • Join Filter: ((ar.type)::text = 'text'::text)
6. 7.421 492.485 ↓ 26.0 21,909 2 / 2

Hash Left Join (cost=6,508.84..11,709.47 rows=844 width=838) (actual time=88.068..492.485 rows=21,909 loops=2)

  • Hash Cond: (sr.id = ica.commentable_id)
7. 11.104 484.259 ↓ 26.0 21,909 2 / 2

Hash Left Join (cost=6,268.64..11,462.93 rows=844 width=798) (actual time=87.250..484.259 rows=21,909 loops=2)

  • Hash Cond: ((w.id = rtl.work_order_id) AND (sp.id = rtl.sample_parameter_id))
8. 8.566 469.650 ↓ 26.0 21,902 2 / 2

Nested Loop Left Join (cost=6,019.44..11,196.84 rows=844 width=794) (actual time=83.693..469.650 rows=21,902 loops=2)

9. 11.260 417.280 ↓ 26.0 21,902 2 / 2

Hash Join (cost=6,019.16..10,810.66 rows=844 width=792) (actual time=83.475..417.280 rows=21,902 loops=2)

  • Hash Cond: (w.created_by_id = u.id)
10. 9.948 405.512 ↓ 26.0 21,902 2 / 2

Hash Join (cost=5,995.38..10,784.64 rows=844 width=778) (actual time=81.947..405.512 rows=21,902 loops=2)

  • Hash Cond: (sr.lab_matrix_id = lm.id)
11. 13.140 395.177 ↓ 26.0 21,902 2 / 2

Hash Join (cost=5,978.75..10,765.78 rows=844 width=757) (actual time=81.545..395.177 rows=21,902 loops=2)

  • Hash Cond: (sp.functional_structure_id = fsd.functional_structure_id)
12. 13.434 381.326 ↓ 26.0 21,902 2 / 2

Nested Loop (cost=5,970.32..10,745.74 rows=844 width=190) (actual time=80.819..381.326 rows=21,902 loops=2)

13. 11.136 324.088 ↓ 26.0 21,902 2 / 2

Nested Loop (cost=5,970.04..10,491.94 rows=844 width=148) (actual time=80.435..324.088 rows=21,902 loops=2)

14. 12.791 227.080 ↓ 26.9 12,268 2 / 2

Nested Loop (cost=5,969.61..10,211.29 rows=456 width=130) (actual time=80.013..227.080 rows=12,268 loops=2)

15. 11.609 189.754 ↓ 26.9 12,268 2 / 2

Nested Loop (cost=5,969.33..10,075.18 rows=456 width=74) (actual time=79.626..189.754 rows=12,268 loops=2)

  • Join Filter: (s.registration_id = sr.id)
16. 34.187 140.820 ↓ 24.4 12,442 2 / 2

Parallel Hash Join (cost=5,969.04..9,907.43 rows=509 width=58) (actual time=79.213..140.820 rows=12,442 loops=2)

  • Hash Cond: ((spa.sample_registration_id = s.registration_id) AND (spa.analytical_parameter_id = sp.parameter_id))
17. 28.154 28.154 ↑ 1.2 86,494 2 / 2

Parallel Seq Scan on sample_parameter_assignments spa (cost=0.00..3,172.37 rows=101,737 width=16) (actual time=0.342..28.154 rows=86,494 loops=2)

18. 6.643 78.479 ↑ 1.3 12,442 2 / 2

Parallel Hash (cost=5,727.02..5,727.02 rows=16,135 width=42) (actual time=78.479..78.479 rows=12,442 loops=2)

  • Buckets: 32,768 Batches: 1 Memory Usage: 2,240kB
19. 5.273 71.836 ↑ 1.3 12,442 2 / 2

Nested Loop (cost=856.38..5,727.02 rows=16,135 width=42) (actual time=16.271..71.836 rows=12,442 loops=2)

20. 5.846 31.720 ↑ 1.3 3,872 2 / 2

Hash Join (cost=855.96..2,450.92 rows=4,885 width=30) (actual time=15.879..31.720 rows=3,872 loops=2)

  • Hash Cond: (s.work_order_id = w.id)
21. 10.254 10.254 ↑ 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.196..10.254 rows=25,124 loops=2)

22. 1.342 15.620 ↓ 1.0 5,455 2 / 2

Hash (cost=789.08..789.08 rows=5,350 width=22) (actual time=15.620..15.620 rows=5,455 loops=2)

  • Buckets: 8,192 Batches: 1 Memory Usage: 359kB
23. 14.278 14.278 ↓ 1.0 5,455 2 / 2

Index Scan using idx_received_at on work_orders w (cost=0.29..789.08 rows=5,350 width=22) (actual time=0.059..14.278 rows=5,455 loops=2)

  • Index Cond: ((received_at >= '2019-12-31 23:59:59'::timestamp without time zone) AND (received_at <= '2020-01-31 23:59:59'::timestamp without time zone))
  • Filter: active
  • Rows Removed by Filter: 55
24. 34.843 34.843 ↑ 2.0 3 7,743 / 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.007..0.009 rows=3 loops=7,743)

  • Index Cond: (sample_id = s.id)
25. 37.325 37.325 ↑ 1.0 1 24,883 / 2

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,883)

  • Index Cond: (id = spa.sample_registration_id)
  • Filter: active
  • Rows Removed by Filter: 0
26. 24.535 24.535 ↑ 1.0 1 24,535 / 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=24,535)

  • Index Cond: (id = spa.analytical_parameter_id)
27. 85.873 85.873 ↑ 2.5 2 24,535 / 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.006..0.007 rows=2 loops=24,535)

  • Index Cond: (sample_parameter_id = sp.id)
28. 43.804 43.804 ↑ 1.0 1 43,804 / 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=43,804)

  • Index Cond: (id = ar.definition_id)
29. 0.021 0.711 ↑ 1.0 82 2 / 2

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 19kB
30. 0.032 0.690 ↑ 1.0 82 2 / 2

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

  • Hash Cond: (fsd.functional_structure_id = fs.id)
31. 0.031 0.426 ↑ 1.0 82 2 / 2

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

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

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

33. 0.008 0.199 ↑ 1.0 9 2 / 2

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

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

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

35. 0.018 0.232 ↑ 1.0 82 2 / 2

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

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

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

37. 0.071 0.387 ↑ 1.0 339 2 / 2

Hash (cost=12.39..12.39 rows=339 width=25) (actual time=0.386..0.387 rows=339 loops=2)

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

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

39. 0.080 0.508 ↑ 1.0 390 2 / 2

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

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

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

41. 43.804 43.804 ↑ 1.0 1 43,804 / 2

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

  • Index Cond: (id = w.patient_id)
42. 1.383 3.505 ↑ 1.0 7,248 2 / 2

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

  • Buckets: 8,192 Batches: 1 Memory Usage: 376kB
43. 2.122 2.122 ↑ 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.611..2.122 rows=7,248 loops=2)

44. 0.001 0.805 ↓ 0.0 0 2 / 2

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
45. 0.001 0.804 ↓ 0.0 0 2 / 2

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

46. 0.803 0.803 ↓ 0.0 0 2 / 2

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

  • Filter: ((report_note_definition_id = 3) AND ((commentable_type)::text = 'SampleRegistration'::text))
  • Rows Removed by Filter: 2,818
47. 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)
48. 43.818 43.818 ↓ 0.0 0 43,818 / 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.002..0.002 rows=0 loops=43,818)

  • Index Cond: (id = ar.value_id)
49. 0.000 0.000 ↓ 0.0 0 43,818 / 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=43,818)

  • Index Cond: (id = trv.text_result_pre_defined_text_id)
50. 0.152 1.755 ↑ 1.0 718 2 / 2

Hash (cost=172.59..172.59 rows=718 width=48) (actual time=1.755..1.755 rows=718 loops=2)

  • Buckets: 1,024 Batches: 1 Memory Usage: 70kB
51. 1.603 1.603 ↑ 1.0 718 2 / 2

Seq Scan on pre_defined_texts pdticp (cost=0.00..172.59 rows=718 width=48) (actual time=0.185..1.603 rows=718 loops=2)

  • Filter: ((master_type)::text = 'comment'::text)
  • Rows Removed by Filter: 1,409
52. 0.290 0.839 ↑ 1.0 1,409 2 / 2

Hash (cost=172.59..172.59 rows=1,409 width=48) (actual time=0.839..0.839 rows=1,409 loops=2)

  • Buckets: 2,048 Batches: 1 Memory Usage: 126kB
53. 0.549 0.549 ↑ 1.0 1,409 2 / 2

Seq Scan on pre_defined_texts pdtip (cost=0.00..172.59 rows=1,409 width=48) (actual time=0.008..0.549 rows=1,409 loops=2)

  • Filter: ((master_type)::text = 'interpretation'::text)
  • Rows Removed by Filter: 718
54.          

SubPlan (for Gather)

55. 0.000 262.908 ↓ 0.0 0 43,818

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

56. 43.818 262.908 ↓ 0.0 0 43,818

Sort (cost=3.77..3.78 rows=1 width=8) (actual time=0.006..0.006 rows=0 loops=43,818)

  • Sort Key: report_parametro.created_at
  • Sort Method: quicksort Memory: 25kB
57. 219.090 219.090 ↓ 0.0 0 43,818

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.005..0.005 rows=0 loops=43,818)

  • 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
58. 0.000 219.090 ↓ 0.0 0 43,818

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

59. 43.818 219.090 ↓ 0.0 0 43,818

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

  • Sort Key: report_amostra.created_at
  • Sort Method: quicksort Memory: 25kB
60. 175.272 175.272 ↓ 0.0 0 43,818

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

  • 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
61. 0.000 136.416 ↓ 0.0 0 34,104

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

62. 34.104 136.416 ↓ 0.0 0 34,104

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

  • Sort Key: report_requisicao.created_at
  • Sort Method: quicksort Memory: 25kB
63. 102.312 102.312 ↓ 0.0 0 34,104

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,104)

  • 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
64. 43.818 350.544 ↑ 1.0 1 43,818

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

65. 43.818 306.726 ↑ 1.0 1 43,818

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

  • Sort Key: technical_parameters.lab_matrix_id, technical_parameters.functional_structure_id, technical_parameters.response_time_days
  • Sort Method: quicksort Memory: 25kB
66. 262.908 262.908 ↑ 1.0 1 43,818

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

  • 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: 309,621