🥇PostgreSQL ಆಂಟಿಪ್ಯಾಟರ್ನ್‌ಗಳು: ಮೊಲದ ರಂಧ್ರ ಎಷ್ಟು ಆಳವಾಗಿದೆ? ಕ್ರಮಾನುಗತದಿಂದ ಹೋಗೋಣ

ಸಂಕೀರ್ಣ ERP ವ್ಯವಸ್ಥೆಗಳಲ್ಲಿ ಅನೇಕ ಘಟಕಗಳು ಕ್ರಮಾನುಗತ ಸ್ವಭಾವವನ್ನು ಹೊಂದಿವೆಏಕರೂಪದ ವಸ್ತುಗಳು ಸಾಲಾಗಿ ಬಂದಾಗ ಪೂರ್ವಜ-ವಂಶಸ್ಥರ ಸಂಬಂಧಗಳ ಮರ - ಇದು ಎಂಟರ್‌ಪ್ರೈಸ್‌ನ ಸಾಂಸ್ಥಿಕ ರಚನೆ (ಈ ಎಲ್ಲಾ ಶಾಖೆಗಳು, ಇಲಾಖೆಗಳು ಮತ್ತು ಕೆಲಸದ ಗುಂಪುಗಳು), ಮತ್ತು ಸರಕುಗಳ ಕ್ಯಾಟಲಾಗ್, ಮತ್ತು ಕೆಲಸದ ಪ್ರದೇಶಗಳು ಮತ್ತು ಮಾರಾಟದ ಬಿಂದುಗಳ ಭೌಗೋಳಿಕತೆ,...

ವಾಸ್ತವವಾಗಿ, ಯಾವುದೂ ಇಲ್ಲ ವ್ಯಾಪಾರ ಯಾಂತ್ರೀಕೃತಗೊಂಡ ಪ್ರದೇಶಗಳು, ಅಲ್ಲಿ ಪರಿಣಾಮವಾಗಿ ಯಾವುದೇ ಕ್ರಮಾನುಗತ ಇರುವುದಿಲ್ಲ. ಆದರೆ ನೀವು "ವ್ಯವಹಾರಕ್ಕಾಗಿ" ಕೆಲಸ ಮಾಡದಿದ್ದರೂ ಸಹ, ನೀವು ಇನ್ನೂ ಸುಲಭವಾಗಿ ಕ್ರಮಾನುಗತ ಸಂಬಂಧಗಳನ್ನು ಎದುರಿಸಬಹುದು. ಇದು ಸರಳವಾಗಿದೆ, ಶಾಪಿಂಗ್ ಸೆಂಟರ್‌ನಲ್ಲಿರುವ ನಿಮ್ಮ ಕುಟುಂಬದ ಮರ ಅಥವಾ ಆವರಣದ ನೆಲದ ಯೋಜನೆ ಕೂಡ ಅದೇ ರಚನೆಯಾಗಿದೆ.

ಅಂತಹ ಮರವನ್ನು DBMS ನಲ್ಲಿ ಸಂಗ್ರಹಿಸಲು ಹಲವು ಮಾರ್ಗಗಳಿವೆ, ಆದರೆ ಇಂದು ನಾವು ಕೇವಲ ಒಂದು ಆಯ್ಕೆಯ ಮೇಲೆ ಕೇಂದ್ರೀಕರಿಸುತ್ತೇವೆ:

CREATE TABLE hier(
  id
    integer
      PRIMARY KEY
, pid
    integer
      REFERENCES hier
, data
    json
);

CREATE INDEX ON hier(pid); -- не забываем, что FK не подразумевает автосоздание индекса, в отличие от PK

ಮತ್ತು ನೀವು ಕ್ರಮಾನುಗತದ ಆಳಕ್ಕೆ ಇಣುಕಿ ನೋಡುತ್ತಿರುವಾಗ, ಅಂತಹ ರಚನೆಯೊಂದಿಗೆ ಕೆಲಸ ಮಾಡುವ ನಿಮ್ಮ "ನಿಷ್ಕಪಟ" ವಿಧಾನಗಳು ಎಷ್ಟು ಪರಿಣಾಮಕಾರಿಯಾಗಿರುತ್ತವೆ ಎಂಬುದನ್ನು ನೋಡಲು ತಾಳ್ಮೆಯಿಂದ ಕಾಯುತ್ತಿದೆ.

ಉದ್ಭವಿಸುವ ವಿಶಿಷ್ಟ ಸಮಸ್ಯೆಗಳನ್ನು ನೋಡೋಣ, SQL ನಲ್ಲಿ ಅವುಗಳ ಅನುಷ್ಠಾನ, ಮತ್ತು ಅವುಗಳ ಕಾರ್ಯಕ್ಷಮತೆಯನ್ನು ಸುಧಾರಿಸಲು ಪ್ರಯತ್ನಿಸೋಣ.

