PostgreSQL ანტიპატერნები: მავნე შეერთებები და 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 საშუალებას გვაძლევს მივცეთ სახელები თუნდაც იეროგლიფებით, თუ ისინი ბრჭყალებში ჩასმული, მაშინ ჩვენ გვირჩევნია დავასახელოთ ობიექტები ცალსახად და ნათლად, რათა არ იყოს შეუსაბამობები.
მოდით შევხედოთ შედეგად გეგმას:

[ნახეთ description.tensor.ru]

144 ms და თითქმის 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 Heap Scan ძვირი დაგვიჯდება – მაგალითად, ჩვენს სიტუაციაში, როდესაც საკმაოდ ბევრი ჩანაწერი აკმაყოფილებს საჭირო პირობას. ჩვენ მივიღეთ იმიტომ OR მდგომარეობა გადაიქცა BitmapOr-ად- ოპერაცია გეგმაში.
დავუბრუნდეთ საწყის პრობლემას - ჩვენ უნდა ვიპოვოთ შესაბამისი ჩანაწერი ნებისმიერი პირობებიდან - ანუ, არ არის საჭირო ყველა 59K ჩანაწერის ძებნა ორივე პირობით. არსებობს ერთი პირობის დამუშავების გზა და გადადით მეორეზე მხოლოდ მაშინ, როცა პირველში არაფერი იპოვეს. შემდეგი დიზაინი დაგვეხმარება:

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

„გარე“ LIMIT 1 უზრუნველყოფს ძიების დასრულებას პირველი ჩანაწერის აღმოჩენისთანავე. და თუ ის უკვე ნაპოვნია პირველ ბლოკში, მეორე ბლოკი არ შესრულდება (არასოდეს შესრულებული მიმართებაში).

"რთული პირობების დამალვა საქმის პირობებში"

თავდაპირველ შეკითხვაში არის უკიდურესად არასასიამოვნო მომენტი - სტატუსის შემოწმება შესაბამის ცხრილთან "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 ქვებლოკებში ოდნავ განსხვავებულია - ეს იმიტომ, რომ ჩვენ უკვე გვაქვს შესაფერისი ინდექსები მაგიდაზე. და თუ ისინი არ არსებობდნენ, ღირდა შექმნა: დოკუმენტი (Person3, DocumentType) и დოკუმენტი (დოკუმენტის ტიპი, თანამშრომელი).
ველების თანმიმდევრობის შესახებ ROW პირობებშიდამგეგმავის თვალსაზრისით, რა თქმა უნდა, შეგიძლიათ დაწეროთ (A, B) = (constA, constB)და (B, A) = (constB, constA). მაგრამ ჩაწერისას ინდექსის ველების თანმიმდევრობით, ასეთი მოთხოვნა უბრალოდ უფრო მოსახერხებელია მოგვიანებით გამართვისთვის.
რა არის გეგმაში?

[ნახეთ description.tensor.ru]

სამწუხაროდ, ჩვენ გაგვიმართლა და პირველ UNION ბლოკში ვერაფერი აღმოაჩინეს, ამიტომ მეორე მაინც შესრულდა. მაგრამ ასეც კი - მხოლოდ 0.037ms და 11 ბუფერი!
ჩვენ დავაჩქარეთ მოთხოვნა და შევამცირეთ მონაცემთა გადატუმბვა მეხსიერებაში რამდენიმე ათასჯერ, საკმაოდ მარტივი ტექნიკის გამოყენებით - კარგი შედეგი მცირე კოპირ-პასტით. 🙂

წყარო: www.habr.com