explain.depesz.com

PostgreSQL's explain analyze made readable

Result: jR6L

Settings
# exclusive inclusive rows x rows loops node
1. 0.094 8,782.411 ↓ 48.0 48 1

Limit (cost=15,227.88..15,227.88 rows=1 width=664) (actual time=8,782.284..8,782.411 rows=48 loops=1)

2. 0.173 8,782.317 ↓ 48.0 48 1

Sort (cost=15,227.88..15,227.88 rows=1 width=664) (actual time=8,782.282..8,782.317 rows=48 loops=1)

  • Sort Key: x1.creationdate
  • Sort Method: quicksort Memory: 50kB
3. 0.205 8,782.144 ↓ 48.0 48 1

Nested Loop Left Join (cost=15,223.88..15,227.87 rows=1 width=664) (actual time=8,781.774..8,782.144 rows=48 loops=1)

  • Join Filter: (x1.xcaseid = step.xcaseid)
4. 0.195 8,781.843 ↓ 48.0 48 1

WindowAgg (cost=14,209.48..14,209.56 rows=1 width=648) (actual time=8,781.763..8,781.843 rows=48 loops=1)

5. 0.307 8,781.648 ↓ 48.0 48 1

HashAggregate (cost=14,209.48..14,209.53 rows=1 width=513) (actual time=8,781.587..8,781.648 rows=48 loops=1)

6. 0.342 8,781.341 ↓ 48.0 48 1

Nested Loop (cost=4,623.91..14,209.40 rows=1 width=513) (actual time=7,418.311..8,781.341 rows=48 loops=1)

7. 0.245 8,780.807 ↓ 48.0 48 1

Nested Loop (cost=4,623.77..14,209.15 rows=1 width=491) (actual time=7,418.278..8,780.807 rows=48 loops=1)

8. 0.283 8,780.370 ↓ 48.0 48 1

Nested Loop (cost=4,623.62..14,208.95 rows=1 width=469) (actual time=7,418.267..8,780.370 rows=48 loops=1)

  • Join Filter: (x1.patientid = pid.patientid)
9. 0.208 8,779.415 ↓ 48.0 48 1

Nested Loop (cost=4,623.21..14,204.97 rows=1 width=468) (actual time=7,418.237..8,779.415 rows=48 loops=1)

10. 2.589 8,778.439 ↓ 48.0 48 1

Hash Join (cost=4,622.79..14,199.31 rows=1 width=443) (actual time=7,418.186..8,778.439 rows=48 loops=1)

  • Hash Cond: (x1.xcaseid = caller.xcaseid)
11. 23.209 2,667.817 ↓ 114.9 804 1

Hash Left Join (cost=4,568.41..14,144.90 rows=7 width=349) (actual time=961.952..2,667.817 rows=804 loops=1)

  • Hash Cond: (x1.xcaseid = stateandactionforactor.xcaseid)
12. 2.980 1,768.699 ↓ 114.9 804 1

Nested Loop Left Join (cost=4,545.66..14,121.25 rows=7 width=251) (actual time=29.945..1,768.699 rows=804 loops=1)

13. 125.130 1,764.111 ↓ 114.9 804 1

Hash Join (cost=4,545.52..14,108.63 rows=7 width=247) (actual time=29.933..1,764.111 rows=804 loops=1)

  • Hash Cond: (s1.xcaseid = x1.xcaseid)
14. 404.665 1,612.614 ↓ 81.3 116,867 1

Nested Loop (cost=66.12..9,623.77 rows=1,438 width=4) (actual time=3.361..1,612.614 rows=116,867 loops=1)

15. 188.754 389.880 ↓ 81.3 116,867 1

Nested Loop (cost=65.70..3,511.02 rows=1,438 width=4) (actual time=3.304..389.880 rows=116,867 loops=1)

16. 0.025 9.710 ↓ 2.0 4 1

Nested Loop (cost=47.25..113.71 rows=2 width=4) (actual time=3.244..9.710 rows=4 loops=1)

17. 2.127 9.621 ↓ 2.0 4 1

Hash Left Join (cost=47.11..113.35 rows=2 width=8) (actual time=3.193..9.621 rows=4 loops=1)

  • Hash Cond: (a1.communityid = c.communityid)
  • 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-
  • Rows Removed by Filter: 1282
18. 2.998 7.322 ↑ 1.0 1,286 1

Hash Left Join (cost=44.42..83.82 rows=1,286 width=31) (actual time=3.005..7.322 rows=1,286 loops=1)

  • Hash Cond: (a1.personid = p.personid)
19. 1.369 1.369 ↑ 1.0 1,286 1

Seq Scan on actor a1 (cost=0.00..21.86 rows=1,286 width=16) (actual time=0.012..1.369 rows=1,286 loops=1)

20. 1.602 2.955 ↑ 1.0 1,263 1

