ProHoster > Blog > Идораи > Antipatterns PostgreSQL: JOINs ва ORs зараровар

Antipatterns PostgreSQL: JOINs ва ORs зараровар

Аз амалиёте, ки буферҳоро меорад, ҳазар кунед...
Бо истифода аз дархости хурд ҳамчун мисол, биёед ба баъзе равишҳои универсалии оптимизатсияи дархостҳо дар PostgreSQL назар андозем. Новобаста аз он ки шумо онҳоро истифода мебаред ё не, ба шумо вобаста аст, аммо дар бораи онҳо донистан лозим аст.

Дар баъзе версияҳои минбаъдаи PG вазъ метавонад тағир ёбад, зеро нақшасоз оқилонатар мешавад, аммо барои 9.4/9.6 он тақрибан ҳамон тавре ба назар мерасад, ки дар мисолҳои ин ҷо.

Биёед як дархости воқеиро гирем:

SELECT
  TRUE
FROM
  "Документ" d
INNER JOIN
  "ДокументРасширение" doc_ex
    USING("@Документ")
INNER JOIN
  "ТипДокумента" t_doc ON
    t_doc."@ТипДокумента" = d."ТипДокумента"
WHERE
  (d."Лицо3" = 19091 or d."Сотрудник" = 19091) AND
  d."$Черновик" IS NULL AND
  d."Удален" IS NOT TRUE AND
  doc_ex."Состояние"[1] IS TRUE AND
  t_doc."ТипДокумента" = 'ПланРабот'
LIMIT 1;

дар бораи номҳои ҷадвал ва майдонҳоБа номхои «русй»-и майдонхо ва чадвалхо ба таври гуногун муносибат кардан мумкин аст, аммо ин масъалаи табъ аст. Зеро ки дар ин ҷо дар Тензор таҳиягарони хориҷӣ вуҷуд надоранд ва PostgreSQL ба мо имкон медиҳад, ки ҳатто дар иероглифҳо ном гузорем, агар онҳо дар иқтибос оварда шудаанд, пас мо бартарӣ медиҳем, ки объектҳоро якхела ва возеҳ ном бибарем, то ки ягон ихтилоф вуҷуд надошта бошад.
Биёед ба нақшаи натиҷавӣ назар андозем:
Antipatterns PostgreSQL: JOINs ва ORs зараровар
[нигаред дар explain.tensor.ru]

144ms ва қариб 53K буферҳо - яъне зиёда аз 400 МБ маълумот! Ва мо хушбахт мешавем, ки агар ҳамаи онҳо дар вақти дархости мо дар кэш бошанд, вагарна ҳангоми хондан аз диск чанд маротиба зиёдтар вақт мегирад.

Алгоритм аз ҳама муҳим аст!

Барои он ки ягон дархостро ба таври беҳбуд гардонед, шумо аввал бояд фаҳмед, ки он чӣ бояд кард.
Ҳоло, биёед таҳияи худи сохтори пойгоҳи додаҳоро аз доираи ин мақола берун гузорем ва розӣ шавем, ки мо метавонем нисбатан "арзон" кунем. дархостро аз нав нависед ва/ё баъзе чизҳоеро, ки ба мо лозим аст, ба пойгоҳ гузоред нишондиҳандаҳо.

Пас дархост:
— мавчуд будани акаллан ягон хуччатро тафтиш мекунад
— дар шароите, ки ба мо лозим аст ва навъи муайян
- дар куҷо муаллиф ё иҷрокунанда кормандест, ки ба мо лозим аст

Ҳамроҳ шудан + лимити 1

Аксар вақт барои таҳиякунанда навиштани дархосте осонтар аст, ки дар он шумораи зиёди ҷадвалҳо аввал пайваст карда мешаванд ва баъд аз ин маҷмӯъ танҳо як сабт боқӣ мемонад. Аммо осонтар барои таҳиякунанда маънои самараноктар барои пойгоҳи додаҳо нест.
Дар ҳолати мо ҳамагӣ 3 ҷадвал вуҷуд дошт - ва чӣ таъсир дорад...

Биёед аввал аз пайвастшавӣ бо ҷадвали "Намуди ҳуҷҷат" халос шавем ва дар айни замон ба базаи маълумот бигӯем, ки рекорди навъи мо беназир аст (мо инро медонем, аммо банақшагир ҳанӯз дарк намекунад):

WITH T AS (
  SELECT
    "@ТипДокумента"
  FROM
    "ТипДокумента"
  WHERE
    "ТипДокумента" = 'ПланРабот'
  LIMIT 1
)
...
WHERE
  d."ТипДокумента" = (TABLE T)
...

Бале, агар ҷадвал/CTE аз як майдони ягонаи сабти ягона иборат бошад, пас дар PG шумо ҳатто метавонед ба ҷои ин тавр нависед

d."ТипДокумента" = (SELECT "@ТипДокумента" FROM T LIMIT 1)

Арзёбии танбал дар дархостҳои PostgreSQL

BitmapOr против UNION

Дар баъзе мавридҳо, Bitmap Heap Scan ба мо хеле гарон хоҳад буд - масалан, дар вазъияти мо, вақте ки миқдори зиёди сабтҳо ба ҳолати зарурӣ мувофиқат мекунанд. Мо онро ба даст овардем, зеро Ё ҳолати ба BitmapOr табдил ёфт- амалиёт дар нақша.
Биёед ба масъалаи аслӣ баргардем - мо бояд сабти мувофиқро пайдо кунем касе аз шароит — яъне дар хар ду шарт чустучуи хамаи 59К сабтхо лозим нест. Роҳи кор кардани як шарт вуҷуд дорад ва ба дуюм танҳо вақте ки дар аввал чизе ёфт нашуд. Тарҳи зерин ба мо кӯмак мекунад:

(
  SELECT
    ...
  LIMIT 1
)
UNION ALL
(
  SELECT
    ...
  LIMIT 1
)
LIMIT 1

ХУДУДи "берунӣ" 1 кафолат медиҳад, ки ҷустуҷӯ ҳангоми пайдо шудани сабти аввал ба итмом мерасад. Ва агар он аллакай дар блоки якум пайдо шуда бошад, блоки дуюм иҷро намешавад (ҳеҷ гоҳ иҷро нашудааст нисбат ба).

"Пинҳон кардани шароити душвор дар CASE"

Дар дархости аслӣ як лаҳзаи бениҳоят нороҳаткунанда вуҷуд дорад - тафтиши вазъият дар ҷадвали марбут "DocumentExtension". Сарфи назар аз дурустии шартҳои дигари ифода (масалан, г. «Нест карда шудааст» ДУРУСТ НЕСТ), ин алока хамеша ичро мешавад ва ресурсхоро сарф мекунад». Бештар ё камтар аз онҳо сарф мешавад - аз андозаи ин ҷадвал вобаста аст.
Аммо шумо метавонед дархостро тавре тағир диҳед, ки ҷустуҷӯи сабти алоқаманд танҳо дар ҳолати зарурӣ сурат гирад:

SELECT
  ...
FROM
  "Документ" d
WHERE
  ... /*index cond*/ AND
  CASE
    WHEN "$Черновик" IS NULL AND "Удален" IS NOT TRUE THEN (
      SELECT
        "Состояние"[1] IS TRUE
      FROM
        "ДокументРасширение"
      WHERE
        "@Документ" = d."@Документ"
    )
  END

Боре аз ҷадвали алоқаманд ба мо барои натича ягон майдон лозим нест, пас мо имкон дорем, ки JOIN-ро ба шарти зерпурсиш табдил диҳем.
Биёед майдонҳои индексатсияшударо "берун аз қавсҳои CASE" гузорем, шартҳои оддиро аз сабт ба блоки WHEN илова кунем - ва ҳоло дархости "вазнин" танҳо ҳангоми гузаштан ба THEN иҷро карда мешавад.

Номи ман "Тотал" аст

Мо дархости натиҷаро бо тамоми механикаи дар боло тавсифшуда ҷамъ меорем:

WITH T AS (
  SELECT
    "@ТипДокумента"
  FROM
    "ТипДокумента"
  WHERE
    "ТипДокумента" = 'ПланРабот'
)
  (
    SELECT
      TRUE
    FROM
      "Документ" d
    WHERE
      ("Лицо3", "ТипДокумента") = (19091, (TABLE T)) AND
      CASE
        WHEN "$Черновик" IS NULL AND "Удален" IS NOT TRUE THEN (
          SELECT
            "Состояние"[1] IS TRUE
          FROM
            "ДокументРасширение"
          WHERE
            "@Документ" = d."@Документ"
        )
      END
    LIMIT 1
  )
UNION ALL
  (
    SELECT
      TRUE
    FROM
      "Документ" d
    WHERE
      ("ТипДокумента", "Сотрудник") = ((TABLE T), 19091) AND
      CASE
        WHEN "$Черновик" IS NULL AND "Удален" IS NOT TRUE THEN (
          SELECT
            "Состояние"[1] IS TRUE
          FROM
            "ДокументРасширение"
          WHERE
            "@Документ" = d."@Документ"
        )
      END
    LIMIT 1
  )
LIMIT 1;

Танзими [ба] индексҳо

Чашми ботаҷриба пай бурд, ки шартҳои индексатсияшуда дар зерблокҳои UNION каме фарқ мекунанд - ин дар он аст, ки мо аллакай дар ҷадвал индексҳои мувофиқ дорем. Ва агар онҳо вуҷуд надоштанд, барои эҷод кардан меарзад: Ҳуҷҷат (Шахси 3, Намуди Ҳуҷҷат) и Ҳуҷҷат (Намуди Ҳуҷҷат, Корманд).
дар бораи тартиби киштзорхо дар шароити РАВАз нуктаи назари планкаш, албатта, шумо метавонед нависад (A, B) = (constA, constB)ва (B, A) = (constB, constA). Аммо ҳангоми сабт бо тартиби майдонхои индекс, чунин дархост барои ислоҳи дертар қулайтар аст.
Дар нақша чӣ пешбинӣ шудааст?
Antipatterns PostgreSQL: JOINs ва ORs зараровар
[нигаред дар explain.tensor.ru]

Мутаассифона, бахти мо нарасид ва дар блоки якуми UNION чизе ёфт нашуд, бинобар ин, дуюмаш то ҳол иҷро карда шуд. Аммо ҳатто - танҳо 0.037ms ва 11 буфер!
Мо дархостро суръат додем ва интиқоли маълумотро дар хотира кам кардем якчанд ҳазор маротиба, бо истифода аз усулҳои хеле содда - натиҷаи хуб бо каме нусхабардорӣ. 🙂

Манбаъ: will.com

Илова Эзоҳ