Контакти
IBSurgeon
Ова страница је машински преведена. Прочитајте енглески оригинал. English

IBSurgeon библиотека

Побољшања у оптимизатору у Firebird 5.0.1

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

Недавно је објављено ажурирање Firebird 5.0 DBMS-а Firebird 5.0.1. Поред исправљања грешака, додата је нова експериментална функција оптимизатора, о којој ће бити речи у овом чланку.

Конвертовање подупита у ANY/SOME/IN/EXISTS у полу-спој (semi-join)

Полу-спој је операција која спаја две релације, враћајући редове само из једне од релација без извршавања целог спајања. За разлику од других оператора спајања, не постоји експлицитна синтакса за одређивање да ли се извршава полу-спој. Међутим, можете извршити полу-спој користећи подупите у ANY/SOME/IN/EXISTS.

Традиционално, Firebird трансформише подупите у ANY/SOME/IN предикатима у корелисане подупите у EXISTS предикату, и извршава подупит у EXISTS за сваки запис спољашњег упита. При извршавању подупита унутар EXISTS предиката, користи се стратегија FIRST ROWS, и његово извршавање се зауставља одмах након што се врати први запис.

Почевши од Firebird 5.0.1, подупити у ANY/SOME/IN/EXISTS предикатима могу се конвертовати у полу-спојеве. Ова функција је подразумевано искључена, а може се омогућити постављањем параметра конфигурације SubQueryConversion на true у датотеци firebird.conf или database.conf.
Ова функција је експериментална, па је подразумевано искључена. Можете је омогућити и тестирати своје упите са подупитима у ANY/SOME/IN/EXISTS предикатима, и ако су перформансе боље, оставите је укљученом, у супротном вратите параметар SubQueryConversion на подразумевану вредност ( false).
Подразумевана вредност параметра конфигурације SubQueryConversion може се променити у будућности, или параметар може бити потпуно уклоњен. То ће се десити када се докаже да је нови начин извршавања оптималнији у већини случајева.

За разлику од извршавања ANY/SOME/IN/EXISTS директно на подупитима, тј. као корелисаних подупита, њихово извршавање као полу-спојева пружа више простора за оптимизацију. Полу-спојеви се могу извршавати различитим алгоритмима Hash Join (semi) или Nested Loop Join (semi), док се корелисани подупити увек извршавају за сваки запис спољашњег упита.

Хајде да покушамо да омогућимо ову функцију постављањем параметра SubQueryConversion на true у датотеци firebird.conf. Сада хајде да урадимо неколико експеримената.

Извршимо следећи упит:

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|         |         |         |
--------------------------------+---------+---------+---------+---------+---------+

У плану извршавања видимо нови метод спајања Hash Join (semi). Резултат подупита у IN је баферован, што се види у плану као Record Buffer (record length: 41). То значи да је у овом случају подупит у IN извршен једном, његов резултат је сачуван у меморији хеш табеле, а затим је спољашњи упит једноставно претраживао у овој хеш табели.

Поређења ради, покренимо исти упит са искљученом конверзијом подупита у полу-спој.

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|         |         |         |
--------------------------------+---------+---------+---------+---------+---------+

План извршавања показује да се подупит извршава за сваки запис главног упита, али користи додатни индекс FK_COVER_FATHER. То је такође видљиво у статистици извршавања: број Fetches је 4 пута већи, време извршавања је скоро 4 пута лошије.
Читалац се може запитати: зашто hash semi-join показује 5 пута више читања индекса табеле COVER, али је иначе бољи? Чињеница је да читања индекса у статистици показују број записа прочитаних помоћу индекса, не показују укупан број приступа индексу, од којих неки уопште не резултирају преузимањем записа, али ти приступи нису бесплатни.

Шта се догодило? Да бисмо боље разумели трансформацију подупита, уведимо замишљени оператор полу-споја “SEMI JOIN”. Као што сам већ рекао, ова врста спајања није представљена у SQL језику. Наш упит са IN оператором је трансформисан у еквивалентну форму, која се може написати на следећи начин:

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

Сада је јасније. Иста ствар се дешава и за подупите који користе EXISTS. Погледајмо још један пример:

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
  )

Тренутно није могуће написати такав EXISTS користећи IN. Погледајмо како је имплементиран без трансформације у полу-спој.

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|         |         |         |
--------------------------------+---------+---------+---------+---------+---------+

