explain.depesz.com

PostgreSQL's explain analyze made readable

Result: L4vnH : Optimization for: plan #iCP

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.712 34.566 ↓ 1.8 253 1

Nested Loop Left Join (cost=182.68..4,114.82 rows=139 width=2,597) (actual time=8.480..34.566 rows=253 loops=1)

  • Join Filter: ("userEnteredInfo"."primaryProviderId" = "userEnteredInfo->primaryProvider".id)
  • Rows Removed by Join Filter: 7225
2. 0.235 33.601 ↓ 1.8 253 1

Hash Left Join (cost=181.47..4,083.35 rows=139 width=2,436) (actual time=8.435..33.601 rows=253 loops=1)

  • Hash Cond: ("episodes->Careplan->CareplanStep"."careplanStepTypeId" = "episodes->Careplan->CareplanStep->CareplanStepType".id)
3. 0.185 33.345 ↓ 1.8 253 1

Nested Loop Left Join (cost=168.78..4,070.28 rows=139 width=1,916) (actual time=8.402..33.345 rows=253 loops=1)

4. 0.240 32.654 ↓ 1.8 253 1

Hash Left Join (cost=168.49..3,205.08 rows=139 width=1,886) (actual time=8.383..32.654 rows=253 loops=1)

  • Hash Cond: (episodes."careplanId" = "episodes->Careplan".id)
5. 0.427 32.072 ↓ 1.2 165 1

Nested Loop Left Join (cost=153.24..3,188.88 rows=139 width=1,757) (actual time=8.032..32.072 rows=165 loops=1)

  • Join Filter: ("userEnteredInfo"."attendingProviderId" = "userEnteredInfo->attendingProvider".id)
  • Rows Removed by Join Filter: 4716
6. 0.428 31.315 ↓ 1.2 165 1

Hash Left Join (cost=152.03..3,157.40 rows=139 width=1,596) (actual time=7.851..31.315 rows=165 loops=1)

  • Hash Cond: (("Patient".id = "userEnteredInfo"."patientId") AND ((SubPlan 2) = "userEnteredInfo"."createdAt"))
7. 0.103 7.712 ↓ 1.2 165 1

Hash Left Join (cost=131.74..152.77 rows=139 width=1,457) (actual time=7.336..7.712 rows=165 loops=1)

  • Hash Cond: ("episodes->visits"."visitTypeId" = "episodes->visits->VisitType".id)
8. 0.080 7.591 ↓ 1.2 165 1

Nested Loop (cost=118.59..139.25 rows=139 width=941) (actual time=7.306..7.591 rows=165 loops=1)

9. 0.010 0.010 ↑ 1.0 1 1

Index Scan using "Organizations_pkey" on "Organizations" organizations (cost=0.29..8.31 rows=1 width=120) (actual time=0.010..0.010 rows=1 loops=1)

  • Index Cond: (id = 1)
  • Filter: (("deletedAt" > now()) OR ("deletedAt" IS NULL))
10. 0.229 7.501 ↓ 1.2 165 1

Hash Right Join (cost=118.30..129.55 rows=139 width=821) (actual time=7.292..7.501 rows=165 loops=1)

  • Hash Cond: ("episodes->visits"."episodeId" = episodes.id)
11. 0.082 0.082 ↓ 1.2 303 1

Seq Scan on "Visits" "episodes->visits" (cost=0.00..8.88 rows=258 width=37) (actual time=0.016..0.082 rows=303 loops=1)

  • Filter: (occurred AND (("deletedAt" > now()) OR ("deletedAt" IS NULL)))
  • Rows Removed by Filter: 1
12. 0.258 7.190 ↓ 1.9 133 1

