explain.depesz.com

PostgreSQL's explain analyze made readable

Result: tv5W

Settings
# exclusive inclusive rows x rows loops node
1. 3.469 2,671.617 ↑ 1.0 37 1

Limit (cost=1,451,934.19..1,451,934.28 rows=37 width=2,012) (actual time=2,668.122..2,671.617 rows=37 loops=1)

  • Buffers: shared hit=2,302,205
2. 31.597 2,668.148 ↑ 3.4 37 1

Sort (cost=1,451,934.19..1,451,934.5 rows=125 width=2,012) (actual time=2,668.119..2,668.148 rows=37 loops=1)

  • Sort Key: (max(_patient_hcaps_hcap1.visit_start)) DESC NULLS LAST
  • Sort Method: top-N heapsort Memory: 93kB
  • Buffers: shared hit=2,302,205
3. 232.426 2,636.551 ↓ 144.6 18,073 1

GroupAggregate (cost=1,442,888.73..1,451,930.31 rows=125 width=2,012) (actual time=2,362.886..2,636.551 rows=18,073 loops=1)

  • Group Key: _pat_mi_hospital_reference_info1.__id__
  • Filter: ((count(_pat_mi_hospital_reference_info3.__id__) = 0) AND (count(_pat_mi_hospital_reference_info2.__id__) > 0))
  • Buffers: shared hit=2,302,205
4. 111.340 2,404.125 ↑ 1.6 47,404 1

Sort (cost=1,442,888.73..1,443,076.87 rows=75,258 width=1,390) (actual time=2,362.813..2,404.125 rows=47,404 loops=1)

  • Sort Key: _pat_mi_hospital_reference_info1.__id__
  • Sort Method: quicksort Memory: 25,969kB
  • Buffers: shared hit=2,302,205
5. 12.134 2,292.785 ↑ 1.6 47,404 1

