Configure range and composite indexes

AI applications use diverse data retrieval patterns to deliver results to users. A semantic search platform might filter documents by type, sort results by relevance score and date, and perform vector similarity searches all within a single user request.

Each query pattern requires specific index configurations to execute efficiently. Poorly designed indexes force Azure Cosmos DB to scan entire partitions or containers, dramatically increasing latency and request unit (RU) consumption. Understanding how to design indexes for common AI retrieval patterns helps you build applications that deliver consistent, cost-effective performance.

1. Design Range Indexes for Filter Operations

Range indexes support the most common filter operations in AI applications. Queries that filter documents by type, status, category, date ranges, or numeric thresholds use range indexes to locate matching items without scanning the entire container.

Context: Users filter documents by type (pdf, docx, image) and upload date. Concept: When both documentType and uploadDate have range indexes, Azure Cosmos DB uses the indexes to find matching documents efficiently. Without indexes, the query performs a full scan of the partition, consuming RUs proportional to the data size rather than the result size.

Example Code: Application Query (SQL):

SELECT * FROM c WHERE c.documentType = 'pdf' AND c.uploadDate > '2024-01-01'

String Functions and Range Indexes

Range indexes also support string functions that AI applications use for text matching. Functions like CONTAINS, STARTSWITH, and ENDSWITH can use range indexes when the indexed property appears as the first argument. These functions enable partial text matching without requiring full-text search infrastructure.

2. Configure Composite Indexes for Multi-Property Sorting

Queries that sort results by multiple properties require composite indexes. The composite index must match the exact sequence of properties and sort directions in the ORDER BY clause.

Context: AI applications often sort results by relevance score and then by date, or by category and then by title. Concept: Without a composite index matching the exact pattern, the query fails with an error.

Example Code:

Step 1: Database Configuration (JSON Policy)

{ 
  "compositeIndexes": [ 
    [ 
      { "path": "/relevanceScore", "order": "descending" }, 
      { "path": "/uploadDate", "order": "descending" } 
    ] 
  ] 
}

Step 2: Application Query (SQL)

SELECT * FROM c ORDER BY c.relevanceScore DESC, c.uploadDate DESC

3. Optimize Queries with Filters and ORDER BY

Many AI retrieval patterns filter on one property and sort by another.

Context: Users might filter documents by category and sort results by date. Concept: You can optimize these queries by including the filter property in the ORDER BY clause, which allows a single composite index to serve the entire query. This optimization significantly reduces RU consumption.

Example Code:

The Original Query (Unoptimized):

SELECT * FROM c WHERE c.documentType = 'pdf' ORDER BY c.uploadDate DESC

This uses a range index for the filter, but requires the database to manually sort the results in memory.

The Optimized Query (SQL):

-- We rewrite the query to include the filter property in ORDER BY
SELECT * FROM c WHERE c.documentType = 'pdf' ORDER BY c.documentType, c.uploadDate DESC

The Required Database Configuration (JSON Policy):

{ 
  "compositeIndexes": [ 
    [ 
      { "path": "/documentType", "order": "ascending" }, 
      { "path": "/uploadDate", "order": "descending" } 
    ],
    [ 
      { "path": "/category", "order": "ascending" }, 
      { "path": "/relevanceScore", "order": "descending" } 
    ] 
  ] 
}

This policy defines two composite indexes that optimize queries filtering by document type or category while sorting by date or relevance score.

4. Use Composite Indexes for Multi-Property Filters

Queries that filter on multiple properties can benefit from composite indexes.

Context: Filtering by category, department, and createdDate. Concept: When designing composite indexes for filters, place equality filters first in the index definition. Each composite index supports at most one range filter, which must be the last property in the index.

Example Code:

Application Query (SQL):

SELECT * FROM c WHERE c.category = 'reports' AND c.department = 'finance' AND c.createdDate > '2024-06-01'

Database Configuration (JSON Policy):

{ 
  "compositeIndexes": [ 
    [ 
      { "path": "/category", "order": "ascending" }, 
      { "path": "/department", "order": "ascending" }, 
      { "path": "/createdDate", "order": "ascending" } 
    ] 
  ] 
}

Handling Multiple Range Filters

If a query has multiple range filters, you need multiple composite indexes because each index supports only one range filter.

For a query filtering on age > 18 AND timestamp > someValue with an equality filter on name, you need two separate composite indexes:

1. (name ASC, age ASC) for the first range filter.
2. (name ASC, timestamp ASC) for the second range filter.

5. Configure Tuple Indexes for Array Element Filtering

AI applications often store structured data within arrays, such as document chunks with positions and text, tags with categories and weights, or events with timestamps and types.

Context: A document stores text chunks. A query finds chunks at specific positions with minimum token counts. Concept: When queries filter on multiple properties within array elements, tuple indexes improve query efficiency by indexing the property combinations within each array element.

Example Code:

JSON Document Structure:

{ 
  "id": "doc-123", 
  "title": "Annual Report 2024", 
  "chunks": [ 
    { "position": 0, "text": "Executive summary...", "tokens": 150 }, 
    { "position": 1, "text": "Financial highlights...", "tokens": 200 }
  ] 
}

Application Query (SQL):

SELECT c.id, chunk.text FROM c JOIN chunk IN c.chunks 
WHERE chunk.position >= 0 AND chunk.position < 3 AND chunk.tokens > 100

Database Configuration (JSON Policy):

{ 
  "includedPaths": [ 
    { "path": "/*" }, 
    { "path": "/chunks/[]/{position, tokens}/?" } 
  ] 
}

Tuple indexes are particularly valuable for AI applications that store chunked documents where queries frequently filter on chunk position, size, or other metadata.

6. Balance Read and Write Performance

Every index increases write latency and RU consumption because Azure Cosmos DB updates the indexes synchronously during write operations. AI applications with high write throughput (such as ingesting documents, updating embeddings, or processing real-time data) need to balance index coverage against write performance.

Consider these guidelines when designing indexes for AI workloads:

1. Include only queried properties: If a property is stored but never appears in query filters or ORDER BY clauses, exclude it from indexing.
2. Analyze query frequency: Create composite indexes for queries that execute frequently. Rare queries might not justify the write overhead of additional indexes.
3. Monitor RU consumption: Compare the RU cost of writes with and without specific indexes. If writes are the dominant workload, fewer indexes might provide better overall performance.
4. Use selective indexing: Instead of indexing all properties with /*, explicitly include only the paths that queries need.

7. Understand Index Transformation Behavior

When you modify an indexing policy, Azure Cosmos DB performs an asynchronous transformation to update indexes. Understanding this behavior helps you plan index changes without disrupting application performance.

• Adding indexes: New indexes don't improve query performance until the transformation completes. During transformation, queries continue using existing indexes or fall back to scans.
• Removing indexes: When you remove an index, queries immediately stop using it and fall back to scans. This happens before the transformation completes.
• Replacing indexes: If you're replacing one index pattern with another, add the new index first and wait for the transformation to complete. Then remove the old index. This approach ensures queries always have appropriate index support.