وصفات لاستعلامات SQL المتعثرة

# 1: فهرس "underorting"

عندما ينشأ

كيفية التعرف

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

عندما ينشأ

كيفية التعرف

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

عندما ينشأ

كيفية التعرف

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

يمكن العثور على قصة أكثر تفصيلاً حول طريقة التحسين هذه باستخدام أمثلة محددة في مقالات PostgreSQL Antipatterns: JOIN و OR الضارة و PostgreSQL Antipatterns: حكاية حول التحسين التكراري للبحث بالاسم ، أو "التحسين هناك والعودة" .

يتم النظر في نسخة معممة من التحديد المرتبط بعدة مفاتيح (وليس فقط من خلال كون / كونت / NULL) في مقالة SQL HowTo: نكتب حلقة متكررة مباشرة في الاستعلام ، أو "Elementary three-way" .

# 4: قراءة الكثير من غير الضروري

عندما ينشأ

"وليس لديك نفس الشيء ، ولكن بأزرار اللؤلؤ ؟" فيلم "يد الماس"

كيفية التعرف

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

التوصيات

إذا كان شرط الفلتر "ثابتًا" لمهامك - أي أنه لا يتضمن توسيع قائمة القيم في المستقبل - فمن الأفضل استخدام فهرس WHERE. تتناسب الحالات المنطقية / التعدادية المختلفة بشكل جيد مع هذه الفئة.

إذا كان شرط المرشح يمكن أن يأخذ قيمًا مختلفة ، فمن الأفضل توسيع الفهرس بهذه الحقول - كما هو الحال مع BitmapAnd أعلاه.

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 قد لا يساعد دائمًا. في مثل هذه الحالات ، يجب أن تتعرف على الخوارزمية من مقالة 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;

CREATE INDEX ON tbl(fk_cli);

يمكن الاطلاع على أمثلة أخرى لحالات الاستخدام غير الفعال للفهارس في مقالة DBA: Find Useless Indexes .

رقم 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

التوصيات

يتم وصف هذا الموقف بمزيد من التفاصيل في PostgreSQL Antipatterns: الإحصائيات في كل مكان .

رقم 10: "حدث خطأ ما"

عندما ينشأ

كيفية التعرف

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

التوصيات