🥇PostgreSQL Antipatterns: хортой НЭГДСЭН болон 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 бидэнд нэрсийг иероглифээр ч өгөх боломжийг олгодог. ишлэлд хавсаргав, дараа нь бид объектуудыг хоёрдмол утгагүй, тодорхой нэрлэхийг илүүд үздэг бөгөөд ингэснээр ямар ч зөрүү гарахгүй.
Үүний үр дүнд гарсан төлөвлөгөөг харцгаая:

[express.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 vs UNION

Зарим тохиолдолд Bitmap нуруулдан скан хийх нь бидэнд маш их зардал гарах болно - жишээлбэл, бидний нөхцөл байдалд, нэлээд олон бичлэг шаардлагатай болзол хангасан үед. Учир нь бид үүнийг авсан OR нөхцөл нь BitmapOr болж хувирсан- төлөвлөгөөний дагуу үйл ажиллагаа явуулах.
Анхны асуудал руугаа буцъя - бид тохирох бичлэгийг олох хэрэгтэй аль ч нөхцлөөс - өөрөөр хэлбэл, хоёр нөхцөлд бүх 59K бичлэгийг хайх шаардлагагүй. Нэг нөхцөлийг боловсруулах арга байдаг, мөн Эхнийх нь юу ч олдоогүй үед л хоёр дахь руу оч. Дараах загвар нь бидэнд туслах болно.

(
  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 хүн, Баримт бичгийн төрөл) и Баримт бичиг(Баримт бичгийн төрөл, Ажилтан).
ROW нөхцөл дэх талбайн дарааллын талаарТөлөвлөгчийн үүднээс бол мэдээж бичиж болно (A, B) = (constA, constB)болон (B, A) = (constB, constA). Гэхдээ бичлэг хийх үед индекс дэх талбаруудын дарааллаар, ийм хүсэлтийг дараа нь дибаг хийхэд илүү тохиромжтой.
Төлөвлөгөөнд юу байгаа вэ?

[express.tensor.ru-г үзнэ үү]

Харамсалтай нь бидэнд аз таарч, эхний UNION блокоос юу ч олдоогүй тул хоёр дахь нь цаазлагдсан хэвээр байв. Гэсэн хэдий ч - зөвхөн 0.037ms ба 11 буфер!
Бид хүсэлтийг хурдасгаж, санах ойд өгөгдөл дамжуулахыг багасгасан хэдэн мянган удаа, нэлээн энгийн техникийг ашиглах - бага зэрэг хуулж буулгахад сайн үр дүн. 🙂

Эх сурвалж: www.habr.com

PostgreSQL Antipatterns: Хортой НЭГДСЭН болон OR-ууд