explain.depesz.com

PostgreSQL's explain analyze made readable

Result: DJlLK

Settings
# exclusive inclusive rows x rows loops node
1. 14.643 105,541.508 ↓ 8,465.0 8,465 1

Nested Loop (cost=332.55..6,674.55 rows=1 width=133) (actual time=13,282.370..105,541.508 rows=8,465 loops=1)

2. 19.376 105,309.307 ↓ 9,889.0 9,889 1

Nested Loop (cost=332.26..6,666.58 rows=1 width=141) (actual time=13.278..105,309.307 rows=9,889 loops=1)

3. 27,120.774 105,072.373 ↓ 9,889.0 9,889 1

Nested Loop (cost=332.26..6,645.96 rows=1 width=149) (actual time=13.247..105,072.373 rows=9,889 loops=1)

  • Join Filter: (re4.end_vertex = sv4_1.content_pid)
  • Rows Removed by Join Filter: 97960434
4. 14.936 827.288 ↓ 9,889.0 9,889 1

Nested Loop (cost=0.29..212.52 rows=1 width=157) (actual time=0.146..827.288 rows=9,889 loops=1)

5. 16.708 634.350 ↓ 9,889.0 9,889 1

Nested Loop (cost=0.29..191.91 rows=1 width=141) (actual time=0.121..634.350 rows=9,889 loops=1)

6. 22.823 340.750 ↓ 9,889.0 9,889 1

Nested Loop (cost=0.29..175.36 rows=1 width=8) (actual time=0.049..340.750 rows=9,889 loops=1)

7. 50.924 50.924 ↓ 9,889.0 9,889 1

Index Scan using _indx_riskassessment_multivalued_index_search_risk_code on riskassessment_multivalued_index mv3_1 (cost=0.29..14.51 rows=1 width=8) (actual time=0.021..50.924 rows=9,889 loops=1)

  • Index Cond: (fhir_core_code_search._search_token_code(search_value) = 'high'::text)
  • Filter: ((materialized_path ? '{org.common.*,org.2267490707683935235.unpartitioned.*,org.2267490707683935235.*}'::lquery[]) AND (param_type = 'token'::text))
  • Rows Removed by Filter: 8522
8. 79.112 267.003 ↑ 3.0 1 9,889

Append (cost=0.00..160.81 rows=3 width=16) (actual time=0.026..0.027 rows=1 loops=9,889)

9. 0.000 0.000 ↓ 0.0 0 9,889

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

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

Index Scan using stock_resource_edge_source_type_path_idx on stock_resource_edge re3_1 (cost=0.29..8.31 rows=1 width=16) (actual time=0.008..0.008 rows=0 loops=9,889)

  • Index Cond: ((source_type = ANY ('{CarePlan}'::text[])) AND (path = 'assessment'::text))
  • Filter: ((materialized_path ? '{org.common.*,org.2267490707683935235.unpartitioned.*,org.2267490707683935235.*}'::lquery[]) AND (mv3_1.content_pid = end_vertex))
11. 59.334 108.779 ↑ 1.0 1 9,889

Bitmap Heap Scan on careplan_edge re3_2 (cost=4.71..152.50 rows=1 width=16) (actual time=0.010..0.011 rows=1 loops=9,889)

  • Recheck Cond: (end_vertex = mv3_1.content_pid)
  • Filter: ((source_type = ANY ('{CarePlan}'::text[])) AND (materialized_path ? '{org.common.*,org.2267490707683935235.unpartitioned.*,org.2267490707683935235.*}'::lquery[]) AND (path = 'assessment'::text))
  • Heap Blocks: exact=9889
12. 49.445 49.445 ↑ 38.0 1 9,889

Bitmap Index Scan on careplan_edge_end_vertex_idx (cost=0.00..4.71 rows=38 width=0) (actual time=0.005..0.005 rows=1 loops=9,889)

  • Index Cond: (end_vertex = mv3_1.content_pid)
13. 39.556 276.892 ↑ 3.0 1 9,889

Append (cost=0.00..16.52 rows=3 width=133) (actual time=0.027..0.028 rows=1 loops=9,889)

14. 0.000 0.000 ↓ 0.0 0 9,889

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,889)

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

Index Scan using _indx_stock_resource_single_content_pid on stock_resource_index sv_1 (cost=0.29..8.16 rows=1 width=130) (actual time=0.003..0.003 rows=0 loops=9,889)

  • Index Cond: (content_pid = re3.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. 207.669 207.669 ↑ 1.0 1 9,889

Index Scan using _indx_careplan_single_content_pid on careplan_index sv_2 (cost=0.29..8.36 rows=1 width=135) (actual time=0.020..0.021 rows=1 loops=9,889)

  • Index Cond: (content_pid = re3.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. 29.667 178.002 ↑ 3.0 1 9,889

Append (cost=0.00..20.59 rows=3 width=16) (actual time=0.016..0.018 rows=1 loops=9,889)

18. 0.000 0.000 ↓ 0.0 0 9,889

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,889)

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

Index Scan using stock_resource_edge_start_vertex_idx on stock_resource_edge re4_1 (cost=0.29..7.71 rows=1 width=16) (actual time=0.002..0.002 rows=0 loops=9,889)

  • 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 = 'patient'::text))
20. 128.557 128.557 ↑ 1.0 1 9,889

Index Scan using careplan_edge_start_vertex_end_vertex_path_materialized_pat_idx on careplan_edge re4_2 (cost=0.55..12.87 rows=1 width=16) (actual time=0.011..0.013 rows=1 loops=9,889)

  • Index Cond: ((start_vertex = sv.content_pid) AND (path = 'patient'::text))
  • Filter: ((source_type = ANY ('{CarePlan}'::text[])) AND (materialized_path ? '{org.common.*,org.2267490707683935235.unpartitioned.*,org.2267490707683935235.*}'::lquery[]))
21. 68,224.211 77,124.311 ↓ 1,238.4 9,907 9,889

Bitmap Heap Scan on patient_index sv4_1 (cost=331.97..6,433.34 rows=8 width=8) (actual time=1.276..7.799 rows=9,907 loops=9,889)

  • Recheck Cond: (fhir_core_code_search._search_reference_id('organization'::text, search_value) = '5fff161cba4c4f06bc844e2f635a0517'::text)
  • Filter: (materialized_path ? '{org.common.*,org.2267490707683935235.unpartitioned.*,org.2267490707683935235.*}'::lquery[])
  • Heap Blocks: exact=34453276
22. 8,900.100 8,900.100 ↓ 1.2 9,907 9,889

Bitmap Index Scan on _indx_patient_index_search_organization_id (cost=0.00..331.97 rows=7,958 width=0) (actual time=0.900..0.900 rows=9,907 loops=9,889)

  • Index Cond: (fhir_core_code_search._search_reference_id('organization'::text, search_value) = '5fff161cba4c4f06bc844e2f635a0517'::text)
23. 59.334 217.558 ↑ 3.0 1 9,889

Append (cost=0.00..20.59 rows=3 width=16) (actual time=0.021..0.022 rows=1 loops=9,889)

24. 0.000 0.000 ↓ 0.0 0 9,889

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,889)

  • 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))
25. 39.556 39.556 ↓ 0.0 0 9,889

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.004..0.004 rows=0 loops=9,889)

  • 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))
26. 118.668 118.668 ↑ 1.0 1 9,889

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.011..0.012 rows=1 loops=9,889)

  • 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[]))
27. 217.558 217.558 ↑ 1.0 1 9,889

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.022..0.022 rows=1 loops=9,889)

  • 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
Planning time : 7.607 ms
Execution time : 105,543.532 ms