बीमार एसक्यूएल प्रश्नों के लिए व्यंजन विधि

महीने पहले हमने घोषणा की व्याख्या.टेंसर.आरयू - जनता क्वेरी योजनाओं को पार्स करने और विज़ुअलाइज़ करने के लिए सेवा पोस्टग्रेएसक्यूएल के लिए।

आप तब से अब तक 6000 से अधिक बार इसका उपयोग कर चुके हैं, लेकिन एक उपयोगी सुविधा पर किसी का ध्यान नहीं गया है संरचनात्मक सुराग, जो कुछ इस तरह दिखते हैं:

उन्हें सुनें और आपके अनुरोध "रेशमी चिकनी हो जाएंगे"। 🙂

लेकिन गंभीरता से, कई स्थितियां जो संसाधनों के मामले में अनुरोध को धीमा और "भक्षक" बनाती हैं, विशिष्ट हैं और योजना की संरचना और डेटा द्वारा पहचाने जा सकते हैं.

इस मामले में, प्रत्येक व्यक्तिगत डेवलपर को अपने स्वयं के अनुभव पर भरोसा करते हुए, अपने दम पर एक अनुकूलन विकल्प की तलाश नहीं करनी होगी - हम उसे बता सकते हैं कि यहाँ क्या हो रहा है, क्या कारण हो सकता है, और कैसे समाधान निकाला जाए. जो हमने किया।

आइए इन मामलों पर करीब से नज़र डालें - उन्हें कैसे परिभाषित किया जाता है और वे किन सिफारिशों की ओर ले जाते हैं।

विषय में बेहतर तल्लीनता के लिए, आप पहले संबंधित ब्लॉक को सुन सकते हैं PGConf.Russia 2020 पर मेरी रिपोर्ट, और उसके बाद ही प्रत्येक उदाहरण के विस्तृत विश्लेषण पर जाएँ:

#1: इंडेक्स "अंडरसॉर्टिंग"

कब करता है

क्लाइंट "एलएलसी कोलोकोलिक" के लिए अंतिम चालान दिखाएं।

कैसे पहचानें

-> Limit
   -> Sort
      -> Index [Only] Scan [Backward] | Bitmap Heap Scan

अनुशंसाएँ

इंडेक्स का इस्तेमाल किया सॉर्ट फ़ील्ड के साथ विस्तृत करें.

उदाहरण:

CREATE TABLE tbl AS
SELECT
  generate_series(1, 100000) pk  -- 100K "фактов"
, (random() * 1000)::integer fk_cli; -- 1K разных внешних ключей

CREATE INDEX ON tbl(fk_cli); -- индекс для foreign key

SELECT
  *
FROM
  tbl
WHERE
  fk_cli = 1 -- отбор по конкретной связи
ORDER BY
  pk DESC -- хотим всего одну "последнюю" запись
LIMIT 1;

[देखें व्याख्या.टेंसर.आरयू]

आप तुरंत नोटिस कर सकते हैं कि इंडेक्स द्वारा 100 से अधिक रिकॉर्ड घटाए गए थे, जो तब सभी सॉर्ट किए गए थे, और फिर केवल एक ही बचा था।

हम तय करते हैं:

DROP INDEX tbl_fk_cli_idx;
CREATE INDEX ON tbl(fk_cli, pk DESC); -- добавили ключ сортировки

[देखें व्याख्या.टेंसर.आरयू]

ऐसे आदिम नमूने पर भी - 8.5x तेज और 33x कम पढ़ता है. प्रभाव स्पष्ट होगा, आपके पास प्रत्येक मूल्य के लिए अधिक "तथ्य" होंगे। fk.

मैं ध्यान देता हूं कि ऐसा सूचकांक "उपसर्ग" सूचकांक के रूप में काम करेगा, जो अन्य प्रश्नों के लिए पिछले एक से भी बदतर नहीं है fk, जहां द्वारा क्रमबद्ध किया जा रहा है pk नहीं था और नहीं है (आप इस बारे में और अधिक पढ़ सकते हैं मेरे लेख में अक्षम अनुक्रमणिका खोजने के बारे में). विशेष रूप से, यह सामान्य प्रदान करेगा स्पष्ट विदेशी कुंजी समर्थन इस क्षेत्र द्वारा।

