Красота партиционирования.

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

Логически это всё ещё та же таблица, но при запросах база понимает, в какую партицию лезть, если ключ партиции указан в where.

Такой подход даёт серьёзный прирост производительности и масштабируемости. Можно держать старые партиции, к которым почти не обращаются, на обычных HDD, а свежие — на быстрых SSD.

На каждую партицию можно вешать свои индексы, и они будут куда компактнее, чем один монолитный индекс на всю таблицу.

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

👉 @SQLPortal

Производительность PostGIS: предикаты пересечения и оверлеи. Новая статья от Пола Рэмзи.

В чём разница между булевыми ST_Intersects и ST_Contains и операциями оверлея ST_Intersection и ST_Difference?

Сегодня разберем самые эффективные подходы к выборке объектов внутри других объектов.

Обычно мелкие геометрии обрезаются по границе с помощью ST_Intersection.

Наивный SQL — это обычный spatial join по ST_Intersects:

SELECT ST_Intersection(polygon.geom, p.geom) AS geom
  FROM parcels p
  JOIN polygon
    ON ST_Intersects(polygon.geom, p.geom);

На небольшом тестовом наборе, как на картинках, запрос выполняется примерно за 14 мс. Это быстро, но задача маленькая, т.е. на больших данных будет заметно медленнее.

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

Что это значит?

Булевые пространственные предикаты - это функции вроде ST_Intersects и ST_Contains. Они принимают две геометрии и возвращают true/false в зависимости от результата проверки.

Операции пространственного оверлея . вроде ST_Intersection или ST_Difference - получают две геометрии и генерируют новую.

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

Ускорение работы с оверлеями основано на простой идее = многие объекты вообще не нужно клиппать, если они полностью внутри полигональной границы. Их можно определить с помощью ST_Contains.

Есть и меньшая группа объектов, которые пересекают границу, их уже нужно обрабатывать через ST_Intersection — те, что ST_Intersects, но не ST_Contains.

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

SELECT
  CASE
    WHEN ST_Contains(polygon.geom, p.geom) THEN p.geom
    ELSE ST_Intersection(polygon.geom, p.geom)
  END AS geom
  FROM parcels p
  JOIN polygon
    ON ST_Intersects(polygon.geom, p.geom);

Здесь используются два предиката:

– ST_Intersects в join выбирает только кандидатов для обработки
– ST_Contains внутри CASE пропускает геометрии, которые не нужно клиппить

На том же тестовом наборе запрос выполняется примерно за 9 мс - разница настолько заметна, что её видно даже на маленьком примере.

Суть подхода: комбинируем предикаты и оверлеи через CASE

Главная идея = булевые пространственные проверки (ST_Contains, ST_Intersects) значительно дешевле, чем операции вроде ST_Intersection. Обычный подход клиппит все пересекающиеся геометрии. Оптимизированный вариант использует CASE и ST_Contains, чтобы сделать shortcut: если объект полностью внутри. то просто возвращаем его без изменений и не вызываем дорогой ST_Intersection.

Этот шаблон можно применять во всех задачах в PostGIS, где есть клиппинг, spatial join или overlay, и где часть объектов ожидаемо находится целиком внутри границ.

Разделяя данные на два потока — «полностью внутри» (быстрый путь) и «пересекает» (медленный путь), мы гарантируем, что дорогостоящая геометрическая обработка выполняется только там, где она реально нужна.

👉 @SQLPortal