explain.depesz.com

PostgreSQL's explain analyze made readable

Result: dSPI

Settings
# exclusive inclusive rows x rows loops node
1. 0.010 636.280 ↓ 0.0 0 1

Sort (cost=7,164.98..7,164.98 rows=2 width=562) (actual time=636.280..636.280 rows=0 loops=1)

  • Sort Key: e.published DESC
  • Sort Method: quicksort Memory: 25kB
2.          

CTE ep

3. 75.030 577.441 ↓ 200.4 11,623 1

Nested Loop Semi Join (cost=0.32..7,086.79 rows=58 width=397) (actual time=0.186..577.441 rows=11,623 loops=1)

4. 11.947 444.296 ↓ 100.2 11,623 1

Nested Loop Left Join (cost=0.04..6,535.41 rows=116 width=1,763) (actual time=0.145..444.296 rows=11,623 loops=1)

5. 12.942 385.857 ↓ 100.2 11,623 1

Nested Loop Left Join (cost=0.03..6,360.25 rows=116 width=1,731) (actual time=0.130..385.857 rows=11,623 loops=1)

6. 2.174 338.046 ↓ 100.2 11,623 1

Nested Loop Left Join (cost=0.03..6,185.08 rows=116 width=1,699) (actual time=0.110..338.046 rows=11,623 loops=1)

7. 3.086 277.757 ↓ 100.2 11,623 1

Nested Loop Left Join (cost=0.02..6,009.92 rows=116 width=1,667) (actual time=0.096..277.757 rows=11,623 loops=1)

8. 12.142 216.556 ↓ 100.2 11,623 1

Nested Loop Left Join (cost=0.01..5,834.76 rows=116 width=1,635) (actual time=0.082..216.556 rows=11,623 loops=1)

9. 3.659 157.922 ↓ 100.2 11,623 1

Nested Loop Left Join (cost=0.01..5,659.59 rows=116 width=1,603) (actual time=0.067..157.922 rows=11,623 loops=1)

10. 4.178 96.148 ↓ 100.2 11,623 1

Nested Loop Left Join (cost=0.01..5,484.43 rows=116 width=1,571) (actual time=0.050..96.148 rows=11,623 loops=1)

11. 22.232 22.232 ↓ 100.2 11,623 1

Seq Scan on episodeofcare e_1 (cost=0.00..5,309.26 rows=116 width=1,539) (actual time=0.016..22.232 rows=11,623 loops=1)

  • Filter: ((content ->> 'status'::text) = 'active'::text)
  • Rows Removed by Filter: 11649
12. 69.738 69.738 ↓ 0.0 0 11,623

Function Scan on jsonb_array_elements surgicalvalue (cost=0.01..1.50 rows=1 width=32) (actual time=0.006..0.006 rows=0 loops=11,623)

  • Filter: ((value ->> 'url'::text) = 'surgicalOrganization'::text)
  • Rows Removed by Filter: 5
13. 58.115 58.115 ↑ 1.0 1 11,623

Function Scan on jsonb_array_elements extvalue (cost=0.01..1.50 rows=1 width=32) (actual time=0.004..0.005 rows=1 loops=11,623)

  • Filter: ((value ->> 'url'::text) = 'practitioner'::text)
  • Rows Removed by Filter: 4
14. 46.492 46.492 ↓ 0.0 0 11,623

Function Scan on jsonb_array_elements surgerydate (cost=0.01..1.50 rows=1 width=32) (actual time=0.004..0.004 rows=0 loops=11,623)

  • Filter: ((value ->> 'url'::text) = 'surgeryDate'::text)
  • Rows Removed by Filter: 5
15. 58.115 58.115 ↑ 1.0 1 11,623

Function Scan on jsonb_array_elements initiatedon (cost=0.01..1.50 rows=1 width=32) (actual time=0.004..0.005 rows=1 loops=11,623)

  • Filter: ((value ->> 'url'::text) = 'InitiatedOn'::text)
  • Rows Removed by Filter: 4
16. 58.115 58.115 ↑ 1.0 1 11,623

Function Scan on jsonb_array_elements programvalue (cost=0.01..1.50 rows=1 width=32) (actual time=0.004..0.005 rows=1 loops=11,623)

  • Filter: ((value ->> 'url'::text) = 'Program'::text)
  • Rows Removed by Filter: 4
17. 34.869 34.869 ↑ 1.0 1 11,623

Function Scan on jsonb_array_elements identifervalue (cost=0.01..1.50 rows=1 width=32) (actual time=0.003..0.003 rows=1 loops=11,623)

  • Filter: ((value ->> 'system'::text) = 'EpisodeCode'::text)
  • Rows Removed by Filter: 1
