C3 AI Documentation Home

Database Indexes

Add a database index to an entity Type when fetches that filter or sort on the same fields run slowly on a large table. You declare indexes in the @db annotation of the Type, and the C3 Agentic AI Platform creates them in the database that stores the Type. The platform creates these indexes for entity Types stored in a SQL database, such as PostgreSQL. Key-value stores do not use these indexes.

On this page, you choose an index, find a slow fetch, add the index, and review the indexes on a table.

Choose an index

An index is a sorted structure that the database keeps for one or more fields, so it can find matching rows without reading the whole table. Choose the kind of index that matches how your application queries the Type:

  • Primary key index: The platform creates this index with the table, so you do not declare it.
  • Unique index: Choose it when a field or set of fields must hold a different value in every row, in addition to the primary key, such as email addresses or external system IDs. Declare unique indexes in the unique field of the @db annotation.
  • Single-field index: Choose it when your common fetches filter on one field.
  • Composite index: An index on two or more fields. Choose a composite index when your common fetches filter on the same set of fields. Order the fields to match those filters.

Each index speeds up reads that use it and adds work to every create, update, and delete on the table. Add an index for fetches you run often, and review indexes that queries do not use.

Indexes and sort order

Indexes keep their entries sorted. When a fetch filters and orders on fields that one index covers, the database returns rows in index order and skips a separate sort. By default, an index field sorts in ascending order with null values last. In descending order, null values come first.

When no single index covers both the filter and the order, the database uses an index for one of them. The database then loads and sorts the matching rows, which takes longer on large results. With no index for the filter, the database reads every row in the table, which is called a full table scan.

Find a slow fetch

A query plan is the list of steps that the database takes to run a query. To see the plan for a slow fetch:

  1. Run the fetch with explain: true in the fetch spec:

    JavaScript
    MyType.fetch({filter: "status == 'Active'", explain: true})

    The result includes queryPlans, which holds the SQL statement and the database query plan.

  2. In the plan, look for a full table scan on a large table, or a sort step on a large result.

  3. Add an index on the fields that the fetch filters or orders on.

  4. Run the fetch with explain: true again and confirm that the plan now uses the index.

Repeat these steps as your query patterns change.

Add an index

The index field of the @db annotation holds a list of strings. Each string defines one index:

  • List the fields of the index in one string, separated by commas. index=["a, b"] creates one composite index on a and then b.
  • Use one string per index. index=["a", "b"] creates two single-field indexes.
  • To set the sort order of a field, wrap it in ascending(field) or descending(field). Add true as the second argument to reverse the default null placement: ascending(field, true) puts null values first, and descending(field, true) puts null values last.

For example, this annotation creates one index that sorts field1 in ascending order with null values first, and field2 in ascending order with null values last:

Type
@db(index = ['ascending(field1, true), field2'])

The index list also accepts index objects with named keys, which Use Partial Indexes in Vector Store uses. For the common @db fields, see About the Database Annotation. For all @db fields, see Ann.Db.

To add an index:

  1. In your package, open the .c3typ file of the entity Type to index.
  2. Add the @db(index=[...]) annotation directly before the entity type declaration.
  3. Save the file and sync or deploy the package. The platform creates the index when it provisions the Type.
  4. Check that the index exists with DbAdmin.inst().getTableIndexes(). See List the indexes on a table.

Example

This example assumes that your application has an entity Type named SmartBulb. Adapt the Type and field names to your own data model.

Type
/**
 * A prediction for a smart bulb at a point in time.
 */
@db(index=["smartBulb, timestamp"])
entity type SmartBulbPrediction {
  smartBulb: SmartBulb
  timestamp: datetime
  // ...
}

The annotation creates one composite index on smartBulb and then timestamp. Fetches that filter on a smart bulb and a time range use the index. Fetches that filter on a smart bulb and order by timestamp get their rows in order without a separate sort.

Review indexes and index usage

The DbAdmin Type provides database administration methods. Two DbAdmin methods help you review indexes: one lists the indexes on a table, and one shows how often queries use them. Both methods belong to the monitor action group. If a call is denied, ask your administrator for access.

Both methods take the name of a table. To get the table name of an entity Type, call <TypeName>.meta().rootC3TableName().

List the indexes on a table

Call getTableIndexes() with the table name. The table name is required.

JavaScript
DbAdmin.inst().getTableIndexes(SmartBulbPrediction.meta().rootC3TableName())

The result has one entry per index. Each entry reports the index name (indexName), the index columns (indexCols, includeCols), and the index method (indexType). Each entry also reports whether the index is unique or the primary key (unique, primaryKey). The remaining fields show the filter of a partial index (filterSql), the size (indexSize), and the index state (isValid, isReady).

Use the result to confirm that a new index exists and to find indexes that repeat another index.

Find unused indexes

Call getIndexUsageStats() to see how often queries use each index. Pass a table name to limit the result to one table, or no table to include all tables.

JavaScript
DbAdmin.inst().getIndexUsageStats(SmartBulbPrediction.meta().rootC3TableName())

The method takes these arguments:

  • table: The table to report on. With no table, the result covers all tables.
  • mostUsedFirst: Set to true to list the most used indexes first. By default, the least used indexes come first.
  • limit: The maximum number of indexes to return. The default is 100.

For example, DbAdmin.inst().getIndexUsageStats(null, true, 10) lists the 10 most used indexes across all tables.

Each entry in the result reports numIndexScans, the number of scans that used the index, along with numTuplesRead, numTuplesFetched, numBlksRead, and numBlksHit. An index with few or no scans adds write cost without speeding up reads.

See also

Was this page helpful?