Sql tips and tricks

Совместные агрегаты

->  $1 = '{2,3,5,7,11,13,17,19}'<-  count | sum      8 |  77

SELECT  count(*), sum(prime)FROM  unnest('{2,3,5,7,11,13,17,19}'::integer[]) prime;

Несовместимые агрегаты

->  $1 = '{2,3,5,7,11,13,17,19}'<-  countlt | countgt        4 |       4

Вложенные запросы

WITH src AS (  SELECT unnest('{2,3,5,7,11,13,17,19}'::integer[]) prime) SELECT  (SELECT count(*) FROM src WHERE prime < 10) countlt, (SELECT count(*) FROM src WHERE prime > 10) countgt;

FILTER-агрегаты

SELECT  count(*) FILTER(WHERE prime < 10) countlt, count(*) FILTER(WHERE prime > 10) countgtFROM  unnest('{2,3,5,7,11,13,17,19}'::integer[]) prime;

Агрегаты от условия

SELECT  sum(CASE WHEN prime < 10 THEN 1 ELSE 0 END) countlt, sum(CASE WHEN prime > 10 THEN 1 ELSE 0 END) countgtFROM  unnest('{2,3,5,7,11,13,17,19}'::integer[]) prime;

SELECT  sum((prime < 10)::integer) countlt, sum((prime > 10)::integer) countgtFROM  unnest('{2,3,5,7,11,13,17,19}'::integer[]) prime;

Агрегация в массив

->  $1 = '{2,3,5,7,11,13,17,19}'<-   primeslt |   primesgt  {2,3,5,7} | {11,13,17,19}

WITH src AS (  SELECT unnest('{2,3,5,7,11,13,17,19}'::integer[]) prime)SELECT  array_agg(prime) FILTER(WHERE prime < 10) primeslt, array_agg(prime) FILTER(WHERE prime > 10) primesgtFROM  src;

WITH src AS (  SELECT unnest('{2,3,5,7,11,13,17,19}'::integer[]) prime) , tmp AS (  SELECT    array_agg(CASE WHEN prime < 10 THEN prime END) primeslt -- {2,3,5,7,NULL,NULL,NULL,NULL}  , array_agg(CASE WHEN prime > 10 THEN prime END) primesgt -- {NULL,NULL,NULL,NULL,11,13,17,19}  FROM    src)SELECT  ARRAY(SELECT * FROM unnest(primeslt) prime WHERE prime IS NOT NULL) primeslt, ARRAY(SELECT * FROM unnest(primesgt) prime WHERE prime IS NOT NULL) primesgtFROM  tmp;

WITH src AS (  SELECT unnest('{2,3,5,7,11,13,17,19}'::integer[]) prime) SELECT  array_remove(array_agg(CASE WHEN prime < 10 THEN prime END), NULL) primeslt, array_remove(array_agg(CASE WHEN prime > 10 THEN prime END), NULL) primesgtFROM  src;

Несколько агрегатов: Function Scan vs CTE

SELECT  array_agg(i) FILTER(WHERE i % 2 = 0) even, array_agg(i) FILTER(WHERE i % 2 = 1) oddFROM  generate_series(1, 1000000) i;

WITH src AS (  SELECT generate_series(1, 1000000) i)SELECT  array_agg(i) FILTER(WHERE i % 2 = 0) even, array_agg(i) FILTER(WHERE i % 2 = 1) oddFROM  src;

DISTINCT + OVER

WITH src AS (  SELECT unnest('{2,3,5,7,11,13,17,19}'::integer[]) prime)SELECT DISTINCT  array_agg(prime) FILTER(WHERE prime < 10) OVER() primeslt, array_agg(prime) FILTER(WHERE prime > 10) OVER() primesgtFROM  src;

Сложный агрегат

->  $1 = '{2,3,5,7,11,13,17,19}'<-                 exp                  |   val  2 * 3 * 5 * 7 * 11 * 13 * 17 * 19 = | 9699690

WITH RECURSIVE src AS (  SELECT    *  FROM    unnest('{2,3,5,7,11,13,17,19}'::integer[])      WITH ORDINALITY T(prime, rn)), T(rn, exp, val) AS (  SELECT    0::bigint    -- база агрегации  , '{}'::integer[]  , 1UNION ALL  SELECT    src.rn    -- итеративное вычисление сразу всех агрегатов  , exp || src.prime  , val * src.prime   FROM    T  JOIN    src      ON src.rn = T.rn + 1 -- переход к следующей записи)SELECT  array_to_string(exp, ' * ') || ' = ' exp, valFROM  TORDER BY -- отбор финального результата  rn DESCLIMIT 1;

WITH RECURSIVE src AS (  SELECT '{2,3,5,7,11,13,17,19}'::integer[] arr), T(i, exp, val) AS (  SELECT    1::bigint    -- база агрегации  , '{}'::integer[]  , 1UNION ALL  SELECT    i + 1    -- итеративное вычисление сразу всех агрегатов  , exp || arr[i]  , val * arr[i]  FROM    T  , src  WHERE    i <= array_length(arr, 1))SELECT  array_to_string(exp, ' * ') || ' = ' exp, valFROM  TORDER BY -- отбор финального результата  i DESCLIMIT 1;

Math.bonus

WITH src AS (  SELECT unnest('{2,3,5,7,11,13,17,19}'::integer[]) prime)SELECT  string_agg(prime::text, ' * ') || ' = ' exp, exp(sum(ln(prime)))::integer val -- для любителей математикиFROM  src;

Сладкая парочка: JOIN + DISTINCT

SELECT DISTINCT  X.*FROM  XJOIN  Y    ON Y.fk = X.pkWHERE  Y.bool_condition;

Вложенный EXISTS

SELECT  *FROM  XWHERE  EXISTS(    SELECT      NULL    FROM      Y    WHERE      fk = X.pk AND      bool_condition    LIMIT 1  );

LATERAL JOIN

SELECT  X.*FROM  X, LATERAL (    SELECT      Y.*    FROM      Y    WHERE      fk = X.pk AND      bool_condition    LIMIT 1  ) YWHERE  Y IS DISTINCT FROM NULL;

Этот же вариант позволяет при необходимости заодно сразу вернуть какие-то данные из нашедшейся связанной Y-записи. Похожий вариант рассмотрен в статье PostgreSQL Antipatterns: редкая запись долетит до середины JOIN.

Зачем платить больше: DISTINCT [ON] + LIMIT 1

SELECT DISTINCT ON(X.pk)  *FROM  XJOIN  Y    ON Y.fk = X.pkLIMIT 1;

• соединяем таблички
• уникализируем по X.pk
• из оставшихся записей выбираем какую-то одну

SELECT  *FROM  (    SELECT      *    FROM      X    -- сюда можно подсунуть подходящих условий    LIMIT 1 -- +1 Limit  ) XJOIN  Y    ON Y.fk = X.pkLIMIT 1;

Мне только спросить: неявный GROUP + LIMIT

...CASE  WHEN (    SELECT      count(*)    FROM      X    LIMIT 1  ) = 0 THEN ...

• посчитай, что хотят по всем записям
• отдай столько строк, сколько просят

• (count + LIMIT 1) = 0 на NOT EXISTS(LIMIT 1)
• (count + LIMIT 1) > 0 на EXISTS(LIMIT 1)
• count >= N на (SELECT count(*) FROM (... LIMIT N))

Сколько вешать в граммах: DISTINCT + LIMIT

SELECT DISTINCT  pkFROM  XLIMIT $1

Определяем регион по номеру

Собираем телефонные коды