18. 46.492 46.492 ↓ 0.0 0 11,623

Function Scan on jsonb_array_elements procedurevalue (cost=0.01..1.50 rows=1 width=32) (actual time=0.004..0.004 rows=0 loops=11,623)

  • Filter: ((value ->> 'url'::text) = 'procedureCode'::text)
  • Rows Removed by Filter: 5
19. 58.115 58.115 ↑ 1.0 1 11,623

Index Only Scan using organization_pkey on organization (cost=0.29..5.62 rows=1 width=37) (actual time=0.005..0.005 rows=1 loops=11,623)

  • Index Cond: (logical_id = (((e_1.content -> 'managingOrganization'::text) -> 'identifier'::text) ->> 'value'::text))
  • Heap Fetches: 11623
20. 0.001 636.270 ↓ 0.0 0 1

Nested Loop (cost=21.72..78.18 rows=2 width=562) (actual time=636.270..636.270 rows=0 loops=1)

21. 0.000 636.269 ↓ 0.0 0 1

Nested Loop (cost=21.44..64.85 rows=2 width=3,369) (actual time=636.269..636.269 rows=0 loops=1)

22. 0.000 636.269 ↓ 0.0 0 1

Nested Loop (cost=21.03..49.07 rows=2 width=2,299) (actual time=636.269..636.269 rows=0 loops=1)

23. 0.002 636.269 ↓ 0.0 0 1

Nested Loop (cost=20.74..35.76 rows=2 width=1,345) (actual time=636.269..636.269 rows=0 loops=1)

24. 3.719 636.267 ↓ 0.0 0 1

Hash Join (cost=20.47..26.74 rows=2 width=424) (actual time=636.267..636.267 rows=0 loops=1)

  • Hash Cond: (((((d.content -> 'epiosdeid'::text) -> 'identifier'::text) ->> 'value'::text)) = e.id)
25. 0.003 0.008 ↓ 0.0 0 1

HashAggregate (cost=18.00..21.50 rows=200 width=32) (actual time=0.008..0.008 rows=0 loops=1)

  • Group Key: (((d.content -> 'epiosdeid'::text) -> 'identifier'::text) ->> 'value'::text)
26. 0.005 0.005 ↓ 0.0 0 1

Seq Scan on documents d (cost=0.00..17.00 rows=400 width=32) (actual time=0.005..0.005 rows=0 loops=1)

27. 18.598 632.540 ↓ 5,811.5 11,623 1

Hash (cost=2.44..2.44 rows=2 width=392) (actual time=632.540..632.540 rows=11,623 loops=1)

  • Buckets: 8192 (originally 1024) Batches: 2 (originally 1) Memory Usage: 4033kB
28. 11.733 613.942 ↓ 5,811.5 11,623 1

Hash Join (cost=1.04..2.44 rows=2 width=392) (actual time=0.215..613.942 rows=11,623 loops=1)

  • Hash Cond: (e.programid = pl.logical_id)
29. 602.194 602.194 ↓ 200.4 11,623 1

CTE Scan on ep e (cost=0.00..1.16 rows=58 width=392) (actual time=0.189..602.194 rows=11,623 loops=1)

30. 0.008 0.015 ↑ 1.0 2 1

Hash (cost=1.02..1.02 rows=2 width=64) (actual time=0.015..0.015 rows=2 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
31. 0.007 0.007 ↑ 1.0 2 1

Seq Scan on plantype pl (cost=0.00..1.02 rows=2 width=64) (actual time=0.005..0.007 rows=2 loops=1)

32. 0.000 0.000 ↓ 0.0 0

Index Scan using practitioner_pkey on practitioner p (cost=0.28..4.50 rows=1 width=958) (never executed)

  • Index Cond: (logical_id = e.initiatorid)
33. 0.000 0.000 ↓ 0.0 0

Index Scan using organization_pkey on organization o_1 (cost=0.28..6.64 rows=1 width=991) (never executed)

  • Index Cond: (logical_id = e.surgicalfacilityid)
34. 0.000 0.000 ↓ 0.0 0

Index Scan using patient_pkey on patient pa (cost=0.41..7.88 rows=1 width=1,102) (never executed)

  • Index Cond: (logical_id = e.pat)
35. 0.000 0.000 ↓ 0.0 0

Index Scan using organization_pkey on organization o (cost=0.28..6.64 rows=1 width=991) (never executed)

  • Index Cond: (logical_id = e.org)