PostgreSQL Antipatterns: насколько Π³Π»ΡƒΠ±ΠΎΠΊΠ° ΠΊΡ€ΠΎΠ»ΠΈΡ‡ΡŒΡ Π½ΠΎΡ€Π°? пробСТимся ΠΏΠΎ ΠΈΠ΅Ρ€Π°Ρ€Ρ…ΠΈΠΈ

Π’ слоТных ERP-систСмах ΠΌΠ½ΠΎΠ³ΠΈΠ΅ сущности ΠΈΠΌΠ΅ΡŽΡ‚ ΠΈΠ΅Ρ€Π°Ρ€Ρ…ΠΈΡ‡Π΅ΡΠΊΡƒΡŽ ΠΏΡ€ΠΈΡ€ΠΎΠ΄Ρƒ, ΠΊΠΎΠ³Π΄Π° ΠΎΠ΄Π½ΠΎΡ€ΠΎΠ΄Π½Ρ‹Π΅ ΠΎΠ±ΡŠΠ΅ΠΊΡ‚Ρ‹ Π²Ρ‹ΡΡ‚Ρ€Π°ΠΈΠ²Π°ΡŽΡ‚ΡΡ Π² Π΄Π΅Ρ€Π΅Π²ΠΎ ΠΎΡ‚Π½ΠΎΡˆΠ΅Π½ΠΈΠΉ Β«ΠΏΡ€Π΅Π΄ΠΎΠΊ β€” ΠΏΠΎΡ‚ΠΎΠΌΠΎΠΊΒ» β€” это ΠΈ организационная структура прСдприятия (всС эти Ρ„ΠΈΠ»ΠΈΠ°Π»Ρ‹, ΠΎΡ‚Π΄Π΅Π»Ρ‹ ΠΈ Ρ€Π°Π±ΠΎΡ‡ΠΈΠ΅ Π³Ρ€ΡƒΠΏΠΏΡ‹), ΠΈ ΠΊΠ°Ρ‚Π°Π»ΠΎΠ³ Ρ‚ΠΎΠ²Π°Ρ€ΠΎΠ², ΠΈ участки Ρ€Π°Π±ΠΎΡ‚, ΠΈ гСография Ρ‚ΠΎΡ‡Π΅ΠΊ ΠΏΡ€ΠΎΠ΄Π°ΠΆ,…

PostgreSQL Antipatterns: насколько Π³Π»ΡƒΠ±ΠΎΠΊΠ° ΠΊΡ€ΠΎΠ»ΠΈΡ‡ΡŒΡ Π½ΠΎΡ€Π°? пробСТимся ΠΏΠΎ ΠΈΠ΅Ρ€Π°Ρ€Ρ…ΠΈΠΈ

ЀактичСски, Π½Π΅Ρ‚ Π½ΠΈ ΠΎΠ΄Π½ΠΎΠΉ сфСры Π°Π²Ρ‚ΠΎΠΌΠ°Ρ‚ΠΈΠ·Π°Ρ†ΠΈΠΈ бизнСса, Π³Π΄Π΅ Ρ…ΠΎΡ‚ΡŒ ΠΊΠ°ΠΊΠΎΠΉ-Π½ΠΈΠ±ΡƒΠ΄ΡŒ ΠΈΠ΅Ρ€Π°Ρ€Ρ…ΠΈΠΈ Π΄Π° Π½Π΅ оказалось Π±Ρ‹ Π² Ρ€Π΅Π·ΡƒΠ»ΡŒΡ‚Π°Ρ‚Π΅. Но Π΄Π°ΠΆΠ΅ Ссли Π²Ρ‹ Π½Π΅ Ρ€Π°Π±ΠΎΡ‚Π°Π΅Ρ‚Π΅ Β«Π½Π° бизнСс», всС Ρ€Π°Π²Π½ΠΎ ΠΌΠΎΠΆΠ΅Ρ‚Π΅ Π»Π΅Π³ΠΊΠΎ ΡΡ‚ΠΎΠ»ΠΊΠ½ΡƒΡ‚ΡŒΡΡ с ΠΈΠ΅Ρ€Π°Ρ€Ρ…ΠΈΡ‡Π½Ρ‹ΠΌΠΈ связями. Π‘Π°Π½Π°Π»ΡŒΠ½ΠΎ, Π΄Π°ΠΆΠ΅ вашС гСнСалогичСскоС Π΄Ρ€Π΅Π²ΠΎ ΠΈΠ»ΠΈ поэтаТная схСма ΠΏΠΎΠΌΠ΅Ρ‰Π΅Π½ΠΈΠΉ Π² Ρ‚ΠΎΡ€Π³ΠΎΠ²ΠΎΠΌ Ρ†Π΅Π½Ρ‚Ρ€Π΅ β€” такая ΠΆΠ΅ структура.