# 2: इंडेक्स चौराहा (बिटमैप और)

कब करता है

"NJSC Lyutik" की ओर से संपन्न क्लाइंट "LLC Kolocolchik" के लिए सभी अनुबंध दिखाएं।

कैसे पहचानें

-> BitmapAnd
   -> Bitmap Index Scan
   -> Bitmap Index Scan

अनुशंसाएँ

बनाएँ संयुक्त सूचकांक दोनों स्रोतों से फ़ील्ड द्वारा या दूसरे से मौजूदा फ़ील्ड में से एक का विस्तार करें।

उदाहरण:

CREATE TABLE tbl AS
SELECT
  generate_series(1, 100000) pk      -- 100K "фактов"
, (random() *  100)::integer fk_org  -- 100 разных внешних ключей
, (random() * 1000)::integer fk_cli; -- 1K разных внешних ключей

CREATE INDEX ON tbl(fk_org); -- индекс для foreign key
CREATE INDEX ON tbl(fk_cli); -- индекс для foreign key

SELECT
  *
FROM
  tbl
WHERE
  (fk_org, fk_cli) = (1, 999); -- отбор по конкретной паре

[देखें व्याख्या.टेंसर.आरयू]

हम तय करते हैं:

DROP INDEX tbl_fk_org_idx;
CREATE INDEX ON tbl(fk_org, fk_cli);

[देखें व्याख्या.टेंसर.आरयू]

यहाँ लाभ छोटा है, क्योंकि बिटमैप हीप स्कैन अपने आप में काफी प्रभावी है। लेकिन वैसे भी 7x तेज और 2.5x कम पढ़ता है.

#3: इंडेक्स का संयोजन (बिटमैपऑर)

कब करता है

प्राथमिकता में स्वयं के साथ पहले 20 सबसे पुराने "स्वयं" या प्रसंस्करण के लिए असाइन नहीं किए गए अनुरोध दिखाएं।

कैसे पहचानें

-> BitmapOr
   -> Bitmap Index Scan
   -> Bitmap Index Scan

अनुशंसाएँ

उपयोग करने के लिए संघ [सभी] प्रत्येक स्थिति या ब्लॉक के लिए उपश्रेणियों को संयोजित करने के लिए।

उदाहरण:

CREATE TABLE tbl AS
SELECT
  generate_series(1, 100000) pk  -- 100K "фактов"
, CASE
    WHEN random() < 1::real/16 THEN NULL -- с вероятностью 1:16 запись "ничья"
    ELSE (random() * 100)::integer -- 100 разных внешних ключей
  END fk_own;

CREATE INDEX ON tbl(fk_own, pk); -- индекс с "вроде как подходящей" сортировкой

SELECT
  *
FROM
  tbl
WHERE
  fk_own = 1 OR -- свои
  fk_own IS NULL -- ... или "ничьи"
ORDER BY
  pk
, (fk_own = 1) DESC -- сначала "свои"
LIMIT 20;

[देखें व्याख्या.टेंसर.आरयू]

हम तय करते हैं:

(
  SELECT
    *
  FROM
    tbl
  WHERE
    fk_own = 1 -- сначала "свои" 20
  ORDER BY
    pk
  LIMIT 20
)
UNION ALL
(
  SELECT
    *
  FROM
    tbl
  WHERE
    fk_own IS NULL -- потом "ничьи" 20
  ORDER BY
    pk
  LIMIT 20
)
LIMIT 20; -- но всего - 20, больше и не надо

[देखें व्याख्या.टेंसर.आरयू]

