---
description: PostgreSQL database indexes for Aidbox. Learn to create, manage, and optimize indexes for faster FHIR search queries and better performance.
---

# 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
* [collect per-SearchParameter usage statistics](search-parameter-usage-stats.md) — rank "hot" parameters and confirm a created index is actually used

## Background

Aidbox uses [PostgreSQL](https://www.postgresql.org/) database for storage. Most of resource data is contained in [_resource_](../../database/database-schema.md) column with [_jsonb_](https://www.postgresql.org/docs/current/datatype-json.html) type. See [Database overview](../../database/overview.md) for the full picture of how resources map onto SQL.

Consider simple example: _active_ search parameter for _Patient_ resource.

Let's try the search query

```http
GET /fhir/Patient?active=true
```

Use [\_explain](../../api/rest-api/aidbox-search.md#explain) to find out SQL query generated by this request

```http
GET /fhir/Patient?active=true&_explain=analyze
```

Possible response is

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

Corresponding SQL is

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

Here [`@>`](https://www.postgresql.org/docs/current/functions-json.html) 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_](https://www.postgresql.org/docs/current/datatype-json.html#JSON-INDEXING) can speed up these kind of queries. A GIN index inverts the jsonb structure into a lookup table of the keys and values it contains, so a containment test (`@>`) can jump straight to matching rows instead of scanning the whole table.

We can create GIN index for the `resource` column

```sql
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

```http
GET /fhir/Patient?name=abc
```

Generates SQL like

<pre class="language-sql"><code class="lang-sql"><strong>SELECT *
</strong><strong>FROM Patient
</strong><strong>WHERE
</strong><strong>  aidbox_text_search(
</strong><strong>    knife_extract_text(
</strong><strong>      resource,
</strong>      '[["name","family"],["name","given"],["name","middle"],["name","text"],["name","prefix"],["name","suffix"]]'
    )
  ) ILIKE unaccent('% abc%')
LIMIT 100
OFFSET 0
</code></pre>

Postgres' [`pg_trgm` module](https://www.postgresql.org/docs/current/pgtrgm.html#id-1.11.7.44.8) supports index searches for `ILIKE` queries.

You can create functional index to speed up this query:

```sql
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"],["name","prefix"],["name","suffix"]]'
    )
  ) gin_trgm_ops
)
```

### Which indexes does Aidbox need?

It depends — and that's the point. A short tour of what can vary:

* **Index method.** GIN for `@>` over jsonb, GIN with `gin_trgm_ops` for fuzzy text (`name`, `_text`), btree for ordered access (`id`, `_lastUpdated`, `date`), GiST for spatial (`near` on Location). The right choice depends on the SearchParameter's type, not its name.
* **Modifiers.** `:contains` and `:exact` on the same `name` parameter need different functional indexes; `:in` / `:not-in` / `:above` / `:below` on token parameters expand into ValueSet lookups; `:identifier` / `:of-type` pull from different jsonb paths.
* **Path expressions.** Aidbox stores resources as jsonb, so the suggester emits *functional* indexes over `knife_extract_text(...)` or `jsonb_path_query(...)` — one per SP path — rather than indexes on plain columns.
* **Joins.** Chained queries (`Observation?subject:Patient.name=John`) and reverse-chain `_has` queries translate into SQL joins or subselects; both sides need their own indexes.
* **Full-resource fallback.** Token and reference parameters without a dedicated path fall back to a GIN over the whole jsonb. It rescues queries that no functional index covers, but it's larger on disk.

Hand-picking the right combination per parameter is impractical. The next sections cover Aidbox's [suggest-index RPCs](#index-suggestion), which compute the candidates for you, and the [usage-statistics RPCs](#usage-statistics), which tell you which suggestions actually deserve the disk space.

## Index suggestion

Aidbox provides two RPCs that can suggest you indexes

### Suggest indexes for parameter

Use [`aidbox.index/suggest-index`](get-suggested-indexes.md) RPC to get index suggestion for specific search parameter

```http
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`](get-suggested-indexes.md) RPC to get index suggestions based on query

```http
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
```

{% content-ref url="get-suggested-indexes.md" %}
[get-suggested-indexes.md](get-suggested-indexes.md)
{% endcontent-ref %}

## Usage statistics

Aidbox tracks how often each SearchParameter is queried and exposes the numbers via RPCs. Use them to rank "hot" parameters, decide which suggested indexes are worth creating, and confirm a created index is actually being used. Available since Aidbox 2605.

{% content-ref url="search-parameter-usage-stats.md" %}
[search-parameter-usage-stats.md](search-parameter-usage-stats.md)
{% endcontent-ref %}

## See also

{% content-ref url="../../tutorials/crud-search-tutorials/set-up-uniqueness-in-resource.md" %}
[set-up-uniqueness-in-resource.md](../../tutorials/crud-search-tutorials/set-up-uniqueness-in-resource.md)
{% endcontent-ref %}