БущСствуСт ΠΌΠ½ΠΎΠ³ΠΎ способов хранСния Ρ‚Π°ΠΊΠΎΠ³ΠΎ Π΄Π΅Ρ€Π΅Π²Π° Π² Π‘Π£Π‘Π”, Π½ΠΎ ΠΌΡ‹ сСгодня остановимся Ρ‚ΠΎΠ»ΡŒΠΊΠΎ Π½Π° ΠΎΠ΄Π½ΠΎΠΌ Π²Π°Ρ€ΠΈΠ°Π½Ρ‚Π΅:

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

CREATE INDEX ON hier(pid); -- Π½Π΅ Π·Π°Π±Ρ‹Π²Π°Π΅ΠΌ, Ρ‡Ρ‚ΠΎ FK Π½Π΅ ΠΏΠΎΠ΄Ρ€Π°Π·ΡƒΠΌΠ΅Π²Π°Π΅Ρ‚ автосозданиС индСкса, Π² ΠΎΡ‚Π»ΠΈΡ‡ΠΈΠ΅ ΠΎΡ‚ PK

И ΠΏΠΎΠΊΠ° Π²Ρ‹ Π²ΡΠΌΠ°Ρ‚Ρ€ΠΈΠ²Π°Π΅Ρ‚Π΅ΡΡŒ Π² Π³Π»ΡƒΠ±ΠΈΠ½Ρƒ ΠΈΠ΅Ρ€Π°Ρ€Ρ…ΠΈΠΈ, ΠΎΠ½Π° Ρ‚Π΅Ρ€ΠΏΠ΅Π»ΠΈΠ²ΠΎ ΠΆΠ΄Π΅Ρ‚, насколько ΠΆΠ΅ [Π½Π΅]эффСктивными окаТутся ваши Β«Π½Π°ΠΈΠ²Π½Ρ‹Π΅Β» способы Ρ€Π°Π±ΠΎΡ‚Ρ‹ с Ρ‚Π°ΠΊΠΎΠΉ структурой.

PostgreSQL Antipatterns: насколько Π³Π»ΡƒΠ±ΠΎΠΊΠ° ΠΊΡ€ΠΎΠ»ΠΈΡ‡ΡŒΡ Π½ΠΎΡ€Π°? пробСТимся ΠΏΠΎ ΠΈΠ΅Ρ€Π°Ρ€Ρ…ΠΈΠΈ
Π”Π°Π²Π°ΠΉΡ‚Π΅ Ρ€Π°Π·Π±Π΅Ρ€Π΅ΠΌ Ρ‚ΠΈΠΏΠΎΠ²Ρ‹Π΅ Π²ΠΎΠ·Π½ΠΈΠΊΠ°ΡŽΡ‰ΠΈΠ΅ Π·Π°Π΄Π°Ρ‡ΠΈ, ΠΈΡ… Ρ€Π΅Π°Π»ΠΈΠ·Π°Ρ†ΠΈΡŽ Π½Π° SQL ΠΈ ΠΏΠΎΠΏΡ€ΠΎΠ±ΡƒΠ΅ΠΌ ΡƒΠ»ΡƒΡ‡ΡˆΠΈΡ‚ΡŒ ΠΈΡ… ΠΏΡ€ΠΎΠΈΠ·Π²ΠΎΠ΄ΠΈΡ‚Π΅Π»ΡŒΠ½ΠΎΡΡ‚ΡŒ.

#1. Насколько Π³Π»ΡƒΠ±ΠΎΠΊΠ° ΠΊΡ€ΠΎΠ»ΠΈΡ‡ΡŒΡ Π½ΠΎΡ€Π°?

Π”Π°Π²Π°ΠΉΡ‚Π΅, для опрСдСлСнности, ΠΏΡ€ΠΈΠΌΠ΅ΠΌ, Ρ‡Ρ‚ΠΎ эта структура Ρƒ нас Π±ΡƒΠ΄Π΅Ρ‚ ΠΎΡ‚Ρ€Π°ΠΆΠ°Ρ‚ΡŒ ΠΏΠΎΠ΄Ρ‡ΠΈΠ½Π΅Π½Π½ΠΎΡΡ‚ΡŒ ΠΎΡ‚Π΄Π΅Π»ΠΎΠ² Π² структурС ΠΎΡ€Π³Π°Π½ΠΈΠ·Π°Ρ†ΠΈΠΈ: Π΄Π΅ΠΏΠ°Ρ€Ρ‚Π°ΠΌΠ΅Π½Ρ‚Ρ‹, Π΄ΠΈΠ²ΠΈΠ·ΠΈΠΎΠ½Ρ‹, сСкторы, Ρ„ΠΈΠ»ΠΈΠ°Π»Ρ‹, Ρ€Π°Π±ΠΎΡ‡ΠΈΠ΅ Π³Ρ€ΡƒΠΏΠΏΡ‹,… β€” ΠΊΠ°ΠΊ ΠΈΡ… Π½ΠΈ Π½Π°Π·ΠΎΠ²ΠΈ.
PostgreSQL Antipatterns: насколько Π³Π»ΡƒΠ±ΠΎΠΊΠ° ΠΊΡ€ΠΎΠ»ΠΈΡ‡ΡŒΡ Π½ΠΎΡ€Π°? пробСТимся ΠΏΠΎ ΠΈΠ΅Ρ€Π°Ρ€Ρ…ΠΈΠΈ

