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

Before you optimize your queries, read aboutthe related concepts.

Optimize queries with Query Explain

To determine if your query and indexes are optimal, you can useQueryExplain to get the query plan summary and execution statisticsof the query:

Queryq=db.collection("employees").whereGreaterThan("salary",100000).whereGreaterThan("experience",0);ExplainResults<QuerySnapshot>explainResults=q.explain(ExplainOptions.builder().analyze(true).build()).get();ExplainMetricsmetrics=explainResults.getMetrics();PlanSummaryplanSummary=metrics.getPlanSummary();ExecutionStatsexecutionStats=metrics.getExecutionStats();System.out.println(planSummary.getIndexesUsed());System.out.println(stats.getResultsReturned());System.out.println(stats.getExecutionDuration());System.out.println(stats.getReadOperations());System.out.println(stats.getDebugStats());

letq=db.collection("employees").where("salary",">",100000).where("experience",">",0);letoptions={analyze:'true'};letexplainResults=awaitq.explain(options);letplanSummary=explainResults.metrics.planSummary;letstats=explainResults.metrics.executionStats;console.log(planSummary);console.log(stats);

The following example shows how the use of correct index ordering reduces thenumber of index entries thatCloud Firestore scans.

Simple queries

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

db.collection("employees").whereGreaterThan("salary",100000).whereGreaterThan("experience",0).orderBy("experience").orderBy("salary");

The query scans 95000 index entries only to return five documents. Since the querypredicate isn't satisfied, a large number of index entries are read but arefiltered out.

// Output query planning info{"indexesUsed":[{"properties":"(experience ASC, salary ASC, __name__ ASC)","query_scope":"Collection"}],// 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"}}}

You can infer from domain expertise that most employees will have at least someexperience but few will have a salary that is more than 100000. Given thisinsight, you can see that thesalary constraint is more selective than theexperience constraint. To influence the index thatCloud Firestore uses toexecute the query, specify anorderBy clause that orders thesalaryconstraint before theexperience constraint.

db.collection("employees").whereGreaterThan("salary",100000).whereGreaterThan("experience",0).orderBy("salary").orderBy("experience");

When you explicitly use theorderBy() clause to add the predicates,Cloud Firestore uses the(salary ASC, experience ASC) index to run the query.Since the selectivity of the first range filter is higher in this querycompared to the earlier query, the query runs faster and is more cost efficient.

// Output query planning info{"indexesUsed":[{"properties":"(salary ASC, experience ASC, __name__ ASC)","query_scope":"Collection"}],// 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

• Learn aboutQuery Explain.
• Learn aboutindexing best practices.