Hash (cost=28.63..28.63 rows=1,263 width=23) (actual time=2.955..2.955 rows=1,263 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 70kB
21. 1.353 1.353 ↑ 1.0 1,263 1

Seq Scan on person p (cost=0.00..28.63 rows=1,263 width=23) (actual time=0.006..1.353 rows=1,263 loops=1)

22. 0.087 0.172 ↑ 1.0 75 1

Hash (cost=1.75..1.75 rows=75 width=29) (actual time=0.172..0.172 rows=75 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 5kB
23. 0.085 0.085 ↑ 1.0 75 1

Seq Scan on community c (cost=0.00..1.75 rows=75 width=29) (actual time=0.005..0.085 rows=75 loops=1)

24. 0.064 0.064 ↑ 1.0 1 4

Index Only Scan using institution_pkey on institution i (cost=0.14..0.17 rows=1 width=4) (actual time=0.014..0.016 rows=1 loops=4)

  • Index Cond: (institutionid = a1.institutionid)
  • Heap Fetches: 4
25. 178.932 191.416 ↓ 25.3 29,217 4

Bitmap Heap Scan on actorrightsonstep ar1 (cost=18.45..1,687.09 rows=1,157 width=8) (actual time=3.377..47.854 rows=29,217 loops=4)

  • Recheck Cond: (actorid = a1.actorid)
26. 12.484 12.484 ↓ 25.3 29,217 4

Bitmap Index Scan on idx_4f47d16c6dcba9b2 (cost=0.00..18.16 rows=1,157 width=0) (actual time=3.121..3.121 rows=29,217 loops=4)

  • Index Cond: (actorid = a1.actorid)
27. 818.069 818.069 ↑ 1.0 1 116,867

Index Scan using step_pkey on step s1 (cost=0.42..4.24 rows=1 width=8) (actual time=0.005..0.007 rows=1 loops=116,867)

  • Index Cond: (stepid = ar1.stepid)
28. 1.264 26.367 ↓ 1.4 804 1

Hash (cost=4,472.10..4,472.10 rows=584 width=243) (actual time=26.367..26.367 rows=804 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 104kB
29. 25.103 25.103 ↓ 1.4 804 1

Seq Scan on xcase x1 (cost=0.00..4,472.10 rows=584 width=243) (actual time=0.308..25.103 rows=804 loops=1)

  • Filter: ((creationdate)::date = '2019-10-01'::date)
  • Rows Removed by Filter: 116069
30. 1.608 1.608 ↓ 0.0 0 804

Index Scan using xfpfld_pkey on xfpfld legacyfld (cost=0.14..1.79 rows=1 width=54) (actual time=0.002..0.002 rows=0 loops=804)

  • Index Cond: ((fld_xfolderinstanceuid)::text = (x1.folderuniqueid)::text)
31. 161.670 875.909 ↓ 116.8 116,805 1

Hash (cost=10.25..10.25 rows=1,000 width=102) (actual time=875.909..875.909 rows=116,805 loops=1)

  • Buckets: 1024 Batches: 8 (originally 1) Memory Usage: 1025kB
32. 714.239 714.239 ↓ 116.8 116,805 1

Function Scan on getxcasesstateforuseractor stateandactionforactor (cost=0.25..10.25 rows=1,000 width=102) (actual time=573.350..714.239 rows=116,805 loops=1)

33. 16.020 6,108.033 ↓ 1,744.0 8,720 1

Hash (cost=54.31..54.31 rows=5 width=106) (actual time=6,108.033..6,108.033 rows=8,720 loops=1)

  • Buckets: 1024 Batches: 2 (originally 1) Memory Usage: 1025kB
34. 164.180 6,092.013 ↓ 1,744.0 8,720 1

Hash Join (cost=40.51..54.31 rows=5 width=106) (actual time=5,802.773..6,092.013 rows=8,720 loops=1)

  • Hash Cond: (correspondents.xcaseid = caller.xcaseid)
35. 3,702.269 3,702.269 ↓ 116.9 116,856 1

Function Scan on getxcasesrecipientsstrforuser correspondents (cost=0.25..10.25 rows=1,000 width=37) (actual time=3,575.857..3,702.269 rows=116,856 loops=1)

36. 13.030 2,225.564 ↓ 8,720.0 8,720 1

Hash (cost=40.25..40.25 rows=1 width=69) (actual time=2,225.564..2,225.564 rows=8,720 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 782kB
37. 2,212.534 2,212.534 ↓ 8,720.0 8,720 1

Function Scan on getxcasesauthorsstr caller (cost=0.25..40.25 rows=1 width=69) (actual time=2,085.992..2,212.534 rows=8,720 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))
  • Rows Removed by Filter: 108153
38. 0.768 0.768 ↑ 1.0 1 48

Index Scan using patient_pkey on patient (cost=0.42..5.65 rows=1 width=25) (actual time=0.015..0.016 rows=1 loops=48)

  • Index Cond: (patientid = x1.patientid)
39. 0.672 0.672 ↑ 1.0 1 48

Index Scan using idx_fdc7c1628f803478 on patientidentifier pid (cost=0.42..3.96 rows=1 width=13) (actual time=0.013..0.014 rows=1 loops=48)

  • Index Cond: (patientid = patient.patientid)
40. 0.192 0.192 ↑ 1.0 1 48

Index Scan using xdscode_pkey on xdscode xcodeprio (cost=0.14..0.19 rows=1 width=30) (actual time=0.003..0.004 rows=1 loops=48)

  • Index Cond: (xdscodeid = x1.priorityeventcodeid)
41. 0.192 0.192 ↑ 1.0 1 48

Index Scan using xdscode_pkey on xdscode xcodetpl (cost=0.14..0.19 rows=1 width=30) (actual time=0.003..0.004 rows=1 loops=48)

  • Index Cond: (xdscodeid = x1.templateclasscodeid)
42. 0.093 0.096 ↓ 0.0 0 48

HashAggregate (cost=1,014.40..1,015.60 rows=120 width=8) (actual time=0.002..0.002 rows=0 loops=48)

43. 0.002 0.003 ↓ 0.0 0 1

Nested Loop (cost=0.42..1,013.80 rows=120 width=8) (actual time=0.003..0.003 rows=0 loops=1)

44. 0.001 0.001 ↓ 0.0 0 1

Seq Scan on stepsroutingstatus (cost=0.00..11.20 rows=120 width=8) (actual time=0.001..0.001 rows=0 loops=1)

45. 0.000 0.000 ↓ 0.0 0

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

  • Index Cond: (stepid = stepsroutingstatus.stepid)
Total runtime : 8,787.637 ms