179 lines
9.1 KiB
Markdown
179 lines
9.1 KiB
Markdown
# Informe de optimizacion query_02
|
|
|
|
Generado: 2026-07-12T22:54:28.283Z
|
|
|
|
## 1. Descripcion de query_02
|
|
|
|
`query_02` corresponde a la primera pagina general del catalogo INVIMA:
|
|
|
|
```sql
|
|
SELECT
|
|
source_dataset_key,
|
|
source_dataset_id,
|
|
source_dataset_name,
|
|
categoria_linea,
|
|
categoria_canonica,
|
|
categoria_slug,
|
|
expediente,
|
|
registro_sanitario,
|
|
producto,
|
|
titular,
|
|
estado_registro,
|
|
fecha_expedicion,
|
|
fecha_vencimiento,
|
|
modalidad,
|
|
grupo,
|
|
marca,
|
|
principio_activo,
|
|
forma_farmaceutica,
|
|
presentacion_comercial,
|
|
atc,
|
|
rol_nombre,
|
|
rol_tipo,
|
|
ciudad_titular,
|
|
pais_titular,
|
|
fabricante,
|
|
importador,
|
|
vigencia
|
|
FROM invima_api_catalog
|
|
ORDER BY
|
|
fecha_vencimiento DESC NULLS LAST,
|
|
producto ASC NULLS LAST,
|
|
source_dataset_key ASC,
|
|
source_uid ASC
|
|
LIMIT 25 OFFSET 0;
|
|
```
|
|
|
|
El endpoint real asociado es `GET /api/invima/catalogo?limit=25&offset=0`. En Angular se invoca desde `InvimaService.searchCatalog()` y `CatalogPageComponent.loadData()`. En Express pasa por `backend/src/routes/invima.routes.js` y termina en `getInvimaCatalog()` / `runCatalogQuery()` dentro de `backend/invima/service.js`.
|
|
|
|
La solicitud real del endpoint ejecuta un `COUNT(*)` exacto y luego la consulta paginada. El benchmark historico de `query_02` mide la consulta paginada SQL.
|
|
|
|
## 2. Causa raiz
|
|
|
|
Antes de optimizar, PostgreSQL recorria aproximadamente 1.407.694 filas de una tabla de 1772 MB y aplicaba `top-N heapsort` para devolver solo 25 registros. La razon principal es que el orden requerido `fecha_vencimiento DESC NULLS LAST, producto ASC NULLS LAST` no tenia un indice compatible. El indice existente sobre `fecha_vencimiento` es ascendente y no satisface directamente `DESC NULLS LAST`.
|
|
|
|
Un indice compuesto completo con `producto` no es recomendable en esta base: se observaron valores de `producto` extremadamente largos, y btree puede superar el limite de tamano de tupla. Por eso la optimizacion usa un indice estrecho por fecha descendente y deja el orden secundario sobre `producto` para un conjunto reducido.
|
|
|
|
## 3. Plan anterior
|
|
|
|
- Nodos: Limit, Gather Merge, Sort, Seq Scan.
|
|
- Seq Scan: 1; Index Scan: 0; Incremental Sort: 0; Gather Merge: 1.
|
|
- Filas estimadas/real raiz: 25 / 25.
|
|
- Ancho estimado de fila: 596 bytes.
|
|
- Bloques hit/read: 63.428 / 844.108.
|
|
- Temporales read/write: 0 / 0.
|
|
- Metodo de ordenamiento: top-N heapsort 44Memory.
|
|
- Planeacion/ejecucion: 7,218 ms / 2.380,218 ms.
|
|
|
|
## 4. Cambios implementados
|
|
|
|
Se agrego una migracion reversible:
|
|
|
|
```sql
|
|
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_query02_catalog_vencimiento_desc_nullslast
|
|
ON invima_api_catalog (fecha_vencimiento DESC NULLS LAST);
|
|
ALTER TABLE invima_api_catalog ALTER COLUMN fecha_vencimiento SET STATISTICS 1000;
|
|
ALTER TABLE invima_api_catalog ALTER COLUMN producto SET STATISTICS 1000;
|
|
```
|
|
|
|
No se modificaron columnas de respuesta, filtros, universo consultado, conteo exacto ni registros regulatorios. Se agrego un desempate estable al `ORDER BY` con `source_dataset_key, source_uid`, que son la clave primaria, para evitar duplicados u omisiones entre paginas cuando hay empates en `fecha_vencimiento` y `producto`.
|
|
|
|
## 5. Indices relevantes
|
|
|
|
| Indice | Tamano | Valido | Listo | Definicion |
|
|
|---|---:|---|---|---|
|
|
| `idx_invima_api_catalog_producto_prefix` | 71 MB | true | true | `CREATE INDEX idx_invima_api_catalog_producto_prefix ON public.invima_api_catalog USING btree ("left"(producto, 512))` |
|
|
| `idx_invima_api_catalog_vencimiento` | 15 MB | true | true | `CREATE INDEX idx_invima_api_catalog_vencimiento ON public.invima_api_catalog USING btree (fecha_vencimiento)` |
|
|
| `idx_query02_catalog_vencimiento_desc_nullslast` | 9808 kB | true | true | `CREATE INDEX idx_query02_catalog_vencimiento_desc_nullslast ON public.invima_api_catalog USING btree (fecha_vencimiento DESC NULLS LAST)` |
|
|
| `invima_api_catalog_pk` | 131 MB | true | true | `CREATE UNIQUE INDEX invima_api_catalog_pk ON public.invima_api_catalog USING btree (source_dataset_key, source_uid)` |
|
|
|
|
## 6. Plan posterior
|
|
|
|
- Nodos: Limit, Incremental Sort, Index Scan.
|
|
- Seq Scan: 0; Index Scan: 1; Incremental Sort: 1; Gather Merge: 0.
|
|
- Filas estimadas/real raiz: 25 / 25.
|
|
- Ancho estimado de fila: 596 bytes.
|
|
- Bloques hit/read: 12 / 53.
|
|
- Temporales read/write: 0 / 0.
|
|
- Metodo de ordenamiento: N/D.
|
|
- Planeacion/ejecucion: 7,893 ms / 0,835 ms.
|
|
|
|
## 7. Validacion funcional
|
|
|
|
- Total antes/despues: 1.407.694 / 1.407.694.
|
|
- Resultado global: VALIDO.
|
|
- Offset 0: valido=true; faltantes=0; adicionales=0; cambio orden=false; cambio claves ORDER BY=false.
|
|
- Offset 25: valido=true; faltantes=0; adicionales=0; cambio orden=false; cambio claves ORDER BY=false.
|
|
- Offset 250.000: valido=true; faltantes=0; adicionales=0; cambio orden=false; cambio claves ORDER BY=false.
|
|
- Offset 1.000.000: valido=true; faltantes=0; adicionales=0; cambio orden=false; cambio claves ORDER BY=false.
|
|
|
|
La validacion before se ejecuto con el plan antiguo forzado mediante GUCs de sesion, y la validacion after con el nuevo indice habilitado. En ambos casos se uso la misma consulta determinista. La validacion exige que no cambien IDs, orden, claves `ORDER BY`, estructura publica ni valores publicos.
|
|
|
|
## 8. Resultados secuenciales
|
|
|
|
- Promedio: 1.076,2853 ms -> 0,6162 ms; mejora 1.075,6691 ms (99,9427 %).
|
|
- Mediana: 1.085,2339 ms -> 0,5799 ms.
|
|
- p95: 1.248,0925 ms -> 0,9834 ms; mejora 1.247,1091 ms (99,9212 %).
|
|
- p99: 1.270,9296 ms -> 1,0935 ms; mejora 1.269,8361 ms (99,914 %).
|
|
- Fallos: 0; tasa de fallos: 0 %.
|
|
- Throughput: 0,9352 ops/s -> 1.515,1515 ops/s.
|
|
|
|
## 9. Resultados concurrentes
|
|
|
|
- 1 usuario(s): promedio 1.078,747 ms -> 51,8153 ms; p95 1.400,5967 ms -> 152,1045 ms; throughput 0,4465 req/s -> 17,5193 req/s.
|
|
- 5 usuario(s): promedio 5.157,4488 ms -> 57,3871 ms; p95 11.830,5646 ms -> 147,1289 ms; throughput 0,8296 req/s -> 82,9876 req/s.
|
|
- 10 usuario(s): promedio 13.306,701 ms -> 70,3586 ms; p95 22.564,3625 ms -> 163,5365 ms; throughput 0,7737 req/s -> 131,2336 req/s.
|
|
- 25 usuario(s): promedio 21.160,2308 ms -> 143,9608 ms; p95 24.849,9474 ms -> 248,9395 ms; throughput 3,0272 req/s -> 158,2278 req/s.
|
|
- 50 usuario(s): promedio 26.465,4267 ms -> 303,4708 ms; p95 28.761,2915 ms -> 712,3709 ms; throughput 4,404 req/s -> 183,4862 req/s.
|
|
|
|
## 10. Criterios
|
|
|
|
- Promedio individual: 0,6162 ms frente a objetivo 300 ms => cumple.
|
|
- p95 individual: 0,9834 ms frente a objetivo 500 ms => cumple.
|
|
- p99 individual: 1,0935 ms frente a objetivo 750 ms => cumple.
|
|
- p95 con 5 usuarios: 147,1289 ms frente a objetivo 1.000 ms => cumple.
|
|
- p95 con 10 usuarios: 163,5365 ms frente a objetivo 2.000 ms => cumple.
|
|
- p95 con 25 usuarios: 248,9395 ms frente a objetivo 5.000 ms => cumple.
|
|
|
|
## 11. Paginacion, conteo y concurrencia
|
|
|
|
La consulta usa `LIMIT 25 OFFSET n`. Para `OFFSET 0`, el costo dominante era encontrar el orden inicial sin indice compatible. Las paginas profundas siguen pagando el costo natural de `OFFSET`; una siguiente iteracion compatible seria exponer paginacion keyset con cursor basado en `fecha_vencimiento`, `producto` y la clave primaria `source_dataset_key/source_uid`, manteniendo la ruta actual como compatibilidad.
|
|
|
|
El endpoint real conserva `COUNT(*)` exacto en cada solicitud. No se reemplazo por estimaciones ni cache como unica solucion. Si el conteo se convierte en cuello de botella, se recomienda medir y separar el total exacto en una ruta o tabla de estadisticas invalidada durante sincronizacion.
|
|
|
|
Perfil del servicio actualizado: `getInvimaCatalog(pool, { limit: 25, offset: 0 })` devolvio 25 filas, total exacto 1.407.694, tiempo logico 178,4872 ms, serializacion 0,3199 ms y respuesta de 20.456 bytes.
|
|
|
|
El pool actual usa `PGPOOL_MAX` desde entorno y PostgreSQL tiene `max_connections` estandar. En una maquina de 2 vCPU y 4 GB, aumentar conexiones sin reducir el costo de query empeora la contencion; la mejora principal provino del plan SQL.
|
|
|
|
## 12. Riesgos
|
|
|
|
- El nuevo indice aumenta espacio en disco y costo de mantenimiento durante inserciones/sincronizaciones.
|
|
- No cubrir `producto` evita un indice enorme, pero todavia requiere ordenar grupos con la misma fecha.
|
|
- El desempate por `source_dataset_key, source_uid` estabiliza la paginacion, pero cambia el orden interno previamente no garantizado de filas empatadas.
|
|
- `ANALYZE` automatico se omitio porque esta base local tiene bytes con codificacion invalida que hacen fallar el escaneo completo.
|
|
|
|
## 13. Redaccion academica
|
|
|
|
Antes de la optimizacion, la consulta general paginada presento una latencia promedio de 1.076,2853 ms y un p95 de 1.248,0925 ms. Despues de implementar el indice `idx_query02_catalog_vencimiento_desc_nullslast`, la latencia promedio se redujo a 0,6162 ms y el p95 a 0,9834 ms, equivalente a una mejora de 1.075,6691 ms (99,9427 %). Bajo 50 usuarios concurrentes, la latencia promedio paso de aproximadamente 26.465,4267 ms a 303,4708 ms.
|
|
|
|
## 14. Reversion y repeticion
|
|
|
|
Revertir:
|
|
|
|
```powershell
|
|
npm.cmd run benchmark:query02:rollback
|
|
```
|
|
|
|
Repetir:
|
|
|
|
```powershell
|
|
npm.cmd run benchmark:query02:validate:before
|
|
npm.cmd run benchmark:query02:explain:before
|
|
npm.cmd run benchmark:query02:optimize
|
|
npm.cmd run benchmark:query02:explain:after
|
|
npm.cmd run benchmark:query02:validate
|
|
npm.cmd run benchmark:query02:optimized
|
|
npm.cmd run benchmark:query02:concurrency
|
|
npm.cmd run benchmark:query02:report
|
|
```
|