#1. ಮೊಲದ ರಂಧ್ರ ಎಷ್ಟು ಆಳವಾಗಿದೆ?

ಸಂಘಟನೆಯ ರಚನೆಯಲ್ಲಿ ಇಲಾಖೆಗಳ ಅಧೀನತೆಯನ್ನು ಈ ರಚನೆಯು ಪ್ರತಿಬಿಂಬಿಸುತ್ತದೆ ಎಂದು ನಾವು ಖಚಿತವಾಗಿ ಒಪ್ಪಿಕೊಳ್ಳೋಣ: ಇಲಾಖೆಗಳು, ವಿಭಾಗಗಳು, ವಲಯಗಳು, ಶಾಖೆಗಳು, ಕಾರ್ಯ ಗುಂಪುಗಳು ... - ನೀವು ಅವುಗಳನ್ನು ಏನೇ ಕರೆದರೂ.

ಮೊದಲಿಗೆ, 10K ಅಂಶಗಳ ನಮ್ಮ 'ಟ್ರೀ' ಅನ್ನು ರಚಿಸೋಣ

INSERT INTO hier
WITH RECURSIVE T AS (
  SELECT
    1::integer id
  , '{1}'::integer[] pids
UNION ALL
  SELECT
    id + 1
  , pids[1:(random() * array_length(pids, 1))::integer] || (id + 1)
  FROM
    T
  WHERE
    id < 10000
)
SELECT
  pids[array_length(pids, 1)] id
, pids[array_length(pids, 1) - 1] pid
FROM
  T;

ಸರಳವಾದ ಕಾರ್ಯದೊಂದಿಗೆ ಪ್ರಾರಂಭಿಸೋಣ - ನಿರ್ದಿಷ್ಟ ವಲಯದಲ್ಲಿ ಕೆಲಸ ಮಾಡುವ ಎಲ್ಲಾ ಉದ್ಯೋಗಿಗಳನ್ನು ಹುಡುಕುವುದು ಅಥವಾ ಕ್ರಮಾನುಗತದಲ್ಲಿ - ನೋಡ್‌ನ ಎಲ್ಲಾ ಮಕ್ಕಳನ್ನು ಹುಡುಕಿ. ವಂಶಸ್ಥರ "ಆಳ" ವನ್ನು ಪಡೆಯುವುದು ಸಹ ಚೆನ್ನಾಗಿರುತ್ತದೆ ... ಇದೆಲ್ಲವೂ ಅಗತ್ಯವಾಗಬಹುದು, ಉದಾಹರಣೆಗೆ, ಕೆಲವು ರೀತಿಯ ನಿರ್ಮಿಸಲು ಈ ಉದ್ಯೋಗಿಗಳ ID ಗಳ ಪಟ್ಟಿಯನ್ನು ಆಧರಿಸಿ ಸಂಕೀರ್ಣ ಆಯ್ಕೆ.

ಈ ವಂಶಸ್ಥರ ಕೇವಲ ಒಂದೆರಡು ಹಂತಗಳಿದ್ದರೆ ಮತ್ತು ಸಂಖ್ಯೆಯು ಒಂದು ಡಜನ್‌ನೊಳಗೆ ಇದ್ದರೆ ಎಲ್ಲವೂ ಚೆನ್ನಾಗಿರುತ್ತದೆ, ಆದರೆ 5 ಕ್ಕಿಂತ ಹೆಚ್ಚು ಹಂತಗಳಿದ್ದರೆ ಮತ್ತು ಈಗಾಗಲೇ ಡಜನ್ಗಟ್ಟಲೆ ವಂಶಸ್ಥರು ಇದ್ದರೆ, ಸಮಸ್ಯೆಗಳಿರಬಹುದು. ಸಾಂಪ್ರದಾಯಿಕ ಕೆಳಗೆ ಮರದ ಹುಡುಕಾಟ ಆಯ್ಕೆಗಳನ್ನು ಹೇಗೆ ಬರೆಯಲಾಗಿದೆ (ಮತ್ತು ಕೆಲಸ) ಎಂಬುದನ್ನು ನೋಡೋಣ. ಆದರೆ ಮೊದಲು, ನಮ್ಮ ಸಂಶೋಧನೆಗೆ ಯಾವ ನೋಡ್‌ಗಳು ಹೆಚ್ಚು ಆಸಕ್ತಿದಾಯಕವೆಂದು ನಿರ್ಧರಿಸೋಣ.

ಹೆಚ್ಚು "ಆಳ" ಉಪವೃಕ್ಷಗಳು:

