Get suggested indexes

Since version 2211, Aidbox can suggest indexes for Search API.

Index suggestion API is in draft stage. It will change in future!

Supported FHIR Search parameters types:

• string

• number

• date

• token

• quantity

• reference

• uri

Supported underscored parameters

• _id

• _ilike

• _text

• _content

• _createdAt

• _lastUpdated

• Dot expressions

Not supported:

• zen Search Parameters

• _filter

• include, _revinclude

• chained Search Parameters

aidbox.index/suggest-index

Required parameters: resource-type and search-param.

POST /rpc

method: aidbox.index/suggest-index
params:
  resource-type: <resourceType>
  search-param: <searchParameter>

Example:

POST /rpc

method: aidbox.index/suggest-index
params:
  resource-type: Observation
  search-param: date

Result:

result:
  - index-name: observation_date_param_knife_date_min_tstz
    name: date
    resource-type: Observation
    statement: >-
      CREATE INDEX CONCURRENTLY IF NOT EXISTS
      "observation_date_param_knife_date_min_tstz" ON "observation" USING btree
      ((knife_extract_min_timestamptz("observation".resource,
      '[["effective","Period","start"],["effective","Period","end"],["effective","dateTime"],["effective","Timing","event"],["effective","instant"]]'))
      )
    subtypes:
      - null
      - eq
      - ne
      - lt
      - le
      - btw
    type: date
  - index-name: observation_date_param_knife_date_max_tstz
    name: date
    resource-type: Observation
    statement: >-
      CREATE INDEX CONCURRENTLY IF NOT EXISTS
      "observation_date_param_knife_date_max_tstz" ON "observation" USING btree
      ((knife_extract_max_timestamptz("observation".resource,
      '[["effective","Period","start"],["effective","Period","end"],["effective","dateTime"],["effective","Timing","event"],["effective","instant"]]'))
      )
    subtypes:
      - null
      - eq
      - ne
      - gt
      - ge
      - btw
    type: date

Suggested two indexes: first one to search using lt, le and eq prefixes, second one to search usinggt, ge, eq prefixes.

aidbox.index/suggest-index-query

You can get all indexes for specific query using suggest-index-query.

Required parameters: resource-type and query.

POST /rpc

method: aidbox.index/suggest-index-query
params:
  resource-type: <resourceType>
  query: <query>

Example:

POST /rpc

method: aidbox.index/suggest-index-query
params:
  resource-type: Observation
  query: date=gt2022-01-01&_id=myid

Response:

result:
  - index-name: observation_date_param_knife_date_min_tstz
    name: date
    resource-type: Observation
    statement: >-
      CREATE INDEX CONCURRENTLY IF NOT EXISTS
      "observation_date_param_knife_date_min_tstz" ON "observation" USING btree
      ((knife_extract_min_timestamptz("observation".resource,
      '[["effective","Period","start"],["effective","Period","end"],["effective","dateTime"],["effective","Timing","event"],["effective","instant"]]'))
      )
    subtypes:
      - null
      - eq
      - ne
      - lt
      - le
      - btw
    type: date
  - index-name: observation_date_param_knife_date_max_tstz
    name: date
    resource-type: Observation
    statement: >-
      CREATE INDEX CONCURRENTLY IF NOT EXISTS
      "observation_date_param_knife_date_max_tstz" ON "observation" USING btree
      ((knife_extract_max_timestamptz("observation".resource,
      '[["effective","Period","start"],["effective","Period","end"],["effective","dateTime"],["effective","Timing","event"],["effective","instant"]]'))
      )
    subtypes:
      - null
      - eq
      - ne
      - gt
      - ge
      - btw
    type: date
  - index-name: observation_resource_id
    name: id
    resource-type: Observation
    statement: >-
      CREATE INDEX CONCURRENTLY IF NOT EXISTS "observation_resource_id" ON
      "observation" USING btree (("observation".id) )
    subtypes:
      - in
      - null
    type: id

Suggested indexes will increase performance of Observation.date and Observation._id.