explain.depesz.com

PostgreSQL's explain analyze made readable

Result: GZmC

Settings
# exclusive inclusive rows x rows loops node
1. 5.303 463.118 ↓ 8,465.0 8,465 1

Nested Loop (cost=0.57..9,096.43 rows=1 width=133) (actual time=0.262..463.118 rows=8,465 loops=1)

2. 4.154 423.955 ↓ 8,465.0 8,465 1

Nested Loop (cost=0.29..9,088.46 rows=1 width=141) (actual time=0.238..423.955 rows=8,465 loops=1)

3. 2.132 377.476 ↓ 8,465.0 8,465 1

Nested Loop (cost=0.29..9,067.84 rows=1 width=149) (actual time=0.229..377.476 rows=8,465 loops=1)

4. 0.000 325.889 ↓ 9,891.0 9,891 1

Nested Loop (cost=0.00..9,059.87 rows=1 width=157) (actual time=0.197..325.889 rows=9,891 loops=1)

5. 4.987 238.056 ↓ 9,907.0 9,907 1

Nested Loop (cost=0.00..9,039.26 rows=1 width=141) (actual time=0.171..238.056 rows=9,907 loops=1)

6. 2.065 163.720 ↓ 9,907.0 9,907 1

Nested Loop (cost=0.00..9,022.87 rows=1 width=8) (actual time=0.118..163.720 rows=9,907 loops=1)

7. 42.771 42.771 ↓ 9,907.0 9,907 1

Seq Scan on patient_index sv4_1 (cost=0.00..8,862.03 rows=1 width=8) (actual time=0.078..42.771 rows=9,907 loops=1)

  • Filter: ((materialized_path ? '{org.common.*,org.2267490707683935235.unpartitioned.*,org.2267490707683935235.*}'::lquery[]) AND (fhir_core_code_search._search_reference_id('organization'::text, search_value) = '5fff161cba4c4f06bc844e2f635a0517'::text))
  • Rows Removed by Filter: 8534
8. 9.907 118.884 ↑ 3.0 1 9,907

Append (cost=0.00..160.81 rows=3 width=16) (actual time=0.011..0.012 rows=1 loops=9,907)

9. 0.000 0.000 ↓ 0.0 0 9,907

Seq Scan on resource_edge re4 (cost=0.00..0.00 rows=1 width=16) (actual time=0.000..0.000 rows=0 loops=9,907)

  • Filter: ((source_type = ANY ('{CarePlan}'::text[])) AND (materialized_path ? '{org.common.*,org.2267490707683935235.unpartitioned.*,org.2267490707683935235.*}'::lquery[]) AND (path = 'patient'::text) AND (sv4_1.content_pid = end_vertex))
10. 29.721 29.721 ↓ 0.0 0 9,907

Index Scan using stock_resource_edge_source_type_path_idx on stock_resource_edge re4_1 (cost=0.29..8.31 rows=1 width=16) (actual time=0.003..0.003 rows=0 loops=9,907)

  • Index Cond: ((source_type = ANY ('{CarePlan}'::text[])) AND (path = 'patient'::text))
  • Filter: ((materialized_path ? '{org.common.*,org.2267490707683935235.unpartitioned.*,org.2267490707683935235.*}'::lquery[]) AND (sv4_1.content_pid = end_vertex))
11. 59.442 79.256 ↑ 1.0 1 9,907

Bitmap Heap Scan on careplan_edge re4_2 (cost=4.71..152.50 rows=1 width=16) (actual time=0.007..0.008 rows=1 loops=9,907)

  • Recheck Cond: (end_vertex = sv4_1.content_pid)
  • Filter: ((source_type = ANY ('{CarePlan}'::text[])) AND (materialized_path ? '{org.common.*,org.2267490707683935235.unpartitioned.*,org.2267490707683935235.*}'::lquery[]) AND (path = 'patient'::text))
  • Rows Removed by Filter: 6
  • Heap Blocks: exact=13688
12. 19.814 19.814 ↑ 5.4 7 9,907

Bitmap Index Scan on careplan_edge_end_vertex_idx (cost=0.00..4.71 rows=38 width=0) (actual time=0.002..0.002 rows=7 loops=9,907)

  • Index Cond: (end_vertex = sv4_1.content_pid)
13. 9.907 69.349 ↑ 3.0 1 9,907

Append (cost=0.00..16.36 rows=3 width=133) (actual time=0.007..0.007 rows=1 loops=9,907)

14. 0.000 0.000 ↓ 0.0 0 9,907

Seq Scan on resource_index sv (cost=0.00..0.00 rows=1 width=104) (actual time=0.000..0.000 rows=0 loops=9,907)

  • Filter: ((type = ANY ('{CarePlan}'::text[])) AND (materialized_path ? '{org.common.*,org.2267490707683935235.unpartitioned.*,org.2267490707683935235.*}'::lquery[]) AND (re4.start_vertex = content_pid) AND (fhir_core_code_search._search_token_code('status'::text, search_value) = 'active'::text))
15. 9.907 9.907 ↓ 0.0 0 9,907

Index Scan using _indx_stock_resource_single_content_pid on stock_resource_index sv_1 (cost=0.29..7.99 rows=1 width=130) (actual time=0.001..0.001 rows=0 loops=9,907)

  • Index Cond: (content_pid = re4.start_vertex)
  • Filter: ((type = ANY ('{CarePlan}'::text[])) AND (materialized_path ? '{org.common.*,org.2267490707683935235.unpartitioned.*,org.2267490707683935235.*}'::lquery[]) AND (fhir_core_code_search._search_token_code('status'::text, search_value) = 'active'::text))
