ಸಿಕ್ SQL ಪ್ರಶ್ನೆಗಳಿಗೆ ಪಾಕವಿಧಾನಗಳು

ಹಲವಾರು ತಿಂಗಳ ಹಿಂದೆ ನಾವು ಘೋಷಿಸಿದ್ದೇವೆ ವಿವರಿಸಿ.tensor.ru - ಸಾರ್ವಜನಿಕ ಪ್ರಶ್ನೆ ಯೋಜನೆಗಳನ್ನು ಪಾರ್ಸಿಂಗ್ ಮತ್ತು ದೃಶ್ಯೀಕರಿಸುವ ಸೇವೆ PostgreSQL ಗೆ.

ನೀವು ಈಗಾಗಲೇ ಇದನ್ನು 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;

[explain.tensor.ru ನೋಡಿ]

ಸೂಚ್ಯಂಕದಿಂದ 100 ಕ್ಕೂ ಹೆಚ್ಚು ದಾಖಲೆಗಳನ್ನು ಕಳೆಯಲಾಗಿದೆ ಎಂದು ನೀವು ತಕ್ಷಣ ಗಮನಿಸಬಹುದು, ನಂತರ ಎಲ್ಲವನ್ನೂ ವಿಂಗಡಿಸಲಾಗಿದೆ ಮತ್ತು ನಂತರ ಒಂದೇ ಒಂದು ಉಳಿದಿದೆ.

ಸರಿಪಡಿಸಲಾಗುತ್ತಿದೆ:

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

[explain.tensor.ru ನೋಡಿ]

ಅಂತಹ ಪ್ರಾಚೀನ ಮಾದರಿಯಲ್ಲಿಯೂ ಸಹ - 8.5 ಪಟ್ಟು ವೇಗವಾಗಿ ಮತ್ತು 33 ಪಟ್ಟು ಕಡಿಮೆ ಓದುತ್ತದೆ. ಪ್ರತಿ ಮೌಲ್ಯಕ್ಕೆ ನೀವು ಹೆಚ್ಚು "ಸತ್ಯಗಳು" ಹೊಂದಿದ್ದೀರಿ, ಪರಿಣಾಮವು ಹೆಚ್ಚು ಸ್ಪಷ್ಟವಾಗಿರುತ್ತದೆ fk.

ಅಂತಹ ಸೂಚ್ಯಂಕವು "ಪೂರ್ವಪ್ರತ್ಯಯ" ಸೂಚ್ಯಂಕವಾಗಿ ಇತರ ಪ್ರಶ್ನೆಗಳಿಗೆ ಮೊದಲಿಗಿಂತ ಕೆಟ್ಟದಾಗಿ ಕಾರ್ಯನಿರ್ವಹಿಸುತ್ತದೆ ಎಂದು ನಾನು ಗಮನಿಸುತ್ತೇನೆ fk, ಅಲ್ಲಿ ವಿಂಗಡಿಸಿ pk ಅಲ್ಲಿ ಇರಲಿಲ್ಲ ಮತ್ತು ಇಲ್ಲ (ನೀವು ಇದರ ಬಗ್ಗೆ ಇನ್ನಷ್ಟು ಓದಬಹುದು ನಿಷ್ಪರಿಣಾಮಕಾರಿ ಸೂಚ್ಯಂಕಗಳನ್ನು ಕಂಡುಹಿಡಿಯುವ ಬಗ್ಗೆ ನನ್ನ ಲೇಖನದಲ್ಲಿ) ಸೇರಿದಂತೆ, ಇದು ಸಾಮಾನ್ಯ ಒದಗಿಸುತ್ತದೆ ಸ್ಪಷ್ಟ ವಿದೇಶಿ ಕೀ ಬೆಂಬಲ ಈ ಮೈದಾನದಲ್ಲಿ.

#2: ಸೂಚ್ಯಂಕ ಛೇದಕ (ಬಿಟ್‌ಮ್ಯಾಪ್ ಮತ್ತು)

ಯಾವಾಗ ಉದ್ಭವಿಸುತ್ತದೆ

ಕ್ಲೈಂಟ್ "ಎಲ್ಎಲ್ ಸಿ ಕೊಲೊಕೊಲ್ಚಿಕ್" ಗಾಗಿ ಎಲ್ಲಾ ಒಪ್ಪಂದಗಳನ್ನು ತೋರಿಸಿ, "NAO ಬಟರ್ಕಪ್" ಪರವಾಗಿ ತೀರ್ಮಾನಿಸಲಾಗಿದೆ.

