explain.depesz.com

PostgreSQL's explain analyze made readable

Result: jzW6

Settings
# exclusive inclusive rows x rows loops node
1. 38.786 915.520 ↓ 5,811.5 11,623 1

Sort (cost=7,164.98..7,164.98 rows=2 width=562) (actual time=909.291..915.520 rows=11,623 loops=1)

  • Sort Key: e.published DESC
  • Sort Method: external merge Disk: 4928kB
2.          

CTE ep

3. 73.879 578.103 ↓ 200.4 11,623 1

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

4. 12.904 446.109 ↓ 100.2 11,623 1

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

5. 1.767 386.713 ↓ 100.2 11,623 1

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

6. 13.326 338.454 ↓ 100.2 11,623 1

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

7. 3.580 278.636 ↓ 100.2 11,623 1

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

8. 12.362 216.941 ↓ 100.2 11,623 1

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

9. 3.602 158.087 ↓ 100.2 11,623 1

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

10. 4.377 96.370 ↓ 100.2 11,623 1

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

11. 22.255 22.255 ↓ 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.255 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. 46.492 46.492 ↑ 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.004 rows=1 loops=11,623)

  • Filter: ((value ->> 'url'::text) = 'Program'::text)
  • Rows Removed by Filter: 4
17. 46.492 46.492 ↑ 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.004 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. 48.097 876.734 ↓ 5,811.5 11,623 1

Nested Loop (cost=21.72..78.18 rows=2 width=562) (actual time=633.730..876.734 rows=11,623 loops=1)

21. 3.250 782.145 ↓ 5,811.5 11,623 1

Nested Loop Left Join (cost=21.44..64.85 rows=2 width=3,369) (actual time=633.710..782.145 rows=11,623 loops=1)

22. 7.912 709.157 ↓ 5,811.5 11,623 1

Nested Loop Left Join (cost=21.03..49.07 rows=2 width=2,299) (actual time=633.697..709.157 rows=11,623 loops=1)

23. 11.400 701.245 ↓ 5,811.5 11,623 1

Nested Loop (cost=20.74..35.76 rows=2 width=1,345) (actual time=633.695..701.245 rows=11,623 loops=1)

24. 9.683 643.353 ↓ 5,811.5 11,623 1

Hash Right Join (cost=20.47..26.74 rows=2 width=424) (actual time=633.682..643.353 rows=11,623 loops=1)

  • Hash Cond: (((((d.content -> 'epiosdeid'::text) -> 'identifier'::text) ->> 'value'::text)) = e.id)
25. 0.004 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.004 0.004 ↓ 0.0 0 1

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

27. 19.237 633.662 ↓ 5,811.5 11,623 1

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

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

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

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

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

30. 0.008 0.012 ↑ 1.0 2 1

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

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

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

32. 46.492 46.492 ↑ 1.0 1 11,623

Index Scan using practitioner_pkey on practitioner p (cost=0.28..4.50 rows=1 width=958) (actual time=0.004..0.004 rows=1 loops=11,623)

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

Index Scan using organization_pkey on organization o_1 (cost=0.28..6.64 rows=1 width=991) (actual time=0.000..0.000 rows=0 loops=11,623)

  • Index Cond: (e.surgicalfacilityid = logical_id)
34. 69.738 69.738 ↑ 1.0 1 11,623

Index Scan using patient_pkey on patient pa (cost=0.41..7.88 rows=1 width=1,102) (actual time=0.005..0.006 rows=1 loops=11,623)

  • Index Cond: (logical_id = e.pat)
35. 46.492 46.492 ↑ 1.0 1 11,623

Index Scan using organization_pkey on organization o (cost=0.28..6.64 rows=1 width=991) (actual time=0.004..0.004 rows=1 loops=11,623)

  • Index Cond: (logical_id = e.org)