Support size-tiered scalar index optimization

[majin1102]

Summary

Extend ALTER TABLE ... OPTIMIZE INDEX from #743 with size-tiered merge planning for scalar index segments.

ALTER TABLE table_name OPTIMIZE INDEX index_name
WITH (
  target_rows_per_segment = 1000000,
  target_bytes_per_segment = 1073741824,
  merge_factor = 8
);

At least one target must be provided. Both targets are soft goals rather than strict limits.

Parameters

Parameter	Meaning
target_rows_per_segment	Desired number of indexed rows per output segment
target_bytes_per_segment	Desired physical size of an output segment
merge_factor	Number of similarly sized segments required to trigger a merge, and the maximum merge fan-in

When both targets are provided, the dimension closest to its target controls segment sizing.

merge_factor is a scheduling parameter, not a direct mapping to Lance Core's numIndicesToMerge.

Planning

Planning runs against a single dataset snapshot:

1. Append a new index segment for uncovered fragments, if any.
2. Read physical segments from describeIndices().
3. Obtain each segment's:
   • byte size from Index#getSizeBytes();
   • row count by summing the current rows of its covered fragments.
4. Group similarly sized segments into internal size tiers.
5. When a tier contains at least merge_factor segments, select a merge group of up to merge_factor segments whose combined size is closest to the configured targets.
6. Leave a fully covered index unchanged when no merge group is eligible.

Tier width is an implementation detail and is not exposed as a SQL parameter.

Execution

Size-tiered planning must select exact physical segments. It must not translate a group into numIndicesToMerge, because that option only selects trailing segments.

For each selected group, Spark uses the existing Lance APIs:

mergeExistingIndexSegments(group)
commitExistingIndexSegments(indexName, column, mergedSegments)

Multiple merged outputs should be committed atomically. Unselected segments remain unchanged.

Appending uncovered fragments may be a separate commit. If a later merge fails, the index remains correct and the next optimize operation can retry the merge.

Initial scope

The initial implementation covers mergeable scalar indexes such as BTree, ZoneMap, and FTS.

Vector indexes are excluded from the initial size-tiered policy. Physical vector-segment consolidation does not replace IVF partition rebalance, and independently rebalanced segments may have different model metadata. Existing vector optimizeIndices and retrain behavior remains delegated to Lance Core.

Distributed merge execution is a follow-up. The SQL contract and planning policy must not depend on whether merge tasks run on the driver or executors.

Dependencies

• Depends on Support OPTIMIZE INDEX SQL #743.
• Follows the size-tiered direction in Lance Vector Index: Multi-Segment Final State lance#6189.
• Requires per-segment Java size metadata from feat(java): expose index segment sizes lance#8355.
• Uses the existing mergeExistingIndexSegments and commitExistingIndexSegments APIs.

Acceptance criteria

• Supports row-based, byte-based, and combined targets.
• Selects exact, similarly sized physical segments.
• Does not merge unrelated large and small segments.
• Does not commit a new version when no append or merge is needed.
• Preserves index coverage after every commit.
• Includes validation, integration tests, and Spark SQL documentation.