ਬੀਮਾਰ SQL ਸਵਾਲਾਂ ਲਈ ਪਕਵਾਨਾਂ

ਮਹੀਨੋ ਪਹਿਲਾਂ ਅਸੀਂ ਐਲਾਨ ਕੀਤਾ explain.tensor.ru - ਜਨਤਕ ਪੁੱਛਗਿੱਛ ਯੋਜਨਾਵਾਂ ਨੂੰ ਪਾਰਸ ਕਰਨ ਅਤੇ ਦੇਖਣ ਲਈ ਸੇਵਾ PostgreSQL ਨੂੰ.

ਤੁਸੀਂ ਪਹਿਲਾਂ ਹੀ ਇਸਦੀ ਵਰਤੋਂ 6000 ਤੋਂ ਵੱਧ ਵਾਰ ਕਰ ਚੁੱਕੇ ਹੋ, ਪਰ ਇੱਕ ਆਸਾਨ ਵਿਸ਼ੇਸ਼ਤਾ ਜੋ ਸ਼ਾਇਦ ਕਿਸੇ ਦਾ ਧਿਆਨ ਨਹੀਂ ਗਈ ਹੈ ਢਾਂਚਾਗਤ ਸੁਰਾਗ, ਜੋ ਕੁਝ ਇਸ ਤਰ੍ਹਾਂ ਦਿਖਾਈ ਦਿੰਦਾ ਹੈ:

ਉਨ੍ਹਾਂ ਨੂੰ ਸੁਣੋ, ਅਤੇ ਤੁਹਾਡੀਆਂ ਬੇਨਤੀਆਂ “ਸੁਲਝੀਆਂ ਅਤੇ ਰੇਸ਼ਮੀ ਹੋ ਜਾਣਗੀਆਂ।” 🙂

ਪਰ ਗੰਭੀਰਤਾ ਨਾਲ, ਬਹੁਤ ਸਾਰੀਆਂ ਸਥਿਤੀਆਂ ਜੋ ਬੇਨਤੀ ਨੂੰ ਹੌਲੀ ਅਤੇ ਸਰੋਤ-ਭੁੱਖੀਆਂ ਬਣਾਉਂਦੀਆਂ ਹਨ ਆਮ ਹਨ ਅਤੇ ਯੋਜਨਾ ਦੇ ਢਾਂਚੇ ਅਤੇ ਡੇਟਾ ਦੁਆਰਾ ਪਛਾਣੇ ਜਾ ਸਕਦੇ ਹਨ.

ਇਸ ਸਥਿਤੀ ਵਿੱਚ, ਹਰੇਕ ਵਿਅਕਤੀਗਤ ਡਿਵੈਲਪਰ ਨੂੰ ਆਪਣੇ ਤਜ਼ਰਬੇ 'ਤੇ ਨਿਰਭਰ ਕਰਦਿਆਂ, ਆਪਣੇ ਆਪ ਇੱਕ ਅਨੁਕੂਲਤਾ ਵਿਕਲਪ ਲੱਭਣ ਦੀ ਜ਼ਰੂਰਤ ਨਹੀਂ ਹੈ - ਅਸੀਂ ਉਸਨੂੰ ਦੱਸ ਸਕਦੇ ਹਾਂ ਕਿ ਇੱਥੇ ਕੀ ਹੋ ਰਿਹਾ ਹੈ, ਕੀ ਕਾਰਨ ਹੋ ਸਕਦਾ ਹੈ, ਅਤੇ ਹੱਲ ਤੱਕ ਕਿਵੇਂ ਪਹੁੰਚਣਾ ਹੈ. ਇਹੀ ਅਸੀਂ ਕੀਤਾ।

ਆਉ ਇਹਨਾਂ ਕੇਸਾਂ 'ਤੇ ਡੂੰਘਾਈ ਨਾਲ ਵਿਚਾਰ ਕਰੀਏ - ਉਹਨਾਂ ਨੂੰ ਕਿਵੇਂ ਪਰਿਭਾਸ਼ਿਤ ਕੀਤਾ ਜਾਂਦਾ ਹੈ ਅਤੇ ਉਹ ਕਿਹੜੀਆਂ ਸਿਫ਼ਾਰਸ਼ਾਂ ਵੱਲ ਲੈ ਜਾਂਦੇ ਹਨ।

ਵਿਸ਼ੇ ਵਿੱਚ ਆਪਣੇ ਆਪ ਨੂੰ ਬਿਹਤਰ ਢੰਗ ਨਾਲ ਲੀਨ ਕਰਨ ਲਈ, ਤੁਸੀਂ ਪਹਿਲਾਂ ਸੰਬੰਧਿਤ ਬਲਾਕ ਨੂੰ ਸੁਣ ਸਕਦੇ ਹੋ PGConf.Russia 2020 'ਤੇ ਮੇਰੀ ਰਿਪੋਰਟ, ਅਤੇ ਕੇਵਲ ਤਦ ਹੀ ਹਰੇਕ ਉਦਾਹਰਣ ਦੇ ਵਿਸਤ੍ਰਿਤ ਵਿਸ਼ਲੇਸ਼ਣ ਵੱਲ ਵਧੋ:

#1: ਸੂਚਕਾਂਕ "ਅੰਡਰਸੌਰਟਿੰਗ"

ਕਦੋਂ ਕਰਦਾ ਹੈ

ਕਲਾਇੰਟ "LLC Kolokolchik" ਲਈ ਨਵੀਨਤਮ ਇਨਵੌਇਸ ਦਿਖਾਓ।

ਪਛਾਣ ਕਿਵੇਂ ਕਰੀਏ

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

