Análisis de Consultas - Tabla RemoteAgents¶

Fecha: 2026-02-01 Problema: Index hit rate del 0.4% con 417,760 sequential scans Objetivo: Documentar patrones de acceso para optimizar índices

Resumen Ejecutivo¶

Métrica	Valor
Sequential Scans	417,760
Index Hit Rate	0.4%
Updates	106,512
Índices existentes	Status, OrganizationId, ApiKeyHash

Problema principal: Los índices existentes no están siendo utilizados eficientemente debido a patrones de consulta que combinan múltiples columnas o requieren ordenamiento.

1. Patrones de Consulta por Frecuencia¶

1.1 Consultas de Alta Frecuencia (HOT PATH)¶

Operación	Consulta	Frecuencia	Índice Utilizado
Heartbeat	`FindAsync(agentId)`	Cada 30s por agente	PK (Id)
Autenticación	`WHERE ApiKeyHash = X AND IsEnabled = true`	Cada conexión	Parcial: `IX_RemoteAgents_ApiKey_Enabled`
Status Update	`FindAsync(agentId)` + UPDATE	Cada comando	PK (Id)
Disconnect	`FindAsync(agentId)` + UPDATE Status	Desconexiones	PK (Id)

1.2 Consultas de Media Frecuencia¶

Operación	Consulta	Índice Requerido
Listar agentes online	`WHERE Status IN (Online, Busy)`	`IX_RemoteAgents_Status`
Listar por organización	`WHERE OrganizationId = X ORDER BY LastConnectedAt DESC`	Compuesto: `(OrganizationId, LastConnectedAt DESC)`
Agentes activos	`WHERE OrganizationId = X AND (Status = Online OR Status = Busy)`	Compuesto: `(OrganizationId, Status)`

1.3 Consultas de Baja Frecuencia¶

Operación	Consulta	Notas
Detalle agente	`Include(Organization, Owner)`	JOIN navigation properties
Crear/Eliminar	Insert/Delete	Sin optimización especial

2. Consultas SQL Equivalentes¶

2.1 Autenticación de Agente (CRÍTICO)¶

-- Ubicación: RemoteAgentHub.Register()
-- Frecuencia: Cada conexión SignalR
SELECT * FROM "RemoteAgents"
WHERE "ApiKeyHash" = @hash AND "IsEnabled" = true
LIMIT 1;

Índice recomendado:

CREATE INDEX CONCURRENTLY IX_RemoteAgents_ApiKey_Enabled
ON "RemoteAgents" ("ApiKeyHash")
WHERE "IsEnabled" = true;

2.2 Heartbeat y Actualización de Estado¶

-- Ubicación: RemoteAgentHub.Heartbeat()
-- Frecuencia: Cada 30 segundos por agente conectado
SELECT * FROM "RemoteAgents" WHERE "Id" = @agentId;

UPDATE "RemoteAgents"
SET "LastHeartbeat" = @now, "Status" = @status, "UpdatedAt" = @now
WHERE "Id" = @agentId;

Optimización: Usa PK, eficiente. Sin cambios necesarios.

2.3 Listar Agentes por Organización¶

-- Ubicación: RemoteAgentService.GetAgentsAsync()
-- Frecuencia: Cada carga de UI de admin
SELECT "Id", "Name", "Status", "SystemInfo", "AgentVersion",
       "LastHeartbeat", "LastConnectedAt", "IsEnabled", "TotalCommandsExecuted"
FROM "RemoteAgents"
WHERE "OrganizationId" = @orgId
ORDER BY "LastConnectedAt" DESC;

Índice recomendado:

CREATE INDEX CONCURRENTLY IX_RemoteAgents_Org_LastConnected
ON "RemoteAgents" ("OrganizationId", "LastConnectedAt" DESC)
INCLUDE ("Name", "Status", "SystemInfo", "AgentVersion",
         "LastHeartbeat", "IsEnabled", "TotalCommandsExecuted");

2.4 Obtener Agentes Online/Busy¶

-- Ubicación: RemoteAgentService.GetOnlineAgentsAsync()
-- Frecuencia: Frecuente en workspace
SELECT "Id", "Name", "Status", "SystemInfo", "AgentVersion",
       "LastHeartbeat", "LastConnectedAt", "IsEnabled", "TotalCommandsExecuted"
FROM "RemoteAgents"
WHERE ("Status" = 2 OR "Status" = 3)  -- Online=2, Busy=3
  AND "OrganizationId" = @orgId;

Índice recomendado (parcial):

CREATE INDEX CONCURRENTLY IX_RemoteAgents_Active
ON "RemoteAgents" ("OrganizationId", "LastConnectedAt" DESC)
WHERE "Status" IN (2, 3);

