Hai varios meses - público a PostgreSQL.
Usaches máis de 6000 veces desde entón, pero unha das características útiles que podería pasar desapercibida é pistas estruturais, que parecen algo así:
Escóitaos e as túas solicitudes "volveranse suaves como a seda". 🙂
Pero en serio, moitas situacións que fan que unha solicitude sexa lenta e "glosa" en canto a recursos, son típicas e pódense recoñecer pola estrutura e os datos do plano.
Neste caso, cada desenvolvedor individual non terá que buscar unha opción de optimización por si só, confiando só na súa propia experiencia: podemos dicirlle o que está a suceder aquí, cal podería ser o motivo e como chegar a unha solución. Que é o que fixemos.
Vexamos máis de cerca estes casos: como se definen e a que recomendacións conducen.
Para unha mellor inmersión no tema, primeiro podes escoitar o bloque correspondente dende , e só entón vai a unha análise detallada de cada exemplo:
#1: índice "subclasificación"
Cando xorde
Mostra a última factura do cliente "LLC Kolokolchik".
Como identificar
-> Limit
-> Sort
-> Index [Only] Scan [Backward] | Bitmap Heap Scan
Recomendacións
Índice utilizado expandir con campos de clasificación.
Exemplo:
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;
Pódese notar de inmediato que o índice subtraeu máis de 100 rexistros, que logo foron todos clasificados e, a continuación, quedou o único.
Resolvemos:
DROP INDEX tbl_fk_cli_idx;
CREATE INDEX ON tbl(fk_cli, pk DESC); -- добавили ключ сортировки
Incluso nunha mostra tan primitiva - 8.5 veces máis rápido e 33 veces menos lecturas. O efecto será máis claro, cantos máis "feitos" teña para cada valor. fk.
Observo que tal índice funcionará como un índice "prefixo" non peor que o anterior para outras consultas con fk, onde ordenar por pk non foi nin é (podes ler máis sobre isto ). En particular, proporcionará normal soporte explícito de clave externa por este campo.
#2: intersección do índice (BitmapAnd)
Cando xorde
Mostrar todos os contratos para o cliente "LLC Kolokolchik" celebrado en nome de "NJSC Lyutik".
Como identificar
-> BitmapAnd
-> Bitmap Index Scan
-> Bitmap Index ScanRecomendacións
crear índice composto por campos de ambas fontes ou expandir un dos campos existentes desde o segundo.
Exemplo:
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); -- отбор по конкретной паре
Resolvemos:
DROP INDEX tbl_fk_org_idx;
CREATE INDEX ON tbl(fk_org, fk_cli);
Aquí a ganancia é menor, xa que Bitmap Heap Scan é bastante eficaz por si só. Pero de todos os xeitos 7 veces máis rápido e 2.5 veces menos lecturas.
#3: Combinación de índices (BitmapOr)
Cando xorde
Mostrar as 20 primeiras solicitudes "propias" ou non asignadas máis antigas para procesar, con prioridade propias.
Como identificar
-> BitmapOr
-> Bitmap Index Scan
-> Bitmap Index ScanRecomendacións
Usa UNIÓN [TODOS] para combinar subconsultas para cada un dos bloques de condición OR.
Exemplo:
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;
Resolvemos:
(
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, больше и не надо
Aproveitamos o feito de que os 20 rexistros necesarios obtivéronse inmediatamente no primeiro bloque, polo que o segundo, co máis "caro" Bitmap Heap Scan, nin sequera se executou, como resultado. 22 veces máis rápido, 44 veces menos lecturas!
Unha historia máis detallada sobre este método de optimización sobre exemplos concretos pódese ler nos artigos и .
Versión xeneralizada selección ordenada por varias teclas (e non só para un par de const / NULL) é discutido no artigo .
#4: Lemos demasiado
Cando xorde
Como regra xeral, ocorre cando quere "anexar outro filtro" a unha solicitude existente.
"E non tes o mesmo, pero con botóns de perlas? " película "Diamond Hand"
Por exemplo, modificando a tarefa anterior, mostra as 20 primeiras solicitudes "críticas" máis antigas para procesar, independentemente da súa finalidade.
Como identificar
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& 5 × rows < RRbF -- отфильтровано >80% прочитанного
&& loops × RRbF > 100 -- и при этом больше 100 записей суммарно
Recomendacións
Crea [máis] especializada índice coa cláusula WHERE ou incluír campos adicionais no índice.
Se a condición de filtrado é "estática" para as túas tarefas, é dicir non inclúe expansión lista de valores no futuro - é mellor usar un índice WHERE. Varios estados booleanos/enum encaixan ben nesta categoría.
Se a condición de filtración pode asumir diferentes valores, é mellor expandir o índice con estes campos, como na situación con BitmapAnd arriba.
Exemplo:
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;
Resolvemos:
CREATE INDEX ON tbl(pk)
WHERE critical; -- добавили "статичное" условие фильтрации
Como podes ver, o filtrado do plan desapareceu por completo e a solicitude converteuse 5 veces máis rápido.
#5: mesa escasa
Cando xorde
Varios intentos de facer a súa propia cola de procesamento de tarefas, cando un gran número de actualizacións / borrados de rexistros sobre a mesa conducen a unha situación de gran número de rexistros "mortos".
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
Recomendacións
Realízase manualmente con regularidade ASPIRADOR [CHEO] ou lograr un procesamento suficientemente frecuente axustando os seus parámetros, incluíndo .
Na maioría dos casos, estes problemas son causados por un deseño de consulta deficiente cando se chama desde a lóxica empresarial, como os que se comentan en .
Pero debemos entender que mesmo VACUUM FULL non sempre pode axudar. Para tales casos, debes familiarizarte co algoritmo do artigo. .
#6: lectura desde o "medio" do índice
Cando xorde
Parece que leron un pouco, e todo estaba indexado, e non filtraron a ninguén máis, pero aínda así, léronse significativamente máis páxinas das que nos gustaría.
Como identificar
-> Index [Only] Scan [Backward]
&& loops × (rows + RRbF) < (shared hit + shared read) × 8
-- прочитано больше 1KB на каждую запись
&& shared hit + shared read > 64
Recomendacións
Bótalle unha ollada atenta á estrutura do índice utilizado e aos campos clave especificados na consulta; moi probablemente, parte do índice non definida. Probablemente necesites crear un índice similar, pero sen campos de prefixo, ou .
Exemplo:
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 ben, mesmo no que se refire ao índice, pero dalgún xeito sospeitoso -por cada un dos 20 rexistros lidos houbo que restar 4 páxinas de datos, 32 KB por rexistro- non é ousada? Si e nome índice tbl_fk_org_fk_cli_idx leva ao pensamento.
Resolvemos:
CREATE INDEX ON tbl(fk_cli);
De súpeto - 10 veces máis rápido e 4 veces menos para ler!
Para obter máis exemplos de uso ineficiente de índices, consulte o artigo .
#7: CTE × CTE
Cando xorde
A petición anotou CTE "gordo". de diferentes mesas, e despois decidiu facer entre elas JOIN.
O caso é relevante para versións inferiores á v12 ou solicitudes con WITH MATERIALIZED.
Como identificar
-> CTE Scan
&& loops > 10
&& loops × (rows + RRbF) > 10000
-- слишком большое декартово произведение CTE
Recomendacións
Analiza atentamente a solicitude ? Se si, entón aplicar "dicionario" en hstore/json segundo o modelo descrito en .
#8: cambiar a disco (escrito temporal)
Cando xorde
O procesamento único (ordenación ou singularización) dun gran número de rexistros non cabe na memoria asignada para iso.
Como identificar
-> *
&& temp written > 0Recomendacións
Se a cantidade de memoria utilizada pola operación non supera moito o valor establecido do parámetro , debe ser corrixido. Podes entrar inmediatamente na configuración para todos, ou podes pasar SET [LOCAL] para unha solicitude/transacción específica.
Exemplo:
SHOW work_mem;
-- "16MB"
SELECT
random()
FROM
generate_series(1, 1000000)
ORDER BY
1;
Resolvemos:
SET work_mem = '128MB'; -- перед выполнением запроса
Por razóns obvias, se só se usa memoria e non disco, entón a consulta executarase moito máis rápido. Ao mesmo tempo, tamén se elimina parte da carga do disco duro.
Pero cómpre entender que asignar moita memoria tampouco sempre funcionará, simplemente non será suficiente para todos.
#9: estatísticas irrelevantes
Cando xorde
Verteuse moito á base á vez, pero non tiveron tempo de afastalo ANALYZE.
Como identificar
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& ratio >> 10Recomendacións
Gasta o mesmo ANALYZE.
Esta situación descríbese con máis detalle en .
# 10: "algo saíu mal"
Cando xorde
Había un bloqueo agardando por unha solicitude competidora ou non 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 -- читали мало, но слишком долго
Recomendacións
Use un externo sistema de vixilancia servidor para bloquear ou consumir recursos anormales. Xa falamos da nosa versión de organizar este proceso para centos de servidores. и .
Fonte: www.habr.com