Contatos
IBSurgeon
Esta página foi traduzida por máquina. Leia o original em inglês. English

Biblioteca IBSurgeon

Melhorias no Otimizador do Firebird 5.0.1

(c) D.Simonov, IBSurgeon, 21-Ago-2024

Recentemente, foi lançada uma versão pontual do SGBD Firebird 5.0 Firebird 5.0.1. Além da correção de erros, foi adicionada uma nova função experimental de otimização, que será discutida neste artigo.

Convertendo subconsultas em ANY/SOME/IN/EXISTS para semi-join

Um semi-join é uma operação que une duas relações, retornando linhas de apenas uma das relações sem realizar a junção completa. Diferente de outros operadores de junção, não há sintaxe explícita para especificar se um semi-join deve ser realizado. No entanto, você pode realizar um semi-join usando subconsultas em ANY/SOME/IN/EXISTS.

Tradicionalmente, o Firebird transforma subconsultas em predicados ANY/SOME/IN em subconsultas correlacionadas no predicado EXISTS, e executa a subconsulta em EXISTS para cada registro da consulta externa. Ao executar uma subconsulta dentro de um predicado EXISTS, a estratégia FIRST ROWS é usada, e sua execução é interrompida imediatamente após o primeiro registro ser retornado.

A partir do Firebird 5.0.1, subconsultas em predicados ANY/SOME/IN/EXISTS podem ser convertidas em semi-joins. Este recurso está desabilitado por padrão e pode ser habilitado definindo o parâmetro de configuração SubQueryConversion como true no arquivo firebird.conf ou database.conf.
Este recurso é experimental, portanto está desabilitado por padrão. Você pode habilitá-lo e testar suas consultas com subconsultas em predicados ANY/SOME/IN/EXISTS, e se o desempenho for melhor, deixe-o habilitado; caso contrário, defina o parâmetro SubQueryConversion de volta ao padrão (false).
O valor padrão para o parâmetro de configuração SubQueryConversion pode ser alterado no futuro, ou o parâmetro pode ser removido completamente. Isso acontecerá quando a nova forma de execução for comprovadamente mais otimizada na maioria dos casos.

Diferente de executar ANY/SOME/IN/EXISTS diretamente em subconsultas, ou seja, como subconsultas correlacionadas, executá-los como semi-joins dá mais espaço para otimização. Semi-joins podem ser executados por vários algoritmos como Hash Join (semi) ou Nested Loop Join (semi), enquanto subconsultas correlacionadas são sempre executadas para cada registro da consulta externa.

Vamos tentar habilitar este recurso definindo o parâmetro SubQueryConversion como true no arquivo firebird.conf. Agora vamos fazer alguns experimentos.

Vamos executar a seguinte consulta:

sql 
SELECT
  COUNT(*)
FROM
  HORSE H
WHERE H.CODE_DEPARTURE = 1
  AND H.CODE_SEX = 2
  AND H.CODE_HORSE IN (
    SELECT COVER.CODE_FATHER
    FROM COVER
    WHERE COVER.CODE_DEPARTURE = 1
      AND EXTRACT(YEAR FROM COVER.BYDATE) = 2023
  )
Code 
Select Expression
    -> Aggregate
        -> Filter
            -> Hash Join (semi)
                -> Filter
                    -> Table "HORSE" as "H" Access By ID
                        -> Bitmap And
                            -> Bitmap
                                -> Index "FK_HORSE_DEPARTURE" Range Scan (full match)
                            -> Bitmap
                                -> Index "FK_HORSE_SEX" Range Scan (full match)
                -> Record Buffer (record length: 41)
                    -> Filter
                        -> Table "COVER" Access By ID
                            -> Bitmap And
                                -> Bitmap
                                    -> Index "IDX_COVER_BYYEAR" Range Scan (full match)
                                -> Bitmap
                                    -> Index "FK_COVER_DEPARTURE" Range Scan (full match)

                COUNT
=====================
                  297

Current memory = 552356752
Delta memory = 352
Max memory = 552567920
Elapsed time = 0.045 sec
Buffers = 32768
Reads = 0
Writes = 0
Fetches = 43984
Per table statistics:
--------------------------------+---------+---------+---------+---------+---------+
 Table name                     | Natural | Index   | Insert  | Update  | Delete  |
