Spanner Diagnose Mitigate Columnar Scan Bottleneck

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.