Немного о диапазонных типах в PostgreSQL и их влиянии на производительность

[Безотносительно конкретной задачи]

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

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

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

Немного о диапазонных типах в PostgreSQL и их влиянии на производительность

Я бы расстался с разработчиком, который, как Вы, стал настаивать на том, что он вправе в задаче использовать какие-то ограничения, которых нет в постановке задачи.

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

Вот мы смоделировали, уточнили применимость - можно рассматривать альтернативные варианты.

Немного о диапазонных типах в PostgreSQL и их влиянии на производительность

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

Так вот потому и отлаживать надо на данных, структурно приближенных к реальным. На относительно мелкой модели btree может выиграть, а на крупной - может и нет.

Основные средства - это вагоны, которых в конкретный момент под управлением может быть сотни тысяч. Суммарно же было под управлением за последние 20 лет - около миллиона.

Я не очень хорошо знаю примерный срок жизни вагона, но 100K/1M - это ни разу не "каждый третий". А когда еще лет через 5 окажется, что "в моменте" останется 75K/2M модель "от вагонов" может оказаться уже совсем неподходящей.

Немного о диапазонных типах в PostgreSQL и их влиянии на производительность

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

Как в статье - почему мы хотим отобрать аж целую треть объектов? Это достаточно много, и есть риск вообще попадать на Seq Scan, пока по каждому из них не накопится достаточно записей.

Мы делали аналогичную задачу по отпускам сотрудников (понятно, что они ровно так же не могут пересекаться) - так вот наиболее эффективным вариантом оказался тоже не btree_gist, а перебор "от длин отпуска" - просто потому, что их очень небольшое количество "в жизни". Отпуск обычно берут на 1, 2 дня, на неделю, на 2 недели или декретный (реальных вариантов побольше, но не сильно).

Немного о диапазонных типах в PostgreSQL и их влиянии на производительность

На сгенерировавшихся у меня данных модель запроса из статьи - 4.8ms:

Nested Loop  (cost=0.43..1471.40 rows=333 width=26) (actual time=0.065..4.819 rows=333 loops=1)
  ->  Function Scan on generate_series g  (cost=0.00..3.33 rows=333 width=4) (actual time=0.028..0.066 rows=333 loops=1)
  ->  Subquery Scan on a  (cost=0.43..4.40 rows=1 width=26) (actual time=0.014..0.014 rows=1 loops=333)
        Filter: (upper(a.date_period) > '2020-01-01'::date)
        ->  Limit  (cost=0.43..4.38 rows=1 width=30) (actual time=0.013..0.013 rows=1 loops=333)
              ->  Index Scan Backward using tmp_asset_location_asset_date_period on tmp_asset_location t  (cost=0.43..1669.91 rows=422 width=30) (actual time=0.013..0.013 rows=1 loops=333)
                    Index Cond: ((asset_id = g.n) AND (lower(date_period) <= '2020-01-01'::date))
Planning Time: 0.223 ms
Execution Time: 4.880 ms

Теперь внимательно смотрим на ограничения генерации, что интервал у нас заведомо не превышает 15 дней. Исходя из этого запрос можно эффективнее переписать через поиск "от дат" вместо "от объекта" - 2.7ms:

EXPLAIN ANALYZE
SELECT DISTINCT ON(asset_id) -- могло попасть несколько интервалов объекта в эти 15 дней
  *
FROM
  tmp_asset_location
WHERE
  lower(date_period) BETWEEN '2020-01-01'::date - 15 AND '2020-01-01'::date AND
  asset_id IN (SELECT generate_series(3,1000,3))
ORDER BY
  asset_id, lower(date_period) DESC

