---
title: "Query optimizer"
description: "Learn how the Aerospike query optimizer reads a query's AEL to select a secondary index, and what happens when no index can serve it."
---

# Query optimizer

> For the complete documentation index see: [llms.txt](https://aerospike.com/docs/llms.txt)
> 
> All documentation pages available in markdown.

This page describes how the Aerospike Database query optimizer chooses a [secondary index (SI)](https://aerospike.com/docs/database/learn/architecture/data-storage/secondary-index) for a query, which queries reach it, and how to confirm the index it chose.

## What the query optimizer does

On Database 8.2.0 and later, the query optimizer reads the [Aerospike Expression Language (AEL)](https://aerospike.com/docs/develop/client/sdk/concepts/ael/) a query carries and uses it to choose the most selective index available to that query. Your application does not name the index, and there is no server setting to turn the optimizer on or off. Create indexes that cover the bins your queries filter on, and matching queries start using them.

The clients that send AEL are the [Developer SDK](https://aerospike.com/docs/develop/client/sdk/) clients: the Java SDK and the Python SDK. AEL itself is language-agnostic, and the optimizer reads it on the server, so everything on this page behaves the same whichever of those clients you use.

A query that carries no AEL does not reach the optimizer. It must name the index itself, either with an explicit index filter or an index name.

::: existing applications keep working
Database 8.2.0 is fully backward compatible with queries from the legacy clients, which build filter expressions from compiled `Exp` / `Expression` classes. Those queries behave exactly as they did before, and need no code change. They do not gain automatic index selection, because they carry no AEL for the optimizer to read.
:::

### Applies to

-   Aerospike Database 8.2.0 and later (see [release notes](https://aerospike.com/docs/database/release))
-   Queries that carry AEL, sent by a Developer SDK client
-   Foreground read queries only. Background queries and aggregations must name an index themselves (see [Secondary index queries: Types of SI queries](https://aerospike.com/docs/develop/learn/queries/secondary-index/#types-of-si-queries))

### Prerequisites

-   Familiarity with [secondary index queries](https://aerospike.com/docs/develop/learn/queries/secondary-index/) and how an index filter differs from a filter expression
-   At least one readable SI matching the bins your query filters on. Without one, the query reads every record in the namespace or set. See [Primary index fallback](#primary-index-fallback).

## When the server chooses the index

Write the filter as AEL and name no index. The optimizer reads the AEL and selects the most selective readable SI that can serve it.

-   [Java SDK](#tab-panel-4854)
-   [Python SDK](#tab-panel-4855)

```java
import com.aerospike.client.sdk.Record;

import com.aerospike.client.sdk.RecordStream;

try (RecordStream stream = session.query(users)

        .where("$.status == 'active'")

        .execute()) {

    stream.forEach(result -> {

        Record row = result.recordOrThrow();

        // Process row

    });

}
```

```python
stream = (

    session.query(users)

    .where("$.status == 'active'")

    .execute()

)

for result in stream:

    row = result.record_or_raise()

    # Process row

stream.close()
```

Selection happens on the server, which adds a round trip to the cluster before the query runs. For long queries this is negligible. For short, high-rate queries it is not, so choose the index yourself in that case.

For the AEL a query can carry, see the [AEL concept guide](https://aerospike.com/docs/develop/client/sdk/concepts/ael/).

## When you choose the index

Name the index with an index-name hint — `forIndex` (Java) or `QueryHint(index_name=...)` (Python) — or send an explicit index filter. See [Secondary index queries](https://aerospike.com/docs/develop/learn/queries/secondary-index) for index-filter examples. Do this when the application already knows which index it wants, and for short queries where the extra round trip matters.

Short queries are optimized for lower latency and higher QPS. Request one with a hint — `withHint(hint -> hint.queryDuration(QueryDuration.SHORT))` (Java), or `with_hint(QueryHint(query_duration=QueryDuration.SHORT))` (Python). The Python SDK also accepts `.expected_duration(QueryDuration.SHORT)` directly on the query builder. `QueryDuration` is an enum in both clients, not a string. Long queries are the Developer SDK default, and only long queries appear in [`show jobs queries`](https://aerospike.com/docs/database/manage/cluster/queries/#list-queries), where you can confirm the index by name.

::: tip
For short queries, choose the index during development. Compare [`entries_per_bval`](https://aerospike.com/docs/database/reference/info#sindex-stat) across your candidate indexes — it reports how many records share each indexed value on average, so lower is more selective — then name the winner with `forIndex` or an explicit index filter.
:::

## How the optimizer picks an index

The server optimizer handles these predicate types:

-   Equality on integer, string, or blob
-   Integer range
-   Geospatial
-   Predicates on list elements, map keys, and map values, including values reached through a nested path (for example `'vip' in $.tags` or `$.profile.city == 'SF'`)
-   Exact expression-index match when the predicate matches the indexed expression

When more than one readable index can serve a predicate, the server picks the index where the fewest records share each indexed value. That is the [`entries_per_bval`](https://aerospike.com/docs/database/reference/info#sindex-stat) statistic, so lower is more selective. Ties break on the lower total key count.

An index scoped to the query’s set is preferred, but an index created on the whole namespace is also eligible.

Two cases surprise people. A newly created index with no records in it yet ranks first, so it captures matching queries as soon as it becomes readable. An index that is still populating has no statistics yet and ranks last, so it is not chosen until its statistics are generated.

Those statistics are generated when an index is created, at server startup, and hourly after that, so they can be up to an hour behind your data. The server does not recount on every query.

An index that is still building is never chosen. While it populates, [`show sindex`](https://aerospike.com/docs/database/tools/asadm/live-mode/#show-sindex) reports its state as `Write-Only`: the index is absorbing writes but cannot yet serve reads. Readability is checked on the node that plans the query, so a plan can name an index that another node has not finished building. That node returns [`AS_ERR_SINDEX_NOT_READABLE`](https://aerospike.com/docs/database/reference/error-codes/#server-errors) (203). Wait until the index reports `Read-Write` on every node before you rely on it.

### Indexable operator surface

A secondary index executes exactly three filter shapes: an equality, an integer range, or a geospatial compare. A predicate that does not match one of those three cannot use an index. If it is one part of an `and`, only that part is skipped and the server checks it on each record the index returns, so the result set is unchanged. If it is the whole filter, the query runs against the primary index.

| Shape | Supported form | Indexable | Notes |
| --- | --- | --- | --- |
| Integer | `==`, `>`, `>=`, `<`, `<=`. Same-bin range bounds are intersected | Yes | `$.age >= 18 and $.age <= 65` |
| String / blob | `==` only, with a literal under 2048 bytes | Yes | Keys are stored hashed with no range ordering, so `>`, `<` and `BETWEEN` fall back to PI. Literals of 2048 bytes or more are dropped from index selection without error and re-evaluated per record. |
| Geospatial | `geoCompare()` against a compile-time literal under 1 MiB | Yes | Verifies point-in-region and region-contains-point. Region literals of 1 MiB or more are dropped from index selection without error and re-evaluated per record. |
| Float / boolean / HLL value | n/a | No | No index key type exists, so the query falls back to PI |
| Collection types | LIST, MAPKEYS, MAPVALUES: containment, `exists()`, closed integer range selectors | Yes | `SET` index type is never selected |
| CDT context | Nested path up to 7 steps, single key or index per step | Yes | Deeper or by-value paths fall back to PI |
| Expression indexes | The result type sets the surface. A numeric result supports `==` and range, a string result supports `==` only | Yes | The predicate must be written exactly as the index was defined, operand for operand. `$.price * $.qty` does not match an index defined on `$.qty * $.price`. An expression index on a bare bin or a path read is not selected; index the bin, or the path with a CDT context, instead. An expression index with a collection result (LIST, MAPKEYS, MAPVALUES) is not selected either; query it with an explicit index filter |
| `and` | Multiple predicates. A part that cannot use an index is skipped at selection time and checked on each record instead | Yes | `$.age > 10 and ($.age < 50 or $.city == 'SF')` selects on `age`. The result set is unchanged, but the query reads more records than it would with a matching index |
| Bare `or` / `not` / `exclusive` | Whole filter is unindexable | No | The query runs against the primary index. See [Primary index fallback](#primary-index-fallback) |
| `!=`, regex | n/a | No | No index-filter shape represents either, so the query falls back to PI |
| Contradictory `and` on one bin | Provably empty | n/a | The server proves no record can match and returns `AS_ERR_FILTERED_OUT` (27) without reading any records |
| Operation applied to a bin | Only with an expression index on that exact expression | No | An index on `name` cannot serve `$.name.upper() == 'ronen'`. See the caution below |

::: caution
**The optimizer does no algebraic rewriting.** A predicate that applies an operation to a bin, such as `$.name.upper() == 'ronen'` or `$.age + 1 < 30`, can never be served by an index on the raw bin. The index holds the bin’s values, not the result of the operation. The only index that can serve such a predicate is an expression index whose stored definition is exactly that expression.
:::

## Query shapes and behavior

| Query shape | Behavior on Database 8.2.0 and later |
| --- | --- |
| Carries AEL, names no index | The optimizer selects the most selective readable SI that can serve the filter |
| Carries AEL, names an index with `forIndex` / `index_name` | The named index is used when it matches the filter and is readable. See [Index hints](#index-hints) |
| Names an index with an explicit index filter | The named index is used, and appears by name in long query jobs |
| Names an index that is missing or still building | Returns 201 or 203 |
| Carries no AEL and names no index | Runs against the primary index, reading every record in the namespace or set |
| Background or aggregation query | Must name an index. Returns 201 or 203 when that index is missing or still building |

::: a query with no usable index reads everything
A query that neither carries AEL nor names an index runs against the primary index, reading every record in the namespace or set and applying the filter to each one. The result set is correct, but the cost scales with the size of the set rather than the number of matches. Monitor query latency and `recs-succeeded` before a production rollout.
:::

## Index hints

AEL queries accept a hint through `withHint` (Java) or `with_hint` (Python). A hint names an index, not a predicate. `forIndex(name)` is a _soft hint_. The named index pre-empts cost-based selection when it matches a predicate and is readable. If it does not match, the optimizer falls through to cost-based selection rather than returning 201. `forIndex(name)` and `forBin(name)` are mutually exclusive. Each may be called at most once. Chain `hardHint()` (Java) or set `hard_hint=True` (Python) after naming an index to make the hint _strict_: if the named index cannot serve the query, it fails with [`AS_ERR_SINDEX_NOT_FOUND`](https://aerospike.com/docs/database/reference/error-codes/#server-errors) (201) instead of falling through. `queryDuration(duration)` overrides the expected query duration (which affects job-monitor visibility).

The two clients express the same hint differently: Java chains methods on a hint builder, Python passes fields to a frozen `QueryHint`.

| Hint | Java | Python |
| --- | --- | --- |
| Name an index (soft) | `hint.forIndex("age_idx")` | `QueryHint(index_name="age_idx")` |
| Make it strict | `.hardHint()` | `hard_hint=True` |
| Bypass index selection (escape hatch) | `hint.forBin("age")` | `QueryHint(bin_name="age")` |
| Set the expected duration | `.queryDuration(QueryDuration.SHORT)` | `query_duration=QueryDuration.SHORT` |
| Require an index for one query | `.disallowScansWithWhere()` | `allow_scans_with_where=False` |

A strict hint requires an index name. Java enforces this at compile time — `hardHint()` is only reachable after `forIndex` — and Python raises `ValueError: hard_hint requires index_name` when you construct the hint.

-   [Java SDK](#tab-panel-4856)
-   [Python SDK](#tab-panel-4857)

```java
import com.aerospike.client.sdk.policy.QueryDuration;

// Soft hint: prefer this index. Falls through to cost-based selection if it does not match.

session.query(users)

    .where("$.age > 30")

    .withHint(hint -> hint.forIndex("age_idx"))

    .execute();

// Strict hint: fail with 201 rather than fall through to another index.

session.query(users)

    .where("$.age > 30")

    .withHint(hint -> hint.forIndex("age_idx").hardHint())

    .execute();

// Override expected duration (for example, to appear in show jobs queries)

session.query(users)

    .where("$.status == 'active'")

    .withHint(hint -> hint.forIndex("status_idx").queryDuration(QueryDuration.LONG))

    .execute();
```

```python
from aerospike_sdk import QueryDuration, QueryHint

# Soft hint: prefer this index. Falls through to cost-based selection if it does not match.

stream = (

    session.query(users)

    .where("$.age > 30")

    .with_hint(QueryHint(index_name="age_idx"))

    .execute()

)

stream.close()

# Strict hint: fail with 201 rather than fall through to another index.

stream = (

    session.query(users)

    .where("$.age > 30")

    .with_hint(QueryHint(index_name="age_idx", hard_hint=True))

    .execute()

)

stream.close()

# Override expected duration (for example, to appear in show jobs queries)

stream = (

    session.query(users)

    .where("$.status == 'active'")

    .with_hint(QueryHint(index_name="status_idx", query_duration=QueryDuration.LONG))

    .execute()

)

stream.close()
```

::: forbin / bin_name is a migration escape hatch, not a hint
`forBin(name)` (Java) and `QueryHint(bin_name=...)` (Python) are not index hints. They turn off server-side index selection for that query: the query runs as a primary index (PI) query over every record in the namespace or set, with your predicate applied to each record. They also bypass `allowScansWithWhere`, so a query that would otherwise fail fast runs a full PI query instead, with no error. Use them only as a temporary bridge when migrating code that depended on client-side selection, and replace them with `forIndex(name)` / `QueryHint(index_name=...)` as soon as you know the index name.
:::
::: stale hints
A hint wins outright. If the index you name is readable and matches the predicate, the server uses it, however selective the alternatives are. That is what paginated queries need, since every page must use the same index. For everything else, leave the hint off and let the server choose. Otherwise a hint you set months ago keeps pinning the query to an index that is no longer the best one.
:::

When a query names an index explicitly and that index is readable, the server uses it. Naming an index that is missing or still building returns 201 or 203.

## Primary index fallback

When no readable SI can serve a query that has a `where` clause, the query either fails or runs as a PI query, depending on `allowScansWithWhere`. A PI query evaluates the predicate against every record in the namespace or set and returns the matching records: nothing in the result set changes, but the query reads the whole namespace or set to produce it.

`allowScansWithWhere` is `false` in `Behavior.DEFAULT`, so a query whose index was never created, or was dropped, fails with [`AS_ERR_SINDEX_NOT_FOUND`](https://aerospike.com/docs/database/reference/error-codes/#server-errors) (201) rather than quietly reading every record. Set it to `true` on the queries that should fall back instead. See [Control PI fallback](#control-pi-fallback).

::: pi fallback is silent
Once fallback is enabled, nothing in the result set marks it. A query whose index is missing keeps returning correct results while reading every record, so enable it deliberately rather than session-wide.
:::
::: caution
A PI query reads every record in every partition. It increases disk I/O and query-thread use and can affect transactional latency. Monitor [`show jobs queries`](https://aerospike.com/docs/database/manage/cluster/queries/#list-queries), PI query metrics, and [`query-threads-limit`](https://aerospike.com/docs/database/reference/config#service__query-threads-limit) when fallback is frequent.
:::

### Control PI fallback

`allowScansWithWhere` only applies to queries that have a `where` clause. Bare PI queries without a `where` clause are always allowed.

Per-Behavior (all queries in a session inherit the setting):

-   [Java SDK](#tab-panel-4858)
-   [Python SDK](#tab-panel-4859)

```java
import com.aerospike.client.sdk.policy.Behavior;

import com.aerospike.client.sdk.policy.Behavior.Selectors;

Behavior allowFallback = Behavior.DEFAULT.deriveWithChanges("allow-pi-fallback", b -> b

    .on(Selectors.reads().query(), ops -> ops

        .allowScansWithWhere(true)));

Session session = cluster.createSession(allowFallback);
```

```python
from aerospike_sdk import Behavior

from aerospike_sdk.policy import Settings

allow_fallback = Behavior.DEFAULT.derive_with_changes(

    "allow-pi-fallback",

    reads_query=Settings(allow_scans_with_where=True),

)

session = cluster.create_session(allow_fallback)
```

Per-query (overrides the Behavior for one query):

-   [Java SDK](#tab-panel-4860)
-   [Python SDK](#tab-panel-4861)

```java
import com.aerospike.client.sdk.Record;

import com.aerospike.client.sdk.RecordStream;

// Allow PI fallback for this query only.

try (RecordStream stream = session.query(products)

        .where("$.category == 'electronics'")

        .withHint(hint -> hint.allowScansWithWhere())

        .execute()) {

    stream.forEach(result -> {

        Record row = result.recordOrThrow();

        // Process row

    });

}
```

```python
from aerospike_sdk import QueryHint

# Allow PI fallback for this query only.

stream = (

    session.query(products)

    .where("$.category == 'electronics'")

    .with_hint(QueryHint(allow_scans_with_where=True))

    .execute()

)

for row in stream:

    pass  # Process row

stream.close()
```

To require an SI on a single query when the Behavior allows fallback, use `.withHint(hint -> hint.disallowScansWithWhere())` (Java) or `.with_hint(QueryHint(allow_scans_with_where=False))` (Python).

A query that carries no AEL, and background queries, are not affected by `allowScansWithWhere`. They must name an index themselves. See [How index changes affect queries](#how-index-changes-affect-queries) for what happens when the only matching SI is dropped.

## Masked bins and role-based access

The optimizer never selects a secondary index built on a masked bin for a role without `read-masked`. Such a query fails with [`AS_SEC_ERR_ROLE_VIOLATION`](https://aerospike.com/docs/database/reference/error-codes/#server-errors) (81), whether the index was chosen by the optimizer or named explicitly. This is not new in Database 8.2.0 and is not specific to the optimizer: it has applied to every client and query type since masking shipped in 8.1.1. See [Querying a masked bin](https://aerospike.com/docs/develop/learn/queries/secondary-index/#querying-a-masked-bin) for the full behavior, including what still runs.

## How index changes affect queries

Index topology affects query cost even when applications never name an index.

**Adding an index.** Create an SI on a bin, CDT path, or expression your queries already reference. See [Create an index](https://aerospike.com/docs/develop/learn/queries/secondary-index/#create-an-index) for the commands. Once the index is readable on every node, matching AEL queries can select it on the next query — including a brand-new empty index, which ranks as the most selective candidate. There is no client-side cache to wait for.

**Dropping an index.** See [Adding and removing secondary indexes](https://aerospike.com/docs/database/manage/cluster/queries/#adding-and-removing-secondary-indexes). Queries that name the dropped index explicitly return 201 or 203. AEL queries move to another readable index if one matches. If none does, they read every record instead, which is correct but much slower — watch query latency after a drop.

## Limitations in Database 8.2.0

The Database 8.2.0 query optimizer does not include:

-   Multi-index intersection or union plans
-   Expression canonicalization (for example, treating `amount + fee` and `fee + amount` as equivalent)
-   CDT context normalization beyond exact byte-level match
-   Cost-based choice between a slow SI and a PI query when an SI is available but expensive
-   Automatic index selection for queries that carry no AEL

Queries that send only an explicit index filter with no filter expression still return 201 or 203 when that index is missing.

## Verify

You can confirm index selection from your own application:

1.  Confirm your cluster runs Database 8.2.0 or later (see [release notes](https://aerospike.com/docs/database/release)).
    
2.  Run the query with `allowScansWithWhere` at its default of `false`. If it returns records, an index served it. If it fails with [`AS_ERR_SINDEX_NOT_FOUND`](https://aerospike.com/docs/database/reference/error-codes/#server-errors) (201), no index matched. See [Control PI fallback](#control-pi-fallback).
    
3.  Compare timings. Run the same predicate against a value you know matches few records and one you know matches many. An index-served query scales with the number of matches. A primary index query does not.
    

Step 2 in full. Run it against a bin you have indexed and one you have not: the first returns records, the second returns 201.

-   [Java SDK](#tab-panel-4862)
-   [Python SDK](#tab-panel-4863)

```java
import com.aerospike.client.sdk.AerospikeException;

import com.aerospike.client.sdk.RecordStream;

try (RecordStream stream = session.query(users)

        .where("$.age >= 21")

        .execute()) {

    System.out.println(stream.stream().count() + " records: an index served it");

} catch (AerospikeException e) {

    System.out.println("no index matched, code " + e.getResultCode());

}
```

```python
from aerospike_sdk.exceptions import AerospikeError

# allow_scans_with_where is false by default, so 201 means no index matched.

try:

    stream = session.query(users).where("$.age >= 21").execute()

    count = 0

    for _ in stream:

        count += 1

    print(f"{count} records: an index served it")

except AerospikeError as exc:

    print("no index matched:", exc)
```

For a self-contained program that creates the index first, see the **Complete example** in [Secondary indexes](https://aerospike.com/docs/develop/client/sdk/concepts/secondary-indexes/#complete-example) or the [AEL concept guide](https://aerospike.com/docs/develop/client/sdk/concepts/ael/#complete-example), both in Java and Python.

The server does not report the chosen index back to the client. To see the index by name you need cluster access:

1.  Confirm matching indexes report `Read-Write` on every node with [`show sindex`](https://aerospike.com/docs/database/tools/asadm/live-mode/#show-sindex). An index still building reports `Write-Only`. The [`sindex-list`](https://aerospike.com/docs/database/reference/info#sindex-list) info command reports the same two states as `RW` and `WO`.
    
2.  Run the query as a [long query](https://aerospike.com/docs/develop/learn/queries/#query-runtime-optimization), the Developer SDK default, so it appears in the job monitor. Short queries never appear in the job list.
    
3.  Run `asadm -e 'show jobs queries'` while the query is active. An SI query shows a `sindex-name` column holding the chosen index name. A PI query reports no index name, so `sindex-name` is either blank for that row or absent from the output entirely.
    

## Troubleshooting

| Symptom | Likely cause | What to do |
| --- | --- | --- |
| Query slower than expected | No readable SI matches the filter, so the query is reading every record | Create the index the filter needs, or name an index explicitly. Confirm the index reports `Read-Write` on every node |
| Query returns 201 unexpectedly | No readable SI matches the filter and `allowScansWithWhere` is `false` | Create an SI that covers the filter, wait until it reports `Read-Write`, or allow the query to read every record. See [Primary index fallback](#primary-index-fallback) |
| Query still returns 201 or 203 | The query names an index that is missing or still building | Fix the index name, drop the hint, or wait until the index reports `Read-Write` |
| Query returns 81 | The chosen SI is on a bin masked for the user’s role | Grant read access to the bin, change the predicate, or use a different index |
| Query returns 27 | The predicate cannot match any record, for example `$.age > 100 and $.age < 50`. The SDK raises this as an error rather than returning an empty result | Check the values bound into the predicate. The server detects this before reading anything, so the query returns immediately rather than timing out |
| Query returns [`AS_ERR_PARAMETER`](https://aerospike.com/docs/database/reference/error-codes/#server-errors) (4) | The server could not parse the AEL string. A common cause is `count()` on a bin with no `:LIST` or `:MAP` type pin | Fix the AEL syntax. A predicate the server understands but cannot serve from an index is never error 4 — it produces a PI query instead |
| Unexpected index chosen | Multiple readable indexes match, and the server picks the lowest cached `entries_per_bval` | Compare [`sindex-stat`](https://aerospike.com/docs/database/reference/info#sindex-stat) across candidates, drop redundant indexes, or pin the index with `withHint(hint -> hint.forIndex("age_idx"))` (Java) or `with_hint(QueryHint(index_name="age_idx"))` (Python). Do not use `forBin` / `bin_name` for this. It disables index selection and runs a PI query |
| Cannot confirm index in job monitor | Query finished before `show jobs queries`, or `queryDuration` / `expectedDuration` is `SHORT` | Re-run with `QueryDuration.LONG` or `expectedDuration` = `long` |

## Next steps

-   [Secondary index queries](https://aerospike.com/docs/develop/learn/queries/secondary-index/)
-   [Primary index queries](https://aerospike.com/docs/develop/learn/queries/primary-index/)
-   [Queries](https://aerospike.com/docs/develop/learn/queries/)
-   [AEL concept guide](https://aerospike.com/docs/develop/client/sdk/concepts/ael/)
-   [Secondary indexes (Developer SDK)](https://aerospike.com/docs/develop/client/sdk/concepts/secondary-indexes/)
-   [Manage queries](https://aerospike.com/docs/database/manage/cluster/queries/)
-   [Secondary index](https://aerospike.com/docs/database/learn/architecture/data-storage/secondary-index/)
-   [Error codes](https://aerospike.com/docs/database/reference/error-codes/#server-errors)