--------------------------------+---------+---------+---------+---------+---------+
COVER                           |         |     1516|         |         |         |
HORSE                           |         |    37069|         |         |         |
--------------------------------+---------+---------+---------+---------+---------+

No plano de execução vemos um novo método de junção Hash Join (semi). O resultado da subconsulta em IN foi armazenado em buffer, o que é visível no plano como Record Buffer (record length: 41). Ou seja, neste caso a subconsulta em IN foi executada uma vez, seu resultado foi salvo na memória da tabela hash, e então a consulta externa simplesmente pesquisou nesta tabela hash.

Para comparação, vamos executar a mesma consulta com a conversão de subconsulta para semi-join desabilitada.

Code 
Sub-query
    -> Filter
        -> Filter
            -> Table "COVER" Access By ID
                -> Bitmap And
                    -> Bitmap
                        -> Index "FK_COVER_FATHER" Range Scan (full match)
                    -> Bitmap
                        -> Index "IDX_COVER_BYYEAR" Range Scan (full match)
Select Expression
    -> Aggregate
        -> Filter
            -> Table "HORSE" as "H" Access By ID
                -> Bitmap And
                    -> Bitmap
                        -> Index "FK_HORSE_DEPARTURE" Range Scan (full match)
                    -> Bitmap
                        -> Index "FK_HORSE_SEX" Range Scan (full match)

                COUNT
=====================
                  297

Current memory = 552046496
Delta memory = 352
Max memory = 552135600
Elapsed time = 0.395 sec
Buffers = 32768
Reads = 0
Writes = 0
Fetches = 186891
Per table statistics:
--------------------------------+---------+---------+---------+---------+---------+
 Table name                     | Natural | Index   | Insert  | Update  | Delete  |
--------------------------------+---------+---------+---------+---------+---------+
COVER                           |         |      297|         |         |         |
HORSE                           |         |    37069|         |         |         |
--------------------------------+---------+---------+---------+---------+---------+

O plano de execução mostra que a subconsulta é executada para cada registro da consulta principal, mas usa um índice adicional FK_COVER_FATHER. Isso também é visível nas estatísticas de execução: o número de Fetches é 4 vezes maior, o tempo de execução é quase 4 vezes pior.
O leitor pode perguntar: por que o hash semi-join mostra 5 vezes mais leituras de índice da tabela COVER, mas por outro lado é melhor? O fato é que as leituras de índice nas estatísticas mostram o número de registros lidos usando o índice, elas não mostram o número total de acessos ao índice, alguns dos quais não resultam na recuperação de registros, mas esses acessos não são gratuitos.

O que aconteceu? Para entender melhor a transformação de subconsultas, vamos introduzir um operador imaginário de semi-join “SEMI JOIN”. Como já disse, este tipo de junção não é representado na linguagem SQL. Nossa consulta com o operador IN foi transformada em uma forma equivalente, que pode ser escrita da seguinte forma:

sql 
SELECT
  COUNT(*)
FROM
  HORSE H
  SEMI JOIN (
    SELECT COVER.CODE_FATHER
    FROM COVER
    WHERE COVER.CODE_DEPARTURE = 1
      AND EXTRACT(YEAR FROM COVER.BYDATE) = 2023
  ) TMP ON TMP.CODE_FATHER = H.CODE_HORSE
WHERE H.CODE_DEPARTURE = 1
  AND H.CODE_SEX = 2

Agora está mais claro. O mesmo acontece para subconsultas usando EXISTS. Vamos ver outro exemplo:

sql 
SELECT
  COUNT(*)
FROM
  HORSE H
WHERE H.CODE_DEPARTURE = 1
  AND EXISTS (
    SELECT *
    FROM COVER
    WHERE COVER.CODE_DEPARTURE = 1
      AND COVER.CODE_FATHER = H.CODE_FATHER
      AND COVER.CODE_MOTHER = H.CODE_MOTHER
  )

Atualmente, não é possível escrever tal EXISTS usando IN. Vamos ver como ele é implementado sem transformá-lo em um semi-join.

