explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 6WAH

Settings
# exclusive inclusive rows x rows loops node
1. 0.001 0.528 ↓ 0.0 0 1

Limit (cost=24,650.04..24,650.07 rows=10 width=1,080) (actual time=0.528..0.528 rows=0 loops=1)

2.          

CTE careteamparticipantjson

3. 0.016 0.482 ↑ 138,700.0 1 1

Bitmap Heap Scan on careteam (cost=82.75..5,183.47 rows=138,700 width=757) (actual time=0.482..0.482 rows=1 loops=1)

  • Recheck Cond: (fhir_extract_as_reference((resource)::json, '[{"path":["CareTeam","subject"],"elementType":"Reference"}]'::json) && '{patient/c6df6d1b-b413-4bde-aab8-4ef7ba6c3e3b,patient/f9ec5e82-adb8-4ad4-8cfb-0d47550691ce,patient/87da91ba-3d08-4ce6-b9f3-c803ef129b7d}'::text[])
  • Heap Blocks: exact=1
4. 0.466 0.466 ↑ 1,387.0 1 1

Bitmap Index Scan on careteam_subject_reference (cost=0.00..82.41 rows=1,387 width=0) (actual time=0.466..0.466 rows=1 loops=1)

  • Index Cond: (fhir_extract_as_reference((resource)::json, '[{"path":["CareTeam","subject"],"elementType":"Reference"}]'::json) && '{patient/c6df6d1b-b413-4bde-aab8-4ef7ba6c3e3b,patient/f9ec5e82-adb8-4ad4-8cfb-0d47550691ce,patient/87da91ba-3d08-4ce6-b9f3-c803ef129b7d}'::text[])
5.          

CTE careteamparticipant

6. 0.494 0.494 ↑ 138,700.0 1 1

CTE Scan on careteamparticipantjson (cost=0.00..6,588.25 rows=138,700 width=64) (actual time=0.494..0.494 rows=1 loops=1)

7. 0.007 0.527 ↓ 0.0 0 1

Sort (cost=12,878.32..12,880.05 rows=693 width=1,080) (actual time=0.527..0.527 rows=0 loops=1)

  • Sort Key: careteamparticipant.period_start DESC, (fhir_extract_as_string((practitioner.resource)::json, '[{"path": ["Practitioner","identifier","assigner","display"], "elementType":"string"}]'::json)), practitioner.id, careteamparticipant.id
  • Sort Method: quicksort Memory: 25kB
8. 0.001 0.520 ↓ 0.0 0 1

WindowAgg (cost=9,375.18..12,863.35 rows=693 width=1,080) (actual time=0.520..0.520 rows=0 loops=1)

9. 0.010 0.519 ↓ 0.0 0 1

Hash Join (cost=9,375.18..12,676.24 rows=693 width=1,080) (actual time=0.519..0.519 rows=0 loops=1)

  • Hash Cond: (careteamparticipant.reference = practitioner.id)
10. 0.496 0.496 ↑ 138,700.0 1 1

CTE Scan on careteamparticipant (cost=0.00..2,774.00 rows=138,700 width=160) (actual time=0.496..0.496 rows=1 loops=1)

11. 0.000 0.013 ↓ 0.0 0 1

Hash (cost=9,343.17..9,343.17 rows=2,561 width=952) (actual time=0.013..0.013 rows=0 loops=1)

  • Buckets: 4,096 Batches: 1 Memory Usage: 32kB
12. 0.002 0.013 ↓ 0.0 0 1

Bitmap Heap Scan on practitioner (cost=75.85..9,343.17 rows=2,561 width=952) (actual time=0.013..0.013 rows=0 loops=1)

  • Recheck Cond: (fhir_extract_as_string_array((resource)::json, '[{"path": ["Practitioner","identifier","assigner","display"], "elementType":"string"}]'::json) && '{"\"^^GP$$\""}'::text[])
13. 0.011 0.011 ↓ 0.0 0 1

Bitmap Index Scan on practitioner_identifier_assigner_display_string_array (cost=0.00..75.21 rows=2,561 width=0) (actual time=0.011..0.011 rows=0 loops=1)

  • Index Cond: (fhir_extract_as_string_array((resource)::json, '[{"path": ["Practitioner","identifier","assigner","display"], "elementType":"string"}]'::json) && '{"\"^^GP$$\""}'::text[])
Planning time : 0.341 ms
Execution time : 0.610 ms