Managing the query optimizer

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++

namespace spanner = ::google::cloud::spanner;
spanner::Client client(
    spanner::MakeConnection(db),
    spanner::ClientOptions().set_query_options(
        spanner::QueryOptions().set_optimizer_version("1")));

C#

var builder = new SpannerConnectionStringBuilder
{
    DataSource = $"projects/{projectId}/instances/{instanceId}/databases/{databaseId}"
};
// Create connection to Cloud Spanner.
using (var connection = new SpannerConnection(builder))
{
    // Set query options on the connection.
    connection.QueryOptions = QueryOptions.Empty.WithOptimizerVersion("1");
    var cmd = connection.CreateSelectCommand(
        "SELECT SingerId, AlbumId, AlbumTitle FROM Albums");
    using (var reader = await cmd.ExecuteReaderAsync())
    {
        while (await reader.ReadAsync())
        {
            Console.WriteLine("SingerId : "
            + reader.GetFieldValue<string>("SingerId")
            + " AlbumId : "
            + reader.GetFieldValue<string>("AlbumId")
            + " AlbumTitle : "
            + reader.GetFieldValue<string>("AlbumTitle"));
        }
    }
}

Go


import (
	"context"
	"fmt"
	"io"
	"time"

	"cloud.google.com/go/spanner"
	"google.golang.org/api/iterator"
	sppb "google.golang.org/genproto/googleapis/spanner/v1"
)

func createClientWithQueryOptions(w io.Writer, database string) error {
	ctx := context.Background()
	queryOptions := spanner.QueryOptions{
		Options: &sppb.ExecuteSqlRequest_QueryOptions{OptimizerVersion: "1"},
	}
	client, err := spanner.NewClientWithConfig(
		ctx, database, spanner.ClientConfig{QueryOptions: queryOptions},
	)
	if err != nil {
		return err
	}
	defer client.Close()

	stmt := spanner.Statement{SQL: `SELECT VenueId, VenueName, LastUpdateTime FROM Venues`}
	iter := client.Single().Query(ctx, stmt)
	defer iter.Stop()
	for {
		row, err := iter.Next()
		if err == iterator.Done {
			return nil
		}
		if err != nil {
			return err
		}
		var venueID int64
		var venueName string
		var lastUpdateTime time.Time
		if err := row.Columns(&venueID, &venueName, &lastUpdateTime); err != nil {
			return err
		}
		fmt.Fprintf(w, "%d %s %s\n", venueID, venueName, lastUpdateTime)
	}
}

Java

static void clientWithQueryOptions(DatabaseId db) {
  SpannerOptions options =
      SpannerOptions.newBuilder()
          .setDefaultQueryOptions(
              db, QueryOptions.newBuilder().setOptimizerVersion("1").build())
          .build();
  Spanner spanner = options.getService();
  DatabaseClient dbClient = spanner.getDatabaseClient(db);
  try (ResultSet resultSet =
      dbClient
          .singleUse()
          .executeQuery(Statement.of("SELECT SingerId, AlbumId, AlbumTitle FROM Albums"))) {
    while (resultSet.next()) {
      System.out.printf(
          "%d %d %s\n", resultSet.getLong(0), resultSet.getLong(1), resultSet.getString(2));
    }
  }
}

Node.js

// Imports the Google Cloud client library
const {Spanner} = require('@google-cloud/spanner');

/**
 * TODO(developer): Uncomment the following lines before running the sample.
 */
// const projectId = 'my-project-id';
// const instanceId = 'my-instance';
// const databaseId = 'my-database';

// Creates a client
const spanner = new Spanner({
  projectId: projectId,
});

// Gets a reference to a Cloud Spanner instance and database
const instance = spanner.instance(instanceId);
const database = instance.database(databaseId, {}, {optimizerVersion: '1'});

const query = {
  sql: `SELECT AlbumId, AlbumTitle, MarketingBudget
        FROM Albums
        ORDER BY AlbumTitle`,
};

// Queries rows from the Albums table
try {
  const [rows] = await database.run(query);

  rows.forEach(row => {
    const json = row.toJSON();
    const marketingBudget = json.MarketingBudget
      ? json.MarketingBudget
      : null; // This value is nullable
    console.log(
      `AlbumId: ${json.AlbumId}, AlbumTitle: ${json.AlbumTitle}, MarketingBudget: ${marketingBudget}`
    );
  });
} catch (err) {
  console.error('ERROR:', err);
} finally {
  // Close the database when finished.
  database.close();
}

