Temos uma política bastante restritiva em relação a anúncios no site. Eles nunca irão te atrapalhar!
Além disso, usamos banners para lhe informar de assuntos importantes. Os bloqueadores de anúncios impedem que esses banners sejam visualizados.
Sendo assim, para continuar, é importante que você desligue o bloqueador de anúncio e recarregue a página. Obrigado!

Além de corrigir bugs, o Firebird 5.0.1 trouxe uma nova função experimental do otimizador de queries que será discutida neste artigo.

1. Convertendo subconsultas para ANY/SOME/IN/EXISTS em semi-joins

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. Ao contrário de outros operadores de junção, não há uma sintaxe explícita para especificar se deve ser realizado um semi-join. No entanto, você pode realizar um semi-join usando subconsultas com ANY/SOME/IN/EXISTS.

Tradicionalmente, o Firebird transforma subconsultas em predicados ANY/SOME/IN em subconsultas correlacionadas no predicado EXISTS, e executa a subconsulta no 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 para 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. Essa funcionalidade está desabilitada por padrão e pode ser ativada definindo o parâmetro de configuração SubQueryConversion como true no arquivo firebird.conf ou database.conf.

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

Tentaremos ativar essa funcionalidade definindo o parâmetro SubQueryConversion como true no arquivo firebird.conf e experimentar algumas variações.

Vamos executar a seguinte consulta:

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
  )

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, nesse 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 procurou nessa tabela hash.

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

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 que aconteceu? Para entender melhor a transformação das subconsultas, vamos introduzir um operador imaginário de semi-join "SEMI JOIN". Como já dito, esse 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 maneira:

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:

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 um EXISTS assim usando IN. Vamos ver como ele é implementado sem transformá-lo em um semi-join.

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! Vamos definir SubQueryConversion = true e executar a consulta novamente:

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 reescrevermos usando nosso operador fictício SEMI JOIN, a consulta ficará assim:

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 para 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 uma consulta assim:

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 da sua execução será o mesmo que sem ela. Vamos ver o plano e as estatísticas dessa consulta.

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

Current memory = 551912944
Delta memory = 288
Max memory = 552002112
Elapsed time = 0.201 sec
Buffers = 32768
Reads = 0
Writes = 0
Fetches = 408988
Per table statistics:
--------------------------------+---------+---------+---------+---------+---------+
 Table name                     | Natural | Index   | Insert  | Update  | Delete  |
--------------------------------+---------+---------+---------+---------+---------+
COVER                           |         |    10971|         |         |         |
HORSE                           |         |    96021|         |         |         |
--------------------------------+---------+---------+---------+---------+---------+

Perceba que a transformação para um semi-join não ocorreu. Agora vamos remover OFFSET 0 ROWS e pegar as estatísticas novamente.

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

Current memory = 552112128
Delta memory = 288
Max memory = 585044592
Elapsed time = 0.405 sec
Buffers = 32768
Reads = 0
Writes = 0
Fetches = 854841
Per table statistics:
--------------------------------+---------+---------+---------+---------+---------+
 Table name                     | Natural | Index   | Insert  | Update  | Delete  |
--------------------------------+---------+---------+---------+---------+---------+
COVER                           |   722465|         |         |         |         |
HORSE                           |         |    96021|         |         |         |
--------------------------------+---------+---------+---------+---------+---------+

Aqui a conversão para semi-join aconteceu, mas 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 contiver apenas igualdade, então o algoritmo Hash Join (semi) é escolhido, caso contrário, as subconsultas IN/EXISTS são executadas como de costume.

Vamos desativar a conversão de semi-join e ver as estatísticas de execução.

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

Current memory = 551912752
Delta memory = 288
Max memory = 552001920
Elapsed time = 0.193 sec
Buffers = 32768
Reads = 0
Writes = 0
Fetches = 408988
Per table statistics:
--------------------------------+---------+---------+---------+---------+---------+
 Table name                     | Natural | Index   | Insert  | Update  | Delete  |
--------------------------------+---------+---------+---------+---------+---------+
COVER                           |         |    10971|         |         |         |
HORSE                           |         |    96021|         |         |         |
--------------------------------+---------+---------+---------+---------+---------+

Perceba que o número de Fetches é 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 desativar a conversão de semi-join.

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

