explain.depesz.com

PostgreSQL's explain analyze made readable

Result: rAIB

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

Gather (cost=20,000,008,414.31..20,000,035,869.91 rows=1,435 width=1,912) (actual time=135.996..1,167.478 rows=43,818 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
2. 7.605 500.203 ↓ 26.0 21,909 2 / 2

Hash Left Join (cost=20,000,007,414.31..20,000,014,846.26 rows=844 width=947) (actual time=102.695..500.203 rows=21,909 loops=2)

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

Hash Left Join (cost=20,000,007,203.49..20,000,014,633.22 rows=844 width=907) (actual time=101.608..491.528 rows=21,909 loops=2)

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

Nested Loop Left Join (cost=20,000,007,001.30..20,000,014,428.82 rows=844 width=867) (actual time=100.295..482.341 rows=21,909 loops=2)

5. 24.387 467.917 ↓ 26.0 21,909 2 / 2

Nested Loop Left Join (cost=20,000,007,001.02..20,000,014,175.86 rows=844 width=832) (actual time=100.264..467.917 rows=21,909 loops=2)

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

Hash Left Join (cost=20,000,007,000.61..20,000,013,794.35 rows=844 width=838) (actual time=100.227..421.621 rows=21,909 loops=2)

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

Hash Left Join (cost=10,000,006,760.41..10,000,013,547.81 rows=844 width=798) (actual time=99.383..413.364 rows=21,909 loops=2)

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

Nested Loop Left Join (cost=6,511.21..13,281.72 rows=844 width=794) (actual time=96.565..400.178 rows=21,902 loops=2)

9. 9.638 352.860 ↓ 26.0 21,902 2 / 2

Hash Join (cost=6,510.92..12,895.54 rows=844 width=792) (actual time=96.553..352.860 rows=21,902 loops=2)

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

Hash Join (cost=6,479.15..12,861.54 rows=844 width=778) (actual time=96.046..342.800 rows=21,902 loops=2)

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

Hash Join (cost=6,457.59..12,837.73 rows=844 width=757) (actual time=95.762..332.945 rows=21,902 loops=2)

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

Nested Loop (cost=6,440.89..12,809.43 rows=844 width=190) (actual time=95.509..319.759 rows=21,902 loops=2)

13. 14.091 264.737 ↓ 26.0 21,902 2 / 2

Nested Loop (cost=6,440.61..12,555.63 rows=844 width=148) (actual time=95.487..264.737 rows=21,902 loops=2)

14. 10.333 201.576 ↓ 26.9 12,268 2 / 2

Nested Loop (cost=6,440.19..12,274.98 rows=456 width=130) (actual time=95.449..201.576 rows=12,268 loops=2)

15. 15.396 166.708 ↓ 26.9 12,268 2 / 2

Nested Loop (cost=6,439.91..12,138.88 rows=456 width=74) (actual time=95.421..166.708 rows=12,268 loops=2)

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

Parallel Hash Join (cost=6,439.62..11,971.12 rows=509 width=58) (actual time=95.380..126.429 rows=12,442 loops=2)

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

Parallel Index Scan Backward using index_parameter_assignments_on_registration_id on sample_parameter_assignments spa (cost=0.42..4,765.91 rows=101,737 width=16) (actual time=0.076..43.793 rows=86,494 loops=2)

18. 5.970 52.880 ↑ 1.3 12,442 2 / 2

Parallel Hash (cost=6,197.25..6,197.25 rows=16,130 width=42) (actual time=52.880..52.880 rows=12,442 loops=2)

  • Buckets: 32,768 Batches: 1 Memory Usage: 2,208kB
19. 5.996 46.910 ↑ 1.3 12,442 2 / 2

Nested Loop (cost=856.67..6,197.25 rows=16,130 width=42) (actual time=20.194..46.910 rows=12,442 loops=2)

20. 5.385 29.299 ↑ 1.3 3,872 2 / 2

Hash Join (cost=856.25..2,921.25 rows=4,885 width=30) (actual time=20.163..29.299 rows=3,872 loops=2)

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

Parallel Index Scan using samples_pkey on samples s (cost=0.29..1,987.33 rows=29,699 width=12) (actual time=0.224..13.577 rows=25,124 loops=2)

22. 1.279 10.337 ↓ 1.0 5,455 2 / 2

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

  • Buckets: 8,192 Batches: 1 Memory Usage: 359kB
23. 9.058 9.058 ↓ 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.061..9.058 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. 11.614 11.614 ↑ 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.002..0.003 rows=3 loops=7,743)

  • Index Cond: (sample_id = s.id)
25. 24.883 24.883 ↑ 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.002..0.002 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. 49.070 49.070 ↑ 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.003..0.004 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.023 0.241 ↑ 1.0 82 2 / 2

