All streams
Search
Write a publication
Pull to refresh
208
Karma
0.1
Global rating
Боровиков Кирилл @Kilor

Архитектура ИС: PostgreSQL, Node.js и highload

Send message
ProfileArticles171Posts6NewsComments699
CPU Utilization = 100%. Это проблема СУБД?

Имеется в виду "не хватает подходящего". Там на картинке в узле Index Scan rows=1, Rows Removed by Filter: 48668 - то есть мы вычитали тучу лишних записей ради одной нужной.

This comment wasn’t rated yet0
Look
PostgreSQL против 10 миллионов записей: оптимизация запросов, которая спасла наш проект

Allow non-GROUP BY columns in the query target list when the primary key is specified in the GROUP BY clause (Peter Eisentraut)

The SQL standard allows this behavior, and because of the primary key, the result is unambiguous.

https://www.postgresql.org/docs/release/9.1.0/

Еще с 9.1 требует не всегда. В какой-то из более поздних версий определение доступных к возврату столбцов было заменено на что-то типа "однозначно функционально вычислимые".

Total votes 2: ↑2 and ↓0+2
Look
PostgreSQL против 10 миллионов записей: оптимизация запросов, которая спасла наш проект

Причина еще может крыться в привычке писать как в моем примере ниже:
JOIN users u ON (u.id, u.is_active) = (o.user_id, TRUE)

vs.

JOIN users u ON u.id = o.user_id AND u.is_active

This comment wasn’t rated yet0
Look
PostgreSQL против 10 миллионов записей: оптимизация запросов, которая спасла наш проект

10M - это еще маловато для секционирования, пожалуй. Да и агрегаты тут явно избыточны, если итоговый запрос пока уложился в 120ms.

Total votes 2: ↑2 and ↓0+2
Look
PostgreSQL против 10 миллионов записей: оптимизация запросов, которая спасла наш проект

Тут, конечно, без плана исходного запроса добрая половина оптимизаций выглядит сделанной непонятно зачем, но попробуем зайти на задачу с точки зрения алгоритмов и прикладной логики.

Сформулируем условие: "Вывести TOP-50 активных (is_active = true) пользователей, у кого наберется хотя бы по 10K оборота, лидирующих по сумме (amount) отгрузок (status = 'completed') заказов, созданных (created_at) с 01.01.2023."

  1. Из базовой прикладной логики можно предположить, что большинство пользователей, отгружавших на диапазоне последних 3 лет, будут активными и по сей день (и чем этот интервал меньше, тем больше вероятность, что все отгружавшие - активны).

  2. По этой причине эффективнее сразу "схлопнуть" все заказы интервала по пользователю.

  3. Потом подтягиваем активных пользователей к полученной выборке и не забываем посортировать снова после JOIN.

Как-то примерно так:

SELECT
	u.id
,	u.name
,	o.order_count
,	o.total_amount
FROM
	(
		SELECT
			user_id
		,	count(id) order_count
		,	sum(amount) total_amount
		FROM
			orders
		WHERE
			status = 'completed' AND
			created_at >= '2023-01-01'
		GROUP BY
			user_id
		HAVING
			total_amount > 10000
		ORDER BY
			total_amount DESC
		LIMIT 50
	) o
JOIN
	users u
		ON (u.id, u.is_active) = (o.user_id, TRUE)
ORDER BY
	total_amount DESC;

И только после этого имеет смысл прикидывать, какие из индексов нам реально пригодятся тут:

CREATE INDEX ON orders(created_at) WHERE status = 'completed';
CREATE INDEX ON users(id) WHERE is_active = true;

Понятно, что индекс orders(user_id, created_at) тоже может быть полезен, но или не для этой задачи, или если разных пользователей у нас всего десяток-полтора.

Total votes 3: ↑3 and ↓0+3
Look
«IT-Планета 2025»: задачи второго этапа по PostgreSQL

Если входная строка конкретно такая, то она невалидна согласно условию 4-й задачи - пробелы после двух последних запятых. Поэтому "ничего" - как раз корректный ответ, по условию.

А если их (пробелы) убрать, то запрос выдаст DC43.

This comment wasn’t rated yet0
Look
«IT-Планета 2025»: задачи второго этапа по PostgreSQL

5-я задача на основе предыдущего варианта:

WITH gameparty AS (
  SELECT
    *
  , parts[1] code
  , regexp_split_to_array(parts[1], '') chars
  , parts[2]::integer b
  , parts[3]::integer c
  FROM
    regexp_split_to_table(upper(current_setting('bulls.gameparty')), ',')
       WITH ORDINALITY T(step, n)
  , regexp_match(step, '^([0-9A-F]{4}) (\d)B(\d)C$') parts -- заодно и формат проверяем
)
SELECT
  xcode code
