AQO - Otimização de consulta adaptável do PostgreSQL

Ao executar consultas, os DBMSs modernos usam o modelo de otimização de custos - com base nos coeficientes salvos nos arquivos de configuração e nas estatísticas coletadas, é calculado o "custo" da obtenção e o volume dos conjuntos de linhas resultantes. Quando as consultas são executadas novamente, o custo e a seletividade são recalculados. Você pode executar uma consulta e ver os valores reais desses parâmetros; no entanto, o otimizador DBMS não usa essas informações no processo de reagendamento (padrão).



Mas e se o otimizador mantivesse os valores reais de custo, seletividade e outros parâmetros necessários para a execução da consulta e, quando repetido, focasse não apenas nas estatísticas coletadas padrão, mas também nas salvas após a execução anterior?



Isso é chamado de otimização de consulta adaptável e é promissor. Alguns DBMSs já usam essas tecnologias.



Empresa Postgres Professional por vários anos trabalhando na extensão AQO para PostgreSQL, que implementa (de alguma forma) a otimização adaptativa. O trabalho ainda está em andamento, mas já há algo a ser testado.



Primeiro, vamos dar uma olhada na área de assunto da otimização de consultas.

Por que o planejador pode escolher um plano abaixo do ideal

A consulta SQL pode ser executada de diferentes maneiras. Por exemplo, quando duas tabelas são unidas, isso pode ser feito de várias maneiras diferentes - usando loops aninhados, mesclando e hash. Quanto mais tabelas envolvidas na consulta, mais variações de suas junções. A tarefa do planejador é escolher um plano de execução de consulta com um custo mínimo de muitas variações.



Como já mencionado, em seu trabalho, os agendadores de muitos DBMS usam informações estatísticas que são coletadas automática ou manualmente. O planejador calcula um custo estimado com base nessas estatísticas.



Em geral, os planejadores dos SGBDs modernos funcionam bem na maioria das situações. No entanto, em alguns casos, o plano escolhido pode estar muito longe do ideal.



Por exemplo,a falta de informações estatísticas atualizadas leva ao fato de o planejador ser guiado em seu trabalho por (provavelmente) dados incorretos sobre o número de linhas nas tabelas que estão sendo unidas. A subestimação excessiva (ou superestimação) da cardinalidade leva à escolha de métodos não ideais para acessar dados em tabelas.



Outro motivo importante pode ser a falta de índices necessários . Na ausência de índices, o planejador tem uma escolha limitada de métodos de acesso a dados.



Uso de condições dependentes (correlacionadas)também pode afetar negativamente a operação do DBMS. O planejador (por padrão) assume que todas as condições em uma consulta são independentes uma da outra, ou seja, o valor de uma condição não afeta a outra de forma alguma. Isso não é sempre o caso. Se condições dependentes forem usadas (por exemplo, código postal e cidade), o planejador também calculará o custo e a cardinalidade incorretos das conexões.



O planejador também pode ser afetado pelo uso de funções no ambiente . Uma função é uma caixa preta para o planejador, ele não sabe quantas linhas a função retornará, o que também pode levar a um custo incorreto do plano.

Maneiras de influenciar o trabalho do planejador

As estatísticas reais são uma condição indispensável para o trabalho adequado do planejador. Antes de tudo, verifique se o sistema está configurado para coletar regularmente informações estatísticas.

Existem várias maneiras de corrigir as situações acima e ajudar o planejador a escolher planos de execução de consulta mais ideais.



Sem índices, o planejador tem apenas uma maneira de obter verificações de tabela sequenciais de dados (e isso nem sempre é ruim e caro). Em alguns casos, a criação dos índices necessários pode ajudar a acelerar o acesso aos dados - você não precisa verificar a tabela inteira. Mas o uso de índices (encontrar o necessário, criar, manter) não é gratuito. Idealmente, eles devem ser usados ​​exatamente onde necessário. E onde não é necessário - não use.



Ao usar condições de conexão correlacionadas em consultas, você pode gerar estatísticas avançadas- explicitamente "avisa" o otimizador de que as condições estão relacionadas entre si. Para fazer isso, o administrador do banco de dados (ou desenvolvedor) precisa conhecer bem seus dados e monitorar as condições dependentes nas consultas, pois é difícil prever o número de combinações de colunas dependentes com antecedência. Estatísticas estendidas terão que ser criadas manualmente para cada uma dessas opções.



Ao criar uma função, você pode especificar o custo aproximado de execução e / ou uma estimativa do número de linhas retornadas pela função. Na versão 12 , tornou - se possível usar funções auxiliares para melhorar as estimativas do planejador, dependendo dos argumentos. Essa também é uma maneira manual, que nem sempre oferece um resultado ideal.



Se tudo mais falhar, você pode reescrever manualmente a consulta, por exemplo, usando visualizações materializadas, Common Table Expressions (CTE). Ou para esclarecer os requisitos da área de assunto e, possivelmente, reescrever radicalmente a lógica da consulta.



E há mais uma forma de "dica" o planejador - otimização de consultas adaptativa ( a daptive q uery o ptimization). A idéia desse método é que, depois que a consulta é executada, informações estatísticas reais são salvas e, quando a consulta fornecida (ou semelhante) é repetida, o otimizador pode confiar nela.



O DBMS Postgres Pro Enterprise é uma extensão para otimização de consulta adaptável chamada AQO... Esta extensão está publicada no github: github.com/postgrespro/aqo , você pode experimentá-lo com o PostgreSQL básico , mais sobre isso abaixo.

O princípio de operação do módulo

O módulo AQO usa aprendizado de máquina em seu trabalho. Você pode ler mais sobre o princípio de operação no artigo de Oleg Ivanov, Usando o Machine Learning para aumentar o desempenho do PostgreSQL, e em mais detalhes na apresentação Otimização de consulta adaptável (relatório no YouTube ).



A essência desse método é descrita brevemente abaixo:



Para estimar o custo, o planejador precisa de uma estimativa de cardinalidade e, para isso, por sua vez, é necessária uma estimativa da seletividade das condições.



Para condições simples (como "attribute = constant" ou "attribute> constant"), o planejador possui um modelo pelo qual estima a seletividade. Para fazer isso, ele usa informações estatísticas: o número de valores de atributos exclusivos, histogramas etc.

Para condições compostas de elementos simples usando conectivos lógicos, o planejador usa fórmulas facilmente calculadas:

• sel (não A) = 1 - sel (A)
• sel (não A) = 1 - sel (A)
• sel (A e B) = sel (A) * sel (B)
• sel (A ou B) = sel (não (não A e não B)) = 1 - (1 - sel (A)) * (1 - sel (B))

Essas fórmulas assumem a independência (não correlacionada) das condições A e B, razão pela qual obtemos estimativas incorretas no caso em que essa suposição é violada.

O AQO complica a fórmula: introduz seu próprio coeficiente para cada condição simples. Usando o aprendizado de máquina (usando a regressão do vizinho mais próximo), o AQO seleciona esses coeficientes para que a seletividade calculada pela fórmula corresponda melhor à seletividade real que o AQO observou anteriormente.



Para isso, o módulo salva:

• , ;
• .

Em seu trabalho, o AQO distingue entre condições até constantes. Isso permite reduzir a complexidade do problema que está sendo resolvido e, além disso, na maioria dos casos, as informações ainda não são perdidas: o AQO não "vê" o valor da constante, mas "vê" a seletividade da condição.

A situação em que a perda ocorre são as condições avaliadas como constantes, independentemente dos valores específicos. Por exemplo, para algumas condições, o planejador não pode fazer estimativas razoáveis ​​e escolhe a constante padrão (por exemplo, a seletividade da condição “expressão1 = expressão2” é sempre avaliada como 0,005 e “expressão1> expressão2” como 1/3).



Assim, o AQO melhora a estimativa de seletividade de condições complexas (e, como conseqüência, a estimativa de custos, que podem levar à escolha de um plano de execução mais adequado).

Instalando o módulo

Para experimentar a funcionalidade do módulo no PostgreSQL, você precisa usar um patch especial e, em seguida, montar o sistema a partir do código-fonte. Veja o arquivo README no github para mais detalhes .



Se o Postgres Pro Enterprise for usado, a instalação do módulo AQO continuará no modo padrão:



shared_preload_libraries = 'aqo'



Depois disso, você poderá criar uma extensão no banco de dados necessário.

Preparando o Banco de Dados

Vejamos um exemplo específico de como o módulo AQO funciona em um banco de dados demo . Usaremos um grande banco de dados contendo informações sobre voos para o ano de setembro de 2016 a setembro de 2017.



Primeiro, crie uma extensão:

CREATE EXTENSION aqo;

Em seguida, desative o processamento de consultas paralelas - para que a exibição de planos paralelos não desvie a tarefa principal:



max_parallel_workers_per_gather = 0;



Para que o planejador PostgreSQL tenha mais opções para ingressar em tabelas, crie dois índices:

CREATE INDEX ON flights (scheduled_departure );
CREATE INDEX ON ticket_flights (fare_conditions );

Ao analisar os resultados, focaremos no valor de BUFFERS como o número de páginas que você precisa ler para concluir o trabalho. Vejamos também o tempo de execução (mas o tempo em um sistema carregado e em um laptop doméstico pode ser muito diferente).



Aumente o cache do buffer e work_mem para que todo o trabalho seja realizado na RAM:



shared_buffers = '256MB';

work_mem = '256MB';

Usando o módulo AQO

Vamos formular um pedido: você precisa pegar passageiros que voaram na classe executiva a partir de uma determinada data e chegaram com um atraso de não mais de uma hora.

Executamos a solicitação sem usar o AQO (a seguir, parte das informações que não afetam o entendimento da operação do módulo foram removidas dos planos):

EXPLAIN (ANALYZE, BUFFERS, TIMING OFF) SELECT t.ticket_no
  FROM flights f
   	JOIN ticket_flights tf ON f.flight_id = tf.flight_id
   	JOIN tickets t ON tf.ticket_no = t.ticket_no
 WHERE f.scheduled_departure > '2017-08-01'::timestamptz
   AND f.actual_arrival < f.scheduled_arrival + interval '1 hour'
   AND tf.fare_conditions = 'Business';

E vamos ver o plano resultante: nesse caso, o planejador considerou o plano ideal, no qual, primeiro, usando uma verificação de bitmap ( ), obtemos um conjunto de linhas da tabela de vôos, que temos (um nó ) com um conjunto de linhas da tabela ticket_flights obtida usando o índice digitalizar ( ). O resultado será usado como um conjunto externo de linhas para a conexão final por um loop aninhado (nó ). O conjunto interno para esta conexão é obtido por uma varredura de índice exclusiva da tabela tickets ( ). A operação mais "volumosa" é obter o conjunto de linhas interno para o loop aninhado - 106.205 buffers são lidos nele.



Nested Loop (rows=33210) (actual rows=31677)

  Buffers: shared hit=116830 read=1

  ->  Hash Join (rows=33210) (actual rows=31677)

        Hash Cond: (tf.flight_id = f.flight_id)

        ->  Index Scan ... on ticket_flights tf  

              Index Cond: fare_conditions = 'Business'

        ->  Hash

              ->  Bitmap Heap Scan on flights f (rows=8331) (actual rows=7673)

                    Recheck Cond: scheduled_departure > '2017-08-01'

                    Filter: actual_arrival < scheduled_arrival + '01:00:00'::interval

                    ->  Bitmap Index Scan on ... [flights]

                          Index Cond: scheduled_departure > '2017-08-01'

                          Buffers: shared hit=44 read=1

  ->   Index Only Scan  ... on tickets t (rows=1 width=14) (actual rows=1 loops=31677)

        Index Cond: (ticket_no = tf.ticket_no)

        Buffers: shared hit=106205

 Planning Time: 9.326 ms

 Execution Time: 675.836 ms



Bitmap Heap Scan on flightsHash JoinIndex Scan ... on ticket_flightsNested LoopIndex Only Scan ... on tickets





Esse plano pode ser chamado de relativamente bom, pois um número relativamente pequeno de linhas no conjunto externo é processado por uma junção de loop aninhada.



Agora vamos realizar um experimento e ver como o plano proposto irá (ou não) mudar, dependendo da alteração nas datas na solicitação. As datas são selecionadas de maneira a aumentar consistentemente o intervalo de linhas da tabela Voos que satisfazem a condição, o que leva a um erro do planejador na avaliação da cardinalidade do acesso a essa tabela. No plano acima, você pode ver que, com a primeira data, o otimizador quase não se engana em cardinalidade ( ). Vamos substituir as seguintes datas na solicitação, uma por uma:Bitmap Heap Scan on flights f (rows=8331) (actual rows=7673)

• 01/04/2017
• 01-01-2017
• 01/08/2016

E veja o resultado:





Para resumir, sem usar o módulo AQO, o planejador funciona da seguinte maneira:



Vejamos o resultado novamente:

Com o AQO ativado, o planejador percebe rapidamente que precisa alternar de uma junção de loop aninhada e uso de índice para uma junção de hash e varredura seqüencial.

Resumir

Existem vantagens e desvantagens em usar o módulo AQO para otimização de consulta adaptável.



Um dos benefícios do uso de um módulo é que você não precisa acompanhar as condições dependentes nas consultas. Em alguns casos, a velocidade de execução da consulta pode aumentar. E existem diferentes modos de usar o módulo. Por exemplo, você pode usar o AQO para otimizar apenas certos tipos de consultas.



Entre as desvantagens do módulo, pode-se destacar custos indiretos adicionais para treinamento e armazenamento de estatísticas nas estruturas do módulo. E as informações estatísticas coletadas pelo módulo não são transferidas para réplicas.



O módulo AQO não é uma bala de prata contra todos os possíveis problemas de agendamento. Por exemplo, em algumas situações, o módulo pode substituir estatísticas estendidas (se não forem criadas manualmente) ou não prestará atenção a estatísticas irrelevantes. Mas o módulo não criará os índices necessários e, além disso, não reescreverá o texto da consulta.



Portanto, você não deve incluir um módulo para todas as solicitações. Os candidatos ideais à otimização para o AQO são consultas em que o erro do planejador em calcular a cardinalidade dos nós resulta em um plano incorreto. E, por alguma razão, não é possível influenciar o refinamento dessa estimativa.