Test filtered and partial index support

Parent Epic: #451

Dependencies

• Validate JSON scalar index creation and querying #454 (blocks this issue)

Overview

Test LanceDB's ability to create filtered/partial indexes scoped to specific node types. This validation directly addresses the question: "can we narrow the scan to a specific node type?" for index creation optimization.

Self-Contained Scope

• Test filtered index creation scoped to node type
• Compare performance: full table index vs filtered index
• Measure index size differences (filtered vs full)
• Document whether filtered indexes are supported in LanceDB
• Test query performance with filtered indexes
• Zero impact on Turso implementation

Implementation Approach

Phase 1: Filtered Index Research

• Review LanceDB documentation for filtered/partial index support
• Test index creation with WHERE clause or filter predicate
• Determine if feature exists or requires workaround
• Document current capabilities and limitations

Phase 2: Node Type Scoped Indexes

• Attempt to create index filtered by node type: WHERE type = 'task'
• Test index on task-specific properties (e.g., metadata.status only for tasks)
• Measure index creation time (full vs filtered)
• Compare index storage size (full vs filtered)

Phase 3: Query Performance Testing

• Test queries that benefit from filtered indexes
• Compare query time: filtered index vs full index
• Test queries outside filtered scope (should still work)
• Measure performance differences across dataset sizes

Phase 4: Optimization Recommendations

• Document whether filtered indexes are worthwhile
• Provide guidance on when to use filtered vs full indexes
• Alternative strategies if filtered indexes not supported
• Performance trade-off analysis

Acceptance Criteria

• Filtered/partial index support status documented
• Node type scoped index tested (if supported)
• Index creation time compared (filtered vs full)
• Index storage size compared (filtered vs full)
• Query performance measured with filtered indexes
• Recommendations documented for production use
• Alternative strategies documented if feature not available
• Test suite validates filtered index behavior

Technical Specifications

Filtered Index Patterns (if supported):

// Attempt 1: Filtered index on node type
table.create_scalar_index_with_filter(
    "metadata.status",
    "type = 'task'",  // Only index task nodes
    Default::default()
).await?;

// Attempt 2: Use LanceDB filter syntax
table.create_scalar_index(
    "metadata.status",
    ScalarIndexOptions {
        filter: Some("type = 'task'"),
        ..Default::default()
    }
).await?;

Performance Comparison:

pub struct FilteredIndexMetrics {
    index_type: String,  // "full" or "filtered"
    filter_condition: Option<String>,
    dataset_size: usize,
    indexed_rows: usize,  // How many rows actually indexed
    creation_time_ms: f64,
    index_size_bytes: u64,
    query_time_ms: f64,
}

async fn compare_index_strategies(
    table: &Table,
    property_path: &str,
) -> Result<Vec<FilteredIndexMetrics>> {
    let mut results = Vec::new();
    
    // Test 1: Full table index
    let full_metrics = create_and_measure_index(
        table,
        property_path,
        None,  // No filter
    ).await?;
    results.push(full_metrics);
    
    // Test 2: Filtered index (if supported)
    let filtered_metrics = create_and_measure_index(
        table,
        property_path,
        Some("type = 'task'"),
    ).await?;
    results.push(filtered_metrics);
    
    Ok(results)
}

Node Type Distribution Testing:

#[test]
async fn test_filtered_index_by_node_type() {
    // Create dataset: 40% tasks, 30% text, 30% other
    create_mixed_node_dataset(10000, vec![
        ("task", 0.4),
        ("text", 0.3),
        ("date", 0.3),
    ]).await?;
    
    // Attempt filtered index on task nodes only
    let result = table.create_scalar_index_with_filter(
        "metadata.status",
        "type = 'task'",
        Default::default()
    ).await;
    
    match result {
        Ok(_) => {
            // Measure benefits
            let metrics = measure_filtered_index_performance(&table).await?;
            println!("Filtered index metrics: {:?}", metrics);
        }
        Err(e) => {
            // Feature not supported
            println!("Filtered indexes not supported: {}", e);
            // Document workaround strategies
        }
    }
}

Workaround Strategy (if filtered indexes not supported):

// Alternative: Separate tables per node type
pub struct NodeTypeSegmentedStorage {
    task_table: Table,
    text_table: Table,
    date_table: Table,
}

impl NodeTypeSegmentedStorage {
    pub async fn create_index_on_tasks(&self, property: &str) -> Result<()> {
        // Only indexes task-specific table (natural filtering)
        self.task_table.create_scalar_index(property, Default::default()).await
    }
}

Resources & References

LanceDB Documentation:

• Scalar index API reference
• Filtered index capabilities (if available)

Architecture Context:

• /Users/malibio/Desktop/nodespace-lancedb-vs-tursodb.md

Related Issues:

• LanceDB Parallel Implementation for Database Evaluation #451 (parent)
• Validate JSON scalar index creation and querying #454 (blocks this issue)

Definition of Done

• Filtered index support status clearly documented
• Node type scoped indexing tested and validated
• Performance comparison data collected and analyzed
• Recommendations provided for production implementation
• Workaround strategies documented if feature unavailable
• Test suite covers all filtered index scenarios
• Code passes cargo fmt and cargo clippy
• Documentation answers "can we narrow scan to node type?" question

🤖 Generated with Claude Code

Co-Authored-By: Claude noreply@anthropic.com