explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 2sdE : Optimization for: plan #iCP

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.427 14.353 ↑ 1.1 253 1

Nested Loop Left Join (cost=1,124.25..4,857.64 rows=267 width=2,597) (actual time=10.901..14.353 rows=253 loops=1)

  • Join Filter: ("userEnteredInfo"."primaryProviderId" = "userEnteredInfo->primaryProvider".id)
  • Rows Removed by Join Filter: 7225
2. 0.460 13.420 ↑ 1.1 253 1

Nested Loop Left Join (cost=1,123.04..4,812.72 rows=267 width=2,436) (actual time=10.718..13.420 rows=253 loops=1)

  • Join Filter: ("userEnteredInfo"."attendingProviderId" = "userEnteredInfo->attendingProvider".id)
  • Rows Removed by Join Filter: 7205
3. 0.189 12.454 ↑ 1.1 253 1

Hash Left Join (cost=1,121.83..4,767.80 rows=267 width=2,275) (actual time=10.474..12.454 rows=253 loops=1)

  • Hash Cond: (episodes."careplanId" = "episodes->Careplan".id)
4. 0.000 11.283 ↑ 1.6 165 1

Hash Right Join (cost=167.81..3,811.95 rows=267 width=1,596) (actual time=9.484..11.283 rows=165 loops=1)

  • Hash Cond: (("userEnteredInfo"."patientId" = "Patient".id) AND ("userEnteredInfo"."createdAt" = (SubPlan 2)))
5. 0.104 0.104 ↑ 1.0 321 1

Seq Scan on "UserEnteredInfo" "userEnteredInfo" (cost=0.00..14.82 rows=321 width=139) (actual time=0.008..0.104 rows=321 loops=1)

  • Filter: (("deletedAt" > now()) OR ("deletedAt" IS NULL))
6. 1.672 9.459 ↑ 1.6 165 1

Hash (cost=163.80..163.80 rows=267 width=1,457) (actual time=9.459..9.459 rows=165 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 71kB
7. 0.094 7.787 ↑ 1.6 165 1

Nested Loop (cost=139.02..163.80 rows=267 width=1,457) (actual time=7.503..7.787 rows=165 loops=1)

8. 0.011 0.011 ↑ 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.011 rows=1 loops=1)

  • Index Cond: (id = 1)
  • Filter: (("deletedAt" > now()) OR ("deletedAt" IS NULL))
9. 0.068 7.682 ↑ 1.6 165 1

Hash Left Join (cost=138.73..152.82 rows=267 width=1,337) (actual time=7.490..7.682 rows=165 loops=1)

  • Hash Cond: ("episodes->visits"."visitTypeId" = "episodes->visits->VisitType".id)
10. 0.123 7.588 ↑ 1.6 165 1

Hash Right Join (cost=125.58..138.95 rows=267 width=821) (actual time=7.453..7.588 rows=165 loops=1)

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

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

  • Filter: (occurred AND (("deletedAt" > now()) OR ("deletedAt" IS NULL)))
  • Rows Removed by Filter: 1
12. 0.149 7.383 ↓ 1.1 133 1

