পোস্টগ্রেএসকিউএল অ্যান্টিপ্যাটার্নস: ক্ষতিকারক যোগদান এবং ওআর

বাফার নিয়ে আসে এমন অপারেশন থেকে সতর্ক থাকুন...
একটি উদাহরণ হিসাবে একটি ছোট ক্যোয়ারী ব্যবহার করে, আসুন 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 আমাদেরকে হায়ারোগ্লিফের মধ্যেও নাম দেওয়ার অনুমতি দেয়, যদি তারা উদ্ধৃতিতে আবদ্ধ, তারপর আমরা দ্ব্যর্থহীনভাবে এবং স্পষ্টভাবে বস্তুর নাম দিতে পছন্দ করি যাতে কোনো অমিল না থাকে।
চলুন ফলাফল পরিকল্পনা তাকান:

[explain.tensor.ru দেখুন]

144ms এবং প্রায় 53K বাফার - অর্থাৎ 400MB এর বেশি ডাটা! এবং আমরা ভাগ্যবান হব যদি সেগুলি আমাদের অনুরোধের সময়ের মধ্যে ক্যাশে থাকে, অন্যথায় ডিস্ক থেকে পড়ার সময় অনেক বেশি সময় লাগবে।

অ্যালগরিদম সবচেয়ে গুরুত্বপূর্ণ!

কোনোভাবে কোনো অনুরোধ অপ্টিমাইজ করার জন্য, আপনাকে প্রথমে বুঝতে হবে এটি কী করা উচিত।
এখন এই নিবন্ধের সুযোগের বাইরে ডাটাবেস কাঠামোর বিকাশকে ছেড়ে দেওয়া যাক এবং সম্মত হই যে আমরা তুলনামূলকভাবে "সস্তা" করতে পারি অনুরোধটি পুনরায় লিখুন এবং/অথবা বেস সম্মুখের কিছু জিনিস আমাদের প্রয়োজন রোল সূচকগুলি.

তাই অনুরোধ:
- অন্তত কিছু নথির অস্তিত্ব পরীক্ষা করে
- আমাদের প্রয়োজন অবস্থায় এবং একটি নির্দিষ্ট ধরনের
- যেখানে লেখক বা অভিনয়কারী আমাদের প্রয়োজন এমন কর্মচারী

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

কিছু ক্ষেত্রে, বিটম্যাপ হিপ স্ক্যান আমাদের অনেক খরচ করবে - উদাহরণস্বরূপ, আমাদের পরিস্থিতিতে, যখন অনেক রেকর্ড প্রয়োজনীয় শর্ত পূরণ করে। আমরা এটা পেয়েছি কারণ বা শর্ত BitmapOr এ পরিণত হয়েছে- পরিকল্পনায় অপারেশন।
আসুন মূল সমস্যায় ফিরে আসি - আমাদের সংশ্লিষ্ট একটি রেকর্ড খুঁজে বের করতে হবে যে কারও কাছে শর্ত থেকে - অর্থাৎ, উভয় অবস্থার অধীনে সমস্ত 59K রেকর্ড অনুসন্ধান করার প্রয়োজন নেই। একটি শর্ত কাজ করার একটি উপায় আছে, এবং প্রথমটিতে যখন কিছুই পাওয়া যায়নি তখনই দ্বিতীয়টিতে যান. নিম্নলিখিত নকশা আমাদের সাহায্য করবে:

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

"বাহ্যিক" সীমা 1 নিশ্চিত করে যে প্রথম রেকর্ডটি পাওয়া গেলে অনুসন্ধান শেষ হয়৷ এবং যদি এটি ইতিমধ্যে প্রথম ব্লকে পাওয়া যায় তবে দ্বিতীয় ব্লকটি কার্যকর করা হবে না (মৃত্যুদন্ড কার্যকর করা হয়নি এর ক্ষেত্রে)।

"কেসের অধীনে কঠিন পরিস্থিতি লুকিয়ে রাখা"

মূল ক্যোয়ারীতে একটি অত্যন্ত অসুবিধাজনক মুহূর্ত রয়েছে - সম্পর্কিত সারণী "ডকুমেন্ট এক্সটেনশন" এর বিরুদ্ধে স্থিতি পরীক্ষা করা। অভিব্যক্তিতে অন্যান্য অবস্থার সত্যতা নির্বিশেষে (উদাহরণস্বরূপ, 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). কিন্তু রেকর্ডিং করার সময় সূচকের ক্ষেত্রের ক্রম অনুসারে, এই ধরনের একটি অনুরোধ পরে ডিবাগ করার জন্য আরও সুবিধাজনক।
কি আছে পরিকল্পনায়?

[explain.tensor.ru দেখুন]

দুর্ভাগ্যবশত, আমরা দুর্ভাগ্যবশত ছিলাম এবং প্রথম UNION ব্লকে কিছুই পাওয়া যায়নি, তাই দ্বিতীয়টি এখনও কার্যকর করা হয়েছিল। কিন্তু তবুও - শুধুমাত্র 0.037ms এবং 11 বাফার!
আমরা অনুরোধের গতি বাড়িয়েছি এবং মেমরিতে ডেটা পাম্পিং কমিয়েছি কয়েক হাজার বার, মোটামুটি সহজ কৌশল ব্যবহার করে - একটি সামান্য কপি-পেস্ট সঙ্গে একটি ভাল ফলাফল. 🙂

উত্স: www.habr.com