Indexes. Search performance

Create indexes to boost Aidbox performance

35 cells · updated Jan 10, 2022

This is a read-only view. Responses shown were saved when the notebook was published — open it in your own Aidbox to run the cells against live data.

Direct access CRUD operations

Regular create/read/update/delete operations such as:

POST <base>/Patient
GET <base>/Patient/<id>
PUT <base>/Patient/<id>
PATCH <base>/Patient/<id>
DELETE <base>/Patient/<id>

do not need any indexes to be created.

The only index affecting regular CRUD performance is primary key index created by default.

Example

POST /fhir/Patient
GET /fhir/Patient/<id>
PUT /fhir/Patient/<id>
PATCH /fhir/Patient/<id>
DELETE /fhir/Patient/<id>

List of indexes created for Patient table with out of the box configuration:

SQL Send

select indexname from pg_indexes where schemaname = 'public' and tablename = 'patient'

Resource history operation

Resource history requests like

GET <base>/<resourceType>/<id>/_history
GET <base>/<resourceType>/<id>/_history/<version-id>

Use resource table primary key index and history table primary key index created by default.

No need to create any additional indexes

Example

GET /fhir/Patient/<id>/_history/
GET /fhir/Patient/<id>/_history/<version-id>

Will use the same index as for regular CRUD operations from previous example and patient_history table primary key index:

SQL Send

select indexname from pg_indexes where schemaname = 'public' and tablename = 'patient_history'

Search & Conditional create/update/delete operations

Operations with some arbitary filter such as:

POST <base>/<resourceType>?<search-parameters>
GET <base>/<resourceType>?<search-parameters>
PUT <base>/<resourceType>?<search-parameters>
PATCH <base>/<resourceType>?<search-parameters>
DELETE <base>/<resourceType>?<search-parameters>

can be optimized by creating indexes for generated SQL expression. Each different filter creates different SQL expression. Structure of generated SQL depends on SearchParameter.type and SearchParameter.expression.

Since genrated SQL expressions don't depend on a request method, but only on a SearchParameter, the same index will affect all of the different conditional CRUD & Search operations with the same SearchParameter used

Use GET /SearchParameter/<id> to find a SearchParameter type and expression

SearchParameter ids are created by following pattern: resourceType and path in a resource separated with .

For example: in a request GET /fhir/Patient?name=... SearchParameter with the id Patient.name will be used

REST Send

GET /SearchParameter/Patient.name
Accept: text/yaml

Response: Body

Status: 200

expression:
- [name]
meta: {lastUpdated: '2021-12-10T10:55:26.146824Z', createdAt: '2021-12-10T10:55:26.146824Z', versionId: '0'}
name: name
type: string
resourceType: SearchParameter
module: fhir-4.0.1
id: Patient.name
resource: {id: Patient, resourceType: Entity}
_source: code

Example: `string` type search parameter

POST /fhir/Patient?name=<patient-name>
GET /fhir/Patient?name=<patient-name>
PUT /fhir/Patient?name=<patient-name>
PATCH /fhir/Patient?name=<patient-name>
DELETE /fhir/Patient?name=<patient-name>

In these requests string type SearchParameter/Patient.name is used

Use _explain=analyze query string parameter to analyze generated SQL:

REST Send

GET /fhir/Patient/?name=somename&_explain=analyze
Accept: text/yaml

Response: Body

Status: 200

query: ['SELECT "patient".* FROM "patient" WHERE aidbox_text_search(knife_extract_text("patient".resource, $JSON$[["name","family"],["name","given"],["name","middle"],["name","text"]]$JSON$)) ilike unaccent(?) LIMIT ? OFFSET ? ', '% somename%', 100, 0]
plan: |-
  Limit  (cost=0.00..107.94 rows=1 width=639) (actual time=3.310..3.311 rows=0 loops=1)
    ->  Seq Scan on patient  (cost=0.00..107.94 rows=1 width=639) (actual time=3.308..3.309 rows=0 loops=1)
          Filter: (immutable_wrap_ws(immutable_unaccent(immutable_array_to_string(knife_extract_text(resource, '[["name", "family"], ["name", "given"], ["name", "middle"], ["name", "text"]]'::jsonb), ' '::text))) ~~* unaccent('% somename%'::text))
          Rows Removed by Filter: 127
  Planning Time: 0.129 ms
  Execution Time: 3.334 ms

Analyze response contains query attribute with a generated SQL and execution plan created by Postres explain analyze query for the SQL

In this case, query plan contains Seq Scan which can be optimized by creating an index with expression matching the corresponding where clause:

SQL Send

create index patient_name_gin_idx
on patient
using gin ((
    aidbox_text_search(
      knife_extract_text(
        resource
      , $JSON$[["name","family"]
              ,["name","given"]
              ,["name","middle"]
              ,["name","text"]]$JSON$))
  )
  gin_trgm_ops
);

No results were returned by the query.

SQL Send

vacuum analyze patient;

No results were returned by the query.