हमने इस तथ्य का लाभ उठाया कि पहले ब्लॉक में सभी 20 आवश्यक रिकॉर्ड तुरंत प्राप्त किए गए थे, इसलिए दूसरा, अधिक "महंगे" बिटमैप हीप स्कैन के साथ, निष्पादित भी नहीं किया गया - परिणामस्वरूप 22x तेज, 44x कम पढ़ता है!

इस अनुकूलन पद्धति के बारे में अधिक विस्तृत कहानी विशिष्ट उदाहरणों का उपयोग करना लेखों में पढ़ा जा सकता है पोस्टग्रेएसक्यूएल एंटीपैटर्न: हानिकारक जॉइन और ओआरएस и PostgreSQL Antipatterns: नाम से खोज के पुनरावृत्त शोधन की कहानी, या "आगे और पीछे का अनुकूलन".

सामान्यीकृत संस्करण कई चाबियों द्वारा चयन का आदेश दिया (और न केवल const / NULL की एक जोड़ी के लिए) लेख में चर्चा की गई है एसक्यूएल हाउटो: क्वेरी में सीधे थोड़ी देर लूप लिखें, या "प्राथमिक तीन-तरफा".

#4: हम बहुत ज्यादा पढ़ते हैं

कब करता है

एक नियम के रूप में, यह तब होता है जब आप किसी मौजूदा अनुरोध में "दूसरा फ़िल्टर संलग्न करना" चाहते हैं।

"और आपके पास समान नहीं है, लेकिन मोती बटन के साथ? " फिल्म "डायमंड हैंड"

उदाहरण के लिए, उपरोक्त कार्य को संशोधित करते हुए, प्रसंस्करण के लिए पहले 20 सबसे पुराने "महत्वपूर्ण" अनुरोधों को दिखाएं, भले ही उनका उद्देश्य कुछ भी हो।

कैसे पहचानें

-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
   && 5 × rows < RRbF -- отфильтровано >80% прочитанного
   && loops × RRbF > 100 -- и при этом больше 100 записей суммарно

अनुशंसाएँ

[अधिक] विशिष्ट बनाएँ WHERE क्लॉज के साथ इंडेक्स या इंडेक्स में अतिरिक्त फ़ील्ड शामिल करें।

यदि फ़िल्टरिंग स्थिति आपके कार्यों के लिए "स्थिर" है - अर्थात विस्तार शामिल नहीं है भविष्य में मूल्यों की सूची - WHERE इंडेक्स का उपयोग करना बेहतर है। इस श्रेणी में विभिन्न बूलियन/एनम स्थितियां अच्छी तरह से फिट बैठती हैं।

अगर छानने की स्थिति अलग-अलग मान ले सकते हैं, इन क्षेत्रों के साथ सूचकांक का विस्तार करना बेहतर है - जैसा कि बिटमैप और ऊपर की स्थिति में है।

उदाहरण:

CREATE TABLE tbl AS
SELECT
  generate_series(1, 100000) pk -- 100K "фактов"
, CASE
    WHEN random() < 1::real/16 THEN NULL
    ELSE (random() * 100)::integer -- 100 разных внешних ключей
  END fk_own
, (random() < 1::real/50) critical; -- 1:50, что заявка "критичная"

CREATE INDEX ON tbl(pk);
CREATE INDEX ON tbl(fk_own, pk);

SELECT
  *
FROM
  tbl
WHERE
  critical
ORDER BY
  pk
LIMIT 20;

[देखें व्याख्या.टेंसर.आरयू]

हम तय करते हैं:

CREATE INDEX ON tbl(pk)
  WHERE critical; -- добавили "статичное" условие фильтрации

[देखें व्याख्या.टेंसर.आरयू]

जैसा कि आप देख सकते हैं, योजना से फ़िल्टरिंग पूरी तरह से चली गई है, और अनुरोध बन गया है 5 गुना तेज.

# 5: विरल तालिका

कब करता है

अपने स्वयं के कार्य प्रसंस्करण कतार बनाने के विभिन्न प्रयास, जब तालिका में बड़ी संख्या में अद्यतन / विलोपन रिकॉर्ड बड़ी संख्या में "मृत" रिकॉर्ड की स्थिति का कारण बनते हैं।