WITH RECURSIVE T AS (
  SELECT
    id
  , pid
  , ARRAY[id] path
  FROM
    hier
  WHERE
    pid IS NULL
UNION ALL
  SELECT
    hier.id
  , hier.pid
  , T.path || hier.id
  FROM
    T
  JOIN
    hier
      ON hier.pid = T.id
)
TABLE T ORDER BY array_length(path, 1) DESC;

 id  | pid  | path
---------------------------------------------
7624 | 7623 | {7615,7620,7621,7622,7623,7624}
4995 | 4994 | {4983,4985,4988,4993,4994,4995}
4991 | 4990 | {4983,4985,4988,4989,4990,4991}
...

ಹೆಚ್ಚು "ಅಗಲ" ಉಪವೃಕ್ಷಗಳು:

...
SELECT
  path[1] id
, count(*)
FROM
  T
GROUP BY
  1
ORDER BY
  2 DESC;

id   | count
------------
5300 |   30
 450 |   28
1239 |   27
1573 |   25

ಈ ಪ್ರಶ್ನೆಗಳಿಗೆ ನಾವು ವಿಶಿಷ್ಟವನ್ನು ಬಳಸಿದ್ದೇವೆ ಪುನರಾವರ್ತಿತ ಸೇರ್ಪಡೆ:

ನಿಸ್ಸಂಶಯವಾಗಿ, ಈ ವಿನಂತಿಯ ಮಾದರಿಯೊಂದಿಗೆ ಪುನರಾವರ್ತನೆಗಳ ಸಂಖ್ಯೆಯು ಒಟ್ಟು ವಂಶಸ್ಥರ ಸಂಖ್ಯೆಗೆ ಹೊಂದಿಕೆಯಾಗುತ್ತದೆ (ಮತ್ತು ಅವುಗಳಲ್ಲಿ ಹಲವಾರು ಡಜನ್ಗಳಿವೆ), ಮತ್ತು ಇದು ಸಾಕಷ್ಟು ಮಹತ್ವದ ಸಂಪನ್ಮೂಲಗಳನ್ನು ತೆಗೆದುಕೊಳ್ಳಬಹುದು ಮತ್ತು ಪರಿಣಾಮವಾಗಿ, ಸಮಯ.

"ಅಗಲ" ಉಪವೃಕ್ಷವನ್ನು ಪರಿಶೀಲಿಸೋಣ:

WITH RECURSIVE T AS (
  SELECT
    id
  FROM
    hier
  WHERE
    id = 5300
UNION ALL
  SELECT
    hier.id
  FROM
    T
  JOIN
    hier
      ON hier.pid = T.id
)
TABLE T;

[explain.tensor.ru ನೋಡಿ]

ನಿರೀಕ್ಷೆಯಂತೆ, ನಾವು ಎಲ್ಲಾ 30 ದಾಖಲೆಗಳನ್ನು ಕಂಡುಕೊಂಡಿದ್ದೇವೆ. ಆದರೆ ಅವರು ಒಟ್ಟು ಸಮಯದ 60% ಸಮಯವನ್ನು ಇದಕ್ಕಾಗಿಯೇ ಕಳೆದರು - ಏಕೆಂದರೆ ಅವರು ಸೂಚ್ಯಂಕದಲ್ಲಿ 30 ಹುಡುಕಾಟಗಳನ್ನು ಸಹ ಮಾಡಿದ್ದಾರೆ. ಕಡಿಮೆ ಮಾಡಲು ಸಾಧ್ಯವೇ?

ಇಂಡೆಕ್ಸ್ ಮೂಲಕ ಬೃಹತ್ ಪ್ರೂಫ್ ರೀಡಿಂಗ್

ನಾವು ಪ್ರತಿ ನೋಡ್‌ಗೆ ಪ್ರತ್ಯೇಕ ಸೂಚ್ಯಂಕ ಪ್ರಶ್ನೆಯನ್ನು ಮಾಡಬೇಕೇ? ಇಲ್ಲ ಎಂದು ಅದು ತಿರುಗುತ್ತದೆ - ನಾವು ಸೂಚ್ಯಂಕದಿಂದ ಓದಬಹುದು ಒಂದು ಕರೆಯಲ್ಲಿ ಏಕಕಾಲದಲ್ಲಿ ಹಲವಾರು ಕೀಗಳನ್ನು ಬಳಸುವುದು ಸಹಾಯದಿಂದ = ANY(array).

