explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ziR

Settings
# exclusive inclusive rows x rows loops node
1. 0.071 13,655,735.983 ↓ 36.5 73 1

Limit (cost=6,403.89..6,403.89 rows=2 width=672) (actual time=13,655,735.869..13,655,735.983 rows=73 loops=1)

2. 0.160 13,655,735.912 ↓ 36.5 73 1

Sort (cost=6,403.89..6,403.89 rows=2 width=672) (actual time=13,655,735.866..13,655,735.912 rows=73 loops=1)

  • Sort Key: x1.creationdate
  • Sort Method: quicksort Memory: 65kB
3. 0.115 13,655,735.752 ↓ 36.5 73 1

Nested Loop Left Join (cost=6,391.76..6,403.88 rows=2 width=672) (actual time=13,655,735.505..13,655,735.752 rows=73 loops=1)

  • Join Filter: (x1.xcaseid = step.xcaseid)
4. 0.190 13,655,735.564 ↓ 73.0 73 1

WindowAgg (cost=3,339.86..3,339.94 rows=1 width=656) (actual time=13,655,735.498..13,655,735.564 rows=73 loops=1)

5. 0.934 13,655,735.374 ↓ 73.0 73 1

HashAggregate (cost=3,339.86..3,339.92 rows=1 width=521) (actual time=13,655,735.307..13,655,735.374 rows=73 loops=1)

6. 5,028.841 13,655,734.440 ↓ 73.0 73 1

Nested Loop (cost=3.84..3,339.78 rows=1 width=521) (actual time=13,074,892.070..13,655,734.440 rows=73 loops=1)

  • Join Filter: (x1.xcaseid = correspondents.xcaseid)
  • Rows Removed by Join Filter: 9474524
7. 4,906.604 13,642,498.720 ↓ 73.0 73 1

Nested Loop Left Join (cost=3.59..3,316.99 rows=1 width=496) (actual time=13,071,175.198..13,642,498.720 rows=73 loops=1)

  • Join Filter: (stateandactionforactor.xcaseid = x1.xcaseid)
  • Rows Removed by Join Filter: 9470363
8. 8.851 13,632,512.849 ↓ 73.0 73 1

Nested Loop (cost=3.34..3,294.24 rows=1 width=398) (actual time=13,070,542.182..13,632,512.849 rows=73 loops=1)

  • Join Filter: (x1.templateclasscodeid = xcodetpl.xdscodeid)
  • Rows Removed by Join Filter: 15257
9. 8.614 13,632,496.041 ↓ 73.0 73 1

Nested Loop (cost=3.34..3,288.08 rows=1 width=372) (actual time=13,070,542.175..13,632,496.041 rows=73 loops=1)

  • Join Filter: (x1.priorityeventcodeid = xcodeprio.xdscodeid)
  • Rows Removed by Join Filter: 15257
10. 0.507 13,632,479.762 ↓ 73.0 73 1

Nested Loop (cost=3.34..3,281.91 rows=1 width=346) (actual time=13,070,542.156..13,632,479.762 rows=73 loops=1)

  • Join Filter: (x1.patientid = pid.patientid)
11. 0.396 13,632,469.181 ↓ 73.0 73 1

Nested Loop (cost=2.92..3,281.39 rows=1 width=345) (actual time=13,070,541.528..13,632,469.181 rows=73 loops=1)

12. 6,599.871 13,632,467.544 ↓ 73.0 73 1

Nested Loop (cost=2.50..3,277.00 rows=1 width=320) (actual time=13,070,541.489..13,632,467.544 rows=73 loops=1)

  • Join Filter: (x1.xcaseid = caller.xcaseid)
  • Rows Removed by Join Filter: 12085417
13. 3,673.913 3,673.913 ↓ 9,770.0 9,770 1

