PostgreSQL
Funciones, operadores y sargabilidad
Funciones y operadores SQL con foco en tipos, resolución de overloads y sargabilidad para conservar el uso eficiente de índices.
- Última actualización
- Actualizada
- Nivel
- Aplicación
PostgreSQL
Funciones y operadores SQL con foco en tipos, resolución de overloads y sargabilidad para conservar el uso eficiente de índices.
Una expresión SQL no es solo sintaxis: determina tipos, selectividad y qué estructuras de acceso puede utilizar el planner. Una condición correcta puede seguir siendo cara si obliga a transformar cada fila antes de compararla.
PostgreSQL incluye funciones y operadores para texto, números, fechas, arrays, ranges, JSONB, red, geometría y extensiones. El objetivo no es memorizarlos todos, sino entender tres preguntas:
¿Qué semántica tiene la expresión?
¿Qué tipo produce?
¿Puede un índice responderla eficientemente?La sargabilidad describe si una condición puede aprovechar una estructura de búsqueda para delimitar candidatos, en lugar de evaluar una transformación sobre todas las filas.
Supón un índice B-tree sobre created_at:
CREATE INDEX orders_created_at_idx ON orders(created_at);Consulta sargable:
WHERE created_at >= $1
AND created_at < $2Consulta potencialmente no sargable:
WHERE date(created_at) = $1La segunda expresa el resultado correcto, pero un índice normal sobre created_at no contiene date(created_at). PostgreSQL puede terminar transformando muchos valores o utilizar un expression index específico.
predicado directo sobre clave indexada
→ el índice delimita rango
→ se visitan candidatos
función sobre cada valor
→ calcular expresión para muchas filas
→ comparar resultadoEl planner puede aplicar optimizaciones, pero no debes asumir que reescribirá cualquier expresión equivalente.
Ejemplos:
lower(name)
round(total_amount, 2)
date_trunc('month', created_at)
jsonb_extract_path_text(metadata, 'source')
array_length(tags, 1)Cada función tiene:
Consulta la referencia oficial para la versión desplegada.
Los operadores también son funciones resueltas por tipo y operator class:
price >= 100
metadata @> '{"source":"mobile"}'::jsonb
tags && ARRAY['featured']
reserved_during && $1::tstzrangeUn índice solo puede ayudar si su access method y operator class soportan el operador.
PostgreSQL clasifica funciones:
Mismo resultado para los mismos argumentos en cualquier contexto relevante.
Puede utilizarse en expression indexes y generated columns cuando cumple restricciones.
No cambia dentro de una statement, pero puede depender de configuración o datos.
now() es estable dentro de la transacción/statement según función temporal utilizada.
Puede cambiar en cada llamada o tener efectos. random() es un ejemplo.
La clasificación permite al planner mover, plegar o reutilizar expresiones.
Marcar como IMMUTABLE una función que depende de timezone, tabla o configuración puede almacenar valores de índice que después no coinciden con la realidad.
PostgreSQL confía en la declaración del autor. Una clasificación incorrecta produce resultados incorrectos, no solo menor rendimiento.
Objetivo: órdenes de un día local.
Patrón frágil:
WHERE created_at::date = DATE '2026-07-24'Patrón de rango:
WHERE created_at >= TIMESTAMPTZ '2026-07-24 00:00:00-05'
AND created_at < TIMESTAMPTZ '2026-07-25 00:00:00-05'En aplicación, calcula límites con la zona de negocio correctamente. No uses 23:59:59.999 porque la precisión puede superar ese valor y los cambios de zona alteran duración del día.
Si el workload consulta siempre una expresión:
CREATE INDEX customers_lower_email_idx
ON customers (lower(email));Consulta:
WHERE lower(email) = lower($1)El planner puede utilizar el índice cuando la expresión coincide semánticamente.
Trade-offs:
CREATE UNIQUE INDEX users_active_email_uidx
ON users (lower(email))
WHERE deleted_at IS NULL;Esto impide duplicados activos según lower.
Limitaciones:
lower no equivale a una política completa de identidad internacional.citext puede ser alternativa, con sus propios trade-offs.Un predicado frecuente puede formar parte del índice:
CREATE INDEX orders_pending_created_idx
ON orders(created_at, id)
WHERE status = 'pending';La query debe permitir al planner demostrar que cumple el predicado:
WHERE status = 'pending'
AND created_at < $1Parámetros y expresiones genéricas pueden impedir esa demostración en algunos casos.
Un B-tree normal sobre texto sirve para igualdad y orden según collation, pero patrones como prefijo LIKE pueden depender de collation y text_pattern_ops.
Ejemplo:
CREATE INDEX products_name_pattern_idx
ON products (name text_pattern_ops);No lo añadas sin comprobar:
Un índice puede necesitar otra operator class o access method.
WHERE name ILIKE '%arroz%'Un B-tree no acelera normalmente un wildcard inicial.
Alternativas:
pg_trgm con GIN/GiST.No fuerces un índice normal a resolver semántica distinta.
Patrón problemático:
WHERE id::text = $1Transforma la columna y puede impedir el índice sobre id.
Mejor:
WHERE id = $1::bigintEl cast debe ocurrir sobre el parámetro cuando represente el mismo tipo del dominio.
Casts implícitos también pueden elegir una operator class inesperada. Revisa tipos que envía el driver.
Consulta:
WHERE price * 1.19 >= 100Puede reescribirse:
WHERE price >= 100 / 1.19Pero verifica precisión y semántica. No toda transformación algebraica es segura con NULL, overflow, numeric scale o funciones no lineales.
WHERE customer_id = $1
OR external_code = $2PostgreSQL puede combinar índices mediante bitmap OR, pero la selectividad y tipos importan.
A veces dos ramas con UNION ALL son más claras; otras veces empeoran. Usa EXPLAIN.
Patrón:
WHERE COALESCE(status, 'unknown') = $1Puede impedir índice simple y mezcla NULL con un valor real.
Alternativa semántica:
WHERE status = $1
OR ($1 = 'unknown' AND status IS NULL)O expression index si el dominio realmente trata ambos como equivalentes.
Permite expresar ramas:
CASE
WHEN quantity = 0 THEN 'out'
WHEN quantity < minimum_stock THEN 'low'
ELSE 'ok'
ENDDevuelve el primer valor no NULL.
Convierte igualdad en NULL, útil para evitar división por cero:
amount / NULLIF(quantity, 0)Seleccionan extremos, con semántica de NULL particular en PostgreSQL.
Estas expresiones deben representar el dominio, no ocultar datos inválidos.
ORDER BY lower(name)Puede necesitar sort. Un expression index puede ayudar si también encaja con filtros y dirección.
Un índice sobre lower(name) no cubre automáticamente name con collation normal.
Requisitos:
Consulta:
SELECT id, status, created_at, total_amount
FROM orders
WHERE tenant_id = $1
AND ($2::text IS NULL OR status = $2)
AND created_at >= $3
AND created_at < $4
AND (created_at, id) < ($5, $6)
ORDER BY created_at DESC, id DESC
LIMIT 50;Un índice posible:
CREATE INDEX orders_tenant_created_id_idx
ON orders(tenant_id, created_at DESC, id DESC)
INCLUDE (status, total_amount);Pero el filtro opcional de status puede justificar otro índice para workloads específicos. No crees uno por cada combinación sin medir.
Patrones como:
WHERE ($1 IS NULL OR status = $1)son cómodos, pero un generic plan puede no optimizar bien para casos con y sin filtro.
Alternativas:
SQL dinámico estructural no significa concatenar input libre.
Usa:
EXPLAIN (ANALYZE, BUFFERS)
SELECT ...;Busca:
Seq Scan con muchas filas eliminadas.Filter en lugar de Index Cond.Un sequential scan no es automáticamente malo. Para gran porcentaje de la tabla puede ser correcto.
Índices de texto pueden requerir reindex tras actualización de libc/ICU.
Una función que depende de timezone no es immutable.
function(NULL) suele devolver NULL si es strict, pero no todas lo son.
Puede ejecutarse millones de veces. Considera materialización, generated column o índice.
Cambiar implementación de función immutable exige reconstruir índice.
Puede conducir a casts o plans distintos. Añade cast explícito.
Impide acceso directo al índice.
Añade coste de escritura inútil.
Puede corromper la lógica del índice.
Cada operator class soporta operaciones concretas.
Solo EXPLAIN y métricas muestran el camino real.
Los parámetros solo representan valores. Usa whitelist para estructura.
date(created_at) = $1 puede impedir un índice normal?lower(email)?ILIKE '%texto%' necesita otra estrategia?Index Cond y Filter?Index Cond delimita candidatos desde el índice; Filter descarta filas después de accederlas.Views y materialized views explica cómo encapsular consultas, exponer contratos y persistir resultados con una política de actualización.