explain.depesz.com

PostgreSQL's explain analyze made readable

Result: TaWj

Settings
# exclusive inclusive rows x rows loops node
1. 0.013 4.069 ↓ 36.0 36 1

Unique (cost=269.64..269.72 rows=1 width=829) (actual time=4.023..4.069 rows=36 loops=1)

2.          

Initplan (for Unique)

3. 0.019 0.019 ↑ 1.0 1 1

Index Scan using xpktsyitbmed on tsyitbmed (cost=0.28..8.31 rows=1 width=32) (actual time=0.018..0.019 rows=1 loops=1)

  • Index Cond: ((code)::text = 'NLS_GESCHLECHT_MANNLICH'::text)
4. 0.009 0.009 ↑ 1.0 1 1

Index Scan using xpktsyitbmed on tsyitbmed tsyitbmed_1 (cost=0.28..8.31 rows=1 width=32) (actual time=0.008..0.009 rows=1 loops=1)

  • Index Cond: ((code)::text = 'NLS_GESCHLECHT_WEIBLICH'::text)
5. 0.000 0.000 ↓ 0.0 0

Index Scan using xpktsyitbmed on tsyitbmed tsyitbmed_2 (cost=0.28..8.31 rows=1 width=32) (never executed)

  • Index Cond: ((code)::text = 'NLS_GESCHLECHT_UNBEKANNT'::text)
6. 0.000 0.000 ↓ 0.0 0

Index Scan using xpktsyitbmed on tsyitbmed tsyitbmed_3 (cost=0.28..8.31 rows=1 width=32) (never executed)

  • Index Cond: ((code)::text = 'NLS_GESCHLECHT_UNBESTIMMT'::text)
7. 0.077 4.028 ↓ 36.0 36 1