ಮತ್ತು ಅಂತಹ ಪ್ರತಿಯೊಂದು ಗುರುತಿನ ಗುಂಪಿನಲ್ಲಿ ನಾವು ಹಿಂದಿನ ಹಂತದಲ್ಲಿ ಕಂಡುಬರುವ ಎಲ್ಲಾ ID ಗಳನ್ನು "ನೋಡ್‌ಗಳು" ಮೂಲಕ ತೆಗೆದುಕೊಳ್ಳಬಹುದು. ಅಂದರೆ, ಪ್ರತಿ ಮುಂದಿನ ಹಂತದಲ್ಲಿ ನಾವು ಮಾಡುತ್ತೇವೆ ಒಂದು ನಿರ್ದಿಷ್ಟ ಮಟ್ಟದ ಎಲ್ಲಾ ವಂಶಸ್ಥರನ್ನು ಏಕಕಾಲದಲ್ಲಿ ಹುಡುಕಿ.

ಕೇವಲ, ಇಲ್ಲಿ ಸಮಸ್ಯೆ ಇದೆ, ಪುನರಾವರ್ತಿತ ಆಯ್ಕೆಯಲ್ಲಿ, ನೀವು ನೆಸ್ಟೆಡ್ ಪ್ರಶ್ನೆಯಲ್ಲಿ ಸ್ವತಃ ಪ್ರವೇಶಿಸಲು ಸಾಧ್ಯವಿಲ್ಲ, ಆದರೆ ಹಿಂದಿನ ಹಂತದಲ್ಲಿ ಕಂಡುಬಂದದ್ದನ್ನು ಮಾತ್ರ ನಾವು ಹೇಗಾದರೂ ಆಯ್ಕೆ ಮಾಡಬೇಕಾಗಿದೆ ... ಸಂಪೂರ್ಣ ಆಯ್ಕೆಗಾಗಿ ನೆಸ್ಟೆಡ್ ಪ್ರಶ್ನೆಯನ್ನು ಮಾಡಲು ಅಸಾಧ್ಯವೆಂದು ಅದು ತಿರುಗುತ್ತದೆ, ಆದರೆ ಅದರ ನಿರ್ದಿಷ್ಟ ಕ್ಷೇತ್ರಕ್ಕೆ ಇದು ಸಾಧ್ಯ. ಮತ್ತು ಈ ಕ್ಷೇತ್ರವು ಒಂದು ಶ್ರೇಣಿಯಾಗಿರಬಹುದು - ಇದು ನಾವು ಬಳಸಬೇಕಾದದ್ದು ANY.

ಇದು ಸ್ವಲ್ಪ ಹುಚ್ಚನಂತೆ ತೋರುತ್ತದೆ, ಆದರೆ ರೇಖಾಚಿತ್ರದಲ್ಲಿ ಎಲ್ಲವೂ ಸರಳವಾಗಿದೆ.

WITH RECURSIVE T AS (
  SELECT
    ARRAY[id] id$
  FROM
    hier
  WHERE
    id = 5300
UNION ALL
  SELECT
    ARRAY(
      SELECT
        id
      FROM
        hier
      WHERE
        pid = ANY(T.id$)
    ) id$
  FROM
    T
  WHERE
    coalesce(id$, '{}') <> '{}' -- условие выхода из цикла - пустой массив
)
SELECT
  unnest(id$) id
FROM
  T;

[explain.tensor.ru ನೋಡಿ]

ಮತ್ತು ಇಲ್ಲಿ ಪ್ರಮುಖ ವಿಷಯ ಕೂಡ ಅಲ್ಲ ಸಮಯಕ್ಕೆ 1.5 ಬಾರಿ ಗೆದ್ದಿರಿ, ಮತ್ತು ನಾವು ಕಡಿಮೆ ಬಫರ್‌ಗಳನ್ನು ಕಳೆಯುತ್ತೇವೆ, ಏಕೆಂದರೆ ನಾವು ಸೂಚ್ಯಂಕಕ್ಕೆ 5 ಬದಲಿಗೆ 30 ಕರೆಗಳನ್ನು ಮಾತ್ರ ಹೊಂದಿದ್ದೇವೆ!

ಹೆಚ್ಚುವರಿ ಬೋನಸ್ ಎಂದರೆ ಅಂತಿಮ ಅನೆಸ್ಟ್‌ನ ನಂತರ, ಗುರುತಿಸುವಿಕೆಗಳು "ಮಟ್ಟಗಳು" ಕ್ರಮವಾಗಿ ಉಳಿಯುತ್ತವೆ.

ನೋಡ್ ಚಿಹ್ನೆ