2.5 Comandos Pendientes al Desconectar¶

-- Ubicación: RemoteAgentHub.OnDisconnectedAsync()
-- Frecuencia: Cada desconexión
SELECT "Id" FROM "RemoteAgentCommands"
WHERE "RemoteAgentId" = @agentId
  AND "Status" IN (0, 1, 2);  -- Pending, Sent, Executing

Nota: Esta consulta es sobre RemoteAgentCommands, no RemoteAgents.

3. Análisis de Índices Existentes¶

Índices Actuales (de migración encontrada)¶

Índice	Columnas	Tipo	Estado
`IX_RemoteAgents_Status`	Status	Simple	Subutilizado
`IX_RemoteAgents_OrganizationId`	OrganizationId	Simple	Subutilizado
`IX_RemoteAgents_ApiKeyHash`	ApiKeyHash	Unique	En uso
`IX_RemoteAgents_Org_Status`	(OrganizationId, Status)	Compuesto	Parcialmente usado

Por qué los índices simples no se usan¶

Baja selectividad: Con pocos valores de Status (5 estados), PostgreSQL prefiere seq scan
Combinación de filtros: Las consultas combinan OrganizationId + Status + ORDER BY
Falta de columnas INCLUDE: Se requieren columnas adicionales causando table lookups

4. Ubicación de Consultas en Código¶

Archivo	Método	Línea	Tipo de Consulta
`RemoteAgentHub.cs`	`Register()`	~152	ApiKeyHash lookup
`RemoteAgentHub.cs`	`Heartbeat()`	~229	FindAsync + Update
`RemoteAgentHub.cs`	`CommandResult()`	~367-421	FindAsync + Update metrics
`RemoteAgentHub.cs`	`OnDisconnectedAsync()`	~544	FindAsync + Status update
`RemoteAgentService.cs`	`GetAgentsAsync()`	-	Filter + OrderBy
`RemoteAgentService.cs`	`GetOnlineAgentsAsync()`	-	Status filter
`ProjectAgentPathsController.cs`	`GetAvailableAgents()`	-	Project agents list

5. Tabla de Impacto¶

Consulta	Scans Estimados/día	Impacto	Prioridad
Heartbeat (FindAsync)	~288,000 (100 agentes × 2880 heartbeats)	Bajo (usa PK)	-
Autenticación ApiKeyHash	~500-1000	Alto (conexiones)	P1
Listar por Org + OrderBy	~10,000	Alto (UI loads)	P1
Agentes Online	~5,000	Medio	P2
Status updates	~50,000	Bajo (usa PK)	-

6. Recomendaciones de Optimización¶

P1 - Alta Prioridad¶

Índice compuesto para listado:

CREATE INDEX CONCURRENTLY IX_RemoteAgents_Org_LastConnected_Covering
ON "RemoteAgents" ("OrganizationId", "LastConnectedAt" DESC)
INCLUDE ("Name", "Status", "IsEnabled");

Índice parcial para autenticación (verificar si existe):

CREATE UNIQUE INDEX CONCURRENTLY IX_RemoteAgents_ApiKey_Enabled
ON "RemoteAgents" ("ApiKeyHash")
WHERE "IsEnabled" = true;

P2 - Media Prioridad¶

Índice parcial para agentes activos:

CREATE INDEX CONCURRENTLY IX_RemoteAgents_Active_Org
ON "RemoteAgents" ("OrganizationId", "Name")
WHERE "Status" IN (2, 3);

P3 - Considerar Eliminar¶

Índice simple de Status: Si hay índice compuesto (Org, Status), el simple es redundante

7. Estadísticas de la Tabla¶

Para obtener estadísticas actuales, ejecutar:

-- Estadísticas de uso de índices
SELECT
    relname AS table,
    indexrelname AS index,
    idx_scan AS index_scans,
    idx_tup_read AS tuples_read,
    idx_tup_fetch AS tuples_fetched
FROM pg_stat_user_indexes
WHERE relname = 'RemoteAgents'
ORDER BY idx_scan DESC;

-- Estadísticas de tabla
SELECT
    relname,
    seq_scan,
    seq_tup_read,
    idx_scan,
    idx_tup_fetch,
    n_live_tup,
    n_dead_tup,
    last_vacuum,
    last_analyze
FROM pg_stat_user_tables
WHERE relname = 'RemoteAgents';

8. Próximos Pasos¶

[ ] Ejecutar EXPLAIN ANALYZE en producción para confirmar planes
[ ] Crear índices compuestos recomendados
[ ] Ejecutar ANALYZE después de crear índices
[ ] Monitorear pg_stat_user_indexes después de 24-48h
[ ] Eliminar índices redundantes si se confirma no uso