В преддверии старта курса PostgreSQL подготовили небольшой полезный материал.



Большинство языков программирования предназначены для профессиональных разработчиков, знающих алгоритмы и структуру данных. Язык SQL немного отличается.

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

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

Будьте внимательны во время деления целых чисел

В PostgreSQL деление целого числа на целое число в результате дает целое число. Не делайте так:

db=# (
  SELECT tax / price AS tax_ratio
  FROM sale
);
 tax_ratio
----------
    0

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

 db=# (
  SELECT tax / price::float AS tax_ratio
  FROM sale
);
 tax_ratio
----------
 0.17

Неспособность распознать эту ошибку может привести к крайне неточным результатам.

Защита от ошибок деления на ноль

Деление на ноль — известная ошибка:

db=# SELECT 1 / 0
ERROR: division by zero

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

db=# SELECT 1 / NULLIF(0, 0);
 ?column?
----------
   -

Функция NULLIF возвращает неопределенное значение null, если первый аргумент равен второму. В этом случае, если знаменатель равен нулю.

При делении любого числа на NULL результатом будет NULL. Чтобы получить некоторое значение, вы можете свернуть все выражение с COALESCE и предоставить дефолтное значение:

db=# SELECT COALESCE(1 / NULLIF(0, 0), 1);
 ?column?
----------
    1

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

Знайте разницу между UNION и UNION ALL

Классический вопрос на собеседовании начального уровня для разработчиков и администраторов баз данных: «В чем разница между функциями UNION и UNION ALL?».

UNION ALL объединяет результаты одного или нескольких запросов. UNION делает то же самое, и к тому же исключает повторяющиеся строки. Не делайте так:

 SELECT created_by_id FROM sale
  UNION
  SELECT created_by_id FROM past_sale
);
QUERY PLAN
-----------
Unique  (cost=2654611.00..2723233.86 rows=13724572 width=4)
  ->  Sort  (cost=2654611.00..2688922.43 rows=13724572 width=4)
        Sort Key: sale.created_by_id
        ->  Append  (cost=0.00..652261.30 rows=13724572 width=4)
              ->  Seq Scan on sale  (cost=0.00..442374.57 rows=13570157 width=4)
              ->  Seq Scan on past_sale  (cost=0.00..4018.15 rows=154415 width=4)

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

Если вам не нужно удалять повторяющиеся строки, лучше использовать функцию UNION ALL:

 db=# (
  SELECT created_by_id FROM sale
  UNION ALL
  SELECT created_by_id FROM past_sale
);
QUERY PLAN
-----------
 Append  (cost=0.00..515015.58 rows=13724572 width=4)
   ->  Seq Scan on sale  (cost=0.00..442374.57 rows=13570157 width=4)
   ->  Seq Scan on past_sale  (cost=0.00..4018.15 rows=154415 width=4)

Выполняется намного проще. Результаты получены, сортировка не требуется.

Будьте внимательны при подсчете столбцов, допускающих неопределенное значение

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

db=# \pset null NULL
Null display is "NULL".
db=# WITH tb AS (
  SELECT 1 AS id
  UNION ALL
  SELECT null AS id
)
SELECT *
FROM tb;
  id
------
    1
 NULL

Столбец id содержит значение null. Посчитаем столбец id:

 db=# WITH tb AS (
  SELECT 1 AS id
  UNION ALL
  SELECT null AS id
)
SELECT COUNT(id)
FROM tb;
 count
-------
     1

В таблице две строки, но функция COUNT возвращает 1. Это произошло, потому что функция COUNT игнорирует неопределенные значения.

Чтобы посчитать строки, используйте функцию COUNT(*):

 db=# WITH tb AS (
  SELECT 1 AS id
  UNION ALL
  SELECT null AS id
)
SELECT COUNT(*)
FROM tb;
 count
-------
  2

Эта особенность также может быть полезна. Например, если поле с именем modified содержит неопределенное значение в том случае, если строка не была изменена, вы можете рассчитать процент измененных строк следующим образом:

db=# (
  SELECT COUNT(modified) / COUNT(*)::float AS modified_pct
  FROM sale
);
 modified_pct
---------------
  0.98

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

db=# WITH tb AS (
  SELECT null AS id
  UNION ALL
  SELECT null AS id
)
SELECT SUM(id::int)
FROM tb;
 sum
-------
 NULL

Это все документированные операции, так что будьте внимательны!

Обратите внимание на часовые пояса

Часовые пояса всегда являются источником путаницы и ошибок. PostgreSQL отлично справляется с часовыми поясами, но вам все равно придется обратить внимание на некоторые вещи.

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

SELECT created_at::date, COUNT(*)
FROM sale
GROUP BY 1

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

 now
------------
 2019-11-08
db=# SET TIME ZONE 'australia/perth';
SET
db=# SELECT now()::date;
now
------------
2019-11-09

Если вы не уверены, с каким часовым поясом работаете, вы можете делать это неправильно.

При обращении к временной метке сначала приведите ее к нужному часовому поясу:

SELECT (timestamp at time zone 'asia/tel_aviv')::date, COUNT(*)
FROM sale
GROUP BY 1;

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

db=# SHOW timezone;
TimeZone
----------
Israel
db=# SELECT now();
now
-------------------------------
2019-11-09 11:41:45.233529+02

А чтобы установить часовой пояс в PSQL:

 db=# SET timezone TO 'UTC';
SET
db=# SELECT now();
now
-------------------------------
2019-11-09 09:41:55.904474+00

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

Избегайте преобразований в индексированных полях

Использование функций в индексированном поле может помешать базе данных использовать индекс в этом поле:

SELECT * FROM sale
  WHERE created at time ZONE 'asia/tel_aviv' > '2019-10-01'
);
QUERY PLAN
----------
Seq Scan on sale (cost=0.00..510225.35 rows=4523386 width=276)
Filter: timezone('asia/tel_aviv', created) > '2019-10-01 00:00:00'::timestamp without time zone

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

Один из способов использования индекса в этом случае — применить преобразование в правой части:

SELECT * FROM sale WHERE created > '2019-10-01' AT TIME ZONE 'asia/tel_aviv' );
QUERY PLAN
----------
Index Scan using sale_created_ix on sale  (cost=0.43..4.51 rows=1 width=276)
Index Cond: (created > '2019-10-01 00:00:00'::timestamp with time zone)

Другим примером использования дат является фильтрация определенного периода:

 db=# (
 SELECT * FROM sale WHERE created + INTERVAL '1 day' > '2019-10-01'
);
QUERY PLAN
----------
 Seq Scan on sale  (cost=0.00..510225.35 rows=4523386 width=276)
   Filter: ((created + '1 day'::interval) > '2019-10-01 00:00:00+03'::timestamp with time zone)

Как и до этого, интервальная функция в поле created препятствовала базе данных использовать индекс. Чтобы база данных использовала индекс, примените преобразование в правой части выражения вместо всего поля:

 SELECT *
  FROM sale
  WHERE created > '2019-10-01'::date - INTERVAL '1 day'
);
QUERY PLAN
----------
 Index Scan using sale_created_ix on sale  (cost=0.43..4.51 rows=1 width=276)
   Index Cond: (created > '2019-10-01 00:00:00'::timestamp without time zone)

Заключение

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