ಕಾರ್ಯಕ್ಷಮತೆಯನ್ನು ಸುಧಾರಿಸಲು ಸಹಾಯ ಮಾಡುವ ಮುಂದಿನ ಪರಿಗಣನೆಯು - "ಎಲೆಗಳು" ಮಕ್ಕಳನ್ನು ಹೊಂದಲು ಸಾಧ್ಯವಿಲ್ಲ, ಅಂದರೆ, ಅವರಿಗೆ "ಕೆಳಗೆ" ನೋಡುವ ಅಗತ್ಯವಿಲ್ಲ. ನಮ್ಮ ಕಾರ್ಯದ ಸೂತ್ರೀಕರಣದಲ್ಲಿ, ಇದರರ್ಥ ನಾವು ಇಲಾಖೆಗಳ ಸರಪಳಿಯನ್ನು ಅನುಸರಿಸಿದರೆ ಮತ್ತು ಉದ್ಯೋಗಿಯನ್ನು ತಲುಪಿದರೆ, ಈ ಶಾಖೆಯ ಉದ್ದಕ್ಕೂ ಮುಂದೆ ನೋಡುವ ಅಗತ್ಯವಿಲ್ಲ.

ನಮ್ಮ ಟೇಬಲ್‌ಗೆ ಪ್ರವೇಶಿಸೋಣ ಹೆಚ್ಚುವರಿ boolean-ಕ್ಷೇತ್ರ, ಇದು ನಮ್ಮ ಮರದಲ್ಲಿನ ಈ ನಿರ್ದಿಷ್ಟ ನಮೂದು "ನೋಡ್" ಆಗಿದೆಯೇ ಎಂದು ತಕ್ಷಣವೇ ನಮಗೆ ತಿಳಿಸುತ್ತದೆ - ಅಂದರೆ, ಅದು ವಂಶಸ್ಥರನ್ನು ಹೊಂದಬಹುದೇ ಎಂದು.

ALTER TABLE hier
  ADD COLUMN branch boolean;

UPDATE
  hier T
SET
  branch = TRUE
WHERE
  EXISTS(
    SELECT
      NULL
    FROM
      hier
    WHERE
      pid = T.id
    LIMIT 1
);
-- Запрос успешно выполнен: 3033 строк изменено за 42 мс.

ಗ್ರೇಟ್! ಎಲ್ಲಾ ಮರದ ಅಂಶಗಳಲ್ಲಿ 30% ಕ್ಕಿಂತ ಸ್ವಲ್ಪ ಹೆಚ್ಚು ಮಾತ್ರ ವಂಶಸ್ಥರನ್ನು ಹೊಂದಿದೆ ಎಂದು ಅದು ತಿರುಗುತ್ತದೆ.

ಈಗ ನಾವು ಸ್ವಲ್ಪ ವಿಭಿನ್ನವಾದ ಮೆಕ್ಯಾನಿಕ್ ಅನ್ನು ಬಳಸೋಣ - ಮೂಲಕ ಪುನರಾವರ್ತಿತ ಭಾಗಕ್ಕೆ ಸಂಪರ್ಕಗಳು LATERAL, ಇದು ಪುನರಾವರ್ತಿತ “ಟೇಬಲ್” ಕ್ಷೇತ್ರಗಳನ್ನು ತಕ್ಷಣವೇ ಪ್ರವೇಶಿಸಲು ನಮಗೆ ಅನುಮತಿಸುತ್ತದೆ ಮತ್ತು ಕೀಗಳ ಗುಂಪನ್ನು ಕಡಿಮೆ ಮಾಡಲು ನೋಡ್ ಅನ್ನು ಆಧರಿಸಿ ಫಿಲ್ಟರಿಂಗ್ ಸ್ಥಿತಿಯೊಂದಿಗೆ ಒಟ್ಟು ಕಾರ್ಯವನ್ನು ಬಳಸಿ:

WITH RECURSIVE T AS (
  SELECT
    array_agg(id) id$
  , array_agg(id) FILTER(WHERE branch) ns$
  FROM
    hier
  WHERE
    id = 5300
UNION ALL
  SELECT
    X.*
  FROM
    T
  JOIN LATERAL (
    SELECT
      array_agg(id) id$
    , array_agg(id) FILTER(WHERE branch) ns$
    FROM
      hier
    WHERE
      pid = ANY(T.ns$)
  ) X
    ON coalesce(T.ns$, '{}') <> '{}'
)
SELECT
  unnest(id$) id
FROM
  T;

[explain.tensor.ru ನೋಡಿ]

ನಾವು ಇನ್ನೊಂದು ಸೂಚ್ಯಂಕ ಕರೆಯನ್ನು ಕಡಿಮೆ ಮಾಡಲು ಸಾಧ್ಯವಾಯಿತು ಮತ್ತು ಸಂಪುಟದಲ್ಲಿ 2ಕ್ಕೂ ಹೆಚ್ಚು ಬಾರಿ ಗೆದ್ದಿದ್ದಾರೆ ಪ್ರೂಫ್ ರೀಡ್.

#2. ಬೇರುಗಳಿಗೆ ಹಿಂತಿರುಗಿ ನೋಡೋಣ

