PostgreSQL اینٹی پیٹرنز: نقصان دہ شمولیت اور 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;

ٹیبل اور فیلڈ کے ناموں کے بارے میںکھیتوں اور میزوں کے "روسی" ناموں کو مختلف طریقے سے سمجھا جا سکتا ہے، لیکن یہ ذائقہ کا معاملہ ہے. کیونکہ یہاں Tensor پر کوئی غیر ملکی ڈویلپر نہیں ہیں، اور PostgreSQL ہمیں hieroglyphs میں بھی نام دینے کی اجازت دیتا ہے، اگر وہ حوالوں میں بند، پھر ہم اشیاء کو غیر مبہم اور واضح طور پر نام دینے کو ترجیح دیتے ہیں تاکہ کوئی تضاد نہ ہو۔
آئیے نتیجے کے منصوبے کو دیکھتے ہیں:

[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

کچھ معاملات میں، Bitmap Heap Scan ہمیں بہت زیادہ لاگت آئے گا - مثال کے طور پر، ہماری صورتحال میں، جب کافی سارے ریکارڈ مطلوبہ شرط کو پورا کرتے ہیں۔ ہمیں مل گیا کیونکہ OR حالت BitmapOr میں بدل گئی۔- منصوبہ بندی میں آپریشن۔
آئیے اصل مسئلے کی طرف لوٹتے ہیں - ہمیں اس سے متعلق ایک ریکارڈ تلاش کرنے کی ضرورت ہے۔ کسی کو بھی شرائط سے - یعنی، دونوں شرائط کے تحت تمام 59K ریکارڈ تلاش کرنے کی ضرورت نہیں ہے۔ ایک شرط پر کام کرنے کا ایک طریقہ ہے، اور دوسرے میں صرف اس وقت جائیں جب پہلے میں کچھ نہیں ملا. مندرجہ ذیل ڈیزائن ہماری مدد کرے گا:

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

"بیرونی" LIMIT 1 اس بات کو یقینی بناتا ہے کہ پہلا ریکارڈ ملنے پر تلاش ختم ہو جاتی ہے۔ اور اگر یہ پہلے ہی بلاک میں پایا جاتا ہے تو، دوسرے بلاک پر عمل نہیں کیا جائے گا (کبھی پھانسی نہیں دی گئی کے احترام میں).

"کیس کے تحت مشکل حالات کو چھپانا"

اصل استفسار میں ایک انتہائی تکلیف دہ لمحہ ہے - متعلقہ ٹیبل "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). لیکن جب ریکارڈنگ انڈیکس میں فیلڈز کی ترتیب میں، اس طرح کی درخواست بعد میں ڈیبگ کرنا زیادہ آسان ہے۔
پلان میں کیا ہے؟

[explain.tensor.ru پر دیکھیں]

بدقسمتی سے، ہم بدقسمت تھے اور پہلے UNION بلاک میں کچھ نہیں ملا، اس لیے دوسرے کو پھر بھی پھانسی دے دی گئی۔ لیکن پھر بھی - صرف 0.037ms اور 11 بفرز!
ہم نے درخواست کو تیز کر دیا ہے اور میموری میں ڈیٹا پمپنگ کو کم کر دیا ہے۔ کئی ہزار بار, کافی آسان تکنیکوں کا استعمال کرتے ہوئے - تھوڑی سی کاپی پیسٹ کے ساتھ ایک اچھا نتیجہ۔ 🙂

ماخذ: www.habr.com