REST Send

GET /fhir/Patient/?name=somename&_explain=analyze
Accept: text/yaml

Response: Body

Status: 200

query: ['SELECT "patient".* FROM "patient" WHERE aidbox_text_search(knife_extract_text("patient".resource, $JSON$[["name","family"],["name","given"],["name","middle"],["name","text"]]$JSON$)) ilike unaccent(?) LIMIT ? OFFSET ? ', '% somename%', 100, 0]
plan: |-
  Limit  (cost=36.00..40.77 rows=1 width=630) (actual time=0.021..0.021 rows=0 loops=1)
    ->  Bitmap Heap Scan on patient  (cost=36.00..40.77 rows=1 width=630) (actual time=0.020..0.020 rows=0 loops=1)
          Recheck Cond: (immutable_wrap_ws(immutable_unaccent(immutable_array_to_string(knife_extract_text(resource, '[["name", "family"], ["name", "given"], ["name", "middle"], ["name", "text"]]'::jsonb), ' '::text))) ~~* unaccent('% somename%'::text))
          ->  Bitmap Index Scan on patient_name_gin_idx  (cost=0.00..36.00 rows=1 width=0) (actual time=0.019..0.019 rows=0 loops=1)
                Index Cond: (immutable_wrap_ws(immutable_unaccent(immutable_array_to_string(knife_extract_text(resource, '[["name", "family"], ["name", "given"], ["name", "middle"], ["name", "text"]]'::jsonb), ' '::text))) ~~* unaccent('% somename%'::text))
  Planning Time: 0.279 ms
  Execution Time: 0.038 ms

After index created and vacuum analyze on the Patient table is done search query plan doesn't use Seq Scan and mentions usage of the index patient_name_gin_idx. This results in Execution time greatly reduced.

These changes will affect create/search/update/delete (POST/GET/PUT/PATCH/DELETE) operations on Patient resource type with name search parameter

Example: `reference` type search parameter

GET /fhir/Observation?subject=Patient/<patient-id>

in this request reference type SearchParameter/Observation.subject is used.

For reference type search a different kind of SQL will be generated:

REST Send

GET /fhir/Observation?subject=Patient/some-id&_explain=analyze
Accept: text/yaml

Response: Body

Status: 200

query: ['SELECT "observation".* FROM "observation" WHERE "observation".resource @> ? LIMIT ? OFFSET ? ', '{"subject":{"resourceType":"Patient","id":"some-id"}}', 100, 0]
plan: |-
  Limit  (cost=0.00..2741.78 rows=2 width=908) (actual time=11.287..11.288 rows=0 loops=1)
    ->  Seq Scan on observation  (cost=0.00..2741.78 rows=2 width=908) (actual time=11.285..11.286 rows=0 loops=1)
          Filter: (resource @> '{"subject": {"id": "some-id", "resourceType": "Patient"}}'::jsonb)
          Rows Removed by Filter: 20382
  Planning Time: 0.270 ms
  Execution Time: 11.316 ms

In this case SQL expression generated is a "observation".resource or just a resource. According gin index can be created

SQL Send

create index observation_subject_gin_idx
on observation
using gin (resource);

No results were returned by the query.

SQL Send

vacuum analyze observation;

No results were returned by the query.

REST Send

GET /fhir/Observation?subject=Patient/some-id&_explain=analyze
Accept: text/yaml

Response: Body

Status: 200

query: ['SELECT "observation".* FROM "observation" WHERE "observation".resource @> ? LIMIT ? OFFSET ? ', '{"subject":{"resourceType":"Patient","id":"some-id"}}', 100, 0]
plan: |-
  Limit  (cost=44.02..51.87 rows=2 width=908) (actual time=0.087..0.088 rows=0 loops=1)
    ->  Bitmap Heap Scan on observation  (cost=44.02..51.87 rows=2 width=908) (actual time=0.087..0.087 rows=0 loops=1)
          Recheck Cond: (resource @> '{"subject": {"id": "some-id", "resourceType": "Patient"}}'::jsonb)
          ->  Bitmap Index Scan on observation_subject_gin_idx  (cost=0.00..44.02 rows=2 width=0) (actual time=0.086..0.086 rows=0 loops=1)
                Index Cond: (resource @> '{"subject": {"id": "some-id", "resourceType": "Patient"}}'::jsonb)
  Planning Time: 0.161 ms
  Execution Time: 0.105 ms

Example: `_include`

GET /fhir/Encounter?_include=patient

Request doesn't contain any filtering parameters on Encounter resource, but Patient table is filtered to contain Patient resources referenced in Encounter resources. Thus target table for performance optimizations in this case is the Patient table

SQL query for requests with _include can be found by executing the request without /fhir/ prefix.

(The following request has &_count=1&_elements=id parameters added to reduce output size)

REST Send

GET /Encounter?_include=patient&_count=1&_elements=id
Accept: text/yaml

Response: Body

Status: 200

