Программа пишется для решения определенных задач за конкретные деньги.
ФОТ программистов легко конвертируется в затраты на "железо" и обратно. Грубо, чем проще написать программу, тем менее эффективно (долго, дорого) она будет выполняться. Иногда ради эффективности можно многим пожертвовать.
В традиционных бизнес-системах документы по клиентам имеют тенденцию повторяться неоднократно (счета ежемесячно, договора ежегодно, отгрузки постоянным клиентам, ...).
Можно вот так, и даже будет использоваться Run Condition:
WITH pre AS (
SELECT
cli.id
, dt
, row_number() OVER (PARTITION BY cli.id ORDER BY doc.dt DESC) rn
FROM
cli
JOIN
doc
ON doc.cli = cli.id
WHERE
cli.name LIKE '!%'
)
SELECT
id
, max(dt) OVER (PARTITION BY id) dt
FROM
pre
WHERE
rn = 2;
Имеется в виду "не хватает подходящего". Там на картинке в узле Index Scan rows=1, Rows Removed by Filter: 48668 - то есть мы вычитали тучу лишних записей ради одной нужной.
Еще с 9.1 требует не всегда. В какой-то из более поздних версий определение доступных к возврату столбцов было заменено на что-то типа "однозначно функционально вычислимые".
Тут, конечно, без плана исходного запроса добрая половина оптимизаций выглядит сделанной непонятно зачем, но попробуем зайти на задачу с точки зрения алгоритмов и прикладной логики.
Сформулируем условие: "Вывести TOP-50 активных (is_active = true) пользователей, у кого наберется хотя бы по 10K оборота, лидирующих по сумме (amount) отгрузок (status = 'completed') заказов, созданных (created_at) с 01.01.2023."
Из базовой прикладной логики можно предположить, что большинство пользователей, отгружавших на диапазоне последних 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) тоже может быть полезен, но или не для этой задачи, или если разных пользователей у нас всего десяток-полтора.
Если входная строка конкретно такая, то она невалидна согласно условию 4-й задачи - пробелы после двух последних запятых. Поэтому "ничего" - как раз корректный ответ, по условию.
А если их (пробелы) убрать, то запрос выдаст DC43.
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;
По 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;
Мне кажется, что эта задача банальным подсчетом точек изменений решается изящнее и эффективнее, чем через 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;
Программа пишется для решения определенных задач за конкретные деньги.
ФОТ программистов легко конвертируется в затраты на "железо" и обратно. Грубо, чем проще написать программу, тем менее эффективно (долго, дорого) она будет выполняться. Иногда ради эффективности можно многим пожертвовать.
CTE не пишется на диск (
temp buffers), пока влезает в work_memв PG массивы нумеруются с 1, а не с 0
В традиционных бизнес-системах документы по клиентам имеют тенденцию повторяться неоднократно (счета ежемесячно, договора ежегодно, отгрузки постоянным клиентам, ...).
"Не считает" или "не читает"?
Можно вот так, и даже будет использоваться
Run Condition:Только медленнее в 1.5 раза от исходного:
Даже на PG16 это не работает, не говоря о более ранних версиях:
Ну и параллелиться
WindowAggпока не умеет.Кроме того, подсчет номера записи в выборке все-таки требует иметь эту выборку (полный JOIN) "под ногами" - то есть грабли ровно те же.
Имеется в виду "не хватает подходящего". Там на картинке в узле Index Scan rows=1, Rows Removed by Filter: 48668 - то есть мы вычитали тучу лишних записей ради одной нужной.
https://www.postgresql.org/docs/release/9.1.0/
Еще с 9.1 требует не всегда. В какой-то из более поздних версий определение доступных к возврату столбцов было заменено на что-то типа "однозначно функционально вычислимые".
Причина еще может крыться в привычке писать как в моем примере ниже:
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_active10M - это еще маловато для секционирования, пожалуй. Да и агрегаты тут явно избыточны, если итоговый запрос пока уложился в 120ms.
Тут, конечно, без плана исходного запроса добрая половина оптимизаций выглядит сделанной непонятно зачем, но попробуем зайти на задачу с точки зрения алгоритмов и прикладной логики.
Сформулируем условие: "Вывести TOP-50 активных (
is_active = true) пользователей, у кого наберется хотя бы по 10K оборота, лидирующих по сумме (amount) отгрузок (status = 'completed') заказов, созданных (created_at) с 01.01.2023."Из базовой прикладной логики можно предположить, что большинство пользователей, отгружавших на диапазоне последних 3 лет, будут активными и по сей день (и чем этот интервал меньше, тем больше вероятность, что все отгружавшие - активны).
По этой причине эффективнее сразу "схлопнуть" все заказы интервала по пользователю.
Потом подтягиваем активных пользователей к полученной выборке и не забываем посортировать снова после JOIN.
Как-то примерно так:
И только после этого имеет смысл прикидывать, какие из индексов нам реально пригодятся тут:
Понятно, что индекс
orders(user_id, created_at)тоже может быть полезен, но или не для этой задачи, или если разных пользователей у нас всего десяток-полтора.Если входная строка конкретно такая, то она невалидна согласно условию 4-й задачи - пробелы после двух последних запятых. Поэтому "ничего" - как раз корректный ответ, по условию.
А если их (пробелы) убрать, то запрос выдаст
DC43.5-я задача на основе предыдущего варианта:
По 4-й задаче предложу решение чуть на иных механизмах:
Просто добавим
WHERE parameters > 1в последний запрос. ))К модели решения это не имеет существенного отношения.
Мне кажется, что эта задача банальным подсчетом точек изменений решается изящнее и эффективнее, чем через json, рекурсию или интервалы:
Вот тут описан этот и несколько других типовых алгоритмов над интервалами "с картинками".
У меня что с пробелами, что без - одно и тоже время с разбежкой в пределах погрешности.
Возможно, от версии PG или даже ОС зависит - у меня даже просто получение строки
'{"i" : ' || i || '}'без каста занимает примерно 570ms из 800ms.Все равно чуть хуже
row_to_jsonполучается (796ms vs 792ms), но много больше вариантов накосячить с экранированием становится.Забавный эффект... Но если избавиться от промежуточной группировки и пересечение массива искать внутри соединения:
... то удается выйти на 183мс: