Optimize queries with range and inequality filters on multiple properties

This page provides examples of indexing strategies that you can use for querieswith range and inequality filters on multiple fields to create an efficientquery experience.

Before you optimize yourqueries, read aboutrange and inequality filters on multiple properties concepts .

Optimize queries with Query Explain

To determine if the query and indexes used are optimal, you can create a queryusingQuery Explain and review the execution summary.

Java

...// Build the queryQuery<Entity>query=Query.newEntityQueryBuilder().setKind("employees").setFilter(CompositeFilter.and(PropertyFilter.gt("salary",100000),PropertyFilter.gt("experience",0))).setOrderBy(OrderBy("experience"),OrderBy("salary")).build();// Set the explain options to get back *only* the plan summaryQueryResults<Entity>results=datastore.run(query,ExplainOptions.newBuilder().build());// Get the explain metricsOptional<ExplainMetrics>explainMetrics=results.getExplainMetrics();if(!explainMetrics.isPresent()){thrownewException("No explain metrics returned");}// Get the plan summaryPlanSummaryplanSummary=explainMetrics.get().getPlanSummary();List<Map<String,Object>>indexesUsed=planSummary.getIndexesUsed();System.out.println("----- Indexes Used -----");indexesUsed.forEach(map->map.forEach((s,o)->System.out.println(s+": "+o)));// Get the execution statsif(!explainMetrics.getExecutionStats().isPresent()){thrownewException("No execution stats returned");}ExecutionStatsqueryStats=explainMetrics.getExecutionStats().get();Map<String,Object>debugStats=queryStats.getDebugStats();System.out.println("----- Debug Stats -----");debugStats.forEach((s,o)->System.out.println(s+": "+o));

The following example shows how the use of correct index ordering saves thenumber of entities that Firestore in Datastore mode scans.

Simple queries

With theearlier example of a collection of employees, the simple querythat runs with the(salary, experience) index is as follows:

GQL

SELECT*FROM/employeesWHEREsalary >100000ANDexperience >0ORDERBYexperience,salary;

Java

Query<Entity>query=Query.newEntityQueryBuilder().setKind("employees").setFilter(CompositeFilter.and(PropertyFilter.gt("salary",100000),PropertyFilter.gt("experience",0))).setOrderBy(OrderBy("experience"),OrderBy("salary")).build();

The query scans 95000 index entries only to return 5 entities. A large numberof index entries were read but filtered out because they did not satisfy thequery predicate.

// Output query planning info{"indexesUsed":[{"query_scope":"Collection Group","properties":"(experience ASC, salary ASC, __name__ ASC)"}]},// Output Query Execution Stats{"resultsReturned":"5","executionDuration":"2.5s","readOperations":"100","debugStats":{"index_entries_scanned":"95000","documents_scanned":"5","billing_details":{"documents_billable":"5","index_entries_billable":"95000","small_ops":"0","min_query_cost":"0"}}}

As per the earlier example, we can infer that thesalary constraint is moreselective than theexperience constraint.

GQL

SELECT*FROM/employeesWHEREsalary >100000ANDexperience >0ORDERBYsalary,experience;

Java

Query<Entity>query=Query.newEntityQueryBuilder().setKind("employees").setFilter(CompositeFilter.and(PropertyFilter.gt("salary",100000),PropertyFilter.gt("experience",0))).setOrderBy(OrderBy("salary"),OrderBy("experience")).build();

When you explicitly use theorderBy() clause to add the predicates in theearlier order, Firestore in Datastore mode uses the(salary, experience) indexto run the query. Since the selection of the first range filter isbetter than the earlier query, the query runs faster and is cost-efficient.

// Output query planning info{"indexesUsed":[{"query_scope":"Collection Group","properties":"(salary ASC, experience ASC, __name__ ASC)"}],// Output Query Execution Stats"resultsReturned":"5","executionDuration":"0.2s","readOperations":"6","debugStats":{"index_entries_scanned":"1000","documents_scanned":"5","billing_details":{"documents_billable":"5","index_entries_billable":"1000","small_ops":"0","min_query_cost":"0"}}}

What's next

Except as otherwise noted, the content of this page is licensed under theCreative Commons Attribution 4.0 License, and code samples are licensed under theApache 2.0 License. For details, see theGoogle Developers Site Policies. Java is a registered trademark of Oracle and/or its affiliates.

Last updated 2025-12-15 UTC.