Pull to refresh
  • by relevance
  • by date
  • by rating

О чем молчит EXPLAIN, и как его разговорить

Тензор corporate blog PostgreSQL *Database Administration *
Sandbox
Классический вопрос, с которым разработчик приходит к своему DBA или владелец бизнеса — к консультанту по PostgreSQL, почти всегда звучит одинаково: «Почему запросы выполняются на базе так долго?»

Традиционный набор причин:

  • неэффективный алгоритм
    когда вы решили сделать JOIN нескольких CTE по паре десятков тысяч записей
  • неактуальная статистика
    если фактическое распределение данных в таблице уже сильно отличается от собранной ANALYZE'ом в последний раз
  • «затык» по ресурсам
    и уже не хватает выделенных вычислительных мощностей CPU, постоянно прокачиваются гигабайты памяти или диск не успевает за всеми «хотелками» БД
  • блокировки от конкурирующих процессов

И если блокировки достаточно сложны в поимке и анализе, то для всего остального нам достаточно плана запроса, который можно получить с помощью оператора EXPLAIN (лучше, конечно, сразу EXPLAIN (ANALYZE, BUFFERS) ...) или модуля auto_explain.

Но, как сказано в той же документации,
«Понимание плана — это искусство, и чтобы овладеть им, нужен определённый опыт, …»
Но можно обойтись и без него, если воспользоваться подходящим инструментом!
Читать дальше →
Total votes 38: ↑38 and ↓0 +38
Views 15K
Comments 77

PostgreSQL Antipatterns: CTE x CTE

Тензор corporate blog PostgreSQL *SQL *Database Administration *
По роду деятельности приходится сталкиваться с ситуациями, когда разработчик пишет запрос и думает "база умная, сама со всем справится!"

В некоторых случаях (частично от незнания возможностей БД, частично от преждевременных оптимизаций) такой подход приводит к появлению «франкенштейнов».
Читать дальше →
Total votes 10: ↑9 and ↓1 +8
Views 6.7K
Comments 13

PostgreSQL Antipatterns: вредные JOIN и OR

Тензор corporate blog PostgreSQL *SQL *Database Administration *
Бойтесь операций, buffers приносящих…
На примере небольшого запроса рассмотрим некоторые универсальные подходы к оптимизации запросов на PostgreSQL. Пользоваться ими или нет — выбирать вам, но знать о них стоит.
Читать дальше →
Total votes 20: ↑20 and ↓0 +20
Views 13K
Comments 22

PostgreSQL Antipatterns: статистика всему голова

Тензор corporate blog PostgreSQL *SQL *Database Administration *
Для выбора наиболее эффективного плана выполнения запроса PostgreSQL пользуется накопленной статистикой о распределении значений данных в целевых таблицах.

Она обновляется с помощью явного запуска команд ANALYZE и VACUUM ANALYZE или в фоновом режиме процессом autovacuum/autoanalyze. Но если статистика не успеет актуализироваться — может произойти беда.

Как такую проблему обнаружить и исправить?
Читать дальше →
Total votes 10: ↑10 and ↓0 +10
Views 6.1K
Comments 1

PostgreSQL Antipatterns: сизифов JOIN массивов

Тензор corporate blog PostgreSQL *SQL *Database Administration *
Иногда возникает задача «склеить» внутри SQL-запроса из переданных в качестве параметров линейных массивов целостную выборку с теми же данными «по столбцам».
Читать дальше →
Total votes 14: ↑14 and ↓0 +14
Views 6.9K
Comments 6

PostgreSQL Antipatterns: передача наборов и выборок в SQL

Тензор corporate blog PostgreSQL *SQL *Database Administration *
Периодически у разработчика возникает необходимость передать в запрос набор параметров или даже целую выборку «на вход». Иногда попадаются очень странные решения этой задачи.

Пойдем «от обратного» и посмотрим, как делать не стоит, почему, и как можно сделать лучше.
Читать дальше →
Total votes 8: ↑8 and ↓0 +8
Views 8K
Comments 4

DBA: вычищаем клон-записи из таблицы без PK