Hash (cost=15.67..15.67 rows=82 width=575) (actual time=0.241..0.241 rows=82 loops=2)

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

Hash Join (cost=3.97..15.67 rows=82 width=575) (actual time=0.126..0.218 rows=82 loops=2)

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

Merge Join (cost=0.29..11.67 rows=82 width=63) (actual time=0.066..0.140 rows=82 loops=2)

  • Merge Cond: (fsd.functional_structure_id = fs.id)
32. 0.074 0.074 ↑ 1.0 82 2 / 2

Index Only Scan using pk_functional_structures_departments on functional_structures_departments fsd (cost=0.14..4.67 rows=82 width=8) (actual time=0.052..0.074 rows=82 loops=2)

  • Heap Fetches: 164
33. 0.038 0.038 ↑ 1.0 82 2 / 2

Index Scan using functional_structures_pkey on functional_structures fs (cost=0.14..5.77 rows=82 width=55) (actual time=0.011..0.038 rows=82 loops=2)

34. 0.007 0.048 ↑ 1.0 9 2 / 2

Hash (cost=3.57..3.57 rows=9 width=520) (actual time=0.048..0.048 rows=9 loops=2)

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

Index Scan using departments_pkey on departments d (cost=0.14..3.57 rows=9 width=520) (actual time=0.038..0.041 rows=9 loops=2)

36. 0.067 0.269 ↑ 1.0 339 2 / 2

Hash (cost=17.33..17.33 rows=339 width=25) (actual time=0.269..0.269 rows=339 loops=2)

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

Index Scan using lab_matrices_pkey on lab_matrices lm (cost=0.15..17.33 rows=339 width=25) (actual time=0.018..0.202 rows=339 loops=2)

38. 0.084 0.422 ↑ 1.0 390 2 / 2

Hash (cost=26.89..26.89 rows=390 width=22) (actual time=0.422..0.422 rows=390 loops=2)

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

Index Scan using users_pkey on users u (cost=0.27..26.89 rows=390 width=22) (actual time=0.027..0.338 rows=390 loops=2)

40. 21.902 21.902 ↑ 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.001..0.001 rows=1 loops=43,804)

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

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

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

Seq Scan on responsability_term_lines rtl (cost=10,000,000,000.00..10,000,000,140.48 rows=7,248 width=12) (actual time=0.351..1.488 rows=7,248 loops=2)

43. 0.000 0.813 ↓ 0.0 0 2 / 2

Hash (cost=10,000,000,239.17..10,000,000,239.17 rows=82 width=48) (actual time=0.813..0.813 rows=0 loops=2)

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

Nested Loop Left Join (cost=10,000,000,000.28..10,000,000,239.17 rows=82 width=48) (actual time=0.813..0.813 rows=0 loops=2)

45. 0.812 0.812 ↓ 0.0 0 2 / 2

Seq Scan on report_notes ica (cost=10,000,000,000.00..10,000,000,134.27 rows=82 width=8) (actual time=0.812..0.812 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. 21.909 21.909 ↓ 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.001..0.001 rows=0 loops=43,818)

  • Index Cond: (id = ar.value_id)
48. 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)
49. 0.155 1.297 ↑ 1.0 718 2 / 2

Hash (cost=193.21..193.21 rows=718 width=48) (actual time=1.297..1.297 rows=718 loops=2)

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

Index Scan using pre_defined_texts_pkey on pre_defined_texts pdticp (cost=0.28..193.21 rows=718 width=48) (actual time=0.043..1.142 rows=718 loops=2)

  • Filter: ((master_type)::text = 'comment'::text)
  • Rows Removed by Filter: 1,409
51. 0.278 1.070 ↑ 1.0 1,409 2 / 2

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

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

Index Scan using pre_defined_texts_pkey on pre_defined_texts pdtip (cost=0.28..193.21 rows=1,409 width=48) (actual time=0.039..0.792 rows=1,409 loops=2)

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

SubPlan (for Gather)

54. 0.000 219.090 ↓ 0.0 0 43,818

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

55. 43.818 219.090 ↓ 0.0 0 43,818

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

  • Sort Key: report_parametro.created_at
  • Sort Method: quicksort Memory: 25kB
56. 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_parametro (cost=0.42..3.76 rows=1 width=8) (actual time=0.004..0.004 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
57. 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)

58. 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
59. 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
60. 34.104 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)

61. 0.000 102.312 ↓ 0.0 0 34,104

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

  • Sort Key: report_requisicao.created_at
  • Sort Method: quicksort Memory: 25kB
62. 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.002..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
63. 0.000 306.726 ↑ 1.0 1 43,818

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

64. 87.636 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
65. 219.090 219.090 ↑ 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.005 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