Add database indexes for filter/sort columns and relation tables

Problem/Motivation

The module creates PostgreSQL tables for vector storage but does not create indexes on commonly-queried columns. This causes sequential scans on filter, sort, and facet
operations, resulting in slow query performance at scale.

For example, with ~43,000 rows in search_index:

• Queries filtering by nid, langcode, or status use sequential scans
• Facet counting on relation tables (search_index__field_*) is slow due to missing indexes on chunk_id and value columns
• Sorting by created requires full table scan

Steps to reproduce

1. Index content into the Postgres VDB provider
2. Run EXPLAIN ANALYZE on a query with filters
3. Observe sequential scan instead of index scan

Proposed resolution

Add indexes during table creation using the Drupal Schema API.

In hook_schema() or table creation:

  // Main table indexes                                                                                                                                                            
  $schema['search_index'] = [                                                                                                                                                      
    'fields' => [                                                                                                                                                                  
      // ... existing field definitions                                                                                                                                            
    ],                                                                                                                                                                             
    'indexes' => [                                                                                                                                                                 
      'nid' => ['nid'],                                                                                                                                                            
      'created' => ['created'],                                                                                                                                                    
      'langcode' => ['langcode'],                                                                                                                                                  
      'status' => ['status'],                                                                                                                                                      
      'drupal_entity_id' => ['drupal_entity_id'],                                                                                                                                  
    ],                                                                                                                                                                             
  ];                                                                                                                                                                               
                                                                                                                                                                                   
  // Relation table indexes (for each multi-value field table)                                                                                                                     
  $schema['search_index__field_example'] = [                                                                                                                                       
    'fields' => [                                                                                                                                                                  
      // ... existing field definitions                                                                                                                                            
    ],                                                                                                                                                                             
    'indexes' => [                                                                                                                                                                 
      'chunk_id' => ['chunk_id'],                                                                                                                                                  
      'value' => ['value'],                                                                                                                                                        
    ],                                                                                                                                                                             
  ];                                                                                                                                                                               
  

For existing installations, add via update hook:

  /**                                                                                                                                                                              
   * Add performance indexes to search_index tables.                                                                                                                               
   */                                                                                                                                                                              
  function ai_vdb_provider_postgres_update_10001() {                                                                                                                               
    $schema = \Drupal::database()->schema();                                                                                                                                       
                                                                                                                                                                                   
    // Main table indexes                                                                                                                                                          
    $indexes = [                                                                                                                                                                   
      'nid' => ['nid'],                                                                                                                                                            
      'created' => ['created'],                                                                                                                                                    
      'langcode' => ['langcode'],                                                                                                                                                  
      'status' => ['status'],                                                                                                                                                      
      'drupal_entity_id' => ['drupal_entity_id'],                                                                                                                                  
    ];                                                                                                                                                                             
                                                                                                                                                                                   
    foreach ($indexes as $name => $columns) {                                                                                                                                      
      if (!$schema->indexExists('search_index', $name)) {                                                                                                                          
        $schema->addIndex('search_index', $name, $columns, []);                                                                                                                    
      }                                                                                                                                                                            
    }                                                                                                                                                                              
                                                                                                                                                                                   
    // Relation table indexes                                                                                                                                                      
    $relation_tables = $schema->findTables('search_index__%');                                                                                                                     
    foreach ($relation_tables as $table) {                                                                                                                                         
      if (!$schema->indexExists($table, 'chunk_id')) {                                                                                                                             
        $schema->addIndex($table, 'chunk_id', ['chunk_id'], []);                                                                                                                   
      }                                                                                                                                                                            
      if (!$schema->indexExists($table, 'value')) {                                                                                                                                
        $schema->addIndex($table, 'value', ['value'], []);                                                                                                                         
      }                                                                                                                                                                            
    }                                                                                                                                                                              
  }                                                                                                                                                                                
  

Remaining tasks

• Identify where table creation occurs in the module
• Add index definitions to schema for new installations
• Add update hook for existing installations
• Consider adding indexes dynamically when relation tables are created for new multi-value fields

User interface changes

None.

API changes

None.

Data model changes

Adds database indexes to improve query performance. No changes to table structure or field definitions.