एसक्यूएल प्रश्नों के लिए व्यंजनों

# 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;

DROP INDEX tbl_fk_cli_idx;
CREATE INDEX ON tbl(fk_cli, pk DESC); --   

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

जब उठता है

कैसे पहचानें?

-> 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);

# 3: इंडेक्स पूलिंग (बिटमैपऑर)

जब उठता है

कैसे पहचानें?

-> 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,    

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

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

# 4: बहुत अनावश्यक पढ़ा

जब उठता है

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

कैसे पहचानें?

-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
   && 5 × rows < RRbF --  >80% 
   && loops × RRbF > 100 --     100  

अनुशंसाएँ

यदि फ़िल्टरिंग स्थिति आपके कार्यों के लिए "स्थिर" है - अर्थात, इसमें भविष्य में मूल्यों की सूची का विस्तार शामिल नहीं है - तो 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: विरल तालिका

जब उठता है

कैसे पहचानें?

-> 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 भी हमेशा मदद नहीं कर सकता है। ऐसे मामलों के लिए, आपको डीबीए लेख से एल्गोरिदम के साथ खुद को परिचित करना चाहिए : जब 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;

CREATE INDEX ON tbl(fk_cli);

इंडेक्स के अकुशल उपयोग की स्थितियों के अन्य उदाहरण डीबीए लेख में देखे जा सकते हैं : यूज़लेस इंडेक्स खोजें ।

# 7: CTE × CTE

जब उठता है

कैसे पहचानें?

-> CTE Scan
   && loops > 10
   && loops × (rows + RRbF) > 10000
      --     CTE

अनुशंसाएँ

# 8: स्वैप टू डिस्क (टेम्प लिखित)

जब उठता है

कैसे पहचानें?

-> *
   && temp written > 0

अनुशंसाएँ

SHOW work_mem;
-- "16MB"

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

SET work_mem = '128MB'; --   

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

जब उठता है

कैसे पहचानें?

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

अनुशंसाएँ

इस स्थिति का वर्णन पोस्टग्रैसक्यूएल एंटिप्टर्न में अधिक विस्तार से किया गया है : आंकड़े पूरे सिर पर हैं ।

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

जब उठता है

कैसे पहचानें?

-> *
   && (shared hit / 8K) + (shared read / 1K) < time / 1000
      -- RAM hit = 64MB/s, HDD read = 8MB/s
   && time > 100ms --  ,   

अनुशंसाएँ