explain.depesz.com

PostgreSQL's explain analyze made readable

Result: muUT

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

Limit (cost=15,699.66..15,699.68 rows=9 width=1,081) (actual time=130.996..130.996 rows=0 loops=1)

2.          

CTE careteamparticipantjson

3. 1.017 11.640 ↑ 1,900.0 1 1

Bitmap Heap Scan on careteam (cost=56.15..144.58 rows=1,900 width=756) (actual time=11.622..11.640 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=2
4. 10.623 10.623 ↑ 9.5 2 1

Bitmap Index Scan on careteam_subject_reference (cost=0.00..56.14 rows=19 width=0) (actual time=10.623..10.623 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. 11.663 11.663 ↑ 1,900.0 1 1

CTE Scan on careteamparticipantjson (cost=0.00..90.25 rows=1,900 width=64) (actual time=11.643..11.663 rows=1 loops=1)

7. 25.710 130.995 ↓ 0.0 0 1

Sort (cost=15,464.82..15,464.85 rows=9 width=1,081) (actual time=130.995..130.995 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.005 105.285 ↓ 0.0 0 1

WindowAgg (cost=0.42..15,464.68 rows=9 width=1,081) (actual time=105.285..105.285 rows=0 loops=1)

9. 0.005 105.280 ↓ 0.0 0 1

Nested Loop (cost=0.42..15,462.25 rows=9 width=1,081) (actual time=105.280..105.280 rows=0 loops=1)

10. 11.667 11.667 ↑ 1,900.0 1 1

CTE Scan on careteamparticipant (cost=0.00..38.00 rows=1,900 width=160) (actual time=11.646..11.667 rows=1 loops=1)

11. 93.608 93.608 ↓ 0.0 0 1

Index Scan using practitioner_pkey on practitioner (cost=0.42..8.11 rows=1 width=953) (actual time=93.608..93.608 rows=0 loops=1)

  • Index Cond: (id = careteamparticipant.reference)
  • Filter: (fhir_extract_as_string_array((resource)::json, '[{"path": ["Practitioner","identifier","assigner","display"], "elementType":"string"}]'::json) && '{"\"^^GP$$\""}'::text[])
  • Rows Removed by Filter: 1
Planning time : 6.460 ms
Execution time : 131.913 ms