Тензор corporate blog PostgreSQL *SQL *Database Administration *
Случаются ситуации, когда в таблицу без первичного ключа или какого-то другого уникального индекса по недосмотру попадают полные клоны уже существующих записей.



Например, пишутся в PostgreSQL COPY-потоком значения хронологической метрики, а потом внезапный сбой, и часть полностью идентичных данных приходит повторно.

Как избавить базу от ненужных клонов?
Читать дальше →
Total votes 13: ↑13 and ↓0 +13
Views 3.4K
Comments 11

PostgreSQL Antipatterns: обновляем большую таблицу под нагрузкой

Тензор corporate blog High performance *PostgreSQL *SQL *Database Administration *
Как стоит поступить (а как точно не надо), если в «многомиллионной» активно используемой таблице PostgreSQL нужно обновить большое количество записей — проинициализировать значение нового поля или скорректировать ошибки в существующих записях? А при этом сохранить свое время и не потерять деньги компании из-за простоя.


Читать дальше →
Total votes 14: ↑14 and ↓0 +14
Views 11K
Comments 7

SQL HowTo: собираем «цепочки» с помощью window functions

Тензор corporate blog PostgreSQL *SQL *Algorithms *
Иногда при анализе данных возникает задача выделения «цепочек» в выборке — то есть упорядоченных последовательностей записей, для каждой из которых выполняется некоторое условие.

Это может быть как условие от данных самой записи, так и сложное выражение относительно одной или нескольких предыдущих записей — например, длина интервала между близкими временными отсчетами.



Традиционные решения предусматривают разные варианты «self join», когда выборка соединяется с собой же, либо использование некоторых фактов «за пределами данных» — например, что записи должны иметь строго определенный шаг (N+1, «за каждый день», ...).

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

Но эту задачу нам помогут эффективно решить оконные функции в PostgreSQL.
Читать дальше →
Total votes 11: ↑11 and ↓0 +11
Views 3.9K
Comments 5

PostgreSQL Antipatterns: редкая запись долетит до середины JOIN

Тензор corporate blog PostgreSQL *SQL *Algorithms *Database Administration *
Если писать SQL-запросы без анализа алгоритма, который они должны реализовать, ни к чему хорошему с точки зрения производительности это обычно не приводит.

Такие запросы любят «кушать» процессорное время и активно почитывать данные практически на ровном месте. Причем, это вовсе не обязательно какие-то сложные запросы, наоборот — чем проще он написан, тем больше шансов получить проблемы. А уж если в дело вступает оператор JOIN…


Само по себе соединение таблиц не вредно и не полезно — это просто инструмент, но и пользоваться им надо уметь.
Читать дальше →
Total votes 18: ↑18 and ↓0 +18
Views 14K
Comments 25

PostgreSQL Antipatterns: ударим словарем по тяжелому JOIN

Тензор corporate blog PostgreSQL *SQL *Algorithms *Database Administration *
Продолжаем серию статей, посвященных исследованию малоизвестных способов улучшения производительности «вроде бы простых» запросов на PostgreSQL:


Не подумайте, что я так сильно не люблю JOIN… :)

Но зачастую без него запрос получается ощутимо производительнее, чем с ним. Поэтому сегодня попробуем вообще избавиться от ресурсоемкого JOIN — с помощью словаря.


Читать дальше →
Total votes 10: ↑9 and ↓1 +8
Views 12K
Comments 14

SQL HowTo: пишем while-цикл прямо в запросе, или «Элементарная трехходовка»

Тензор corporate blog PostgreSQL *SQL *Algorithms *Database Administration *
Периодически возникает задача поиска связанных данных по набору ключей, пока не наберем нужное суммарное количество записей.

Наиболее «жизненный» пример — вывести 20 самых старых задач, числящихся на списке сотрудников (например, в рамках одного подразделения). Для различных управленческих «дашбордов» с краткими выжимками по участкам работы похожая тема требуется достаточно часто.



В статье рассмотрим реализацию на PostgreSQL «наивного» варианта решения такой задачи, «поумнее» и совсем сложный алгоритм «цикла» на SQL с условием выхода от найденных данных, который может быть полезен как для общего развития, так и для применения в других похожих случаях.
Читать дальше →
Total votes 10: ↑9 and ↓1 +8
Views 15K
Comments 5