Unique  (cost=5542.70..5553.25 rows=998 width=30) (actual time=2.499..2.602 rows=333 loops=1)
  ->  Sort  (cost=5542.70..5547.97 rows=2109 width=30) (actual time=2.498..2.528 rows=712 loops=1)
        Sort Key: tmp_asset_location.asset_id, (lower(tmp_asset_location.date_period)) DESC
        Sort Method: quicksort  Memory: 63kB
        ->  Nested Loop  (cost=2.94..5426.26 rows=2109 width=30) (actual time=0.127..2.240 rows=712 loops=1)
              ->  HashAggregate  (cost=2.52..4.52 rows=200 width=4) (actual time=0.097..0.165 rows=333 loops=1)
                    Group Key: generate_series(3, 1000, 3)
                    Batches: 1  Memory Usage: 61kB
                    ->  ProjectSet  (cost=0.00..1.68 rows=333 width=4) (actual time=0.003..0.025 rows=333 loops=1)
                          ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.001..0.001 rows=1 loops=1)
              ->  Index Scan using tmp_asset_location_asset_date_period on tmp_asset_location  (cost=0.43..27.02 rows=6 width=26) (actual time=0.004..0.005 rows=2 loops=333)
                    Index Cond: ((asset_id = (generate_series(3, 1000, 3))) AND (lower(date_period) >= '2019-12-17'::date) AND (lower(date_period) <= '2020-01-01'::date))
Planning Time: 0.346 ms
Execution Time: 2.686 ms

Теперь подложим под него же чуть более подходящий индекс - и уже 2.1ms:

DROP INDEX tmp_asset_location_asset_date_period;
CREATE UNIQUE INDEX tmp_asset_location_lower_asset ON tmp_asset_location(lower(date_period), asset_id);

Unique  (cost=9236.34..9246.89 rows=998 width=30) (actual time=1.879..1.988 rows=333 loops=1)
  ->  Sort  (cost=9236.34..9241.61 rows=2109 width=30) (actual time=1.878..1.913 rows=712 loops=1)
        Sort Key: tmp_asset_location.asset_id, (lower(tmp_asset_location.date_period)) DESC
        Sort Method: quicksort  Memory: 63kB
        ->  Hash Join  (cost=144.35..9119.90 rows=2109 width=30) (actual time=0.446..1.632 rows=712 loops=1)
              Hash Cond: (tmp_asset_location.asset_id = (generate_series(3, 1000, 3)))
              ->  Bitmap Heap Scan on tmp_asset_location  (cost=137.33..9067.53 rows=6332 width=26) (actual time=0.282..1.062 rows=2119 loops=1)
                    Recheck Cond: ((lower(date_period) >= '2019-12-17'::date) AND (lower(date_period) <= '2020-01-01'::date))
                    Heap Blocks: exact=523
                    ->  Bitmap Index Scan on tmp_asset_location_lower_asset  (cost=0.00..135.75 rows=6332 width=0) (actual time=0.222..0.223 rows=2119 loops=1)
                          Index Cond: ((lower(date_period) >= '2019-12-17'::date) AND (lower(date_period) <= '2020-01-01'::date))
              ->  Hash  (cost=4.52..4.52 rows=200 width=4) (actual time=0.155..0.156 rows=333 loops=1)
                    Buckets: 1024  Batches: 1  Memory Usage: 20kB
                    ->  HashAggregate  (cost=2.52..4.52 rows=200 width=4) (actual time=0.088..0.122 rows=333 loops=1)
                          Group Key: generate_series(3, 1000, 3)
                          Batches: 1  Memory Usage: 61kB
                          ->  ProjectSet  (cost=0.00..1.68 rows=333 width=4) (actual time=0.003..0.023 rows=333 loops=1)
                                ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.001 rows=1 loops=1)
Planning Time: 0.309 ms
Execution Time: 2.082 ms

А если теперь перебрать все подходящие даты "вручную" и поискать в каждой, то становится уже 0.6ms:

EXPLAIN ANALYZE
SELECT DISTINCT ON(asset_id)
  *
FROM
  generate_series('2020-01-01'::date - 15, '2020-01-01'::date, '1 day') dt
