Agregados de Ordenación y Hash
Las ordenaciones y los agregados de hash necesitan RAM hasta work_mem por nodo de plan por operación. Cuando la memoria es insuficiente, PostgreSQL 18.4 vuelca a disco y el tiempo de ejecución aumenta.
Receta
Tarjeta de receta de referencia rápida - lista para copiar y pegar.
EXPLAIN (ANALYZE, BUFFERS)
SELECT customer_id, count(*), sum(total)
FROM orders
GROUP BY customer_id
ORDER BY sum(total) DESC
LIMIT 20;Cuándo usar esto: Las consultas de agregación o ORDER BY muestran alta E/S o external merge / Disk en el plan.
Ejemplo de Trabajo
CREATE TABLE orders (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
customer_id bigint NOT NULL,
total numeric(12,2) NOT NULL,
created_at timestamptz NOT NULL DEFAULT now()
);
INSERT INTO orders (customer_id, total)
SELECT (random() * 50000)::bigint, (random() * 1000)::numeric(12,2)
FROM generate_series(1, 1000000);
ANALYZE orders;
SET work_mem = '1MB';
EXPLAIN (ANALYZE, BUFFERS)
SELECT customer_id, sum(total) AS revenue
FROM orders
GROUP BY customer_id
ORDER BY revenue DESC;
RESET work_mem;Busque HashAggregate frente a GroupAggregate, y Sort Method: external merge Disk cuando work_mem es muy pequeño.
Qué demuestra esto:
- Se prefiere el agregado de hash cuando no se requiere ordenación.
- Volcado de ordenación cuando se excede
work_mem. - Por qué los roles OLTP y OLAP necesitan diferentes
work_mem.
Profundización
Cómo Funciona
- HashAggregate - Construye una tabla hash con claves por las columnas
GROUP BY. Una pasada si no se requiere ordenación. - GroupAggregate - Requiere entrada ordenada por las claves de grupo; se empareja con
Sorto un escaneo de índice que emite filas ordenadas. - Sort - Quicksort en memoria hasta
work_mem; fusión externa a disco cuando es mayor. - Cada ordenación o hash en la misma consulta puede asignar hasta
work_mem(no compartido entre nodos en una consulta).
Nodos de Plan a Observar
| Nodo | Significado |
|---|---|
HashAggregate | Agrupación en memoria a través de tabla hash |
GroupAggregate | Agrupa la entrada previamente ordenada |
Sort Method: quicksort Memory | La ordenación cabe en work_mem |
Sort Method: external merge Disk | La ordenación se ha volcado |
Notas SQL
-- Reducir el trabajo de ordenación: igualar el orden del índice
CREATE INDEX orders_customer_total_idx ON orders (customer_id, total);
EXPLAIN (ANALYZE)
SELECT customer_id, sum(total)
FROM orders
GROUP BY customer_id;Trampas
- Aumentar
work_memglobalmente - Diez consultas concurrentes, cada una usando 256MB, pueden agotar la memoria del host. Solución: Establecerwork_mempor rol; mantener el valor global modesto. - Asumir que el agregado de hash siempre gana - Algunos planes necesitan salida ordenada para funciones de ventana. Solución: Leer si el nodo padre requiere orden.
- Distinción mediante ordenación -
SELECT DISTINCTen filas amplias ordena todo. Solución:DISTINCT ONcon un índice de soporte, o reescribir. - Ignorar el volcado de
HashAggregate- Raro pero posible en recuentos distintos enormes. Solución: Aumentarwork_mempara el rol de procesamiento por lotes o pre-agregar. - Funciones de ventana más ordenación - Múltiples ordenaciones apilan costos. Solución: Particionar los datos, usar índices parciales o mover a una réplica.
Alternativas
| Alternativa | Usar Cuándo | No Usar Cuándo |
|---|---|---|
| Vista materializada incremental | Agregados pesados repetidos | Necesidad de totales en tiempo real |
| GROUP BY parcial en la aplicación | Cardinalidad de resultado pequeña | Grupos distintos grandes |
| BRIN + filtro de tiempo | Ventana deslizante en series temporales | Grupos de dimensiones arbitrarias |
Preguntas Frecuentes
¿Cuánta work_mem necesito?
Comience a partir de los indicadores de volcado de EXPLAIN ANALYZE. Aumente en pequeños pasos por rol de carga de trabajo, no globalmente.
¿HashAggregate vs GroupAggregate?
Hash no necesita entrada ordenada. Group usa entrada ordenada y puede canalizar desde escaneos de índice.
¿Por qué external merge en SSD todavía duele?
Las ordenaciones en disco añaden latencia y CPU incluso en almacenamiento rápido. Las coincidencias en memoria son drásticamente más baratas.
¿LIMIT ayuda a GROUP BY?
Solo con optimizaciones como la detención temprana del agregado de grupo ordenado en planes específicos. No asuma.
¿Pueden los índices eliminar las ordenaciones?
Sí, cuando el orden del escaneo coincide con las claves principales de ORDER BY o GROUP BY.
¿Qué pasa con el agregado paralelo?
Los escaneos grandes pueden usar Partial Aggregate más Gather. Cada trabajador tiene su propio presupuesto de work_mem.
¿Cómo detecto el volcado de la tabla hash?
Hash Join muestra lotes; el volcado de HashAggregate es menos común, pero verifique el uso de archivos temporales en EXPLAIN ANALYZE.
¿work_mem afecta la creación de índices?
maintenance_work_mem controla la construcción de índices y VACUUM, no las ordenaciones de consultas.
¿Rol de temp_file_limit?
Limita las ordenaciones descontroladas por rol para proteger el almacenamiento compartido en grupos de SQL ad hoc.
¿Próxima lectura?
Ajuste de work_mem en la sección OLTP vs OLAP para valores predeterminados específicos del rol.
Relacionado
- Nested Loop, Hash Join, Merge Join - Volcados de Hash Join
- work_mem & sort/hash tuning - Valores predeterminados por rol
- Workload Basics - OLTP vs memoria por lotes
- EXPLAIN Basics - Lectura de líneas de buffer
Versiones de Stack: Esta página fue escrita para PostgreSQL 18.4 (estable 18, mantenimiento 17), pgvector 0.8+, PgBouncer 1.x, Patroni 3.x, y PostGIS 3.5+.