Code 
Sub-query
    -> Filter
        -> Table "COVER" Access By ID
            -> Bitmap And
                -> Bitmap
                    -> Index "FK_COVER_MOTHER" Range Scan (full match)
                -> Bitmap
                    -> Index "FK_COVER_FATHER" Range Scan (full match)
Select Expression
    -> Aggregate
        -> Filter
            -> Table "HORSE" as "H" Access By ID
                -> Bitmap
                    -> Index "FK_HORSE_DEPARTURE" Range Scan (full match)

                COUNT
=====================
                91908

Current memory = 552240400
Delta memory = 352
Max memory = 554680016
Elapsed time = 19.083 sec
Buffers = 32768
Reads = 0
Writes = 0
Fetches = 935679
Per table statistics:
--------------------------------+---------+---------+---------+---------+---------+
 Table name                     | Natural | Index   | Insert  | Update  | Delete  |
--------------------------------+---------+---------+---------+---------+---------+
COVER                           |         |    91908|         |         |         |
HORSE                           |         |    96021|         |         |         |
--------------------------------+---------+---------+---------+---------+---------+

Muito lento. Agora vamos definir SubQueryConversion = true e executar a consulta novamente.

Code 
Select Expression
    -> Aggregate
        -> Filter
            -> Hash Join (semi)
                -> Filter
                    -> Table "HORSE" as "H" Access By ID
                        -> Bitmap
                            -> Index "FK_HORSE_DEPARTURE" Range Scan (full match)
                -> Record Buffer (record length: 49)
                    -> Filter
                        -> Table "COVER" Access By ID
                            -> Bitmap
                                -> Index "FK_COVER_DEPARTURE" Range Scan (full match)

                COUNT
=====================
                91908

Current memory = 552102000
Delta memory = 352
Max memory = 561520736
Elapsed time = 0.208 sec
Buffers = 32768
Reads = 0
Writes = 0
Fetches = 248009
Per table statistics:
--------------------------------+---------+---------+---------+---------+---------+
 Table name                     | Natural | Index   | Insert  | Update  | Delete  |
--------------------------------+---------+---------+---------+---------+---------+
COVER                           |         |   140254|         |         |         |
HORSE                           |         |    96021|         |         |         |
--------------------------------+---------+---------+---------+---------+---------+

A consulta foi executada 100 vezes mais rápido! Se a reescrevermos usando nosso operador fictício SEMI JOIN, a consulta ficará assim:

sql 
SELECT
  COUNT(*)
FROM
  HORSE H
  SEMI JOIN (
    SELECT
      COVER.CODE_FATHER,
      COVER.CODE_MOTHER
    FROM COVER
  ) TMP ON TMP.CODE_FATHER = H.CODE_FATHER AND TMP.CODE_MOTHER = H.CODE_MOTHER
WHERE H.CODE_DEPARTURE = 1

Qualquer subconsulta correlacionada em IN/EXISTS pode ser convertida em um semi-join? Não, nem todas; por exemplo, se a subconsulta contiver filtros FETCH/FIRST/SKIP/ROWS, então a subconsulta não pode ser convertida em um semi-join e será executada como uma subconsulta correlacionada. Aqui está um exemplo de tal consulta:

sql 
SELECT
  COUNT(*)
FROM
  HORSE H
WHERE H.CODE_DEPARTURE = 1
  AND EXISTS (
    SELECT *
    FROM COVER
    WHERE COVER.CODE_FATHER = H.CODE_HORSE
    OFFSET 0 ROWS
  )

Aqui a frase OFFSET 0 ROWS não altera a semântica da consulta, e o resultado de sua execução será o mesmo que sem ela. Vamos ver o plano e as estatísticas desta consulta.

Code 
Sub-query
    -> Skip N Records
        -> Filter
            -> Table "COVER" Access By ID
                -> Bitmap
                    -> Index "FK_COVER_FATHER" Range Scan (full match)
Select Expression
    -> Aggregate
        -> Filter
            -> Table "HORSE" as "H" Access By ID
                -> Bitmap
                    -> Index "FK_HORSE_DEPARTURE" Range Scan (full match)

                COUNT
=====================

10971