Sort (cost=236.38..236.38 rows=1 width=829) (actual time=4.022..4.028 rows=36 loops=1)

  • Sort Key: tblfall.fallid, (DECODE( tblpatient_1.geschlecht , $0 , '@ICON(whitebgman_18x18.gif)'::character varying , $1 , '@ICON(whitebgwomen_18x18.gif)'::character varying , $2 , '@ICON(whitebgwomen_18x18.gif)'::character varying , $3 , '@ICON(whitebgwomen_18x18.gif)'::character varying , tblpatient_1.geschlecht )), tblfall.fallnr, tblpatient_1.patientennr, tblpatient_1.name, tblpatient_1.vorname, (tmp_fgetageinwords(tblpatient_1.geburtsdatum, tblpatient_1.todesdatum, 'J'::character varying, tblpatient_1.ispatientdead)), ((SubPlan 5)), ((SubPlan 6)), (DECODE( tblpatient_1.hatalarmkennzeich , 'J'::character varying , '@ICON(warning.gif)'::character varying , NULL::character varying )), (DECODE( (sum(CASE WHEN (tblcolorindicator.stripid = '22'::numeric) THEN tblcolorindicator.status ELSE NULL::numeric END)) , (NULL::text)::numeric , '@ICON(CIB_red_not.gif)'::character varying , (0)::numeric , '@ICON(CIB_red_not.gif)'::character varying , (1)::numeric , '@ICON(CIB_red_half.gif)'::character varying , (2)::numeric , '@ICON(CIB_red_full.gif)'::character varying , ((sum(CASE WHEN (tblcolorindicator.stripid = '22'::numeric) THEN tblcolorindicator.status ELSE NULL::numeric END)))::character varying )), (DECODE( (sum(CASE WHEN (tblcolorindicator.stripid = '23'::numeric) THEN tblcolorindicator.status ELSE NULL::numeric END)) , (NULL::text)::numeric , '@ICON(CIB_green_not.gif)'::character varying , (0)::numeric , '@ICON(CIB_green_not.gif)'::character varying , (1)::numeric , '@ICON(CIB_green_half.gif)'::character varying , (2)::numeric , '@ICON(CIB_green_full.gif)'::character varying , ((sum(CASE WHEN (tblcolorindicator.stripid = '23'::numeric) THEN tblcolorindicator.status ELSE NULL::numeric END)))::character varying )), (DECODE( (sum(CASE WHEN (tblcolorindicator.stripid = '24'::numeric) THEN tblcolorindicator.status ELSE NULL::numeric END)) , (NULL::text)::numeric , '@ICON(CIB_yellow_not.gif)'::character varying , (0)::numeric , '@ICON(CIB_yellow_not.gif)'::character varying , (1)::numeric , '@ICON(CIB_yellow_half.gif)'::character varying , (2)::numeric , '@ICON(CIB_yellow_full.gif)'::character varying , ((sum(CASE WHEN (tblcolorindicator.stripid = '24'::numeric) THEN tblcolorindicator.status ELSE NULL::numeric END)))::character varying )), (DECODE( ((SubPlan 7))::character varying , 'J'::character varying , '@ICON(collapsedparentnode.gif)'::character varying , NULL::character varying )), (DECODE( ((SubPlan 8))::character varying , 'J'::character varying , '@ICON(red_diamonds.gif)'::character varying , NULL::character varying )), (DECODE( (sum(CASE WHEN (tblcolorindicator.stripid = '1'::numeric) THEN tblcolorindicator.status ELSE NULL::numeric END)) , (NULL::text)::numeric , '@ICON(CIB_blue_not.gif)'::character varying , (0)::numeric , '@ICON(CIB_blue_not.gif)'::character varying , (1)::numeric , '@ICON(CIB_blue_half.gif)'::character varying , (2)::numeric , '@ICON(CIB_blue_full.gif)'::character varying , ((sum(CASE WHEN (tblcolorindicator.stripid = '1'::numeric) THEN tblcolorindicator.status ELSE NULL::numeric END)))::character varying )), (DECODE( (sum(CASE WHEN (tblcolorindicator.stripid = '2'::numeric) THEN tblcolorindicator.status ELSE NULL::numeric END)) , (NULL::text)::numeric , '@ICON(CIB_magenta_not.gif)'::character varying , (0)::numeric , '@ICON(CIB_magenta_not.gif)'::character varying , (1)::numeric , '@ICON(CIB_magenta_half.gif)'::character varying , (2)::numeric , '@ICON(CIB_magenta_full.gif)'::character varying , ((sum(CASE WHEN (tblcolorindicator.stripid = '2'::numeric) THEN tblcolorindicator.status ELSE NULL::numeric END)))::character varying )), (DECODE( (sum(CASE WHEN (tblcolorindicator.stripid = '3'::numeric) THEN tblcolorindicator.status ELSE NULL::numeric END)) , (NULL::text)::numeric , '@ICON(CIB_green_not.gif)'::character varying , (0)::numeric , '@ICON(CIB_green_not.gif)'::character varying , (1)::numeric , '@ICON(CIB_green_half.gif)'::character varying , (2)::numeric , '@ICON(CIB_green_full.gif)'::character varying , ((sum(CASE WHEN (tblcolorindicator.stripid = '3'::numeric) THEN tblcolorindicator.status ELSE NULL::numeric END)))::character varying )), (DECODE( (sum(CASE WHEN (tblcolorindicator.stripid = '4'::numeric) THEN tblcolorindicator.status ELSE NULL::numeric END)) , (NULL::text)::numeric , '@ICON(CIB_yellow_not.gif)'::character varying , (0)::numeric , '@ICON(CIB_yellow_not.gif)'::character varying , (1)::numeric , '@ICON(CIB_yellow_half.gif)'::character varying , (2)::numeric , '@ICON(CIB_yellow_full.gif)'::character varying , ((sum(CASE WHEN (tblcolorindicator.stripid = '4'::numeric) THEN tblcolorindicator.status ELSE NULL::numeric END)))::character varying )), (DECODE( (sum(CASE WHEN (tblcolorindicator.stripid = '5'::numeric) THEN tblcolorindicator.status ELSE NULL::numeric END)) , (NULL::text)::numeric , '@ICON(CIB_black_not.gif)'::character varying , (0)::numeric , '@ICON(CIB_black_not.gif)'::character varying , (1)::numeric , '@ICON(CIB_black_half.gif)'::character varying , (2)::numeric , '@ICON(CIB_black_full.gif)'::character varying , ((sum(CASE WHEN (tblcolorindicator.stripid = '5'::numeric) THEN tblcolorindicator.status ELSE NULL::numeric END)))::character varying )), (DECODE( (sum(CASE WHEN (tblcolorindicator.stripid = '6'::numeric) THEN tblcolorindicator.status ELSE NULL::numeric END)) , (NULL::text)::numeric , '@ICON(CIB_red_not.gif)'::character varying , (0)::numeric , '@ICON(CIB_red_not.gif)'::character varying , (1)::numeric , '@ICON(CIB_red_half.gif)'::character varying , (2)::numeric , '@ICON(CIB_red_full.gif)'::character varying , ((sum(CASE WHEN (tblcolorindicator.stripid = '6'::numeric) THEN tblcolorindicator.status ELSE NULL::numeric END)))::character varying )), (DECODE( (sum(CASE WHEN (tblcolorindicator.stripid = '7'::numeric) THEN tblcolorindicator.status ELSE NULL::numeric END)) , (NULL::text)::numeric , '@ICON(CIB_magenta_not.gif)'::character varying , (0)::numeric , '@ICON(CIB_magenta_not.gif)'::character varying , (1)::numeric , '@ICON(CIB_magenta_half.gif)'::character varying , (2)::numeric , '@ICON(CIB_magenta_full.gif)'::character varying , ((sum(CASE WHEN (tblcolorindicator.stripid = '7'::numeric) THEN tblcolorindicator.status ELSE NULL::numeric END)))::character varying )), (DECODE( (sum(CASE WHEN (tblcolorindicator.stripid = '8'::numeric) THEN tblcolorindicator.status ELSE NULL::numeric END)) , (NULL::text)::numeric , '@ICON(CIB_yellow_not.gif)'::character varying , (0)::numeric , '@ICON(CIB_yellow_not.gif)'::character varying , (1)::numeric , '@ICON(CIB_yellow_half.gif)'::character varying , (2)::numeric , '@ICON(CIB_yellow_full.gif)'::character varying , ((sum(CASE WHEN (tblcolorindicator.stripid = '8'::numeric) THEN tblcolorindicator.status ELSE NULL::numeric END)))::character varying )), (DECODE( (sum(CASE WHEN (tblcolorindicator.stripid = '9'::numeric) THEN tblcolorindicator.status ELSE NULL::numeric END)) , (NULL::text)::numeric , '@ICON(CIB_blue_not.gif)'::character varying , (0)::numeric , '@ICON(CIB_blue_not.gif)'::character varying , (1)::numeric , '@ICON(CIB_blue_half.gif)'::character varying , (2)::numeric , '@ICON(CIB_blue_full.gif)'::character varying , ((sum(CASE WHEN (tblcolorindicator.stripid = '9'::numeric) THEN tblcolorindicator.status ELSE NULL::numeric END)))::character varying )), (DECODE( (sum(CASE WHEN (tblcolorindicator.stripid = '10'::numeric) THEN tblcolorindicator.status ELSE NULL::numeric END)) , (NULL::text)::numeric , '@ICON(CIB_magenta_not.gif)'::character varying , (0)::numeric , '@ICON(CIB_magenta_not.gif)'::character varying , (1)::numeric , '@ICON(CIB_magenta_half.gif)'::character varying , (2)::numeric , '@ICON(CIB_magenta_full.gif)'::character varying , ((sum(CASE WHEN (tblcolorindicator.stripid = '10'::numeric) THEN tblcolorindicator.status ELSE NULL::numeric END)))::character varying )), (DECODE( (sum(CASE WHEN (tblcolorindicator.stripid = '11'::numeric) THEN tblcolorindicator.status ELSE NULL::numeric END)) , (NULL::text)::numeric , '@ICON(CIB_cornflowerblue_not.gif)'::character varying , (0)::numeric , '@ICON(CIB_cornflowerblue_not.gif)'::character varying , (1)::numeric , '@ICON(CIB_cornflowerblue_half.gif)'::character varying , (2)::numeric , '@ICON(CIB_cornflowerblue_full.gif)'::character varying , ((sum(CASE WHEN (tblcolorindicator.stripid = '11'::numeric) THEN tblcolorindicator.status ELSE NULL::numeric END)))::character varying )), (DECODE( (sum(CASE WHEN (tblcolorindicator.stripid = '12'::numeric) THEN tblcolorindicator.status ELSE NULL::numeric END)) , (NULL::text)::numeric , '@ICON(CIB_red_not.gif)'::character varying , (0)::numeric , '@ICON(CIB_red_not.gif)'::character varying , (1)::numeric , '@ICON(CIB_red_half.gif)'::character varying , (2)::numeric , '@ICON(CIB_red_full.gif)'::character varying , ((sum(CASE WHEN (tblcolorindicator.stripid = '12'::numeric) THEN tblcolorindicator.status ELSE NULL::numeric END)))::character varying )), (DECODE( (sum(CASE WHEN (tblcolorindicator.stripid = '13'::numeric) THEN tblcolorindicator.status ELSE NULL::numeric END)) , (NULL::text)::numeric , '@ICON(CIB_coral_not.gif)'::character varying , (0)::numeric , '@ICON(CIB_coral_not.gif)'::character varying , (1)::numeric , '@ICON(CIB_coral_half.gif)'::character varying , (2)::numeric , '@ICON(CIB_coral_full.gif)'::character varying , ((sum(CASE WHEN (tblcolorindicator.stripid = '13'::numeric) THEN tblcolorindicator.status ELSE NULL::numeric END)))::character varying )), (DECODE( (sum(CASE WHEN (tblcolorindicator.stripid = '14'::numeric) THEN tblcolorindicator.status ELSE NULL::numeric END)) , (NULL::text)::numeric , '@ICON(CIB_green_not.gif)'::character varying , (0)::numeric , '@ICON(CIB_green_not.gif)'::character varying , (1)::numeric , '@ICON(CIB_green_half.gif)'::character varying , (2)::numeric , '@ICON(CIB_green_full.gif)'::character varying , ((sum(CASE WHEN (tblcolorindicator.stripid = '14'::numeric) THEN tblcolorindicator.status ELSE NULL::numeric END)))::character varying )), tblpatient_1.geburtsdatum, (DECODE( floor(((nvl((0)::numeric, (0)::numeric) + (1)::numeric) / (nvl(tblpatient.istvip, (0)::numeric) + (1)::numeric))) , (0)::numeric , 1 , 0 ))
  • Sort Method: quicksort Memory: 61kB