Массовая оптимизация запросов PostgreSQL. Кирилл Боровиков (Тензор)

Тензор corporate blog High performance *PostgreSQL *Node.JS *Database Administration *
В докладе представлены некоторые подходы, которые позволяют следить за производительностью SQL-запросов, когда их миллионы в сутки, а контролируемых серверов PostgreSQL — сотни.

Какие технические решения позволяют нам эффективно обрабатывать такой объем информации, и как это облегчает жизнь обычного разработчика.


Кому интересен разбор конкретных проблем и разные техники оптимизаций SQL-запросов и решения типовых DBA-задач в PostgreSQL — можно также ознакомиться с серией статей на эту тему.
Total votes 30: ↑29 and ↓1 +28
Views 13K
Comments 15

PostgreSQL Antipatterns: сказ об итеративной доработке поиска по названию, или «Оптимизация туда и обратно»

Тензор corporate blog High performance *PostgreSQL *SQL *Database Administration *
Тысячи менеджеров из офисов продаж по всей стране фиксируют в нашей CRM-системе ежедневно десятки тысяч контактов — фактов общения с потенциальными или уже работающими с нами клиентами. А для этого клиента надо сначала найти, и желательно очень быстро. И происходит это чаще всего по названию.

Поэтому неудивительно, что, разбирая в очередной раз «тяжелые» запросы на одной из самых нагруженных баз — нашего собственного корпоративного аккаунта СБИС, я обнаружил «в топе» запрос для «быстрого» поиска по названию для карточек организаций.

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

0: чего же хотел пользователь


[КДПВ отсюда]

Что вообще обычно подразумевает пользователь, когда говорит про «быстрый» поиск по названию? Почти никогда это не оказывается «честный» поиск по подстроке типа ... LIKE '%роза%' — ведь тогда в результат попадают не только 'Розалия' и 'Магазин Роза', но и роза' и даже 'Дом Деда Мороза'.

Пользователь же подразумевает на бытовом уровне, что вы ему обеспечите поиск по началу слова в названии и покажете более релевантным то, что начинается на введенное. И сделаете это практически мгновенно — при подстрочном вводе.
Читать дальше →
Total votes 17: ↑17 and ↓0 +17
Views 6.1K
Comments 15

Рецепты для хворающих SQL-запросов

Тензор corporate blog PostgreSQL *SQL *Database Administration *Data visualization
Несколько месяцев назад мы анонсировали explain.tensor.ru — публичный сервис для разбора и визуализации планов запросов к PostgreSQL.

За прошедшее время вы уже воспользовались им более 6000 раз, но одна из удобных функций могла остаться незамеченной — это структурные подсказки, которые выглядят примерно так:



Прислушивайтесь к ним, и ваши запросы «станут гладкими и шелковистыми». :)

А если серьезно, то многие ситуации, которые делают запрос медленным и «прожорливым» по ресурсам, типичны и могут быть распознаны по структуре и данным плана.

В этом случае каждому отдельному разработчику не придется искать вариант оптимизации самостоятельно, опираясь исключительно на свой опыт — мы можем ему подсказать, что тут происходит, в чем может быть причина, и как можно подойти к решению. Что мы и сделали.



Давайте чуть подробнее рассмотрим эти кейсы — как они определяются и к каким рекомендациям приводят.
Total votes 23: ↑23 and ↓0 +23
Views 21K
Comments 28

PostgreSQL Antipatterns: насколько глубока кроличья нора? пробежимся по иерархии

Тензор corporate blog High performance *PostgreSQL *SQL *Database Administration *
В сложных ERP-системах многие сущности имеют иерархическую природу, когда однородные объекты выстраиваются в дерево отношений «предок — потомок» — это и организационная структура предприятия (все эти филиалы, отделы и рабочие группы), и каталог товаров, и участки работ, и география точек продаж,…



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

Существует много способов хранения такого дерева в СУБД, но мы сегодня остановимся только на одном варианте:

