C3 AI Documentation Home

Database Indexes

Indexes should be added when performance issues are observed with common filters on large tables. These recommendations provide a structured approach to understanding when and how to use different types of indexes to optimize database performance and maintain data integrity.

Recommendations on database indexes

The process of identifying where indexes are needed is typically iterative:

  1. Identify Performance Issues – When experiencing slow fetch operations, consider using the FetchSpec#explain = true option to analyze the database's query execution plan.
  2. Analyze Query Plan – Examine the query plan generated by the database to identify areas where adding indexes could improve performance.
  3. Add Appropriate Indexes – Based on the analysis, add suitable indexes to help the database generate more efficient query plans and improve performance.

Note: Due to the unique nature of different databases and data structures, it is difficult to provide detailed, one-size-fits-all guidance. It is recommended to approach this process iteratively and tailor it to specific use cases as they arise.

The following points outline the recommendations for including indexes:

Primary key indexes

Automatic Creation – Primary key indexes are automatically generated when a primary key is defined. Therefore, these do not require manual annotations or additional configuration.

Unique indexes

Purpose – Unique indexes should be used to enforce additional unique constraints that go beyond the primary key.

Use Cases – These indexes are particularly beneficial when there are explicit requirements for data uniqueness, such as social security numbers, email addresses, or other fields where duplicate entries are not permissible.

General index usage

Common Filters – Indexes should be created when queries commonly filter on a specific field or a set of fields. This approach helps to optimize the performance of these frequent lookups.

Single-Field Indexes – If filters in common queries are typically based on a single field, then creating an index on that single field is recommended.

Multi-Field (Composite) Indexes – When queries involve filtering on multiple fields, creating a composite index that covers those fields can provide better performance. Ensure that the order of fields in a composite index matches the common query patterns to maximize efficiency.

Implementation guidelines

Analyze Query Patterns – Before creating indexes, analyze the database's query patterns and identify the fields that are frequently used in filters or WHERE clauses.

Monitor and Adjust – Periodically review the usage and performance of existing indexes and adjust as necessary to avoid redundant or unused indexes that may negatively impact database performance.

Consider Trade-offs – Indexes can improve read performance but may introduce additional overhead for write operations (for example, inserts, updates, deletes). Balance the benefits with potential drawbacks based on the specific use case and workload.

Optimizing database sorting and indexing for performance

Understanding strategic index design is essential for improving sorting, filtering, and overall database query performance.

Leverage existing indexes for sorting and filtering

Sorted Index Entries – Indexes are inherently sorted. When a query requests data in a specific order, the database considers existing indexes to determine if one can fulfill both filtering and ordering requirements. By default, database indexes are maintained in an ascending order, with null values appearing last. This default behavior ensures efficient data retrieval even if no specific sorting order (ascending or descending) is explicitly defined in the index configuration.

Combined Index Use – If an index on relevant fields covers both filtering and ordering, rows can be directly returned in the correct order, enhancing performance. The mechanism of indexing maintains an internal sorted structure, enabling the database to optimize query execution. Explicitly specifying the sorting order in the index annotation (for example, ascending or descending) allows for fine-tuning, but it is not required for basic sorting and filtering operations, as the inherent order handles typical use cases effectively.

This example demonstrates how to manually specify sorting order and control null value placement in an index, while highlighting that indexes inherently maintain an ordered structure for optimized performance.

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

In this example, field1 is indexed in ascending order with null values appearing first, and field2 is indexed in ascending order with null values appearing last. Even without specifying a sorting order in the annotation, the index maintains an internal sorted structure by default.

Trade-offs in index selection

Single Index Limitation – If no single index can satisfy both filtering and ordering, the database selects an index for either filtering or ordering.

Performance Cost of Sorting – When an index is used only for filtering (or no index is available), the database must materialize and sort the result set before returning data, which can be resource-intensive and slow.

Full Table Scan for Filtering – If no suitable index exists for filtering, the database typically performs a "full table scan," reading all rows to find matches. This process is more resource-intensive and time-consuming.

Key recommendations

Optimize Index Design – Create indexes that cover both filtering and ordering when possible, especially for common queries on large tables. This reduces the need for in-memory sorting or full table scans.

Monitor Query Plans – Use tools like EXPLAIN to analyze query plans and identify if the database is resorting to full table scans or sorting operations due to inadequate indexes.

Iterative Indexing – Approach index optimization iteratively—observe performance, adjust indexes based on query patterns, and re-evaluate to ensure improved performance.

How to add an index

To improve data retrieval performance within the C3 Agentic AI Platform, you can define indexes on your entity types using the @db annotation. Here's a general guide on how to add an index to an entity type.

Note on database index implementation

Important: The implementation of database indexes within the C3 AI system is currently supported exclusively for PostgreSQL (PG). You should ensure that your database configurations align with this requirement to leverage indexing capabilities effectively.