8. 2.282 3.951 ↓ 36.0 36 1

Nested Loop (cost=18.36..236.37 rows=1 width=829) (actual time=0.659..3.951 rows=36 loops=1)

9. 0.033 1.093 ↓ 36.0 36 1

Nested Loop Left Join (cost=17.94..193.93 rows=1 width=620) (actual time=0.340..1.093 rows=36 loops=1)

  • Join Filter: ((tblfall_1.patientenid = tblcolorindicator.patientenid) AND (tblfall_1.fallid = tblcolorindicator.caseid))
  • Rows Removed by Join Filter: 143
10. 0.052 0.484 ↓ 36.0 36 1

Nested Loop (cost=1.27..175.34 rows=1 width=82) (actual time=0.054..0.484 rows=36 loops=1)

11. 0.008 0.288 ↓ 36.0 36 1

Nested Loop (cost=0.85..174.81 rows=1 width=31) (actual time=0.039..0.288 rows=36 loops=1)

12. 0.172 0.172 ↓ 36.0 36 1

Index Scan using xpkfall on tblfall (cost=0.42..166.35 rows=1 width=19) (actual time=0.028..0.172 rows=36 loops=1)

  • Index Cond: ((fallid > '0'::numeric) AND (fallid = ANY ('{575666,575690,575693,575694,575697,575698,575699,575700,575860,576048,576627,576631,576633,576634,576636,576642,576643,576644,576721,576722,576724,576725,576726,576727,576728,577017,577018,577019,577020,577095,577096,577097,577098,577099,577100,579400}'::numeric[])))
  • Filter: ((stornierer IS NULL) AND ((einrichtung)::text = 'CARE'::text))
