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.

Last updated