ಯಾವ ಮೂಲ ಹಾಳೆಯ (ಮತ್ತು ಯಾವ ಸೂಚಕಗಳೊಂದಿಗೆ) ಅದನ್ನು ಮಾದರಿಯಲ್ಲಿ ಸೇರಿಸಲು ಕಾರಣವಾಯಿತು - ಉದಾಹರಣೆಗೆ, ಸಾರಾಂಶ ವರದಿಯನ್ನು ರಚಿಸಲು ನೀವು ಎಲ್ಲಾ ಅಂಶಗಳಿಗೆ ದಾಖಲೆಗಳನ್ನು ಸಂಗ್ರಹಿಸಬೇಕಾದರೆ ಈ ಅಲ್ಗಾರಿದಮ್ ಉಪಯುಕ್ತವಾಗಿರುತ್ತದೆ. ನೋಡ್‌ಗಳಾಗಿ ಒಟ್ಟುಗೂಡಿಸುವಿಕೆಯೊಂದಿಗೆ.

ವಿನಂತಿಯು ತುಂಬಾ ತೊಡಕಿನದ್ದಾಗಿರುವುದರಿಂದ ಕೆಳಗಿನವುಗಳನ್ನು ಪರಿಕಲ್ಪನೆಯ ಪುರಾವೆಯಾಗಿ ಮಾತ್ರ ತೆಗೆದುಕೊಳ್ಳಬೇಕು. ಆದರೆ ಅದು ನಿಮ್ಮ ಡೇಟಾಬೇಸ್‌ನಲ್ಲಿ ಪ್ರಾಬಲ್ಯ ಹೊಂದಿದ್ದರೆ, ನೀವು ಇದೇ ರೀತಿಯ ತಂತ್ರಗಳನ್ನು ಬಳಸುವ ಬಗ್ಗೆ ಯೋಚಿಸಬೇಕು.

ಒಂದೆರಡು ಸರಳ ಹೇಳಿಕೆಗಳೊಂದಿಗೆ ಪ್ರಾರಂಭಿಸೋಣ:

ಡೇಟಾಬೇಸ್‌ನಿಂದ ಅದೇ ದಾಖಲೆ ಒಮ್ಮೆ ಓದುವುದು ಉತ್ತಮ.
ಡೇಟಾಬೇಸ್‌ನಿಂದ ದಾಖಲೆಗಳು ಬ್ಯಾಚ್‌ಗಳಲ್ಲಿ ಓದಲು ಇದು ಹೆಚ್ಚು ಪರಿಣಾಮಕಾರಿಯಾಗಿದೆಒಂಟಿಯಾಗಿರುವುದಕ್ಕಿಂತ.

ಈಗ ನಮಗೆ ಅಗತ್ಯವಿರುವ ವಿನಂತಿಯನ್ನು ನಿರ್ಮಿಸಲು ಪ್ರಯತ್ನಿಸೋಣ.

1 ಹೆಜ್ಜೆ

ನಿಸ್ಸಂಶಯವಾಗಿ, ಪುನರಾವರ್ತನೆಯನ್ನು ಪ್ರಾರಂಭಿಸುವಾಗ (ಅದು ಇಲ್ಲದೆ ನಾವು ಎಲ್ಲಿದ್ದೇವೆ!) ಆರಂಭಿಕ ಗುರುತಿಸುವಿಕೆಗಳ ಗುಂಪಿನ ಆಧಾರದ ಮೇಲೆ ನಾವು ಎಲೆಗಳ ದಾಖಲೆಗಳನ್ನು ಕಳೆಯಬೇಕಾಗುತ್ತದೆ:

WITH RECURSIVE tree AS (
  SELECT
    rec -- это цельная запись таблицы
  , id::text chld -- это "набор" приведших сюда исходных листьев
  FROM
    hier rec
  WHERE
    id = ANY('{1,2,4,8,16,32,64,128,256,512,1024,2048,4096,8192}'::integer[])
UNION ALL
  ...

"ಸೆಟ್" ಅನ್ನು ಸ್ಟ್ರಿಂಗ್ ಆಗಿ ಸಂಗ್ರಹಿಸಲಾಗಿದೆ ಮತ್ತು ರಚನೆಯಾಗಿಲ್ಲ ಎಂದು ಯಾರಿಗಾದರೂ ವಿಚಿತ್ರವಾಗಿ ತೋರುತ್ತಿದ್ದರೆ, ಇದಕ್ಕೆ ಸರಳವಾದ ವಿವರಣೆಯಿದೆ. ತಂತಿಗಳಿಗಾಗಿ ಅಂತರ್ನಿರ್ಮಿತ ಒಟ್ಟುಗೂಡಿಸುವ "ಅಂಟಿಸುವ" ಕಾರ್ಯವಿದೆ string_agg, ಆದರೆ ಅರೇಗಳಿಗೆ ಅಲ್ಲ. ಆದರೂ ಅವಳು ನಿಮ್ಮ ಸ್ವಂತ ಕಾರ್ಯಗತಗೊಳಿಸಲು ಸುಲಭ.

2 ಹೆಜ್ಜೆ

ಈಗ ನಾವು ವಿಭಾಗ ID ಗಳ ಗುಂಪನ್ನು ಪಡೆಯುತ್ತೇವೆ ಅದನ್ನು ಮತ್ತಷ್ಟು ಓದಬೇಕಾಗಿದೆ. ಬಹುತೇಕ ಯಾವಾಗಲೂ ಅವುಗಳನ್ನು ಮೂಲ ಸೆಟ್‌ನ ವಿವಿಧ ದಾಖಲೆಗಳಲ್ಲಿ ನಕಲು ಮಾಡಲಾಗುತ್ತದೆ - ಆದ್ದರಿಂದ ನಾವು ಮಾಡುತ್ತೇವೆ ಅವುಗಳನ್ನು ಗುಂಪು ಮಾಡಿ, ಮೂಲ ಎಲೆಗಳ ಬಗ್ಗೆ ಮಾಹಿತಿಯನ್ನು ಸಂರಕ್ಷಿಸುವಾಗ.

ಆದರೆ ಇಲ್ಲಿ ಮೂರು ತೊಂದರೆಗಳು ನಮಗೆ ಕಾಯುತ್ತಿವೆ:

ಪ್ರಶ್ನೆಯ "ಸಬ್ರೆಕರ್ಸಿವ್" ಭಾಗವು ಇದರೊಂದಿಗೆ ಒಟ್ಟು ಕಾರ್ಯಗಳನ್ನು ಹೊಂದಿರುವುದಿಲ್ಲ GROUP BY.
ಪುನರಾವರ್ತಿತ "ಟೇಬಲ್" ಗೆ ಉಲ್ಲೇಖವು ನೆಸ್ಟೆಡ್ ಸಬ್ಕ್ವೆರಿಯಲ್ಲಿ ಇರುವಂತಿಲ್ಲ.
ಪುನರಾವರ್ತಿತ ಭಾಗದಲ್ಲಿನ ವಿನಂತಿಯು CTE ಅನ್ನು ಒಳಗೊಂಡಿರಬಾರದು.

ಅದೃಷ್ಟವಶಾತ್, ಈ ಎಲ್ಲಾ ಸಮಸ್ಯೆಗಳನ್ನು ಪರಿಹರಿಸಲು ತುಂಬಾ ಸುಲಭ. ಅಂತ್ಯದಿಂದ ಪ್ರಾರಂಭಿಸೋಣ.

ಪುನರಾವರ್ತಿತ ಭಾಗದಲ್ಲಿ CTE

ಇಲ್ಲಿ ಕೇವಲ ಕೆಲಸಗಳು:

WITH RECURSIVE tree AS (
  ...
UNION ALL
  WITH T (...)
  SELECT ...
)

ಮತ್ತು ಆದ್ದರಿಂದ ಇದು ಕಾರ್ಯನಿರ್ವಹಿಸುತ್ತದೆ, ಆವರಣಗಳು ವ್ಯತ್ಯಾಸವನ್ನುಂಟುಮಾಡುತ್ತವೆ!

WITH RECURSIVE tree AS (
  ...
UNION ALL
  (
    WITH T (...)
    SELECT ...
  )
)

ಪುನರಾವರ್ತಿತ "ಟೇಬಲ್" ವಿರುದ್ಧ ನೆಸ್ಟೆಡ್ ಪ್ರಶ್ನೆ

ಹಾಂ... ಉಪಪ್ರಶ್ನೆಯಲ್ಲಿ ಪುನರಾವರ್ತಿತ CTE ಅನ್ನು ಪ್ರವೇಶಿಸಲಾಗುವುದಿಲ್ಲ. ಆದರೆ ಅದು CTE ಒಳಗೆ ಇರಬಹುದು! ಮತ್ತು ನೆಸ್ಟೆಡ್ ವಿನಂತಿಯು ಈಗಾಗಲೇ ಈ CTE ಅನ್ನು ಪ್ರವೇಶಿಸಬಹುದು!

ಆಂತರಿಕ ಪುನರಾವರ್ತನೆಯ ಮೂಲಕ ಗುಂಪು

ಇದು ಅಹಿತಕರವಾಗಿದೆ, ಆದರೆ... ಬಳಸಿಕೊಂಡು GROUP ಅನ್ನು ಅನುಕರಿಸಲು ನಾವು ಸರಳವಾದ ಮಾರ್ಗವನ್ನು ಹೊಂದಿದ್ದೇವೆ DISTINCT ON ಮತ್ತು ವಿಂಡೋ ಕಾರ್ಯಗಳು!

SELECT
  (rec).pid id
, string_agg(chld::text, ',') chld
FROM
  tree
WHERE
  (rec).pid IS NOT NULL
GROUP BY 1 -- не работает!

ಮತ್ತು ಇದು ಹೇಗೆ ಕೆಲಸ ಮಾಡುತ್ತದೆ!

SELECT DISTINCT ON((rec).pid)
  (rec).pid id
, string_agg(chld::text, ',') OVER(PARTITION BY (rec).pid) chld
FROM
  tree
WHERE
  (rec).pid IS NOT NULL

ಸಂಖ್ಯಾತ್ಮಕ ಐಡಿಯನ್ನು ಏಕೆ ಪಠ್ಯವಾಗಿ ಪರಿವರ್ತಿಸಲಾಗಿದೆ ಎಂಬುದನ್ನು ನಾವು ಈಗ ನೋಡುತ್ತೇವೆ - ಇದರಿಂದ ಅವುಗಳನ್ನು ಅಲ್ಪವಿರಾಮದಿಂದ ಬೇರ್ಪಡಿಸಬಹುದು!

3 ಹೆಜ್ಜೆ

ಫೈನಲ್‌ಗೆ ನಮಗೆ ಏನೂ ಉಳಿದಿಲ್ಲ:

ನಾವು ಗುಂಪು ಮಾಡಿದ ID ಗಳ ಆಧಾರದ ಮೇಲೆ "ವಿಭಾಗ" ದಾಖಲೆಗಳನ್ನು ಓದುತ್ತೇವೆ
ನಾವು ಕಳೆಯಲಾದ ವಿಭಾಗಗಳನ್ನು ಮೂಲ ಹಾಳೆಗಳ "ಸೆಟ್" ಗಳೊಂದಿಗೆ ಹೋಲಿಸುತ್ತೇವೆ
ಬಳಸಿ ಸೆಟ್-ಸ್ಟ್ರಿಂಗ್ ಅನ್ನು "ವಿಸ್ತರಿಸು" unnest(string_to_array(chld, ',')::integer[])

WITH RECURSIVE tree AS (
  SELECT
    rec
  , id::text chld
  FROM
    hier rec
  WHERE
    id = ANY('{1,2,4,8,16,32,64,128,256,512,1024,2048,4096,8192}'::integer[])
UNION ALL
  (
    WITH prnt AS (
      SELECT DISTINCT ON((rec).pid)
        (rec).pid id
      , string_agg(chld::text, ',') OVER(PARTITION BY (rec).pid) chld
      FROM
        tree
      WHERE
        (rec).pid IS NOT NULL
    )
    , nodes AS (
      SELECT
        rec
      FROM
        hier rec
      WHERE
        id = ANY(ARRAY(
          SELECT
            id
          FROM
            prnt
        ))
    )
    SELECT
      nodes.rec
    , prnt.chld
    FROM
      prnt
    JOIN
      nodes
        ON (nodes.rec).id = prnt.id
  )
)
SELECT
  unnest(string_to_array(chld, ',')::integer[]) leaf
, (rec).*
FROM
  tree;

[explain.tensor.ru ನೋಡಿ]

ಮೂಲ: www.habr.com

PostgreSQL ಆಂಟಿಪ್ಯಾಟರ್ನ್ಸ್: ಮೊಲದ ರಂಧ್ರ ಎಷ್ಟು ಆಳವಾಗಿದೆ? ಕ್ರಮಾನುಗತ ಮೂಲಕ ಹೋಗೋಣ

#1. ಮೊಲದ ರಂಧ್ರ ಎಷ್ಟು ಆಳವಾಗಿದೆ?

ಇಂಡೆಕ್ಸ್ ಮೂಲಕ ಬೃಹತ್ ಪ್ರೂಫ್ ರೀಡಿಂಗ್

ನೋಡ್ ಚಿಹ್ನೆ

#2. ಬೇರುಗಳಿಗೆ ಹಿಂತಿರುಗಿ ನೋಡೋಣ

1 ಹೆಜ್ಜೆ

2 ಹೆಜ್ಜೆ

ಪುನರಾವರ್ತಿತ ಭಾಗದಲ್ಲಿ CTE

ಪುನರಾವರ್ತಿತ "ಟೇಬಲ್" ವಿರುದ್ಧ ನೆಸ್ಟೆಡ್ ಪ್ರಶ್ನೆ

ಆಂತರಿಕ ಪುನರಾವರ್ತನೆಯ ಮೂಲಕ ಗುಂಪು

3 ಹೆಜ್ಜೆ

ಕಾಮೆಂಟ್ ಅನ್ನು ಸೇರಿಸಿ Отменить ответ