FROM
  generate_series(0xFFFF, 0, -1) i -- нужен максимальный код, так что сразу генерим реверсивно (до первого подходящего, благодаря LIMIT)
, lpad(upper(to_hex(i)), 4, '0') xcode
, regexp_split_to_array(xcode, '') secret
, LATERAL (
    SELECT
      bool_and((b, c) = (xb, xc - xb)) chk -- совпадение быков/коров во всех ходах
    FROM
      gameparty
    , LATERAL (
        SELECT
          count(*) FILTER(WHERE x = y) xb
        FROM
          unnest(chars, secret) T(x, y)
      ) b
    , LATERAL (
        SELECT
          count(*) FILTER(WHERE x = ANY(secret)) xc
        FROM
          unnest(chars) T(x)
      ) c
  ) T
WHERE
  NOT EXISTS (           -- при корректности кодов в исходнике
    SELECT
      NULL
    FROM
      gameparty
    WHERE
      code IS NULL OR
      code ~ '(.).*\1'
    LIMIT 1
  ) AND
  xcode !~ '(.).*\1' AND -- отсекаем коды с повторными символами
  chk                    -- оставляем только удовлетворившие всем ходам
LIMIT 1;
Total votes 1: ↑1 and ↓0+1
Look
«IT-Планета 2025»: задачи второго этапа по PostgreSQL

По 4-й задаче предложу решение чуть на иных механизмах:

-- проверяем ходы и секрет вместе
WITH chk AS (
  SELECT
    *
  , regexp_split_to_array(code, '') chars
  , NOT(code !~ '(.).*\1' AND code ~ '^[0-9A-F]{4}$') is_error
  FROM
    regexp_split_to_table(upper(current_setting('bulls.secretcode') || ',' || current_setting('bulls.game')), ',')
      WITH ORDINALITY step(code, n)
)
-- PG выберет за нас подзапрос в зависимости от корректности данных
(
  SELECT
    'error' gameparty
  FROM
    chk
  GROUP BY
    1
  HAVING
    bool_or(is_error) -- хоть где-то есть ошибка
UNION ALL
  SELECT
    string_agg(code || ' ' || b || 'B' || (c - b) || 'C', ',' ORDER BY n)
  FROM
    chk
  , (
      SELECT
        chars secret
      FROM
        chk
      WHERE
        n = 1
    )
  , LATERAL (
      SELECT
        count(*) FILTER(WHERE x = y) b -- только "быки" на своих местах
      FROM
        unnest(chars, secret) T(x, y)
    ) b
  , LATERAL (
      SELECT
        count(*) FILTER(WHERE x = ANY(secret)) c -- "коровы" вместе с "быками"
      FROM
        unnest(chars) T(x)
    ) c
  WHERE
    n > 1
)
LIMIT 1;
Total votes 2: ↑2 and ↓0+2
Look
«IT-Планета 2025»: задачи второго этапа по PostgreSQL

Просто добавим WHERE parameters > 1 в последний запрос. ))

К модели решения это не имеет существенного отношения.

This comment wasn’t rated yet0
Look
«IT-Планета 2025»: задачи второго этапа по PostgreSQL

Мне кажется, что эта задача банальным подсчетом точек изменений решается изящнее и эффективнее, чем через json, рекурсию или интервалы:

WITH src(parameter_name, ts, value) AS (
VALUES ('max_connections', '2025-01-10 11:30'::timestamp, '500'),
       ('max_connections', '2025-01-11 12:30', NULL),
       ('max_connections', '2025-01-11 15:30', '500'),
       ('max_connections', '2025-01-12 12:30', NULL),
       ('max_connections', '2025-01-12 15:30', '500'),
       ('work_mem', '2025-01-10 11:30', '8MB'),
       ('work_mem', '2025-01-12 14:00', '16MB'),
       ('max_connections', '2025-01-12 14:00', '1000'),
       ('max_connections', '2025-01-17 12:30', NULL),
       ('shared_buffers', '2025-01-12 12:30', NULL),
       ('shared_buffers', '2025-01-12 13:30', '20000'),
       ('shared_buffers', '2025-01-14 10:00', NULL),
       ('shared_buffers', '2025-01-15 07:40', NULL),
       ('temp_buffers', '2025-01-09 10:20', '2000'),
       ('temp_buffers', '2025-01-11 11:30', null),
       ('temp_buffers', '2025-01-12 12:30', '1500'),
       ('temp_buffers', '2025-01-14 03:15', null),
       ('maintenance_work_mem', '2025-01-09 10:20','1000'),
       ('maintenance_work_mem', '2025-01-10 13:26', null),
       ('maintenance_work_mem', '2025-01-12 12:30', null),
       ('maintenance_work_mem', '2025-01-12 14:30', '1500'),
       ('maintenance_work_mem', '2025-01-15 14:30', null),
       ('max_worker_processes', '2025-01-08 00:30', '16'),
       ('max_worker_processes', '2025-01-12 14:00', null)
)
-- определяем "в точке", как должно измениться кол-во "недефолтных" параметров
, diff AS (
  SELECT
    *
  , CASE
      WHEN value IS NOT NULL AND lag(value) OVER(PARTITION BY parameter_name ORDER BY ts) IS     NULL THEN +1
      WHEN value IS     NULL AND lag(value) OVER(PARTITION BY parameter_name ORDER BY ts) IS NOT NULL THEN -1
      ELSE 0
    END diff
  FROM
    src
)
-- находим суммарное количество "недефолтных" в каждой точке
, sum AS (
  SELECT
    *
  , sum(diff) OVER(ORDER BY ts) parameters
  FROM
    diff
)
-- помечаем точки, изменяющие суммарное значение
, rngs AS (
  SELECT
    *
  , parameters IS DISTINCT FROM lag(parameters) OVER(ORDER BY ts) is_rng_start
  FROM
    sum
)
-- находим следующую точку изменения суммарного значения - это конец интервала
, rnge AS (
  SELECT
    *
  , lead(ts) OVER(ORDER BY ts) ts_end
  FROM
    rngs
  WHERE
    is_rng_start
)
-- отбираем максимум с учетом возможного дублирования
SELECT
  ts ts_start
, ts_end
, parameters
FROM
  rnge
ORDER BY
  parameters DESC
FETCH NEXT 1 ROWS
WITH TIES;
Total votes 3: ↑3 and ↓0+3
Look
Использование Daterange для поиска разрывов истории записей SCD2

Вот тут описан этот и несколько других типовых алгоритмов над интервалами "с картинками".

Total votes 1: ↑1 and ↓0+1
Look
PostgreSQL Antipatterns: создаем JSON из строки

У меня что с пробелами, что без - одно и тоже время с разбежкой в пределах погрешности.

This comment wasn’t rated yet0
Look
PostgreSQL Antipatterns: создаем JSON из строки

Возможно, от версии PG или даже ОС зависит - у меня даже просто получение строки '{"i" : ' || i || '}' без каста занимает примерно 570ms из 800ms.

This comment wasn’t rated yet0
Look
PostgreSQL Antipatterns: создаем JSON из строки

Все равно чуть хуже row_to_json получается (796ms vs 792ms), но много больше вариантов накосячить с экранированием становится.

This comment wasn’t rated yet0
Look
SQL HowTo: работаем с массивами (Advent of Code 2024, Day 23: LAN Party)

Забавный эффект... Но если избавиться от промежуточной группировки и пересечение массива искать внутри соединения:

WITH inp AS (
  SELECT
    pair[1] src
  , pair[2] dst
  FROM
    regexp_matches(
      $$
kh-tc
qp-kh
de-cg
ka-co
yn-aq
qp-ub
cg-tb
vc-aq
tb-ka
wh-tc
yn-cg
kh-ub
ta-co
de-co
tc-td
tb-wq
wh-td
ta-ka
td-qp
aq-cg
wq-ub
ub-vc
de-ta
wq-aq
wq-vc
wh-yn
ka-de
kh-ta
co-tc
wh-qp
tb-vc
td-yn
$$
    , '([a-z]{2})-([a-z]{2})'
    , 'g'
    ) pair
)
, directed AS (
  SELECT DISTINCT
    T.* -- (src, dst) | (dst, src) | (src, src) | (dst, dst)
  FROM
    inp
  , unnest(
      ARRAY[src, dst, src, dst]
    , ARRAY[dst, src, src, dst]
    ) T(src, dst)
)
SELECT
  array_to_string(grp, ',')
FROM
  (
    SELECT
      array_agg(d1.dst ORDER BY d1.dst) grp
    FROM
      directed d1
    JOIN
      directed d2
        ON d2.src = d1.dst AND -- вместо пересечения массивов
        d2.dst > d1.src
    GROUP BY
      d1.src, d2.dst
  ) T
, array_length(grp, 1) ln
GROUP BY
  grp
HAVING
  count(*) = array_length(grp, 1) * (array_length(grp, 1) - 1) / 2