ಗುರುತಿಸುವುದು ಹೇಗೆ

-> 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); -- отбор по конкретной паре

[explain.tensor.ru ನೋಡಿ]

ಸರಿಪಡಿಸಲಾಗುತ್ತಿದೆ:

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

[explain.tensor.ru ನೋಡಿ]

ಇಲ್ಲಿ ಪ್ರತಿಫಲವು ಚಿಕ್ಕದಾಗಿದೆ, ಏಕೆಂದರೆ ಬಿಟ್‌ಮ್ಯಾಪ್ ಹೀಪ್ ಸ್ಕ್ಯಾನ್ ತನ್ನದೇ ಆದ ಮೇಲೆ ಸಾಕಷ್ಟು ಪರಿಣಾಮಕಾರಿಯಾಗಿದೆ. ಆದರೆ ಹೇಗಾದರೂ 7 ಪಟ್ಟು ವೇಗವಾಗಿ ಮತ್ತು 2.5 ಪಟ್ಟು ಕಡಿಮೆ ಓದುತ್ತದೆ.

#3: ಸೂಚಿಕೆಗಳನ್ನು ವಿಲೀನಗೊಳಿಸಿ (ಬಿಟ್‌ಮ್ಯಾಪ್‌ಆರ್)

ಯಾವಾಗ ಉದ್ಭವಿಸುತ್ತದೆ

ಪ್ರಕ್ರಿಯೆಗಾಗಿ ಮೊದಲ 20 ಹಳೆಯ “ನಮಗೆ” ಅಥವಾ ನಿಯೋಜಿಸದ ವಿನಂತಿಗಳನ್ನು ನಿಮ್ಮ ಆದ್ಯತೆಯೊಂದಿಗೆ ತೋರಿಸಿ.

ಗುರುತಿಸುವುದು ಹೇಗೆ

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

ಶಿಫಾರಸುಗಳನ್ನು

ಬಳಸಲು ಒಕ್ಕೂಟ [ಎಲ್ಲ] ಷರತ್ತುಗಳ ಪ್ರತಿಯೊಂದು OR-ಬ್ಲಾಕ್‌ಗಳಿಗೆ ಉಪಪ್ರಶ್ನೆಗಳನ್ನು ಸಂಯೋಜಿಸಲು.

ಉದಾಹರಣೆ:

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;

[explain.tensor.ru ನೋಡಿ]

ಸರಿಪಡಿಸಲಾಗುತ್ತಿದೆ:

(
  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, больше и не надо

[explain.tensor.ru ನೋಡಿ]

ಅಗತ್ಯವಿರುವ ಎಲ್ಲಾ 20 ದಾಖಲೆಗಳನ್ನು ಮೊದಲ ಬ್ಲಾಕ್‌ನಲ್ಲಿ ತಕ್ಷಣವೇ ಸ್ವೀಕರಿಸಲಾಗಿದೆ ಎಂಬ ಅಂಶದ ಲಾಭವನ್ನು ನಾವು ಪಡೆದುಕೊಂಡಿದ್ದೇವೆ, ಆದ್ದರಿಂದ ಎರಡನೆಯದನ್ನು ಹೆಚ್ಚು “ದುಬಾರಿ” ಬಿಟ್‌ಮ್ಯಾಪ್ ಹೀಪ್ ಸ್ಕ್ಯಾನ್‌ನೊಂದಿಗೆ ಕಾರ್ಯಗತಗೊಳಿಸಲಾಗಿಲ್ಲ - ಕೊನೆಯಲ್ಲಿ 22x ವೇಗವಾಗಿ, 44x ಕಡಿಮೆ ಓದುತ್ತದೆ!

ಈ ಆಪ್ಟಿಮೈಸೇಶನ್ ವಿಧಾನದ ಬಗ್ಗೆ ಹೆಚ್ಚು ವಿವರವಾದ ಕಥೆ ನಿರ್ದಿಷ್ಟ ಉದಾಹರಣೆಗಳನ್ನು ಬಳಸಿ ಲೇಖನಗಳಲ್ಲಿ ಓದಬಹುದು PostgreSQL ಆಂಟಿಪ್ಯಾಟರ್ನ್‌ಗಳು: ಹಾನಿಕಾರಕ ಸೇರ್ಪಡೆಗಳು ಮತ್ತು ORಗಳು и PostgreSQL ಆಂಟಿಪ್ಯಾಟರ್ನ್ಸ್: ಹೆಸರಿನ ಮೂಲಕ ಹುಡುಕಾಟದ ಪುನರಾವರ್ತಿತ ಪರಿಷ್ಕರಣೆಯ ಕಥೆ, ಅಥವಾ "ಹಿಂದೆ ಮತ್ತು ಮುಂದಕ್ಕೆ ಆಪ್ಟಿಮೈಜ್ ಮಾಡುವುದು".

ಸಾಮಾನ್ಯೀಕೃತ ಆವೃತ್ತಿ ಹಲವಾರು ಕೀಲಿಗಳ ಆಧಾರದ ಮೇಲೆ ಆಯ್ಕೆಯನ್ನು ಆದೇಶಿಸಲಾಗಿದೆ (ಮತ್ತು const/NULL ಜೋಡಿ ಮಾತ್ರವಲ್ಲ) ಲೇಖನದಲ್ಲಿ ಚರ್ಚಿಸಲಾಗಿದೆ SQL ಹೇಗೆ: ಪ್ರಶ್ನೆಯಲ್ಲಿ ನೇರವಾಗಿ ಲೂಪ್ ಅನ್ನು ಬರೆಯಿರಿ, ಅಥವಾ "ಎಲಿಮೆಂಟರಿ ತ್ರಿ-ವೇ".

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

[explain.tensor.ru ನೋಡಿ]

ಸರಿಪಡಿಸಲಾಗುತ್ತಿದೆ:

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

[explain.tensor.ru ನೋಡಿ]

ನೀವು ನೋಡುವಂತೆ, ಫಿಲ್ಟರಿಂಗ್ ಯೋಜನೆಯಿಂದ ಸಂಪೂರ್ಣವಾಗಿ ಕಣ್ಮರೆಯಾಗಿದೆ ಮತ್ತು ವಿನಂತಿಯು ಮಾರ್ಪಟ್ಟಿದೆ 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 ಆಂಟಿಪ್ಯಾಟರ್ನ್ಸ್: "ಸತ್ತವರ" ಹೋರಾಟದ ಗುಂಪುಗಳು.

ಆದರೆ ವ್ಯಾಕ್ಯೂಮ್ ಫುಲ್ ಸಹ ಯಾವಾಗಲೂ ಸಹಾಯ ಮಾಡುವುದಿಲ್ಲ ಎಂದು ನೀವು ಅರ್ಥಮಾಡಿಕೊಳ್ಳಬೇಕು. ಅಂತಹ ಸಂದರ್ಭಗಳಲ್ಲಿ, ಲೇಖನದಿಂದ ಅಲ್ಗಾರಿದಮ್ನೊಂದಿಗೆ ನೀವೇ ಪರಿಚಿತರಾಗಿರುವುದು ಯೋಗ್ಯವಾಗಿದೆ DBA: ವ್ಯಾಕ್ಯೂಮ್ ವಿಫಲವಾದಾಗ, ನಾವು ಟೇಬಲ್ ಅನ್ನು ಹಸ್ತಚಾಲಿತವಾಗಿ ಸ್ವಚ್ಛಗೊಳಿಸುತ್ತೇವೆ.

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

[explain.tensor.ru ನೋಡಿ]

ಸೂಚ್ಯಂಕದ ಪ್ರಕಾರ ಎಲ್ಲವೂ ಸರಿಯಾಗಿದೆ ಎಂದು ತೋರುತ್ತದೆ, ಆದರೆ ಇದು ಹೇಗಾದರೂ ಅನುಮಾನಾಸ್ಪದವಾಗಿದೆ - ಓದುವ 20 ದಾಖಲೆಗಳಲ್ಲಿ ಪ್ರತಿಯೊಂದಕ್ಕೂ ನಾವು 4 ಪುಟಗಳ ಡೇಟಾವನ್ನು ಕಳೆಯಬೇಕಾಗಿತ್ತು, ಪ್ರತಿ ದಾಖಲೆಗೆ 32KB - ಅದು ದಪ್ಪ ಅಲ್ಲವೇ? ಮತ್ತು ಸೂಚ್ಯಂಕ ಹೆಸರು tbl_fk_org_fk_cli_idx ಅಭಿಪ್ರಾಯ ಪ್ರಚೋಧಕ.

ಸರಿಪಡಿಸಲಾಗುತ್ತಿದೆ:

CREATE INDEX ON tbl(fk_cli);

[explain.tensor.ru ನೋಡಿ]

ಇದ್ದಕ್ಕಿದ್ದಂತೆ - 10 ಪಟ್ಟು ವೇಗವಾಗಿ, ಮತ್ತು ಓದಲು 4 ಪಟ್ಟು ಕಡಿಮೆ!

ಸೂಚಿಕೆಗಳ ನಿಷ್ಪರಿಣಾಮಕಾರಿ ಬಳಕೆಯ ಸಂದರ್ಭಗಳ ಇತರ ಉದಾಹರಣೆಗಳನ್ನು ಲೇಖನದಲ್ಲಿ ಕಾಣಬಹುದು DBA: ಅನುಪಯುಕ್ತ ಸೂಚ್ಯಂಕಗಳನ್ನು ಕಂಡುಹಿಡಿಯುವುದು.

#7: CTE × CTE

ಯಾವಾಗ ಉದ್ಭವಿಸುತ್ತದೆ

ವಿನಂತಿಯಲ್ಲಿ "ಕೊಬ್ಬಿನ" CTE ಗಳಿಸಿದರು ವಿವಿಧ ಕೋಷ್ಟಕಗಳಿಂದ, ಮತ್ತು ನಂತರ ಅವುಗಳ ನಡುವೆ ಮಾಡಲು ನಿರ್ಧರಿಸಿದರು JOIN.

ಪ್ರಕರಣವು v12 ಕ್ಕಿಂತ ಕೆಳಗಿನ ಆವೃತ್ತಿಗಳಿಗೆ ಅಥವಾ ವಿನಂತಿಗಳಿಗೆ ಸಂಬಂಧಿಸಿದೆ WITH MATERIALIZED.

ಗುರುತಿಸುವುದು ಹೇಗೆ

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

ಶಿಫಾರಸುಗಳನ್ನು

ವಿನಂತಿಯನ್ನು ಎಚ್ಚರಿಕೆಯಿಂದ ವಿಶ್ಲೇಷಿಸಿ - ಮತ್ತು CTE ಗಳು ಇಲ್ಲಿ ಅಗತ್ಯವಿದೆಯೇ?? ಹೌದಾದರೆ, ಆಗ hstore/json ನಲ್ಲಿ "ಡಿಕ್ಷನರಿ" ಅನ್ನು ಅನ್ವಯಿಸಿ ವಿವರಿಸಿದ ಮಾದರಿಯ ಪ್ರಕಾರ PostgreSQL ಆಂಟಿಪ್ಯಾಟರ್ನ್‌ಗಳು: ನಿಘಂಟಿನೊಂದಿಗೆ ಭಾರೀ JOIN ಅನ್ನು ಹೊಡೆಯೋಣ.

#8: ಡಿಸ್ಕ್‌ಗೆ ಸ್ವ್ಯಾಪ್ ಮಾಡಿ (ತಾಪಮಾನ ಬರೆಯಲಾಗಿದೆ)

ಯಾವಾಗ ಉದ್ಭವಿಸುತ್ತದೆ

ಹೆಚ್ಚಿನ ಸಂಖ್ಯೆಯ ದಾಖಲೆಗಳ ಒಂದು-ಬಾರಿ ಪ್ರಕ್ರಿಯೆ (ವಿಂಗಡಣೆ ಅಥವಾ ಅನನ್ಯತೆ) ಇದಕ್ಕಾಗಿ ನಿಯೋಜಿಸಲಾದ ಮೆಮೊರಿಗೆ ಹೊಂದಿಕೆಯಾಗುವುದಿಲ್ಲ.

ಗುರುತಿಸುವುದು ಹೇಗೆ

-> *
   && temp written > 0

ಶಿಫಾರಸುಗಳನ್ನು

ಕಾರ್ಯಾಚರಣೆಯಿಂದ ಬಳಸಲಾಗುವ ಮೆಮೊರಿಯ ಪ್ರಮಾಣವು ನಿಯತಾಂಕದ ನಿಗದಿತ ಮೌಲ್ಯವನ್ನು ಹೆಚ್ಚು ಮೀರದಿದ್ದರೆ ಕೆಲಸ_ಮೆಮ್, ಅದನ್ನು ಸರಿಪಡಿಸುವುದು ಯೋಗ್ಯವಾಗಿದೆ. ನೀವು ತಕ್ಷಣ ಎಲ್ಲರಿಗೂ ಕಾನ್ಫಿಗರ್ ಮಾಡಬಹುದು, ಅಥವಾ ನೀವು ಮೂಲಕ ಮಾಡಬಹುದು SET [LOCAL] ನಿರ್ದಿಷ್ಟ ವಿನಂತಿ/ವ್ಯವಹಾರಕ್ಕಾಗಿ.

ಉದಾಹರಣೆ:

SHOW work_mem;
-- "16MB"

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

[explain.tensor.ru ನೋಡಿ]

ಸರಿಪಡಿಸಲಾಗುತ್ತಿದೆ:

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

[explain.tensor.ru ನೋಡಿ]

ಸ್ಪಷ್ಟ ಕಾರಣಗಳಿಗಾಗಿ, ಮೆಮೊರಿಯನ್ನು ಮಾತ್ರ ಬಳಸಿದರೆ ಮತ್ತು ಡಿಸ್ಕ್ ಅಲ್ಲ, ನಂತರ ಪ್ರಶ್ನೆಯನ್ನು ಹೆಚ್ಚು ವೇಗವಾಗಿ ಕಾರ್ಯಗತಗೊಳಿಸಲಾಗುತ್ತದೆ. ಅದೇ ಸಮಯದಲ್ಲಿ, HDD ಯಿಂದ ಲೋಡ್ನ ಭಾಗವನ್ನು ಸಹ ತೆಗೆದುಹಾಕಲಾಗುತ್ತದೆ.

ಆದರೆ ನೀವು ಯಾವಾಗಲೂ ಸಾಕಷ್ಟು ಮತ್ತು ಸಾಕಷ್ಟು ಮೆಮೊರಿಯನ್ನು ನಿಯೋಜಿಸಲು ಸಾಧ್ಯವಾಗುವುದಿಲ್ಲ ಎಂದು ನೀವು ಅರ್ಥಮಾಡಿಕೊಳ್ಳಬೇಕು - ಎಲ್ಲರಿಗೂ ಸಾಕಾಗುವುದಿಲ್ಲ.

#9: ಅಪ್ರಸ್ತುತ ಅಂಕಿಅಂಶಗಳು

ಯಾವಾಗ ಉದ್ಭವಿಸುತ್ತದೆ

ಅವರು ಒಂದೇ ಬಾರಿಗೆ ಡೇಟಾಬೇಸ್ಗೆ ಬಹಳಷ್ಟು ಸುರಿದರು, ಆದರೆ ಅದನ್ನು ಓಡಿಸಲು ಸಮಯವಿರಲಿಲ್ಲ ANALYZE.

ಗುರುತಿಸುವುದು ಹೇಗೆ

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

ಶಿಫಾರಸುಗಳನ್ನು

ಅದನ್ನು ಕೈಗೊಳ್ಳಿ ANALYZE.

ಈ ಪರಿಸ್ಥಿತಿಯನ್ನು ಹೆಚ್ಚು ವಿವರವಾಗಿ ವಿವರಿಸಲಾಗಿದೆ PostgreSQL ಆಂಟಿಪ್ಯಾಟರ್ನ್ಸ್: ಅಂಕಿಅಂಶಗಳು ಎಲ್ಲವೂ.

#10: "ಏನೋ ತಪ್ಪಾಗಿದೆ"

ಯಾವಾಗ ಉದ್ಭವಿಸುತ್ತದೆ

ಸ್ಪರ್ಧಾತ್ಮಕ ವಿನಂತಿಯಿಂದ ವಿಧಿಸಲಾದ ಲಾಕ್‌ಗಾಗಿ ಕಾಯುವಿಕೆ ಇತ್ತು ಅಥವಾ ಸಾಕಷ್ಟು CPU/ಹೈಪರ್‌ವೈಸರ್ ಹಾರ್ಡ್‌ವೇರ್ ಸಂಪನ್ಮೂಲಗಳು ಇರಲಿಲ್ಲ.

ಗುರುತಿಸುವುದು ಹೇಗೆ

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

ಶಿಫಾರಸುಗಳನ್ನು

ಬಾಹ್ಯ ಬಳಸಿ ಮೇಲ್ವಿಚಾರಣಾ ವ್ಯವಸ್ಥೆ ನಿರ್ಬಂಧಿಸುವ ಅಥವಾ ಅಸಹಜ ಸಂಪನ್ಮೂಲ ಬಳಕೆಗಾಗಿ ಸರ್ವರ್. ನೂರಾರು ಸರ್ವರ್‌ಗಳಿಗಾಗಿ ಈ ಪ್ರಕ್ರಿಯೆಯನ್ನು ಆಯೋಜಿಸುವ ನಮ್ಮ ಆವೃತ್ತಿಯ ಕುರಿತು ನಾವು ಈಗಾಗಲೇ ಮಾತನಾಡಿದ್ದೇವೆ ಇಲ್ಲಿ и ಇಲ್ಲಿ.

ಮೂಲ: www.habr.com