Memória atual = 551912944
Delta de memória = 288
Memória máxima = 552002112
Tempo decorrido = 0,201 seg
Buffers = 32768
Leituras = 0
Gravações = 0
Buscas = 408988
Estatísticas por tabela:
--------------------------------+---------+---------+---------+---------+---------+
 Nome da tabela                 | Natural | Índice  | Inserir | Atualizar| Excluir |
--------------------------------+---------+---------+---------+---------+---------+
COVER                           |         |    10971|         |         |         |
HORSE                           |         |    96021|         |         |         |
--------------------------------+---------+---------+---------+---------+---------+

Como você pode ver, a transformação para semi-join não ocorreu. Agora vamos remover OFFSET 0 ROWS e obter as estatísticas novamente.

Code 
Select Expression
    -> Aggregate
        -> Filter
            -> Hash Join (semi)
                -> Filter
                    -> Table "HORSE" as "H" Access By ID
                        -> Bitmap
                            -> Index "FK_HORSE_DEPARTURE" Range Scan (full match)
                -> Record Buffer (record length: 33)
                    -> Table "COVER" Full Scan

                COUNT
=====================
                10971

Memória atual = 552112128
Delta de memória = 288
Memória máxima = 585044592
Tempo decorrido = 0,405 seg
Buffers = 32768
Leituras = 0
Gravações = 0
Buscas = 854841
Estatísticas por tabela:
--------------------------------+---------+---------+---------+---------+---------+
 Nome da tabela                 | Natural | Índice  | Inserir | Atualizar| Excluir |
--------------------------------+---------+---------+---------+---------+---------+
COVER                           |   722465|         |         |         |         |
HORSE                           |         |    96021|         |         |         |
--------------------------------+---------+---------+---------+---------+---------+

Aqui a conversão para semi-join aconteceu e, como podemos ver, o tempo de execução piorou. A razão é que atualmente o otimizador não tem uma estimativa de custo entre os algoritmos de junção Hash Join (semi) e Nested Loop Join (semi) usando um índice, então a regra é: se a condição de junção contém apenas igualdade, então o algoritmo Hash Join (semi) é escolhido, caso contrário, as subconsultas IN/EXISTS são executadas normalmente.

Agora vamos desabilitar a conversão para semi-join e observar as estatísticas de execução.

Code 
Sub-query
    -> Filter
        -> Table "COVER" Access By ID
            -> Bitmap
                -> Index "FK_COVER_FATHER" Range Scan (full match)
Select Expression
    -> Aggregate
        -> Filter
            -> Table "HORSE" as "H" Access By ID
                -> Bitmap
                    -> Index "FK_HORSE_DEPARTURE" Range Scan (full match)

                COUNT
=====================
                10971

Memória atual = 551912752
Delta de memória = 288
Memória máxima = 552001920
Tempo decorrido = 0,193 seg
Buffers = 32768
Leituras = 0
Gravações = 0
Buscas = 408988
Estatísticas por tabela:
--------------------------------+---------+---------+---------+---------+---------+
 Nome da tabela                 | Natural | Índice  | Inserir | Atualizar| Excluir |
--------------------------------+---------+---------+---------+---------+---------+
COVER                           |         |    10971|         |         |         |
HORSE                           |         |    96021|         |         |         |
--------------------------------+---------+---------+---------+---------+---------+

Como você pode ver, Buscas é exatamente igual ao caso em que a subconsulta continha a cláusula OFFSET 0 ROWS, e o tempo de execução difere dentro da margem de erro. Isso significa que você pode usar a cláusula OFFSET 0 ROWS como uma dica para desabilitar a conversão para semi-join.

Agora vamos analisar casos onde qualquer condição correlacionada diferente de igualdade e IS NOT DISTINCT FROM é usada em subconsultas.

sql 
SELECT
  COUNT(*)
FROM
  HORSE H
WHERE H.CODE_DEPARTURE = 1
  AND EXISTS (
    SELECT *
    FROM COVER
    WHERE COVER.BYDATE > H.BIRTHDAY
  )
Code 
Sub-query
    -> Filter
        -> Table "COVER" Access By ID
            -> Bitmap
                -> Index "COVER_IDX_BYDATE" Range Scan (lower bound: 1/1)
Select Expression
    -> Aggregate
        -> Filter
            -> Table "HORSE" as "H" Access By ID
                -> Bitmap
                    -> Index "FK_HORSE_DEPARTURE" Range Scan (full match)