Π‘Π½Π°Ρ‡Π°Π»Π° Π½Π°Π³Π΅Π½Π΅Ρ€ΠΈΡ€ΡƒΠ΅ΠΌ нашС 'Π΄Π΅Ρ€Π΅Π²ΠΎ' ΠΈΠ· 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

Для этих запросов ΠΌΡ‹ воспользовались Ρ‚ΠΈΠΏΠΈΡ‡Π½Ρ‹ΠΌ рСкурсивным JOIN:
PostgreSQL Antipatterns: насколько Π³Π»ΡƒΠ±ΠΎΠΊΠ° ΠΊΡ€ΠΎΠ»ΠΈΡ‡ΡŒΡ Π½ΠΎΡ€Π°? пробСТимся ΠΏΠΎ ΠΈΠ΅Ρ€Π°Ρ€Ρ…ΠΈΠΈ

ΠžΡ‡Π΅Π²ΠΈΠ΄Π½ΠΎ, ΠΏΡ€ΠΈ Ρ‚Π°ΠΊΠΎΠΉ ΠΌΠΎΠ΄Π΅Π»ΠΈ запроса количСство ΠΈΡ‚Π΅Ρ€Π°Ρ†ΠΈΠΉ Π±ΡƒΠ΄Π΅Ρ‚ ΡΠΎΠ²ΠΏΠ°Π΄Π°Ρ‚ΡŒ с ΠΎΠ±Ρ‰ΠΈΠΌ количСством ΠΏΠΎΡ‚ΠΎΠΌΠΊΠΎΠ² (Π° ΠΈΡ… вСдь нСсколько дСсятков), ΠΈ Π·Π°Π½ΠΈΠΌΠ°Ρ‚ΡŒ это ΠΌΠΎΠΆΠ΅Ρ‚ достаточно сущСствСнныС рСсурсы, ΠΈ, ΠΊΠ°ΠΊ слСдствиС, врСмя.

ΠŸΡ€ΠΎΠ²Π΅Ρ€ΠΈΠΌ Π½Π° самом Β«ΡˆΠΈΡ€ΠΎΠΊΠΎΠΌΒ» ΠΏΠΎΠ΄Π΄Π΅Ρ€Π΅Π²Π΅:

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;

PostgreSQL Antipatterns: насколько Π³Π»ΡƒΠ±ΠΎΠΊΠ° ΠΊΡ€ΠΎΠ»ΠΈΡ‡ΡŒΡ Π½ΠΎΡ€Π°? пробСТимся ΠΏΠΎ ΠΈΠ΅Ρ€Π°Ρ€Ρ…ΠΈΠΈ
[ΠΏΠΎΡΠΌΠΎΡ‚Ρ€Π΅Ρ‚ΡŒ Π½Π° explain.tensor.ru]

Как ΠΈ ΠΏΡ€Π΅Π΄ΠΏΠΎΠ»Π°Π³Π°Π»ΠΈ, ΠΌΡ‹ нашли всС 30 записСй. Но ΠΏΠΎΡ‚Ρ€Π°Ρ‚ΠΈΠ»ΠΈ Π½Π° это 60% всСго Π²Ρ€Π΅ΠΌΠ΅Π½ΠΈ β€” ΠΏΠΎΡ‚ΠΎΠΌΡƒ Ρ‡Ρ‚ΠΎ сдСлали ΠΏΡ€ΠΈ этом ΠΈ 30 поисков ΠΏΠΎ индСксу. А мСньшС β€” ΠΌΠΎΠΆΠ½ΠΎ?

Массовая Π²Ρ‹Ρ‡ΠΈΡ‚ΠΊΠ° ΠΏΠΎ индСксу

А для ΠΊΠ°ΠΆΠ΄ΠΎΠ³ΠΎ Π»ΠΈ ΡƒΠ·Π»Π° Π½Π°ΠΌ Π½Π΅ΠΎΠ±Ρ…ΠΎΠ΄ΠΈΠΌΠΎ Π΄Π΅Π»Π°Ρ‚ΡŒ ΠΎΡ‚Π΄Π΅Π»ΡŒΠ½Ρ‹ΠΉ запрос ΠΊ индСксу? ΠžΠΊΠ°Π·Ρ‹Π²Π°Π΅Ρ‚ΡΡ, Π½Π΅Ρ‚ β€” Ρ‡ΠΈΡ‚Π°Ρ‚ΡŒ ΠΈΠ· индСкса ΠΌΡ‹ ΠΌΠΎΠΆΠ΅ΠΌ сразу ΠΏΠΎ нСскольким ΠΊΠ»ΡŽΡ‡Π°ΠΌ Π·Π° ΠΎΠ΄Π½ΠΎ ΠΎΠ±Ρ€Π°Ρ‰Π΅Π½ΠΈΠ΅ с ΠΏΠΎΠΌΠΎΡ‰ΡŒΡŽ = ANY(array).

