# 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](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.

Consider simple example: _active_ search parameter for _Patient_ resource.

Let's try the search query

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

Use [\_explain](../../api/rest-api/aidbox-search.md#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

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

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

```
GET /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"]]'
    )
  ) 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"]]'
    )
  ) 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
```