CREATE TABLE hier(
  id
    integer
      PRIMARY KEY
, pid
    integer
      REFERENCES hier
, data
    json
);

CREATE INDEX ON hier(pid); -- не забываем, что FK не подразумевает автосоздание индекса, в отличие от PK

И пока вы всматриваетесь в глубину иерархии, она терпеливо ждет, насколько же [не]эффективными окажутся ваши «наивные» способы работы с такой структурой.


Давайте разберем типовые возникающие задачи, их реализацию на SQL и попробуем улучшить их производительность.
Читать дальше →
Total votes 21: ↑20 and ↓1 +19
Views 6.2K
Comments 20

Понимаем планы PostgreSQL-запросов еще удобнее

Тензор corporate blog PostgreSQL *SQL *Database Administration *Data visualization
Полгода назад мы представили explain.tensor.ru — публичный сервис для разбора и визуализации планов запросов к PostgreSQL.



За прошедшие месяцы мы сделали про него доклад на PGConf.Russia 2020, подготовили обобщающую статью по ускорению SQL-запросов на основе рекомендаций, которые он выдает… но самое главное — собирали ваши отзывы и смотрели за реальными use case.

И теперь готовы рассказать о новых возможностях, которыми вы можете пользоваться.
Читать дальше →
Total votes 25: ↑25 and ↓0 +25
Views 9.2K
Comments 10

SQL HowTo: 1000 и один способ агрегации

Тензор corporate blog High performance *PostgreSQL *Programming *SQL *
Наш СБИС, как и другие системы управления бизнесом, не обходится без формирования отчетов — каждый руководитель любит сводные цифры, особенно всякие суммы по разделам и красивые "Итого".

А чтобы эти итоги собрать, необходимо по исходным данным вычислить значение некоторой агрегатной функции: количество, сумма, среднее, минимум, максимум,… — и, как правило, не одной.


Сегодня мы рассмотрим некоторые способы, с помощью которых можно вычислить агрегаты в PostgreSQL или ускорить выполнение SQL-запроса.
Читать дальше →
Total votes 14: ↑13 and ↓1 +12
Views 6.6K
Comments 1

Вооруженным глазом: наглядно о проблемах PostgreSQL-запроса

Тензор corporate blog PostgreSQL *SQL *Database Administration *Data visualization
🔥 Technotext 2020
Продолжаем открывать для публичного доступа новый функционал нашего сервиса анализа планов выполнения запросов в PostgreSQL explain.tensor.ru. Сегодня мы научимся определять больные места навскидку в больших и сложных планах, лишь мельком взглянув на них вооруженным глазом…


В этом нам помогут различные варианты визуализации:


Читать дальше →
Total votes 32: ↑32 and ↓0 +32
Views 7K
Comments 1

Правильно [c]читаем параллельные планы PostgreSQL

Тензор corporate blog PostgreSQL *SQL *Database Administration *Data visualization
Исторически, модель работы сервера PostgreSQL выглядит как множество независимых процессов с частично разделяемой памятью. Каждый из них обслуживает только одно клиентское подключение и один запрос в любой момент времени — и никакой многопоточности.

Поэтому внутри каждого отдельного процесса нет никаких традиционных «странных» проблем с параллельным выполнением кода, блокировками, race condition,… А разработка самой СУБД приятна и проста.

Но эта же простота накладывает существенное ограничение. Раз внутри процесса всего один рабочий поток, то и использовать он может не более одного ядра CPU для выполнения запроса — а, значит, скорость работы сервера впрямую зависит от частоты и архитектуры отдельного ядра.

В наш век закончившейся «гонки мегагерцев» и победивших многоядерных и многопроцессорных систем такое поведение является непозволительной роскошью и расточительностью. Поэтому, начиная с версии PostgreSQL 9.6, при отработке запроса часть операций может выполняться несколькими процессами одновременно.


Со схемами работы некоторых параллельных узлов можно ознакомиться в статье «Parallelism in PostgreSQL» by Ibrar Ahmed, откуда взято и это изображение.
Правда, читать планы в этом случае становится… нетривиально.
Читать дальше →
Total votes 17: ↑17 and ↓0 +17
Views 2.8K
Comments 0
1