А Π² ΠΊΠ°ΠΆΠ΄ΡƒΡŽ Ρ‚Π°ΠΊΡƒΡŽ Π³Ρ€ΡƒΠΏΠΏΡƒ ΠΈΠ΄Π΅Π½Ρ‚ΠΈΡ„ΠΈΠΊΠ°Ρ‚ΠΎΡ€ΠΎΠ² ΠΌΡ‹ ΠΌΠΎΠΆΠ΅ΠΌ Π²Π·ΡΡ‚ΡŒ всС Π½Π°ΠΉΠ΄Π΅Π½Π½Ρ‹Π΅ Π½Π° ΠΏΡ€Π΅Π΄Ρ‹Π΄ΡƒΡ‰Π΅ΠΌ шагС ID ΠΏΠΎ Β«ΡƒΠ·Π»Π°ΠΌΒ». Π’ΠΎ Π΅ΡΡ‚ΡŒ Π½Π° ΠΊΠ°ΠΆΠ΄ΠΎΠΌ ΡΠ»Π΅Π΄ΡƒΡŽΡ‰Π΅ΠΌ шагС ΠΌΡ‹ Π±ΡƒΠ΄Π΅ΠΌ ΠΈΡΠΊΠ°Ρ‚ΡŒ сразу Π²ΠΎΠΎΠ±Ρ‰Π΅ всСх ΠΏΠΎΡ‚ΠΎΠΌΠΊΠΎΠ² ΠΎΠΏΡ€Π΅Π΄Π΅Π»Π΅Π½Π½ΠΎΠ³ΠΎ уровня.

Волько, Π²ΠΎΡ‚ Π½Π΅Π·Π°Π΄Π°Ρ‡Π°, Π² рСкурсивной Π²Ρ‹Π±ΠΎΡ€ΠΊΠ΅ нСльзя ΠΎΠ±Ρ€Π°Ρ‚ΠΈΡ‚ΡŒΡΡ ΠΊ самой сСбС Π²ΠΎ Π²Π»ΠΎΠΆΠ΅Π½Π½ΠΎΠΌ запросС, Π° Π½Π°ΠΌ вСдь Π½Π°Π΄ΠΎ ΠΊΠ°ΠΊ-Ρ‚ΠΎ ΠΎΡ‚ΠΎΠ±Ρ€Π°Ρ‚ΡŒ ΠΈΠΌΠ΅Π½Π½ΠΎ Ρ‚ΠΎΠ»ΡŒΠΊΠΎ Π½Π°ΠΉΠ΄Π΅Π½Π½ΠΎΠ΅ Π½Π° ΠΏΡ€Π΅Π΄Ρ‹Π΄ΡƒΡ‰Π΅ΠΌ уровнС… ΠžΠΊΠ°Π·Ρ‹Π²Π°Π΅Ρ‚ΡΡ, ΡΠ΄Π΅Π»Π°Ρ‚ΡŒ Π²Π»ΠΎΠΆΠ΅Π½Π½Ρ‹ΠΉ запрос ΠΊΠΎ всСй Π²Ρ‹Π±ΠΎΡ€ΠΊΠ΅ β€” нСльзя, Π° Π²ΠΎΡ‚ ΠΊ Π΅Π΅ ΠΊΠΎΠ½ΠΊΡ€Π΅Ρ‚Π½ΠΎΠΌΡƒ полю β€” ΠΌΠΎΠΆΠ½ΠΎ. А это ΠΏΠΎΠ»Π΅ ΠΌΠΎΠΆΠ΅Ρ‚ Π±Ρ‹Ρ‚ΡŒ ΠΈ массивом β€” Ρ‡Ρ‚ΠΎ Π½Π°ΠΌ ΠΈ Π½ΡƒΠΆΠ½ΠΎ для использования ANY.

Π—Π²ΡƒΡ‡ΠΈΡ‚ нСсколько Π΄ΠΈΠΊΠΎ, Π½ΠΎ Π½Π° схСмС β€” всС просто.

PostgreSQL Antipatterns: насколько Π³Π»ΡƒΠ±ΠΎΠΊΠ° ΠΊΡ€ΠΎΠ»ΠΈΡ‡ΡŒΡ Π½ΠΎΡ€Π°? пробСТимся ΠΏΠΎ ΠΈΠ΅Ρ€Π°Ρ€Ρ…ΠΈΠΈ

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;