कैसे पहचानें

-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
   && loops × (rows + RRbF) < (shared hit + shared read) × 8
      -- прочитано больше 1KB на каждую запись
   && shared hit + shared read > 64

अनुशंसाएँ

मैन्युअल रूप से नियमित रूप से करें वैक्यूम [पूर्ण] या पर्याप्त रूप से लगातार प्रसंस्करण प्राप्त करें ऑटोवैक्यूम सहित इसके मापदंडों को ठीक करके एक विशिष्ट तालिका के लिए.

ज्यादातर मामलों में, ऐसी समस्याएं व्यावसायिक तर्क से बुलाए जाने पर खराब क्वेरी लेआउट के कारण होती हैं, जैसे कि जिन पर चर्चा की गई है PostgreSQL Antipatterns: "मृत" की भीड़ से लड़ना.

लेकिन हमें यह समझना चाहिए कि VACUUM FULL हमेशा मदद नहीं कर सकता। ऐसे मामलों के लिए, आपको लेख से एल्गोरिथम से परिचित होना चाहिए। DBA: जब VACUUM पास हो जाता है, तो हम टेबल को मैन्युअल रूप से साफ करते हैं.

#6: सूचकांक के "मध्य" से पढ़ना

कब करता है

ऐसा लगता है कि उन्होंने थोड़ा पढ़ा, और सब कुछ अनुक्रमित किया गया था, और उन्होंने किसी को भी अतिरिक्त फ़िल्टर नहीं किया - लेकिन फिर भी, हम जितने पृष्ठ चाहेंगे, उससे कहीं अधिक पृष्ठ पढ़े गए।

कैसे पहचानें

-> Index [Only] Scan [Backward]
   && loops × (rows + RRbF) < (shared hit + shared read) × 8
      -- прочитано больше 1KB на каждую запись
   && shared hit + shared read > 64

अनुशंसाएँ

उपयोग किए गए सूचकांक की संरचना और क्वेरी में निर्दिष्ट प्रमुख क्षेत्रों पर करीब से नज़र डालें - सबसे अधिक संभावना है, अनुक्रमणिका भाग सेट नहीं है. सबसे अधिक संभावना है कि आपको एक समान अनुक्रमणिका बनाने की आवश्यकता होगी, लेकिन उपसर्ग फ़ील्ड के बिना, या उनके मूल्यों को पुनरावृत्त करना सीखें.

उदाहरण:

CREATE TABLE tbl AS
SELECT
  generate_series(1, 100000) pk      -- 100K "фактов"
, (random() *  100)::integer fk_org  -- 100 разных внешних ключей
, (random() * 1000)::integer fk_cli; -- 1K разных внешних ключей

CREATE INDEX ON tbl(fk_org, fk_cli); -- все почти как в #2
-- только вот отдельный индекс по fk_cli мы уже посчитали лишним и удалили

SELECT
  *
FROM
  tbl
WHERE
  fk_cli = 999 -- а fk_org не задано, хотя стоит в индексе раньше
LIMIT 20;

[देखें व्याख्या.टेंसर.आरयू]

सूचकांक के संदर्भ में भी सब कुछ ठीक लगता है, लेकिन किसी तरह संदेहास्पद - ​​प्रत्येक 20 रिकॉर्ड पढ़ने के लिए, डेटा के 4 पृष्ठ घटाए जाने थे, 32KB प्रति रिकॉर्ड - क्या यह बोल्ड नहीं है? हां और इंडेक्स नाम tbl_fk_org_fk_cli_idx विचार की ओर ले जाता है।

हम तय करते हैं:

CREATE INDEX ON tbl(fk_cli);

[देखें व्याख्या.टेंसर.आरयू]

अचानक - पढ़ने में 10 गुना तेज और 4 गुना कम!

इंडेक्स के अक्षम उपयोग के अधिक उदाहरणों के लिए, आलेख देखें डीबीए: बेकार इंडेक्स खोजें.