13. 0.108 0.108 ↑ 1.0 1 36

Index Scan using xpkfall on tblfall tblfall_1 (cost=0.42..8.46 rows=1 width=12) (actual time=0.003..0.003 rows=1 loops=36)

  • Index Cond: (fallid = tblfall.fallid)
14. 0.144 0.144 ↑ 1.0 1 36

Index Scan using xpkpatient2 on tblpatient tblpatient_1 (cost=0.42..0.52 rows=1 width=57) (actual time=0.004..0.004 rows=1 loops=36)

  • Index Cond: (patientenid = tblfall_1.patientenid)
15. 0.448 0.576 ↑ 3.5 4 36

HashAggregate (cost=16.67..17.69 rows=14 width=565) (actual time=0.010..0.016 rows=4 loops=36)

  • Group Key: tblcolorindicator.patientenid, tblcolorindicator.caseid, tblcolorindicator.ward
16. 0.128 0.128 ↑ 1.0 68 1

Seq Scan on tblcolorindicator (cost=0.00..10.38 rows=68 width=29) (actual time=0.014..0.128 rows=68 loops=1)

  • Filter: (stripid = ANY ('{1,2,3,4,5,6,7,8,9,10,11,12,13,14,22,23,24}'::numeric[]))
  • Rows Removed by Filter: 76