Como eu disse acima, nenhuma transformação para semi-join ocorreu, a subconsulta é executada para cada registro da consulta principal.

Vamos continuar os experimentos, escrever uma consulta usando igualdade e mais um predicado além da igualdade.

sql 
SELECT
  COUNT(*)
FROM
  HORSE H
WHERE H.CODE_DEPARTURE = 1
  AND EXISTS (
    SELECT *
    FROM COVER
    WHERE COVER.CODE_FATHER = H.CODE_FATHER
      AND COVER.BYDATE > H.BIRTHDAY
  )
Code 
Select Expression
    -> Aggregate
        -> Nested Loop Join (semi)
            -> Filter
                -> Table "HORSE" as "H" Access By ID
                    -> Bitmap
                        -> Index "FK_HORSE_DEPARTURE" Range Scan (full match)
            -> Filter
                -> Filter
                    -> Table "COVER" Access By ID
                        -> Bitmap
                            -> Index "COVER_IDX_BYDATE" Range Scan (lower bound: 1/1)

Aqui no plano vemos o primeiro uso do método de junção Nested Loop Join (semi), mas infelizmente este plano é ruim, porque o índice FK_COVER_FATHER não é usado. Você não obterá nenhum resultado com essa consulta. Isso pode ser corrigido usando a dica OFFSET 0 ROWS.

sql 
SELECT
  COUNT(*)
FROM
  HORSE H
WHERE H.CODE_DEPARTURE = 1
  AND EXISTS (
    SELECT *
    FROM COVER
    WHERE COVER.CODE_FATHER = H.CODE_FATHER
      AND COVER.BYDATE > H.BIRTHDAY
    OFFSET 0 ROWS
  )
Code 
Sub-query
    -> Skip N Records
        -> Filter
            -> Table "COVER" Access By ID
                -> Bitmap
                    -> Index "FK_COVER_FATHER" Range Scan (full match)
Select Expression
    -> Aggregate
        -> Filter
            -> Table "HORSE" as "H" Access By ID
                -> Bitmap
                    -> Index "FK_HORSE_DEPARTURE" Range Scan (full match)

                COUNT
=====================
                72199

Memória atual = 554017824
Delta de memória = 320
Memória máxima = 554284480
Tempo decorrido = 45,548 seg
Buffers = 32768
Leituras = 0
Gravações = 0
Buscas = 84145713
Estatísticas por tabela:
--------------------------------+---------+---------+---------+---------+---------+
 Nome da tabela                 | Natural | Índice  | Inserir | Atualizar| Excluir |
--------------------------------+---------+---------+---------+---------+---------+
COVER                           |         | 75894621|         |         |         |
HORSE                           |         |    96021|         |         |         |
--------------------------------+---------+---------+---------+---------+---------+

Não é o melhor tempo de execução, mas neste caso pelo menos obtivemos o resultado.

Assim, converter subconsultas para ANY/SOME/IN/EXISTS em semi-join permite em alguns casos acelerar significativamente a execução da consulta, mas atualmente esse recurso ainda é imperfeito e, portanto, desabilitado por padrão. No Firebird 6.0, eles tentarão adicionar estimativa de custo para esse recurso, bem como corrigir uma série de outras deficiências. Além disso, o Firebird 6.0 planeja adicionar conversão de subconsultas ALL/NOT IN/NOT EXISTS para anti-join.

Para concluir a revisão da execução de subconsultas em IN/EXISTS, gostaria de observar que se você tiver uma consulta da forma

Code 
SELECT ...
FROM T1
WHERE  IN (SELECT campo FROM T2 ...)

ou

Code 
SELECT ...
FROM T1
WHERE EXISTS (SELECT ... FROM T2 WHERE T1. = T2.campo)

então tais consultas são quase sempre mais eficientes de executar como

Code 
SELECT ...
FROM
  T1
  JOIN (SELECT DISTINCT campo FROM T2) tmp ON tmp.campo = T1.

Deixe-me dar um exemplo claro:

sql 
SELECT
  COUNT(*)
FROM
  HORSE H
WHERE H.CODE_HORSE IN (
  SELECT
    CODE_FATHER
  FROM COVER
  WHERE EXTRACT(YEAR FROM COVER.BYDATE) = 2022
)