16. 49.535 49.535 ↑ 1.0 1 9,907

Index Scan using _indx_careplan_single_content_pid on careplan_index sv_2 (cost=0.29..8.37 rows=1 width=135) (actual time=0.005..0.005 rows=1 loops=9,907)

  • Index Cond: (content_pid = re4.start_vertex)
  • Filter: ((type = ANY ('{CarePlan}'::text[])) AND (materialized_path ? '{org.common.*,org.2267490707683935235.unpartitioned.*,org.2267490707683935235.*}'::lquery[]) AND (fhir_core_code_search._search_token_code('status'::text, search_value) = 'active'::text))
17. 9.907 89.163 ↑ 3.0 1 9,907

Append (cost=0.00..20.59 rows=3 width=16) (actual time=0.008..0.009 rows=1 loops=9,907)

18. 0.000 0.000 ↓ 0.0 0 9,907

Seq Scan on resource_edge re2 (cost=0.00..0.00 rows=1 width=16) (actual time=0.000..0.000 rows=0 loops=9,907)

  • Filter: ((source_type = ANY ('{CarePlan}'::text[])) AND (materialized_path ? '{org.common.*,org.2267490707683935235.unpartitioned.*,org.2267490707683935235.*}'::lquery[]) AND (path = 'assessment'::text) AND (sv.content_pid = start_vertex))
19. 9.907 9.907 ↓ 0.0 0 9,907

Index Scan using stock_resource_edge_start_vertex_idx on stock_resource_edge re2_1 (cost=0.29..7.71 rows=1 width=16) (actual time=0.001..0.001 rows=0 loops=9,907)

  • Index Cond: (start_vertex = sv.content_pid)
  • Filter: ((source_type = ANY ('{CarePlan}'::text[])) AND (materialized_path ? '{org.common.*,org.2267490707683935235.unpartitioned.*,org.2267490707683935235.*}'::lquery[]) AND (path = 'assessment'::text))
20. 69.349 69.349 ↑ 1.0 1 9,907

Index Scan using careplan_edge_start_vertex_end_vertex_path_materialized_pat_idx on careplan_edge re2_2 (cost=0.55..12.87 rows=1 width=16) (actual time=0.007..0.007 rows=1 loops=9,907)

  • Index Cond: ((start_vertex = sv.content_pid) AND (path = 'assessment'::text))
  • Filter: ((source_type = ANY ('{CarePlan}'::text[])) AND (materialized_path ? '{org.common.*,org.2267490707683935235.unpartitioned.*,org.2267490707683935235.*}'::lquery[]))
21. 49.455 49.455 ↑ 1.0 1 9,891

Index Scan using _indx_riskassessment_single_content_pid on riskassessment_index sv2_1 (cost=0.29..7.96 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=9,891)

  • Index Cond: (content_pid = re2.end_vertex)
  • Filter: ((materialized_path ? '{org.common.*,org.2267490707683935235.unpartitioned.*,org.2267490707683935235.*}'::lquery[]) AND (fhir_core_code_search._search_token_code('status'::text, search_value) = 'final'::text))
  • Rows Removed by Filter: 0
22. 8.465 42.325 ↑ 3.0 1 8,465

Append (cost=0.00..20.59 rows=3 width=16) (actual time=0.005..0.005 rows=1 loops=8,465)

23. 0.000 0.000 ↓ 0.0 0 8,465

Seq Scan on resource_edge re3 (cost=0.00..0.00 rows=1 width=16) (actual time=0.000..0.000 rows=0 loops=8,465)

  • Filter: ((source_type = ANY ('{CarePlan}'::text[])) AND (materialized_path ? '{org.common.*,org.2267490707683935235.unpartitioned.*,org.2267490707683935235.*}'::lquery[]) AND (path = 'assessment'::text) AND (re2.start_vertex = start_vertex))
24. 8.465 8.465 ↓ 0.0 0 8,465

Index Scan using stock_resource_edge_start_vertex_idx on stock_resource_edge re3_1 (cost=0.29..7.71 rows=1 width=16) (actual time=0.001..0.001 rows=0 loops=8,465)

  • Index Cond: (start_vertex = re2.start_vertex)
  • Filter: ((source_type = ANY ('{CarePlan}'::text[])) AND (materialized_path ? '{org.common.*,org.2267490707683935235.unpartitioned.*,org.2267490707683935235.*}'::lquery[]) AND (path = 'assessment'::text))
25. 25.395 25.395 ↑ 1.0 1 8,465

Index Scan using careplan_edge_start_vertex_end_vertex_path_materialized_pat_idx on careplan_edge re3_2 (cost=0.55..12.87 rows=1 width=16) (actual time=0.003..0.003 rows=1 loops=8,465)

  • Index Cond: ((start_vertex = re2.start_vertex) AND (path = 'assessment'::text))
  • Filter: ((source_type = ANY ('{CarePlan}'::text[])) AND (materialized_path ? '{org.common.*,org.2267490707683935235.unpartitioned.*,org.2267490707683935235.*}'::lquery[]))
26. 33.860 33.860 ↑ 1.0 1 8,465

Index Scan using _indx_riskassessment_multi_content_pid_ on riskassessment_multivalued_index mv3_1 (cost=0.29..7.96 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=8,465)

  • Index Cond: (content_pid = re3.end_vertex)
  • Filter: ((materialized_path ? '{org.common.*,org.2267490707683935235.unpartitioned.*,org.2267490707683935235.*}'::lquery[]) AND (param_name = 'risk'::text) AND (param_type = 'token'::text) AND (fhir_core_code_search._search_token_code(search_value) = 'high'::text))
Planning time : 7.403 ms
Execution time : 463.598 ms