17. 0.108 0.108 ↑ 1.0 1 36

Index Scan using xpkpatient2 on tblpatient (cost=0.42..8.46 rows=1 width=9) (actual time=0.003..0.003 rows=1 loops=36)

  • Index Cond: (patientenid = tblfall.patientenid)
18.          

SubPlan (for Nested Loop)

19. 0.036 0.144 ↑ 1.0 1 36

Aggregate (cost=8.32..8.35 rows=1 width=32) (actual time=0.004..0.004 rows=1 loops=36)

20. 0.108 0.108 ↑ 1.0 1 36

Index Scan using xie1qckaktfallid on qckaktaufenthort (cost=0.28..8.32 rows=1 width=10) (actual time=0.003..0.003 rows=1 loops=36)

  • Index Cond: (fallid = tblfall_1.fallid)
21. 0.036 0.108 ↑ 1.0 1 36

Aggregate (cost=8.32..8.35 rows=1 width=32) (actual time=0.003..0.003 rows=1 loops=36)

22. 0.072 0.072 ↑ 1.0 1 36

Index Scan using xie1qckaktfallid on qckaktaufenthort qckaktaufenthort_1 (cost=0.28..8.32 rows=1 width=5) (actual time=0.002..0.002 rows=1 loops=36)

  • Index Cond: (fallid = tblfall_1.fallid)
23. 0.036 0.108 ↑ 1.0 1 36

Aggregate (cost=8.32..8.35 rows=1 width=32) (actual time=0.003..0.003 rows=1 loops=36)

24. 0.072 0.072 ↑ 1.0 1 36

Index Scan using xie1qckaktfallid on qckaktaufenthort qckaktaufenthort_2 (cost=0.28..8.32 rows=1 width=2) (actual time=0.002..0.002 rows=1 loops=36)

  • Index Cond: (fallid = tblfall_1.fallid)
25. 0.036 0.108 ↑ 1.0 1 36

Aggregate (cost=8.32..8.35 rows=1 width=32) (actual time=0.003..0.003 rows=1 loops=36)

26. 0.072 0.072 ↑ 1.0 1 36

Index Scan using xie1qckaktfallid on qckaktaufenthort qckaktaufenthort_3 (cost=0.28..8.32 rows=1 width=2) (actual time=0.002..0.002 rows=1 loops=36)

  • Index Cond: (fallid = tblfall_1.fallid)
Planning time : 3.318 ms
Execution time : 4.463 ms