PHP

use Google\Cloud\Spanner\SpannerClient;
use Google\Cloud\Spanner\Database;

/**
 * Create a client with query options.
 * Example:
 * ```
 * create_client_with_query_options($instanceId, $databaseId);
 * ```
 *
 * @param string $instanceId The Spanner instance ID.
 * @param string $databaseId The Spanner database ID.
 */
function create_client_with_query_options($instanceId, $databaseId)
{
    $spanner = new SpannerClient([
        'queryOptions' => [
            'optimizerVersion' => "1"
        ]
    ]);
    $instance = $spanner->instance($instanceId);
    $database = $instance->database($databaseId);

    $results = $database->execute(
        'SELECT VenueId, VenueName, LastUpdateTime FROM Venues'
    );

    foreach ($results as $row) {
        printf('VenueId: %s, VenueName: %s, LastUpdateTime: %s' . PHP_EOL,
            $row['VenueId'], $row['VenueName'], $row['LastUpdateTime']);
    }
}

Python

# instance_id = "your-spanner-instance"
# database_id = "your-spanner-db-id"
spanner_client = spanner.Client(query_options={"optimizer_version": "1"})
instance = spanner_client.instance(instance_id)
database = instance.database(database_id)

with database.snapshot() as snapshot:
    results = snapshot.execute_sql(
        "SELECT VenueId, VenueName, LastUpdateTime FROM Venues"
    )

    for row in results:
        print(u"VenueId: {}, VenueName: {}, LastUpdateTime: {}".format(*row))

Ruby

# project_id  = "Your Google Cloud project ID"
# instance_id = "Your Spanner instance ID"
# database_id = "Your Spanner database ID"

require "google/cloud/spanner"

query_options = { optimizer_version: "1" }

spanner = Google::Cloud::Spanner.new project: project_id
client  = spanner.client instance_id, database_id, query_options: query_options

sql_query = "SELECT VenueId, VenueName, LastUpdateTime FROM Venues"

client.execute(sql_query).rows.each do |row|
  puts "#{row[:VenueId]} #{row[:VenueName]} #{row[:LastUpdateTime]}"
end

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++

void QueryWithQueryOptions(google::cloud::spanner::Client client) {
  namespace spanner = ::google::cloud::spanner;
  auto sql = spanner::SqlStatement("SELECT SingerId, FirstName FROM Singers");
  auto opts = spanner::QueryOptions().set_optimizer_version("1");
  auto rows = client.ExecuteQuery(std::move(sql), std::move(opts));

  using RowType = std::tuple<std::int64_t, std::string>;
  for (auto const& row : spanner::StreamOf<RowType>(rows)) {
    if (!row) throw std::runtime_error(row.status().message());
    std::cout << "SingerId: " << std::get<0>(*row) << "\t";
    std::cout << "FirstName: " << std::get<1>(*row) << "\n";
  }
  std::cout << "Read completed for [spanner_query_with_query_options]\n";
}

C#

var builder = new SpannerConnectionStringBuilder
{
    DataSource = $"projects/{projectId}/instances/{instanceId}/databases/{databaseId}"
};
// Create connection to Cloud Spanner.
using (var connection = new SpannerConnection(builder))
{
    var cmd = connection.CreateSelectCommand(
        "SELECT SingerId, AlbumId, AlbumTitle FROM Albums");
    // Set query options just for this command.
    cmd.QueryOptions = QueryOptions.Empty.WithOptimizerVersion("1");
    using (var reader = await cmd.ExecuteReaderAsync())
    {
        while (await reader.ReadAsync())
        {
            Console.WriteLine("SingerId : "
            + reader.GetFieldValue<string>("SingerId")
            + " AlbumId : "
            + reader.GetFieldValue<string>("AlbumId")
            + " AlbumTitle : "
            + reader.GetFieldValue<string>("AlbumTitle"));
        }
    }
}

Go


import (
	"context"
	"fmt"
	"io"
	"time"

	"cloud.google.com/go/spanner"
	"google.golang.org/api/iterator"
	sppb "google.golang.org/genproto/googleapis/spanner/v1"
)