Plano de execução e estatísticas usando Hash Join (semi)

Code 
Select Expression
    -> Aggregate
        -> Filter
            -> Hash Join (semi)
                -> Table "HORSE" as "H" Full Scan
                -> Record Buffer (record length: 41)
                    -> Filter
                        -> Table "COVER" Access By ID
                            -> Bitmap
                                -> Index "IDX_COVER_BYYEAR" Range Scan (full match)

                COUNT
=====================
                 1616

Memória atual = 554176768
Delta de memória = 288
Memória máxima = 555531328
Tempo decorrido = 0,229 seg
Buffers = 32768
Leituras = 0
Gravações = 0
Buscas = 569683
Estatísticas por tabela:
--------------------------------+---------+---------+---------+---------+---------+
 Nome da tabela                 | Natural | Índice  | Inserir | Atualizar| Excluir |
--------------------------------+---------+---------+---------+---------+---------+
COVER                           |         |     6695|         |         |         |
HORSE                           |   525875|         |         |         |         |
--------------------------------+---------+---------+---------+---------+---------+

Bastante rápido, mas a tabela HORSE é lida completamente.

Plano de execução e estatísticas com execução clássica de subconsulta

Code 
Sub-query
    -> Filter
        -> Filter
            -> Table "COVER" Access By ID
                -> Bitmap And
                    -> Bitmap
                        -> Index "FK_COVER_FATHER" Range Scan (full match)
                    -> Bitmap
                        -> Index "IDX_COVER_BYYEAR" Range Scan (full match)
Select Expression
    -> Aggregate
        -> Filter
            -> Table "HORSE" as "H" Full Scan

                COUNT
=====================
                 1616

Memória atual = 553472512
Delta de memória = 288
Memória máxima = 553966592
Tempo decorrido = 6,862 seg
Buffers = 32768
Leituras = 0
Gravações = 0
Buscas = 2462726
Estatísticas por tabela:
--------------------------------+---------+---------+---------+---------+---------+
 Nome da tabela                 | Natural | Índice  | Inserir | Atualizar| Excluir |
--------------------------------+---------+---------+---------+---------+---------+
COVER                           |         |     1616|         |         |         |
HORSE                           |   525875|         |         |         |         |
--------------------------------+---------+---------+---------+---------+---------+

Muito lento. A tabela HORSE é totalmente varrida, e a subconsulta é executada várias vezes - para cada registro na tabela HORSE.

E agora uma opção rápida com DISTINCT

sql 
SELECT
  COUNT(*)
FROM
  HORSE H
  JOIN (
      SELECT
        DISTINCT
        CODE_FATHER
      FROM COVER
      WHERE EXTRACT(YEAR FROM COVER.BYDATE) = 2022
  ) TMP ON TMP.CODE_FATHER = H.CODE_HORSE
Code 
Select Expression
    -> Aggregate
        -> Nested Loop Join (inner)
            -> Unique Sort (record length: 44, key length: 12)
                -> Filter
                    -> Table "COVER" as "TMP COVER" Access By ID
                        -> Bitmap
                            -> Index "IDX_COVER_BYYEAR" Range Scan (full match)
            -> Filter
                -> Table "HORSE" as "H" Access By ID
                    -> Bitmap
                        -> Index "PK_HORSE" Unique Scan

                COUNT
=====================
                 1616

Memória atual = 554349728
Delta de memória = 320
Memória máxima = 555531328
Tempo decorrido = 0,011 seg
Buffers = 32768
Leituras = 0
Gravações = 0
Buscas = 14954
Estatísticas por tabela:
--------------------------------+---------+---------+---------+---------+---------+
 Nome da tabela                 | Natural | Índice  | Inserir | Atualizar| Excluir |
--------------------------------+---------+---------+---------+---------+---------+

COVER                           |         |     6695|         |         |         |
HORSE                           |         |     1616|         |         |         |
--------------------------------+---------+---------+---------+---------+---------+

Sem leituras desnecessárias, a consulta é executada muito rapidamente. Portanto, a conclusão - sempre observe o plano de execução de subconsultas em IN/EXISTS/ANY/SOME e verifique variantes alternativas de escrita de consultas.