Hace varios meses - público a PostgreSQL.
Ya lo has usado más de 6000 veces, pero una característica útil que puede haber pasado desapercibida es pistas estructurales, que se parece a esto:
Escúchalos y tus peticiones “se volverán suaves y sedosas”. 🙂
Pero en serio, muchas situaciones que hacen que una solicitud sea lenta y consuma muchos recursos Son típicos y pueden reconocerse por la estructura y los datos del plan..
En este caso, cada desarrollador no tiene que buscar una opción de optimización por su cuenta, basándose únicamente en su experiencia: podemos decirle qué está sucediendo aquí, cuál podría ser el motivo y cómo abordar una solución. Eso es lo que hicimos.
Echemos un vistazo más de cerca a estos casos: cómo se definen y a qué recomendaciones conducen.
Para sumergirte mejor en el tema, primero puedes escuchar el bloque correspondiente de , y solo entonces pasar a un análisis detallado de cada ejemplo:
#1: índice de “subclasificación”
Cuando surge
Muestre la última factura del cliente "LLC Kolokolchik".
como identificar
-> Limit
-> Sort
-> Index [Only] Scan [Backward] | Bitmap Heap Scan
Recomendaciones
Índice utilizado expandir con campos de clasificación.
Ejemplo:
CREATE TABLE tbl AS
SELECT
generate_series(1, 100000) pk -- 100K "фактов"
, (random() * 1000)::integer fk_cli; -- 1K разных внешних ключей
CREATE INDEX ON tbl(fk_cli); -- индекс для foreign key
SELECT
*
FROM
tbl
WHERE
fk_cli = 1 -- отбор по конкретной связи
ORDER BY
pk DESC -- хотим всего одну "последнюю" запись
LIMIT 1;
Inmediatamente se puede notar que se restaron más de 100 registros del índice, los cuales luego se ordenaron todos y luego quedó el único.
De corrección:
DROP INDEX tbl_fk_cli_idx;
CREATE INDEX ON tbl(fk_cli, pk DESC); -- добавили ключ сортировки
Incluso en una muestra tan primitiva. 8.5 veces más rápido y 33 veces menos lecturas. Cuantos más "hechos" tenga para cada valor, más obvio será el efecto fk.
Observo que dicho índice funcionará como un índice de "prefijo" no peor que antes para otras consultas con fk, donde ordenar por pk no hubo y no hay (puedes leer más sobre esto) ). Incluyendo, proporcionará normal. soporte explícito de clave externa en este campo.
#2: intersección de índice (BitmapAnd)
Cuando surge
Mostrar todos los acuerdos para el cliente “LLC Kolokolchik”, celebrados en nombre de “NAO Buttercup”.
como identificar
-> BitmapAnd
-> Bitmap Index Scan
-> Bitmap Index ScanRecomendaciones
crear índice compuesto por campos de ambos originales o ampliar uno de los existentes con campos del segundo.
Ejemplo:
CREATE TABLE tbl AS
SELECT
generate_series(1, 100000) pk -- 100K "фактов"
, (random() * 100)::integer fk_org -- 100 разных внешних ключей
, (random() * 1000)::integer fk_cli; -- 1K разных внешних ключей
CREATE INDEX ON tbl(fk_org); -- индекс для foreign key
CREATE INDEX ON tbl(fk_cli); -- индекс для foreign key
SELECT
*
FROM
tbl
WHERE
(fk_org, fk_cli) = (1, 999); -- отбор по конкретной паре
De corrección:
DROP INDEX tbl_fk_org_idx;
CREATE INDEX ON tbl(fk_org, fk_cli);
La recompensa aquí es menor, ya que Bitmap Heap Scan es bastante efectivo por sí solo. Pero de todos modos 7 veces más rápido y 2.5 veces menos lecturas.
#3: Fusionar índices (BitmapOr)
Cuando surge
Muestre las primeras 20 solicitudes de procesamiento “nosotros” o no asignadas más antiguas, teniendo la suya prioridad.
como identificar
-> BitmapOr
-> Bitmap Index Scan
-> Bitmap Index ScanRecomendaciones
Utilizar UNIÓN [TODOS] para combinar subconsultas para cada uno de los bloques OR de condiciones.
Ejemplo:
CREATE TABLE tbl AS
SELECT
generate_series(1, 100000) pk -- 100K "фактов"
, CASE
WHEN random() < 1::real/16 THEN NULL -- с вероятностью 1:16 запись "ничья"
ELSE (random() * 100)::integer -- 100 разных внешних ключей
END fk_own;
CREATE INDEX ON tbl(fk_own, pk); -- индекс с "вроде как подходящей" сортировкой
SELECT
*
FROM
tbl
WHERE
fk_own = 1 OR -- свои
fk_own IS NULL -- ... или "ничьи"
ORDER BY
pk
, (fk_own = 1) DESC -- сначала "свои"
LIMIT 20;
De corrección:
(
SELECT
*
FROM
tbl
WHERE
fk_own = 1 -- сначала "свои" 20
ORDER BY
pk
LIMIT 20
)
UNION ALL
(
SELECT
*
FROM
tbl
WHERE
fk_own IS NULL -- потом "ничьи" 20
ORDER BY
pk
LIMIT 20
)
LIMIT 20; -- но всего - 20, больше и не надо
Aprovechamos el hecho de que los 20 registros requeridos se recibieron inmediatamente en el primer bloque, por lo que el segundo, con el Bitmap Heap Scan más "caro", ni siquiera se ejecutó; al final 22 veces más rápido, 44 veces menos lecturas!
Una historia más detallada sobre este método de optimización. en ejemplos concretos se puede leer en artículos и .
Versión generalizada selección ordenada basada en varias claves (y no solo el par constante/NULL) se analiza en el artículo .
#4: Leemos muchas cosas innecesarias
Cuando surge
Como regla general, surge cuando desea "adjuntar otro filtro" a una solicitud ya existente.
“Y no tienes el mismo, pero con botones de nácar? " película "El brazo de diamante"
Por ejemplo, modificando la tarea anterior, muestre las primeras 20 solicitudes "críticas" más antiguas para su procesamiento, independientemente de su propósito.
como identificar
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& 5 × rows < RRbF -- отфильтровано >80% прочитанного
&& loops × RRbF > 100 -- и при этом больше 100 записей суммарно
Recomendaciones
Crear [más] especializado índice con condición WHERE o incluir campos adicionales en el índice.
Si la condición del filtro es "estática" para sus propósitos, es decir no implica expansión lista de valores en el futuro; es mejor usar un índice WHERE. Varios estados booleanos/enum encajan bien en esta categoría.
Si la condición de filtrado puede adquirir diferentes significados, entonces es mejor expandir el índice con estos campos, como en la situación con Bitmap y arriba.
Ejemplo:
CREATE TABLE tbl AS
SELECT
generate_series(1, 100000) pk -- 100K "фактов"
, CASE
WHEN random() < 1::real/16 THEN NULL
ELSE (random() * 100)::integer -- 100 разных внешних ключей
END fk_own
, (random() < 1::real/50) critical; -- 1:50, что заявка "критичная"
CREATE INDEX ON tbl(pk);
CREATE INDEX ON tbl(fk_own, pk);
SELECT
*
FROM
tbl
WHERE
critical
ORDER BY
pk
LIMIT 20;
De corrección:
CREATE INDEX ON tbl(pk)
WHERE critical; -- добавили "статичное" условие фильтрации
Como puede ver, el filtrado ha desaparecido por completo del plan y la solicitud se ha convertido 5 veces más rápido.
# 5: mesa escasa
Cuando surge
Varios intentos de crear su propia cola de procesamiento de tareas, cuando una gran cantidad de actualizaciones/eliminaciones de registros en la tabla conducen a una situación de una gran cantidad de registros "muertos".
como identificar
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& loops × (rows + RRbF) < (shared hit + shared read) × 8
-- прочитано больше 1KB на каждую запись
&& shared hit + shared read > 64
Recomendaciones
Realizar manualmente con regularidad VACÍO [LLENO] o lograr una formación suficientemente frecuente ajustando sus parámetros, incluyendo .
En la mayoría de los casos, estos problemas se deben a una composición deficiente de la consulta cuando se llama desde una lógica empresarial como las que se analizan en .
Pero es necesario comprender que incluso VACÍO LLENO puede no siempre ayudar. Para tales casos, vale la pena familiarizarse con el algoritmo del artículo. .
#6: Leer desde el “medio” del índice
Cuando surge
Parece que leemos un poco, todo está indexado y no filtramos a nadie en exceso, pero aun así leemos muchas más páginas de las que nos gustaría.
como identificar
-> Index [Only] Scan [Backward]
&& loops × (rows + RRbF) < (shared hit + shared read) × 8
-- прочитано больше 1KB на каждую запись
&& shared hit + shared read > 64
Recomendaciones
Observe de cerca la estructura del índice utilizado y los campos clave especificados en la consulta; lo más probable es que parte del índice no está establecido. Lo más probable es que tengas que crear un índice similar, pero sin los campos de prefijo o .
Ejemplo:
CREATE TABLE tbl AS
SELECT
generate_series(1, 100000) pk -- 100K "фактов"
, (random() * 100)::integer fk_org -- 100 разных внешних ключей
, (random() * 1000)::integer fk_cli; -- 1K разных внешних ключей
CREATE INDEX ON tbl(fk_org, fk_cli); -- все почти как в #2
-- только вот отдельный индекс по fk_cli мы уже посчитали лишним и удалили
SELECT
*
FROM
tbl
WHERE
fk_cli = 999 -- а fk_org не задано, хотя стоит в индексе раньше
LIMIT 20;
Todo parece estar bien, incluso según el índice, pero de alguna manera es sospechoso: para cada uno de los 20 registros leídos, tuvimos que restar 4 páginas de datos, 32 KB por registro, ¿no es audaz? Y el nombre del índice tbl_fk_org_fk_cli_idx estimulante.
De corrección:
CREATE INDEX ON tbl(fk_cli);
De repente - 10 veces más rápido y 4 veces menos para leer!
Otros ejemplos de situaciones de uso ineficaz de índices se pueden ver en el artículo. .
#7: CTE × CTE
Cuando surge
A pedido CTE “graso” puntuado de diferentes mesas, y luego decidió hacerlo entre ellas JOIN.
El caso es relevante para versiones inferiores a v12 o solicitudes con WITH MATERIALIZED.
como identificar
-> CTE Scan
&& loops > 10
&& loops × (rows + RRbF) > 10000
-- слишком большое декартово произведение CTE
Recomendaciones
Analice cuidadosamente la solicitud y ? Si es así, entonces aplicar "diccionario" en hstore/json según el modelo descrito en .
#8: cambiar a disco (escritura temporal)
Cuando surge
El procesamiento único (clasificación o unicización) de una gran cantidad de registros no cabe en la memoria asignada para ello.
como identificar
-> *
&& temp written > 0Recomendaciones
Si la cantidad de memoria utilizada por la operación no excede en gran medida el valor especificado del parámetro , vale la pena corregirlo. Puede hacerlo inmediatamente en la configuración para todos, o puede hacerlo a través de SET [LOCAL] para una solicitud/transacción específica.
Ejemplo:
SHOW work_mem;
-- "16MB"
SELECT
random()
FROM
generate_series(1, 1000000)
ORDER BY
1;
De corrección:
SET work_mem = '128MB'; -- перед выполнением запроса
Por razones obvias, si sólo se utiliza la memoria y no el disco, la consulta se ejecutará mucho más rápido. Al mismo tiempo, también se elimina parte de la carga del disco duro.
Pero debe comprender que no siempre podrá asignar mucha memoria: simplemente no habrá suficiente para todos.
#9: estadísticas irrelevantes
Cuando surge
Introdujeron mucho en la base de datos a la vez, pero no tuvieron tiempo de eliminarlo. ANALYZE.
como identificar
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& ratio >> 10Recomendaciones
llevarlo a cabo ANALYZE.
Esta situación se describe con más detalle en .
#10: "algo salió mal"
Cuando surge
Hubo una espera para un bloqueo impuesto por una solicitud competitiva o no había suficientes recursos de hardware de CPU/hipervisor.
como identificar
-> *
&& (shared hit / 8K) + (shared read / 1K) < time / 1000
-- RAM hit = 64MB/s, HDD read = 8MB/s
&& time > 100ms -- читали мало, но слишком долго
Recomendaciones
Usar externo Sistema de monitoreo servidor por bloqueo o consumo anormal de recursos. Ya hemos hablado de nuestra versión de organizar este proceso para cientos de servidores. и .
Fuente: habr.com