, tmp_asset_location
WHERE
  lower(date_period) = dt::date AND -- точечно ищем подходящие объекты в каждой из дат
  asset_id IN (SELECT generate_series(3,1000,3))
ORDER BY
  asset_id, lower(date_period) DESC

Unique  (cost=464862.49..475405.50 rows=1000 width=38) (actual time=644.209..644.323 rows=333 loops=1)
  ->  Sort  (cost=464862.49..470133.99 rows=2108601 width=38) (actual time=644.208..644.243 rows=712 loops=1)
        Sort Key: tmp_asset_location.asset_id, (lower(tmp_asset_location.date_period)) DESC
        Sort Method: quicksort  Memory: 69kB
        ->  Merge Join  (cost=79550.77..128058.57 rows=2108601 width=38) (actual time=643.241..643.933 rows=712 loops=1)
              Merge Cond: (((dt.dt)::date) = (lower(tmp_asset_location.date_period)))
              ->  Sort  (cost=59.84..62.34 rows=1000 width=8) (actual time=6.051..6.054 rows=16 loops=1)
                    Sort Key: ((dt.dt)::date)
                    Sort Method: quicksort  Memory: 25kB
                    ->  Function Scan on generate_series dt  (cost=0.01..10.01 rows=1000 width=8) (actual time=5.964..5.969 rows=16 loops=1)
              ->  Materialize  (cost=79490.93..81599.53 rows=421720 width=26) (actual time=510.997..602.200 rows=324725 loops=1)
                    ->  Sort  (cost=79490.93..80545.23 rows=421720 width=26) (actual time=510.992..564.428 rows=324725 loops=1)
                          Sort Key: (lower(tmp_asset_location.date_period))
                          Sort Method: external merge  Disk: 17344kB
                          ->  Hash Join  (cost=7.02..29999.29 rows=421720 width=26) (actual time=0.305..326.025 rows=421957 loops=1)
                                Hash Cond: (tmp_asset_location.asset_id = (generate_series(3, 1000, 3)))
                                ->  Seq Scan on tmp_asset_location  (cost=0.00..21976.27 rows=1266427 width=26) (actual time=0.029..102.729 rows=1266427 loops=1)
                                ->  Hash  (cost=4.52..4.52 rows=200 width=4) (actual time=0.167..0.168 rows=333 loops=1)
                                      Buckets: 1024  Batches: 1  Memory Usage: 20kB
                                      ->  HashAggregate  (cost=2.52..4.52 rows=200 width=4) (actual time=0.099..0.133 rows=333 loops=1)
                                            Group Key: generate_series(3, 1000, 3)
                                            Batches: 1  Memory Usage: 61kB
                                            ->  ProjectSet  (cost=0.00..1.68 rows=333 width=4) (actual time=0.003..0.021 rows=333 loops=1)
                                                  ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.001..0.001 rows=1 loops=1)
Planning Time: 0.543 ms
Execution Time: 656.556 ms

SQL HowTo: от частностей к рекурсии (Advent of Code 2025, Day 3: Lobby)

Зависит от смысла, вкладываемого в термин "обход 2D лабиринта".

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

Просто в качестве примера: день 4 (читайте завтра пост) я сначала попробовал решить "втупую" переведя вход в двумерный массив - и за 20 минут запрос не досчитался.

PostgreSQL Antipatterns? Анализ эффективности замены агрегатной функции MAX на ARRAY

Rows Removed by Join Filter: 680711

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

А оба приведенные здесь плана отбрасывают примерно в 6 раз больше записей соединения, чем выдают полезных.

PostgreSQL Antipatterns: отказ от агрегатных функций = кратное ускорение

Так LATERAL подразумевает неявный вызов JOIN согласно документации?

В случае возврата из LATERAL единственной записи, в плане видно, что узлов соединений (Nested Loop, ... Join) не появляется. Вот если их хотя бы две, как вот тут, то приходится тратиться и на соединение.

