Analyze query execution with Query Explain

This page describes how to retrieve query execution information when you execute a query.

Use Query Explain

Use Query Explain to understand how your queries are being executed. This provides details that you can use to optimize your queries .

 import 
  
 { 
  
 field 
  
 } 
  
 from 
  
 '@google-cloud/firestore/pipelines' 
 ; 

 const 
  
 q 
  
 = 
  
 db 
 . 
 pipeline 
 () 
  
 . 
 collection 
 ( 
 '/users' 
 ) 
  
 . 
 sort 
 ( 
 field 
 ( 
 'status' 
 ). 
 ascending 
 ()) 
  
 . 
 limit 
 ( 
 100 
 ); 
 let 
  
 results 
 ; 
 try 
  
 { 
  
 results 
  
 = 
  
 await 
  
 q 
 . 
 execute 
 ({ 
  
 explainOptions 
 : 
  
 { 
  
 mode 
 : 
  
 'analyze' 
 , 
  
 outputFormat 
 : 
  
 'text' 
  
 } 
  
 }); 
 } 
  
 catch 
  
 ( 
 error 
 ) 
  
 { 
  
 console 
 . 
 log 
 ( 
 error 
 ); 
 } 
 const 
  
 metrics 
  
 = 
  
 results 
 ? 
 . 
 explainStats 
 ? 
 . 
 text 
 ; 
 console 
 . 
 log 
 ( 
 metrics 
 ); 

 Pipeline 
  
 q 
  
 = 
  
 db 
 . 
 pipeline 
 () 
  
 . 
 collection 
 ( 
 "/users" 
 ) 
  
 . 
 sort 
 ( 
 field 
 ( 
 "status" 
 ). 
 ascending 
 ()) 
  
 . 
 limit 
 ( 
 100 
 ); 
 PipelineExecuteOptions 
  
 pipelineOpts 
  
 = 
  
 new 
  
 PipelineExecuteOptions 
 (). 
 withExplainOptions 
 ( 
  
 new 
  
 ExplainOptions 
 (). 
 withExecutionMode 
 ( 
 ExplainOptions 
 . 
 ExecutionMode 
 . 
 ANALYZE 
 ) 
 ); 
 Pipeline 
 . 
 Snapshot 
  
 result 
  
 = 
  
 q 
 . 
 execute 
 ( 
 pipelineOpts 
 ). 
 get 
 (); 
 String 
  
 metrics 
  
 = 
  
 null 
 ; 
 if 
  
 ( 
 result 
 . 
 getExplainStats 
 () 
  
 != 
  
 null 
 ) 
  
 { 
  
 metrics 
  
 = 
  
 result 
 . 
 getExplainStats 
 (). 
 getText 
 (); 
  
 System 
 . 
 out 
 . 
 println 
 ( 
 metrics 
 ); 
 } 

Explain modes

Depending on what you want to debug, you can execute a query with Query Explain in different modes:

• analyze : Plans and executes the query. Returns planner information, runtime execution statistics and metrics, alongside the regular results produced by the query.

• explain : Plans the query, but does not execute it. Returns the planner information but no runtime statistics, metrics, or results. This is useful for debugging the behaviour of a query without running costly operations.

Analysis

The output of Query Explain contains two main components - the Summary Statistics and Execution Tree. Consider this query as an example:

 db.pipeline().collection('/users').sort(field("status").ascending()).limit(100) 

Summary Statistics

The top of the explained output contains a summary of the execution statistics. Use these statistics to determine if a query has high latency or cost. It also contains memory statistics which let you know how close your query is to memory limits .

 Execution:
 results returned: 2
 request peak memory usage: 20.25 KiB (20,736 B)
 data bytes read: 148 B
 entity row scanned: 2

Billing:
 read units: 1 

Execution Tree

The execution tree describes the query execution as a series of nodes. The bottom nodes (leaf nodes) retrieve data from the storage layer which traverses up the tree to generate a query response.

For details about each execution node, refer to the Execution reference .

For details on how to use this information to optimize your queries, see Optimize query execution .

The following is an example of an execution tree:

 Tree:
• Compute
|  $out_1: map_set($record_1, "__name__", $__name___1, "__key__", unset)
|  is query result: true
|
|  Execution:
|   records returned: 2
|   latency: 5.96 ms (local <1 ms)
|
└── • Compute
    |  $__name___1: map_get($record_1, "__key__")
    |
    |  Execution:
    |   records returned: 2
    |   latency: 5.88 ms (local <1 ms)
    |
    └── • MajorSort
        |  fields: [$v_1 ASC]
        |  output: [$record_1]
        |  limit: 100
        |
        |  Execution:
        |   records returned: 2
        |   latency: 5.86 ms (local <1 ms)
        |   peak memory usage: 20.25 KiB (20,736 B)
        |
        └── • Compute
            |  $v_1: map_get($record_1, "status")
            |
            |  Execution:
            |   records returned: 2
            |   latency: 5.23 ms (local <1 ms)
            |
            └── • TableScan
                   source: /users
                   order: UNDEFINED
                   properties: *
                   row range: (-∞..+∞)
                   output record: $record_1
                   variables: [$record_1]

                   Execution:
                    records returned: 2
                    latency: 4.68 ms
                    records scanned: 2
                    data bytes read: 148 B 

What's next

• To learn about the execution tree nodes, see the Query execution reference .
• To learn how to optimize your queries, see Optimize query execution .