Function Scan on getxcasesauthorsstr caller (cost=0.25..40.25 rows=1 width=69) (actual time=3,428.135..3,673.913 rows=9,770 loops=1)

  • Filter: ((lower(((COALESCE(fullname, ''::text) || ' '::text) || (COALESCE(institut, ''::character varying))::text)) ~~ '%mont%'::text) AND (lower(((COALESCE(fullname, ''::text) || ' '::text) || (COALESCE(institut, ''::character varying))::text)) ~~ '%de%'::text) AND (lower(((COALESCE(fullname, ''::text) || ' '::text) || (COALESCE(institut, ''::character varying))::text)) ~~ '%marsan%'::text))
  • Rows Removed by Filter: 119775
14. 19,940.570 13,622,193.760 ↓ 35.3 1,237 9,770

Merge Join (cost=2.25..3,236.32 rows=35 width=251) (actual time=0.093..1,394.288 rows=1,237 loops=9,770)

  • Merge Cond: (a1.institutionid = i.institutionid)
15. 18,260.130 13,593,567.660 ↓ 35.3 1,237 9,770

Nested Loop Left Join (cost=2.11..4,704.48 rows=35 width=255) (actual time=0.081..1,391.358 rows=1,237 loops=9,770)

16. 1,868,043.540 13,551,136.550 ↓ 35.3 1,237 9,770

Nested Loop (cost=1.97..4,697.19 rows=35 width=251) (actual time=0.070..1,387.015 rows=1,237 loops=9,770)

17. 2,646,800.470 9,151,510.150 ↓ 38.6 129,559 9,770

Nested Loop (cost=1.55..3,103.13 rows=3,355 width=8) (actual time=0.059..936.695 rows=129,559 loops=9,770)

18. 1,244,307.200 2,707,335.390 ↓ 38.6 129,559 9,770

Nested Loop (cost=1.12..1,143.56 rows=3,355 width=8) (actual time=0.049..277.107 rows=129,559 loops=9,770)

19. 17,996.340 111,876.270 ↑ 1.0 4 9,770

Nested Loop Left Join (cost=0.70..811.08 rows=4 width=8) (actual time=0.038..11.451 rows=4 loops=9,770)

  • Filter: ((((p.login)::text = 'gerald.rigou@tmf-sante.fr'::text) AND ((p.institutionaet)::text = 'TELEMED-HDS-2'::text)) OR (((c.login)::text = ANY ('{Default,TELERAD}'::text[])) AND ((c.institutionaet)::text = 'TELEMED-HDS-2'::text)))
  • Rows Removed by Filter: 1343
20. 31,576.640 67,559.550 ↓ 1.1 1,347 9,770

Nested Loop Left Join (cost=0.56..583.26 rows=1,199 width=31) (actual time=0.010..6.915 rows=1,347 loops=9,770)

21. 9,662.530 9,662.530 ↓ 1.1 1,347 9,770

Index Scan using idx_85b479fdf76065b2 on actor a1 (cost=0.28..77.57 rows=1,199 width=16) (actual time=0.007..0.989 rows=1,347 loops=9,770)

22. 26,320.380 26,320.380 ↑ 1.0 1 13,160,190

Index Scan using person_pkey on person p (cost=0.28..0.41 rows=1 width=23) (actual time=0.002..0.002 rows=1 loops=13,160,190)

  • Index Cond: (a1.personid = personid)
23. 26,320.380 26,320.380 ↑ 1.0 1 13,160,190

Index Scan using community_pkey on community c (cost=0.14..0.17 rows=1 width=29) (actual time=0.001..0.002 rows=1 loops=13,160,190)

  • Index Cond: (a1.communityid = communityid)
24. 1,351,151.920 1,351,151.920 ↓ 21.6 32,390 39,080

Index Scan using idx_4f47d16c6dcba9b2 on actorrightsonstep ar1 (cost=0.42..68.14 rows=1,498 width=8) (actual time=0.050..34.574 rows=32,390 loops=39,080)

  • Index Cond: (actorid = a1.actorid)