PostgreSQL Antipatterns: отказ от агрегатных функций = кратное ускорение

Если немного развить тему, то можно "агрегацию" оконными функциями внести внутрь скобок:

SELECT
  cli.id
, first_value dt
FROM
  cli
, LATERAL (
    SELECT
      first_value(dt) OVER w
    , nth_value(dt, 2) OVER w
    FROM
      doc
    WHERE
      cli = cli.id
    WINDOW w AS(ORDER BY cli, dt DESC ROWS UNBOUNDED PRECEDING)
    ORDER BY
      cli, dt DESC
    LIMIT 2
  ) doc
WHERE
  cli.name LIKE '!%' AND
  nth_value IS NOT NULL;

Но результат все равно чуть хуже из-за необходимости фильтрации "первых" записей при соединении:

PostgreSQL Antipatterns: отказ от агрегатных функций = кратное ускорение

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

Если закинете планы на explain, будут более наглядно видны затраты на каждом из узлов. Разницу в последних примерах я отношу к погрешности измерений - у меня до 10% разброс между замерами получался, но в статью брал минимально достигнутые.

PostgreSQL Antipatterns: отказ от агрегатных функций = кратное ускорение

При отсутствии параллельных узлов операции плана выполняются последовательно. Или речь о нескольких одновременно запущенных запросах? Но это не влияет же на распределение user/system.

PostgreSQL Antipatterns: отказ от агрегатных функций = кратное ускорение

Но, в условиях высокой нагрузки и параллельности - разница в производительности на порядки.

Параллельность тут вообще не при чем. Ни в одном из моих планов она даже не пыталась использоваться.

Вывод: В тесте "MAX" CPU интенсивнее используется приложением, тогда как в "ARRAY" выше нагрузка на ядро.

Тут все достаточно очевидно - при агрегации доминируют затраты на JOIN, производимые как раз в usertime. При его отсутствии, за счет уменьшения общего времени, увеличивается доля чтений данных в systime.

PostgreSQL Antipatterns: отказ от агрегатных функций = кратное ускорение

LIMIT 2 - это чтобы нашлась вторая запись, если она есть - чтобы проверить условие задачи "документов по нему несколько".

PostgreSQL Antipatterns: отказ от агрегатных функций = кратное ускорение

Программа пишется для решения определенных задач за конкретные деньги.

ФОТ программистов легко конвертируется в затраты на "железо" и обратно. Грубо, чем проще написать программу, тем менее эффективно (долго, дорого) она будет выполняться. Иногда ради эффективности можно многим пожертвовать.

PostgreSQL Antipatterns: отказ от агрегатных функций = кратное ускорение

1. CTE не пишется на диск (temp buffers), пока влезает в work_mem

2. в PG массивы нумеруются с 1, а не с 0

PostgreSQL Antipatterns: отказ от агрегатных функций = кратное ускорение

В традиционных бизнес-системах документы по клиентам имеют тенденцию повторяться неоднократно (счета ежемесячно, договора ежегодно, отгрузки постоянным клиентам, ...).

PostgreSQL Antipatterns: отказ от агрегатных функций = кратное ускорение

"Не считает" или "не читает"?

Можно вот так, и даже будет использоваться 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;

Только медленнее в 1.5 раза от исходного:

PostgreSQL Antipatterns: отказ от агрегатных функций = кратное ускорение

Даже на PG16 это не работает, не говоря о более ранних версиях:

ERROR:  window functions are not allowed in WHERE
LINE 11:   row_number() OVER (PARTITION BY cli.id ORDER BY doc.dt DES...

Ну и параллелиться WindowAgg пока не умеет.

Кроме того, подсчет номера записи в выборке все-таки требует иметь эту выборку (полный JOIN) "под ногами" - то есть грабли ровно те же.

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

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

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 требует не всегда. В какой-то из более поздних версий определение доступных к возврату столбцов было заменено на что-то типа "однозначно функционально вычислимые".