explain.depesz.com

PostgreSQL's explain analyze made readable

Result: CIM1

Settings
# exclusive inclusive rows x rows loops node
1. 0.272 61,297.856 ↓ 0.0 0 1

Limit (cost=218,471.55..218,471.57 rows=10 width=1,081) (actual time=61,297.856..61,297.856 rows=0 loops=1)

2.          

CTE careteamparticipantjson

3. 0.050 0.505 ↑ 139,900.0 1 1

Bitmap Heap Scan on careteam (cost=74.84..5,213.23 rows=139,900 width=757) (actual time=0.505..0.505 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.455 0.455 ↑ 699.5 2 1

Bitmap Index Scan on careteam_subject_reference (cost=0.00..74.49 rows=1,399 width=0) (actual time=0.455..0.455 rows=2 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.528 0.528 ↑ 139,900.0 1 1

CTE Scan on careteamparticipantjson (cost=0.00..6,645.25 rows=139,900 width=64) (actual time=0.528..0.528 rows=1 loops=1)

7. 0.284 61,297.584 ↓ 0.0 0 1

Sort (cost=206,613.07..206,614.82 rows=699 width=1,081) (actual time=61,297.584..61,297.584 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.611 61,297.300 ↓ 0.0 0 1

WindowAgg (cost=203,079.62..206,597.96 rows=699 width=1,081) (actual time=61,297.300..61,297.300 rows=0 loops=1)

9. 0.437 61,296.689 ↓ 0.0 0 1

Hash Join (cost=203,079.62..206,409.23 rows=699 width=1,081) (actual time=61,296.689..61,296.689 rows=0 loops=1)

  • Hash Cond: (careteamparticipant.reference = practitioner.id)
10. 0.532 0.532 ↑ 139,900.0 1 1

CTE Scan on careteamparticipant (cost=0.00..2,798.00 rows=139,900 width=160) (actual time=0.532..0.532 rows=1 loops=1)

11. 0.600 61,295.720 ↓ 0.0 0 1

Hash (cost=203,047.63..203,047.63 rows=2,559 width=953) (actual time=61,295.720..61,295.720 rows=0 loops=1)

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

Seq Scan on practitioner (cost=0.00..203,047.63 rows=2,559 width=953) (actual time=61,295.120..61,295.120 rows=0 loops=1)

  • Filter: (fhir_extract_as_string_array((resource)::json, '[{ "path": ["Practitioner","identifier","assigner","display"], "elementType":"string"}]'::json) && '{"\"^^GP$$\""}'::text[])
  • Rows Removed by Filter: 513,006
Planning time : 12.706 ms
Execution time : 61,302.277 ms