perf-monitor

Performance Monitoring Command

Monitor and analyze the performance of vector search operations, index efficiency, and memory lifecycle metrics.

Usage

This command provides comprehensive performance monitoring for:

• Vector search query performance
• Index usage and efficiency
• Memory lifecycle statistics
• Database query patterns
• Resource utilization

Available Monitoring Tasks

1. Vector Search Performance

# Check current pgvector index statistics
psql $DATABASE_URL -c "
  SELECT 
    schemaname,
    tablename,
    indexname,
    idx_scan as index_scans,
    idx_tup_read as tuples_read,
    idx_tup_fetch as tuples_fetched,
    pg_size_pretty(pg_relation_size(indexrelid)) as index_size
  FROM pg_stat_user_indexes
  WHERE indexname LIKE '%vector%' OR indexname LIKE '%embedding%'
  ORDER BY idx_scan DESC;
"

# Analyze query performance for vector operations
psql $DATABASE_URL -c "
  SELECT 
    substring(query, 1, 50) as query_preview,
    calls,
    mean_exec_time as avg_ms,
    min_exec_time as min_ms,
    max_exec_time as max_ms,
    total_exec_time as total_ms,
    rows
  FROM pg_stat_statements
  WHERE query LIKE '%embedding%' OR query LIKE '%vector%'
  ORDER BY mean_exec_time DESC
  LIMIT 20;
"

2. Index Efficiency Analysis

# Check IVFFlat index clustering quality
psql $DATABASE_URL -c "
  SELECT 
    indexname,
    lists,
    pages,
    tuples,
    ROUND(tuples::numeric / NULLIF(lists, 0), 2) as avg_vectors_per_list,
    CASE 
      WHEN tuples::numeric / NULLIF(lists, 0) > 10000 THEN 'Rebalance recommended'
      WHEN tuples::numeric / NULLIF(lists, 0) < 100 THEN 'Over-partitioned'
      ELSE 'Optimal'
    END as status
  FROM (
    SELECT 
      'memories_embedding_ivfflat_idx'::regclass as indexname,
      (SELECT current_setting('ivfflat.lists')::int) as lists,
      relpages as pages,
      reltuples as tuples
    FROM pg_class 
    WHERE oid = 'memories_embedding_ivfflat_idx'::regclass
  ) index_stats;
"

# Check HNSW index parameters
psql $DATABASE_URL -c "
  SELECT 
    indexname,
    m,
    ef_construction,
    ef_search,
    CASE 
      WHEN ef_search < 100 THEN 'Low recall configuration'
      WHEN ef_search > 500 THEN 'High cost configuration'
      ELSE 'Balanced configuration'
    END as configuration_assessment
  FROM (
    SELECT 
      'memories_embedding_hnsw_idx' as indexname,
      current_setting('hnsw.m')::int as m,
      current_setting('hnsw.ef_construction')::int as ef_construction,
      current_setting('hnsw.ef_search')::int as ef_search
  ) hnsw_config;
"