Step-by-step instructions

  1. Open/Create Your Type File: Begin by creating or opening a .c3typ file where you want to define your type.

  2. Define the Type: Declare your type by using the type keyword, followed by the name of your type and its properties.

  3. Add the DB Annotation: Include the @db annotation above your type declaration to specify database-related settings, including the indexes. The index attribute within the @db annotation should be an array of field names to be included in the index.

  4. Combine the Elements: Ensure that the @db annotation with the index specification precedes your type declaration.

  5. Save Your Changes: After defining your type with the index, save the file. The C3 Agentic AI Platform recognizes the index definition during deployment or compilation.

By following these steps, you can efficiently add indexes to your data types using the @db annotation, enhancing the performance and reliability of your C3 AI applications.

Example

JavaScript
/**
 * SmartBulbPrediction.c3typ
 */
@db(index=["smartBulb, timestamp"])
type SmartBulbPrediction {
    smartBulb: SmartBulb
    // ...
}

In this example, a database index is being added to the SmartBulbPrediction entity type to optimize query performance for specific fields. The @db(index=["smartBulb, timestamp"]) annotation is used to define an index on the SmartBulbPrediction entity type. This index includes the smartBulb and timestamp fields, enabling more efficient querying when both fields are involved in filter conditions or sorting operations.

Benefits

  • Improved Query Performance: By indexing these fields, the database can locate and retrieve relevant records when queries include conditions on smartBulb and timestamp.
  • Optimized Sorting: If a query requires sorting based on timestamp after filtering on smartBulb, this index helps return results in the desired order without additional sorting steps.
  • Combined Filtering: The index supports more efficient filtering when both fields are included in a query's WHERE clause.

Use case

This type of index is useful in scenarios where applications frequently query the SmartBulbPrediction entity for data related to specific smart bulbs within a certain time range. For example, a query that retrieves predictions for a given smart bulb over a specific period can directly benefit from this index, making the query execution faster and more efficient.

View indexes and index usage

In environments like C3 AI, where multiple applications might share the same database resources, understanding index usage can help in resource allocation decisions, ensuring that the most critical applications have the necessary performance optimizations in place.

Understanding index usage is also crucial for performance optimization in any database system. By analyzing how often an index is accessed, database administrators can determine if the index is beneficial or if it might be contributing to unnecessary overhead.

Prerequisite

To run the following DbAdmin APIs for understanding index usage, you must have cluster admin privileges. This access is crucial for analyzing how frequently an index is accessed.

Overview of DbAdmin.getIndexUsageStats()

getIndexUsageStats() – This method retrieves statistics related to the usage of indexes within the database. Specifically, it provides insights into how often indexes are being utilized during query operations.

Context and importance

  • Index Management – If certain indexes are rarely used, it might make sense to drop them to reduce the maintenance burden. Conversely, if some indexes are heavily used, it might be worthwhile to create additional indexes or optimize existing ones to enhance query performance.
  • Query Tuning – The statistics obtained from getIndexUsageStats() can inform decisions regarding query tuning. If specific queries are consistently slow, the insights from index usage can guide adjustments in the database schema or query structure to improve performance.

Conclusion

The DbAdmin.getIndexUsageStats() method serves as a valuable tool for C3 AI users and administrators to monitor and analyze the effectiveness of database indexes. By leveraging this functionality, they can ensure that their data models remain efficient and performant, aligning with the broader goals of data management and optimization within the C3 Agentic AI Platform.

Overview of DbAdmin.getTableIndexes()

getTableIndexes() – This method is designed to retrieve information about the indexes associated with specific tables in the database. It provides details about the indexes that have been created for a given table, including their names, types, and any other relevant properties.

Context and importance

  • Index Management – Understanding which indexes exist for a table is crucial for effective database management. Indexes improve query performance by allowing the database to locate data more efficiently. The getTableIndexes() method allows administrators to review and manage these indexes.
  • Performance Optimization – By retrieving the list of indexes for a specific table, users can analyze their impact on query performance. This helps in identifying redundant or unused indexes that can be removed to optimize storage and improve performance.
  • Database Design – When designing or refining a database schema, knowing the existing indexes can inform decisions about how to structure tables and queries. It helps ensure that the most effective indexing strategies are employed to enhance data retrieval.
  • Monitoring Changes – Over time, as applications evolve, the data access patterns may change. The getTableIndexes() method enables administrators to monitor and adjust the indexing strategy based on how the application interacts with the data.

Use cases

  • Retrieving Index Information – Users can call this method to get a comprehensive list of indexes for a particular table, including information on primary keys, foreign keys, and other indexes that improve data retrieval efficiency.
  • Database Optimization – Administrators can use the output of this method to make informed decisions about which indexes to keep, modify, or remove based on their usage and effectiveness.
  • Reporting and Documentation – The information retrieved can be useful for generating reports on database structure and for documentation purposes, helping teams understand the indexing strategy employed in the database.

Conclusion

The DbAdmin.getTableIndexes() method is a valuable function within the C3 Agentic AI Platform that allows users and database administrators to manage and optimize database performance effectively. By understanding the indexing structure of their tables, users can enhance query efficiency, maintain optimal data access strategies, and ensure the overall performance of their applications.

Was this page helpful?