PostgreSQL Antipatterns: насколько Π³Π»ΡƒΠ±ΠΎΠΊΠ° ΠΊΡ€ΠΎΠ»ΠΈΡ‡ΡŒΡ Π½ΠΎΡ€Π°? пробСТимся ΠΏΠΎ ΠΈΠ΅Ρ€Π°Ρ€Ρ…ΠΈΠΈ
[ΠΏΠΎΡΠΌΠΎΡ‚Ρ€Π΅Ρ‚ΡŒ Π½Π° explain.tensor.ru]

И Ρ‚ΡƒΡ‚ самым Π²Π°ΠΆΠ½Ρ‹ΠΌ являСтся Π΄Π°ΠΆΠ΅ Π½Π΅ Π²Ρ‹ΠΈΠ³Ρ€Ρ‹Ρˆ Π² 1.5 Ρ€Π°Π·Π° ΠΏΠΎ Π²Ρ€Π΅ΠΌΠ΅Π½ΠΈ, Π° Ρ‡Ρ‚ΠΎ ΠΌΡ‹ Π²Ρ‹Ρ‡ΠΈΡ‚Π°Π»ΠΈ мСньшС buffers, ΠΏΠΎΡΠΊΠΎΠ»ΡŒΠΊΡƒ ΠΎΠ±Ρ€Π°Ρ‰Π΅Π½ΠΈΠΉ ΠΊ индСксу Ρƒ нас всСго 5 вмСсто 30!

Π”ΠΎΠΏΠΎΠ»Π½ΠΈΡ‚Π΅Π»ΡŒΠ½Ρ‹ΠΌ бонусом ΠΈΠ΄Π΅Ρ‚ Ρ‚ΠΎΡ‚ Ρ„Π°ΠΊΡ‚, Ρ‡Ρ‚ΠΎ послС Ρ„ΠΈΠ½Π°Π»ΡŒΠ½ΠΎΠ³ΠΎ unnest ΠΈΠ΄Π΅Π½Ρ‚ΠΈΡ„ΠΈΠΊΠ°Ρ‚ΠΎΡ€Ρ‹ останутся упорядочСными ΠΏΠΎ «уровням».

ΠŸΡ€ΠΈΠ·Π½Π°ΠΊ ΡƒΠ·Π»Π°

Π‘Π»Π΅Π΄ΡƒΡŽΡ‰Π΅Π΅ сообраТСниС, ΠΊΠΎΡ‚ΠΎΡ€ΠΎΠ΅ ΠΏΠΎΠΌΠΎΠΆΠ΅Ρ‚ ΡƒΠ»ΡƒΡ‡ΡˆΠΈΡ‚ΡŒ ΠΏΡ€ΠΎΠΈΠ·Π²ΠΎΠ΄ΠΈΡ‚Π΅Π»ΡŒΠ½ΠΎΡΡ‚ΡŒ β€” Ρƒ Β«Π»ΠΈΡΡ‚ΡŒΠ΅Π²Β» Π½Π΅ ΠΌΠΎΠΆΠ΅Ρ‚ Π±Ρ‹Ρ‚ΡŒ Π΄Π΅Ρ‚Π΅ΠΉ, Ρ‚ΠΎ Π΅ΡΡ‚ΡŒ для Π½ΠΈΡ… ΠΈΡΠΊΠ°Ρ‚ΡŒ Β«Π²Π½ΠΈΠ·Β» Π²ΠΎΠΎΠ±Ρ‰Π΅ Π½ΠΈΡ‡Π΅Π³ΠΎ Π½Π΅ Π½Π°Π΄ΠΎ. Π’ постановкС нашСй Π·Π°Π΄Π°Ρ‡ΠΈ это ΠΎΠ·Π½Π°Ρ‡Π°Π΅Ρ‚, Ρ‡Ρ‚ΠΎ Ссли ΠΌΡ‹ шли ΠΏΠΎ Ρ†Π΅ΠΏΠΎΡ‡ΠΊΠ΅ ΠΎΡ‚Π΄Π΅Π»ΠΎΠ² ΠΈ дошли Π΄ΠΎ сотрудника, Ρ‚ΠΎ дальшС ΠΏΠΎ этой Π²Π΅Ρ‚ΠΊΠ΅ ΠΈΡΠΊΠ°Ρ‚ΡŒ ΡƒΠΆΠ΅ Π½Π΅Π·Π°Ρ‡Π΅ΠΌ.

