explain.depesz.com

PostgreSQL's explain analyze made readable

Result: RwV8

Settings
# exclusive inclusive rows x rows loops node
1. 5.301 150.024 ↓ 8,465.0 8,465 1

Nested Loop (cost=5.01..551.86 rows=1 width=133) (actual time=2.374..150.024 rows=8,465 loops=1)

2. 3.343 85.468 ↓ 8,465.0 8,465 1

Nested Loop (cost=5.01..535.62 rows=1 width=8) (actual time=2.267..85.468 rows=8,465 loops=1)

3. 12.707 14.405 ↓ 8,465.0 8,465 1

Bitmap Heap Scan on riskassessment_index sv2_1 (cost=5.01..367.01 rows=1 width=8) (actual time=2.222..14.405 rows=8,465 loops=1)

  • Recheck Cond: (fhir_core_code_search._search_token_code('status'::text, search_value) = 'final'::text)
  • Filter: (materialized_path ? '{org.common.*,org.2267490707683935235.unpartitioned.*,org.2267490707683935235.*}'::lquery[])
  • Rows Removed by Filter: 8522
  • Heap Blocks: exact=3694
4. 1.698 1.698 ↓ 176.9 16,987 1

Bitmap Index Scan on _indx_riskassessment_index_search_status_code (cost=0.00..5.01 rows=96 width=0) (actual time=1.698..1.698 rows=16,987 loops=1)

  • Index Cond: (fhir_core_code_search._search_token_code('status'::text, search_value) = 'final'::text)
5. 8.465 67.720 ↑ 3.0 1 8,465

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

6. 0.000 0.000 ↓ 0.0 0 8,465

Seq Scan on resource_edge re2 (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 (sv2_1.content_pid = end_vertex))
7. 25.395 25.395 ↓ 0.0 0 8,465

Index Scan using stock_resource_edge_source_type_path_idx on stock_resource_edge re2_1 (cost=0.29..8.31 rows=1 width=16) (actual time=0.003..0.003 rows=0 loops=8,465)

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

Bitmap Heap Scan on careplan_edge re2_2 (cost=4.73..160.26 rows=1 width=16) (actual time=0.004..0.004 rows=1 loops=8,465)

  • Recheck Cond: (end_vertex = sv2_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=8465
9. 16.930 16.930 ↑ 40.0 1 8,465

Bitmap Index Scan on careplan_edge_end_vertex_idx (cost=0.00..4.72 rows=40 width=0) (actual time=0.002..0.002 rows=1 loops=8,465)

  • Index Cond: (end_vertex = sv2_1.content_pid)
10. 0.000 59.255 ↑ 3.0 1 8,465

Append (cost=0.00..16.21 rows=3 width=133) (actual time=0.007..0.007 rows=1 loops=8,465)

11. 0.000 0.000 ↓ 0.0 0 8,465

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

  • 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))
12. 8.465 8.465 ↓ 0.0 0 8,465

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.001..0.001 rows=0 loops=8,465)

  • 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))
13. 50.790 50.790 ↑ 1.0 1 8,465

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.005..0.006 rows=1 loops=8,465)

  • 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))
Planning time : 2.675 ms
Execution time : 150.354 ms