IBM Cloudant Query performance guide

How to identify large scans

Use the _explain endpoint to check which index your query will use:

POST /orders/_explain
{
  "selector": {
    "username": "bob@email.com",
    "status": "cancelled",
    "date": {"$gte": "2018-01-01", "$lt": "2019-01-01"}
  }
}

If the response shows a specific index, evaluate whether the index is high or low selectivity. In this example, an index on the status field is likely a low selectivity index, so this query needs work:

{
  "index": {
    "ddoc": "_design/status-index",
    "def": {
      "fields": [
        {
          "status": "asc"
        }
      ]
    },
    "name": "d479bdddf50865e520a0193704c8b93a3bd48f77",
    "type": "json"
  }
}

If the response shows "name": "_all_docs", your query will scan the entire database:

{
  "index": {
    "ddoc": null,
    "name": "_all_docs",
    "type": "special"
  }
}

Solution: create appropriate indexes

For the query above, the optimal index is a compound (multi-field) JSON index covering the fields you're querying:

POST /orders/_index
{
  "index": {
    "fields": ["username", "status", "date"]
  },
  "name": "username-status-date-index",
  "type": "json"
}

Performance impact: In a 100,000 document database, this single change can reduce query time from 20+ seconds to under 10 milliseconds.

Production safety: prevent accidental large scans

If you know a certain index will be highly selective for a query, combine use_index with allow_fallback: false to force use of that index:

{
  "selector": {"username": "bob@email.com", "status": "cancelled"},
  "use_index": ["my_design_doc", "username-index"],
  "allow_fallback": false
}

This will return an error if my_design_doc/username-index is unusable for the query, rather than silently performing an expensive scan. The default for allow_fallback is true, meaning that IBM Cloudant will use a different index to respond to the query, potentially resulting in a large scan.

If you don't supply use_index, then allow_fallback: false will still prevent a query using the _all_docs index:

{
  "selector": {"username": "bob@email.com"},
  "allow_fallback": false
}

This will return an error if no suitable user-defined index exists, avoiding an expensive scan.

See the Query API documentation for more details on these query parameters.

Consistently combining use_index with allow_fallback is recommended practice for critical queries.

Critical rule 1: compound index selectivity

Order your equality fields (non-range fields) by selectivity - most selective first:

• user field: 50 docs per user (high selectivity) — put this first.
• status field: 10,000 docs per status (medium selectivity).

Optimal index ordering: ["username", "status", "date"] where date is the range field.

Critical rule 2: range fields must come last

When creating compound indexes, any field used with range operators ($gt, $gte, $lt, $lte) must appear last in the field list.

Wrong - causes partial database scans:

{
  "index": {
    "fields": ["date", "username", "status"]  // Bad - range field first
  }
}

Correct - enables efficient seeking:

{
  "index": {
    "fields": ["username", "status", "date"]  // Good - range field last
  }
}

Critical rule 3: sort fields must come last

Indexes are also used for sorting query results. When your query filters on fields like username and status, and sorts by another field like date, the index must include all three fields — with the sort field last.

For example, to support the following query:

{
  "selector": { "username": "bob@email.com", "status": "cancelled" },
  "sort": [ { "date": "asc" } ]
}

You need an index on ["username", "status", "date"]. In this structure, the sort field (date) must come last. This ordering ensures IBM Cloudant can use the index both to filter and to sort efficiently.

Why field ordering matters

IBM Cloudant indexes work like sorted tables. An index on ["username", "status", "date"] creates entries sorted first by user, then by status within each user, then by date within each user-status combination:

["alice", "paid", "2018-01-15"]
["alice", "paid", "2018-02-20"]
["bob", "cancelled", "2018-01-10"]
["bob", "cancelled", "2018-01-15"]
["bob", "paid", "2018-01-12"]

When using an efficient index for a query like user: "bob", status: "cancelled", date: {$gte: "2018-01-01"}, IBM Cloudant can:

1. Jump directly to Bob's records.
2. Find the cancelled orders within Bob's records.
3. Scan efficiently through the date range.

If date were first, IBM Cloudant would need to examine every document in the date range across all users and statuses.

Index type selection guidelines

Choose JSON indexes when:

• Query patterns are predictable and consistent.
• You need maximum performance for exact matches.
• Documents have consistent structure.
• You're primarily using $and, $eq, and range operators.

Choose text indexes when:

• You need $or operations across multiple fields.
• Documents have varying structures.
• You need full-text search capabilities.
• Query patterns are ad-hoc or user-driven.
• You're using $ne, $exists, or complex operators.

Example decision matrix:

// Predictable e-commerce queries → JSON index
{
  "selector": {
    "username": "bob@email.com",
    "status": "paid",
    "date": {"$gte": "2018-01-01"}
  }
}

// Flexible search interface → Text index  
{
  "selector": {
    "$or": [
      "status": {"$in": ["paid", "in-progress"]}
      "category": "electronics"
    ]
  }
}

Problem

If you create an index on ["username", "status", "date"], any document missing even one of these fields is excluded from the index entirely. This means:

// This document WON'T be indexed
{
  "_id": "order123",
  "username": "bob@email.com",
  "status": "paid"
  // Missing "date" field
}

Solutions

Option 1: Ensure consistent document structure

// Always include all indexed fields
{
  "_id": "order123",
  "username": "bob@email.com",
  "status": "paid",
  "date": null  // Use null for missing values
}

Option 2: Create multiple JSON indexes for different document structures

// Index for documents with all fields
{"fields": ["username", "status", "date"]}

// Index for documents without date
{"fields": ["username", "status"]}

Option 3: Use text indexes for flexible field requirements

{
  "index": {
    "fields": [
      {"name": "username", "type": "string"},
      {"name": "status", "type": "string"},
      {"name": "date", "type": "string"}
    ]
  },
  "type": "text"
}

A text index will index documents missing the date (or other) fields.

When to use partial indexes

Partial indexes are ideal when:

• You consistently query a subset of documents (e.g., only active records).
• Your database contains large amounts of irrelevant data for most queries.
• You want to reduce IBM Cloudant storage costs (pay-per-GB pricing).

Creating partial indexes

Use partial_filter_selector to specify which documents to include:

{
  "index": {
    "fields": ["date", "category"]
  },
  "partial_filter_selector": {
    "status": "published"
  },
  "name": "published-articles-index"
}

This index only includes documents where status: "published", making it much smaller and faster for queries on published content.

The partial_filter_selector can include any valid selector, not just simple single field selectors as shown in the example.

Real-world example: blog system

Scenario: A blog database with 100,000 articles, where 80,000 are drafts and 20,000 are published.

Traditional index (covers all documents):

{"fields": ["category", "publishDate"]}

Partial index (only published articles):

{
  "index": {"fields": ["category", "publishDate"]},
  "partial_filter_selector": {"status": "published"}
}

Benefits:

• Index size: 80% smaller
• Query speed: 2-3x faster
• Storage cost: Significantly reduced
• Cache efficiency: More relevant data stays in memory

Ensuring query compatibility with partial indexes

To use the index, your query selector must be compatible with the partial filter.

This works:

// Compatible - query includes the partial filter condition
{
  "selector": {
    "status": "published",    // Matches partial filter
    "category": "technology"
  }
}

This doesn't work:

// Incompatible - query doesn't match partial filter
{
  "selector": {
    "status": "draft",        // Contradicts partial filter
    "category": "technology"
  }
}

Understanding covering indexes

A covering index occurs when your index contains all the fields needed to satisfy both the query selector and the returned fields:

// Index covers these fields
{
  "index": {
    "fields": ["username", "status", "date", "amount"]
  }
}

// Query only needs indexed fields
{
  "selector": {"username": "bob@email.com", "status": "paid"},
  "fields": ["username", "date", "amount"]  // All fields in index
}

IBM Cloudant can return results directly from the index without reading the original documents, providing significant performance benefits.

Designing for coverage

Plan your indexes around common query patterns:

// Analyze your query patterns
const commonQueries = [
  {selector: {username: "...", status: "..."}, fields: ["date", "amount"]},
  {selector: {status: "...", date: {...}}, fields: ["username", "amount"]},
  {selector: {username: "..."}, fields: ["status", "date", "total"]}
];

// Design covering index
{
  "index": {
    "fields": ["username", "status", "date", "amount", "total"]
  }
}

The _explain endpoint will contain "covering": true when an index is found that can cover the query.

Index strategy review

• All production queries have appropriate indexes
• Compound indexes follow the range-field-last rule
• Field ordering reflects actual selectivity in your data
• No queries accidentally fall back to _all_docs

Performance validation

• Test queries with production-scale data volumes
• Monitor efficiency ratios (aim for >50%)
• Validate execution times meet SLA requirements
• Consider partial indexes for cost optimization

Monitoring setup

• Enable execution_stats logging
• Set up alerts for poor efficiency ratios
• Monitor index usage patterns
• Plan for query performance regression testing

Error handling

• Use allow_fallback: false to prevent expensive accidents
• Handle index selection errors gracefully
• Plan for index building time during deployments