SELECT
  COUNT(*)
FROM
  HORSE H
WHERE H.CODE_DEPARTURE = 1
  AND EXISTS (
    SELECT *
    FROM COVER
    WHERE COVER.BYDATE > H.BIRTHDAY
  )

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 já mencionado, nenhuma transformação para semi-join ocorreu, a subconsulta é executada para cada registro da consulta principal.

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

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
  )

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)

Pelo plano vemos o primeiro uso do método de junção Nested Loop Join (semi), mas infelizmente este plano é ruim, pois o índice FK_COVER_FATHER não é usado. Uma consulta assim não dá um resultado ideal. Isso pode ser corrigido usando a dica OFFSET 0 ROWS.

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
  )

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

Current memory = 554017824
Delta memory = 320
Max memory = 554284480
Elapsed time = 45.548 sec
Buffers = 32768
Reads = 0
Writes = 0
Fetches = 84145713
Per table statistics:
--------------------------------+---------+---------+---------+---------+---------+
 Table name                     | Natural | Index   | Insert  | Update  | Delete  |
--------------------------------+---------+---------+---------+---------+---------+
COVER                           |         | 75894621|         |         |         |
HORSE                           |         |    96021|         |         |         |
--------------------------------+---------+---------+---------+---------+---------+

Não é o melhor tempo de execução, mas nesse caso pelo menos obtivemos alguma melhoria.

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

Para concluir a análise da execução de subconsultas em IN/EXISTS, gostaria de observar que se você tiver uma consulta do tipo:

SELECT ...
FROM T1
WHERE  IN (SELECT field FROM T2 ...)

ou

SELECT ...
FROM T1
WHERE EXISTS (SELECT ... FROM T2 WHERE T1. = T2.field)

tais consultas são quase sempre mais eficientes de serem executadas como

SELECT ...
FROM
  T1
  JOIN (SELECT DISTINCT field FROM T2) tmp ON tmp.field = T1.<primary key>

Vou dar um exemplo claro:

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)

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

Current memory = 554176768
Delta memory = 288
Max memory = 555531328
Elapsed time = 0.229 sec
Buffers = 32768
Reads = 0
Writes = 0
Fetches = 569683
Per table statistics:
--------------------------------+---------+---------+---------+---------+---------+
 Table name                     | Natural | Index   | Insert  | Update  | Delete  |
--------------------------------+---------+---------+---------+---------+---------+
COVER                           |         |     6695|         |         |         |
HORSE                           |   525875|         |         |         |         |
--------------------------------+---------+---------+---------+---------+---------+

Muito rápido, mas a tabela HORSE foi lida completamente.

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

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

Current memory = 553472512
Delta memory = 288
Max memory = 553966592
Elapsed time = 6.862 sec
Buffers = 32768
Reads = 0
Writes = 0
Fetches = 2462726
Per table statistics:
--------------------------------+---------+---------+---------+---------+---------+
 Table name                     | Natural | Index   | Insert  | Update  | Delete  |
--------------------------------+---------+---------+---------+---------+---------+
COVER                           |         |     1616|         |         |         |
HORSE                           |   525875|         |         |         |         |
--------------------------------+---------+---------+---------+---------+---------+

Muito lento. A tabela HORSE é lida completamente e a subconsulta é executada várias vezes — para cada registro na tabela HORSE.

Agora uma opção rápida com DISTINCT

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

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

Current memory = 554349728
Delta memory = 320
Max memory = 555531328
Elapsed time = 0.011 sec
Buffers = 32768
Reads = 0
Writes = 0
Fetches = 14954
Per table statistics:
--------------------------------+---------+---------+---------+---------+---------+
 Table name                     | Natural | Index   | Insert  | Update  | Delete  |
--------------------------------+---------+---------+---------+---------+---------+
COVER                           |         |     6695|         |         |         |
HORSE                           |         |     1616|         |         |         |
--------------------------------+---------+---------+---------+---------+---------+

Nenhuma leitura desnecessária, a consulta é executada muito rapidamente. Assim, podemos concluir: sempre observe o plano de execução de subconsultas em IN/EXISTS/ANY/SOME, e verifique variantes alternativas de escrita das consultas.

Autor: D.Simonov. Artigo originalmente publicado em inglês por IBSurgeon, em 21-Aug-2024