#7: सीटीई × सीटीई

कब करता है

अनुरोध में "वसा" सीटीई स्कोर किया अलग-अलग तालिकाओं से, और फिर उनके बीच करने का फैसला किया JOIN.

मामला v12 से नीचे के संस्करणों या अनुरोधों के लिए प्रासंगिक है WITH MATERIALIZED.

कैसे पहचानें

-> CTE Scan
   && loops > 10
   && loops × (rows + RRbF) > 10000
      -- слишком большое декартово произведение CTE

अनुशंसाएँ

अनुरोध का सावधानीपूर्वक विश्लेषण करें क्या यहां सीटीई की बिल्कुल जरूरत है? यदि हां, तो hstore/json में "शब्दकोश" लागू करें में वर्णित मॉडल के अनुसार PostgreSQL एंटीपैटर्न: डिक्शनरी हिट हैवी जॉइन.

# 8: डिस्क पर स्वैप करें (अस्थायी रूप से लिखा गया)

कब करता है

बड़ी संख्या में रिकॉर्ड की एक बार की प्रोसेसिंग (सॉर्टिंग या यूनिकाइजेशन) इसके लिए आवंटित मेमोरी में फिट नहीं होती है।

कैसे पहचानें

-> *
   && temp written > 0

अनुशंसाएँ

यदि ऑपरेशन द्वारा उपयोग की जाने वाली मेमोरी की मात्रा पैरामीटर के सेट मान से बहुत अधिक नहीं है काम_मेम, इसे ठीक किया जाए। आप तुरंत सभी के लिए कॉन्फिगर कर सकते हैं, या आप कर सकते हैं SET [LOCAL] एक विशिष्ट अनुरोध/लेनदेन के लिए।

उदाहरण:

SHOW work_mem;
-- "16MB"

SELECT
  random()
FROM
  generate_series(1, 1000000)
ORDER BY
  1;

[देखें व्याख्या.टेंसर.आरयू]

हम तय करते हैं:

SET work_mem = '128MB'; -- перед выполнением запроса

[देखें व्याख्या.टेंसर.आरयू]

स्पष्ट कारणों के लिए, यदि केवल मेमोरी का उपयोग किया जाता है और डिस्क का नहीं, तो क्वेरी बहुत तेज होगी। वहीं, लोड का हिस्सा एचडीडी से भी हटा दिया जाता है।

लेकिन आपको यह समझने की जरूरत है कि बहुत सारी मेमोरी आवंटित करने से हमेशा काम नहीं चलेगा - यह बस सभी के लिए पर्याप्त नहीं होगा।

#9: अप्रासंगिक आँकड़े

कब करता है

बेस में एक बार में बहुत कुछ डाला गया था, लेकिन उनके पास इसे चलाने का समय नहीं था ANALYZE.

कैसे पहचानें

-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
   && ratio >> 10

अनुशंसाएँ

ही खर्च करें ANALYZE.

इस स्थिति को में और अधिक विस्तार से वर्णित किया गया है PostgreSQL Antipatterns: आँकड़े हर चीज के प्रमुख हैं.

#10: "कुछ गलत हो गया"

कब करता है

प्रतिस्पर्धी अनुरोध के लिए एक लॉक प्रतीक्षा कर रहा था, या पर्याप्त CPU/हाइपरवाइज़र हार्डवेयर संसाधन नहीं थे।

कैसे पहचानें

-> *
   && (shared hit / 8K) + (shared read / 1K) < time / 1000
      -- RAM hit = 64MB/s, HDD read = 8MB/s
   && time > 100ms -- читали мало, но слишком долго

अनुशंसाएँ

एक बाहरी प्रयोग करें निगरानी प्रणाली अवरुद्ध या असामान्य संसाधन खपत के लिए सर्वर। हम सैकड़ों सर्वरों के लिए इस प्रक्रिया को आयोजित करने के अपने संस्करण के बारे में पहले ही बात कर चुके हैं। यहां и यहां.

स्रोत: www.habr.com