Hash (cost=124.12..124.12 rows=117 width=788) (actual time=7.383..7.383 rows=133 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 44kB
13. 0.081 7.234 ↓ 1.1 133 1

Hash Left Join (cost=94.84..124.12 rows=117 width=788) (actual time=4.653..7.234 rows=133 loops=1)

  • Hash Cond: (episodes."episodeTypeId" = "episodes->EpisodeType".id)
14. 0.065 7.137 ↓ 1.1 133 1

Hash Left Join (cost=82.15..111.11 rows=117 width=252) (actual time=4.631..7.137 rows=133 loops=1)

  • Hash Cond: ("Patient"."personId" = person.id)
15. 0.056 6.907 ↓ 1.1 133 1

Hash Join (cost=62.78..91.44 rows=117 width=152) (actual time=4.460..6.907 rows=133 loops=1)

  • Hash Cond: ("Patient".id = "organizations->PatientOrganization"."patientId")
16. 0.000 6.737 ↓ 1.1 148 1

Hash Right Join (cost=55.48..83.76 rows=140 width=128) (actual time=4.336..6.737 rows=148 loops=1)

  • Hash Cond: ((episodes."patientId" = "Patient".id) AND (episodes."createdAt" = (SubPlan 1)))
17. 0.030 0.030 ↑ 1.0 130 1

Seq Scan on "Episodes" episodes (cost=0.00..3.95 rows=130 width=70) (actual time=0.004..0.030 rows=130 loops=1)

  • Filter: (("deletedAt" > now()) OR ("deletedAt" IS NULL))
18. 4.077 4.305 ↓ 1.1 148 1

Hash (cost=53.38..53.38 rows=140 width=58) (actual time=4.305..4.305 rows=148 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 21kB
19. 0.228 0.228 ↓ 1.1 148 1

Seq Scan on "Patients" "Patient" (cost=0.00..53.38 rows=140 width=58) (actual time=0.012..0.228 rows=148 loops=1)

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

SubPlan (for Hash Right Join)

21. 0.988 6.175 ↑ 1.0 1 247

Aggregate (cost=3.63..3.64 rows=1 width=8) (actual time=0.025..0.025 rows=1 loops=247)

22. 5.187 5.187 ↑ 1.0 1 247

Seq Scan on "Episodes" (cost=0.00..3.63 rows=1 width=8) (actual time=0.015..0.021 rows=1 loops=247)

  • Filter: ("patientId" = "Patient".id)
  • Rows Removed by Filter: 129
23. 0.034 0.114 ↓ 1.1 217 1

Hash (cost=4.90..4.90 rows=192 width=24) (actual time=0.114..0.114 rows=217 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 20kB
24. 0.080 0.080 ↓ 1.1 217 1

Seq Scan on "PatientOrganization" "organizations->PatientOrganization" (cost=0.00..4.90 rows=192 width=24) (actual time=0.037..0.080 rows=217 loops=1)

  • Filter: ("organizationId" = 1)
  • Rows Removed by Filter: 15
25. 0.069 0.165 ↓ 1.3 476 1

Hash (cost=14.65..14.65 rows=377 width=100) (actual time=0.165..0.165 rows=476 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 61kB
26. 0.096 0.096 ↓ 1.3 476 1

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

  • Filter: (("deletedAt" > now()) OR ("deletedAt" IS NULL))
27. 0.007 0.016 ↑ 47.0 1 1

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

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

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

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

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

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

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

31.          

SubPlan (for Hash Right Join)

32. 1.830 2.745 ↑ 1.0 1 305

Aggregate (cost=11.29..11.30 rows=1 width=8) (actual time=0.009..0.009 rows=1 loops=305)

33. 0.305 0.915 ↓ 1.3 4 305

Bitmap Heap Scan on "UserEnteredInfo" (cost=4.17..11.28 rows=3 width=8) (actual time=0.003..0.003 rows=4 loops=305)

  • Recheck Cond: ("patientId" = "Patient".id)
  • Heap Blocks: exact=494
34. 0.610 0.610 ↓ 1.3 4 305

Bitmap Index Scan on user_entered_info_patient_id (cost=0.00..4.17 rows=3 width=0) (actual time=0.002..0.002 rows=4 loops=305)

  • Index Cond: ("patientId" = "Patient".id)
35. 0.193 0.982 ↓ 1.7 257 1

Hash (cost=952.16..952.16 rows=149 width=679) (actual time=0.982..0.982 rows=257 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 44kB
36. 0.161 0.789 ↓ 1.7 257 1

Hash Left Join (cost=20.18..952.16 rows=149 width=679) (actual time=0.241..0.789 rows=257 loops=1)

  • Hash Cond: ("episodes->Careplan->CareplanStep"."careplanStepTypeId" = "episodes->Careplan->CareplanStep->CareplanStepType".id)
37. 0.096 0.597 ↓ 1.7 257 1

Nested Loop Left Join (cost=7.49..939.07 rows=149 width=159) (actual time=0.141..0.597 rows=257 loops=1)

38. 0.082 0.244 ↓ 1.7 257 1

Hash Right Join (cost=7.20..13.41 rows=149 width=129) (actual time=0.131..0.244 rows=257 loops=1)

  • Hash Cond: ("episodes->Careplan->CareplanStep"."careplanId" = "episodes->Careplan".id)
39. 0.048 0.048 ↑ 1.0 160 1

Seq Scan on "CareplanSteps" "episodes->Careplan->CareplanStep" (cost=0.00..5.78 rows=160 width=74) (actual time=0.012..0.048 rows=160 loops=1)

  • Filter: (("deletedAt" > now()) OR ("deletedAt" IS NULL))
  • Rows Removed by Filter: 25
40. 0.052 0.114 ↓ 1.3 199 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 25kB
41. 0.062 0.062 ↓ 1.3 199 1

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

  • Filter: (("deletedAt" > now()) OR ("deletedAt" IS NULL))
  • Rows Removed by Filter: 9
42. 0.257 0.257 ↑ 1.0 1 257

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

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

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
44. 0.019 0.019 ↑ 7.8 6 1

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

  • Filter: (("deletedAt" > now()) OR ("deletedAt" IS NULL))
  • Rows Removed by Filter: 6
45. 0.297 0.506 ↓ 4.1 29 253

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

46. 0.043 0.209 ↓ 4.1 29 1

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

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

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

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

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

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
49. 0.023 0.023 ↓ 4.1 29 1

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

  • Filter: (("deletedAt" > now()) OR ("deletedAt" IS NULL))
  • Rows Removed by Filter: 1
50. 0.361 0.506 ↓ 4.1 29 253

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

51. 0.039 0.145 ↓ 4.1 29 1

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

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

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

  • Filter: (("deletedAt" > now()) OR ("deletedAt" IS NULL))
53. 0.015 0.031 ↓ 4.1 29 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
54. 0.016 0.016 ↓ 4.1 29 1

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

  • Filter: (("deletedAt" > now()) OR ("deletedAt" IS NULL))
  • Rows Removed by Filter: 1
Planning time : 4.784 ms
Execution time : 15.514 ms