Vários meses atrás - público para PostgreSQL.
Você já o usou mais de 6000 vezes, mas um recurso útil que pode ter passado despercebido é pistas estruturais, que se parece com isto:
Ouça-os e seus pedidos “se tornarão suaves e sedosos”. 🙂
Mas, falando sério, muitas situações que tornam uma solicitação lenta e que consome muitos recursos são típicos e podem ser reconhecidos pela estrutura e dados do plano.
Nesse caso, cada desenvolvedor individual não precisa procurar uma opção de otimização por conta própria, contando apenas com sua experiência - podemos contar a ele o que está acontecendo aqui, qual poderia ser o motivo e como abordar uma solução. Foi isso que fizemos.
Vamos dar uma olhada nesses casos - como eles são definidos e a quais recomendações eles levam.
Para melhor mergulhar no assunto, você pode primeiro ouvir o bloco correspondente de , e só então passar para uma análise detalhada de cada exemplo:
Nº 1: índice “subclassificação”
Quando surge
Mostrar a última fatura do cliente "LLC Kolokolchik".
como identificar
-> Limit
-> Sort
-> Index [Only] Scan [Backward] | Bitmap Heap Scan
Recomendações
Índice usado expandir com campos de classificação.
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;
Você pode notar imediatamente que mais de 100 registros foram subtraídos do índice, que foram todos classificados e sobrou apenas um.
Corrigindo:
DROP INDEX tbl_fk_cli_idx;
CREATE INDEX ON tbl(fk_cli, pk DESC); -- добавили ключ сортировки
Mesmo em um modelo tão primitivo - 8.5 vezes mais rápido e 33 vezes menos leituras. Quanto mais “fatos” você tiver para cada valor, mais óbvio será o efeito fk.
Observo que tal índice funcionará como um índice de “prefixo” não pior do que antes para outras consultas com fk, onde classificar por pk não houve e não há (você pode ler mais sobre isso ). Inclusive, fornecerá normal suporte explícito de chave estrangeira neste campo.
#2: interseção de índice (BitmapAnd)
Quando surge
Mostrar todos os contratos do cliente “LLC Kolokolchik”, celebrados em nome de “NAO Buttercup”.
como identificar
-> BitmapAnd
-> Bitmap Index Scan
-> Bitmap Index ScanRecomendações
criar índice composto por campos de ambos os originais ou expanda um dos existentes com campos do 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); -- отбор по конкретной паре
Corrigindo:
DROP INDEX tbl_fk_org_idx;
CREATE INDEX ON tbl(fk_org, fk_cli);
A recompensa aqui é menor, já que o Bitmap Heap Scan é bastante eficaz por si só. Mas mesmo assim 7 vezes mais rápido e 2.5 vezes menos leituras.
Nº 3: Mesclar índices (BitmapOr)
Quando surge
Mostre as primeiras 20 solicitações “nós” ou não atribuídas mais antigas para processamento, com a sua em prioridade.
como identificar
-> BitmapOr
-> Bitmap Index Scan
-> Bitmap Index ScanRecomendações
Usar UNIÃO [TODOS] para combinar subconsultas para cada um dos blocos OR de condições.
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;
Corrigindo:
(
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 fato de que todos os 20 registros necessários foram recebidos imediatamente no primeiro bloco, então o segundo, com o Bitmap Heap Scan mais “caro”, nem foi executado - no final 22x mais rápido, 44x menos leituras!
Uma história mais detalhada sobre este método de otimização em exemplos concretos pode ser lido em artigos и .
Versão generalizada seleção ordenada baseada em várias chaves (e não apenas o par const/NULL) é discutido no artigo .
#4: Lemos muitas coisas desnecessárias
Quando surge
Via de regra, surge quando você deseja “anexar outro filtro” a uma solicitação já existente.
“E você não tem o mesmo, mas com botões de madrepérola? » filme "O Braço de Diamante"
Por exemplo, modificando a tarefa acima, mostre as primeiras 20 solicitações “críticas” mais antigas para processamento, independentemente de sua finalidade.
como identificar
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& 5 × rows < RRbF -- отфильтровано >80% прочитанного
&& loops × RRbF > 100 -- и при этом больше 100 записей суммарно
Recomendações
Crie [mais] especializado índice com condição WHERE ou inclua campos adicionais no índice.
Se a condição do filtro for "estática" para seus propósitos - isto é não implica expansão lista de valores no futuro - é melhor usar um índice WHERE. Vários status booleanos/enum se enquadram bem nesta categoria.
Se a condição de filtragem pode assumir diferentes significados, então é melhor expandir o índice com esses campos - como na situação com BitmapE acima.
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;
Corrigindo:
CREATE INDEX ON tbl(pk)
WHERE critical; -- добавили "статичное" условие фильтрации
Como você pode ver, a filtragem desapareceu completamente do plano e a solicitação tornou-se 5 vezes mais rápido.
#5: tabela esparsa
Quando surge
Várias tentativas de criar sua própria fila de processamento de tarefas, quando um grande número de atualizações/exclusões de registros na tabela levam a uma situação de um grande número de registros “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
Recomendações
Execute manualmente regularmente VÁCUO [COMPLETO] ou conseguir treinamento adequadamente frequente ajustando seus parâmetros, incluindo .
Na maioria dos casos, esses problemas são causados pela má composição da consulta ao chamar a partir da lógica de negócios, como as discutidas em .
Mas você precisa entender que mesmo o VACUUM FULL nem sempre ajuda. Para tais casos, vale a pena se familiarizar com o algoritmo do artigo .
#6: Lendo do “meio” do índice
Quando surge
Parece que lemos um pouco, e tudo foi indexado, e não filtramos ninguém em excesso - mas ainda assim lemos significativamente mais páginas do que gostaríamos.
como identificar
-> Index [Only] Scan [Backward]
&& loops × (rows + RRbF) < (shared hit + shared read) × 8
-- прочитано больше 1KB на каждую запись
&& shared hit + shared read > 64
Recomendações
Observe atentamente a estrutura do índice usado e os campos-chave especificados na consulta - provavelmente parte do índice não está definida. Muito provavelmente você terá que criar um índice semelhante, mas sem os 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;
Tudo parece estar bem, até mesmo de acordo com o índice, mas é um tanto suspeito - para cada um dos 20 registros lidos, tivemos que subtrair 4 páginas de dados, 32KB por registro - não é ousado? E o nome do índice tbl_fk_org_fk_cli_idx instigante.
Corrigindo:
CREATE INDEX ON tbl(fk_cli);
De repente - 10 vezes mais rápido e 4 vezes menos para ler!
Outros exemplos de situações de uso ineficaz de índices podem ser vistos no artigo .
Nº 7: CTE × CTE
Quando surge
A pedido marcou CTE “gordo” de mesas diferentes, e então decidi fazer isso entre elas JOIN.
O caso é relevante para versões abaixo da v12 ou solicitações com WITH MATERIALIZED.
como identificar
-> CTE Scan
&& loops > 10
&& loops × (rows + RRbF) > 10000
-- слишком большое декартово произведение CTE
Recomendações
Analise cuidadosamente a solicitação - e ? Se sim, então aplique "dicionário" em hstore/json de acordo com o modelo descrito em .
#8: trocar para disco (gravação temporária)
Quando surge
O processamento único (classificação ou exclusividade) de um grande número de registros não cabe na memória alocada para isso.
como identificar
-> *
&& temp written > 0Recomendações
Se a quantidade de memória usada pela operação não exceder muito o valor especificado do parâmetro , vale a pena corrigi-lo. Você pode imediatamente na configuração para todos ou através SET [LOCAL] para uma solicitação/transação específica.
Exemplo:
SHOW work_mem;
-- "16MB"
SELECT
random()
FROM
generate_series(1, 1000000)
ORDER BY
1;
Corrigindo:
SET work_mem = '128MB'; -- перед выполнением запроса
Por razões óbvias, se apenas a memória for usada e não o disco, a consulta será executada muito mais rapidamente. Ao mesmo tempo, parte da carga do HDD também é removida.
Mas você precisa entender que nem sempre será capaz de alocar muita memória - simplesmente não haverá o suficiente para todos.
Nº 9: estatísticas irrelevantes
Quando surge
Eles colocaram muito no banco de dados de uma vez, mas não tiveram tempo de afastá-lo ANALYZE.
como identificar
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& ratio >> 10Recomendações
Faça isso ANALYZE.
Esta situação é descrita com mais detalhes em .
#10: “algo deu errado”
Quando surge
Houve uma espera por um bloqueio imposto por uma solicitação concorrente ou havia recursos de hardware de CPU/hipervisor insuficientes.
como identificar
-> *
&& (shared hit / 8K) + (shared read / 1K) < time / 1000
-- RAM hit = 64MB/s, HDD read = 8MB/s
&& time > 100ms -- читали мало, но слишком долго
Recomendações
Usar externo sistema de monitoramento servidor para bloqueio ou consumo anormal de recursos. Já falamos sobre nossa versão de organização desse processo para centenas de servidores и .
Fonte: habr.com