25. 3,797,374.290 3,797,374.290 ↑ 1.0 1 1,265,791,430

Index Scan using step_pkey on step s1 (cost=0.42..0.57 rows=1 width=8) (actual time=0.002..0.003 rows=1 loops=1,265,791,430)

  • Index Cond: (stepid = ar1.stepid)
26. 2,531,582.860 2,531,582.860 ↓ 0.0 0 1,265,791,430

Index Scan using xcase_pkey on xcase x1 (cost=0.42..0.47 rows=1 width=243) (actual time=0.002..0.002 rows=0 loops=1,265,791,430)

  • Index Cond: (xcaseid = s1.xcaseid)
  • Filter: ((creationdate >= '2019-10-30 23:00:00'::timestamp without time zone) AND (creationdate <= '2019-11-01 22:59:59'::timestamp without time zone))
  • Rows Removed by Filter: 1
27. 24,170.980 24,170.980 ↓ 0.0 0 12,085,490

Index Scan using xfpfld_pkey on xfpfld legacyfld (cost=0.14..0.20 rows=1 width=58) (actual time=0.002..0.002 rows=0 loops=12,085,490)

  • Index Cond: ((fld_xfolderinstanceuid)::text = (x1.folderuniqueid)::text)
28. 8,685.530 8,685.530 ↓ 18.5 1,238 9,770

Index Only Scan using institution_pkey on institution i (cost=0.14..9.15 rows=67 width=4) (actual time=0.007..0.889 rows=1,238 loops=9,770)

  • Heap Fetches: 12095260
29. 1.241 1.241 ↑ 1.0 1 73

Index Scan using patient_pkey on patient (cost=0.42..4.38 rows=1 width=25) (actual time=0.015..0.017 rows=1 loops=73)

  • Index Cond: (patientid = x1.patientid)
30. 10.074 10.074 ↑ 1.0 1 73

Index Scan using idx_fdc7c1628f803478 on patientidentifier pid (cost=0.42..0.51 rows=1 width=13) (actual time=0.135..0.138 rows=1 loops=73)

  • Index Cond: (patientid = patient.patientid)
31. 7.665 7.665 ↓ 1.1 210 73

Seq Scan on xdscode xcodeprio (cost=0.00..3.85 rows=185 width=34) (actual time=0.004..0.105 rows=210 loops=73)

32. 7.957 7.957 ↓ 1.1 210 73

Seq Scan on xdscode xcodetpl (cost=0.00..3.85 rows=185 width=34) (actual time=0.002..0.109 rows=210 loops=73)

33. 5,079.267 5,079.267 ↓ 129.7 129,732 73

Function Scan on getxcasesstateforuseractor stateandactionforactor (cost=0.25..10.25 rows=1,000 width=102) (actual time=6.993..69.579 rows=129,732 loops=73)

34. 8,206.879 8,206.879 ↓ 129.8 129,789 73

Function Scan on getxcasesrecipientsstrforuser correspondents (cost=0.25..10.25 rows=1,000 width=37) (actual time=49.196..112.423 rows=129,789 loops=73)

35. 0.071 0.073 ↓ 0.0 0 73

HashAggregate (cost=3,051.90..3,055.60 rows=370 width=8) (actual time=0.001..0.001 rows=0 loops=73)

36. 0.000 0.002 ↓ 0.0 0 1

Nested Loop (cost=0.42..3,050.05 rows=370 width=8) (actual time=0.002..0.002 rows=0 loops=1)

37. 0.002 0.002 ↓ 0.0 0 1

Seq Scan on stepsroutingstatus (cost=0.00..13.70 rows=370 width=8) (actual time=0.002..0.002 rows=0 loops=1)

38. 0.000 0.000 ↓ 0.0 0

Index Scan using step_pkey on step (cost=0.42..8.20 rows=1 width=8) (never executed)

  • Index Cond: (stepid = stepsroutingstatus.stepid)
Total runtime : 13,655,758.783 ms