explain.depesz.com

PostgreSQL's explain analyze made readable

Result: QlwY : Optimization for: plan #za6

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.004 519,719.049 ↑ 100.0 1 1

Limit (cost=656.30..3,311.66 rows=100 width=1,602) (actual time=118,576.688..519,719.049 rows=1 loops=1)

2. 168,299.902 519,719.045 ↑ 115,683,026.0 1 1

Nested Loop (cost=656.30..3,071,808,757.96 rows=115,683,026 width=1,602) (actual time=118,576.686..519,719.045 rows=1 loops=1)

  • Join Filter: (fhirbase_idx_fns.index_as_reference(medicationprescription.content, '{patient}'::text[]) && ARRAY[patient.logical_id])
  • Rows Removed by Join Filter: 2071972
3. 351,419.143 351,419.143 ↓ 1.1 2,071,973 1

Index Scan using fb_medicationprescription_datewritten_datewritten_date_idx on medicationprescription (cost=0.41..6,183,368.51 rows=1,889,628 width=1,602) (actual time=1.015..351,419.143 rows=2,071,973 loops=1)

  • Index Cond: (fhirbase_date_idx.index_as_date(content, '{dateWritten}'::text[], 'dateTime'::text) && '["2017-10-18 00:00:00+00",)'::tstzrange)
  • Filter: (fhirbase_idx_fns.index_primitive_as_token(content, '{status}'::text[]) && '{active}'::text[])
  • Rows Removed by Filter: 72819
4. 0.000 0.000 ↑ 6,122.0 1 2,071,973

Materialize (cost=655.88..25,211.51 rows=6,122 width=37) (actual time=0.000..0.000 rows=1 loops=2,071,973)

5. 0.348 6.148 ↑ 6,122.0 1 1

Bitmap Heap Scan on patient (cost=655.88..25,180.90 rows=6,122 width=37) (actual time=6.147..6.148 rows=1 loops=1)

  • Recheck Cond: (fhirbase_idx_fns.index_identifier_as_token(content, '{identifier}'::text[]) && '{9112420006172080}'::text[])
  • Filter: (fhirbase_idx_fns.index_primitive_as_token(content, '{active}'::text[]) && '{true}'::text[])
  • Heap Blocks: exact=1
6. 5.800 5.800 ↑ 6,180.0 1 1

Bitmap Index Scan on fb_patient_identifier_identifier_token_idx (cost=0.00..654.35 rows=6,180 width=0) (actual time=5.800..5.800 rows=1 loops=1)

  • Index Cond: (fhirbase_idx_fns.index_identifier_as_token(content, '{identifier}'::text[]) && '{9112420006172080}'::text[])