explain.depesz.com

PostgreSQL's explain analyze made readable

Result: TM2q

Settings
# exclusive inclusive rows x rows loops node
1. 1.718 91,835.417 ↓ 2,271.0 2,271 1

Nested Loop (cost=0.57..9,518.99 rows=1 width=133) (actual time=72,612.750..91,835.417 rows=2,271 loops=1)

2. 2.787 91,810.969 ↓ 2,273.0 2,273 1

Nested Loop (cost=0.29..9,510.96 rows=1 width=141) (actual time=72,596.607..91,810.969 rows=2,273 loops=1)

3. 15.162 91,753.630 ↓ 2,273.0 2,273 1

Nested Loop (cost=0.29..9,494.72 rows=1 width=32) (actual time=72,596.486..91,753.630 rows=2,273 loops=1)

4. 11.224 648.014 ↓ 10,738.0 10,738 1

Nested Loop (cost=0.00..9,473.48 rows=1 width=24) (actual time=0.123..648.014 rows=10,738 loops=1)

5. 11.710 475.315 ↓ 10,765.0 10,765 1

Nested Loop (cost=0.00..9,452.84 rows=1 width=8) (actual time=0.099..475.315 rows=10,765 loops=1)

6. 194.480 194.480 ↓ 10,765.0 10,765 1

Seq Scan on patient_index sv4_1 (cost=0.00..9,284.24 rows=1 width=8) (actual time=0.062..194.480 rows=10,765 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
7. 43.060 269.125 ↑ 3.0 1 10,765

Append (cost=0.00..168.58 rows=3 width=16) (actual time=0.022..0.025 rows=1 loops=10,765)

8. 0.000 0.000 ↓ 0.0 0 10,765

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

  • 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))
9. 64.590 64.590 ↓ 0.0 0 10,765

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.006..0.006 rows=0 loops=10,765)

  • 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))
10. 107.650 161.475 ↑ 1.0 1 10,765

Bitmap Heap Scan on careplan_edge re4_2 (cost=4.73..160.26 rows=1 width=16) (actual time=0.012..0.015 rows=1 loops=10,765)

  • 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=14944
11. 53.825 53.825 ↑ 5.7 7 10,765

Bitmap Index Scan on careplan_edge_end_vertex_idx (cost=0.00..4.72 rows=40 width=0) (actual time=0.005..0.005 rows=7 loops=10,765)

  • Index Cond: (end_vertex = sv4_1.content_pid)
12. 21.530 161.475 ↑ 3.0 1 10,765

Append (cost=0.00..20.61 rows=3 width=16) (actual time=0.014..0.015 rows=1 loops=10,765)

13. 0.000 0.000 ↓ 0.0 0 10,765

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

  • Filter: ((source_type = ANY ('{CarePlan}'::text[])) AND (materialized_path ? '{org.common.*,org.2267490707683935235.unpartitioned.*,org.2267490707683935235.*}'::lquery[]) AND (path = 'assessment'::text) AND (re4.start_vertex = start_vertex))
14. 10.765 10.765 ↓ 0.0 0 10,765

Index Scan using stock_resource_edge_start_vertex_idx on stock_resource_edge re3_1 (cost=0.29..7.74 rows=1 width=16) (actual time=0.001..0.001 rows=0 loops=10,765)

  • Index Cond: (start_vertex = re4.start_vertex)
  • Filter: ((source_type = ANY ('{CarePlan}'::text[])) AND (materialized_path ? '{org.common.*,org.2267490707683935235.unpartitioned.*,org.2267490707683935235.*}'::lquery[]) AND (path = 'assessment'::text))
15. 129.180 129.180 ↑ 1.0 1 10,765

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

  • Index Cond: ((start_vertex = re4.start_vertex) AND (path = 'assessment'::text))
  • Filter: ((source_type = ANY ('{CarePlan}'::text[])) AND (materialized_path ? '{org.common.*,org.2267490707683935235.unpartitioned.*,org.2267490707683935235.*}'::lquery[]))
16. 655.018 91,090.454 ↓ 0.0 0 10,738

Nested Loop (cost=0.29..21.23 rows=1 width=8) (actual time=7.610..8.483 rows=0 loops=10,738)

