Optimize queries with range and inequality filters on multiple properties
Stay organized with collectionsSave and categorize content based on your preferences.
This page provides examples of indexing strategies that you can use for queries
with range and inequality filters on multiple fields to create an efficient
query experience.
To determine if the query and indexes used are optimal, you can create a query
usingQuery Explainand 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 the
number of entities that Firestore in Datastore mode scans.
Simple queries
With theearlier exampleof a collection of employees, the simple query
that runs with the(salary, experience)index is as follows:
The query scans 95000 index entries only to return 5 entities. A large number
of index entries were read but filtered out because they did not satisfy the
query predicate.
When you explicitly use theorderBy()clause to add the predicates in the
earlier order, Firestore in Datastore mode uses the(salary, experience)index
to run the query. Since the selection of the first range filter is
better than the earlier query, the query runs faster and is cost-efficient.
[[["Easy to understand","easyToUnderstand","thumb-up"],["Solved my problem","solvedMyProblem","thumb-up"],["Other","otherUp","thumb-up"]],[["Hard to understand","hardToUnderstand","thumb-down"],["Incorrect information or sample code","incorrectInformationOrSampleCode","thumb-down"],["Missing the information/samples I need","missingTheInformationSamplesINeed","thumb-down"],["Other","otherDown","thumb-down"]],["Last updated 2025-09-04 UTC."],[[["\u003cp\u003eThis page demonstrates indexing strategies for optimizing queries that involve range and inequality filters across multiple fields.\u003c/p\u003e\n"],["\u003cp\u003eUsing Query Explain helps determine if a query and its associated indexes are optimized for performance, allowing for a review of the execution summary.\u003c/p\u003e\n"],["\u003cp\u003eProper index ordering, by setting the more selective constraints first, significantly reduces the number of scanned entries and improves query efficiency and cost-effectiveness.\u003c/p\u003e\n"],["\u003cp\u003eThe provided examples illustrate how a simple query can scan thousands of index entries only to filter them out, whereas optimized indexing reduces the number of index entries scanned to find the matching documents.\u003c/p\u003e\n"]]],[],null,["# Optimize queries with range and inequality filters on multiple properties\n\nThis page provides examples of indexing strategies that you can use for queries\nwith range and inequality filters on multiple fields to create an efficient\nquery experience.\n\nBefore you optimize your\nqueries, read about [range and inequality filters on multiple properties](/datastore/docs/multiple-range-fields) concepts .\n\nOptimize queries with Query Explain\n-----------------------------------\n\nTo determine if the query and indexes used are optimal, you can create a query\nusing [Query Explain](/datastore/docs/query-explain-analyze) and review the execution summary. \n\n### Java\n\n ...\n // Build the query\n Query\u003cEntity\u003e query =\n Query.newEntityQueryBuilder()\n .setKind(\"employees\")\n .setFilter(\n CompositeFilter.and(\n PropertyFilter.gt(\"salary\", 100000), PropertyFilter.gt(\"experience\", 0)))\n .setOrderBy(OrderBy(\"experience\"), OrderBy(\"salary\"))\n .build();\n\n // Set the explain options to get back *only* the plan summary\n QueryResults\u003cEntity\u003e results = datastore.run(query, ExplainOptions.newBuilder().build());\n\n // Get the explain metrics\n Optional\u003cExplainMetrics\u003e explainMetrics = results.getExplainMetrics();\n if (!explainMetrics.isPresent()) {\n throw new Exception(\"No explain metrics returned\");\n }\n\n // Get the plan summary\n PlanSummary planSummary = explainMetrics.get().getPlanSummary();\n List\u003cMap\u003cString, Object\u003e\u003e indexesUsed = planSummary.getIndexesUsed();\n System.out.println(\"----- Indexes Used -----\");\n indexesUsed.forEach(map -\u003e map.forEach((s, o) -\u003e System.out.println(s + \": \" + o)));\n\n // Get the execution stats\n if (!explainMetrics.getExecutionStats().isPresent()) {\n throw new Exception(\"No execution stats returned\");\n }\n\n ExecutionStats queryStats = explainMetrics.getExecutionStats().get();\n Map\u003cString, Object\u003e debugStats = queryStats.getDebugStats();\n System.out.println(\"----- Debug Stats -----\");\n debugStats.forEach((s, o) -\u003e System.out.println(s + \": \" + o));\n\nThe following example shows how the use of correct index ordering saves the\nnumber of entities that Firestore in Datastore mode scans.\n\n### Simple queries\n\nWith the [earlier example](/datastore/docs/multiple-range-fields#indexing_considerations) of a collection of employees, the simple query\nthat runs with the `(salary, experience)` index is as follows: \n\n### GQL\n\n SELECT *\n FROM /employees\n WHERE salary \u003e 100000 AND experience \u003e 0\n ORDER BY experience, salary;\n\n### Java\n\n Query\u003cEntity\u003e query =\n Query.newEntityQueryBuilder()\n .setKind(\"employees\")\n .setFilter(\n CompositeFilter.and(\n PropertyFilter.gt(\"salary\", 100000), PropertyFilter.gt(\"experience\", 0)))\n .setOrderBy(OrderBy(\"experience\"), OrderBy(\"salary\"))\n .build();\n\nThe query scans 95000 index entries only to return 5 entities. A large number\nof index entries were read but filtered out because they did not satisfy the\nquery predicate. \n\n```scilab\n// Output query planning info\n{\n \"indexesUsed\": [\n {\n \"query_scope\": \"Collection Group\",\n \"properties\": \"(experience ASC, salary ASC, __name__ ASC)\"\n }\n ]\n },\n // Output Query Execution Stats\n {\n \"resultsReturned\": \"5\",\n \"executionDuration\": \"2.5s\",\n \"readOperations\": \"100\",\n \"debugStats\": {\n \"index_entries_scanned\": \"95000\",\n \"documents_scanned\": \"5\",\n \"billing_details\": {\n \"documents_billable\": \"5\",\n \"index_entries_billable\": \"95000\",\n \"small_ops\": \"0\",\n \"min_query_cost\": \"0\"\n }\n }\n }\n```\n\nAs per the earlier example, we can infer that the `salary` constraint is more\nselective than the `experience` constraint. \n\n### GQL\n\n SELECT *\n FROM /employees\n WHERE salary \u003e 100000 AND experience \u003e 0\n ORDER BY salary, experience;\n\n### Java\n\n Query\u003cEntity\u003e query =\n Query.newEntityQueryBuilder()\n .setKind(\"employees\")\n .setFilter(\n CompositeFilter.and(\n PropertyFilter.gt(\"salary\", 100000), PropertyFilter.gt(\"experience\", 0)))\n .setOrderBy(OrderBy(\"salary\"), OrderBy(\"experience\"))\n .build();\n\nWhen you explicitly use the `orderBy()` clause to add the predicates in the\nearlier order, Firestore in Datastore mode uses the `(salary, experience)` index\nto run the query. Since the selection of the first range filter is\nbetter than the earlier query, the query runs faster and is cost-efficient. \n\n```scilab\n // Output query planning info\n{\n \"indexesUsed\": [\n {\n \"query_scope\": \"Collection Group\",\n \"properties\": \"(salary ASC, experience ASC, __name__ ASC)\"\n }\n ],\n // Output Query Execution Stats\n \"resultsReturned\": \"5\",\n \"executionDuration\": \"0.2s\",\n \"readOperations\": \"6\",\n \"debugStats\": {\n \"index_entries_scanned\": \"1000\",\n \"documents_scanned\": \"5\",\n \"billing_details\": {\n \"documents_billable\": \"5\",\n \"index_entries_billable\": \"1000\",\n \"small_ops\": \"0\",\n \"min_query_cost\": \"0\"\n }\n }\n }\n```\n\nWhat's next\n-----------\n\n- Learn about [Query Explain](/datastore/docs/query-explain-analyze)."]]
