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
Use _explain to find out SQL query generated by this request
Possible response is
Corresponding SQL is
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
Now Postgres can use this index to make search much faster.
Functional indexes
Consider more complex example: name
search parameter for Patient
resource.
Request
Generates SQL like
Postgres' pg_trgm
module supports index searches for ILIKE
queries.
You can create functional index to speed up this query:
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
Suggest indexes for query
Use aidbox.index/suggest-index-query
RPC to get index suggestions based on query
Last updated
Was this helpful?