- PVSM.RU - https://www.pvsm.ru -
Сегодня мы расскажем о самых главных фичах PostgreSQL 11. Почему только о них — потому что некоторые возможности нужны далеко не всем, поэтому мы остановились на самых востребованных.
В PostgreSQL наконец-то появилась JIT-компиляция, то есть компиляция запросов в бинарный код. Для этого нужно скомпилировать PostgreSQL с поддержкой JIT-компиляции (Compile time 1 (--with-llvm))
. При этом на машине должен быть LLVM версии не ниже 3.9.
Что же умеет ускорять JIT?
Compile time 2
означает, что JIT не используется. В PostgreSQL есть момент планирования запроса, когда система решает, что стоит JIT’ить, а что не стоит. В этот момент он JIT’ится и дальше executor выполняет, как есть.
JIT сделан подключаемым. По умолчанию он работает с LLVM, но вы можете подключить любой другой JIT.
У вас есть все наборы, контролирующие JIT.
Если вы скомпилировали PostgreSQL без поддержки JIT, то самая первая настройка не работает. Реализованы опции для разработчиков, есть настройки для отдельных функций JIT.
Следующий тонкий момент связан с jit_above_cost. Сам JIT не бесплатный. Поэтому PostgreSQL по умолчанию занимается JIT-оптимизацией, если стоимость запроса превысила 100 тыс. условных попугаев, в которых меряется explain, analyze и так далее. Это значение выбрано наугад, так что обратите на него внимание.
Но не всегда после включения JIT сразу всё работает. Обычно все начинают экспериментировать с JIT с помощью запроса select * from table where id=600 и у них ничего не получается. Наверное, надо как-то усложнять запрос, и тогда все генерируют гигантскую базу и сочиняют запрос. В результате PostgreSQL упирается в возможности диска, ему не хватает ни ёмкости общих буферов, ни кэшей.
Вот совершенно абстрактный пример. Здесь 9 полей null с разной частотой, чтобы можно было заметить влияние tuple deforming.
select i as x1,
case when i % 2 = 0 then i else null end as x2,
case when i % 3 = 0 then i else null end as x3,
case when i % 4 = 0 then i else null end as x4,
case when i % 5 = 0 then i else null end as x5,
case when i % 6 = 0 then i else null end as x6,
case when i % 7 = 0 then i else null end as x7,
case when i % 8 = 0 then i else null end as x8,
case when i % 9 = 0 then i else null end as x9
into t
from generate_series(0, 10000000) i;
vacuum t;
analyze t;
Возможностей у PostgreSQL много, и чтобы увидеть преимущества JIT, отключим первые две строчки, чтобы не мешались, и обнуляем пороги.
set max_parallel_workers=0;
set max_parallel_workers_per_gather=0;
set jit_above_cost=0;
set jit_inline_above_cost=0;
set jit_optimize_above_cost=0;
Вот сам запрос:
set jit=off;
explain analyze
select count(*) from t where
sqrt(pow(x9, 2) + pow(x8,2)) < 10000;
set jit=on;
explain analyze
select count(*) from t where
sqrt(pow(x9, 2) + pow(x8,2)) < 10000;
И вот результат:
Planning Time: 0.71 ms
Execution Time: 1986.323 ms
VS
Planning Time: 0.060 ms
JIT:
Functions: 4
Generation Time: 0.911 ms
Inlining: true
Inlining Time: 23.876 ms
Optimization: true
Optimization Time: 41.399 ms
Emission Time: 21.856 ms
Execution Time: 949.112 ms
JIT помог ускорить запрос вдвое. Planning time приблизительно одно и то же, но это, скорее всего, следствие того, что PostgreSQL что-то закэшировал, поэтому не обращайте на это внимания.
Если просуммировать, то на JIT-компиляцию ушло порядка 80 мс. Почему JIT не бесплатный? Прежде чем выполнять запрос, нужно его скомпилировать, а это тоже занимает время. Причём на три порядка больше, чем планирование. Недешевое удовольствие, но зато окупается за счёт длительности исполнения.
Вот таким образом можно использовать JIT, хотя он приносит пользу далеко не всегда.
Если вы обращали внимание на секционирование в PostgreSQL, то, наверное, замечали, что оно там сделано для галочки. Ситуация несколько улучшилась в версии 10, когда появилось декларативное объявление партиций (секций). С другой стороны, внутри всё оставалось по-старому и работало примерно так же, как и в предыдущих версиях, то есть плохо.
Во многом эту проблему решал модуль pg_pathman, который позволял работать с секциями и достаточно оптимально отсекать их во время выполнения.
В версии 11 секционирование значительно улучшено:
Большой вопрос: сколько можно иметь секций? Честно говоря, с большим количеством секций (тысячи и десятки тысяч) фича работает плохо. У pg_pathman получается лучше.
Также сделали секции по умолчанию. Опять же, в pg_pathman можно сделать автоматическое создание секций, что удобнее. Здесь же в секции сваливается всё, что не удалось куда-нибудь запихнуть. Если в реальной системе сделать такое по умолчанию, то через какое-то время получится такая каша, которую потом замучаешься разгребать.
PostgreSQL 11 теперь умеет оптимизировать секционирование, если две таблицы соединяются по ключу секционирования, и схемы секционирования совпадают. Этим управляет специальный параметр, который по умолчанию выключен.
Можно вычислять агрегаты для каждой секции отдельно, а потом суммировать. И наконец, можно создать индекс на родительскую секционированную таблицу, и тогда будут созданы локальные индексы на все таблицы, которые к ней подключены.
В разделе «Что нового» упомянута замечательная вещь — возможность выкидывать секции при выполнении запроса. Давайте проверим, как это работает. Получилась вот такая таблица:
Делаем тип и таблицу из двух колонок с первичным ключом, с колонкой bigserial, вставляем данные. Создаем вторую таблицу, которая будет партиционирована и будет копией первой. Добавляем первичный ключ на секционированную таблицу.
Таблица будет состоять из записей двух типов: «женщины-няни» и «мужчины-водители». И будет одна женщина-водитель. Делаем две секции, разделяем по списку, добавляем первичный ключ и вставляем все данные из той таблицы, в которой всё это сгенерировано. Результат получился совершенно неинтересный:
Обратите внимание на запрос. Мы выбираем всё из несекционированной таблицы, соединяем с секционированной. Берем небольшой кусочек и выбираем только один тип, они идут через один. Указываем, что колонка осс должна иметь одно значение. Получается выборка сплошных водителей.
При выполнении мы специально отключаем распараллеливание, потому что PostgreSQL 11 по умолчанию очень активно распараллеливает более-менее сложные запросы. Если мы посмотрим на план выполнения (explain analyze), то видно, что система сложила даные в обе секции: и в няней, и в водителей, хотя нянь там не было. К буферу обращений не было. Время потрачено, условие использовано, хотя PostgreSQL мог всё это вычислить. То есть, объявление partition elimination сходу не работает. Возможно, в следующих сборках это поправят. При этом модуль pg_pathman в этом случае работает без проблем.
Эта функциональность была реализована PostgresPro три года назад, и всё это время ее пытались пропихнуть в ванильный PostgreSQL. Покрывающие индексы означают, что вы можете к уникальному индексу, прямо в индексные tuple, добавить дополнительные колонки.
Зачем? Все любят index-only scan за быструю работу. Для этого строят условно «покрывающие» индексы:
CREATE UNIQUE INDEX idx ON TBL
(a, b) INCLUDE (c, d)
VS
CREATE UNIQUE INDEX idx1 ON tbl
(a, b)
CREATE UNIQUE INDEX idx1 ON tbl
(a, b, c, d)
... PRIMARY KEY (a, b) INCLUDE (c, d)
Но при этом нужно сохранить уникальность. Поэтому строится два индекса, узкий и широкий.
Недостаток в том, что когда вы применяете к таблице vacuum, insert или update, то в обязательном порядке нужно обновить оба индекса. Так что вставка в индекс — операция медленная. А покрывающий индекс позволит обойтись только одним индексом.
Правда, у него есть некие ограничения. Точнее, преимущества, которые, может быть, будут не сразу понятны. Колонки c и d в первом create index не обязаны быть скалярными типами, для которых определен b-tree индекс. То есть, они необязательно имеют сравнение «больше-меньше». Это могут быть точки или полигоны. Единственное, кортеж должен быть меньше 2,7 Кб, потому что toasting в индексе нет, но туда можно уместить то, что невозможно сравнить.
Однако, внутри индекса с этими гарантировано покрытыми колонками не производится никаких вычислений при поиске. Это должен делать фильтр, который стоит над индексом. С одной стороны, почему бы ему не вычислять это внутри индекса, с другой стороны, это лишний вызов функции. Но всё не так страшно, как кажется.
Ну и кроме того, вы можете эти покрытые колонки добавить в первичный ключ.
Этот индекс мало кто использует, потому что он достаточно специфичен. Тем не менее, появилась возможность хранить в нём не совсем то, что вставили. Имеется в виду lossy — индекс, компрессия. В качестве примера возьмём полигоны. Вместо них в индекс кладётся bounding box, то есть минимальный прямоугольник, который содержит нужный полигон. В данном случае мы представляем прямоугольник как точку в четырехмерном пространстве, и дальше работаем классическим quad3, в четырехмерном пространстве.
Также для SP-GiST ввели операцию «префиксный поиск». Она возвращает true, если одна строка является префиксом другой. Ввели это не просто так, а ради вот такого запроса с поддержкой SP-GiST.
SELECT * FROM table WHERE c ^@ „abc“
В b-tree есть лимит 2,7 Кб на всю строчку, а у SP-GiST такого ограничения нет. Правда, есть ограничение у PostgreSQL: одно значение не может превышать 1 Гб.
Если со времени последнего vacuum не было delete или update, то у вас нет мертвых записей, их не нужно удалять. В этом случае в индекс можно не ходить. Там есть дополнительные тонкости, b-tree удаляет свои страницы не сразу, а в два прохода. Поэтому, если вы удалили много данных в таблице, то vacuum делать нужно. Но если вы хотите освободить место в индексах, то сделайте vacuum дважды.
Кто-то удивится, что это за таблица, в которой не было delete или update? На самом деле многие имеют с этим дело, только не задумываются. Это таблицы append only, куда складываются, например, логи. В них удаление происходит крайне редко. И это сильно экономит продолжительность vacuum/autovacuum, снижает нагрузку на диск, использование кэшей и так далее.
Теперь с помощью тонкой настройки можно поменять это поведение для всей базы данных или отдельной таблицы, чтобы вот такие маленькие выходы за рамки не требовали применения toast. Но вы должны понимать, что делаете, без этого ничего не получится.
Пример параллельного создания индексов:
alter table usr reset (parallel_workers)
create index on usr(lower((so).occ)) — 2 сек
alter table usr set (parallel_workers=2)
create index on usr(upper((so).occ)) — 1.8 сек
У таблицы есть параметр parallel worker. С его помощью можно задать нужное количество воркеров. На тестируемой машине с 16 Гб памяти и 4 ядрами (таблица влезает в кэш ОС) создание в один поток занимает 2 сек., с двумя воркерами — 1,8 сек. Будь в налличии много дисков, и таблица покрупнее, то разница была бы ощутимее. То есть, это нововведение полезно для больших серьезных таблиц и серверов.
А вот пример распараллеливания запроса:
explain analyze
select u1.* from usr u, usr1 u1 where
u.id=u1.id+0
Это две таблицы из тех, которые упомянуты в главе про секционирование. Напоминаем, user — это обычная таблица, секционированная на две части. Обратите внимание на условия соединения. Мы специально добавляем ноль, чтобы соединение произошло не по индексу, а по хэшу.
Мы специально выключаем параллельное выполнение, потому что PostgreSQL 11 в этом плане весьма агрессивен и имеет большую склонность к распараллеливанию.
Этот запрос выполнялся 1425 мс, почти 1,5 сек. Запрос внутри обрабатывался 1,4 сек. Выбрано 2 млн строк. Для сравнения, в версии 9.6 было эмпирическое правило: 1 млн не очень больших строк — примерно 1 сек., а тут 2 млн за 1 сек. Это связано с тем, что в версии 10 ускорили обращение к tuple. А в версии 11 обращение ещё больше ускорено. В этом примере получилось соединение по хэшированию: сначала собрали хэш из таблицы user, получился один batch, оттуда с помощью x-scan выбраны данные и с помощью append собраны две таблицы.
Следующий вариант параллельного выполнения:
Посмотрим время выполнения. В верхнем узле система начала отдавать данные через 211 мс, закончила через 702 мс. В предыдущем варианте, начала отдавать через 510 мс и закончила через 1473. То есть и первая, и последняя строки отданы в 2 раза быстрее.
Теперь соединим с помощью parallel hash join. Было поднято два воркера на верхнем уровне. Ограничение по числу процессоров — 4. Один воркер строил паралел хэш, второй выбирал из другой таблицы.
В таблице parallel index scan всё весьма агрессивно распараллелилось. Данные поместились в один batch в хэше. Что это означает? У нас может не хватить памяти для hash join, и тогда система может выполнять задачу в несколько проходов. Таблица user прочитана в два потока. Также в два потока мы читали другую таблицу, строили parallel hash, который уже работает.
Время выполнения стало меньше 1 сек. Есть мнение, что параллельное выполнение надо применять для OLAP-задач, а OLTP выполнять последовательно. Но это если OLTP совсем тупой, вроде получения значения по первичному ключу.
Радикальных изменений здесь почти не произошло.
Они теперь соответствуют стандарту SQL:2011, и появилась возможность задавать группы ограничений.
Как вы знаете, есть несколько способов написания полнотекстовых запросов, в том числе руками. Очевидно, что у того, кто это пытался сделать, всё получалось, но потом он стрелялся, либо его пользователи убивали.
Теперь появился websearch, который сделан по образу поисковиков. Он точно никогда не выдаст ошибку, что у вас неправильный запрос. Вместо этого он может интерпретировать не совсем так, как вы ожидали.
# select websearch_to_tsquery('dog or cat');
----------------------
'dor' | 'cat'
# select websearch_to_tsquery('dog -cat');
----------------------
'dor' & !'cat'
# select websearch_to_tsquery('or cat');
----------------------
'cat'
Первый пример — dog or cat — собака или кошка. Websearch понимает суть и формирует такой запрос. Второй запрос видит символ | и понимает, что мы хотим найти только про собаку. И третий запрос “or cat”. Тут трудно сказать, что хотел пользователь. В данном случае websearch трактует “or” как слово. Другое дело, что оно является стоп-словом, поэтому выкинуто из запроса.
Websearch — это средство построения полнотекстового поиска. В следующих версиях планируется реализовать две операции: поиск по фразе и по расстоянию между словами, с указанием диапазона. Сейчас ищет по точному вхождению, понимает двойные кавычки.
В 10-й версии появилась индексация всех текстов, а в 11-й добавилась индексация и численных полей. Реализована функция для json и json(b), которая порождает tsvector. Второй аргумент (тоже json(b)) обязателен из-за требований полиморфизма. Сейчас это может быть либо скаляр, либо массив названий того, что хотим проиндексировать, поля bull, numeric, string, ключи. Всё это можно указывать вместе.
# select jsonb_to_tsvector
('{"a":"texts", "b":12}', '"string"');
-------------------
'text':1
# select jsonb_to_tsvector
('{"a":"texts", "b":12}', '["string", "numeric"]');
-------------------
'12':3 'text':1
Мы намеренно используем здесь json(b), чтобы в будущем можно было безболезненно расширить функциональность. Хотя аргументов в виде исполняемого кода, который извлекает то, что нужно проиндексировать, не будет никогда.
В некоторых языках появились процедуры.
CREATE PROCEDURE transaction_test1()
LANGUAGE plpgsql
AS $$
BEGIN
FOR i IN 0..9 LOOP
INSERT INTO test1 (a) VALUES (i);
IF i % 2 = 0 THEN
COMMIT;
ELSE
ROLLBACK;
END IF;
END LOOP;
END
$$;
CALL transaction_test1();
Вызывать их можно с помощью call, но основное преимущество процедур сегодня в том, что прямо внутри них можно начинать и заканчивать транзакции. Побочным следствием будет невозможность вызова процедуры внутри транзакции. Процедура должна работать вне транзакции. Она не может участвовать внутри select, insert и так далее.
Это не автономная транзакция, это просто вызов функции, которая внутри PostgreSQL может создать несколько транзакций. Она доступна для Perl, Python, TСL и PL/pgSQL. В языках типа Perl нужно явно использовать sp begin, без этого вы просто не сможете обратиться к базе.
PL/pgSQL немного отличается от остальных: он начинает транзакцию неявно, а остальные языки явно.
Была цель реализовать на pgbench функциональность ICSB bench — инструмента, моделирующего сетевую нагрузку, но не хватило всевозможных функций и операторов. В результате реализовано if, который позволяет выбирать в тесте, какой запрос вы хотите выполнить. Появился case, чтобы можно было прямо в запросе что-то поменять. Появилась трассировка --init-steps
, чтобы можно было узнать, что там происходит.
Теперь можно для повышения стабильности ваших бенчмарков указать random-seed. Появилось несколько zipfian-распределений цифр. Действия пользователей в соцсетях/интернете — это неравномерные распределения, не Гауссиана и не распределение Пуассона. Они по какой-то причине подчиняются распределению цифр, поэтому оно подходит для написания тестов, моделирующих какую-то нагрузку, генерируемую людьми.
Наконец, появился хэшинг, чтобы повысить случайность каких-то операций.
Для тех, кто застревает в PSQL, теперь есть два новых способа выйти из него. Можно просто напечатать exit или quit.
Автор: tspavel
Источник [18]
Сайт-источник PVSM.RU: https://www.pvsm.ru
Путь до страницы источника: https://www.pvsm.ru/postgresql/282891
Ссылки в тексте:
[1] JIT-компиляция: #1
[2] Секционирование: #2
[3] Индексы: #3
[4] Покрывающие индексы: #4
[5] SP GiST: #5
[6] Производительность: #6
[7] WAL: #7
[8] Бэкап и репликация: #8
[9] Для DBA: #9
[10] Параллельное выполнение: #10
[11] Оптимизаторы: #11
[12] Window-функции: #12
[13] Изменения в полнотекстовом поиске: #13
[14] Json(b) и полнотекст: #14
[15] PL/* процедуры: #15
[16] pgbench: #16
[17] Улучшения PSQL: #17
[18] Источник: https://habr.com/post/414031/?utm_campaign=414031
Нажмите здесь для печати.