[explanation.tensor.ru 'ਤੇ ਦੇਖੋ]

ਤੁਸੀਂ ਤੁਰੰਤ ਨੋਟਿਸ ਕਰ ਸਕਦੇ ਹੋ ਕਿ ਸੂਚਕਾਂਕ ਤੋਂ 100 ਤੋਂ ਵੱਧ ਰਿਕਾਰਡਾਂ ਨੂੰ ਘਟਾ ਦਿੱਤਾ ਗਿਆ ਸੀ, ਜੋ ਫਿਰ ਸਾਰੇ ਕ੍ਰਮਬੱਧ ਕੀਤੇ ਗਏ ਸਨ, ਅਤੇ ਫਿਰ ਸਿਰਫ਼ ਇੱਕ ਹੀ ਬਚਿਆ ਸੀ।

ਠੀਕ ਕਰਨਾ:

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

[explanation.tensor.ru 'ਤੇ ਦੇਖੋ]

ਅਜਿਹੇ ਆਦਿਮ ਨਮੂਨੇ 'ਤੇ ਵੀ - 8.5 ਗੁਣਾ ਤੇਜ਼ ਅਤੇ 33 ਗੁਣਾ ਘੱਟ ਪੜ੍ਹਿਆ ਜਾਂਦਾ ਹੈ. ਹਰੇਕ ਮੁੱਲ ਲਈ ਤੁਹਾਡੇ ਕੋਲ ਜਿੰਨੇ ਜ਼ਿਆਦਾ "ਤੱਥ" ਹੋਣਗੇ, ਪ੍ਰਭਾਵ ਓਨਾ ਹੀ ਸਪੱਸ਼ਟ ਹੋਵੇਗਾ fk.

ਮੈਂ ਨੋਟ ਕਰਦਾ ਹਾਂ ਕਿ ਅਜਿਹਾ ਸੂਚਕਾਂਕ ਇੱਕ "ਅਗੇਤਰ" ਸੂਚਕਾਂਕ ਦੇ ਤੌਰ ਤੇ ਕੰਮ ਕਰੇਗਾ, ਜਿਸ ਨਾਲ ਹੋਰ ਪੁੱਛਗਿੱਛਾਂ ਲਈ ਪਹਿਲਾਂ ਨਾਲੋਂ ਮਾੜਾ ਨਹੀਂ ਹੋਵੇਗਾ fk, ਜਿੱਥੇ ਦੁਆਰਾ ਕ੍ਰਮਬੱਧ pk ਉੱਥੇ ਨਹੀਂ ਸੀ ਅਤੇ ਉੱਥੇ ਨਹੀਂ ਹੈ (ਤੁਸੀਂ ਇਸ ਬਾਰੇ ਹੋਰ ਪੜ੍ਹ ਸਕਦੇ ਹੋ ਬੇਅਸਰ ਸੂਚਕਾਂਕ ਲੱਭਣ ਬਾਰੇ ਮੇਰੇ ਲੇਖ ਵਿੱਚ). ਸਮੇਤ, ਇਹ ਆਮ ਪ੍ਰਦਾਨ ਕਰੇਗਾ ਸਪਸ਼ਟ ਵਿਦੇਸ਼ੀ ਕੁੰਜੀ ਸਮਰਥਨ ਇਸ ਖੇਤਰ 'ਤੇ.

#2: ਇੰਡੈਕਸ ਇੰਟਰਸੈਕਸ਼ਨ (ਬਿਟਮੈਪ ਅਤੇ)

ਕਦੋਂ ਕਰਦਾ ਹੈ

ਕਲਾਇੰਟ "LLC ਕੋਲੋਕੋਲਚਿਕ" ਲਈ ਸਾਰੇ ਸਮਝੌਤੇ ਦਿਖਾਓ, "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); -- отбор по конкретной паре

[explanation.tensor.ru 'ਤੇ ਦੇਖੋ]

ਠੀਕ ਕਰਨਾ:

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

[explanation.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;

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

[explanation.tensor.ru 'ਤੇ ਦੇਖੋ]

ਅਸੀਂ ਇਸ ਤੱਥ ਦਾ ਫਾਇਦਾ ਉਠਾਇਆ ਕਿ ਪਹਿਲੇ ਬਲਾਕ ਵਿੱਚ ਸਾਰੇ 20 ਲੋੜੀਂਦੇ ਰਿਕਾਰਡ ਤੁਰੰਤ ਪ੍ਰਾਪਤ ਕੀਤੇ ਗਏ ਸਨ, ਇਸਲਈ ਦੂਜਾ, ਵਧੇਰੇ "ਮਹਿੰਗੇ" ਬਿਟਮੈਪ ਹੀਪ ਸਕੈਨ ਦੇ ਨਾਲ, ਨੂੰ ਵੀ ਲਾਗੂ ਨਹੀਂ ਕੀਤਾ ਗਿਆ ਸੀ - ਅੰਤ ਵਿੱਚ 22 ਗੁਣਾ ਤੇਜ਼, 44 ਗੁਣਾ ਘੱਟ ਪੜ੍ਹਿਆ!

ਇਸ ਓਪਟੀਮਾਈਜੇਸ਼ਨ ਵਿਧੀ ਬਾਰੇ ਇੱਕ ਹੋਰ ਵਿਸਤ੍ਰਿਤ ਕਹਾਣੀ ਖਾਸ ਉਦਾਹਰਣਾਂ ਦੀ ਵਰਤੋਂ ਕਰਦੇ ਹੋਏ ਲੇਖਾਂ ਵਿੱਚ ਪੜ੍ਹਿਆ ਜਾ ਸਕਦਾ ਹੈ PostgreSQL ਐਂਟੀਪੈਟਰਨ: ਹਾਨੀਕਾਰਕ JOINs ਅਤੇ ORs и PostgreSQL ਐਂਟੀਪੈਟਰਨਜ਼: ਨਾਮ ਦੁਆਰਾ ਖੋਜ ਦੇ ਦੁਹਰਾਏ ਸੁਧਾਰ ਦੀ ਕਹਾਣੀ, ਜਾਂ "ਅੱਗੇ ਅਤੇ ਅੱਗੇ ਅਨੁਕੂਲਤਾ".

ਆਮ ਸੰਸਕਰਣ ਕਈ ਕੁੰਜੀਆਂ ਦੇ ਆਧਾਰ 'ਤੇ ਆਰਡਰ ਕੀਤੀ ਚੋਣ (ਅਤੇ ਨਾ ਸਿਰਫ਼ const/NULL ਜੋੜਾ) ਲੇਖ ਵਿੱਚ ਚਰਚਾ ਕੀਤੀ ਗਈ ਹੈ SQL HowTo: ਪੁੱਛ-ਗਿੱਛ ਵਿੱਚ ਸਿੱਧਾ ਕੁਝ ਸਮਾਂ ਲੂਪ ਲਿਖਣਾ, ਜਾਂ “ਐਲੀਮੈਂਟਰੀ ਤਿੰਨ-ਪੜਾਅ”.

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

[explanation.tensor.ru 'ਤੇ ਦੇਖੋ]

ਠੀਕ ਕਰਨਾ:

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

[explanation.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;

[explanation.tensor.ru 'ਤੇ ਦੇਖੋ]

ਇੰਡੈਕਸ ਦੇ ਅਨੁਸਾਰ ਵੀ, ਸਭ ਕੁਝ ਠੀਕ ਜਾਪਦਾ ਹੈ, ਪਰ ਇਹ ਕਿਸੇ ਤਰ੍ਹਾਂ ਸ਼ੱਕੀ ਹੈ - ਪੜ੍ਹੇ ਗਏ 20 ਰਿਕਾਰਡਾਂ ਵਿੱਚੋਂ ਹਰੇਕ ਲਈ, ਸਾਨੂੰ 4 ਪੰਨਿਆਂ ਦੇ ਡੇਟਾ, 32KB ਪ੍ਰਤੀ ਰਿਕਾਰਡ ਘਟਾਉਣਾ ਪਿਆ - ਕੀ ਇਹ ਬੋਲਡ ਨਹੀਂ ਹੈ? ਅਤੇ ਸੂਚਕਾਂਕ ਦਾ ਨਾਮ tbl_fk_org_fk_cli_idx ਸੋਚ-ਉਕਸਾਉਣ ਵਾਲਾ।

ਠੀਕ ਕਰਨਾ:

CREATE INDEX ON tbl(fk_cli);

[explanation.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 ਐਂਟੀਪੈਟਰਨ: ਆਉ ਇੱਕ ਸ਼ਬਦਕੋਸ਼ ਦੇ ਨਾਲ ਭਾਰੀ ਜੁਆਇਨ ਕਰੀਏ.

#8: ਡਿਸਕ 'ਤੇ ਅਦਲਾ-ਬਦਲੀ (ਟੈਂਪ ਲਿਖਿਆ)

ਕਦੋਂ ਕਰਦਾ ਹੈ

ਵੱਡੀ ਗਿਣਤੀ ਵਿੱਚ ਰਿਕਾਰਡਾਂ ਦੀ ਇੱਕ-ਵਾਰ ਪ੍ਰੋਸੈਸਿੰਗ (ਛਾਂਟੀ ਜਾਂ ਵਿਲੱਖਣਤਾ) ਇਸ ਲਈ ਨਿਰਧਾਰਤ ਕੀਤੀ ਗਈ ਮੈਮੋਰੀ ਵਿੱਚ ਫਿੱਟ ਨਹੀਂ ਹੁੰਦੀ ਹੈ।

ਪਛਾਣ ਕਿਵੇਂ ਕਰੀਏ

-> *
   && temp written > 0

ਿਸਫ਼ਾਰ

ਜੇਕਰ ਓਪਰੇਸ਼ਨ ਦੁਆਰਾ ਵਰਤੀ ਗਈ ਮੈਮੋਰੀ ਦੀ ਮਾਤਰਾ ਪੈਰਾਮੀਟਰ ਦੇ ਨਿਰਧਾਰਤ ਮੁੱਲ ਤੋਂ ਬਹੁਤ ਜ਼ਿਆਦਾ ਨਹੀਂ ਹੈ work_mem, ਇਸ ਨੂੰ ਠੀਕ ਕਰਨ ਦੀ ਕੀਮਤ ਹੈ. ਤੁਸੀਂ ਤੁਰੰਤ ਹਰ ਕਿਸੇ ਲਈ ਸੰਰਚਨਾ ਵਿੱਚ ਕਰ ਸਕਦੇ ਹੋ, ਜਾਂ ਤੁਸੀਂ ਕਰ ਸਕਦੇ ਹੋ SET [LOCAL] ਕਿਸੇ ਖਾਸ ਬੇਨਤੀ/ਲੈਣ-ਦੇਣ ਲਈ।

ਉਦਾਹਰਨ:

SHOW work_mem;
-- "16MB"

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

[explanation.tensor.ru 'ਤੇ ਦੇਖੋ]

ਠੀਕ ਕਰਨਾ:

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

[explanation.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