Hash (cost=117.44..117.44 rows=69 width=788) (actual time=7.190..7.190 rows=133 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 44kB
13. 0.079 6.932 ↓ 1.9 133 1

Hash Left Join (cost=100.03..117.44 rows=69 width=788) (actual time=6.589..6.932 rows=133 loops=1)

  • Hash Cond: (episodes."episodeTypeId" = "episodes->EpisodeType".id)
14. 0.172 6.828 ↓ 1.9 133 1

Hash Right Join (cost=87.34..104.57 rows=69 width=252) (actual time=6.550..6.828 rows=133 loops=1)

  • Hash Cond: (person.id = "Patient"."personId")
15. 0.129 0.129 ↓ 1.3 476 1

Seq Scan on "Person" person (cost=0.00..14.65 rows=377 width=100) (actual time=0.013..0.129 rows=476 loops=1)

  • Filter: (("deletedAt" > now()) OR ("deletedAt" IS NULL))
16. 0.078 6.527 ↓ 1.9 133 1

Hash (cost=86.48..86.48 rows=69 width=152) (actual time=6.527..6.527 rows=133 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 30kB
17. 0.115 6.449 ↓ 1.9 133 1

Hash Join (cost=59.10..86.48 rows=69 width=152) (actual time=3.773..6.449 rows=133 loops=1)

  • Hash Cond: ("Patient".id = "organizations->PatientOrganization"."patientId")
18. 0.000 6.244 ↓ 1.8 148 1

Hash Right Join (cost=53.63..80.78 rows=82 width=128) (actual time=3.670..6.244 rows=148 loops=1)

  • Hash Cond: ((episodes."patientId" = "Patient".id) AND (episodes."createdAt" = (SubPlan 1)))
19. 0.041 0.041 ↓ 1.0 130 1

Seq Scan on "Episodes" episodes (cost=0.00..3.89 rows=126 width=70) (actual time=0.005..0.041 rows=130 loops=1)

  • Filter: (("deletedAt" > now()) OR ("deletedAt" IS NULL))
20. 3.434 3.621 ↓ 1.8 148 1

Hash (cost=52.40..52.40 rows=82 width=58) (actual time=3.621..3.621 rows=148 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 21kB
21. 0.187 0.187 ↓ 1.8 148 1

Seq Scan on "Patients" "Patient" (cost=0.00..52.40 rows=82 width=58) (actual time=0.017..0.187 rows=148 loops=1)

  • Filter: (("deletedAt" > now()) OR ("deletedAt" IS NULL))
  • Rows Removed by Filter: 11
22.          

SubPlan (for Hash Right Join)

23. 0.741 5.681 ↑ 1.0 1 247

Aggregate (cost=3.58..3.59 rows=1 width=8) (actual time=0.023..0.023 rows=1 loops=247)

24. 4.940 4.940 ↑ 1.0 1 247

Seq Scan on "Episodes" (cost=0.00..3.58 rows=1 width=8) (actual time=0.014..0.020 rows=1 loops=247)

  • Filter: ("patientId" = "Patient".id)
  • Rows Removed by Filter: 129
25. 0.038 0.090 ↓ 1.7 217 1

Hash (cost=3.90..3.90 rows=126 width=24) (actual time=0.090..0.090 rows=217 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 20kB
26. 0.052 0.052 ↓ 1.7 217 1

Seq Scan on "PatientOrganization" "organizations->PatientOrganization" (cost=0.00..3.90 rows=126 width=24) (actual time=0.022..0.052 rows=217 loops=1)

  • Filter: ("organizationId" = 1)
  • Rows Removed by Filter: 15
27. 0.011 0.025 ↑ 47.0 1 1

Hash (cost=12.10..12.10 rows=47 width=536) (actual time=0.025..0.025 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
28. 0.014 0.014 ↑ 47.0 1 1

Seq Scan on "EpisodeTypes" "episodes->EpisodeType" (cost=0.00..12.10 rows=47 width=536) (actual time=0.013..0.014 rows=1 loops=1)

  • Filter: (("deletedAt" > now()) OR ("deletedAt" IS NULL))
29. 0.008 0.018 ↑ 46.7 3 1

Hash (cost=11.40..11.40 rows=140 width=520) (actual time=0.018..0.018 rows=3 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
30. 0.010 0.010 ↑ 46.7 3 1

Seq Scan on "VisitTypes" "episodes->visits->VisitType" (cost=0.00..11.40 rows=140 width=520) (actual time=0.009..0.010 rows=3 loops=1)

31. 0.133 0.300 ↑ 1.1 321 1

Hash (cost=15.15..15.15 rows=343 width=139) (actual time=0.300..0.300 rows=321 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 54kB
32. 0.167 0.167 ↑ 1.1 321 1

Seq Scan on "UserEnteredInfo" "userEnteredInfo" (cost=0.00..15.15 rows=343 width=139) (actual time=0.020..0.167 rows=321 loops=1)

  • Filter: (("deletedAt" > now()) OR ("deletedAt" IS NULL))
33.          

SubPlan (for Hash Left Join)

34. 2.135 22.875 ↑ 1.0 1 305

Aggregate (cost=14.30..14.31 rows=1 width=8) (actual time=0.075..0.075 rows=1 loops=305)

35. 20.740 20.740 ↓ 1.3 4 305

Seq Scan on "UserEnteredInfo" (cost=0.00..14.29 rows=3 width=8) (actual time=0.034..0.068 rows=4 loops=305)

  • Filter: ("patientId" = "Patient".id)
  • Rows Removed by Filter: 317
36. 0.170 0.330 ↓ 4.1 29 165

Materialize (cost=1.21..16.90 rows=7 width=161) (actual time=0.001..0.002 rows=29 loops=165)

37. 0.063 0.160 ↓ 4.1 29 1

Hash Right Join (cost=1.21..16.86 rows=7 width=161) (actual time=0.073..0.160 rows=29 loops=1)

  • Hash Cond: ("userEnteredInfo->attendingProvider->Person".id = "userEnteredInfo->attendingProvider".id)
38. 0.071 0.071 ↓ 1.3 476 1

Seq Scan on "Person" "userEnteredInfo->attendingProvider->Person" (cost=0.00..14.65 rows=377 width=92) (actual time=0.006..0.071 rows=476 loops=1)

  • Filter: (("deletedAt" > now()) OR ("deletedAt" IS NULL))
39. 0.011 0.026 ↓ 4.1 29 1

Hash (cost=1.12..1.12 rows=7 width=69) (actual time=0.026..0.026 rows=29 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
40. 0.015 0.015 ↓ 4.1 29 1

Seq Scan on "Providers" "userEnteredInfo->attendingProvider" (cost=0.00..1.12 rows=7 width=69) (actual time=0.008..0.015 rows=29 loops=1)

  • Filter: (("deletedAt" > now()) OR ("deletedAt" IS NULL))
  • Rows Removed by Filter: 1
41. 0.084 0.342 ↓ 1.7 257 1

Hash (cost=13.39..13.39 rows=149 width=129) (actual time=0.342..0.342 rows=257 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 40kB
42. 0.123 0.258 ↓ 1.7 257 1

Hash Right Join (cost=7.20..13.39 rows=149 width=129) (actual time=0.162..0.258 rows=257 loops=1)

  • Hash Cond: ("episodes->Careplan->CareplanStep"."careplanId" = "episodes->Careplan".id)
43. 0.042 0.042 ↓ 1.0 160 1

Seq Scan on "CareplanSteps" "episodes->Careplan->CareplanStep" (cost=0.00..5.76 rows=159 width=74) (actual time=0.011..0.042 rows=160 loops=1)

  • Filter: (("deletedAt" > now()) OR ("deletedAt" IS NULL))
  • Rows Removed by Filter: 25
44. 0.043 0.093 ↓ 1.3 199 1

Hash (cost=5.34..5.34 rows=149 width=55) (actual time=0.093..0.093 rows=199 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 25kB
45. 0.050 0.050 ↓ 1.3 199 1

Seq Scan on "Careplans" "episodes->Careplan" (cost=0.00..5.34 rows=149 width=55) (actual time=0.012..0.050 rows=199 loops=1)

  • Filter: (("deletedAt" > now()) OR ("deletedAt" IS NULL))
  • Rows Removed by Filter: 9
46. 0.506 0.506 ↑ 1.0 1 253

Index Scan using "Organizations_pkey" on "Organizations" "episodes->Careplan->CareplanStep->Location" (cost=0.29..6.22 rows=1 width=30) (actual time=0.002..0.002 rows=1 loops=253)

  • Index Cond: ("episodes->Careplan->CareplanStep"."organizationId" = id)
  • Filter: (("deletedAt" > now()) OR ("deletedAt" IS NULL))
47. 0.008 0.021 ↑ 7.8 6 1

Hash (cost=12.10..12.10 rows=47 width=520) (actual time=0.021..0.021 rows=6 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
48. 0.013 0.013 ↑ 7.8 6 1

Seq Scan on "CareplanStepTypes" "episodes->Careplan->CareplanStep->CareplanStepType" (cost=0.00..12.10 rows=47 width=520) (actual time=0.012..0.013 rows=6 loops=1)

  • Filter: (("deletedAt" > now()) OR ("deletedAt" IS NULL))
  • Rows Removed by Filter: 6
49. 0.133 0.253 ↓ 4.1 29 253

Materialize (cost=1.21..16.90 rows=7 width=161) (actual time=0.000..0.001 rows=29 loops=253)

50. 0.035 0.120 ↓ 4.1 29 1

Hash Right Join (cost=1.21..16.86 rows=7 width=161) (actual time=0.033..0.120 rows=29 loops=1)

  • Hash Cond: ("userEnteredInfo->primaryProvider->Person".id = "userEnteredInfo->primaryProvider".id)
51. 0.064 0.064 ↓ 1.3 476 1

Seq Scan on "Person" "userEnteredInfo->primaryProvider->Person" (cost=0.00..14.65 rows=377 width=92) (actual time=0.005..0.064 rows=476 loops=1)

  • Filter: (("deletedAt" > now()) OR ("deletedAt" IS NULL))
52. 0.010 0.021 ↓ 4.1 29 1

Hash (cost=1.12..1.12 rows=7 width=69) (actual time=0.021..0.021 rows=29 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
53. 0.011 0.011 ↓ 4.1 29 1

Seq Scan on "Providers" "userEnteredInfo->primaryProvider" (cost=0.00..1.12 rows=7 width=69) (actual time=0.006..0.011 rows=29 loops=1)

  • Filter: (("deletedAt" > now()) OR ("deletedAt" IS NULL))
  • Rows Removed by Filter: 1
Planning time : 2,236.064 ms
Execution time : 35.654 ms