Query performance

AgentDB keeps your queries fast without SQL. You never need to know how indexes work.

AgentDB watches how each query runs. When an index would make a query much faster, AgentDB says so in the query result. You or your agent then decide whether to create it. AgentDB never creates an index by itself.

#When AgentDB suggests an index

AgentDB checks a query only when its object has 5,000 records or more. Smaller objects are fast without indexes.

AgentDB suggests an index when all of these are true:

• The query filters with eq, in, lt, lte, gt, gte, is_null or starts_with, or it sorts by a field.
• The query reads at least 5,000 records.
• The index would cut the records read at least fivefold.
• The field has no index yet.

AgentDB does not suggest an index in these cases:

• The filter uses neq or contains. An index can't answer these.
• The field has few values that are all common. An example is a status with two values that each cover half the records.
• The field is a list, an object, rich text or a yes/no field.
• The filter is on a related object.

#What the hint looks like

A query result can carry a performance object next to the records. All other fields stay the same.

{
  "records": ["..."],
  "next_cursor": "...",
  "performance": {
    "index_recommended": true,
    "object": "contact",
    "field": "status",
    "reason": "Filtering contact by status reads about 80,000 records to find about 80 matches. An index on status lets AgentDB read only the matching records.",
    "evidence": {
      "duration_ms": 36,
      "records_scanned_estimate": "about 80,000",
      "records_matching_estimate": "about 80",
      "records_in_object_estimate": "about 80,000",
      "estimate": true,
      "plan": "full scan of contact records"
    },
    "suggested_operation": "indexes_create",
    "suggested_arguments": { "object": "contact", "field": "status", "name": "contact_status" },
    "can_create": true,
    "limits": { "used": 1, "max_per_object": 5, "used_in_workspace": 1, "max_per_workspace": 20 },
    "write_overhead": "Each index adds a little work to every create, update and delete of that object's records, and uses some storage. Create indexes only for filters and sorts you run often.",
    "policy": "recommend"
  }
}

The numbers are estimates. AgentDB samples about 1,000 records to make them, and estimate: true marks them.

The same hint reaches you at most once a day. You can ask for it again at any time (see below).

can_create is false when your key or role can't create indexes. Only owners and admins without permission rules can. Ask one of them to create it.

AgentDB says when a limit stops a recommendation. Each object can have 5 indexes, and each workspace 20. At the limit, the hint has index_recommended: false and limit_reached: true. Drop an index you no longer need first.

#Create the index

Create the index with the suggested arguments. Here the object is small, so the index is only an example.

{"tool": "schema_define_object", "arguments": {"name": "contact"}}

{"tool": "schema_define_field", "arguments": {"object": "contact", "name": "status", "type": "text"}}

{"tool": "indexes_create", "arguments": {"object": "contact", "field": "status", "name": "contact_status"}}

In the AgentDB app, open the Schema tab. Each object has a Performance section with its indexes and recommendations. Owners and admins see a Create index button there.

Each index adds a little work to every write of that object. Create indexes only for filters and sorts you run often. One index per field is enough.

#See all recommendations

Ask for the current recommendations at any time. AgentDB lists them from the queries run recently, slowest first. The list also shows your existing indexes and the limits.

{"tool": "indexes_list", "arguments": {"include_recommendations": true}}

Over REST, call GET /v1/workspaces/{ws}/indexes?recommendations=1. With the CLI, run agentdb indexes list --recommendations.

AgentDB keeps recent queries in memory only. After a restart the list starts empty.

#Check that the index helped

Add "explain": true to a query. The result then always has performance.explain with the time the query took and a short plan.

{"tool": "records_query", "arguments": {"object": "contact", "where": {"all": [{"field": "status", "op": "eq", "value": "churned"}]}, "explain": true}}

Run the query once before and once after you create the index. Compare duration_ms. After the index, plan names the index, for example uses index contact_status.

records_aggregate accepts explain too.

#For operators

Set AGENTDB_QUERY_ADVICE=off to turn query advice off. Queries then never carry performance, and the list of recommendations stays empty.

Creating an index also refreshes the statistics SQLite uses to plan queries. This takes about one pass over the records.