Indexes

Database indexes are essential for performance. In particular you will need indexes to speed up search requests.

Aidbox provides mechanisms to

• manage indexes

• suggest indexes

• generate indexes automatically

Background

Aidbox uses PostgreSQL database for storage. Most of resource data is contained in resource column with jsonb type.

Consider simple example: active search parameter for Patient resource.

Let's try the search query

GET /Patient?active=true

Use _explain to find out SQL query generated by this request

GET /Patient?active=true&_explain=analyze

Possible response is

query:
  - >-
    SELECT "patient".* FROM "patient" WHERE "patient".resource @> ? LIMIT ?
    OFFSET ? 
  - '{"active":true}'
  - 100
  - 0
plan: >-
  Limit  (cost=0.00..1.01 rows=1 width=124) (actual time=0.015..0.015 rows=0
  loops=1)
    ->  Seq Scan on patient  (cost=0.00..1.01 rows=1 width=124) (actual time=0.014..0.014 rows=0 loops=1)
          Filter: (resource @> '{"active": true}'::jsonb)
          Rows Removed by Filter: 1
  Planning Time: 0.729 ms

  Execution Time: 0.050 ms

Corresponding SQL is

SELECT "patient".*
FROM "patient"
WHERE "patient".resource @> '{"active": "true"}'::jsonb
LIMIT 100
OFFSET 0

Here @> is containment operator. It tests whether jsonb value on the right-hand side is contained in the jsonb value on the left-hand side.

Without indexes Postgres has to check this condition for every Patient resource stored in the database.

However, GIN indexes can speed up these kind of queries.

We can create GIN index for the resource column

CREATE INDEX patient_resource_gin_idx
ON Patient
USING GIN (resource)

Now Postgres can use this index to make search much faster.

Functional indexes

Consider more complex example: name search parameter for Patient resource.

Request

GET /Patient?name=abc

Generates SQL like

SELECT *
FROM Patient
WHERE
  aidbox_text_search(
    knife_extract_text(
      resource,
      '[["name","family"],["name","given"],["name","middle"],["name","text"]]'
    )
  ) ILIKE unaccent('% abc%')
LIMIT 100
OFFSET 0

Postgres' pg_trgm module supports index searches for ILIKE queries.

You can create functional index to speed up this query:

CREATE INDEX patient_name_trgm_idx
ON Patient
USING GIN (
  aidbox_text_search(
    knife_extract_text(
      resource,
      '[["name","family"],["name","given"],["name","middle"],["name","text"]]'
    )
  ) gin_trgm_ops
)

Index suggestion

Aidbox provides two RPCs that can suggest you indexes

Suggest indexes for parameter

Use aidbox.index/suggest-index RPC to get index suggestion for specific search parameter

POST /rpc
Content-Type: text/yaml
Accept: text/yaml

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

Suggest indexes for query

Use aidbox.index/suggest-index-query RPC to get index suggestions based on query

POST /rpc
Content-Type: text/yaml
Accept: text/yaml

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