func queryWithQueryOptions(w io.Writer, db string) error {
	ctx := context.Background()
	client, err := spanner.NewClient(ctx, db)
	if err != nil {
		return err
	}
	defer client.Close()

	stmt := spanner.Statement{SQL: `SELECT VenueId, VenueName, LastUpdateTime FROM Venues`}
	queryOptions := spanner.QueryOptions{
		Options: &sppb.ExecuteSqlRequest_QueryOptions{OptimizerVersion: "1"},
	}
	iter := client.Single().QueryWithOptions(ctx, stmt, queryOptions)
	defer iter.Stop()
	for {
		row, err := iter.Next()
		if err == iterator.Done {
			return nil
		}
		if err != nil {
			return err
		}
		var venueID int64
		var venueName string
		var lastUpdateTime time.Time
		if err := row.Columns(&venueID, &venueName, &lastUpdateTime); err != nil {
			return err
		}
		fmt.Fprintf(w, "%d %s %s\n", venueID, venueName, lastUpdateTime)
	}
}

Java

static void queryWithQueryOptions(DatabaseClient dbClient) {
  try (ResultSet resultSet =
      dbClient
          .singleUse()
          .executeQuery(
              Statement
                  .newBuilder("SELECT SingerId, AlbumId, AlbumTitle FROM Albums")
                  .withQueryOptions(QueryOptions.newBuilder().setOptimizerVersion("1").build())
                  .build())) {
    while (resultSet.next()) {
      System.out.printf(
          "%d %d %s\n", resultSet.getLong(0), resultSet.getLong(1), resultSet.getString(2));
    }
  }
}

Node.js

// Imports the Google Cloud client library
const {Spanner} = require('@google-cloud/spanner');

/**
 * TODO(developer): Uncomment the following lines before running the sample.
 */
// const projectId = 'my-project-id';
// const instanceId = 'my-instance';
// const databaseId = 'my-database';

// Creates a client
const spanner = new Spanner({
  projectId: projectId,
});

// Gets a reference to a Cloud Spanner instance and database
const instance = spanner.instance(instanceId);
const database = instance.database(databaseId);

const query = {
  sql: `SELECT AlbumId, AlbumTitle, MarketingBudget
        FROM Albums
        ORDER BY AlbumTitle`,
  queryOptions: {
    optimizerVersion: 'latest',
  },
};

// Queries rows from the Albums table
try {
  const [rows] = await database.run(query);

  rows.forEach(row => {
    const json = row.toJSON();
    const marketingBudget = json.MarketingBudget
      ? json.MarketingBudget
      : null; // This value is nullable
    console.log(
      `AlbumId: ${json.AlbumId}, AlbumTitle: ${json.AlbumTitle}, MarketingBudget: ${marketingBudget}`
    );
  });
} catch (err) {
  console.error('ERROR:', err);
} finally {
  // Close the database when finished.
  database.close();
}

PHP

use Google\Cloud\Spanner\SpannerClient;
use Google\Cloud\Spanner\Database;

/**
 * Queries sample data using SQL with query options.
 * Example:
 * ```
 * query_data_with_query_options($instanceId, $databaseId);
 * ```
 *
 * @param string $instanceId The Spanner instance ID.
 * @param string $databaseId The Spanner database ID.
 */
function query_data_with_query_options($instanceId, $databaseId)
{
    $spanner = new SpannerClient();
    $instance = $spanner->instance($instanceId);
    $database = $instance->database($databaseId);

    $results = $database->execute(
        'SELECT VenueId, VenueName, LastUpdateTime FROM Venues',
        [
            'queryOptions' => [
                'optimizerVersion' => "1"
            ]
        ]
    );

    foreach ($results as $row) {
        printf('VenueId: %s, VenueName: %s, LastUpdateTime: %s' . PHP_EOL,
            $row['VenueId'], $row['VenueName'], $row['LastUpdateTime']);
    }
}

Python

# instance_id = "your-spanner-instance"
# database_id = "your-spanner-db-id"
spanner_client = spanner.Client()
instance = spanner_client.instance(instance_id)
database = instance.database(database_id)

with database.snapshot() as snapshot:
    results = snapshot.execute_sql(
        "SELECT VenueId, VenueName, LastUpdateTime FROM Venues",
        query_options={"optimizer_version": "1"},
    )

    for row in results:
        print(u"VenueId: {}, VenueName: {}, LastUpdateTime: {}".format(*row))

Ruby

# project_id  = "Your Google Cloud project ID"
# instance_id = "Your Spanner instance ID"
# database_id = "Your Spanner database ID"

require "google/cloud/spanner"

spanner = Google::Cloud::Spanner.new project: project_id
client  = spanner.client instance_id, database_id

sql_query = "SELECT VenueId, VenueName, LastUpdateTime FROM Venues"
query_options = { optimizer_version: "1" }

client.execute(sql_query, query_options: query_options).rows.each do |row|
  puts "#{row[:VenueId]} #{row[:VenueName]} #{row[:LastUpdateTime]}"
end

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.

import edu.umd.cs.findbugs.annotations.SuppressFBWarnings;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

class ConnectionWithQueryOptionsExample {

  static void connectionWithQueryOptions() throws SQLException {
    // TODO(developer): Replace these variables before running the sample.
    String projectId = "my-project";
    String instanceId = "my-instance";
    String databaseId = "my-database";
    connectionWithQueryOptions(projectId, instanceId, databaseId);
  }

  @SuppressFBWarnings(
      value = "OBL_UNSATISFIED_OBLIGATION",
      justification = "https://github.com/spotbugs/spotbugs/issues/293")
  static void connectionWithQueryOptions(String projectId, String instanceId, String databaseId)
      throws SQLException {
    String optimizerVersion = "1";
    String connectionUrl =
        String.format(
            "jdbc:cloudspanner:/projects/%s/instances/%s/databases/%s?optimizerVersion=%s",
            projectId, instanceId, databaseId, optimizerVersion);
    try (Connection connection = DriverManager.getConnection(connectionUrl);
        Statement statement = connection.createStatement()) {
      // Execute a query using the optimizer version '1'.
      try (ResultSet rs =
          statement.executeQuery(
              "SELECT SingerId, FirstName, LastName FROM Singers ORDER BY LastName")) {
        while (rs.next()) {
          System.out.printf("%d %s %s%n", rs.getLong(1), rs.getString(2), rs.getString(3));
        }
      }
      try (ResultSet rs = statement.executeQuery("SHOW VARIABLE OPTIMIZER_VERSION")) {
        while (rs.next()) {
          System.out.printf("Optimizer version: %s%n", rs.getString(1));
        }
      }
    }
  }
}

You can also set the query optimizer version using the SET OPTIMIZER_VERSION statement as shown in the following example.

import edu.umd.cs.findbugs.annotations.SuppressFBWarnings;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

class SetQueryOptionsExample {

  static void setQueryOptions() throws SQLException {
    // TODO(developer): Replace these variables before running the sample.
    String projectId = "my-project";
    String instanceId = "my-instance";
    String databaseId = "my-database";
    setQueryOptions(projectId, instanceId, databaseId);
  }

  @SuppressFBWarnings(
      value = "OBL_UNSATISFIED_OBLIGATION",
      justification = "https://github.com/spotbugs/spotbugs/issues/293")
  static void setQueryOptions(String projectId, String instanceId, String databaseId)
      throws SQLException {
    String connectionUrl =
        String.format(
            "jdbc:cloudspanner:/projects/%s/instances/%s/databases/%s",
            projectId, instanceId, databaseId);
    try (Connection connection = DriverManager.getConnection(connectionUrl);
        Statement statement = connection.createStatement()) {
      // Instruct the JDBC connection to use version '1' of the query optimizer.
      statement.execute("SET OPTIMIZER_VERSION='1'");
      // Execute a query using the latest optimizer version.
      try (ResultSet rs =
          statement.executeQuery(
              "SELECT SingerId, FirstName, LastName FROM Singers ORDER BY LastName")) {
        while (rs.next()) {
          System.out.printf("%d %s %s%n", rs.getLong(1), rs.getString(2), rs.getString(3));
        }
      }
      try (ResultSet rs = statement.executeQuery("SHOW VARIABLE OPTIMIZER_VERSION")) {
        while (rs.next()) {
          System.out.printf("Optimizer version: %s%n", rs.getString(1));
        }
      }
    }
  }
}

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.

Queries count in Metrics Explorer grouped by query optimizer version

Figure 1. Count of queries displayed in Metrics Explorer with queries grouped by optimizer version.

To set up a similar chart for your instance:

  1. Navigate to the Metrics Explorer in the Cloud Console.
  2. In the Resource type field, select Cloud Spanner Instance.
  3. In the Metric field, select Count of queries.
  4. In the Group By field, select database, optimizer_version, and status.

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