Diagnosing and Fixing High Read Latency in FaunaDB Caused by Missing or Inefficient Indexes

Problem and Takeaway

When a FaunaDB query shows latency above ~200 ms and the dashboard reports a high readOps count relative to the number of documents returned, the usual culprit is a collection scan or an index that does not cover the query’s filter predicates. Adding or reshaping an index so that its terms match the query’s equality filters (in the same order) typically reduces readOps by an order of magnitude and brings latency back into the low‑millisecond range.

Recognizable Condition

• Query latency > 200 ms (observed in the FaunaDB dashboard “Query” tab or driver‑side timing).
• readOps in the query stats are disproportionately high compared with computeOps and the result set size.
• The query uses Filter, Where, or a Match on an index that does not include all filtered fields.

Cause / Diagnostic Quick‑Reference Table

Symptom	Likely Cause	First Check
High readOps, low result count	Collection scan (no usable index)	Run the query with stats: true and note readOps.
High readOps but index exists	Index terms missing or out‑of‑order vs. query predicates	Compare index definition (terms array) with query filter order.
Latency spikes after index change	Background index rebuild in progress	Check index status in dashboard – “building” state.
Persistently high latency after optimal index	Hot shard, cross‑region replica lag, or throttling	Inspect replica metrics; open a support ticket with query + stats.

Ordered Diagnostic Checks

1. Capture baseline stats. In the Fauna Shell or dashboard, run the problematic query with stats: true. Example (Fauna Shell):

   client.query(
     q.Map(
       q.Paginate(q.Match(q.Index("users_by_email"), "[contact removed]")),
       q.Lambda("ref", q.Get(q.Var("ref")))
     ),
     { stats: true }
   )

   Record readOps, computeOps, and latency.
2. Identify the access path. Open the same query in the dashboard “Query” tab → “Explain”. The execution graph shows either a Collection node (full scan) or an Index node. If it shows Collection, no suitable index is being used.
3. Compare index definition to query predicates. Retrieve the index definition:

   client.query(q.Get(q.Index("users_by_email")))

   Inspect the terms array. For a query filtering on email then status, the index must have terms: [{field: ["data", "email"]}, {field: ["data", "status"]}] in that order.
4. Test a minimal index lookup. Create a temporary index that exactly matches the filter order and run a simple Match to see the cost difference:

   client.query(
     q.CreateIndex({
       name: "users_by_email_status",
       source: q.Collection("Users"),
       terms: [
         { field: ["data", "email"] },
         { field: ["data", "status"] }
       ]
     })
   )

   Then query:

   client.query(
     q.Map(
       q.Paginate(q.Match(q.Index("users_by_email_status"), "[contact removed]", "active")),
       q.Lambda("ref", q.Get(q.Var("ref")))
     ),
     { stats: true }
   )

   Expect readOps to drop to near the result count.
5. Validate under load. Run the optimized query repeatedly (e.g., 50 times) and monitor the dashboard for stable latency and readOps.

Fixes Tied to Findings

Missing Index → Create Covering Index

If step 2 shows a Collection node, create an index whose terms list mirrors every equality filter in the query, preserving order. Use the CreateIndex FQL shown in step 4. After creation, the index enters a “building” state; queries may still use the old path until the build completes (usually seconds to minutes depending on collection size).

Existing Index Mis‑ordered → Re‑create with Correct Term Order

FaunaDB does not allow altering an index’s terms after creation. Drop the old index and create a new one with the proper order. Dropping is instantaneous, but the new index must rebuild. Schedule this during low‑traffic windows if the collection is large.

Index Exists but Still High readOps → Verify Bindings and Values

Ensure the index’s values field (if used for sorting) does not force a scan. If the query only filters, omit values or set it to the primary key only. Also confirm that the query uses Match rather than Filter on a larger set.

Post‑Optimization Latency Remains High → Escalate

If latency > 200 ms persists after the index is active and readOps ≈ result count, check:

• Replica lag in the region serving the request (dashboard “Replication” tab).
• Hot shard indicators: uneven readOps distribution across partitions.
• Throttling limits on the account (see “Limits” page).

Gather the query text, index definition, and stats output, then open a FaunaDB support ticket.

Concrete Example: Users Collection

Assume a collection Users with documents like:

{
  "data": {
    "email": "[contact removed]",
    "status": "active",
    "createdAt": "2024-03-12T14:22:00Z"
  }
}

Original query (slow):

q.Map(
  q.Paginate(q.Filter(
    q.Documents(q.Collection("Users")),
    q.Lambda("doc",
      q.And(
        q.Equals(q.Select(["data", "email"], q.Get(q.Var("doc"))), "[contact removed]"),
        q.Equals(q.Select(["data", "status"], q.Get(q.Var("doc"))), "active")
      )
    )
  )),
  q.Lambda("ref", q.Get(q.Var("ref")))
)

Stats before fix: readOps: 12,400, latency 420 ms.

Optimized index:

CreateIndex({
  name: "users_by_email_status",
  source: Collection("Users"),
  terms: [
    { field: ["data", "email"] },
    { field: ["data", "status"] }
  ]
})

Optimized query (fast):

q.Map(
  q.Paginate(q.Match(q.Index("users_by_email_status"), "[contact removed]", "active")),
  q.Lambda("ref", q.Get(q.Var("ref")))
)

Stats after fix: readOps: 2, latency 12 ms.

Limitations & Trade‑offs

• Each additional index adds write‑operation cost (one extra writeOps per document write per index) and storage overhead.
• Index rebuilds are asynchronous; during rebuild the old index may still serve reads, causing mixed latency.
• FaunaDB does not support partial indexes or conditional terms; every document matching the source collection gets indexed.

Verification Checklist

1. Run the optimized query with stats: true – confirm readOps ≈ number of returned documents.
2. In the dashboard “Query” tab, verify the execution graph shows an Index node (not Collection).
3. Monitor the “Metrics” page for 5‑minute windows: latency should stay < 50 ms and readOps per query should be stable.
4. Check write‑operation metrics to ensure the new index’s write cost is acceptable for your workload.

Rollback (if needed)

If the new index causes unacceptable write‑load, drop it:

client.query(q.Delete(q.Index("users_by_email_status")))

This instantly removes the index; reads will revert to the previous access path (likely a collection scan). No data loss occurs.