explain.depesz.com

PostgreSQL's explain analyze made readable

Result: cH7t

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

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

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

CTE ep

3. 73.576 575.295 ↓ 200.4 11,623 1

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

4. 12.545 443.604 ↓ 100.2 11,623 1

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

5. 12.922 384.567 ↓ 100.2 11,623 1

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

6. 13.130 336.776 ↓ 100.2 11,623 1

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

7. 3.268 277.154 ↓ 100.2 11,623 1

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

8. 12.599 215.771 ↓ 100.2 11,623 1

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

9. 3.446 156.680 ↓ 100.2 11,623 1

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

10. 3.322 95.119 ↓ 100.2 11,623 1

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

11. 22.059 22.059 ↓ 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.013..22.059 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. 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. 47.260 869.247 ↓ 5,811.5 11,623 1

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

21. 3.519 775.495 ↓ 5,811.5 11,623 1

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

22. 7.866 702.238 ↓ 5,811.5 11,623 1

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

23. 11.170 694.372 ↓ 5,811.5 11,623 1

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

24. 9.896 636.710 ↓ 5,811.5 11,623 1

Hash Right Join (cost=20.47..26.74 rows=2 width=424) (actual time=626.820..636.710 rows=11,623 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.106 626.806 ↓ 5,811.5 11,623 1

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

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

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

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

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

30. 0.003 0.008 ↑ 1.0 2 1

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

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

Seq Scan on plantype pl (cost=0.00..1.02 rows=2 width=64) (actual time=0.005..0.005 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)