17. 17,116.372 17,116.372 ↓ 2,276.0 2,276 10,738

Index Scan using _indx_riskassessment_index_search_status_code on riskassessment_index sv2_1 (cost=0.29..4.31 rows=1 width=8) (actual time=0.009..1.594 rows=2,276 loops=10,738)

  • Index Cond: (fhir_core_code_search._search_token_code('status'::text, search_value) = 'preliminary'::text)
  • Filter: (materialized_path ? '{org.common.*,org.2267490707683935235.unpartitioned.*,org.2267490707683935235.*}'::lquery[])
18. 24,439.688 73,319.064 ↓ 0.0 0 24,439,688

Append (cost=0.00..16.89 rows=3 width=16) (actual time=0.003..0.003 rows=0 loops=24,439,688)

19. 0.000 0.000 ↓ 0.0 0 24,439,688

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

  • Filter: ((source_type = ANY ('{CarePlan}'::text[])) AND (materialized_path ? '{org.common.*,org.2267490707683935235.unpartitioned.*,org.2267490707683935235.*}'::lquery[]) AND (path = 'assessment'::text) AND (re3.start_vertex = start_vertex) AND (sv2_1.content_pid = end_vertex))
20. 24,439.688 24,439.688 ↓ 0.0 0 24,439,688

Index Scan using stock_resource_edge_start_vertex_idx on stock_resource_edge re2_1 (cost=0.29..8.31 rows=1 width=16) (actual time=0.001..0.001 rows=0 loops=24,439,688)

  • Index Cond: (start_vertex = re3.start_vertex)
  • Filter: ((source_type = ANY ('{CarePlan}'::text[])) AND (materialized_path ? '{org.common.*,org.2267490707683935235.unpartitioned.*,org.2267490707683935235.*}'::lquery[]) AND (path = 'assessment'::text) AND (sv2_1.content_pid = end_vertex))
21. 24,439.688 24,439.688 ↓ 0.0 0 24,439,688

Index Scan using careplan_edge_start_vertex_end_vertex_path_materialized_pat_idx on careplan_edge re2_2 (cost=0.55..8.58 rows=1 width=16) (actual time=0.001..0.001 rows=0 loops=24,439,688)

  • Index Cond: ((start_vertex = re3.start_vertex) AND (end_vertex = sv2_1.content_pid) AND (path = 'assessment'::text))
  • Filter: ((source_type = ANY ('{CarePlan}'::text[])) AND (materialized_path ? '{org.common.*,org.2267490707683935235.unpartitioned.*,org.2267490707683935235.*}'::lquery[]))
22. 4.546 54.552 ↑ 3.0 1 2,273

Append (cost=0.00..16.21 rows=3 width=133) (actual time=0.024..0.024 rows=1 loops=2,273)

23. 0.000 0.000 ↓ 0.0 0 2,273

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

  • Filter: ((type = ANY ('{CarePlan}'::text[])) AND (materialized_path ? '{org.common.*,org.2267490707683935235.unpartitioned.*,org.2267490707683935235.*}'::lquery[]) AND (re2.start_vertex = content_pid) AND (fhir_core_code_search._search_token_code('status'::text, search_value) = 'active'::text))
24. 6.819 6.819 ↓ 0.0 0 2,273

Index Scan using _indx_stock_resource_single_content_pid on stock_resource_index sv_1 (cost=0.29..8.02 rows=1 width=129) (actual time=0.003..0.003 rows=0 loops=2,273)

  • Index Cond: (content_pid = re2.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))
25. 43.187 43.187 ↑ 1.0 1 2,273

Index Scan using _indx_careplan_single_content_pid on careplan_index sv_2 (cost=0.29..8.20 rows=1 width=135) (actual time=0.018..0.019 rows=1 loops=2,273)

  • Index Cond: (content_pid = re2.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))
26. 22.730 22.730 ↑ 1.0 1 2,273

Index Scan using _indx_riskassessment_multi_content_pid_ on riskassessment_multivalued_index mv3_1 (cost=0.29..8.02 rows=1 width=8) (actual time=0.009..0.010 rows=1 loops=2,273)

  • 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))
  • Rows Removed by Filter: 0
Planning time : 7.782 ms
Execution time : 91,835.821 ms