PostgreSQL Antipatterns: نقصانڪار JOINs ۽ 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 ۾ به ڏيو، جيڪڏهن اهي حوالن ۾ بند ٿيل، پوءِ اسان شين کي غير واضح ۽ واضح طور تي نالا ڏيڻ کي ترجيح ڏيون ٿا ته جيئن ڪو به تضاد نه هجي.
اچو ته نتيجن واري منصوبي کي ڏسو:

[explanation.tensor.ru تي ڏسو]

144ms ۽ لڳ ڀڳ 53K بفر - يعني 400MB کان وڌيڪ ڊيٽا! ۽ اسان خوش قسمت ٿينداسين جيڪڏهن اهي سڀئي اسان جي درخواست جي وقت تائين ڪيش ۾ آهن، ٻي صورت ۾ اهو ڪيترائي ڀيرا وڌيڪ وٺندو جڏهن ڊسڪ مان پڙهي.

الورورٿم سڀ کان اهم آهي!

ڪنهن به درخواست کي بهتر ڪرڻ لاء، توهان کي پهريان سمجهڻ گهرجي ته اهو ڇا ڪرڻ گهرجي.
اچو ته ڊيٽابيس جي ڍانچي جي ترقي کي ڇڏي ڏيون هن آرٽيڪل جي دائري کان ٻاهر، ۽ متفق آهيون ته اسان نسبتا "سست" ڪري سگهون ٿا. درخواست ٻيهر لکو ۽/يا بنيادي طور تي رول ڪريو ڪجھ شيون جيڪي اسان کي گهربل آھن انڊيڪس.

تنهن ڪري درخواست:
- گهٽ ۾ گهٽ ڪجهه دستاويز جي موجودگي کي چيڪ ڪري ٿو
- حالت ۾ اسان کي ضرورت آهي ۽ هڪ خاص قسم جي
- جتي ليکڪ يا اداڪار ملازم آهي اسان کي ضرورت آهي

شامل ٿيو + حد 1

گهڻو ڪري اهو آسان آهي ته ڊولپر لاءِ هڪ سوال لکڻ جتي ٽيبلن جو هڪ وڏو تعداد پهريون ڀيرو شامل ٿئي ٿو، ۽ پوءِ هن پوري سيٽ مان صرف هڪ رڪارڊ رهي ٿو. پر ڊولپر لاءِ آسان مطلب ڊيٽابيس لاءِ وڌيڪ ڪارائتو ناهي.
اسان جي حالت ۾ صرف 3 ٽيبل هئا - ۽ ڇا اثر آهي ...

اچو ته سڀ کان پهريان "Document Type" ٽيبل سان ڪنيڪشن ختم ڪريون، ۽ ساڳئي وقت ڊيٽابيس کي ٻڌايو ته اسان جو قسم رڪارڊ منفرد آهي (اسان اهو ڄاڻون ٿا، پر شيڊولر کي اڃا تائين ڪا خبر ناهي):

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

"بيروني" 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

هڪ دفعو اسان سان ڳنڍيل ٽيبل تان نتيجن لاءِ ڪنهن به فيلڊ جي ضرورت ناهي، پوءِ اسان وٽ موقعو آھي جوائن کي ھڪڙي شرط ۾ تبديل ڪرڻ جو ذيلي سوال تي.
اچو ته انڊيڪس ٿيل فيلڊز کي ڇڏي ڏيون “CASE brackets کان ٻاهر”، سادو شرطن کي رڪارڊ مان 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، Document Type) и دستاويز (دستاويز جو قسم، ملازم).
ROW حالتن ۾ فيلڊ جي ترتيب بابتپلانر جي نقطي نظر کان، يقينا، توهان لکي سگهو ٿا (A، B) = (constA، constB)۽ (B، A) = (constB، constA). پر جڏهن رڪارڊنگ انڊيڪس ۾ شعبن جي ترتيب ۾, اهڙي درخواست صرف بعد ۾ ڊيبگ ڪرڻ لاء وڌيڪ آسان آهي.
منصوبي ۾ ڇا آهي؟

[explanation.tensor.ru تي ڏسو]

بدقسمتي سان، اسان بدقسمتي سان هئاسين ۽ پهرين يونين بلاڪ ۾ ڪجهه به نه مليو، تنهنڪري ٻيو هڪ اڃا تائين قتل ڪيو ويو. پر ان جي باوجود - صرف 0.037ms ۽ 11 بفر!
اسان درخواست کي تيز ڪيو ۽ ميموري ۾ ڊيٽا پمپنگ کي گھٽايو ڪيترائي هزار ڀيرا, بلڪل سادو ٽيڪنڪ استعمال ڪندي - ٿوري ڪاپي پيسٽ سان سٺو نتيجو. 🙂

جو ذريعو: www.habr.com