query-time: 4
meta: {versionId: '543'}
include-queries:
- ['SELECT * FROM "patient" WHERE (id in (?)) LIMIT ?', 5edae93e-d979-d4cb-2eaa-aec472b78d44, 5000]
type: searchset
resourceType: Bundle
total: 607917
link:
- {relation: first, url: 'http://localhost:8765/Encounter?_include=patient&_count=1&_elements=id&page=1'}
- {relation: self, url: 'http://localhost:8765/Encounter?_include=patient&_count=1&_elements=id&page=1'}
- {relation: next, url: 'http://localhost:8765/Encounter?_include=patient&_count=1&_elements=id&page=2'}
- {relation: last, url: 'http://localhost:8765/Encounter?_include=patient&_count=1&_elements=id&page=607917'}
query-timeout: 60000
entry:
- resource: {id: 00315837-325a-f9e3-661c-5cfcddcaa223, resourceType: Encounter}
  fullUrl: http://localhost:8765/Encounter/00315837-325a-f9e3-661c-5cfcddcaa223
  link:
  - {relation: self, url: 'http://localhost:8765/Encounter/00315837-325a-f9e3-661c-5cfcddcaa223'}
query-sql: ['SELECT "encounter".* FROM "encounter" LIMIT ? OFFSET ? ', 1, 0]

The request response contains:

include-queries:
  - - SELECT * FROM "patient" WHERE (id in (?)) LIMIT ?
    - 5edae93e-d979-d4cb-2eaa-aec472b78d44
    - 5000

The SQL query relies target table primary key index which is created by default. No additional indexes need to be created to speed up _include performance

Example: `_revinclude`

GET /fhir/Patient?_revinclude:logical=Encounter:patient:Patient

As in previous example the only filter in this example is applied to Patient table

REST Send

GET /Patient?_revinclude=Encounter:patient:Patient&_count=1&_elements=id
Accept: text/yaml

Response: Body

Status: 200

query-time: 1414
meta: {versionId: '572'}
include-queries:
- ['SELECT distinct * FROM "encounter" WHERE ((knife_extract_text(resource, $$[["subject",{"resourceType":"Patient"},"id"]]$$))[1] in (?)) LIMIT ?', 99782a94-4c0e-4549-8552-8f115aecfa82, 5000]
type: searchset
resourceType: Bundle
total: 127
link:
- {relation: first, url: 'http://localhost:8765/Patient?_revinclude=Encounter:patient:Patient&_count=1&_elements=id&page=1'}
- {relation: self, url: 'http://localhost:8765/Patient?_revinclude=Encounter:patient:Patient&_count=1&_elements=id&page=1'}
- {relation: next, url: 'http://localhost:8765/Patient?_revinclude=Encounter:patient:Patient&_count=1&_elements=id&page=2'}
- {relation: last, url: 'http://localhost:8765/Patient?_revinclude=Encounter:patient:Patient&_count=1&_elements=id&page=127'}
query-timeout: 60000
entry:
- resource: {id: 99782a94-4c0e-4549-8552-8f115aecfa82, resourceType: Patient}
  fullUrl: http://localhost:8765/Patient/99782a94-4c0e-4549-8552-8f115aecfa82
  link:
  - {relation: self, url: 'http://localhost:8765/Patient/99782a94-4c0e-4549-8552-8f115aecfa82'}
query-sql: ['SELECT "patient".* FROM "patient" LIMIT ? OFFSET ? ', 1, 0]

include-queries:
  - - >-
      SELECT distinct * FROM "encounter" WHERE ((knife_extract_text(resource,
      $$[["subject",{"resourceType":"Patient"},"id"]]$$))[1] in (?)) LIMIT ?
    - 99782a94-4c0e-4549-8552-8f115aecfa82
    - 5000

Plan for this SQL query:

SQL Send

explain analyze
SELECT distinct * FROM "encounter" WHERE ((knife_extract_text(resource,
$$[["subject",{"resourceType":"Patient"},"id"]]$$))[1] in ('99782a94-4c0e-4549-8552-8f115aecfa82'))
LIMIT 5000

SQL Send

create index encounter_subject_gin_idx
on encounter
using btree
(((knife_extract_text(resource, '[["subject", {"resourceType": "Patient"}, "id"]]'::jsonb))[1]));

No results were returned by the query.

SQL Send

vacuum analyze encounter;

No results were returned by the query.

SQL Send

explain analyze
SELECT distinct * FROM "encounter" WHERE ((knife_extract_text(resource,
$$[["subject",{"resourceType":"Patient"},"id"]]$$))[1] in ('99782a94-4c0e-4549-8552-8f115aecfa82'))
LIMIT 5000

Indexes. Search performance

Direct access CRUD operations

Example

Resource history operation

Example

Search & Conditional create/update/delete operations

Example: string type search parameter

Example: reference type search parameter

Example: _include

Example: _revinclude

Example: `string` type search parameter

Example: `reference` type search parameter

Example: `_include`

Example: `_revinclude`