An analytics team is troubleshooting severe latency regressions in analytical aggregation queries running on a Cloud Spanner Enterprise instance. Operational transactions continue to write updates, while reporting queries scan millions of rows. The team investigates the performance diagnostics and observes the following: Query execution plans reveal that the Table scan operator is consuming the majority of execution latency. Querying SPANNER_SYS.QUERY_STATS_TOP_* shows that the AVG_COLUMNAR_READ_SHARE metric for these queries is near 0%. The queries currently select all table columns using SELECT *. Which actions should the team take to diagnose and mitigate this retrieval bottleneck?

Rewrite queries to select only the essential columns required for aggregation, and allow Spanner's background compaction to build and populate the columnar representation.: ### What the Columnar Engine and Diagnostics Indicate Cloud Spanner's **columnar engine** provides accelerated scan performance for analytical workloads on operational data by storing single-column runs across blocks instead of colocating full rows. When diagnosing queries using `SPANNER_SYS.QUERY_STATS_TOP_*` and the **query execution plan**, an `AVG_COLUMNAR_READ_SHARE` near 0% indicates that Spanner is falling back to scanning row-oriented **PAX (Ressi)** storage rather than columnar storage. Furthermore, a high-latency **Table scan** node confirms that the query is scan-bound. ### How This Solves the Bottleneck - **Projecting Essential Columns**: Using `SELECT *` forces Spanner to retrieve every column in the schema. In columnar layouts, reading all columns diminishes columnar scan advantages because multiple individual column files must be scanned and reconstructed. Restricting projections to specific columns (e.g., `SELECT customer_id, SUM(order_total)`) allows Spanner to read strictly the necessary contiguous column blocks. - **Awaiting Background Compaction**: Spanner generates columnar data representations asynchronously during **background compaction**. A near 0% `AVG_COLUMNAR_READ_SHARE` indicates the newly loaded or heavily churned data has not yet completed compaction into columnar runs. Allowing automatic compaction to complete will naturally raise the columnar read share and improve scan speed by up to 200x. ### Key Advantages - **Drastic I/O Reduction**: Eliminates unnecessary data reads across unreferenced columns. - **Optimal Columnar Utilization**: Allows the storage engine to maximize memory bandwidth and vectorize operations across contiguous column runs. - **Zero ETL Overhead**: Preserves strong transactional consistency on operational data without maintaining separate analytical replicas. - **Targeted Performance Mitigation**: Directly resolves scan-bound bottlenecks identified in the query plan. ### Why This Is Optimal Because the diagnostic metrics clearly show a scan-bound profile with unpopulated columnar blocks, pruning projection lists and letting background compaction finalize columnar structures aligns precisely with the database engine's architecture without introducing operational overhead.

Spanner Diagnose Mitigate Columnar Scan Bottleneck | professional-cloud-data-engineer

Spanner Diagnose Mitigate Columnar Scan Bottleneck

Worked example. The correct answer is already marked and every option is explained below, so there is nothing to select here. To answer questions yourself, start the free trial.

An analytics team is troubleshooting severe latency regressions in analytical aggregation queries running on a Cloud Spanner Enterprise instance. Operational transactions continue to write updates, while reporting queries scan millions of rows.

The team investigates the performance diagnostics and observes the following:

Query execution plans reveal that the Table scan operator is consuming the majority of execution latency.
Querying SPANNER_SYS.QUERY_STATS_TOP_* shows that the AVG_COLUMNAR_READ_SHARE metric for these queries is near 0%.
The queries currently select all table columns using SELECT *.

Which actions should the team take to diagnose and mitigate this retrieval bottleneck?

Rewrite queries to select only the essential columns required for aggregation, and allow Spanner's background compaction to build and populate the columnar representation.

Configure an aggressive LIMIT clause on all aggregation queries and disable background compaction to conserve CPU utilization.

Add a non-storing secondary index on the aggregation columns and force an index scan using query hints to trigger the Cross Apply operator.

Export the data to Cloud Storage and use a manual Bigtable instance with HDD nodes to perform sequential row-key range scans.

Professional Cloud Data Engineer

Spanner Diagnose Mitigate Columnar Scan Bottleneck

Related questions