const async = require('async')  , request = require('request');const fs = require('fs');let queue = [  'ABC-3xx', 'ABC-4xx', 'ABC-8xx', 'DEF-9xx']  .map(key => (    {      base : 'https://rossvyaz.gov.ru'    , path : `/data/${key}.csv`    }  ));let ranges = [];async.doWhilst(  cb => {    // берем из очереди и загружаем очередную страницу    let task = queue.shift();    request(      {        url  : task.base + task.path      , pool : false      }    , (err, res, body) => {        // примитивный разбор CSV        body.split('\n').forEach(line => {          let tds = line.split(';');          let place = tds[5].split('|');          ranges.push([            tds[0]          , tds[1]          , tds[2]          , tds[4]          , place[place.length - 1]          , place[place.length - 2] && place[place.length - 2].startsWith('р-н') ? place[place.length - 2] : ''          , place.length > 1            ? place[0].startsWith('р-н')              ? ''              : place[0]            : ''          ]);        });        return cb(err);      }    );  }  // итерируем, пока очередь заданий непуста, cb => {    return cb(null, queue.length);  }  // когда все распарсили - подчищаем данные и формируем файл для загрузки в БД, err => {    // чистим коды и диапазоны    ranges.forEach(row => {      // убираем пересечение цифр кода и диапазона      let ln = row[0].length + row[1].length - 10;      if (ln > 0) {        let sfx = row[0].slice(-ln);        if (row[1].startsWith(sfx) && row[2].startsWith(sfx)) {          row[1] = row[1].slice(ln);          row[2] = row[2].slice(ln);        }      }      // пересобираем общий префикс      let pfx;      for (let i = 1; i < row[1].length; i++) {        if (row[2].startsWith(row[1].slice(0, i))) {          pfx = row[1].slice(0, i);        }        else {          break;        }      }      if (pfx) {        row[0] = row[0] + pfx;        row[1] = row[1].slice(pfx.length);        row[2] = row[2].slice(pfx.length);      }    });    let sql = `SET client_encoding = 'UTF-8';CREATE TABLE phonecodes(  code    varchar, numb    varchar, nume    varchar, oper    varchar, region    varchar, district    varchar, city    varchar);COPY phonecodes FROM STDIN;`;    // собираем COPY-формат    let copy = ranges.map(row => row.join('\t')).join('\n') + '\n\\.\n';    fs.writeFileSync('phonecodes.sql', sql + copy);  });

psql -f phonecodes.sql -U postgres tst

SETCREATE TABLECOPY 377937

Замечу, что в нашем примере и код, и граничные номера диапазона представлены строками. Да, их можно превратить в integer/bigint, но мы пока не будем этим заниматься. Тем более, что входящий номер телефона не всегда состоит только из цифр например, некоторые таксофоны могут сообщать свой номер с цифрой A.

Ищут пожарные, ищет милиция...

WITH src AS (  SELECT '4852262000' num -- входящий номер)SELECT  *FROM  src, phonecodesWHERE  num LIKE (code || '%') AND -- проверяем совпадение кода  num BETWEEN (code || numb) AND (code || nume) -- проверяем вхождение в диапазонLIMIT 1;

num        | code   | numb | nume | oper | region           | district | city-----------------------------------------------------------------------------------4852262000 | 485226 | 0000 | 9999 | МТС  | Ярославская обл. |          | Ярославль

Используем знания о прикладной области

SELECT -- сколько кодов с таким кол-вом диапазонов  ranges, count(*)FROM  (    SELECT -- сколько диапазонов по каждому коду      code    , count(*) ranges    FROM      phonecodes    GROUP BY      1  ) TGROUP BY  1ORDER BY  1 DESC;

ranges | count--------------    10 |   121     9 |   577     8 |  1705     7 |  3556     6 |  6667     5 | 10496     4 | 12491     3 | 20283     2 | 22627     1 | 84453

CREATE INDEX ON phonecodes(code);CLUSTER phonecodes USING phonecodes_code_idx;

WITH RECURSIVE src AS (  SELECT '4852262000' num), T AS (  SELECT    num pfx -- в качестве исходного "префикса" задаем весь номер  , NULL::phonecodes pc  FROM    srcUNION ALL  SELECT    substr(pfx, 1, length(pfx) - 1) -- "отщипываем" последнюю цифру  , (      SELECT        X      FROM        phonecodes X      WHERE        code = T.pfx AND -- проверяем полное совпадение префикса        (TABLE src) BETWEEN (code || numb) AND (code || nume) -- проверяем вхождение в диапазон      LIMIT 1    ) pc  FROM    T  WHERE    pc IS NOT DISTINCT FROM NULL AND -- ищем, пока ничего не нашли    length(pfx) > 2 -- ... и префикс еще может оказаться кодом)SELECT  (pc).* -- "разворачиваем" найденную запись диапазона в поляFROM  TWHERE  pc IS DISTINCT FROM NULL;

А можно делать еще меньше итераций по индексу если все коды заранее привести к классическому виду от 3 до 5 цифр. Правда, тогда возрастет количество диапазонов в каждом коде, и их фильтрация может добавить проблем.

Определяем клиента по номеру

АТС дает полный номер

АТС дает городской номер

пришло от АТС      :     262000указано в карточке : 4852262000

    reverse(262000) -> 000262reverse(4852262000) -> 0002622584

CREATE INDEX ON client(reverse(phone) varchar_pattern_ops);

SELECT  *FROM  clientWHERE  reverse(phone) LIKE (reverse($1) || '%');

Не писать рекурсивные запросы

Использовать принципиально другой подход к задаче

WITH RECURSIVE src AS (  SELECT '{2,3,5,7,11,13,17,19}'::integer[] arr), T(i, val) AS (  SELECT    1::bigint  , 1UNION ALL  SELECT    i + 1  , val * arr[i]  FROM    T  , src  WHERE    i <= array_length(arr, 1))SELECT  valFROM  TORDER BY -- отбор финального результата  i DESCLIMIT 1;

WITH src AS (  SELECT unnest('{2,3,5,7,11,13,17,19}'::integer[]) prime)SELECT  exp(sum(ln(prime)))::integer valFROM  src;

Использовать generate_series вместо циклов

WITH RECURSIVE T AS (  SELECT 'abcdefgh' strUNION ALL  SELECT    substr(str, 1, length(str) - 1)  FROM    T  WHERE    length(str) > 1)TABLE T;

SELECT  substr(str, 1, ln) strFROM  (VALUES('abcdefgh')) T(str), LATERAL(    SELECT generate_series(length(str), 1, -1) ln  ) X;

Изменить структуру БД

CREATE TABLE message(  message_id    uuid      PRIMARY KEY, reply_to    uuid      REFERENCES message, body    text);CREATE INDEX ON message(reply_to);

WITH RECURSIVE T AS (  SELECT    *  FROM    message  WHERE    message_id = $1UNION ALL  SELECT    m.*  FROM    T  JOIN    message m      ON m.reply_to = T.message_id)TABLE T;

-- добавим поле с общим идентификатором темы и индекс на негоALTER TABLE message  ADD COLUMN theme_id uuid;CREATE INDEX ON message(theme_id);-- инициализируем идентификатор темы в триггере при вставкеCREATE OR REPLACE FUNCTION ins() RETURNS TRIGGER AS $$BEGIN  NEW.theme_id = CASE    WHEN NEW.reply_to IS NULL THEN NEW.message_id -- берем из стартового события    ELSE ( -- или из сообщения, на которое отвечаем      SELECT        theme_id      FROM        message      WHERE        message_id = NEW.reply_to    )  END;  RETURN NEW;END;$$ LANGUAGE plpgsql;CREATE TRIGGER ins BEFORE INSERT  ON message    FOR EACH ROW      EXECUTE PROCEDURE ins();

SELECT  *FROM  messageWHERE  theme_id = $1;

Использовать прикладные ограничители

Счетчик глубины рекурсии

WITH RECURSIVE T AS (  SELECT    0 i  ...UNION ALL  SELECT    i + 1  ...  WHERE    T.i < 64 -- предел)

Хранитель пути

WITH RECURSIVE T AS (  SELECT    ARRAY[id] path  ...UNION ALL  SELECT    path || id  ...  WHERE    id <> ALL(T.path) -- не совпадает ни с одним из)

Ограничение длины пути

WITH RECURSIVE T AS (  SELECT    ARRAY[id] path  ...UNION ALL  SELECT    path || id  ...  WHERE    id <> ALL(T.path) AND    array_length(T.path, 1) < 10)

CREATE TABLE task ASSELECT  id, (random() * 100)::integer person -- всего 100 сотрудников, least(trunc(-ln(random()) / ln(2)), 10)::integer priority -- каждый следующий приоритет в 2 раза менее вероятенFROM  generate_series(1, 1e5) id; -- 100K задачCREATE INDEX ON task(person, priority);

SELECT  *FROM  generate_series(0, 99) pidWHERE  EXISTS(    SELECT      NULL    FROM      task    WHERE      person = pid AND      priority = 10  );

EXISTS + IN

SELECT  *FROM  generate_series(0, 99) pidWHERE  EXISTS(    SELECT      NULL    FROM      task    WHERE      person = pid AND      priority IN (10, 9, 8)  );

OR + EXISTS

SELECT  *FROM  generate_series(0, 99) pidWHERE  EXISTS(    SELECT      NULL    FROM      task    WHERE      person = pid AND      priority = 8  ) OR  EXISTS(    SELECT      NULL    FROM      task    WHERE      person = pid AND      priority = 9  ) OR  EXISTS(    SELECT      NULL    FROM      task    WHERE      person = pid AND      priority = 10  );

CASE + EXISTS + ...

SELECT  *FROM  generate_series(0, 99) pidWHERE  CASE    WHEN      EXISTS(        SELECT          NULL        FROM          task        WHERE          person = pid AND          priority = 8      ) THEN TRUE    ELSE      CASE        WHEN          EXISTS(            SELECT              NULL            FROM              task            WHERE              person = pid AND              priority = 9          ) THEN TRUE        ELSE          EXISTS(            SELECT              NULL            FROM              task            WHERE              person = pid AND              priority = 10          )      END  END;

EXISTS + UNION ALL + LIMIT

SELECT  *FROM  generate_series(0, 99) pidWHERE  EXISTS(    (      SELECT        NULL      FROM        task      WHERE        person = pid AND        priority = 8      LIMIT 1    )    UNION ALL    (      SELECT        NULL      FROM        task      WHERE        person = pid AND        priority = 9      LIMIT 1    )    UNION ALL    (      SELECT        NULL      FROM        task      WHERE        person = pid AND        priority = 10      LIMIT 1    )    LIMIT 1  );

Правильные индексы залог здоровья базы

CREATE INDEX ON task(person) WHERE priority >= 8;

SELECT  *FROM  generate_series(0, 99) pidWHERE  EXISTS(    SELECT      NULL    FROM      task    WHERE      person = pid AND      priority >= 8  );

SELECT DISTINCT  personFROM  taskWHERE  priority >= 8;

pidstat -rudw -lh 1

Структура базы

CREATE TABLE diccmd(  cmd    uuid      PRIMARY KEY, data    varchar);

CREATE TABLE pidstat(  host    uuid, tm    integer, pid    integer, cpu    smallint, rss    bigint, cmd    uuid);

Подробнее о способах эффективной упаковки записей в PostgreSQL-хранилище можно прочитать в статье Экономим копеечку на больших объемах, а про увеличение пропускной способности базы на запись в Пишем на субсветовой: 1 host, 1 day, 1TB.

Бесплатное хранение NULL'ов

Подробнее с внутренней механикой представления записей в PostgreSQL можно ознакомиться в докладе Николая Шаплова на PGConf.Russia 2016 Что у него внутри: хранение данных на низком уровне. Конкретно хранению NULL посвящен слайд #16.

• CPU/DSK
  Меняется постоянно, но очень часто обращается в ноль так что выгодно писать в базу NULL вместо 0.
• RSS/CMD
  Меняется достаточно редко поэтому будем писать NULL вместо повторов в рамках одного и того же PID.

Собираем красивый отчет

Использование входящих параметров

-- сохраняем параметры отчетаWITH args AS (  SELECT    json_object(      ARRAY[        'dtb'      , extract('epoch' from '2020-07-16 10:00'::timestamp(0)) -- переводим timestamp в integer      , 'dte'      , extract('epoch' from '2020-07-16 10:01'::timestamp(0))      , 'host'      , 'e828a54d-7e8a-43dd-b213-30c3201a6d8e' -- это у нас uuid      ]::text[]    ))

Извлекаем сырые данные

CREATE INDEX ON pidstat(host, tm);

-- извлекаем "сырые" данные, src AS (  SELECT    *  FROM    pidstat  WHERE    host = ((TABLE args) ->> 'host')::uuid AND    tm >= ((TABLE args) ->> 'dtb')::integer AND    tm <  ((TABLE args) ->> 'dte')::integer)

Группировка по ключу анализа

-- группировка по ключу анализа, pidtm AS (  SELECT DISTINCT ON(pid)    host  , pid  , cmd  , min(tm) OVER(w) tmb -- начало активности процесса на интервале  , max(tm) OVER(w) tme -- завершение активности  FROM    src  WINDOW    w AS(PARTITION BY pid)  ORDER BY    pid  , tm)

Границы активности процесса

CREATE INDEX ON pidstat(host, pid, tm DESC) WHERE cmd IS NOT NULL;

-- определяем начало активности каждого "неопределившегося" процесса, precmd AS (  SELECT    t.host  , t.pid  , c.tm  , c.rss  , c.cmd  FROM    pidtm t  , LATERAL(      SELECT        *      FROM        pidstat -- увы, SELF JOIN не избежать      WHERE        (host, pid) = (t.host, t.pid) AND        tm < t.tmb AND        cmd IS NOT NULL -- садимся на условный индекс      ORDER BY        tm DESC      LIMIT 1  ) c  WHERE    t.cmd IS NULL -- только для "неопределившихся")

Аналогичную методику мы уже использовали в статье PostgreSQL Antipatterns: навигация по реестру.

-- определяем момент окончания активности сегмента, pstcmd AS (  SELECT    host  , pid  , c.tm  , NULL::bigint rss  , NULL::uuid cmd  FROM    pidtm t  , LATERAL(      SELECT        tm      FROM        pidstat      WHERE        (host, pid) = (t.host, t.pid) AND        tm > t.tme AND        tm < coalesce((          SELECT            tm          FROM            pidstat          WHERE            (host, pid) = (t.host, t.pid) AND            tm > t.tme AND            cmd IS NOT NULL          ORDER BY            tm          LIMIT 1        ), x'7fffffff'::integer) -- MAX_INT4      ORDER BY        tm DESC      LIMIT 1  ) c)

JSON-конвертация форматов записей

• row_to_json превращаем каждую запись с полями в json-объект
• array_agg собираем все записи в '{...}'::json[]
• array_to_json преобразуем массив-из-JSON в JSON-массив '[...]'::json
• json_populate_recordset генерируем из JSON-массива выборку заданной структуры

Тут мы используем именно однократный вызов json_populate_recordset вместо множественного json_populate_record, потому что это банально быстрее в разы.

-- склеиваем все, uni AS (  TABLE srcUNION ALL  SELECT    *  FROM    json_populate_recordset( -- развернули в полный      NULL::pidstat    , (        SELECT          array_to_json(array_agg(row_to_json(t))) -- свернули сокращенный формат        FROM          (            TABLE precmd          UNION ALL            TABLE pstcmd          ) t      )    ))

Заполняем NULL-пропуски повторов

Воспользуемся моделью, рассмотренной в статье SQL HowTo: собираем цепочки с помощью window functions.

-- выделение групп, grp AS (  SELECT    *  , count(*) FILTER(WHERE cmd IS NOT NULL) OVER(w) grp  -- группы по CMD  , count(*) FILTER(WHERE rss IS NOT NULL) OVER(w) grpm -- группы по RSS  FROM    uni  WINDOW    w AS(PARTITION BY pid ORDER BY tm))

-- заполняем пропуски, rst AS (  SELECT    *  , CASE      WHEN least(coalesce(lead(tm) OVER(w) - 1, tm), ((TABLE args) ->> 'dte')::integer - 1) >= greatest(tm, ((TABLE args) ->> 'dtb')::integer) THEN        least(coalesce(lead(tm) OVER(w) - 1, tm), ((TABLE args) ->> 'dte')::integer - 1) - greatest(tm, ((TABLE args) ->> 'dtb')::integer) + 1    END gln -- продолжительность сегмента от предыдущей записи или начала интервала  , first_value(rss) OVER(PARTITION BY pid, grpm ORDER BY tm) _rss -- заполнение пропусков по RSS  FROM    grp  WINDOW    w AS(PARTITION BY pid, grp ORDER BY tm))

Мультигруппировка с помощью GROUPING SETS

-- мультигруппировка, gs AS (  SELECT    pid  , grp  , max(grp) qty -- количество сегментов активности по PID  , (array_agg(cmd ORDER BY tm) FILTER(WHERE cmd IS NOT NULL))[1] cmd -- "должен остаться только один"  , sum(cpu) cpu  , avg(_rss)::bigint rss  , min(tm) tmb  , max(tm) tme  , sum(gln) gln  FROM    rst  GROUP BY    GROUPING SETS((pid, grp), pid))

Вариант использования (array_agg(... ORDER BY ..) FILTER(WHERE ...))[1] позволяет нам прямо при группировке, без дополнительных телодвижений получить первое непустое (даже если оно не самое первое) значение из всего набора.

Словарь вместо JOIN

Подробнее про методику ословаривания можно прочесть в статье PostgreSQL Antipatterns: ударим словарем по тяжелому JOIN.

-- словарь CMD, cmdhs AS (  SELECT    json_object(      array_agg(cmd)::text[]    , array_agg(data)    )  FROM    diccmd  WHERE    cmd = ANY(ARRAY(      SELECT DISTINCT        cmd      FROM        gs      WHERE        cmd IS NOT NULL    )))

SELECT  pid, grp, CASE    WHEN grp IS NOT NULL THEN -- это "сегмент" активности      cmd  END cmd, (nullif(cpu::numeric / gln, 0))::numeric(32,2) cpu -- приводим CPU к "средней" нагрузке, nullif(rss, 0) rss, tmb -- верхняя граница активности, tme -- нижняя граница активности, gln -- продолжительность активности, CASE    WHEN grp IS NULL THEN -- это весь процесс      qty  END cnt, CASE    WHEN grp IS NOT NULL THEN      (TABLE cmdhs) ->> cmd::text -- извлекаем данные из словаря  END commandFROM  gsWHERE  grp IS NOT NULL OR -- это запись "сегмента"  qty > 1 -- или в процессе больше одного сегментаORDER BY  pid DESC, grp NULLS FIRST;

• сначала читаем из блока A влево от стартовой записи получаем N записей
• дальше читаем L - N вправо от значения A
• находим в последнем блоке максимальный ключ C
• отфильтровываем из предыдущей выборки все записи этим ключом и вычитываем его справа

CREATE TABLE doc(  id    serial, dt    date);CREATE INDEX ON doc(dt, id); -- наш индекс-- случайно "раскидаем" документы по последнему годуINSERT INTO doc(dt)SELECT  now()::date - (random() * 365)::integerFROM  generate_series(1, 10000);

(  ... LIMIT 100)UNION ALL(  ... LIMIT 100)LIMIT 100

WITH src AS (  SELECT    '{"dt" : "2019-09-07", "id" : 2331}'::json -- "опорный" ключ), pre AS (  (    ( -- читаем не более 100 записей "влево" от опорной точки из "левого" значения A      SELECT        *      FROM        doc      WHERE        dt = ((TABLE src) ->> 'dt')::date AND        id < ((TABLE src) ->> 'id')::integer      ORDER BY        dt DESC, id DESC      LIMIT 100    )    UNION ALL    ( -- дочитываем до 100 записей "вправо" от исходного значения "верхнего" ключа A -> B, C      SELECT        *      FROM        doc      WHERE        dt > ((TABLE src) ->> 'dt')::date      ORDER BY        dt, id      LIMIT 100    )  )  LIMIT 100)-- находим крайний правый ключ C в том, что прочитали, maxdt AS (  SELECT    max(dt)  FROM    pre  WHERE    dt > ((TABLE src) ->> 'dt')::date)(  ( -- вычищаем "левые" записи по ключу C    SELECT      *    FROM      pre    WHERE      dt <> (TABLE maxdt)    LIMIT 100  )  UNION ALL  ( -- дочитываем "правые" записи по ключу C до 100 штук    SELECT      *    FROM      doc    WHERE      dt = (TABLE maxdt)    ORDER BY      dt DESC, id DESC    LIMIT 100  )  LIMIT 100)ORDER BY  dt, id DESC; -- накладываем целевую сортировку, поскольку записи по B у нас лежат не в том порядке

• Итак, по первому ключу A = '2019-09-07' мы прочитали 3 записи.
• К ним дочитали еще 97 по B и C за счет точного попадания в Index Scan.
• Среди всех записей отфильтровали 18 по максимальному ключу C.
• Дочитали 23 записи (вместо 18 искомых из-за Bitmap Scan) по максимальному ключу.
• Все пересортировали и отсекли целевые 100 записей.
• и на все это ушло меньше миллисекунды!

• элемент-предок стоит раньше любого из элементов его поддерева
• дочерние элементы внутри одного родительского упорядочиваются по дополнительному прикладному ключу

Таков путь!

Рекурсивная сортировка

WITH RECURSIVE src(id, pid, data) AS (  VALUES    (1, NULL, 'A')  , (2, 1, 'AA')  , (3, 2, 'AAA')  , (4, 1, 'AB')  , (5, 2, 'AAB')  , (6, 3, 'AAAA')  , (7, 5, 'AABA')  , (8, 4, 'ABA')  , (9, 7, 'AABAA')  , (10, 2, 'AAC')), T AS (  SELECT    id  , ARRAY[data] path -- инициализируем массив пути корневым элементом  FROM    src  WHERE    pid IS NULLUNION ALL  SELECT    s.id  , T.path || s.data -- наращиваем путь  FROM    T  JOIN    src s      ON s.pid = T.id)SELECT  *FROM  srcNATURAL JOIN  TORDER BY  path; -- сортируем согласно пути

 id | pid | data  |         path----+-----+-------+-----------------------  1 |     | A     | {A}  2 |   1 | AA    | {A,AA}  3 |   2 | AAA   | {A,AA,AAA}  6 |   3 | AAAA  | {A,AA,AAA,AAAA}  5 |   2 | AAB   | {A,AA,AAB}  7 |   5 | AABA  | {A,AA,AAB,AABA}  9 |   7 | AABAA | {A,AA,AAB,AABA,AABAA} 10 |   2 | AAC   | {A,AA,AAC}  4 |   1 | AB    | {A,AB}  8 |   4 | ABA   | {A,AB,ABA}

Подключаем комбинаторику

Комбинаторика (комбинаторный анализ) раздел математики, изучающий дискретные объекты, множества (сочетания, перестановки, размещения и перечисления элементов) и отношения на них (например, частичного порядка).

Комбинации

{A,A,A}{A,A,B}{A,A,C}{A,B,A}{A,B,B}...{C,C,B}{C,C,C}

3^2 |  0  0  0  0  0  0  0  0  0  1  1  1  1  1  1  1  1  1  2  2  2  2  2  2  2  2  23^1 |  0  0  0  1  1  1  2  2  2  0  0  0  1  1  1  2  2  2  0  0  0  1  1  1  2  2  23^0 |  0  1  2  0  1  2  0  1  2  0  1  2  0  1  2  0  1  2  0  1  2  0  1  2  0  1  2=== |  0  1  2  3  4  5  6  7  8  9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26

• генерируем каждое число в диапазоне 0 .. N^N - 1
• раскладываем в N-ричную систему счисления
• берем элемент на соответствующей позиции разложения

SELECT  dstFROM  -- исходный набор элементов  (VALUES('{A,B,C}'::varchar[])) data(src)  -- кэшируем размер набора, LATERAL array_length(src, 1) n  -- кэшируем границу интервала, LATERAL (SELECT (n ^ n)::bigint l) X  -- генерируем все числа на интервале, LATERAL generate_series(0, l - 1) num  -- формируем разложение числа в N-ричной системе, LATERAL (    SELECT      array_agg((num % (n ^ (pos + 1))::bigint) / (n ^ pos)::bigint ORDER BY pos DESC) num_n    FROM      generate_series(0, n - 1) pos  ) Y  -- собираем элементы согласно "цифрам", LATERAL (    SELECT      array_agg(src[num_n[pos] + 1]) dst    FROM      generate_subscripts(num_n, 1) pos  ) Z;

Перестановки

JOIN LATERAL(  SELECT    count(DISTINCT unnest) = n cond  FROM    unnest(num_n)) flt  ON cond

SELECT  dstFROM  -- исходный набор элементов  (VALUES('{A,B,C}'::varchar[])) data(src)  -- кэшируем размер набора, LATERAL array_length(src, 1) n  -- кэшируем границу интервала, LATERAL (SELECT (n ^ n)::bigint l) X  -- генерируем все числа на интервале, LATERAL generate_series(0, l - 1) num  -- формируем разложение числа в N-ричной системе, LATERAL (    SELECT      array_agg((num % (n ^ (pos + 1))::bigint) / (n ^ pos)::bigint ORDER BY pos DESC) num_n    FROM      generate_series(0, n - 1) pos  ) Y  -- фильтруем комбинации с неполным набором "цифр"JOIN LATERAL(    SELECT      count(DISTINCT unnest) = n cond    FROM      unnest(num_n)  ) flt    ON cond  -- собираем элементы согласно "цифрам", LATERAL (    SELECT      array_agg(src[num_n[pos] + 1]) dst    FROM      generate_subscripts(num_n, 1) pos  ) Z;

{A,B,C}{A,C,B}{B,A,C}{B,C,A}{C,A,B}{C,B,A}

Подмножества

{}{A}{B}{A,B}{C}{A,C}{B,C}{A,B,C}

2^2 |  0  0  0  0  1  1  1  12^1 |  0  0  1  1  0  0  1  12^0 |  0  1  0  1  0  1  0  1=== |  0  1  2  3  4  5  6  7

  -- кэшируем разложение числа в двоичной системе, LATERAL (SELECT num::bit(64) num_n) Y  -- собираем элементы согласно "цифрам", LATERAL (    SELECT      coalesce(array_agg(src[i]) FILTER(WHERE get_bit(num_n, 64 - i) = 1), '{}') dst    FROM      generate_series(1, n) i  ) Z

SELECT  dstFROM  -- исходный набор элементов  (VALUES('{A,B,C}'::varchar[])) data(src)  -- кэшируем размер набора, LATERAL array_length(src, 1) n  -- кэшируем границу интервала, LATERAL (SELECT (2 ^ n)::bigint l) X  -- генерируем все числа на интервале, LATERAL generate_series(0, l - 1) num  -- кэшируем разложение числа в двоичной системе, LATERAL (SELECT num::bit(64) num_n) Y  -- собираем элементы согласно "цифрам", LATERAL (    SELECT      coalesce(array_agg(src[i]) FILTER(WHERE get_bit(num_n, 64 - i) = 1), '{}') dst    FROM      generate_series(1, n) i  ) Z;

Иерархия без рекурсии!

Пути-дороги

Дано: газовая плита, чайник. Задача: вскипятить воду.
Физик: Зажечь плиту, наполнить чайник водой и поставить на плиту, ждать.
Математик: Аналогично.

Дано: зажженная газовая плита, наполненный водой чайник. Задача: вскипятить воду.
Физик: Поставить чайник на плиту, ждать.
Математик: Выливаем воду из чайника на плиту. Огонь погас, чайник пуст, задача сведена к предыдущей.
Народный анекдот

• Правило #1: начинается и заканчивается нужными нам элементами
  path[1] = root AND path[array_length(path, 1)] = id
• Правило #2: предок каждого элемента, кроме корневого, так же присутствует в наборе
  pid = ANY(path) OR pid = root
• Правило #3: из всех таких наборов искомый самой маленькой длины
  Иначе для id=3 наборы {1,2,3} и {1,2,3,4} окажутся равноподходящими, поскольку предок id=4 (pid=1) тоже присутствует.
• Правило #4: предок каждого элемента стоит ровно в предыдущей позиции
  pid = path[pos - 1]

• генерируем все подмножества элементов, исключая root и id, формируя тело пути по правилу #1
• проверяем выполнение правила #2
• выбираем, согласно правилу #3, самый короткий набор
• генерируем все перестановки его элементов
• проверяем выполнение правила #4
• что осталось искомый путь

Полный вариант запроса, смотреть с осторожностью

Я вас предупредил [источник картинки]

WITH src(id, pid, data) AS (  VALUES    (1, NULL, 'A')  , (2, 1, 'AA')  , (3, 2, 'AAA')  , (4, 1, 'AB')  , (5, 2, 'AAB')  , (6, 3, 'AAAA')  , (7, 5, 'AABA')  , (8, 4, 'ABA')  , (9, 7, 'AABAA')  , (10, 2, 'AAC'))-- кэшируем ID корневого элемента, root AS (  SELECT    id  FROM    src  WHERE    pid IS NULL  LIMIT 1)-- формируем уже известные пути и предварительные наборы, preset AS (  SELECT    *  , CASE      -- для корневого элемента путь состоит только из него самого      WHEN pid IS NULL THEN ARRAY[id]      -- для ссылающегося на корневой - из пары      WHEN pid = (TABLE root) THEN ARRAY[pid, id]    END prepath  , CASE      WHEN pid IS NULL THEN NULL      WHEN pid = (TABLE root) THEN NULL      -- все ID, кроме корневого и собственного - EXCLUDE CURRENT ROW      ELSE array_agg(id) FILTER(WHERE pid IS NOT NULL) OVER(ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING EXCLUDE CURRENT ROW)    END preset  FROM    src)-- формируем "переборные" пути, prepath AS (  SELECT    id  , prepath  FROM    -- отбираем только элементы, чей путь еще не определили    (      SELECT        id      , pid      , preset src -- комбинируемый набор      FROM        preset      WHERE        prepath IS NULL    ) data    -- подмножества  , LATERAL (      SELECT        dst pathset      FROM        -- кэшируем размер набора        LATERAL array_length(src, 1) n        -- кэшируем границу интервала      , LATERAL (SELECT (2 ^ n)::bigint l) X        -- генерируем все числа на интервале      , LATERAL generate_series(1, l - 1) num -- тут можно с 1, поскольку пустой набор нас заведомо не интересует        -- кэшируем разложение числа в двоичной системе      , LATERAL (SELECT num::bit(64) num_n) Y        -- собираем элементы согласно "цифрам"      , LATERAL (          SELECT            coalesce(array_agg(src[i]) FILTER(WHERE get_bit(num_n, 64 - i) = 1), '{}') || data.id dst          FROM            generate_series(1, n) i        ) Z        -- проверяем наличие предка в наборе      , LATERAL (          SELECT            NULL          FROM            (              SELECT                (SELECT pid FROM src WHERE id = dst[i] LIMIT 1) _pid              FROM                generate_subscripts(dst, 1) i            ) T          HAVING            bool_and(_pid = (TABLE root) OR _pid = ANY(dst))        ) rule2      -- отбираем первый подходящий минимальной длины      ORDER BY        array_length(dst, 1) -- rule3      LIMIT 1    ) X    -- перестановки  , LATERAL (      SELECT        dst prepath      FROM        -- исходный набор элементов        (SELECT pathset) data(src)        -- кэшируем размер набора      , LATERAL array_length(src, 1) n        -- кэшируем границу интервала      , LATERAL (SELECT (n ^ n)::bigint l) X        -- генерируем все числа на интервале      , LATERAL generate_series(0, l - 1) num        -- формируем разложение числа в N-ричной системе      , LATERAL (          SELECT            array_agg((num % (n ^ (pos + 1))::bigint) / (n ^ pos)::bigint ORDER BY pos DESC) num_n          FROM            generate_series(0, n - 1) pos        ) Y        -- фильтруем комбинации с неполным набором "цифр"      JOIN LATERAL(          SELECT            count(DISTINCT unnest) = n cond          FROM            unnest(num_n)        ) flt          ON cond        -- собираем элементы согласно "цифрам"      , LATERAL (          SELECT            array_agg(src[num_n[pos] + 1]) dst          FROM            generate_subscripts(num_n, 1) pos        ) Z        -- проверяем наличие предка в предыдущей позиции      , LATERAL (          SELECT            NULL          FROM            (              SELECT                (SELECT pid FROM src WHERE id = dst[i] LIMIT 1) _pid              , i              FROM                generate_subscripts(dst, 1) i            ) T          HAVING            bool_and((i = 1 AND _pid = (TABLE root)) OR _pid = dst[i - 1])        ) rule4    ) Y)SELECT  src.*  -- восстанавливаем "путь" из прикладных ключей, (    SELECT      array_agg(data ORDER BY i)    FROM      coalesce(X.prepath, ARRAY[(TABLE root)] || Y.prepath) p -- помним о необходимости добавить "корень"    , LATERAL generate_subscripts(p, 1) i    , LATERAL (        SELECT          data        FROM          src        WHERE          id = p[i]        LIMIT 1      ) T  ) pathFROM  srcLEFT JOIN  preset X    USING(id)LEFT JOIN  prepath Y    USING(id)ORDER BY  path;

А попроще можно?..

WITH src(id, pid, data) AS (  VALUES    (1, NULL, 'A')  , (2, 1, 'AA')  , (3, 2, 'AAA')  , (4, 1, 'AB')  , (5, 2, 'AAB')  , (6, 3, 'AAAA')  , (7, 5, 'AABA')  , (8, 4, 'ABA')  , (9, 7, 'AABAA')  , (10, 2, 'AAC'))-- кэшируем ID корневого элемента, root AS (  SELECT    id  FROM    src  WHERE    pid IS NULL  LIMIT 1)-- собираем все предстоящие id в массив для текущего, prepath AS (  SELECT    id  , pid  , array_agg(id) OVER(ORDER BY id /*!!! сортировка по тому самому ключу*/ ROWS UNBOUNDED PRECEDING EXCLUDE CURRENT ROW) src  FROM    src  WHERE    pid IS NOT NULL)-- находим пути, pre AS (  SELECT    id  , path  FROM    prepath    -- подмножества  , LATERAL (      SELECT        dst path      FROM        -- кэшируем размер набора        LATERAL array_length(src, 1) n        -- кэшируем границу интервала      , LATERAL (SELECT (2 ^ n)::bigint l) X        -- генерируем все числа на интервале      , LATERAL generate_series(0, l - 1) num        -- кэшируем разложение числа в двоичной системе      , LATERAL (SELECT num::bit(64) num_n) Y        -- собираем элементы согласно "цифрам"      , LATERAL (          SELECT            coalesce(array_agg(src[i]) FILTER(WHERE get_bit(num_n, 64 - i) = 1), '{}') || id dst          FROM            generate_series(1, n) i        ) Z        -- проверяем наличие предка в предыдущей позиции      , LATERAL (          SELECT            NULL          FROM            (              SELECT                (SELECT pid FROM src WHERE id = dst[i] LIMIT 1) _pid              , i              FROM                generate_subscripts(dst, 1) i            ) T          HAVING            bool_and((i = 1 AND _pid = (TABLE root)) OR (i > 1 AND _pid = dst[i - 1]))        ) rule4    ) X)SELECT  src.*  -- восстанавливаем "путь" из прикладных ключей, (    SELECT      array_agg(data ORDER BY i)    FROM      (        SELECT          CASE            -- для корневого элемента путь состоит только из него самого            WHEN pid IS NULL THEN ARRAY[id]            -- для ссылающегося на корневой - из пары            WHEN pid = (TABLE root) THEN ARRAY[pid, id]            ELSE ARRAY[(TABLE root)] || pre.path          END p -- помним о необходимости добавить "корень"      ) p    , LATERAL generate_subscripts(p, 1) i    , LATERAL (        SELECT          data        FROM          src        WHERE          id = p[i]        LIMIT 1      ) T  ) pathFROM  srcLEFT JOIN  pre    USING(id)ORDER BY  path;

Отличие enterprise [решения] от всего остального он всегда идёт от запросов бизнеса и решает какую-то бизнес-задачу. [src]

• поиск отрезков, пересекающих точку/интервал
• слияние отрезков по максимальному перекрытию
• подсчет количества отрезков в каждой точке

Отрезки в точке

Типичная бизнес-задача: кто из сотрудников был на больничном в прошлый понедельник?, можно ли сейчас уйти на обед? или даже кто из контрагентов должен нам на начало этого года?.

CREATE TABLE ranges(  owner    integer, dtb -- начало отрезка    date, dte -- окончание отрезка    date);INSERT INTO ranges(  owner, dtb, dte)SELECT  (random() * 1e3)::integer, dtb, dtb + (random() * 1e2)::integerFROM  (    SELECT      now()::date - (random() * 1e3)::integer dtb    FROM      generate_series(1, 1e5)  ) T;

BETWEEN + btree

SELECT  *FROM  rangesWHERE  '2020-01-01'::date BETWEEN dtb AND dte;

CREATE INDEX ON ranges(dtb, dte);

*range + GiST

CREATE INDEX ON ranges  USING gist( -- используем "пространственный" GiST-индекс    daterange(dtb, dte, '[]') -- формируем диапазон дат  );

SELECT  *FROM  rangesWHERE  daterange(dtb, dte, '[]') @> '2020-01-01'::date; -- значение входит в диапазон

Отрезки по группе

ALTER TABLE ranges ADD COLUMN segment integer;UPDATE ranges SET segment = (random() * 1e2)::integer;

SELECT  *FROM  rangesWHERE  segment = 1 AND  daterange(dtb, dte, '[]') @> '2020-01-01'::date;

CREATE INDEX ON ranges USING gist(segment, daterange(dtb, dte, '[]'));-- ОШИБКА:  для типа данных integer не определён класс операторов по умолчанию для метода доступа "gist"-- HINT:  Вы должны указать класс операторов для индекса или определить класс операторов по умолчанию для этого типа данных.

CREATE EXTENSION btree_gist;CREATE INDEX ON ranges USING gist(segment, daterange(dtb, dte, '[]'));

Отрезки на интервале

Типичная бизнес-задача: "кто ходил в отпуск в этом месяце?", "какие задачи в моем календаре на сегодня?"

SELECT  *FROM  rangesWHERE  segment = 1 AND  daterange(dtb, dte, '[]') && daterange('2020-01-01', '2020-02-01', '[)'); -- пересечение отрезков, без включения правой границы

Объединение отрезков

SELECT int4range('[0,2]') + int4range('[1,3]');-- [0,4)

WITH rng(s, e) AS (  VALUES    ( 1,  3)  , ( 2,  4)  , ( 5,  6)  , ( 5,  8)  , ( 6,  9)  , ( 7, 10)  , ( 8, 10)  , (10, 11)  , (10, 15)  , (11, 12)  , (12, 13))SELECT  min(s) s, max(e) eFROM  (    SELECT      s    , e    , max(new_start) OVER(ORDER BY s, e) left_edge    FROM      (        SELECT          s        , e        , CASE            WHEN s < max(le) OVER(ORDER BY s, e) THEN              NULL            ELSE              s          END new_start        FROM          (            SELECT              s            , e            , lag(e) OVER(ORDER BY s, e) le            FROM              rng          ) s1      ) s2  ) s3GROUP BY  left_edge;

1. Отсортируем все отрезки по паре (начало, конец).
2. Для каждого начала вычислим, стоит ли оно правее максимального из предшествующих ему концов. Если левее вообще ничего нет или самый правый конец из предыдущих все равно левее это точка начала искомого интервала.
3. Отнесем все отрезки в группы по количеству предшествующих им найденных начал.
4. В каждой группе берем наименьшее из начал и наибольший из концов это и есть искомый непрерывный интервал.

WITH rng(s, e) AS (  VALUES    ( 1,  3)  , ( 2,  4)  , ( 5,  6)  , ( 5,  8)  , ( 6,  9)  , ( 7, 10)  , ( 8, 10)  , (10, 11)  , (10, 15)  , (11, 12)  , (12, 13))SELECT -- min/max по группе  min(s) s, max(e) eFROM  (    SELECT      *    , sum(ns::integer) OVER(ORDER BY s, e) grp -- определение групп    FROM      (        SELECT          *        , coalesce(s > max(e) OVER(ORDER BY s, e ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), TRUE) ns -- начало правее самого правого из предыдущих концов == разрыв        FROM          rng      ) t  ) tGROUP BY  grp;

Длина объединенных отрезков

1. Для каждого отрезка вычисляем максимум концов всех предыдущих.
2. Берем разность между концом отрезка и большим из начала отрезка и найденного конца.
3. Осталось только просуммировать полученные разности.

WITH rng(s, e) AS (  VALUES    ( 1,  3)  , ( 2,  4)  , ( 5,  6)  , ( 5,  8)  , ( 6,  9)  , ( 7, 10)  , ( 8, 10)  , (10, 11)  , (10, 15)  , (11, 12)  , (12, 13))SELECT  sum(delta)FROM  (    SELECT      *    , greatest(        e - greatest(          max(e) OVER(ORDER BY s, e ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING)        , s        )      , 0      ) delta    FROM      rng  ) T;

Подсчет отрезков на каждом интервале

Типичная бизнес-задача: анализ и распределение нагрузки например, между операторами call-центра: "Сколько звонков мы обслуживаем одновременно? Сколько для этого нужно операторов в ночной смене?"

1. Присвоим каждому началу отрезка вес +1, а каждому концу -1.
2. Просуммируем накопительно значения в каждой точке это и есть количество отрезков на интервале, начинающемся с этой точки и вплоть до следующей по порядку.

WITH rng(s, e) AS (  VALUES    ( 1,  3)  , ( 2,  4)  , ( 5,  6)  , ( 5,  8)  , ( 6,  9)  , ( 7, 10)  , ( 8, 10)  , (10, 11)  , (10, 15)  , (11, 12)  , (12, 13))SELECT DISTINCT ON(p) -- уникализация до единственного значения в точке  p, sum(v) OVER(ORDER BY p) qty -- накопительная суммаFROM  (    SELECT      s p    , +1 v    FROM      rng  UNION ALL    SELECT      e p    , -1 v    FROM      rng  ) TORDER BY  1;

CREATE TABLE corpus ASSELECT  id, dt, strFROM  (    SELECT      id::integer    , now()::date - (random() * 1e3)::integer dt -- дата где-то за последние 3 года    , (random() * 1e2 + 1)::integer len -- длина "текста" до 100    FROM      generate_series(1, 1e6) id -- 1M записей  ) X, LATERAL(    SELECT      string_agg(        CASE          WHEN random() < 1e-1 THEN ' ' -- 10% на пробел          ELSE chr((random() * 25 + ascii('a'))::integer)        END      , '') str    FROM      generate_series(1, len)  ) Y;

Наивный подход #1: gist + btree

CREATE INDEX ON corpus(dt);CREATE INDEX ON corpus USING gist(to_tsvector('simple', str));

SELECT  *FROM  corpusWHERE  to_tsvector('simple', str) @@ to_tsquery('simple', 'abc:*');

SELECT  *FROM  corpusWHERE  to_tsvector('simple', str) @@ to_tsquery('simple', 'abc:*')ORDER BY  dt DESCLIMIT 10;

Наивный подход #2: btree_gist

CREATE EXTENSION btree_gist;CREATE INDEX ON corpus USING gist(to_tsvector('simple', str), dt);

SELECT  *FROM  corpusWHERE  to_tsvector('simple', str) @@ to_tsquery('simple', 'abc:*')ORDER BY  dt <-> '2100-01-01'::date DESC -- сортировка по "расстоянию" от даты далеко в будущемLIMIT 10;

Геометрия в помощь!

Разбиваем текст на слова

CREATE TABLE corpus_kw ASSELECT  id, dt, kwFROM  corpus, LATERAL (    SELECT      kw    FROM      regexp_split_to_table(lower(str), E'[^\\-a-zа-я0-9]+', 'i') kw    WHERE      length(kw) > 1  ) T;

Укладываем слова

ALTER TABLE corpus_kw ADD COLUMN p point;UPDATE  corpus_kwSET  p = point(    (      SELECT        sum((2 ^ 16) ^ (64 - i) * ascii(substr(kw, i, 1)))      FROM        generate_series(1, length(kw)) i    )  , extract('epoch' from dt)  );CREATE INDEX ON corpus_kw USING gist(p);

Формируем поисковый запрос

WITH src AS (  SELECT    point(      ( -- копипасту можно вынести в функцию        SELECT          sum((2 ^ 16) ^ (48 - i) * ascii(substr(kw, i, 1)))        FROM          generate_series(1, length(kw)) i      )    , extract('epoch' from dt)    ) ps  FROM    (VALUES('abc', '2100-01-01'::date)) T(kw, dt) -- поисковый запрос)SELECT  *, src.ps <-> kw.p dFROM  corpus_kw kw, srcORDER BY  dLIMIT 10;

Небольшая ложка дегтя

CREATE UNIQUE INDEX ON corpus(id);

WITH src AS (  SELECT    point(      (        SELECT          sum((2 ^ 16) ^ (48 - i) * ascii(substr(kw, i, 1)))        FROM          generate_series(1, length(kw)) i      )    , extract('epoch' from dt)    ) ps  FROM    (VALUES('abc', '2100-01-01'::date)) T(kw, dt) -- поисковый запрос), dc AS (  SELECT    (      SELECT        dc      FROM        corpus dc      WHERE        id = kw.id    )  FROM    corpus_kw kw  , src  WHERE    p[0] >= ps[0] AND -- kw >= ...    p[1] <= ps[1]     -- dt DESC  ORDER BY    src.ps <-> kw.p  LIMIT 10)SELECT  (dc).*FROM  dc;

Ложка дегтя #2

SELECT relname, pg_size_pretty(pg_total_relation_size(oid)) FROM pg_class WHERE relname LIKE 'corpus%';

corpus          | 242 MB -- исходный набор текстовcorpus_id_idx   |  21 MB -- это его PKcorpus_kw       | 705 MB -- ключевые слова с датамиcorpus_kw_p_idx | 403 MB -- GiST-индекс

ALTER TABLE corpus_kw  DROP COLUMN kw, DROP COLUMN dt;VACUUM FULL corpus_kw;

corpus_kw       | 641 MB -- только id и point

WITH lm(ld, lr) AS (  VALUES    ('AccessShareLock', '{AccessExclusiveLock}'::text[])  , ('RowShareLock', '{ExclusiveLock,AccessExclusiveLock}'::text[])  , ('RowExclusiveLock', '{ShareLock,ShareRowExclusiveLock,ExclusiveLock,AccessExclusiveLock}'::text[])  , ('ShareUpdateExclusiveLock', '{ShareUpdateExclusiveLock,ShareLock,ShareRowExclusiveLock,ExclusiveLock,AccessExclusiveLock}'::text[])  , ('ShareLock', '{RowExclusiveLock,ShareUpdateExclusiveLock,ShareRowExclusiveLock,ExclusiveLock,AccessExclusiveLock}'::text[])  , ('ShareRowExclusiveLock', '{RowExclusiveLock,ShareUpdateExclusiveLock,ShareLock,ShareRowExclusiveLock,ExclusiveLock,AccessExclusiveLock}'::text[])  , ('ExclusiveLock', '{RowShareLock,RowExclusiveLock,ShareUpdateExclusiveLock,ShareLock,ShareRowExclusiveLock,ExclusiveLock,AccessExclusiveLock}'::text[])  , ('AccessExclusiveLock', '{AccessShareLock,RowShareLock,RowExclusiveLock,ShareUpdateExclusiveLock,ShareLock,ShareRowExclusiveLock,ExclusiveLock,AccessExclusiveLock}'::text[])), locks AS (  SELECT    (      locktype    , database    , relation    , page    , tuple    , virtualxid    , transactionid::text::bigint    , classid    , objid    , objsubid    ) target  , *  FROM    pg_locks), ld AS (  SELECT    *  FROM    locks  WHERE    NOT granted), lr AS (  SELECT    *  FROM    locks  WHERE    target::text = ANY(ARRAY(      SELECT DISTINCT        target::text      FROM        ld    )) AND    granted), lcx AS (  SELECT    lr.target  , ld.pid ldp  , ld.mode ldm  , lr.pid lrp  , lr.mode lrm  FROM    ld  JOIN    lr      ON lr.pid <> ld.pid AND        lr.target IS NOT DISTINCT FROM ld.target), cfl AS (SELECT  lc.locktype "type", CASE lc.locktype    WHEN 'relation' THEN      ARRAY[relation]    WHEN 'extend' THEN      ARRAY[relation]    WHEN 'page' THEN      ARRAY[relation, page]    WHEN 'tuple' THEN      ARRAY[relation, page, tuple]    WHEN 'transactionid' THEN      ARRAY[transactionid::text::oid]    WHEN 'virtualxid' THEN      string_to_array(virtualxid::text, '/')::oid[]    WHEN 'object' THEN      ARRAY[classid, objid, objsubid]    WHEN 'userlock' THEN      ARRAY[classid]    WHEN 'advisory' THEN      ARRAY[classid, objid, objsubid]  END target, nullif(lc.pid = lcx.ldp, FALSE) as locked, lc.pid, regexp_replace(lc.mode, 'Lock$', '') "mode", nullif(lc.granted, TRUE) "granted", nullif(lc.target IS NOT DISTINCT FROM lcx.target, FALSE) "conflict"FROM  lcxJOIN  locks lc    ON lc.pid IN (lcx.ldp, lcx.lrp))SELECT  cfl.*, CASE    WHEN "type" NOT IN ('virtualxid', 'transactionid') THEN target[1]::regclass  END relname, cl.relkindFROM  cflLEFT JOIN LATERAL(    SELECT      *    FROM      pg_class    WHERE      cfl.type = 'relation' AND      oid = target[1]    LIMIT 1  ) cl    ON TRUEORDER BY                                            -- сортируем ...  locked                                            -- сначала кого блокируют, pid                                               -- по принадлежности процессу (1 процесс = 1 транзакция), CASE "type"                                       -- по приоритету типов блокировок    WHEN 'virtualxid'    THEN 0    WHEN 'transactionid' THEN 1    WHEN 'relation'      THEN 2    WHEN 'tuple'         THEN 3    WHEN 'object'        THEN 4    WHEN 'advisory'      THEN 5  END, CASE relkind                                       -- по принадлежности объекта таблице    WHEN 'r' THEN cl.oid    WHEN 't' THEN regexp_replace(cl.relname, E'^.*\\D(\\d+)$', E'\\1', '')::oid    WHEN 'i' THEN (      SELECT        indrelid      FROM        pg_index      WHERE        indexrelid = cl.oid      LIMIT 1    )    WHEN 'S' THEN (      SELECT        (          SELECT            adrelid          FROM            pg_attrdef          WHERE            oid = dp.objid        )      FROM        pg_depend dp      WHERE        (refclassid, refobjid) = ('pg_class'::regclass, cl.oid) AND        (deptype, classid) = ('n', 'pg_attrdef'::regclass)      LIMIT 1    )  END  -- https://postgrespro.ru/docs/postgresql/12/catalog-pg-class, CASE relkind                                       -- по типу объекта БД    WHEN 'r' THEN 0 -- relation    WHEN 'm' THEN 1 -- materialized view    WHEN 'p' THEN 2 -- partitioned table    WHEN 'f' THEN 3 -- foreign table    WHEN 't' THEN 4 -- TOAST    WHEN 'i' THEN 5 -- index    WHEN 'I' THEN 6 -- partitioned index    WHEN 'S' THEN 7 -- sequence    WHEN 'c' THEN 8 -- composite type    WHEN 'v' THEN 9 -- view  END, CASE                                               -- по типу индекса  PK вперед    WHEN relkind = 'i' THEN      NOT (        SELECT          indisprimary        FROM          pg_index        WHERE          indexrelid = cl.oid        LIMIT 1      )  END, cl.relname                                         -- по имени объекта, CASE mode                                          -- по приоритету режима блокировки    WHEN 'AccessExclusive'      THEN 0    WHEN 'Exclusive'            THEN 1    WHEN 'ShareRowExclusive'    THEN 2    WHEN 'Share'                THEN 3    WHEN 'ShareUpdateExclusive' THEN 4    WHEN 'RowExclusive'         THEN 5    WHEN 'RowShare'             THEN 6    WHEN 'AccessShare'          THEN 7  END;

1. создание одноименных таблиц
2. создание одноименных индексов
3. самоблокировка (CONCURRENTLY vs транзакция)
4. блокировка по UNIQUE-индексу
5. одновременное изменение записи
6. обслуживание таблицы

Создание одноименных таблиц

-- tx1BEGIN;CREATE TABLE tbl(pk integer, val integer);    -- tx2    CREATE TABLE tbl(pk integer, val integer);

• r = обычная таблица (Relation)
• i = индекс (Index)
• S = последовательность (Sequence)
• t = таблица TOAST
• v = представление (View)
• m = материализованное представление (Materialized view)
• c = составной тип (Composite type)
• f = сторонняя таблица (Foreign table)
• p = секционированная таблица (Partitioned table)
• I = секционированный индекс (partitioned Index)

SELECT NULL::tbl;

Создание одноименных индексов

-- tx1BEGIN;CREATE UNIQUE INDEX idx ON tbl(pk);    -- tx2    CREATE UNIQUE INDEX idx ON tbl(pk);

-- tx1BEGIN;CREATE UNIQUE INDEX idx1 ON tbl(pk);    -- tx2    CREATE UNIQUE INDEX idx2 ON tbl(pk); -- работает, и никаких блокировок!

Самоблокировка (CONCURRENTLY vs транзакция)

DO $$BEGIN  SELECT dblink_exec('dbname=' || current_database() || ' user=' || current_user, 'CREATE INDEX CONCURRENTLY idx_cic ON tbl(pk);');END$$;

Блокировка по UNIQUE-индексу

-- tx1BEGIN;INSERT INTO tbl(pk, val)VALUES(1, 1);    -- tx2    INSERT INTO tbl(pk, val)VALUES(1, 1);

Одновременное изменение записи

-- tx1BEGIN;UPDATE tbl SET val = val + 1 WHERE pk = 1;    -- tx2    UPDATE tbl SET val = val + 1 WHERE pk = 1;

SELECT * FROM <relation> WHERE ctid = '(<page>,<tuple>)';

Обслуживание таблицы

-- tx1BEGIN;INSERT INTO tbl(pk, val)VALUES(2, 2);    -- tx2    TRUNCATE TABLE tbl;

• ожидание блокировки
  LOG: process 38162 still waiting for ExclusiveLock on advisory lock [225382138,225386226,141586103,2] after 100.047 ms
• получение блокировки
  LOG: process 38162 acquired ExclusiveLock on advisory lock [225382138,225386226,141586103,2] after 150.741 ms
• взаимоблокировка
  ERROR: deadlock detected

CREATE TABLE lock(  dt       -- ключ хронологического секционирования    date, host     -- сервер, на котором возникла блокировка    uuid, pid      -- PID процесса из строки лога    integer, ts       -- момент события    timestamp, event    -- { lock-wait | lock-acquire | deadlock-detect }    lockevent, type     -- { relation | extend | ... }    locktype, mode     -- { AccessShare | RowShare | ... }    lockmode, lock     -- объект блокировки    uuid, exectime -- продолжительность    numeric(32,2));

Более подробно про организацию секционирования в нашей системе мониторинга можно прочитать в статье Пишем в PostgreSQL на субсветовой: 1 host, 1 day, 1TB, а про различные типы и режимы блокировок в DBA: кто скрывается за блокировкой.

Как слышится, так и пишется

• берем каждый случай ожидания (lock-wait)
• для него находим ближайшую снизу по времени запись получения (lock-acquire) этой же (lock, pid, mode) блокировки то есть на тот же объект, в том же процессе, с тем же режимом

SELECT  ts, pid, event, type, mode, lock, exectime, T.*FROM  lock lc, LATERAL (    SELECT      exectime waittime    FROM      lock    WHERE      (        dt      , host      , lock      , pid      , mode      , event      ) = (        '2020-06-19'::date      , lc.host      , lc.lock      , lc.pid      , lc.mode      , 'lock-acquire'      ) AND      ts >= lc.ts    ORDER BY      ts    LIMIT 1  ) TWHERE  (    lc.dt  , lc.host  , lc.event  ) = (    '2020-06-19'::date  , 'e828a54d-7e8a-43dd-b213-30c3201a6d8e'::uuid  , 'lock-wait'::lockevent  );

Помни о цели!

Window Functions: семерых одним ударом

-- формируем условие окончания блокировкиWITH lc AS (  SELECT    *  , CASE      WHEN event = 'lock-wait' THEN        exectime > coalesce(lead(exectime) OVER(PARTITION BY lock, pid, mode ORDER BY ts, exectime), 0) -- "перелом" времени ожидания      ELSE TRUE -- 'lock-acquire' - блокировка получена    END cond -- условие окончания "цепочки"  FROM    lock lc  WHERE    event <> 'deadlock-detect' AND -- исключаем все deadlock    (      lc.dt    , lc.host    ) = (      '2020-06-19'::date    , 'e828a54d-7e8a-43dd-b213-30c3201a6d8e'::uuid    ))-- оставляем только "последние" записи - их exectime и есть время ожидания "всей" блокировкиSELECT  ts, pid, event, type, mode, lock, exectimeFROM  lcWHERE  cond;

OFFSET start { ROW | ROWS }
FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } { ONLY | WITH TIES }

CREATE TABLE events(  id    serial      PRIMARY KEY, ts    timestamp, data    json);INSERT INTO events(ts)SELECT  now() - ((random() * 1e8) || ' sec')::intervalFROM  generate_series(1, 1e6);

CREATE INDEX ON events(ts DESC);

SELECT  ...WHERE  ts < $1 AND  ts >= coalesce((    SELECT      ts    FROM      events    WHERE      ts < $1    ORDER BY      ts DESC    LIMIT 1 OFFSET 25  ), '-infinity')ORDER BY  ts DESC;

Старый-добрый подзапрос

EXPLAIN (ANALYZE, BUFFERS)SELECT  *FROM  eventsWHERE  ts < '2020-01-01'::timestamp AND  ts >= coalesce((    SELECT      ts    FROM      events    WHERE      ts < '2020-01-01'::timestamp    ORDER BY      ts DESC    LIMIT 1 OFFSET 25  ), '-infinity')ORDER BY  ts DESC;

Пробуем WITH TIES на зуб

EXPLAIN (ANALYZE, BUFFERS)SELECT  *FROM  eventsWHERE  ts < '2020-01-01'::timestampORDER BY  ts DESCFETCH FIRST 26 ROWS WITH TIES;

Программист ставит себе на тумбочку перед сном два стакана. Один с водой на случай, если захочет ночью пить. А второй пустой на случай, если не захочет.

#1: Нехватка work_mem

SELECT generate_series(1, 1e6) i ORDER BY i;

ALTER SYSTEM SET work_mem = '128MB';

#2: Сортировка уже отсортированного

SELECT  *FROM  (    SELECT generate_series(1, 1e6) i  ) TORDER BY  i;

SELECT  *FROM  (    SELECT generate_series(1, 1e6) i  ) T;

Этим же свойством сохранения порядка записей обладают чтение из CTE (Common Table Expression, узел CTE Scan в плане), SRF (Set-Returning Function, Function Scan) или VALUES (Values Scan).

#3: Вложенная отладочная сортировка

SELECT  i, 1e6 - iFROM  (    SELECT      *    FROM      generate_series(1, 1e6) i    WHERE      (i % 2, i % 3, i % 5, i % 7) = (1, 2, 4, 6)    ORDER BY -- осталось от отладки      i DESC  ) TORDER BY  i;

SELECT  i, 1e6 - iFROM  (    SELECT      *    FROM      generate_series(1, 1e6) i    WHERE      (i % 2, i % 3, i % 5, i % 7) = (1, 2, 4, 6)  ) TORDER BY  i;

SELECT  i, 1e6 - iFROM  generate_series(1, 1e6) iWHERE  (i % 2, i % 3, i % 5, i % 7) = (1, 2, 4, 6);

#4: Index Scan вместо сортировки

CREATE TABLE tbl ASSELECT  (random() * 1e4)::integer fk -- 10K разных внешних ключей, now() - ((random() * 1e8) || ' sec')::interval tsFROM  generate_series(1, 1e6) pk;  -- 1M "фактов"CREATE INDEX ON tbl(fk); -- индекс для foreign key

SELECT  tsFROM  tblWHERE  fk = 1 -- отбор по конкретной связиORDER BY  ts DESC -- хотим всего одну "последнюю" записьLIMIT 1;

DROP INDEX tbl_fk_idx;CREATE INDEX ON tbl(fk, ts DESC);

#5: UNION ALL вместо сортировки

TRUNCATE TABLE tbl;INSERT INTO tblSELECT  CASE    WHEN random() >= 1e-5      THEN (random() * 1e4)::integer  END fk -- 10K разных внешних ключей, из них 0.0001 - NULL'ы, now() - ((random() * 1e8) || ' sec')::interval tsFROM  generate_series(1, 1e6) pk;  -- 1M "фактов"

SELECT  *FROM  tblWHERE  fk IS NULL OR  fk = 1ORDER BY  fk NULLS FIRST, ts DESCLIMIT 10;

(  SELECT    *  FROM    tbl  WHERE    fk IS NULL  ORDER BY    fk, ts DESC  LIMIT 10)UNION ALL(  SELECT    *  FROM    tbl  WHERE    fk = 1  ORDER BY    fk, ts DESC  LIMIT 10)LIMIT 10;

#6: Сортировки для оконных функций

SELECT DISTINCT ON(fk)  *, count(*) OVER(PARTITION BY fk ORDER BY ts) -- без DESC!FROM  tblWHERE  fk < 10ORDER BY  fk, ts DESC; -- хотим "последнее" значение ts

SELECT DISTINCT ON(fk)  *, count(*) OVER(PARTITION BY fk)FROM  tblWHERE  fk < 10ORDER BY  fk, ts DESC;

SELECT DISTINCT ON(fk)  *, count(*) OVER(PARTITION BY fk ORDER BY ts DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)FROM  tblWHERE  fk < 10ORDER BY  fk, ts DESC;

Bonus: Полезные сортировки, которые не происходят

SELECT DISTINCT ON(fk)  *FROM  tblORDER BY  fk, ts DESC;

WITH RECURSIVE T AS (  (    SELECT      fk    , ts    FROM      tbl    ORDER BY      fk, ts DESC    LIMIT 1 -- первая запись с минимальным ключом  )UNION ALL  SELECT    X.*  FROM    T  , LATERAL(      SELECT        fk      , ts      FROM        tbl      WHERE        fk > T.fk      ORDER BY        fk, ts DESC      LIMIT 1 -- следующая по индексу запись    ) X  WHERE    T.fk IS NOT NULL)TABLE T;

Вероятно, в будущих версиях PostgreSQL для таких точечных чтений появится соответствующий тип узла Index Skip Scan. Но скоро его ждать не стоит.

• Дело о непростом пути вверх
  Разберем в live-видео на реальном примере некоторые из способов улучшения производительности иерархического запроса.
• Дело о худеющем запросе
  Увидим, как можно запрос упростить и ускорить в несколько раз, пошагово применяя стандартные методики.
• Дело о развесистой клюкве
  Восстановим структуру БД на основании единственного запроса с 11 JOIN и предложим альтернативный вариант решения на ней той же задачи.

#1: Дело о непростом пути вверх

WITH RECURSIVE h AS (  SELECT    n."@Номенклатура" id  , ARRAY[      coalesce(        (          SELECT            ne."Info"          FROM            "NomenclatureExt" ne          WHERE            ne."@Номенклатура" = n."@Номенклатура"          LIMIT 1        )      , '{}'      )    ] res  , n."Раздел" -- предок по иерархии  FROM    "Номенклатура" n  WHERE    n."@Номенклатура" = ANY($1::integer[])UNION -- уникализация  SELECT    h.id  , array_append(      h.res    , coalesce(        (          SELECT            ne."Info"          FROM            "NomenclatureExt" ne          WHERE            ne."@Номенклатура" = n."@Номенклатура"          LIMIT 1        )      , '{}'      )    ) -- расширение массива  , n."Раздел"  FROM    "Номенклатура" n  , h  WHERE    n."@Номенклатура" = h."Раздел" -- двигаемся вверх по иерархии в сторону предков)SELECT  h.id, h.resFROM  hWHERE  h."Раздел" IS NULL;

Если нечто выглядит как утка, плавает как утка и крякает как утка, то это, вероятно, и есть утка.

Что/зачем делает запрос?

Проблемы в запросе

1. Повторная вычитка одного предка
   Переданные карточки с очень большой вероятностью будут находиться в одном или соседних разделах, но эта информация у нас нигде не учитывается. Поэтому, при движении по иерархии вверх, мы будем вычитывать одни и те же промежуточные узлы раз за разом.
   
   Представим, что на первом шаге мы передали 60 идентификаторов в запрос и нашли эти 60 карточек из одного раздела. Несмотря на это, мы будем искать одного и того же предка те же 60 раз.
2. Повторная вычитка связанной записи
   Поскольку мы вычитываем связанную запись независимо ни от чего, то и этот поиск мы делаем ровно столько же раз, сколько вычитываем запись основную.
3. Вложенный запрос под уникализацией
   Одно и то же значение из связанной записи вычитывается каждый раз, и только после этого схлапывается до единственного экземпляра.
   
   То есть в нашем примере 59 из 60 вложенных запросов будут выполнены заведомо абсолютно зря.
   

• 107 карточек вычитано Bitmap Scan на стартовой итерации рекурсии и плюсом к ним 107 индексных поисков связанных
• Поскольку PostgreSQL заранее не понимает, сколько и каких записей мы найдем вверх по иерархии, он вычитывает сразу все 18K из номенклатуры с помощью Seq Scan. В результате, из 22мс выполнения запроса 12мс мы потратили на чтение всей таблицы и еще 5мс на ее хэширование, итого больше 77%.
• Из вычитанных 18K нужными нам по результату Hash Join окажутся только 475 штук и теперь добавим к ним еще 475 Index Scan по связанным записям.
• Итого: 22мс и 2843 buffers суммарно.

Что/как можно исправить?

1. Поскольку нам нужны сразу и идентификатор самой карточки, и идентификатор ее предка, будем вычитывать записи сразу целиком как (tableAlias).
2. Вычитку будем производить с помощью конструкции = ANY(ARRAY(...)), исключая возможность возникновения неудобных JOIN.
3. Для возможности уникализации и хэширования скастуем записи таблицы в (row)::text.
4. Поскольку внутри рекурсии обращение к рекурсивной части может быть только однократным и строго не внутри вложенных запросов, вместо этого материализуем ее внутри отдельной CTE.
5. Таблицу состоящую из единственного столбца можно свернуть с помощью ARRAY(TABLE X) до скалярного значения-массива. А если в ней и так одна запись, то использовать ее с нужной раскастовкой (TABLE X)::integer[].

-- рекурсивный подъем вверх до корня с поиском только уникальных записей, it AS (  SELECT    it::text -- иначе не работает уникализация через UNION  FROM    "Номенклатура" it  WHERE    "@Номенклатура" = ANY((TABLE src)::integer[])UNION  (    WITH X AS (      SELECT DISTINCT        (it::"Номенклатура")."Раздел"      FROM        it      WHERE        (it::"Номенклатура")."Раздел" IS NOT NULL    )    SELECT      it2::text    FROM      "Номенклатура" it2    WHERE      "@Номенклатура" = ANY(ARRAY(TABLE X))  ))

-- рекурсивный спуск вниз для формирования "пути" к каждой карточке, itr AS (  SELECT    ARRAY[(it::"Номенклатура")."@Номенклатура"] path  , it::"Номенклатура" -- запись исходной таблицы  FROM    it  WHERE    (it::"Номенклатура")."Раздел" IS NULL -- стартуем от "корневых" записейUNION ALL  SELECT    ARRAY[((_it.it)::"Номенклатура")."@Номенклатура"] || itr.path -- наращиваем "путь" спереди  , (_it.it)::"Номенклатура"  FROM    itr  JOIN    it _it      ON ((_it.it)::"Номенклатура")."Раздел@" IS NOT FALSE AND      ((_it.it)::"Номенклатура")."Раздел" = (itr.it)."@Номенклатура")

• Соберем весь набор ID, встречающихся в путях. Но это ровно тот же набор, который дают ID самих наших извлеченных записей.
• Извлечем опять сразу все нужные нам записи связанной таблицы за один проход через = ANY(ARRAY(...)).
• Сложим все полученные значения нужного поля в hstore-словарик.

-- формируем словарь info для каждого ключа, чтобы не бегать по записям CTE, hs AS (  SELECT    hstore(      array_agg("@Номенклатура"::text)    , array_agg(coalesce("Info", '{}'))    )  FROM    "NomenclatureExt"  WHERE    "@Номенклатура" = ANY(ARRAY(      SELECT        (it)."@Номенклатура"      FROM        itr    )))

, ARRAY(    SELECT      (TABLE hs) -> id::text -- извлекаем данные из "словаря"    FROM      unnest(path) id  ) res

-- список всех исходных IDWITH RECURSIVE src AS (  SELECT $1::integer[] -- набор ID в виде сериализованного массива)-- рекурсивный подъем вверх до корня с поиском только уникальных записей, it AS (  SELECT    it::text -- иначе не работает уникализация через UNION  FROM    "Номенклатура" it  WHERE    "@Номенклатура" = ANY((TABLE src)::integer[])UNION  (    WITH X AS (      SELECT DISTINCT        (it::"Номенклатура")."Раздел"      FROM        it      WHERE        (it::"Номенклатура")."Раздел" IS NOT NULL    )    SELECT      it2::text    FROM      "Номенклатура" it2    WHERE      "@Номенклатура" = ANY(ARRAY(TABLE X))  ))-- рекурсивный спуск вниз для формирования "пути" к каждой карточке, itr AS (  SELECT    ARRAY[(it::"Номенклатура")."@Номенклатура"] path  , it::"Номенклатура"  FROM    it  WHERE  WHERE    (it::"Номенклатура")."Раздел" IS NULL -- стартуем от "корневых" записейUNION ALL  SELECT    ARRAY[((_it.it)::"Номенклатура")."@Номенклатура"] || itr.path -- наращиваем "путь" спереди  , (_it.it)::"Номенклатура"  FROM    itr  JOIN    it _it      ON ((_it.it)::"Номенклатура")."Раздел@" IS NOT FALSE AND      ((_it.it)::"Номенклатура")."Раздел" = (itr.it)."@Номенклатура")-- формируем словарь info для каждого ключа, чтобы не бегать по записям CTE, hs AS (  SELECT    hstore(      array_agg("@Номенклатура"::text)    , array_agg(coalesce("Info", '{}'))    )  FROM    "NomenclatureExt"  WHERE    "@Номенклатура" = ANY(ARRAY(      SELECT        (it)."@Номенклатура"      FROM        itr    )))-- строим цепочку info для каждого id из оригинального набораSELECT  path[1] id, ARRAY(    SELECT      (TABLE hs) -> id::text -- извлекаем данные из "словаря"    FROM      unnest(path) id  ) resFROM  itrWHERE  path[1] = ANY((TABLE src)::integer[]); -- ограничиваемся только стартовым набором

• Теперь на каждом шаге рекурсии (а их получается 4, в соответствии с глубиной дерева) мы добавляем, в среднем, всего по 12 записей.
• Восстановление путей вниз заняло большую часть времени 10мс. Можно сделать и меньше, но это гораздо сложнее.
• Итого, новый запрос выполняется 15мс вместо 22мс и читает только лишь 860 страниц данных вместо 2843, что имеет принципиальное влияние на время работы, когда нет возможности обеспечить постоянное присутствие этих данных в кэше.

#2: Дело о худеющем запросе

Регулярно возникают реплики типа "Вот ты ускорил запрос в 10 раз, но всего на 10мс оно же того не стоит! Мы лучше поставим еще пару реплик! Вместо 100MB памяти получилось 1MB? Да нам проще памяти на сервер добавить!"

Тут какой момент разработчик, вооруженный набором стандартных приемов, на оптимизацию запроса тратит константное время (= деньги), а с увеличением функционала и количества пользователей нагрузка на БД растет примерно как N(logN), а даже не линейно. То есть если сейчас ваш проект ест CPU базы на 50%, готовьтесь к тому, что уже через год вам придется ставить еще один такой же сервер (= деньги), потом еще и еще

Оптимизация запросов не избавляет от добавления мощностей, но сильно отодвигает их в будущее. Добившись вместо нагрузки в 50% всего 10%, вы сможете не расширять железо еще года 2-3, а вложить те же деньги, например, в увеличение штата или чьей-то зарплаты.

00: исходное состояние

WITH personIds("Персона") AS (  SELECT    $1::uuid[]), persons AS (  SELECT    P."Персона"  , coalesce(P."Фамилия", '') "Фамилия"  , coalesce(P."Имя", '') "Имя"  , coalesce(P."Отчество", '') "Отчество"  , coalesce(      CASE        WHEN nullif(P."ФамилияЛица", '') IS NOT NULL THEN          P."ФамилияЛица"        ELSE          P."Фамилия"      END    , ''    ) "ФамилияЛица"  , coalesce(      CASE        WHEN nullif(P."ФамилияЛица", '') IS NOT NULL THEN          P."ИмяЛица"        ELSE          P."Имя"      END    , ''    ) "ИмяЛица"  , coalesce(      CASE        WHEN nullif(P."ФамилияЛица", '') IS NOT NULL THEN          P."ОтчествоЛица"        ELSE          P."Отчество"      END    , ''    ) "ОтчествоЛица"  , P."Примечание"  , P."Обновлено"  , P."Уволен"  , P."Группа"  , P."Пол"  , P."Логин"  , P."Город"  , P."ДатаРождения"  , P."$Создано"::date "ДатаРегистрации"  , coalesce(P."ОтдельныйПрофиль", FALSE) "ОтдельныйПрофиль"  , coalesce(P."ЕстьКорпАккаунт", FALSE) "ЕстьКорпАккаунт"  FROM    "Персона" P  WHERE    "Персона" = ANY((TABLE personids)::uuid[])), counts AS (  SELECT    NULL c), users AS (  SELECT    hstore(      array_agg("Персона"::text)    , array_agg(udata::text)    )  FROM    (      SELECT        "Персона"::text      , array_agg(u::text) udata      FROM        "Пользователь" u      WHERE        "Персона" IN (          SELECT            "Персона"          FROM            persons        ) AND        (          "Главный" OR          (            "Уволен" IS DISTINCT FROM TRUE AND            "Удален" IS DISTINCT FROM TRUE          )        )      GROUP BY 1    ) u2), T1 AS (  SELECT    persons."Персона"  , persons."Фамилия"  , persons."Имя"  , persons."Отчество"  , persons."ФамилияЛица"  , persons."ИмяЛица"  , persons."ОтчествоЛица"  , persons."Примечание"  , persons."Обновлено"  , persons."Город"  , coalesce(persons."ОтдельныйПрофиль", FALSE) "ОтдельныйПрофиль"  , coalesce(persons."ЕстьКорпАккаунт", FALSE) "ЕстьКорпАккаунт"  , counts.c "Всего"  , persons."Группа"  , (      SELECT        ARRAY(          SELECT            row_to_json(t2)          FROM            (              SELECT                "Пользователь" >> 32 as "Account"              , "Пользователь" & x'FFFFFFFF'::bigint "Face"              , coalesce("ЕстьПользователь", TRUE) "HasUser"              , coalesce("ЕстьПользователь", TRUE) AND coalesce("Входил", TRUE) "HasLoggedIn"              , coalesce("Уволен", persons."Уволен") "Fired"              FROM                (                  SELECT                    *                  FROM                    (                      SELECT                        (udata::"Пользователь").*                      FROM                        unnest(((TABLE users) -> "Персона"::text)::text[]) udata                    ) udata15                  WHERE                    "Уволен" IS DISTINCT FROM TRUE AND                    "Удален" IS DISTINCT FROM TRUE                ) udata2            ) t2        )    )::text[] "Users"  , coalesce(      (        SELECT          row_to_json(t3)        FROM          (            SELECT              "Пользователь" >> 32 as "Account"            , "Пользователь" & x'FFFFFFFF'::bigint "Face"            FROM              (                SELECT                  (udata::"Пользователь").*                FROM                  unnest(((TABLE users) -> "Персона"::text)::text[]) udata              ) udata2            WHERE              "Уволен" IS DISTINCT FROM TRUE AND              "Удален" IS DISTINCT FROM TRUE AND              "Пользователь" >> 32 = 5313189::int            ORDER BY              "ЕстьПользователь" DESC, "Входил" DESC            LIMIT 1          ) t3      )    , (        SELECT          row_to_json(t4)        FROM          (            SELECT              "Пользователь" >> 32 as "Account"            , "Пользователь" & x'FFFFFFFF'::bigint "Face"            FROM              (                SELECT                  (udata::"Пользователь").*                FROM                  unnest(((TABLE users) -> "Персона"::text)::text[]) udata              ) udata2            WHERE              "Уволен" IS DISTINCT FROM TRUE AND              "Удален" IS DISTINCT FROM TRUE AND              "Главный"            ORDER BY              "ЕстьПользователь" DESC, "Входил" DESC            LIMIT 1          ) t4      )    , (        SELECT          row_to_json(t5)        FROM          (            SELECT              "Пользователь" >> 32 as "Account"            , "Пользователь" & x'FFFFFFFF'::bigint "Face"            FROM              (                SELECT                  (udata::"Пользователь").*                FROM                  unnest(((TABLE users) -> "Персона"::text)::text[]) udata              ) udata2            WHERE              "Уволен" IS DISTINCT FROM TRUE AND              "Удален" IS DISTINCT FROM TRUE            LIMIT 1          ) t5      )    ) "PrimaryFaceAccount"  , (      SELECT        "Пользователь" >> 32      FROM        (          SELECT            "Пользователь"          FROM            (              SELECT                (udata::"Пользователь").*              FROM                unnest(((TABLE users) -> "Персона"::text)::text[]) udata            ) udata2          WHERE            "Главный"        ) t3      LIMIT 1    ) "MainAccount"  , ARRAY(      SELECT        "Значение"::int      FROM        "КонтактныеДанные"      WHERE        persons."Группа" AND        "Персона" = persons."Персона" AND        "Тип" = 'account'    ) "АккаунтыГруппы"  , persons."Пол"  , persons."Логин"  , persons."ДатаРождения"  , persons."ДатаРегистрации"  FROM    persons  , counts)SELECT  CASE    WHEN "ЕстьКорпАккаунт" OR "MainAccount" IS NOT NULL THEN      "ФамилияЛица"    ELSE      "Фамилия"  END "LastName", CASE    WHEN "ЕстьКорпАккаунт" OR "MainAccount" IS NOT NULL THEN      "ИмяЛица"    ELSE      "Имя"  END "FirstName", CASE    WHEN "ЕстьКорпАккаунт" OR "MainAccount" IS NOT NULL THEN      "ОтчествоЛица"    ELSE      "Отчество"  END "PatronymicName", *FROM  T1;

  , coalesce(      (        SELECT          row_to_json(T)        FROM          (            SELECT              ...            FROM              (                SELECT                  (udata::"Пользователь").*                FROM                  unnest(((TABLE users) -> "Персона"::text)::text[]) udata              ) udata2            WHERE              ...            ORDER BY              ...            LIMIT 1          ) T      )

1. Можно ли сделать все то же самое за один проход? Конечно! В этом нам помогут FILTER (9.4+) и LATERAL (9.3+).
2. Вместо построения JSON независимо в 5 разных местах (по одним и тем же записям, в основном). Построим эти JSON сразу для каждой исходной записи в полном (5 ключей) и коротком (2 ключа) вариантах.
3. Сортировка исходного набора совпадает во всех местах, где используется. Где не используется значит, непринципиально для данных, и ее можно использовать все равно.
4. LIMIT 1 можно успешно заменить на извлечение первого элемента массива: arr[1]. Так что собираем по каждому условию именно массивы.
5. Для одновременного возврата нескольких агрегатов используем сериализацию в ARRAY[aggx::text, aggy::text].

WITH personIds("Персона") AS (  SELECT    $1::uuid[]), persons AS (  SELECT    P."Персона"  , coalesce(P."Фамилия", '') "Фамилия"  , coalesce(P."Имя", '') "Имя"  , coalesce(P."Отчество", '') "Отчество"  , coalesce(      CASE        WHEN nullif(P."ФамилияЛица", '') IS NOT NULL THEN          P."ФамилияЛица"        ELSE          P."Фамилия"      END    , ''    ) "ФамилияЛица"  , coalesce(      CASE        WHEN nullif(P."ФамилияЛица", '') IS NOT NULL THEN          P."ИмяЛица"        ELSE          P."Имя"      END    , ''    ) "ИмяЛица"  , coalesce(      CASE        WHEN nullif(P."ФамилияЛица", '') IS NOT NULL THEN          P."ОтчествоЛица"        ELSE          P."Отчество"      END    , ''    ) "ОтчествоЛица"  , P."Примечание"  , P."Обновлено"  , P."Уволен"  , P."Группа"  , P."Пол"  , P."Логин"  , P."Город"  , P."ДатаРождения"  , P."$Создано"::date "ДатаРегистрации"  , coalesce(P."ОтдельныйПрофиль", FALSE) "ОтдельныйПрофиль"  , coalesce(P."ЕстьКорпАккаунт", FALSE) "ЕстьКорпАккаунт"  FROM    "Персона" P  WHERE    "Персона" = ANY((TABLE personids)::uuid[])), counts AS (  SELECT    NULL c), users AS (  SELECT    hstore(      array_agg("Персона"::text)    , array_agg(udata::text)    )  FROM    (      SELECT        "Персона"::text      , array_agg(u::text) udata      FROM        "Пользователь" u      WHERE        "Персона" IN (          SELECT            "Персона"          FROM            persons        ) AND        (          "Главный" OR          (            "Уволен" IS DISTINCT FROM TRUE AND            "Удален" IS DISTINCT FROM TRUE          )        )      GROUP BY 1    ) u2), T1 AS (  SELECT    persons."Персона"  , persons."Фамилия"  , persons."Имя"  , persons."Отчество"  , persons."ФамилияЛица"  , persons."ИмяЛица"  , persons."ОтчествоЛица"  , persons."Примечание"  , persons."Обновлено"  , persons."Город"  , coalesce(persons."ОтдельныйПрофиль", FALSE) "ОтдельныйПрофиль"  , coalesce(persons."ЕстьКорпАккаунт", FALSE) "ЕстьКорпАккаунт"  , counts.c "Всего"  , persons."Группа"-- 8< --  , coalesce(usjs[1]::text[], '{}') "Users"  , coalesce(      (usjs[2]::json[])[1]    , (usjs[3]::json[])[1]    , (usjs[4]::json[])[1]    ) "PrimaryFaceAccount"  , ((usjs[5]::json[])[1] ->> 'Account')::bigint "MainAccount"-- 8< --  , ARRAY(      SELECT        "Значение"::int      FROM        "КонтактныеДанные"      WHERE        persons."Группа" AND        "Персона" = persons."Персона" AND        "Тип" = 'account'    ) "АккаунтыГруппы"  , persons."Пол"  , persons."Логин"  , persons."ДатаРождения"  , persons."ДатаРегистрации"  FROM    persons  , counts-- 8< --  , LATERAL (      SELECT        ARRAY[ -- массив сериализованных json[]          array_agg(json_f) FILTER (WHERE -- фильтрация по необходимому условию            "Уволен" IS DISTINCT FROM TRUE AND            "Удален" IS DISTINCT FROM TRUE          )::text        , array_agg(json_s) FILTER (WHERE            "Уволен" IS DISTINCT FROM TRUE AND            "Удален" IS DISTINCT FROM TRUE AND            "Пользователь" >> 32 = 5313189::int          )::text        , array_agg(json_s) FILTER (WHERE            "Уволен" IS DISTINCT FROM TRUE AND            "Удален" IS DISTINCT FROM TRUE AND            "Главный"          )::text        , array_agg(json_s) FILTER (WHERE            "Уволен" IS DISTINCT FROM TRUE AND            "Удален" IS DISTINCT FROM TRUE          )::text        , array_agg(json_s) FILTER (WHERE            "Главный"          )::text        ] usjs      FROM        (          SELECT -- готовим JSON для каждой записи сразу, потом только используем готовый            json_build_object(              'Account'            , "Пользователь" >> 32            , 'Face'            , "Пользователь" & x'FFFFFFFF'::bigint            , 'HasUser'            , coalesce("ЕстьПользователь", TRUE)            , 'HasLoggedIn'            , coalesce("ЕстьПользователь", TRUE) AND coalesce("Входил", TRUE)            , 'Fired'            , coalesce("Уволен", persons."Уволен")            ) json_f          , json_build_object(              'Account'            , "Пользователь" >> 32            , 'Face'            , "Пользователь" & x'FFFFFFFF'::bigint            ) json_s          , *          FROM            (              SELECT                (unnest).*              FROM                unnest(((TABLE users) -> "Персона"::text)::"Пользователь"[])            ) T          ORDER BY -- сортировка одна на всех            "ЕстьПользователь" DESC, "Входил" DESC        ) T    ) usjs-- 8< --)SELECT  CASE    WHEN "ЕстьКорпАккаунт" OR "MainAccount" IS NOT NULL THEN      "ФамилияЛица"    ELSE      "Фамилия"  END "LastName", CASE    WHEN "ЕстьКорпАккаунт" OR "MainAccount" IS NOT NULL THEN      "ИмяЛица"    ELSE      "Имя"  END "FirstName", CASE    WHEN "ЕстьКорпАккаунт" OR "MainAccount" IS NOT NULL THEN      "ОтчествоЛица"    ELSE      "Отчество"  END "PatronymicName", *FROM  T1;

1. В общем, выносим формирование JSON в подзапрос именно по каждой из персон. В результате у нас исчезает CTE users и hstore.

WITH personIds("Персона") AS (  SELECT    $1::uuid[]), persons AS (  SELECT    P."Персона"  , coalesce(P."Фамилия", '') "Фамилия"  , coalesce(P."Имя", '') "Имя"  , coalesce(P."Отчество", '') "Отчество"  , coalesce(      CASE        WHEN nullif(P."ФамилияЛица", '') IS NOT NULL THEN          P."ФамилияЛица"        ELSE          P."Фамилия"      END    , ''    ) "ФамилияЛица"  , coalesce(      CASE        WHEN nullif(P."ФамилияЛица", '') IS NOT NULL THEN          P."ИмяЛица"        ELSE          P."Имя"      END    , ''    ) "ИмяЛица"  , coalesce(      CASE        WHEN nullif(P."ФамилияЛица", '') IS NOT NULL THEN          P."ОтчествоЛица"        ELSE          P."Отчество"      END    , ''    ) "ОтчествоЛица"  , P."Примечание"  , P."Обновлено"  , P."Уволен"  , P."Группа"  , P."Пол"  , P."Логин"  , P."Город"  , P."ДатаРождения"  , P."$Создано"::date "ДатаРегистрации"  , coalesce(P."ОтдельныйПрофиль", FALSE) "ОтдельныйПрофиль"  , coalesce(P."ЕстьКорпАккаунт", FALSE) "ЕстьКорпАккаунт"-- 8< --  , (      SELECT        ARRAY[ -- массив сериализованных json[]          array_agg(json_f) FILTER (WHERE -- фильтрация по необходимому условию            "Уволен" IS DISTINCT FROM TRUE AND            "Удален" IS DISTINCT FROM TRUE          )::text        , array_agg(json_s) FILTER (WHERE            "Уволен" IS DISTINCT FROM TRUE AND            "Удален" IS DISTINCT FROM TRUE AND            "Пользователь" >> 32 = 5313189::int          )::text        , array_agg(json_s) FILTER (WHERE            "Уволен" IS DISTINCT FROM TRUE AND            "Удален" IS DISTINCT FROM TRUE AND            "Главный"          )::text        , array_agg(json_s) FILTER (WHERE            "Уволен" IS DISTINCT FROM TRUE AND            "Удален" IS DISTINCT FROM TRUE          )::text        , array_agg(json_s) FILTER (WHERE            "Главный"          )::text        ] usjs      FROM        (          SELECT -- готовим JSON для каждой записи сразу, потом только используем готовый            json_build_object(              'Account'            , "Пользователь" >> 32            , 'Face'            , "Пользователь" & x'FFFFFFFF'::bigint            , 'HasUser'            , coalesce("ЕстьПользователь", TRUE)            , 'HasLoggedIn'            , coalesce("ЕстьПользователь", TRUE) AND coalesce("Входил", TRUE)            , 'Fired'            , coalesce("Уволен", P."Уволен")            ) json_f          , json_build_object(              'Account'            , "Пользователь" >> 32            , 'Face'            , "Пользователь" & x'FFFFFFFF'::bigint            ) json_s          , *          FROM            "Пользователь"          WHERE            "Персона" = P."Персона" AND            (              "Главный" OR              (                "Уволен" IS DISTINCT FROM TRUE AND                "Удален" IS DISTINCT FROM TRUE              )            )          ORDER BY -- сортировка одна на всех            "ЕстьПользователь" DESC, "Входил" DESC        ) T    ) usjs-- 8< --  FROM    "Персона" P  WHERE    "Персона" = ANY((TABLE personids)::uuid[])), counts AS (  SELECT    NULL c), T1 AS (  SELECT    persons."Персона"  , persons."Фамилия"  , persons."Имя"  , persons."Отчество"  , persons."ФамилияЛица"  , persons."ИмяЛица"  , persons."ОтчествоЛица"  , persons."Примечание"  , persons."Обновлено"  , persons."Город"  , coalesce(persons."ОтдельныйПрофиль", FALSE) "ОтдельныйПрофиль"  , coalesce(persons."ЕстьКорпАккаунт", FALSE) "ЕстьКорпАккаунт"  , counts.c "Всего"  , persons."Группа"  , coalesce(usjs[1]::text[], '{}') "Users"  , coalesce(      (usjs[2]::json[])[1]    , (usjs[3]::json[])[1]    , (usjs[4]::json[])[1]    ) "PrimaryFaceAccount"  , ((usjs[5]::json[])[1] ->> 'Account')::bigint "MainAccount"  , ARRAY(      SELECT        "Значение"::int      FROM        "КонтактныеДанные"      WHERE        persons."Группа" AND        "Персона" = persons."Персона" AND        "Тип" = 'account'    ) "АккаунтыГруппы"  , persons."Пол"  , persons."Логин"  , persons."ДатаРождения"  , persons."ДатаРегистрации"  FROM    persons  , counts)SELECT  CASE    WHEN "ЕстьКорпАккаунт" OR "MainAccount" IS NOT NULL THEN      "ФамилияЛица"  ELSE    "Фамилия"  END "LastName", CASE    WHEN "ЕстьКорпАккаунт" OR "MainAccount" IS NOT NULL THEN      "ИмяЛица"    ELSE      "Имя"  END "FirstName", CASE    WHEN "ЕстьКорпАккаунт" OR "MainAccount" IS NOT NULL THEN      "ОтчествоЛица"    ELSE      "Отчество"  END "PatronymicName", *FROM  T1;

1. Очевидно, CTE personids (заменяем на inline-параметр с раскастовкой) и CTE counts (вообще какой-то странный атавизм, возвращающий один NULL).
2. После этого замечаем, что все выборки у нас стали из единственной таблички, поэтому лучше убрать избыточные алиасы.

WITH persons AS (  SELECT    "Персона"  , coalesce("Фамилия", '') "Фамилия"  , coalesce("Имя", '') "Имя"  , coalesce("Отчество", '') "Отчество"  , coalesce(      CASE        WHEN nullif("ФамилияЛица", '') IS NOT NULL THEN          "ФамилияЛица"        ELSE          "Фамилия"      END    , ''    ) "ФамилияЛица"  , coalesce(      CASE        WHEN nullif("ФамилияЛица", '') IS NOT NULL THEN          "ИмяЛица"        ELSE          "Имя"      END    , ''    ) "ИмяЛица"  , coalesce(      CASE        WHEN nullif("ФамилияЛица", '') IS NOT NULL THEN          "ОтчествоЛица"        ELSE          "Отчество"      END    , ''    ) "ОтчествоЛица"  , "Примечание"  , "Обновлено"  , "Уволен"  , "Группа"  , "Пол"  , "Логин"  , "Город"  , "ДатаРождения"  , "$Создано"::date "ДатаРегистрации"  , coalesce("ОтдельныйПрофиль", FALSE) "ОтдельныйПрофиль"  , coalesce("ЕстьКорпАккаунт", FALSE) "ЕстьКорпАккаунт"  , (      SELECT        ARRAY[ -- массив сериализованных json[]          array_agg(json_f) FILTER (WHERE -- фильтрация по необходимому условию            "Уволен" IS DISTINCT FROM TRUE AND            "Удален" IS DISTINCT FROM TRUE          )::text        , array_agg(json_s) FILTER (WHERE            "Уволен" IS DISTINCT FROM TRUE AND            "Удален" IS DISTINCT FROM TRUE AND            "Пользователь" >> 32 = 5313189::int          )::text        , array_agg(json_s) FILTER (WHERE            "Уволен" IS DISTINCT FROM TRUE AND            "Удален" IS DISTINCT FROM TRUE AND            "Главный"          )::text        , array_agg(json_s) FILTER (WHERE            "Уволен" IS DISTINCT FROM TRUE AND            "Удален" IS DISTINCT FROM TRUE          )::text        , array_agg(json_s) FILTER (WHERE            "Главный"          )::text        ] usjs      FROM        (          SELECT -- готовим JSON для каждой записи сразу, потом только используем готовый            json_build_object(              'Account'            , "Пользователь" >> 32            , 'Face'            , "Пользователь" & x'FFFFFFFF'::bigint            , 'HasUser'            , coalesce("ЕстьПользователь", TRUE)            , 'HasLoggedIn'            , coalesce("ЕстьПользователь", TRUE) AND coalesce("Входил", TRUE)            , 'Fired'            , coalesce("Уволен", p."Уволен")            ) json_f          , json_build_object(              'Account'            , "Пользователь" >> 32            , 'Face'            , "Пользователь" & x'FFFFFFFF'::bigint            ) json_s          , *          FROM            "Пользователь"          WHERE            "Персона" = p."Персона" AND            (              "Главный" OR              (                "Уволен" IS DISTINCT FROM TRUE AND                "Удален" IS DISTINCT FROM TRUE              )            )          ORDER BY -- сортировка одна на всех            "ЕстьПользователь" DESC, "Входил" DESC        ) T    ) usjs  FROM    "Персона" p  WHERE-- 8< --    "Персона" = ANY($1::uuid[])-- 8< --), T1 AS (  SELECT    "Персона"  , "Фамилия"  , "Имя"  , "Отчество"  , "ФамилияЛица"  , "ИмяЛица"  , "ОтчествоЛица"  , "Примечание"  , "Обновлено"  , "Город"  , coalesce("ОтдельныйПрофиль", FALSE) "ОтдельныйПрофиль"  , coalesce("ЕстьКорпАккаунт", FALSE) "ЕстьКорпАккаунт"  , NULL::bigint "Всего"  , "Группа"  , coalesce(usjs[1]::text[], '{}') "Users"  , coalesce(      (usjs[2]::json[])[1]    , (usjs[3]::json[])[1]    , (usjs[4]::json[])[1]    ) "PrimaryFaceAccount"  , ((usjs[5]::json[])[1] ->> 'Account')::bigint "MainAccount"  , ARRAY(      SELECT        "Значение"::int      FROM        "КонтактныеДанные"      WHERE        persons."Группа" AND        "Персона" = persons."Персона" AND        "Тип" = 'account'    ) "АккаунтыГруппы"  , "Пол"  , "Логин"  , "ДатаРождения"  , "ДатаРегистрации"  FROM    persons)SELECT  CASE    WHEN "ЕстьКорпАккаунт" OR "MainAccount" IS NOT NULL THEN      "ФамилияЛица"  ELSE    "Фамилия"  END "LastName", CASE    WHEN "ЕстьКорпАккаунт" OR "MainAccount" IS NOT NULL THEN      "ИмяЛица"    ELSE      "Имя"  END "FirstName", CASE    WHEN "ЕстьКорпАккаунт" OR "MainAccount" IS NOT NULL THEN      "ОтчествоЛица"    ELSE      "Отчество"  END "PatronymicName", *FROM  T1;

1. Зачем нам лишняя CTE T1 (ведь CTE Scan стоит ресурсов)?
2. Зачем мы один и тот же список полей переписываем дважды?
3. Зачем дважды применяется coalesce на одни и те же поля?

WITH p AS (  SELECT    *-- 8< --  , CASE      WHEN nullif("ФамилияЛица", '') IS NOT NULL THEN        ARRAY[          coalesce("ФамилияЛица", '')        , coalesce("ИмяЛица", '')        , coalesce("ОтчествоЛица", '')        ]      ELSE        ARRAY[          coalesce("Фамилия", '')        , coalesce("Имя", '')        , coalesce("Отчество", '')        ]    END fio-- 8< --  , (      SELECT        ARRAY[ -- массив сериализованных json[]          array_agg(json_f) FILTER (WHERE -- фильтрация по необходимому условию            "Уволен" IS DISTINCT FROM TRUE AND            "Удален" IS DISTINCT FROM TRUE          )::text        , array_agg(json_s) FILTER (WHERE            "Уволен" IS DISTINCT FROM TRUE AND            "Удален" IS DISTINCT FROM TRUE AND            "Пользователь" >> 32 = 5313189::int          )::text        , array_agg(json_s) FILTER (WHERE            "Уволен" IS DISTINCT FROM TRUE AND            "Удален" IS DISTINCT FROM TRUE AND            "Главный"          )::text        , array_agg(json_s) FILTER (WHERE            "Уволен" IS DISTINCT FROM TRUE AND            "Удален" IS DISTINCT FROM TRUE          )::text        , array_agg(json_s) FILTER (WHERE            "Главный"          )::text        ] usjs      FROM        (          SELECT -- готовим JSON для каждой записи сразу, потом только используем готовый            json_build_object(              'Account'            , "Пользователь" >> 32            , 'Face'            , "Пользователь" & x'FFFFFFFF'::bigint            , 'HasUser'            , coalesce("ЕстьПользователь", TRUE)            , 'HasLoggedIn'            , coalesce("ЕстьПользователь", TRUE) AND coalesce("Входил", TRUE)            , 'Fired'            , coalesce("Уволен", p."Уволен")            ) json_f          , json_build_object(              'Account'            , "Пользователь" >> 32            , 'Face'            , "Пользователь" & x'FFFFFFFF'::bigint            ) json_s          , *          FROM            "Пользователь"          WHERE            "Персона" = p."Персона" AND            (              "Главный" OR              (                "Уволен" IS DISTINCT FROM TRUE AND                "Удален" IS DISTINCT FROM TRUE              )            )          ORDER BY -- сортировка одна на всех            "ЕстьПользователь" DESC, "Входил" DESC        ) T    ) usjs  FROM    "Персона" p  WHERE    "Персона" = ANY($1::uuid[]))-- 8< --SELECT  CASE    WHEN "ЕстьКорпАккаунт" OR "MainAccount" IS NOT NULL THEN      "ФамилияЛица"  ELSE    "Фамилия"  END "LastName", CASE    WHEN "ЕстьКорпАккаунт" OR "MainAccount" IS NOT NULL THEN      "ИмяЛица"    ELSE      "Имя"  END "FirstName", CASE    WHEN "ЕстьКорпАккаунт" OR "MainAccount" IS NOT NULL THEN      "ОтчествоЛица"    ELSE      "Отчество"  END "PatronymicName", *FROM  (    SELECT      "Персона"    , coalesce("Фамилия", '') "Фамилия"    , coalesce("Имя", '') "Имя"    , coalesce("Отчество", '') "Отчество"-- 8< --    , fio[1] "ФамилияЛица"    , fio[2] "ИмяЛица"    , fio[3] "ОтчествоЛица"-- 8< --    , "Примечание"    , "Обновлено"    , "Город"    , coalesce("ОтдельныйПрофиль", FALSE) "ОтдельныйПрофиль"    , coalesce("ЕстьКорпАккаунт", FALSE) "ЕстьКорпАккаунт"    , NULL::bigint "Всего"    , "Группа"    , coalesce(usjs[1]::text[], '{}') "Users"    , coalesce(        (usjs[2]::json[])[1]      , (usjs[3]::json[])[1]      , (usjs[4]::json[])[1]      ) "PrimaryFaceAccount"    , ((usjs[5]::json[])[1] ->> 'Account')::bigint "MainAccount"    , ARRAY(        SELECT          "Значение"::int        FROM          "КонтактныеДанные"        WHERE          p."Группа" AND-- 8< --          ("Персона", "Тип") = (p."Персона", 'account')-- 8< --      ) "АккаунтыГруппы"    , "Пол"    , "Логин"    , "ДатаРождения"    , "$Создано"::date "ДатаРегистрации"    FROM      p  ) T;-- 8< --

#3: Дело о развесистой клюкве

В своем докладе на конференции я говорил, что самая большая проблема, с которой приходится сталкиваться при оптимизации запросов алгоритмическая. То есть когда разработчик не заморачивается пониманием того, как конкретно БД должна/будет выполнять запрос.

Классический пример цепочка JOIN'ов, приводящая к развесистой клюкве из Nested Loop/Hash Join/Merge Join в плане. В особо клинических случаях к ней добавляется схлапывание полученной матрицы с помощью DISTINCT/GROUP BY.

Оригинальный запрос, 10.1мс, 11600 buffers

SELECT DISTINCT ON (db."@ПулСерверов")  group_id."@ПулСерверов" "ИдГруппы", group_id."Название" "ИмяГруппы", CASE    WHEN group_id."Название" = 'Управление облаком' THEN      TRUE  ELSE    FALSE  END "ЭтоУправлениеОблаком", group_id."Тип" "Тип", group_id."Заблокирован" "Заблокирован", CASE    WHEN group_id."Тип" = 15 THEN      app."Код"  ELSE    group_id."Код"  END "Код", is_demo."@ПулСерверов" is not null "Демо", group_ext_id."ДопустимоеЧислоПользователей" "ДопустимоеЧислоПользователей", group_ext_id."Состояние" "Состояние", db."@ПулСерверов" "ИдБД", db_name."ИмяБД" "ИмяБД", hosts."Название" "ХостБД", db_name."Порт" "ПортБД", group_id. "Отстойник" "Отстойник", (    WITH params AS(      SELECT        cpv."Значение"      , cpv."Сайт"      FROM        "ОбщиеПараметры" cp      INNER JOIN        "ЗначенияОбщихПараметров" cpv          ON cp."@ОбщиеПараметры" = cpv."ОбщиеПараметры"      WHERE        cp."Название" = 'session_cache_time' AND        (cpv."Сайт" = 9 or cpv."Сайт" is null)    )    SELECT      coalesce(        (SELECT "Значение" FROM params WHERE "Сайт" = 9)      , (SELECT "Значение" FROM params WHERE "Сайт" IS NULL)      , (SELECT "ЗначениеПоУмолчанию" FROM "ОбщиеПараметры" WHERE "Название" = 'session_cache_time')      , 60::text      )::integer  ) "ТаймаутКэша", CASE    WHEN nullif(111, 0) IS NULL THEN      NULL    WHEN 111 = group_id."@ПулСерверов" THEN      TRUE    ELSE      FALSE  END "Эталонная", site."@Сайт" "ИдСайта", site."Адрес" "ИмяСайта"FROM  "ПулСерверов" group_idJOIN  "ПулРасширение" group_ext_id    ON group_id."@ПулСерверов" = group_ext_id."@ПулСерверов" AND NOT (group_id."@ПулСерверов" = ANY('{}'::integer[]))JOIN  "ПулСерверов" folder_db    ON group_id."@ПулСерверов" = folder_db."Раздел"JOIN  "ПулСерверов" db    ON folder_db."@ПулСерверов" = db."Раздел"LEFT JOIN  "Сервер" hosts    ON db."Сервер" = hosts."@Сервер"JOIN  "БазаДанных" db_name    ON db."@ПулСерверов" = db_name."@ПулСерверов"LEFT JOIN  (    WITH list_demo_app AS (      SELECT        ps0."ПулСерверов"      FROM        "ОбщиеПараметры" p0      INNER JOIN        "ОбщиеПараметры" p1          ON p1."Раздел" = p0."@ОбщиеПараметры" AND p0."Название" = 'Управление облаком'      INNER JOIN        "ОбщиеПараметры" p2          ON p2."Раздел" = p1."@ОбщиеПараметры" AND p1."Название" = 'Шайтан' AND p2."Название" = 'ЭтоДемонстрационнаяГруппа'      INNER JOIN        "ОбщиеПараметрыСервис" ps0          ON ps0."ОбщиеПараметры" = p2."@ОбщиеПараметры"    )    , list_demo_srv AS (      SELECT        pool1."@ПулСерверов"      FROM        list_demo_app ls      INNER JOIN        "ПулСерверов" pool0          ON ls."ПулСерверов" = pool0."@ПулСерверов"      INNER JOIN        "ПулСерверов" pool1          ON pool1."Раздел" = pool0."@ПулСерверов" AND pool1."Тип" = 15    )    SELECT      "@ПулСерверов"    FROM      list_demo_srv  ) is_demo    ON is_demo."@ПулСерверов" = group_id."@ПулСерверов"JOIN  "ПулСерверов" app    ON group_id."Раздел" = app."@ПулСерверов"LEFT JOIN  "Приложение" service    ON service."ПулСерверов" = group_id."@ПулСерверов"LEFT JOIN  "СайтПриложение" site_app    ON site_app."Приложение" = service."Раздел"LEFT JOIN  "Сайт" site    ON site."@Сайт" = site_app."Сайт"WHERE  group_id."Тип" = 15 AND  folder_db."Тип" = 8 AND  db."Тип" = 4 AND  db_name."ИмяБД" IS NOT NULL AND  (    (1 = 1 AND is_demo."@ПулСерверов" IS NOT NULL) OR    (1 = 2 AND is_demo."@ПулСерверов" IS NULL) OR    1 NOT IN (1, 2)  );

1. В запросе используется 11 таблиц, провязанных JOIN'ами Это очень смело. Чтобы так делать безболезненно, вы должны точно знать, что после каждого шага связывания количество записей будет ограничено, буквально, единицами. Иначе рискуете получить join 1000 x 1000.
2. Внимательно смотрим на запрос и строим понятийную модель БД. Разработчику, который это писал проще он ее и так знает, а нам придется восстановить на основе условий соединений, названий полей и бытовой логики. Вообще, если вы графически представляете, как у вас устроена БД, это может сильно помочь с написанием хорошего запроса. У меня получилось вот так:
   
   
   
3. За счет DISTINCT ON(db."@ПулСерверов") мы ожидаем результат, уникализованный по записи db, в нашей схеме она вон аж в каком низу Но посмотрим на условия запроса в самом низу они из каждой сущности (group_id, folder_db, db) отсекают сверху вниз по значению типа существенные куски.
4. Теперь самое интересное вложенный запрос, создающий выборку is_demo. Заметим, что его тело не зависит ни от чего то есть его можно смело поднять в самое начало основного WITH-блока. То есть лишнее выделение в подзапрос тут только усложняет все без какого-либо профита.
5. Заметим, что условия is_demo."@ПулСерверов" = group_id."@ПулСерверов" и is_demo."@ПулСерверов" IS NOT NULL при LEFT JOIN этих таблиц, фактически, означает необходимость присутствия PK group_id среди идентификаторов в is_demo.
   
   Самое очевидное, что тут можно сделать так и переписать запрос, отбирая записи group_id по набору идентификаторов is_demo.
6. Переписываем извлечение этих сущностей в независимые CTE, и с удивлением замечаем, что у нас на БД отсутствуют подходящие индексы по ПулСерверов(Тип, Раздел). Причем эти типы константны с точки зрения приложения, поэтому лучше триплет индексов ПулСерверов(Раздел) WHERE Тип = ....
7. Вспомним, что пересечение нескольких CTE может быть весьма затратным, и заменим его на JOIN через словарь, предварительно сформировав его из записей group_id, folder_db и db ведь это одна исходная таблица ПулСерверов.
8. Вложенный запрос получения параметра ТаймаутКэша просто переписываем, избавляя от ненужных CTE.

Результат: 0.4мс (в 25 раз лучше), 134 buffers (в 86 раз лучше)

WITH demo_app AS (  SELECT    ps0."ПулСерверов"  FROM    "ОбщиеПараметры" p0  JOIN    "ОбщиеПараметры" p1      ON (p1."Раздел", p1."Название") = (p0."@ОбщиеПараметры", 'Шайтан')  JOIN    "ОбщиеПараметры" p2      ON (p2."Раздел", p2."Название") = (p1."@ОбщиеПараметры", 'ЭтоДемонстрационнаяГруппа')  JOIN    "ОбщиеПараметрыСервис" ps0      ON ps0."ОбщиеПараметры" = p2."@ОбщиеПараметры"  WHERE    p0."Название" = 'Управление облаком'), demo_srv as(  SELECT    pool1."@ПулСерверов"  FROM    demo_app ls  JOIN    "ПулСерверов" pool0      ON ls."ПулСерверов" = pool0."@ПулСерверов"  JOIN    "ПулСерверов" pool1      ON (pool1."Тип", pool1."Раздел") = (15, pool0."@ПулСерверов") -- CREATE INDEX CONCURRENTLY "iПС-tmp0-t15" ON "ПулСерверов"("Раздел") WHERE "Тип" = 15;), grp AS (  SELECT    grp  FROM    "ПулСерверов" grp  WHERE    "Тип" = 15 AND    "@ПулСерверов" = ANY(ARRAY(      SELECT        "@ПулСерверов"      FROM        demo_srv    ))), fld AS (  SELECT    fld  FROM    "ПулСерверов" fld  WHERE    "Раздел" = ANY(ARRAY(      SELECT        (grp)."@ПулСерверов"      FROM        grp    )) AND    "Тип" = 8 -- CREATE INDEX CONCURRENTLY "iПС-tmp0-t8" ON "ПулСерверов"("Раздел") WHERE "Тип" = 8;), dbs AS (  SELECT    dbs  FROM    "ПулСерверов" dbs  WHERE    "Раздел" = ANY(ARRAY(      SELECT        (fld)."@ПулСерверов"      FROM        fld    )) AND    "Тип" = 4 -- CREATE INDEX CONCURRENTLY "iПС-tmp0-t4" ON "ПулСерверов"("Раздел") WHERE "Тип" = 4;), srvhs AS (  SELECT    hstore(      array_agg((dbs)."@ПулСерверов"::text)    , array_agg((dbs)::text)    )  FROM    (      TABLE dbs    UNION ALL      TABLE fld    UNION ALL      TABLE grp    ) T)SELECT  (grp)."@ПулСерверов" "ИдГруппы", (grp)."Название" "ИмяГруппы", (grp)."Название" IS NOT DISTINCT FROM 'Управление облаком' "ЭтоУправлениеОблаком", (grp)."Тип", (grp)."Заблокирован", CASE    WHEN (grp)."Тип" = 15 THEN      app."Код"    ELSE      (grp)."Код"  END "Код", TRUE "Демо", grpe."ДопустимоеЧислоПользователей", grpe."Состояние", (dbn)."@ПулСерверов" "ИдБД", dbn."ИмяБД", dbh."Название" "ХостБД", dbn."Порт" "ПортБД", (grp)."Отстойник", (    SELECT      coalesce(        (          SELECT            "Значение"          FROM            "ЗначенияОбщихПараметров"          WHERE            "ОбщиеПараметры" = cp."@ОбщиеПараметры" AND            coalesce("Сайт", 9) = 9          ORDER BY            "Сайт" NULLS LAST          LIMIT 1        )      , "ЗначениеПоУмолчанию"      , '60'      )::integer    FROM      "ОбщиеПараметры" cp    WHERE      "Название" = 'session_cache_time'  ) "ТаймаутКэша", CASE    WHEN nullif(111, 0) IS NULL THEN      NULL    WHEN (grp)."@ПулСерверов" = 111 THEN      TRUE    ELSE      FALSE  END "Эталонная", site."@Сайт" "ИдСайта", site."Адрес" "ИмяСайта"--, *FROM  dbsJOIN  "БазаДанных" dbn    ON dbn."@ПулСерверов" = (dbs.dbs)."@ПулСерверов"JOIN LATERAL  (    SELECT      ((TABLE srvhs)->((dbs)."Раздел"::text))::"ПулСерверов" fld  ) fld ON TRUEJOIN LATERAL  (    SELECT      ((TABLE srvhs)->((fld)."Раздел"::text))::"ПулСерверов" grp  ) grp ON TRUEJOIN  "ПулРасширение" grpe    ON grpe."@ПулСерверов" = (grp)."@ПулСерверов"JOIN  "ПулСерверов" app    ON app."@ПулСерверов" = (grp)."Раздел"JOIN  "Сервер" dbh    ON dbh."@Сервер" = (dbs)."Сервер"LEFT JOIN  "Приложение" srv    ON srv."ПулСерверов" = (grp)."@ПулСерверов"LEFT JOIN  "СайтПриложение" site_app    ON site_app."Приложение" = srv."Раздел"LEFT JOIN  "Сайт" site    ON site."@Сайт" = site_app."Сайт"WHERE  dbn."ИмяБД" IS NOT NULL;

• CTE x CTE
• редкая запись долетит до середины JOIN
• ударим словарем по тяжелому JOIN
• насколько глубока кроличья нора? пробежимся по иерархии
• DISTINCT и LIMIT: Должен остаться только один!

• 1000 и один способ агрегации
• пишем while-цикл прямо в запросе, или Элементарная трехходовка

• почти 9000 баз общим объемом 1PB
• свыше 200TB данных клиентов
• 1500 разработчиков работают с БД

• Анализ запросов
  Наглядно демонстрируем все тайны EXPLAIN [ANALYZE].
• SQL Antipatterns и оптимизация SQL
  Понимаем как [не] надо решать те или иные задачи в PostgreSQL и почему.
• SQL HowTo
  Пробуем подходы к реализации сложных алгоритмов на SQL для развлечения и с пользой.
• DBA
  Присматриваем за базой, чтобы ей легко дышалось.
• Прикладные решения
  Решаем с помощью PostgreSQL конкретные бизнес-задачи.

Анализ запросов в PostgreSQL

SQL Antipatterns и оптимизация SQL

SQL HowTo

DBA

Решения для PostgreSQL