Optimize queries with range and inequality filters on multiple fields
This page provides examples of indexing strategy that should be used for queries with range & inequality filters on multiple fields to create an efficient query experience.
Read about the related concepts before optimizing your queries.
Optimize queries with Query Explain
To determine if the query and indexes used are optimal, you can use Query Explain to get the query plan summary and execution statistics of the query:
Java
Query q = db.collection("employees").whereGreaterThan("salary",
100000).whereGreaterThan("experience", 0);
ExplainResults<QuerySnapshot> explainResults = q.explain(ExplainOptions.builder().analyze(true).build()).get();
ExplainMetrics metrics = explainResults.getMetrics();
PlanSummary planSummary = metrics.getPlanSummary();
ExecutionStats executionStats = 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());
Node.js
let q = db.collection("employees")
.where("salary", ">", 100000)
.where("experience", ">",0);
let options = { analyze : 'true' };
let explainResults = await q.explain(options);
let planSummary = explainResults.metrics.planSummary;
let stats = explainResults.metrics.executionStats;
console.log(planSummary);
console.log(stats);
The following example shows how the use of correct index ordering reduces the number of index entries that Firestore scans.
Simple queries
With the earlier example of a collection of employees, the simple query
that runs with the (experience ASC, salary ASC)
index is as follows:
Java
db.collection("employees")
.whereGreaterThan("salary", 100000)
.whereGreaterThan("experience", 0)
.orderBy("experience")
.orderBy("salary");
The query scans 95000 index entries only to return 5 documents. Since the query predicate isn't satisfied, a large number of index entries are read, but filtered 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" } } }
We can infer from domain expertise that most employees will have at least some experience but few will have a salary that is more than
100000. Given this insight, we can conclude that the salary
constraint is more selective than the experience
constraint. To influence the index that Firestore uses to execute the query, specify an orderBy
clause that orders the salary
constraint before the experience
constraint.
Java
db.collection("employees")
.whereGreaterThan("salary", 100000)
.whereGreaterThan("experience", 0)
.orderBy("salary")
.orderBy("experience");
When you explicitly use the orderBy()
clause to add the predicates, Firestore uses the (salary ASC, experience ASC)
index to run the
query. Thus, since the selectivity of the first range filter is higher in this query compared 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 about Query Explain.
- Learn about indexing best practices.