Veoma sporo. Sada postavimo SubQueryConversion = true i ponovo pokrenimo upit.

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|         |         |         |
--------------------------------+---------+---------+---------+---------+---------+

Upit je izvršen 100 puta brže! Ako ga prepišemo koristeći naš fiktivni SEMI JOIN operator, upit će izgledati ovako:

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

Može li se bilo koji korelirani podupit u IN/EXISTS konvertovati u semi-join? Ne, ne bilo koji, na primer, ako podupit sadrži FETCH/FIRST/SKIP/ROWS filtere, onda se podupit ne može konvertovati u semi-join i biće izvršen kao korelirani podupit. Evo primera takvog upita:

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
  )

Ovde fraza OFFSET 0 ROWS ne menja semantiku upita, a rezultat njegovog izvršenja biće isti kao i bez nje. Pogledajmo plan i statistiku ovog upita.

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

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|         |         |         |
--------------------------------+---------+---------+---------+---------+---------+

Kao što vidite, transformacija u semi-join se nije dogodila. Sada uklonimo OFFSET 0 ROWS i ponovo uzmimo statistiku.

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

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|         |         |         |
--------------------------------+---------+---------+---------+---------+---------+

Ovde se konverzija u semi-join dogodila, i kao što vidimo vreme izvršenja je postalo gore. Razlog je to što trenutno optimizer nema procenu troškova između algoritama spajanja Hash Join (semi) i Nested Loop Join (semi) koristeći indeks, pa je pravilo: ako uslov spajanja sadrži samo jednakost, onda se bira algoritam Hash Join (semi), u suprotnom se IN/EXISTS podupiti izvršavaju kao i obično.

Sada onemogućimo konverziju semi-join-a i pogledajmo statistiku izvršenja.

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

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|         |         |         |
--------------------------------+---------+---------+---------+---------+---------+

Kao što vidite, Fetches je tačno jednak slučaju kada je podupit sadržao klauzulu OFFSET 0 ROWS, a vreme izvršenja se razlikuje u granicama greške. To znači da možete koristiti klauzulu OFFSET 0 ROWS kao hint za onemogućavanje konverzije semi-join-a.

Sada pogledajmo slučajeve gde se u podupitima koristi bilo koji korelirani uslov osim jednakosti i IS NOT DISTINCT FROM.

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)

Као што сам рекао горе, није дошло до трансформације у полу-спој (semi-join), подупит се извршава за сваки запис главног упита.

Наставимо са експериментима, напишимо упит користећи једнакост и још један предикат осим једнакости.

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)

Овде у плану видимо прву употребу метода спајања Nested Loop Join (semi), али нажалост овај план је лош, јер се индекс FK_COVER_FATHER не користи. Нећете добити никакве резултате из таквог упита. Ово се може поправити коришћењем хинта 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

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|         |         |         |
--------------------------------+---------+---------+---------+---------+---------+

Није најбоље време извршавања, али у овом случају смо барем добили резултат.

Дакле, претварање подупита у ANY/SOME/IN/EXISTS у полу-спој (semi-join) у неким случајевима омогућава значајно убрзање извршавања упита, али тренутно је ова функција још увек несавршена и стога је подразумевано искључена. У Firebird 6.0, покушаће да додају процену трошкова за ову функцију, као и да исправе низ других недостатака. Поред тога, Firebird 6.0 планира да дода конверзију подупита ALL/NOT IN/NOT EXISTS у анти-спој (anti-join).

У закључку прегледа извршавања подупита у IN/EXISTS, желео бих да напоменем да ако имате упит облика

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

или

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

онда је такве упите готово увек ефикасније извршити као

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

Дајте ми јасан пример:

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

План извршавања и статистика коришћењем 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

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|         |         |         |         |
--------------------------------+---------+---------+---------+---------+---------+

Прилично брзо, али табела HORSE се чита у потпуности.

План извршавања и статистика са класичним извршавањем подупита

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

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|         |         |         |         |
--------------------------------+---------+---------+---------+---------+---------+

Веома споро. Табела HORSE је потпуно скенирана, а подупит се извршава више пута - за сваки запис у табели HORSE.

А сада брза опција са 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

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| | | | ——————————–+———+———+———+———+———+

Code 

Нема непотребних читања, упит се извршава веома брзо. Отуда закључак - увек погледајте план извршавања подупита у IN/EXISTS/ANY/SOME, и проверите алтернативне варијанте писања упита.