Π”Π°Π²Π°ΠΉΡ‚Π΅ Π²Π²Π΅Π΄Π΅ΠΌ Π² Π½Π°ΡˆΡƒ Ρ‚Π°Π±Π»ΠΈΡ†Ρƒ Π΄ΠΎΠΏΠΎΠ»Π½ΠΈΡ‚Π΅Π»ΡŒΠ½ΠΎΠ΅ 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, Ρ‡Ρ‚ΠΎ ΠΏΠΎΠ·Π²ΠΎΠ»ΠΈΡ‚ Π½Π°ΠΌ сразу ΠΎΠ±Ρ€Π°Ρ‚ΠΈΡ‚ΡŒΡΡ ΠΊ полям рСкурсивной Β«Ρ‚Π°Π±Π»ΠΈΡ†Ρ‹Β», Π° Π°Π³Ρ€Π΅Π³Π°Ρ‚Π½ΡƒΡŽ Ρ„ΡƒΠ½ΠΊΡ†ΠΈΡŽ с условиСм Ρ„ΠΈΠ»ΡŒΡ‚Ρ€Π°Ρ†ΠΈΠΈ ΠΏΠΎ ΠΏΡ€ΠΈΠ·Π½Π°ΠΊΡƒ ΡƒΠ·Π»Π° ΠΈΡΠΏΠΎΠ»ΡŒΠ·ΡƒΠ΅ΠΌ для ΡƒΠΌΠ΅Π½ΡŒΡˆΠ΅Π½ΠΈΡ Π½Π°Π±ΠΎΡ€Π° ΠΊΠ»ΡŽΡ‡Π΅ΠΉ:

PostgreSQL Antipatterns: насколько Π³Π»ΡƒΠ±ΠΎΠΊΠ° ΠΊΡ€ΠΎΠ»ΠΈΡ‡ΡŒΡ Π½ΠΎΡ€Π°? пробСТимся ΠΏΠΎ ΠΈΠ΅Ρ€Π°Ρ€Ρ…ΠΈΠΈ

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;

PostgreSQL Antipatterns: насколько Π³Π»ΡƒΠ±ΠΎΠΊΠ° ΠΊΡ€ΠΎΠ»ΠΈΡ‡ΡŒΡ Π½ΠΎΡ€Π°? пробСТимся ΠΏΠΎ ΠΈΠ΅Ρ€Π°Ρ€Ρ…ΠΈΠΈ
[ΠΏΠΎΡΠΌΠΎΡ‚Ρ€Π΅Ρ‚ΡŒ Π½Π° explain.tensor.ru]

ΠœΡ‹ смогли ΡΠΎΠΊΡ€Π°Ρ‚ΠΈΡ‚ΡŒ Π΅Ρ‰Π΅ ΠΎΠ΄Π½ΠΎ ΠΎΠ±Ρ€Π°Ρ‰Π΅Π½ΠΈΠ΅ ΠΊ индСксу ΠΈ Π²Ρ‹ΠΈΠ³Ρ€Π°Π»ΠΈ Π±ΠΎΠ»Π΅Π΅ Ρ‡Π΅ΠΌ Π² 2 Ρ€Π°Π·Π° ΠΏΠΎ ΠΎΠ±ΡŠΠ΅ΠΌΡƒ Π²Ρ‹Ρ‡ΠΈΡ‚Ρ‹Π²Π°Π΅ΠΌΠΎΠ³ΠΎ.

#2. ВСрнСмся ΠΊ корням

Π­Ρ‚ΠΎΡ‚ Π°Π»Π³ΠΎΡ€ΠΈΡ‚ΠΌ Π±ΡƒΠ΄Π΅Ρ‚ ΠΏΠΎΠ»Π΅Π·Π΅Π½, Ссли Π²Π°ΠΌ Π½Π΅ΠΎΠ±Ρ…ΠΎΠ΄ΠΈΠΌΠΎ ΡΠΎΠ±Ρ€Π°Ρ‚ΡŒ записи для всСх элСмСнтов Β«Π²Π²Π΅Ρ€Ρ… ΠΏΠΎ Π΄Π΅Ρ€Π΅Π²ΡƒΒ», сохранив ΠΏΡ€ΠΈ этом ΠΈΠ½Ρ„ΠΎΡ€ΠΌΠ°Ρ†ΠΈΡŽ, ΠΊΠ°ΠΊΠΈΠΌ исходным листом (ΠΈ с ΠΊΠ°ΠΊΠΈΠΌΠΈ показатСлями) Π²Ρ‹Π·Π²Π°Π½ΠΎ Π΅Π³ΠΎ ΠΏΠΎΠΏΠ°Π΄Π°Π½ΠΈΠ΅ Π² Π²Ρ‹Π±ΠΎΡ€ΠΊΡƒ β€” Π½Π°ΠΏΡ€ΠΈΠΌΠ΅Ρ€, для формирования сводного ΠΎΡ‚Ρ‡Π΅Ρ‚Π° с Π°Π³Ρ€Π΅Π³Π°Ρ†ΠΈΠ΅ΠΉ Π½Π° ΡƒΠ·Π»Ρ‹.