Gather (cost=7,579.17..1,436,793 rows=75,258 width=1,390) (actual time=198.064..2,292.785 rows=47,404 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
  • Buffers: shared hit=2,302,205
6. 38.887 2,280.651 ↑ 2.0 15,801 3 / 3

Hash Join (cost=6,579.17..1,428,267.2 rows=31,358 width=1,390) (actual time=186.01..2,280.651 rows=15,801 loops=3)

  • Buffers: shared hit=2,302,205
7. 33.451 2,241.650 ↑ 2.0 15,801 3 / 3

Hash Join (cost=6,573.93..1,423,762.35 rows=31,358 width=1,402) (actual time=185.871..2,241.65 rows=15,801 loops=3)

  • Buffers: shared hit=2,302,193
8. 36.527 2,208.162 ↑ 2.0 15,801 3 / 3

Hash Join (cost=6,563.48..1,422,745.31 rows=31,358 width=898) (actual time=185.807..2,208.162 rows=15,801 loops=3)

  • Buffers: shared hit=2,302,190
9. 34.383 2,171.574 ↑ 2.0 15,801 3 / 3

Hash Join (cost=6,560.98..1,419,686.22 rows=31,358 width=878) (actual time=185.721..2,171.574 rows=15,801 loops=3)

  • Buffers: shared hit=2,302,184
10. 47.662 2,137.155 ↑ 2.0 15,801 3 / 3

Nested Loop (cost=6,550.53..1,418,709.75 rows=31,358 width=374) (actual time=185.615..2,137.155 rows=15,801 loops=3)

  • Buffers: shared hit=2,302,147
11. 29.588 2,001.407 ↑ 2.1 14,681 3 / 3

Hash Join (cost=6,550.1..1,401,585.77 rows=31,358 width=296) (actual time=185.578..2,001.407 rows=14,681 loops=3)

  • Buffers: shared hit=2,122,956
12. 30.380 1,947.321 ↑ 2.1 14,681 3 / 3

Hash Join (cost=4,740.17..1,399,693.51 rows=31,358 width=296) (actual time=160.749..1,947.321 rows=14,681 loops=3)

  • Buffers: shared hit=2,121,552
13. 46.999 1,776.935 ↑ 2.1 14,681 3 / 3

Nested Loop (cost=2,297.33..1,397,168.36 rows=31,358 width=296) (actual time=20.102..1,776.935 rows=14,681 loops=3)

  • Buffers: shared hit=2,119,557
14. 44.386 1,685.893 ↑ 2.1 14,681 3 / 3

Nested Loop (cost=2,296.77..1,166,563.7 rows=31,358 width=296) (actual time=20.053..1,685.893 rows=14,681 loops=3)

  • Buffers: shared hit=2,044,398
15. 33.102 1,597.486 ↑ 2.1 14,674 3 / 3

Nested Loop (cost=2,296.33..1,146,899.08 rows=31,358 width=296) (actual time=20.036..1,597.486 rows=14,674 loops=3)

  • Buffers: shared hit=1,978,918
16. 43.446 1,520.363 ↑ 2.1 14,674 3 / 3

Nested Loop (cost=2,295.9..1,127,672 rows=31,358 width=296) (actual time=20.007..1,520.363 rows=14,674 loops=3)

  • Buffers: shared hit=1,914,662
17. 54.112 1,432.896 ↑ 2.1 14,674 3 / 3

Nested Loop (cost=2,295.46..1,105,268.56 rows=31,358 width=296) (actual time=19.99..1,432.896 rows=14,674 loops=3)

  • Buffers: shared hit=1,849,350
18. 35.944 1,041.464 ↑ 2.8 11,244 3 / 3

Nested Loop (cost=2,294.18..937,438.26 rows=31,358 width=348) (actual time=19.924..1,041.464 rows=11,244 loops=3)

  • Buffers: shared hit=1,530,591
19. 37.944 960.544 ↑ 2.8 11,244 3 / 3

Nested Loop (cost=2,293.61..706,833.6 rows=31,358 width=348) (actual time=19.903..960.544 rows=11,244 loops=3)

  • Buffers: shared hit=1,379,386
20. 25.488 881.217 ↑ 3.0 10,346 3 / 3

Nested Loop (cost=2,293.18..687,168.98 rows=31,358 width=348) (actual time=19.879..881.217 rows=10,346 loops=3)

  • Buffers: shared hit=1,267,901
21. 36.642 824.692 ↑ 3.0 10,346 3 / 3

Nested Loop (cost=2,292.74..667,941.9 rows=31,358 width=348) (actual time=19.854..824.692 rows=10,346 loops=3)

  • Buffers: shared hit=1,156,658
22. 32.088 746.667 ↑ 3.0 10,346 3 / 3

Nested Loop (cost=2,292.3..650,042.87 rows=31,358 width=340) (actual time=19.834..746.667 rows=10,346 loops=3)

  • Buffers: shared hit=1,046,469
23. 36.318 662.851 ↑ 3.0 10,346 3 / 3

Nested Loop (cost=2,291.87..605,947.32 rows=31,358 width=288) (actual time=19.805..662.851 rows=10,346 loops=3)

  • Buffers: shared hit=922,267
24. 26.717 583.585 ↑ 1.6 8,590 3 / 3

Nested Loop (cost=2,291.43..596,855.94 rows=13,432 width=288) (actual time=19.776..583.585 rows=8,590 loops=3)

  • Buffers: shared hit=815,291
25. 24.148 505.330 ↑ 1.6 8,590 3 / 3

Nested Loop (cost=2,291..587,932.39 rows=13,432 width=206) (actual time=19.741..505.33 rows=8,590 loops=3)

  • Buffers: shared hit=712,194
26. 34.045 429.644 ↑ 1.6 8,590 3 / 3

Nested Loop (cost=2,290.57..581,326.85 rows=13,432 width=206) (actual time=19.709..429.644 rows=8,590 loops=3)

  • Buffers: shared hit=609,016
27. 72.180 339.659 ↑ 1.6 9,323 3 / 3

Nested Loop (cost=2,290.01..472,054.75 rows=14,859 width=151) (actual time=19.676..339.659 rows=9,323 loops=3)

  • Buffers: shared hit=468,969
28. 83.225 87.622 ↑ 1.2 35,971 3 / 3

Bitmap Heap Scan on _patient_hcap_medical_institution _patient_hcap_medical_institution1 (cost=2,289.57..198,919.8 rows=44,577 width=32) (actual time=19.407..87.622 rows=35,971 loops=3)

  • Heap Blocks: exact=12,029
  • Buffers: shared hit=36,873
29. 4.397 4.397 ↓ 1.0 107,997 1 / 3

Bitmap Index Scan on i_patient_hcap_medical_institution (cost=0..2,262.82 rows=106,985 width=0) (actual time=13.19..13.191 rows=107,997 loops=1)

  • Index Cond: (_patient_hcap_medical_institution1.__ref_id__ = '4503599644148803'::bigint)
  • Buffers: shared hit=353
30. 179.857 179.857 ↓ 0.0 0 107,914 / 3

Index Scan using p_patient_hcaps_hcap on _patient_hcaps_hcap _patient_hcaps_hcap1 (cost=0.43..6.13 rows=1 width=119) (actual time=0.005..0.005 rows=0 loops=107,914)

  • Index Cond: (_patient_hcaps_hcap1.__id__ = _patient_hcap_medical_institution1.__parent_id__)
  • Filter: ((_patient_hcaps_hcap1.visit_start)::date >= to_date('2020-01-01'::text, 'YYYY-MM-DD'::text))
  • Buffers: shared hit=432,096
31. 55.940 55.940 ↑ 1.0 1 27,970 / 3

Index Scan using _pat_mi_hospital_reference_info___parent_id___idx on _pat_mi_hospital_reference_info _pat_mi_hospital_reference_info1 (cost=0.56..7.34 rows=1 width=63) (actual time=0.005..0.006 rows=1 loops=27,970)

  • Index Cond: (_pat_mi_hospital_reference_info1.__parent_id__ = _patient_hcap_medical_institution1.__id__)
  • Filter: ((_pat_mi_hospital_reference_info1.is_order_ref1 = 0) OR (_pat_mi_hospital_reference_info1.is_order_ref1 IS NULL))
  • Buffers: shared hit=140,047
32. 51.538 51.538 ↑ 1.0 1 25,769 / 3

Index Scan using p_patient_hcaps on _patient_hcaps _patient_hcaps1 (cost=0.43..0.49 rows=1 width=16) (actual time=0.006..0.006 rows=1 loops=25,769)

  • Index Cond: (_patient_hcaps1.__id__ = _patient_hcaps_hcap1.__parent_id__)
  • Buffers: shared hit=103,178
33. 51.538 51.538 ↑ 1.0 1 25,769 / 3

Index Scan using p_patient on _patient _patient1 (cost=0.43..0.66 rows=1 width=90) (actual time=0.006..0.006 rows=1 loops=25,769)

  • Index Cond: (_patient1.__id__ = _patient_hcaps1.__parent_id__)
  • Buffers: shared hit=103,097
34. 42.948 42.948 ↑ 2.0 1 25,769 / 3

Index Scan using _patient_hcap_health_issue___parent_id___idx on _patient_hcap_health_issue _patient_hcap_health_issue1 (cost=0.44..0.65 rows=2 width=16) (actual time=0.004..0.005 rows=1 loops=25,769)

  • Index Cond: (_patient_hcap_health_issue1.__parent_id__ = _patient_hcaps_hcap1.__id__)
  • Buffers: shared hit=106,976
35. 51.728 51.728 ↑ 1.0 1 31,037 / 3

Index Scan using p_patient_health_issues_health_issue on _patient_health_issues_health_issue _patient_health_issues_health_issue1 (cost=0.44..1.41 rows=1 width=60) (actual time=0.005..0.005 rows=1 loops=31,037)

  • Index Cond: (_patient_hcap_health_issue1.__subobj_id__ = _patient_health_issues_health_issue1.__id__)
  • Filter: ((_patient_health_issues_health_issue1.outcome IS NULL) OR ((_patient_health_issues_health_issue1.outcome)::text = '0'::text) OR ((_patient_health_issues_health_issue1.outcome)::text = ''::text))
  • Buffers: shared hit=124,202
36. 41.383 41.383 ↑ 1.0 1 31,037 / 3

Index Scan using _patient_hcap_health_issue___subobj_id___idx on _patient_hcap_health_issue _patient_hcap_health_issue2 (cost=0.44..0.56 rows=1 width=16) (actual time=0.003..0.004 rows=1 loops=31,037)

  • Index Cond: (_patient_hcap_health_issue2.__subobj_id__ = _patient_health_issues_health_issue1.__id__)
  • Buffers: shared hit=110,189
37. 31.037 31.037 ↑ 1.0 1 31,037 / 3

Index Only Scan using p_patient_hcaps_hcap on _patient_hcaps_hcap _patient_hcaps_hcap2 (cost=0.43..0.61 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=31,037)

  • Index Cond: (_patient_hcaps_hcap2.__id__ = _patient_hcap_health_issue2.__parent_id__)
  • Heap Fetches: 26,045
  • Buffers: shared hit=111,243
38. 41.383 41.383 ↑ 1.0 1 31,037 / 3

Index Scan using _patient_hcap_medical_institution___parent_id___idx on _patient_hcap_medical_institution _patient_hcap_medical_institution2 (cost=0.44..0.62 rows=1 width=16) (actual time=0.003..0.004 rows=1 loops=31,037)

  • Index Cond: (_patient_hcaps_hcap2.__id__ = _patient_hcap_medical_institution2.__parent_id__)
  • Filter: (_patient_hcap_medical_institution2.__ref_id__ = '4503599644148803'::bigint)
  • Buffers: shared hit=111,485
39. 44.976 44.976 ↑ 1.0 1 33,732 / 3

Index Scan using _pat_mi_hospital_reference_info___parent_id___idx on _pat_mi_hospital_reference_info _pat_mi_hospital_reference_info2 (cost=0.56..7.34 rows=1 width=16) (actual time=0.003..0.004 rows=1 loops=33,732)

  • Index Cond: (_patient_hcap_medical_institution2.__id__ = _pat_mi_hospital_reference_info2.__parent_id__)
  • Filter: (_pat_mi_hospital_reference_info2.is_order_ref1 = 0)
  • Buffers: shared hit=151,205
40. 67.464 337.320 ↓ 0.0 0 33,732 / 3

Bitmap Heap Scan on _patient_health_issues_health_issue _patient_health_issues_health_issue2 (cost=1.28..5.34 rows=1 width=60) (actual time=0.028..0.03 rows=0 loops=33,732)

  • Filter: ((_patient_health_issues_health_issue2.outcome IS NOT NULL) AND ((_patient_health_issues_health_issue2.outcome)::text <> '0'::text) AND ((_patient_health_issues_health_issue2.outcome)::text <> ''::text))
  • Heap Blocks: exact=33,915
  • Buffers: shared hit=318,759
41. 33.732 269.856 ↓ 0.0 0 33,732 / 3

BitmapOr (cost=1.28..1.28 rows=3 width=0) (actual time=0.024..0.024 rows=0 loops=33,732)

  • Buffers: shared hit=213,627
42. 22.488 22.488 ↑ 1.0 1 33,732 / 3

Bitmap Index Scan on p_patient_health_issues_health_issue (cost=0..0.49 rows=1 width=0) (actual time=0.002..0.002 rows=1 loops=33,732)

  • Index Cond: (_patient_health_issues_health_issue2.__id__ = _patient_health_issues_health_issue1.__id__)
  • Buffers: shared hit=90,762
43. 213.636 213.636 ↓ 1.5 3 33,732 / 3

Bitmap Index Scan on _patient_health_issues_health_issue_hi_uuid_index (cost=0..0.79 rows=2 width=0) (actual time=0.019..0.019 rows=3 loops=33,732)

  • Index Cond: ((_patient_health_issues_health_issue2.hi_uuid)::text = (_patient_health_issues_health_issue1.hi_uuid)::text)
  • Buffers: shared hit=122,865
44. 44.021 44.021 ↓ 0.0 0 44,021 / 3

Index Scan using _patient_hcap_health_issue___subobj_id___idx on _patient_hcap_health_issue _patient_hcap_health_issue3 (cost=0.44..0.7 rows=1 width=16) (actual time=0.003..0.003 rows=0 loops=44,021)

  • Index Cond: (_patient_hcap_health_issue3.__subobj_id__ = _patient_health_issues_health_issue2.__id__)
  • Buffers: shared hit=65,312
45. 44.021 44.021 ↓ 0.0 0 44,021 / 3

Index Only Scan using p_patient_hcaps_hcap on _patient_hcaps_hcap _patient_hcaps_hcap3 (cost=0.43..0.61 rows=1 width=8) (actual time=0.003..0.003 rows=0 loops=44,021)

  • Index Cond: (_patient_hcaps_hcap3.__id__ = _patient_hcap_health_issue3.__parent_id__)
  • Heap Fetches: 3,385
  • Buffers: shared hit=64,256
46. 44.021 44.021 ↓ 0.0 0 44,021 / 3

Index Scan using _patient_hcap_medical_institution___parent_id___idx on _patient_hcap_medical_institution _patient_hcap_medical_institution3 (cost=0.44..0.62 rows=1 width=16) (actual time=0.003..0.003 rows=0 loops=44,021)

  • Index Cond: (_patient_hcaps_hcap3.__id__ = _patient_hcap_medical_institution3.__parent_id__)
  • Filter: (_patient_hcap_medical_institution3.__ref_id__ = '4503599644148803'::bigint)
  • Buffers: shared hit=65,480
47. 44.043 44.043 ↓ 0.0 0 44,043 / 3

Index Scan using _pat_mi_hospital_reference_info___parent_id___idx on _pat_mi_hospital_reference_info _pat_mi_hospital_reference_info3 (cost=0.56..7.34 rows=1 width=16) (actual time=0.003..0.003 rows=0 loops=44,043)

  • Index Cond: (_patient_hcap_medical_institution3.__id__ = _pat_mi_hospital_reference_info3.__parent_id__)
  • Filter: (_pat_mi_hospital_reference_info3.is_order_ref1 = 0)
  • Buffers: shared hit=75,159
48. 74.275 140.006 ↑ 1.0 78,888 3 / 3

Hash (cost=1,455.15..1,455.15 rows=79,015 width=16) (actual time=140.005..140.006 rows=78,888 loops=3)

  • Buffers: shared hit=1,995
49. 65.731 65.731 ↑ 1.0 78,888 3 / 3

Seq Scan on _medical_institution_rp_miw_med_ref_visits _medical_institution_rp_miw_med_ref_visits1 (cost=0..1,455.15 rows=79,015 width=16) (actual time=0.033..65.731 rows=78,888 loops=3)

  • Buffers: shared hit=1,995
50. 12.671 24.498 ↑ 1.8 12,859 3 / 3

Hash (cost=1,527.75..1,527.75 rows=22,575 width=8) (actual time=24.497..24.498 rows=12,859 loops=3)

  • Buffers: shared hit=1,302
51. 11.827 11.827 ↑ 1.8 12,859 3 / 3

Seq Scan on _medical_institution_health_worker_rp_miw_med _medical_institution_health_worker_rp_miw_med1 (cost=0..1,527.75 rows=22,575 width=8) (actual time=0.007..11.827 rows=12,859 loops=3)

  • Buffers: shared hit=1,302
52. 88.086 88.086 ↑ 1.0 1 44,043 / 3

Index Scan using _patient_addresses___parent_id___idx on _patient_addresses _patient_addresses1 (cost=0.43..0.54 rows=1 width=86) (actual time=0.005..0.006 rows=1 loops=44,043)

  • Index Cond: (_patient_addresses1.__parent_id__ = _patient1.__id__)
  • Buffers: shared hit=179,191
53. 0.013 0.036 ↑ 5.0 4 3 / 3

Hash (cost=10.2..10.2 rows=20 width=678) (actual time=0.034..0.036 rows=4 loops=3)

  • Buffers: shared hit=3
54. 0.023 0.023 ↑ 5.0 4 3 / 3

Seq Scan on _dic_gender _dic_gender1 (cost=0..10.2 rows=20 width=678) (actual time=0.019..0.023 rows=4 loops=3)

  • Buffers: shared hit=3
55. 0.028 0.061 ↑ 1.0 22 3 / 3

Hash (cost=2.22..2.22 rows=22 width=50) (actual time=0.06..0.061 rows=22 loops=3)

  • Buffers: shared hit=6
56. 0.033 0.033 ↑ 1.0 22 3 / 3

Seq Scan on _dic_visit_type _dic_visit_type1 (cost=0..2.22 rows=22 width=50) (actual time=0.01..0.033 rows=22 loops=3)

  • Buffers: shared hit=6
57. 0.019 0.037 ↑ 2.2 9 3 / 3

Hash (cost=10.2..10.2 rows=20 width=678) (actual time=0.036..0.037 rows=9 loops=3)

  • Buffers: shared hit=3
58. 0.018 0.018 ↑ 2.2 9 3 / 3

Seq Scan on _dic_visit_mode _dic_visit_mode1 (cost=0..10.2 rows=20 width=678) (actual time=0.009..0.018 rows=9 loops=3)

  • Buffers: shared hit=3
59. 0.053 0.114 ↑ 1.0 55 3 / 3

Hash (cost=4.55..4.55 rows=55 width=18) (actual time=0.113..0.114 rows=55 loops=3)

  • Buffers: shared hit=12
60. 0.061 0.061 ↑ 1.0 55 3 / 3

Seq Scan on _dic_marz _dic_marz1 (cost=0..4.55 rows=55 width=18) (actual time=0.008..0.061 rows=55 loops=3)

  • Buffers: shared hit=12
Planning time : 73.314 ms
Execution time : 2,672.436 ms