Query optimizer

This page describes how the Aerospike Database query optimizer chooses a secondary index (SI) 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) 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 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)
• 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)

Prerequisites

• Familiarity with secondary index queries 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.

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

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 SDK

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.

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 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, where you can confirm the index by name.

Tip

For short queries, choose the index during development. Compare entries_per_bval 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 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 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 (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
!=, 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
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 (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

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 SDK

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

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 (201) rather than quietly reading every record. Set it to true on the queries that should fall back instead. See 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, PI query metrics, and 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

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 SDK

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

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 SDK

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 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 (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 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 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. 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).

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 (201), no index matched. See 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

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 SDK

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 or the AEL concept guide, 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. An index still building reports Write-Only. The sindex-list info command reports the same two states as RW and WO.

2. Run the query as a long query, 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
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 (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 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
• Primary index queries
• Queries
• AEL concept guide
• Secondary indexes (Developer SDK)
• Manage queries
• Secondary index
• Error codes