PostgreSQL Antipatterns: насколько Π³Π»ΡƒΠ±ΠΎΠΊΠ° ΠΊΡ€ΠΎΠ»ΠΈΡ‡ΡŒΡ Π½ΠΎΡ€Π°? пробСТимся ΠΏΠΎ ΠΈΠ΅Ρ€Π°Ρ€Ρ…ΠΈΠΈ
Π”Π°Π»ΡŒΠ½Π΅ΠΉΡˆΠ΅Π΅ стоит Π²ΠΎΡΠΏΡ€ΠΈΠ½ΠΈΠΌΠ°Ρ‚ΡŒ ΠΈΡΠΊΠ»ΡŽΡ‡ΠΈΡ‚Π΅Π»ΡŒΠ½ΠΎ ΠΊΠ°ΠΊ proof-of-concept, ΠΏΠΎΡΠΊΠΎΠ»ΡŒΠΊΡƒ запрос получаСтся ΠΎΡ‡Π΅Π½ΡŒ ΡƒΠΆ Π³Ρ€ΠΎΠΌΠΎΠ·Π΄ΠΊΠΈΠΉ. Но Ссли ΠΎΠ½ Π΄ΠΎΠΌΠΈΠ½ΠΈΡ€ΡƒΠ΅Ρ‚ Π² вашСй Π±Π°Π·Π΅ β€” стоит Π·Π°Π΄ΡƒΠΌΠ°Ρ‚ΡŒΡΡ Π½Π°Π΄ ΠΏΡ€ΠΈΠΌΠ΅Π½Π΅Π½ΠΈΠ΅ΠΌ ΠΏΠΎΠ΄ΠΎΠ±Π½Ρ‹Ρ… ΠΌΠ΅Ρ‚ΠΎΠ΄ΠΈΠΊ.

НачнСм с ΠΏΠ°Ρ€Ρ‹ простых ΡƒΡ‚Π²Π΅Ρ€ΠΆΠ΄Π΅Π½ΠΈΠΉ:

  • ΠžΠ΄Π½Ρƒ ΠΈ Ρ‚Ρƒ ΠΆΠ΅ запись ΠΈΠ· Π±Π°Π·Ρ‹ Π»ΡƒΡ‡ΡˆΠ΅ Ρ‡ΠΈΡ‚Π°Ρ‚ΡŒ всСго ΠΎΠ΄ΠΈΠ½ Ρ€Π°Π·.
  • Записи ΠΈΠ· Π±Π°Π·Ρ‹ эффСктивнСС Ρ‡ΠΈΡ‚Π°Ρ‚ΡŒ Β«ΠΏΠ°Ρ‡ΠΊΠΎΠΉΒ», Ρ‡Π΅ΠΌ ΠΏΠΎΠΎΠ΄ΠΈΠ½ΠΎΡ‡ΠΊΠ΅.

Π’Π΅ΠΏΠ΅Ρ€ΡŒ ΠΏΠΎΠΏΡ€ΠΎΠ±ΡƒΠ΅ΠΌ ΡΠΊΠΎΠ½ΡΡ‚Ρ€ΡƒΠΈΡ€ΠΎΠ²Π°Ρ‚ΡŒ Π½ΡƒΠΆΠ½Ρ‹ΠΉ Π½Π°ΠΌ запрос.

Π¨Π°Π³ 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 Ρ€Π°Π·Π΄Π΅Π»ΠΎΠ², ΠΊΠΎΡ‚ΠΎΡ€Ρ‹Π΅ Π½Π°Π΄ΠΎ Π±ΡƒΠ΄Π΅Ρ‚ Π²Ρ‹Ρ‡ΠΈΡ‚Π°Ρ‚ΡŒ дальшС. ΠŸΠΎΡ‡Ρ‚ΠΈ всСгда ΠΎΠ½ΠΈ Π±ΡƒΠ΄ΡƒΡ‚ Π΄ΡƒΠ±Π»ΠΈΡ€ΠΎΠ²Π°Ρ‚ΡŒΡΡ Ρƒ Ρ€Π°Π·Π½Ρ‹Ρ… записСй исходного Π½Π°Π±ΠΎΡ€Π° β€” поэтому Π½Π°ΠΌ Π±Ρ‹ ΠΈΡ… ΡΠ³Ρ€ΡƒΠΏΠΏΠΈΡ€ΠΎΠ²Π°Ρ‚ΡŒ, сохранив ΠΏΡ€ΠΈ этом ΠΈΠ½Ρ„ΠΎΡ€ΠΌΠ°Ρ†ΠΈΡŽ ΠΎ Π»ΠΈΡΡ‚ΡŒΡΡ…-источниках.

Но Ρ‚ΡƒΡ‚ нас ΠΏΠΎΠ΄ΠΆΠΈΠ΄Π°ΡŽΡ‚ Ρ‚Ρ€ΠΈ нСприятности:

  1. Β«ΠŸΠΎΠ΄Ρ€Π΅ΠΊΡƒΡ€ΡΠΈΠ²Π½Π°ΡΒ» Ρ‡Π°ΡΡ‚ΡŒ запроса Π½Π΅ ΠΌΠΎΠΆΠ΅Ρ‚ ΡΠΎΠ΄Π΅Ρ€ΠΆΠ°Ρ‚ΡŒ Π°Π³Ρ€Π΅Π³Π°Ρ‚Π½Ρ‹Ρ… Ρ„ΡƒΠ½ΠΊΡ†ΠΈΠΉ с GROUP BY.
  2. ΠžΠ±Ρ€Π°Ρ‰Π΅Π½ΠΈΠ΅ ΠΊ рСкурсивной Β«Ρ‚Π°Π±Π»ΠΈΡ†Π΅Β» Π½Π΅ ΠΌΠΎΠΆΠ΅Ρ‚ Π½Π°Ρ…ΠΎΠ΄ΠΈΡ‚ΡŒΡΡ Π²ΠΎ Π²Π»ΠΎΠΆΠ΅Π½Π½ΠΎΠΌ подзапросС.
  3. Запрос Π² рСкурсивной части Π½Π΅ ΠΌΠΎΠΆΠ΅Ρ‚ ΡΠΎΠ΄Π΅Ρ€ΠΆΠ°Ρ‚ΡŒ CTE.

