Cloud Spanner provides optimizer control through query optimizer versioning. This guide shows you how to manage the query optimizer version that your queries use.
As we roll out updates to Cloud Spanner's query optimizer, our goal is to improve query execution plans and deliver improved performance for your queries. By default, Cloud Spanner uses the latest query optimizer version for each database. With query optimizer versioning, you can run queries against an older version of the optimizer that gives you predictable performance.
List supported optimizer versions
The query optimizer version is an integer value, incrementing by 1 with each update. The latest version of the query optimizer is 2.
Execute the following SQL statement to return a list of all supported optimizer versions, along with their corresponding release dates. The largest version number returned is the latest supported version of the optimizer.
SELECT * FROM SPANNER_SYS.SUPPORTED_OPTIMIZER_VERSIONS
By default, Cloud Spanner uses the latest version of the optimizer, which is currently 2. Use one of the methods described in this guide to override this default behavior for your scenario.
For details about each version, see Query optimizer version history.
Version override precedence
Cloud Spanner offers multiple ways to change the optimizer version. For example, you can set the version for a specific query or configure the version in the client library at the process or query level. When the version is set in multiple ways, the following precedence order applies. (Select a link to jump to that section in this document).
Cloud Spanner default ← database option ← client app ← environment variable ← client query ← statement hint
We can interpret the above precedence order as follows: When you create a database, it uses the Cloud Spanner default optimizer version, which is always the latest version. Setting the optimizer version using one of the methods listed above takes precedence over anything to the left of it. For example, setting the optimizer for an app using an environment variable takes precedence over any value you set for the database using the database option. Setting the optimizer version through a statement hint has the highest precedence for the given query, taking precedence over the value set using any other method.
Let's now look at each method in more detail.
Set optimizer version for a database using ALTER DATABASE
You can set the default optimizer version on a database using the following ALTER DATABASE
DDL command.
ALTER DATABASE MyDatabase
SET OPTIONS (optimizer_version = 2);
You can run the ALTER DATABASE
in gcloud spanner with the
gcloud spanner databases ddl update
command as follows.
gcloud spanner databases ddl update MyDatabase --instance=test-instance \
--ddl='ALTER DATABASE MyDatabase SET OPTIONS ( optimizer_version = 2 )'
Setting the database option to NULL
clears it, and makes the database default
to the latest version. This is the default for new databases.
To see the current value of this option for a database, read it from the INFORMATION_SCHEMA.DATABASE_OPTIONS
view.
Set optimizer version for a client app
When you are programmatically interacting with Cloud Spanner through client libraries, there are a number of ways to change the optimizer version for your client application.
An application can set a global query option on the client library by configuring the query options property as shown in the following code snippets. The optimizer version setting is stored in the client instance and is applied to all queries run throughout the lifetime of the client. Even though the options apply at a database level in the backend, when the options are set at a client level, they apply to all databases connected to through that client.
C++
C#
Go
Java
Node.js
PHP
Python
Ruby
Set optimizer version for a client app using an environment variable
To make it easier to try different optimizer versions without having to
recompile your app, you can set the SPANNER_OPTIMIZER_VERSION
environment
variable and run your app, as shown in the following snippet.
Linux / macOS
export SPANNER_OPTIMIZER_VERSION="2"
Windows
set SPANNER_OPTIMIZER_VERSION="2"
The specified query optimizer version value is read and stored in the client instance at client initialization time and applied to all queries run throughout the lifetime of the client.
Set optimizer version for a client query
You can specify a value for optimizer version at the query level in your client application by specifying a query options property when building your query. This is illustrated in the following code snippets for each supported language.
C++
C#
Go
Java
Node.js
PHP
Python
Ruby
Set optimizer version for a query using a statement hint
A statement hint is a hint on a query statement that changes the execution
of the query from the default behavior. Setting the OPTIMIZER_VERSION
hint on
a statement forces that query to run using the specified query optimizer
version.
The OPTIMIZER_VERSION
hint has the highest optimizer version precedence. If the
statement hint is specified, it will be used regardless of all other optimizer
version settings.
@{OPTIMIZER_VERSION=2} SELECT * FROM MyTable
You can also use the latest literal to set the optimizer version for a query to the latest version as shown here.
@{OPTIMIZER_VERSION=latest} SELECT * FROM MyTable
Set optimizer version when using the JDBC driver
You can override the default value of the optimizer version by specifying a version in the JDBC connection string as shown in the following example.
You can also set the query optimizer version using the SET OPTIMIZER_VERSION
statement as shown in the following example.
For more details on using the open-source driver, see Using the open-source JDBC driver.
How invalid optimizer versions are handled
Cloud Spanner supports a range of optimizer versions. This range changes over time when the query optimizer is updated. If the version you specify when using one of the methods described in this guide is out of range, Cloud Spanner fails the query. For example if you attempt to run a query with optimizer version = 100, and assuming that exceeds the current maximum value, you would receive the following error.
Query optimizer version: 100 is not supported
Determine the query optimizer version used to run a query
The optimizer version used for a query is visible in gcloud spanner and through the Cloud Console.
gcloud spanner
To see the version used when running a query in gcloud spanner, set
the --query-mode
flag to PROFILE
as shown in the following snippet.
gcloud spanner databases execute-sql MyDatabase --instance=test-instance \
--query-mode=PROFILE --sql='SELECT * FROM MyTable'
Cloud Console
To view the optimizer version used for a query, run the query in the Query database view of the Cloud Console and then select the Explanation tab. You should see a message similar to the following:
This query was run using optimizer version 2.
Visualize query optimizer version in Metrics Explorer
Cloud Monitoring collects measurements to help you understand how your applications and system services are performing. One of the metrics collected for Cloud Spanner is Count of queries, which measures the number of queries in an instance, sampled over time. While this metric is very useful to view queries grouped by error code, we can also use it to see what optimizer version was used to run each query.
You can use Metrics Explorer in Cloud Console to visualize Count of queries for your database instance. Figure 1 shows the count of queries for three databases. You can see which optimizer version is being used in each database.
The table below the chart in this figure shows that my-db-1 attempted to run a query with an invalid optimizer version, returning the status Bad usage and resulting in a query count of 0. The other databases ran queries using versions 1 and 2 of the optimizer respectively.
Figure 1. Count of queries displayed in Metrics Explorer with queries grouped by optimizer version.
To set up a similar chart for your instance:
- Navigate to the Metrics Explorer in the Cloud Console.
- In the Resource type field, select
Cloud Spanner Instance
. - In the Metric field, select
Count of queries
. - In the Group By field, select
database
,optimizer_version
, andstatus
.
Not shown in this example is the case where a different optimizer version is being used for different queries in the same database. In that case, the chart would display a bar segment for each combination of database and optimizer version.
To learn how to use Cloud Monitoring to monitor your Cloud Spanner instances, see Monitoring with Cloud Monitoring