PostgreSQL антипаттерндері: зиянды JOIN және OR

Буферлерді әкелетін операциялардан сақ болыңыз...
Мысал ретінде шағын сұрауды пайдалана отырып, 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 бізге тіпті иероглифтерде де атау беруге мүмкіндік береді, егер олар болса тырнақшаға алынған, онда сәйкессіздіктер болмас үшін объектілерді бір мәнді және анық атағанды ​​жөн көреміз.
Алынған жоспарды қарастырайық:

[express.tensor.ru сайтынан қарау]

144 мс және 53 мыңға жуық буфер - яғни 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 түріне айналды- жоспар бойынша жұмыс істеу.
Бастапқы мәселеге оралайық - сәйкес жазбаны табу керек кез келген шарттардан - яғни екі жағдайда да барлық 59K жазбаларды іздеудің қажеті жоқ. Бір шартты шешудің жолы бар, және біріншіден ештеңе табылмаған кезде ғана екіншісіне өтіңіз. Келесі дизайн бізге көмектеседі:

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

«Сыртқы» ШЕК 1 бірінші жазба табылған кезде іздеудің аяқталуын қамтамасыз етеді. Ал егер ол бірінші блокта табылса, екінші блок орындалмайды (ешқашан орындалмаған жоспарда).

«CASE бойынша қиын жағдайларды жасыру»

Түпнұсқа сұрауда өте ыңғайсыз сәт бар - «DocumentExtension» сәйкес кестеге қатысты күйді тексеру. Өрнектегі басқа шарттардың ақиқаттығына қарамастан (мысалы, d.«Жойылған» ДҰРЫС ЕМЕС), бұл байланыс әрқашан орындалады және «ресурстарды жұмсайды». Олардың көп немесе аз жұмсалады - осы кестенің өлшеміне байланысты.
Бірақ сіз сұрауды өзгертуге болады, осылайша сәйкес жазбаны іздеу шынымен қажет болғанда ғана орындалады:

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 адам, құжат түрі) и Құжат(құжат түрі, қызметкер).
ROW жағдайындағы өрістердің реті туралыЖоспарлаушының көзқарасы бойынша, әрине, жазуға болады (A, B) = (constA, constB)мен (B, A) = (constB, constA). Бірақ жазу кезінде индекстегі өрістердің реті бойынша, мұндай сұрауды кейінірек жөндеуге ыңғайлырақ.
Жоспарда не бар?

[express.tensor.ru сайтынан қарау]

Өкінішке орай, біздің жолымыз болмады және бірінші UNION блогында ештеңе табылмады, сондықтан екіншісі әлі де орындалды. Бірақ сонда да - тек 0.037 мс және 11 буфер!
Біз сұрауды жылдамдаттық және деректерді жадқа айдауды азайттық бірнеше мың рет, жеткілікті қарапайым әдістерді қолдану - аздап көшіріп қою арқылы жақсы нәтиже. 🙂

Ақпарат көзі: www.habr.com