К ΡΡ‡Π°ΡΡ‚ΡŒΡŽ, всС эти ΠΏΡ€ΠΎΠ±Π»Π΅ΠΌΡ‹ достаточно Π»Π΅Π³ΠΊΠΎ обходятся. НачнСм с ΠΊΠΎΠ½Ρ†Π°.

CTE Π² рСкурсивной части

Π’ΠΎΡ‚ Ρ‚Π°ΠΊ Π½Π΅ Ρ€Π°Π±ΠΎΡ‚Π°Π΅Ρ‚:

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

А Ρ‚Π°ΠΊ β€” Ρ€Π°Π±ΠΎΡ‚Π°Π΅Ρ‚, скобочки Ρ€Π΅ΡˆΠ°ΡŽΡ‚!

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

Π’Π»ΠΎΠΆΠ΅Π½Π½Ρ‹ΠΉ запрос ΠΊ рСкурсивной Β«Ρ‚Π°Π±Π»ΠΈΡ†Π΅Β»

Π₯м… ΠžΠ±Ρ€Π°Ρ‰Π΅Π½ΠΈΠ΅ ΠΊ рСкурсивной CTE Π½Π΅ ΠΌΠΎΠΆΠ΅Ρ‚ Π±Ρ‹Ρ‚ΡŒ Π²ΠΎ Π²Π»ΠΎΠΆΠ΅Π½Π½ΠΎΠΌ запросС. Но ΠΎΠ½ΠΎ ΠΌΠΎΠΆΠ΅Ρ‚ Π±Ρ‹Ρ‚ΡŒ Π²Π½ΡƒΡ‚Ρ€ΠΈ CTE! А Π²Π»ΠΎΠΆΠ΅Π½Π½Ρ‹ΠΉ запрос ΠΌΠΎΠΆΠ΅Ρ‚ ΠΎΠ±Ρ€Π°Ρ‰Π°Ρ‚ΡŒΡΡ ΡƒΠΆΠ΅ ΠΊ этой CTE!

GROUP BY Π²Π½ΡƒΡ‚Ρ€ΠΈ рСкурсии

НСприятно, но… Π£ нас ΠΆΠ΅ Π΅ΡΡ‚ΡŒ простой способ, ΠΊΠ°ΠΊ ΠΌΠΎΠΆΠ½ΠΎ ΡΡΠΌΡƒΠ»ΠΈΡ€ΠΎΠ²Π°Ρ‚ΡŒ GROUP BY с ΠΏΠΎΠΌΠΎΡ‰ΡŒΡŽ 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

Π’ΠΎΡ‚ Ρ‚Π΅ΠΏΠ΅Ρ€ΡŒ ΠΌΡ‹ Π²ΠΈΠ΄ΠΈΠΌ, Π·Π°Ρ‡Π΅ΠΌ числовой ID прСвращался Π² тСкст β€” Ρ‡Ρ‚ΠΎΠ±Ρ‹ ΠΈΡ… ΠΌΠΎΠΆΠ½ΠΎ Π±Ρ‹Π»ΠΎ ΡΠΊΠ»Π΅ΠΈΠ²Π°Ρ‚ΡŒ Ρ‡Π΅Ρ€Π΅Π· Π·Π°ΠΏΡΡ‚ΡƒΡŽ!

Π¨Π°Π³ 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;

PostgreSQL Antipatterns: насколько Π³Π»ΡƒΠ±ΠΎΠΊΠ° ΠΊΡ€ΠΎΠ»ΠΈΡ‡ΡŒΡ Π½ΠΎΡ€Π°? пробСТимся ΠΏΠΎ ΠΈΠ΅Ρ€Π°Ρ€Ρ…ΠΈΠΈ
[ΠΏΠΎΡΠΌΠΎΡ‚Ρ€Π΅Ρ‚ΡŒ Π½Π° explain.tensor.ru]

Π˜ΡΡ‚ΠΎΡ‡Π½ΠΈΠΊ: habr.com

Π”ΠΎΠ±Π°Π²ΠΈΡ‚ΡŒ ΠΊΠΎΠΌΠΌΠ΅Π½Ρ‚Π°Ρ€ΠΈΠΉ