explain.depesz.com

PostgreSQL's explain analyze made readable

Result: NTRr

Settings
# exclusive inclusive rows x rows loops node
1. 0.166 36,063.522 ↓ 36.0 36 1

Unique (cost=28,958,845.24..28,958,845.32 rows=1 width=829) (actual time=36,063.260..36,063.522 rows=36 loops=1)

  • Functions: 327
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
  • Timing: Generation 58.714 ms, Inlining 22.411 ms, Optimization 2087.580 ms, Emission 1739.087 ms, Total 3907.791 ms
2. 0.253 36,063.356 ↓ 36.0 36 1

Sort (cost=28,958,845.24..28,958,845.25 rows=1 width=829) (actual time=36,063.244..36,063.356 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, (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( ivtblcolorindicator.status22 , (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 , (ivtblcolorindicator.status22)::character varying )), (DECODE( ivtblcolorindicator.status23 , (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 , (ivtblcolorindicator.status23)::character varying )), (DECODE( ivtblcolorindicator.status24 , (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 , (ivtblcolorindicator.status24)::character varying )), (DECODE( ((SubPlan 8))::character varying , 'J'::character varying , '@ICON(collapsedparentnode.gif)'::character varying , NULL::character varying )), (DECODE( ((SubPlan 7))::character varying , 'J'::character varying , '@ICON(red_diamonds.gif)'::character varying , NULL::character varying )), (DECODE( ivtblcolorindicator.status1 , (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 , (ivtblcolorindicator.status1)::character varying )), (DECODE( ivtblcolorindicator.status2 , (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 , (ivtblcolorindicator.status2)::character varying )), (DECODE( ivtblcolorindicator.status3 , (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 , (ivtblcolorindicator.status3)::character varying )), (DECODE( ivtblcolorindicator.status4 , (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 , (ivtblcolorindicator.status4)::character varying )), (DECODE( ivtblcolorindicator.status5 , (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 , (ivtblcolorindicator.status5)::character varying )), (DECODE( ivtblcolorindicator.status6 , (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 , (ivtblcolorindicator.status6)::character varying )), (DECODE( ivtblcolorindicator.status7 , (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 , (ivtblcolorindicator.status7)::character varying )), (DECODE( ivtblcolorindicator.status8 , (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 , (ivtblcolorindicator.status8)::character varying )), (DECODE( ivtblcolorindicator.status9 , (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 , (ivtblcolorindicator.status9)::character varying )), (DECODE( ivtblcolorindicator.status10 , (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 , (ivtblcolorindicator.status10)::character varying )), (DECODE( ivtblcolorindicator.status11 , (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 , (ivtblcolorindicator.status11)::character varying )), (DECODE( ivtblcolorindicator.status12 , (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 , (ivtblcolorindicator.status12)::character varying )), (DECODE( ivtblcolorindicator.status13 , (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 , (ivtblcolorindicator.status13)::character varying )), (DECODE( ivtblcolorindicator.status14 , (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 , (ivtblcolorindicator.status14)::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
3. 141.565 36,063.103 ↓ 36.0 36 1

Hash Join (cost=9,850,362.60..28,958,845.23 rows=1 width=829) (actual time=8,671.749..36,063.103 rows=36 loops=1)

  • Hash Cond: (tblfall_1.fallid = tblfall.fallid)
4. 24,989.242 32,069.645 ↑ 1.0 555,236 1

Result (cost=9,850,187.59..28,939,709.85 rows=561,787 width=812) (actual time=4,815.611..32,069.645 rows=555,236 loops=1)

5.          

Initplan (for Result)

6. 0.034 0.034 ↑ 1.0 1 1

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

  • Index Cond: ((code)::text = 'NLS_GESCHLECHT_MANNLICH'::text)
7. 0.015 0.015 ↑ 1.0 1 1

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

  • Index Cond: ((code)::text = 'NLS_GESCHLECHT_WEIBLICH'::text)
8. 0.052 0.052 ↑ 1.0 1 1

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

  • Index Cond: ((code)::text = 'NLS_GESCHLECHT_UNBEKANNT'::text)
9. 0.023 0.023 ↑ 1.0 1 1

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

  • Index Cond: ((code)::text = 'NLS_GESCHLECHT_UNBESTIMMT'::text)
10. 2,075.776 5,414.571 ↑ 1.0 555,236 1

Sort (cost=9,850,154.33..9,851,558.80 rows=561,787 width=725) (actual time=4,815.111..5,414.571 rows=555,236 loops=1)

  • Sort Key: ((SubPlan 6)), ((SubPlan 5)), tblpatient_1.name, tblpatient_1.vorname
  • Sort Method: external merge Disk: 290,608kB
11. 807.616 3,338.795 ↑ 1.0 555,236 1

Hash Left Join (cost=23,830.79..9,616,006.12 rows=561,787 width=725) (actual time=223.103..3,338.795 rows=555,236 loops=1)

  • Hash Cond: ((tblfall_1.patientenid = ivtblcolorindicator.patientenid) AND (tblfall_1.fallid = ivtblcolorindicator.caseid))
12. 555.719 865.222 ↑ 1.0 555,236 1

Hash Join (cost=23,811.12..62,798.49 rows=561,787 width=63) (actual time=222.545..865.222 rows=555,236 loops=1)

  • Hash Cond: (tblfall_1.patientenid = tblpatient_1.patientenid)
13. 87.700 87.700 ↑ 1.0 555,236 1

Seq Scan on tblfall tblfall_1 (cost=0.00..37,512.61 rows=561,787 width=12) (actual time=0.010..87.700 rows=555,236 loops=1)

14. 99.706 221.803 ↑ 1.0 242,922 1

Hash (cost=15,873.78..15,873.78 rows=244,226 width=57) (actual time=221.795..221.803 rows=242,922 loops=1)

  • Buckets: 262,144 Batches: 1 Memory Usage: 25,068kB
15. 122.097 122.097 ↑ 1.0 242,922 1

Seq Scan on tblpatient tblpatient_1 (cost=0.00..15,873.78 rows=244,226 width=57) (actual time=0.029..122.097 rows=242,922 loops=1)

16. 0.035 0.249 ↑ 2.2 4 1

Hash (cost=19.35..19.35 rows=9 width=558) (actual time=0.238..0.249 rows=4 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
17. 0.008 0.214 ↑ 2.2 4 1

Subquery Scan on ivtblcolorindicator (cost=18.43..19.35 rows=9 width=558) (actual time=0.201..0.214 rows=4 loops=1)

18. 0.113 0.206 ↑ 2.2 4 1

HashAggregate (cost=18.43..19.08 rows=9 width=567) (actual time=0.196..0.206 rows=4 loops=1)

  • Group Key: tblcolorindicator.patientenid, tblcolorindicator.caseid, tblcolorindicator.ward
19. 0.093 0.093 ↑ 1.1 68 1

Seq Scan on tblcolorindicator (cost=0.00..11.30 rows=77 width=31) (actual time=0.025..0.093 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
20.          

SubPlan (for Hash Left Join)

21. 555.236 1,110.472 ↑ 1.0 1 555,236

Aggregate (cost=8.32..8.35 rows=1 width=32) (actual time=0.002..0.002 rows=1 loops=555,236)

22. 555.236 555.236 ↓ 0.0 0 555,236

Index Scan using xie1qckaktfallid on qckaktaufenthort (cost=0.28..8.32 rows=1 width=10) (actual time=0.001..0.001 rows=0 loops=555,236)

  • Index Cond: (fallid = tblfall_1.fallid)
23. 0.000 555.236 ↑ 1.0 1 555,236

Aggregate (cost=8.32..8.35 rows=1 width=32) (actual time=0.001..0.001 rows=1 loops=555,236)

24. 555.236 555.236 ↓ 0.0 0 555,236

Index Scan using xie1qckaktfallid on qckaktaufenthort qckaktaufenthort_1 (cost=0.28..8.32 rows=1 width=5) (actual time=0.001..0.001 rows=0 loops=555,236)

  • Index Cond: (fallid = tblfall_1.fallid)
25.          

SubPlan (for Result)

26. 0.000 1,110.472 ↑ 1.0 1 555,236

Aggregate (cost=8.32..8.35 rows=1 width=32) (actual time=0.002..0.002 rows=1 loops=555,236)

27. 1,110.472 1,110.472 ↓ 0.0 0 555,236

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

  • Index Cond: (fallid = tblfall_1.fallid)
28. 0.000 555.236 ↑ 1.0 1 555,236

Aggregate (cost=8.32..8.35 rows=1 width=32) (actual time=0.001..0.001 rows=1 loops=555,236)

29. 555.236 555.236 ↓ 0.0 0 555,236

Index Scan using xie1qckaktfallid on qckaktaufenthort qckaktaufenthort_3 (cost=0.28..8.32 rows=1 width=2) (actual time=0.001..0.001 rows=0 loops=555,236)

  • Index Cond: (fallid = tblfall_1.fallid)
30. 0.031 3,851.893 ↓ 36.0 36 1

Hash (cost=174.98..174.98 rows=1 width=16) (actual time=3,851.883..3,851.893 rows=36 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
31. 0.064 3,851.862 ↓ 36.0 36 1

Nested Loop (cost=0.84..174.98 rows=1 width=16) (actual time=3,851.641..3,851.862 rows=36 loops=1)

32. 3,851.654 3,851.654 ↓ 36.0 36 1

Index Scan using xpkfall on tblfall (cost=0.42..166.52 rows=1 width=19) (actual time=3,851.550..3,851.654 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))
33. 0.144 0.144 ↑ 1.0 1 36

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

  • Index Cond: (patientenid = tblfall.patientenid)
Planning time : 6.529 ms
Execution time : 36,173.888 ms