When to Enable Skip Index — Cutting Wasted I/O in Mixed Workloads Without Extra Maintenance Pain
When transactions and reporting share the same OceanBase cluster, a drop in analytical query performance is usually first investigated from the angles of SQL, B+ tree indexes, and parallelism. Even when partition pruning is already in effect in the execution plan and the scan range has converged to a reasonable interval, a query may still read large amounts of data within a partition. This drives up response times, increases CPU and I/O overhead, and — during business peaks — affects short transactions and background compaction on the same node.
These problems are often not simply a matter of a table being too large or a missing index. More commonly, the scan phase reads in a large amount of data, only for part of it to be filtered out later. To arrive at the final matching results, the system still has to read all of that data first. This cost is what we call wasted scanning.
Skip Index targets exactly this problem. It is column-level pre-aggregated data written into the intermediate micro-block index layer of the SSTable — commonly MIN_MAX and NULL_COUNT, and in some scenarios SUM as well. Once a filter condition is pushed down to the storage layer, the engine uses these statistics to decide whether a data block still needs to be read: if a block cannot possibly contain the target rows, it skips reading and decoding that block, thereby reducing wasted I/O.
That said, Skip Index mainly addresses wasted reads in scan-oriented workloads, not point-lookup positioning — and it is not a universal capability that “speeds things up whenever there’s a WHERE clause." Under mixed workloads, its more important value lies in letting analytical queries consume less shared I/O and CPU, rather than simply making a single SQL statement faster.
Why Wasted Scanning Still Happens
As described above, wasted scanning refers to the I/O and decoding cost incurred by data that is read during the scan phase but later excluded by filtering. This cost does not automatically disappear just because routine optimizations have already been applied.
HTAP queries in OceanBase typically read both the merged baseline data and the newer incremental data at the same time. The columnar path can read only the columns involved in the query, and B+ tree indexes can narrow the row-positioning range for point lookups — but the two operate at different levels: the former reduces column-level reads, while the latter avoids entering a large-range scan. Once a query has already entered the scan phase, it may still read in large amounts of data at the block granularity that ends up being filtered out. When the filter conditions themselves cannot exclude many data blocks in advance, the system may still pay the cost of reading and decoding many blocks that will ultimately be filtered away.
If the problem stems from an oversized table, a missing suitable index, or a poorly designed partition key, then the focus of optimization usually does not lie at the Skip Index layer.
Skip Index is better suited to scenarios like this: the execution plan has already landed on a large-range scan within a partition (row store or column store both work), and the real bottleneck genuinely comes from “reading a lot but keeping little.” In a cluster where transactions and analytics share resources, this kind of waste does not always first show up as one particular SQL statement being slow. More often, analytical tasks continuously occupy shared resources and, during business peaks, affect short transactions and background compaction on the same node.
Where Skip Index Fits in the Scan Optimization Chain
Wasted scanning happens at the data-block granularity. To judge whether Skip Index applies, you first need to be clear about which layer it and other scan-related capabilities each operate on.
Skip Index and B+ tree indexes do not solve the same kind of problem. B+ tree indexes are good for quickly positioning a small number of target rows; Skip Index targets a different scenario: the query has already entered the scan phase, but the system still wants to read as few of the ultimately non-matching data blocks as possible. It does not turn a scan into a point lookup, but it can reduce unnecessary disk reads during the scan.
The division of responsibilities among several common capabilities is as follows: partition pruning narrows the range of partitions to be scanned; column store decides which columns to read and which columnar scan path to use; B+ tree indexes handle row positioning for point lookups and high-selectivity access; and only if a large number of data blocks still need to be read within the scan range after the above steps does Skip Index become applicable. When the preceding optimizations have not resolved the corresponding problem, or when filters cannot be pushed down and the query is mostly point lookups, enabling Skip Index alone usually brings little noticeable improvement. Only when the execution plan has already landed on a large-range scan (row store or column store) and the bottleneck manifests as a high scan-read volume with a low post-filter retention ratio does it enter the scope of Skip Index evaluation.
In scenarios such as hybrid row-column storage or a row-store primary table paired with columnar indexes, having Skip Index configured on a table does not mean every query will use it. The filter conditions must be pushable down to the storage layer, and the query must ultimately adopt a scan method that can leverage block-level statistics. The baseline side of a column-store table usually already carries default MIN_MAX statistics, which may not necessarily be shown in DESC when created by default; a row-store table requires explicitly declaring SKIP_INDEX(...) on the column. This brings additional storage and compaction maintenance overhead, but in low-selectivity filter and aggregate-pushdown scenarios it can likewise significantly reduce wasted I/O.
Why Skip Index Can Reduce Scanning
Skip Index works by relying on the block-level statistics maintained during the write and compaction processes. For a specified column, the system records statistics such as the minimum value, maximum value, and null count in the intermediate index layer of the baseline data.
How These Statistics Are Used During Query Execution
After a filter condition is pushed down to the storage layer, the engine — before prefetching a data micro-block — first uses these statistics to judge whether the block still needs to be read. If, judging by its value range, a data block cannot possibly satisfy the condition, it can directly skip the disk read and decoding; the remaining data blocks are still read normally and confirmed again by the subsequent filter, so no target rows are missed.
In certain aggregate queries, the system can also leverage the additionally maintained aggregate values to further reduce access to data micro-blocks.
Which Scenarios Show the Effect More Readily
Column-store tables: The scan data volume is usually larger, so the room for skipping is often greater as well; the baseline side typically already carries MIN_MAX statistics by default.
Row-store tables: In large-range scans with filters, the index layer can first judge whether a data block might contain the target rows before deciding whether to read the data micro-block. After explicitly declaring SKIP_INDEX(...) on a few key columns, wasted I/O can likewise be noticeably reduced in low-selectivity filter and aggregate-pushdown scenarios.
Aggregate queries: SUM mainly provides additional optimization room in aggregation scenarios. If the business genuinely depends on SUM aggregate pushdown, you can create it explicitly and then decide whether to keep it based on actual conditions. Column-store tables do not create SUM-type statistics by default, partly because it adds to the burden of direct load and compaction tasks.
The core role of Skip Index is to read fewer of the ultimately non-matching data blocks during the scan phase, thereby reducing wasted scanning and freeing up I/O and decoding resources. In HTAP scenarios, these statistics are built primarily on the baseline data; the portion that has already been compacted can participate in query acceleration first.
How to Evaluate Whether It Is Worth Enabling in HTAP
In a pure analytical environment, the main role of Skip Index is to reduce resource consumption during scanning; in HTAP scenarios, you have to look one level deeper: are analytical queries still heavily consuming the I/O and CPU that should be left for short transactions and background compaction? Every data block not read means giving back a portion of I/O and decoding capacity to the shared resource pool. Therefore, in HTAP scenarios, whether to enable Skip Index should not be judged solely by whether the analytical query itself gets faster — you also need to consider whether the resources it saves are enough to offset the extra storage overhead, the compaction maintenance overhead, and the impact on transactions and background tasks.
First, Check Whether the Query Path Matches
Skip Index is worth considering only when the following conditions are met:
The execution plan is dominated by large-range scans, in either column store or row store; partition pruning has already converged the scan range to a reasonable interval, yet large amounts of data still need to be read within the partition.
The filter fields are stable over the long term, and the filter conditions can be pushed down to the storage layer.
The data distribution allows a substantial portion of data blocks to be excluded in advance — for example, reporting-style queries filtered by low-selectivity conditions such as time or status.
For column-store tables, first confirm whether the default MIN_MAX covers the commonly used filter columns; for row-store tables, it is more appropriate to explicitly declare SKIP_INDEX only on a few key columns, confirm that there is genuinely significant room for skipping, and then consider whether SUM is needed—avoiding redundant configuration across too many columns.
If the query is mostly point lookups, or the filter conditions cover almost all data blocks, you should generally prioritize investigating partition design, SQL phrasing, or B+ tree indexes first. If these problems that should be solved first have not yet been resolved, Skip Index usually cannot make up for that layer.
Which Scenarios Are Usually Suitable for Enabling It
Skip Index is better suited to analytical queries that have already entered the scan phase but where a substantial portion of data blocks still does not need to be read — not to every query with a WHERE clause. Common situations include:
The execution plan has already landed on a large-range scan within a partition, with the bottleneck manifesting as a high scan-read volume and a low post-filter data retention ratio.
The filter conditions themselves cannot precisely position a small number of rows, but they can let a portion of data blocks be excluded in advance via block-level statistics.
The filter fields are stable and appear over the long term in analytical workloads such as reporting and batch jobs.
A few key columns have already been identified on a row-store table, and these columns have significant room for skipping.
Which Scenarios Usually Show Limited Effect
In the following scenarios, Skip Index often struggles to bring noticeable improvement:
The table itself is small, or analytical queries are rare and the workload is mostly point lookups.
The filter conditions require scanning almost all data blocks. In this case, even with Skip Index, most blocks cannot be excluded in advance, so the disk reads and decoding that can be saved are limited.
The filter fields used by queries change frequently, making it hard to consistently hit the columns maintained by Skip Index.
The data is dominated by random updates. When data within a certain range is frequently updated at random, the reference value of the statistics declines and fewer data blocks can be skipped, yet the maintenance overhead does not disappear accordingly.
When preceding optimizations such as partition pruning, B+ tree indexes, or scan paths have not been done well, enabling Skip Index alone often yields little.
Applicability Boundaries and Additional Costs
When deciding whether to enable it, you also need to factor in the additional costs and the pace at which it takes effect:
The system must pay extra storage for these statistics, and they also need ongoing maintenance during compaction.
Skip Index does not take effect across the entire table immediately upon configuration. After an attribute change, it requires progressive compaction to gradually rewrite the intermediate index layer and build statistics on the baseline side step by step; during this process, the portion that has already taken effect can participate in queries first. When judging whether it is effective, you should not rely solely on results from the launch day or before compaction is complete.
It cannot replace partition pruning, B+ tree indexes, or appropriate scan path selection.
Before adding Skip Index to a column, first confirm that it genuinely corresponds to a stable query scenario; an oversized Skip Index on a single table can also raise errors.
If the business strongly depends on the latest data that has not yet been compacted into the baseline, it is not appropriate to judge whether Skip Index is effective based on this data alone.
How to Tell Whether It Is Working After Enabling It
A common approach is to first identify stable workloads, configure Skip Index on only a few columns, wait for compaction to progress for a while, and then decide whether to keep it based on query performance and system metrics. Focus on the following points:
After Skip Index is specified for a column, statistics are gradually built during compaction; you should compare the scan volume and response time of typical analytical SQL after compaction has progressed.
The Skip Index-related storage statistics in execution plan monitoring — for example, the number of data blocks skipped in advance by Skip Index — can be used to confirm whether skipping actually happens.
During business peaks, observe whether short transactions are still noticeably dragged down by analytical queries.
Therefore, judging whether Skip Index is effective is better done after statistics have gradually been built on the baseline side, combined with actual query performance.
Don’t Look Only at the Response Time of a Single SQL
In HTAP scenarios, a drop in the response time of a single SQL statement does not mean the tenant as a whole is more stable. When deciding whether Skip Index is worth keeping, you also need to consider the compaction burden, storage footprint, and peak-time resource contention together:
If analytical queries improve slightly but compaction slows down, or online transaction latency shows noticeable fluctuations, it may not be worth it overall.
If a single SQL statement gets faster but the cluster still shows noticeable fluctuations during peaks, it often means the assessment still lacks observation of resource usage and contention.
Beyond the response time of a single SQL statement, you usually also need to look at I/O, CPU, and background task performance at the same time.
Summary
Under the LSM-Tree architecture, OceanBase unifies row store, column store, and transactional capabilities: column store mainly answers “which columns to read,” while Skip Index goes a step further on both row and column stores to answer “which data blocks can be skipped.” Discussing it separately in HTAP scenarios is warranted because the extra cost of wasted scanning spills over to short transactions and compaction tasks; and given that the execution plan is already reasonable, further reducing wasted reads in analytical queries at the storage layer remains an optimization worth considering.
Skip Index is not a universal acceleration capability, but within clearly defined applicability boundaries, it remains an option in mixed-workload governance — one that can be rolled back through configuration and is genuinely worth carefully evaluating before adoption.
Top comments (0)