ORDER BY
  count(*) DESC
LIMIT 1;

... то удается выйти на 183мс:

This comment wasn’t rated yet0
Look
SQL HowTo: работаем с массивами (Advent of Code 2024, Day 23: LAN Party)

Можно чуть ускорить вычисления пересечений массивов:

, crossing_lans AS (
  SELECT
    ARRAY(
      SELECT
        unnest(T1.links)
    INTERSECT ALL
      SELECT
        unnest(T2.links)
    ORDER BY
      1
    ) nodes
  FROM
    full_links T1
  JOIN
    full_links T2
      ON T2.nodefrom > T1.nodefrom
)

А вот подход с "арифметикой" и треугольными числами - это красиво, согласен, хотя и требует несколько более подробного объяснения, почему это так.

This comment wasn’t rated yet0
Look
SQL HowTo: работаем с массивами (Advent of Code 2024, Day 23: LAN Party)

"Нет предела совершенству". ))

Я поэтому и написал: "... мы не применяли вообще никаких упрощений и оптимизаций (типа предварительного формирования только пар содержащих имена "на t"), решая задачу "в лоб" самым простым и очевидным способом, то полный "кубический" перебор (два соединения) ..."

Так-то можно уложиться в 60мс, если анализировать только t-наборы:

 explain (analyze, costs off)
WITH src AS (
  SELECT
    regexp_matches(
      $$
kh-tc
qp-kh
de-cg
ka-co
yn-aq
qp-ub
cg-tb
vc-aq
tb-ka
wh-tc
yn-cg
kh-ub
ta-co
de-co
tc-td
tb-wq
wh-td
ta-ka
td-qp
aq-cg
wq-ub
ub-vc
de-ta
wq-aq
wq-vc
wh-yn
ka-de
kh-ta
co-tc
wh-qp
tb-vc
td-yn
$$
    , '([a-z]{2})-([a-z]{2})'
    , 'g'
    ) pair
)
, tname AS ( -- собираем, с кем связаны 't?'
  SELECT
    pair[1] src
  , array_agg(pair[2]) dst
  FROM
    (
      SELECT
        *
      FROM
        src
      WHERE
        pair[1] ^@ 't'
    UNION ALL
      SELECT
        ARRAY[pair[2], pair[1]]
      FROM
        src
      WHERE
        pair[2] ^@ 't'
    ) T
  GROUP BY
    1
)
SELECT
  count(*)                                       -- все записи
  - count(*) FILTER(
    WHERE
      (pair[1] ^@ 't' AND NOT pair[2] ^@ 't') OR
      (NOT pair[1] ^@ 't' AND pair[2] ^@ 't')
  ) / 2                                          -- исключаем посчитанные дважды ta|{tb,x} и tb|{ta,x}
  - count(*) FILTER(
    WHERE
      (pair[1] ^@ 't' AND pair[2] ^@ 't')
  ) / 3 * 2                                      -- исключаем посчитанные трижды ta|{tb,tc}, tb|{ta,tc} и tc|{ta,tb}
FROM
  tname
, src
WHERE
  pair <@ dst; -- оба элемента исходной пары связаны с одним и тем же 't?'

This comment wasn’t rated yet0
Look
SQL HowTo: оконные функции (Advent of Code 2024, Day 22: Monkey Market)

Согласен, так можно, только last_changes получается "развернутым" относительно условия. Но вроде на ответ это повлиять не должно.

Если взглянуть на этот кусок плана, то на блоке с парой WindowAgg так можно сэкономить:

Дальше лишь вопрос в длине массива-последовательности - при больших размерах операции над массивами становятся очень уж небыстрыми.

This comment wasn’t rated yet0
Look
SQL HowTo: оконные функции (Advent of Code 2024, Day 22: Monkey Market)

Не факт, что оно будет как-то существенно быстрее - это же на каждом шаге рекурсии к каждому массиву надо приписывать значение в конец и отбрасывать первое, если я понял идею правильно.

This comment wasn’t rated yet0
Look
SQL HowTo: динамическое программирование (Advent of Code 2024, Day 19: Linen Layout)

Иногда - лучше, иногда - нет. Все зависит и от объема передаваемых туда-сюда данных, и от сложности/удобства реализации алгоритма на конкретном ЯП.

This comment wasn’t rated yet0
Look
BackHere
1
23 ...
3435

Information

Rating
3,709-th
Location
Ярославль, Ярославская обл., Россия
Works in
Date of birth
Registered
Activity

Your account

Sections

Information

Services

Support
© 2006–2025, Habr