[PostgreSQL, SQL] Перечислимый тип и PostgreSQL
Автор
Сообщение
news_bot ®
Стаж: 6 лет 9 месяцев
Сообщений: 27286
Пролог
Под перечислимым типом обычно понимают тип данных, который может принимать ограниченное и, как правило, небольшое число значений. Его выделяет то, что эти значения часто хардкодятся программистами в исходный код. И, как следствие, пользователи и операторы приложения не могут менять множество значений перечислимого типа. Их меняют только разработчики, зачастую с соответствующими исправлениями в коде и бизнес-логике приложения. Примерами перечислимых типов могут быть: времена года, месяцы, направление типа въезда/выезд или in/out, какие-нибудь типы или категории чего-нибудь, и так далее. В PostgreSQL подобную функциональность могут и реализуют различными способами. Этому посвящена статья.
Лирическое отступление (или почему не boolean)
В качестве примера перечислимого типа для всего последующего изложения я выбрал пол человека. Часто для хранения пола выбирают тип данных boolean. Что неправильно. Во-первых, придется объяснять феминисткам, почему мужской пол «истинный», а женский — «ложный». Во-вторых, boolean создавался совсем для другого, и все типы операций и функций, определенные для него, в этой задаче будут бессмысленными. Ну, разве что только XOR сохраняет здравый смысл. И в-третьих, помимо мужского и женского пола есть еще пол непонятный. Речь здесь не только про извращенцев вроде Кончиты Вурст, есть люди с генетической аномалией мозаицизм по половым хромосомам, когда даже на генетическом уровне нельзя сказать, какой пол у человека.
Что гораздо важнее, такой тип пола, как "other", стандартизирован ИКАО для официальных документов, и встречается в официальных документах, предъявляемых на пограничных пунктах, к сожалению, гораздо чаще, чем того требует природа человека. А когда люди с такими документами пересекают российскую границу, наши православные пограничники тоже вынуждены указывать такой пол уже во внутрироссийских документах. И для этой цели нельзя использовать значение null в типе boolean. Значение null означает «значение неизвестно», например, не была заполнена графа "sex" в документе, и в действительности пол может оказаться неизвестно каким. А вот пол "other" — это совершенно точно известный факт, что человек чувствует и записывает в документах, что он «особенный». Поэтому для sex надо использовать не boolean, а перечислимый тип.
Варианты
Enum — встроенный в PostgreSQL официальный тип
В PostgreSQL есть специальный тип данных, созданный для такого случая, называется enum. Вот пример его определения:
CREATE TYPE sex AS ENUM ('мужчина', 'женщина', 'иное');
Пример использования:
select id from table where sex='женщина';
То, что везде в примерах ищется женщина, это не сексизм, а олицетворение поговорки: "Cherchez la femme".
Текстовые обозначения не могут быть длиннее 63 байт (если используем русский язык и UTF-8, то делите на два). В самой таблице значения будут занимать 4 байта. Потому что, по сути, этот тип данных — синтаксический сахар. На самом деле этот тип реализуется с помощью внешней таблицы, но планировщик выполняет некоторые оптимизации. Текстовые значения хранятся в таблице pg_enum, а ключом являются четырёхбайтные OID. Но это лучше, чем простое использование внешней таблицы. В запросах можно применять текстовые обозначения напрямую. И если в случае ошибки будет указано несуществующее значение, то будет поднят syntax error, в то время как при обычном использовании внешней таблицы никакой ошибки не было бы, запрос попросту вернул пустой результат.
Также этот тип безопасен в том смысле, что его нельзя сравнивать не только с другими типами, но даже с разными типами enum. В качестве бонуса, этот тип поддерживает упорядочивание его элементов (определены операции сравнения и сортировки), и этим порядком можно управлять (например, менять с помощью ALTER TYPE). Недостатки: использовать 4 байта там, где можно было бы обойтись одним, кажется расточительством. И когда я написал Тому Лэйну об этом недостатке существующего решения, то получил обычный в мире Open Source ответ: «Раз ты такой умный, реализуй сам как считаешь лучше».
Char — внутренний перечислимый тип PostgreSQL
Но не смотря на то, что в PostgreSQL есть специальный перечислимый тип для пользователей, во внутренних таблицах используется тип "char" в качестве перечислимого типа. Кавычки обязательны, потому что без них он превратится в широко известный тип char(много букв). В тип "char" помещается ровно 1 байт в символьном виде, т.е. размер в 4 раза меньше, чем официальный enum. При кодировке UTF-8 в него влезут английские буквы, цифры и символы, а вот русские буквы — нет. Тип можно использовать, прямо указывая обозначения в виде букв, подобрав их по какому-нибудь мнемоническому правилу или стандарту. В нашем случае, в соответствии со стандартом ИКАО это будет m, f, x. Но это пока не так интересно: буквы, конечно, удобно хардкодить, но хочется иметь возможность работать и с текстовыми обозначениями. Для этого можно написать простые функции. Также можно усилить проверку типов, использовав domain с указанием допустимых значений.
CREATE DOMAIN sex_char AS "char" CHECK (VALUE in ('m','f','x'));
CREATE FUNCTION sex(txt varchar, OUT ch sex_char) LANGUAGE plpgsql IMMUTABLE STRICT PARALLEL SAFE AS
$sex$
BEGIN
ch:= case txt
when 'мужчина' then 'm'::sex_char
when 'женщина' then 'f'::sex_char
when 'иное' then 'x'::sex_char
else null
end;
if ch is null then
raise invalid_parameter_value;
end if;
END
$sex$;
CREATE FUNCTION sex(ch sex_char, OUT txt varchar) LANGUAGE plpgsql IMMUTABLE STRICT PARALLEL SAFE AS
$sex$
BEGIN
txt:= case ch
when 'm'::sex_char then 'мужчина'
when 'f'::sex_char then 'женщина'
when 'x'::sex_char then 'иное'
else null
end;
if txt is null then
raise invalid_parameter_value;
end if;
END
$sex$;
Две этих функции, по сути, составляют одну полиморфную функцию. Примеры использования:
=> select sex(ch=>'f');
sex
---------
женщина
(1 row)
=> select sex(txt=>'женщина');
sex
-----
f
(1 row)
Указывать имя аргумента (или типа данных) нужно потому, что парсер, видя текстовый литерал, не может определить тип аргумента и выбрать нужную функцию. В тех случаях, когда парсеру очевиден тип аргумента, его имя можно не указывать. Например, если применить функцию саму к себе, получится тривиальная:
=> select sex(sex(txt=>'женщина'));
sex
---------
женщина
(1 row)
Примеры использования:
select id from table where sex='f';
select id from table where sex=sex(txt=>'женщина');
Из достоинств этого метода: занимает 1 байт, нет внешних таблиц и ожидается хорошее быстродействие.
Классическая внешняя таблица
Классика нормализации.
create table sex_t (
sex_t_id smallint primary key,
sex varchar not null unique
);
И эта таблица подключается куда надо как внешняя. Пример использования:
select id from table join sex_t using (sex_t_id) where sex='женщина';
Очевидно, что всё это похоже на внутреннее устройство у официального enum. Из недостатков всё то, что в enum было перечислено как достоинства: приходится указывать в запросах внешнюю таблицу, что сильно загромождает запрос; нет синтаксических ошибок в случае, если кто-то неправильно запишет текстовое значение, и т.д. Достоинство одно: занимает 2 байта вместо 4 (т.е. в два раза меньше, чем официальный enum).
Экзотика
Можно еще упомянуть способы, к которым я не имею ни малейшего отношения. Но они встречаются. Видел пример, который выглядит как «классическая внешняя таблица», но для ключа вместо smallint использовался serial. Причем в связанном с ним sequence шаг умышленно выставлялся в 0 (чтобы вызвать ошибки при его использовании), и это не баг, а идеологическая фича (как мне объяснил разработчик): поскольку значения ключа захардкожены, при добавлении новых значений значения ключа должны были явно указываться программистом. И значений там было не больше 10.
Другой способ, который любят поклонники денормализации, заключается в создании текстового поля с указанием в нём текстовых значений. Вдобавок можно сделать вспомогательную таблицу со списком допустимых значений, чтобы использовать, например, при создании комбобоксов.
Когда структуру базы данных создают «веб-разработчики», перечислимый тип могут сохранять в виде текстовых значений, но не в текстовом поле, а внутри jsonb. Как правило, конечно, не в специально для этого созданном jsonb, а внутри одного большого jsonb, куда заложены все атрибуты данной таблицы.
Всё это вызывает у меня скепсис, но такие варианты интересно рассмотреть при тестировании не потому, что они хороши, а потому, что интересно узнать, насколько они плохи.
Описание эксперимента
Идея
Предположим, есть девелоперская контора, в которой трудится 75 % мужчин, 24 % женщин и еще 1 % неопределившихся существ. Отделу кадров на 23 февраля надо получить количество мужчин, чтобы закупить для них подарки, потом 8 марта получить количество женщин. А после кадровики задумываются, что меньшинство дискриминировать и оставлять без подарков нехорошо. И нужно количество иных, чтобы 1 апреля подарить подарки и им. Создам разные варианты таблиц, имитирующих список сотрудников с указанием пола, и замерю время выполнения всех трех запросов.
Поскольку работу с винчестерами мерить не интересно (слишком большой элемент случайности, связанный с движением головок), то для начала «прогрею» таблицы, чтобы работать только с кэшем в ОЗУ. Чтобы уменьшить влияние на результат каких-нибудь сторонних процессов, которые могут возникать в операционке и вне её, измерения буду проводить сериями. И чтобы измерять эффективность типов данных, а не то, как планировщик PostgreSQL иногда ошибается, принимая решения по распараллеливанию запросов, распараллеливание будет отключено.
В каждой таблице 10 000 000 записей, содержимое всех таблиц одинаковое (по составу). И поскольку запросы должны символизировать фильтрацию по полю перечисления и выдачу полезных данных из других полей, я решил отключить index only scan. Сделаю я это, изменив в запросах count(*) на count(id), т.е. явно укажу, что нужны данные, не входящие в индекс.
Описание стенда
Стенд сделал из того, что было: ноут MSI, операционка сообщает о 8 ядрах процессора, 16 Гб ОЗУ (hugepages 2 Мб на 14 Гб), 0 swap. Но поскольку тут интересно лишь относительное сравнение результатов измерений друг с другом, а не абсолютные значения, подробно расписывать железо не буду. CentOS 8, PostgreSQL 13 с shared_buffers (кэшем PostgreSQL) на 14 Гб.
Было сделано 100 серий экспериментов, в каждой серии по 100 замеров каждого варианта, итого 10 000 замеров каждого варианта. Чтобы каждый мог повторить эксперимент, привожу все скрипты.
postgresql.conf
Этот файл инклюдится в стандартный postgresql.conf.
# Минимальный уровень WAL чтобы уменьшить время на создание таблиц
wal_level = minimal
max_wal_senders = 0
# Поскольку работаем с закэшированными таблицами, издержек на "случайный" доступ нет.
random_page_cost = 1
# отключаем распараллеливание
max_parallel_workers_per_gather=0
# Кэш PostgreSQL
shared_buffers = 14GB
prewarm.sql
Прогреваю БД с помощью pg_prewarm.
select pg_prewarm('sex1');
select pg_prewarm('sex1_btree');
select pg_prewarm('sex2');
select pg_prewarm('sex2_btree');
select pg_prewarm('sex3');
select pg_prewarm('sex3_btree');
select pg_prewarm('sex4');
select pg_prewarm('sex4_btree');
select pg_prewarm('sex5');
select pg_prewarm('sex5_btree');
select pg_prewarm('sex5h');
select pg_prewarm('sex5h_hash');
select pg_prewarm('sex6');
select pg_prewarm('sex6_gin');
select pg_prewarm('sex6h');
select pg_prewarm('sex6h_gin_hash');
test.sql
Такими запросами проводится тестирование. И эти же запросы используются для дополнительного прогрева (pg_prewarm недостаточно). Напомню, что я использую count(id), чтобы отключить index only scan.
Код
SPL
select count(id) from sex1 where sex='мужчина';
select count(id) from sex1 where sex='женщина';
select count(id) from sex1 where sex='иное';
select count(id) from sex2 where sex_char=sex(txt=>'мужчина');
select count(id) from sex2 where sex_char=sex(txt=>'женщина');
select count(id) from sex2 where sex_char=sex(txt=>'иное');
select count(id) from sex3 join sex_t using (sex_t_id) where sex='мужчина';
select count(id) from sex3 join sex_t using (sex_t_id) where sex='женщина';
select count(id) from sex3 join sex_t using (sex_t_id) where sex='иное';
select count(id) from sex3 where sex_t_id=(select t.sex_t_id from sex_t t where sex='мужчина');
select count(id) from sex3 where sex_t_id=(select t.sex_t_id from sex_t t where sex='женщина');
select count(id) from sex3 where sex_t_id=(select t.sex_t_id from sex_t t where sex='иное');
select count(id) from sex4 join sex_t4 using (sex_t4_id) where sex='мужчина';
select count(id) from sex4 join sex_t4 using (sex_t4_id) where sex='женщина';
select count(id) from sex4 join sex_t4 using (sex_t4_id) where sex='иное';
select count(id) from sex4 where sex_t4_id=(select t.sex_t4_id from sex_t4 t where sex='мужчина');
select count(id) from sex4 where sex_t4_id=(select t.sex_t4_id from sex_t4 t where sex='женщина');
select count(id) from sex4 where sex_t4_id=(select t.sex_t4_id from sex_t4 t where sex='иное');
select count(id) from sex5 where sex='мужчина';
select count(id) from sex5 where sex='женщина';
select count(id) from sex5 where sex='иное';
select count(id) from sex5h where sex='мужчина';
select count(id) from sex5h where sex='женщина';
select count(id) from sex5h where sex='иное';
select count(id) from sex6 where jdoc@>'{"sex":"мужчина"}';
select count(id) from sex6 where jdoc@>'{"sex":"женщина"}';
select count(id) from sex6 where jdoc@>'{"sex":"иное"}';
select count(id) from sex6h where jdoc@>'{"sex":"мужчина"}';
select count(id) from sex6h where jdoc@>'{"sex":"женщина"}';
select count(id) from sex6h where jdoc@>'{"sex":"иное"}';
init.sql
Скрипт первоначального создания БД для экспериментов:
Код
SPL
-- заполняем таблицы, во всех таблицах одинаковые данные
\set table_size 10000000
-- удобный view для посмотра размера таблиц после их заполнения
create or replace view disk as SELECT n.nspname AS schema,
c.relname,
pg_size_pretty(pg_relation_size(c.oid::regclass)) AS size,
pg_relation_size(c.oid::regclass)/1024 AS size_KiB
FROM pg_class c
LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
ORDER BY (pg_relation_size(c.oid::regclass)) DESC
LIMIT 20;
begin;
-- sex1 официальный enum
CREATE TYPE sex_enum AS ENUM ('мужчина', 'женщина', 'иное');
create table sex1 (id float, sex sex_enum not null);
-- sex2 "char"
CREATE DOMAIN sex_char AS "char" CHECK (VALUE in ('m','f','x'));
CREATE FUNCTION sex(txt varchar, OUT ch sex_char) LANGUAGE plpgsql IMMUTABLE STRICT PARALLEL SAFE AS
$sex$
BEGIN
ch:= case txt
when 'мужчина' then 'm'::sex_char
when 'женщина' then 'f'::sex_char
when 'иное' then 'x'::sex_char
else null
end;
if ch is null then
raise invalid_parameter_value;
end if;
END
$sex$;
CREATE FUNCTION sex(ch sex_char, OUT txt varchar) LANGUAGE plpgsql IMMUTABLE STRICT PARALLEL SAFE AS
$sex$
BEGIN
txt:= case ch
when 'm'::sex_char then 'мужчина'
when 'f'::sex_char then 'женщина'
when 'x'::sex_char then 'иное'
else null
end;
if txt is null then
raise invalid_parameter_value;
end if;
END
$sex$;
create table sex2 (id float, sex_char "char" not null);
-- sex3 внешняя таблица c ключом smallint
create table sex_t (
sex_t_id smallint primary key,
sex varchar not null unique
);
insert into sex_t (sex_t_id,sex) values (1,'мужчина'),(0,'женщина'),(-1,'иное');
create table sex3 (id float, sex_t_id smallint not null references sex_t);
-- sex4 с serial, как бы это странно не выглядело, повторяю то, что видел в одной уважаемой компании
create table sex_t4 (
sex_t4_id serial primary key,
sex varchar not null unique
);
insert into sex_t4 (sex_t4_id,sex) values (1,'мужчина'),(0,'женщина'),(-1,'иное');
create table sex4 (id float, sex_t4_id integer not null references sex_t4);
-- текстовое поле
create table sex_t5 (
sex varchar primary key
);
insert into sex_t5 (sex) values ('мужчина'),('женщина'),('иное');
-- для btree индекса
create table sex5 (id float, sex varchar not null references sex_t5);
-- для hash индекса
create table sex5h (id float, sex varchar not null references sex_t5);
-- jsonb
-- для обычного gin индекса
create table sex6 (id float, jdoc jsonb not null);
-- для gin индекса с хэш по ключам и значениям
create table sex6h (id float, jdoc jsonb not null);
-- вставка данных
insert into sex1 (id,sex) select random, case when random<0.75 then 'мужчина'::sex_enum when random<0.99 then 'женщина'::sex_enum else 'иное'::sex_enum end from (select random() as random, generate_series(1,:table_size)) as subselect;
insert into sex5 (id,sex) select id,sex::varchar from sex1;
insert into sex2 (id,sex_char) select id,sex(sex) from sex5;
insert into sex3 (id,sex_t_id) select id,sex_t_id from sex5 join sex_t using (sex);
insert into sex4 (id,sex_t4_id) select id,sex_t4_id from sex5 join sex_t4 using (sex);
insert into sex5h (id,sex) select id,sex from sex5;
insert into sex6 (id,jdoc) select id,('{"sex": "'||sex||'"}')::jsonb from sex5;
insert into sex6h (id,jdoc) select id,jdoc from sex6;
-- создаем индексы
create index sex1_btree on sex1(sex);
create index sex2_btree on sex2(sex_char);
create index sex3_btree on sex3(sex_t_id);
create index sex4_btree on sex4(sex_t4_id);
create index sex5_btree on sex5(sex);
-- для текста используем hash
create index sex5h_hash on sex5h using hash(sex);
create index sex6_gin on sex6 using gin(jdoc);
-- тут тоже, по сути, hash
create index sex6h_gin_hash on sex6h using gin(jdoc jsonb_path_ops);
commit;
set role postgres;
-- экстеншин для прогрева (заполнения кэша PostgreSQL)
create extension if not exists pg_prewarm;
-- удобный экстеншин для мониторинга заполнения кэша
create extension if not exists pg_buffercache;
create or replace view cache as SELECT n.nspname AS schema,
c.relname,
pg_size_pretty(count(*) * 8192) AS buffered,
count(*) * 8 AS buffered_KiB,
round(100.0 * count(*)::numeric / ((( SELECT pg_settings.setting
FROM pg_settings
WHERE pg_settings.name = 'shared_buffers'::text))::integer)::numeric, 1) AS buffer_percent,
round(100.0 * count(*)::numeric * 8192::numeric / pg_table_size(c.oid::regclass)::numeric, 1) AS percent_of_relation
FROM pg_class c
JOIN pg_buffercache b ON b.relfilenode = c.relfilenode
JOIN pg_database d ON b.reldatabase = d.oid AND d.datname = current_database()
LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
GROUP BY c.oid, n.nspname, c.relname
ORDER BY buffered_kib DESC
LIMIT 20;
-- заключительный vacuum
vacuum freeze analyze;
test
Скрипт для тестирования:
Код
SPL
#!/bin/sh
set -o errexit -o noclobber -o nounset -o pipefail
#set -o errexit -o noclobber -o nounset -o pipefail -o xtrace
# for pgbench
PATH="$PATH:/usr/pgsql-13/bin"
# config
# database connection parameters
readonly PGDATABASE='sex'
readonly PGPORT=5432
export PGDATABASE PGPORT
# output data file
readonly data_csv='data.csv'
# init data files
readonly header='sex:,male,female,other'
if [ ! -s "$data_csv" ]
then
echo "$header" >|"$data_csv"
fi
# prewarm to the cache
psql --quiet -f prewarm.sql >/dev/null
# more prewarm
pgbench --no-vacuum --transaction 100 --file test.sql >/dev/null
for i in $(seq 1 100)
do
echo -n "$i "
date --iso-8601=seconds
pgbench --no-vacuum --transaction 100 --report-latencies --file 'test.sql' | \
awk "
/from sex1 where sex='мужчина';\$/ {printf "enum,%s,", \$1 >>"$data_csv";}
/from sex1 where sex='женщина';\$/ {printf "%s,", \$1 >>"$data_csv";}
/from sex1 where sex='иное';\$/ {printf "%s\\n", \$1 >>"$data_csv";}
/from sex2 where sex_char=sex\(txt=>'мужчина'\);\$/ {printf "\\"char\\",%s,", \$1 >>"$data_csv";}
/from sex2 where sex_char=sex\(txt=>'женщина'\);\$/ {printf "%s,", \$1 >>"$data_csv";}
/from sex2 where sex_char=sex\(txt=>'иное'\);\$/ {printf "%s\\n", \$1 >>"$data_csv";}
/from sex3 join sex_t using \(sex_t_id\) where sex='мужчина';\$/ {printf "smallint(join),%s,", \$1 >>"$data_csv";}
/from sex3 join sex_t using \(sex_t_id\) where sex='женщина';\$/ {printf "%s,", \$1 >>"$data_csv";}
/from sex3 join sex_t using \(sex_t_id\) where sex='иное';\$/ {printf "%s\\n", \$1 >>"$data_csv";}
/from sex3 where sex_t_id=\(select t.sex_t_id from sex_t t where sex='мужчина'\);\$/ {printf "smallint(subsel),%s,", \$1 >>"$data_csv";}
/from sex3 where sex_t_id=\(select t.sex_t_id from sex_t t where sex='женщина'\);\$/ {printf "%s,", \$1 >>"$data_csv";}
/from sex3 where sex_t_id=\(select t.sex_t_id from sex_t t where sex='иное'\);\$/ {printf "%s\\n", \$1 >>"$data_csv";}
/from sex4 join sex_t4 using \(sex_t4_id\) where sex='мужчина';\$/ {printf "integer(join),%s,", \$1 >>"$data_csv";}
/from sex4 join sex_t4 using \(sex_t4_id\) where sex='женщина';\$/ {printf "%s,", \$1 >>"$data_csv";}
/from sex4 join sex_t4 using \(sex_t4_id\) where sex='иное';\$/ {printf "%s\\n", \$1 >>"$data_csv";}
/from sex4 where sex_t4_id=\(select t.sex_t4_id from sex_t4 t where sex='мужчина'\);\$/ {printf "integer(subsel),%s,", \$1 >>"$data_csv";}
/from sex4 where sex_t4_id=\(select t.sex_t4_id from sex_t4 t where sex='женщина'\);\$/ {printf "%s,", \$1 >>"$data_csv";}
/from sex4 where sex_t4_id=\(select t.sex_t4_id from sex_t4 t where sex='иное'\);\$/ {printf "%s\\n", \$1 >>"$data_csv";}
/from sex5 where sex='мужчина';\$/ {printf "varchar(btree),%s,", \$1 >>"$data_csv";}
/from sex5 where sex='женщина';\$/ {printf "%s,", \$1 >>"$data_csv";}
/from sex5 where sex='иное';\$/ {printf "%s\\n", \$1 >>"$data_csv";}
/from sex5h where sex='мужчина';\$/ {printf "varchar(hash),%s,", \$1 >>"$data_csv";}
/from sex5h where sex='женщина';\$/ {printf "%s,", \$1 >>"$data_csv";}
/from sex5h where sex='иное';\$/ {printf "%s\\n", \$1 >>"$data_csv";}
/from sex6 where jdoc@>'{"sex":"мужчина"}';\$/ {printf "jsonb(gin),%s,", \$1 >>"$data_csv";}
/from sex6 where jdoc@>'{"sex":"женщина"}';\$/ {printf "%s,", \$1 >>"$data_csv";}
/from sex6 where jdoc@>'{"sex":"иное"}';\$/ {printf "%s\\n", \$1 >>"$data_csv";}
/from sex6h where jdoc@>'{"sex":"мужчина"}';\$/ {printf "jsonb(gin+hash),%s,", \$1 >>"$data_csv";}
/from sex6h where jdoc@>'{"sex":"женщина"}';\$/ {printf "%s,", \$1 >>"$data_csv";}
/from sex6h where jdoc@>'{"sex":"иное"}';\$/ {printf "%s\\n", \$1 >>"$data_csv";}
"
done
echo 'Done'
Размер таблиц и индексов
=> \dt+
List of relations
Schema | Name | Type | Owner | Persistence | Size | Description
--------+--------+-------+-------+-------------+--------+-------------
public | sex1 | table | olleg | permanent | 422 MB |
public | sex2 | table | olleg | permanent | 422 MB |
public | sex3 | table | olleg | permanent | 422 MB |
public | sex4 | table | olleg | permanent | 422 MB |
public | sex5 | table | olleg | permanent | 498 MB |
public | sex5h | table | olleg | permanent | 498 MB |
public | sex6 | table | olleg | permanent | 651 MB |
public | sex6h | table | olleg | permanent | 651 MB |
public | sex_t | table | olleg | permanent | 48 kB |
public | sex_t4 | table | olleg | permanent | 48 kB |
public | sex_t5 | table | olleg | permanent | 48 kB |
(11 rows)
=> \di+
List of relations
Schema | Name | Type | Owner | Table | Persistence | Size | Description
--------+----------------+-------+-------+--------+-------------+--------+-------------
public | sex1_btree | index | olleg | sex1 | permanent | 66 MB |
public | sex2_btree | index | olleg | sex2 | permanent | 66 MB |
public | sex3_btree | index | olleg | sex3 | permanent | 66 MB |
public | sex4_btree | index | olleg | sex4 | permanent | 66 MB |
public | sex5_btree | index | olleg | sex5 | permanent | 67 MB |
public | sex5h_hash | index | olleg | sex5h | permanent | 448 MB |
public | sex6_gin | index | olleg | sex6 | permanent | 21 MB |
public | sex6h_gin_hash | index | olleg | sex6h | permanent | 10 MB |
public | sex_t4_pkey | index | olleg | sex_t4 | permanent | 16 kB |
public | sex_t4_sex_key | index | olleg | sex_t4 | permanent | 16 kB |
public | sex_t5_pkey | index | olleg | sex_t5 | permanent | 16 kB |
public | sex_t_pkey | index | olleg | sex_t | permanent | 16 kB |
public | sex_t_sex_key | index | olleg | sex_t | permanent | 16 kB |
(13 rows)
Заметно, что при использовании типов данных размером 1 байт или 2 байта вместо типов данных размером 4 байта нет выигрыша ни в размере таблицы, ни в размере индекса. Видимо, это как-то связанно с выравниванием данных PostgreSQL по границам «слов». Более того, даже при использовании текстового поля проигрыш по размерам оказался не так велик, как ожидалось. Наверное, это связано с тем, что такое текстовое поле было одно (и строки небольших длин) и дополнительно есть много служебных полей в строке таблицы.
Размеры таблицы при использовании JSON оказались ожидаемо хуже, потому что там не только значение хранится в текстовом виде, но и именование атрибута. Конечно, если атрибут всего один, его можно было бы не именовать, но тут имитируется модная среди веб-разработчиков ситуация, когда вообще все данные таблицы загоняются в общий JSON, да еще, как правило, в денормализованном виде.
Удручают размеры hash-индекса, по размеру он как таблица, на основе которой построен. Хотя правильный hash-индекс (в теории) должен был бы показать хороший результат. Связано это с тем, что в PostgreSQL hash-индекс организован чтобы использовать универсальные hash функции и не так, как в описано теории. Написал письмо в PostgreSQL, без результата.
Удивительно маленькие размеры у индексов, построенных на базе GIN (по сравнению с btree). Но результаты их использования, как покажу потом, наихудшие. Где-то читал, что GIN-индексы активно используют внутри себя сжатие данных, возможно, этим можно всё объяснить.
Результаты
Выборка 75% должна быть характерна тем, что тут планировщик должен предпочитать поиск последовательным чтением таблицы, а не использовать индекс. При выборке 24% он предпочитает использовать индекс, но это довольно экстремальный случай. Выборка 1% более типичный поиск по индексу.
Данные потом были залиты в M$ Exel и там преобразованы в диаграммы «коробочки с усиками» (удобно, можно смотреть не только среднее значение или медиану, но также и распределение данных). То, что «коробочки с усиками» выглядят как горизонтальные полоски, говорит о том, что точность (повторяемость) замеров очень хорошая, разброса данных практический нет.
Сразу бросается в глаза что поиск по JSON примерно в несколько раз хуже всех остальных вариантов. Рассмотрим варианты подробнее:
enum и "char"
- Лидеры этого теста выполняются примерно одинаково, хотя я ожидал, что "char" будет в четыре раза быстрее. Возможно, это связано с тем, что PostgreSQL предпочитает выравнивать данные по размерам «слов». Поскольку выигрыша от "char" нет, значительно проще использовать enum.
- Планировщик на значениях гистограммы может правильно оценить размер выборки, при 75% работает последовательное чтение, а при 24% и 1% — индексы.
- По сути, внутренняя реализация enum представляет собой случай с внешней таблицей и integer (четырёхбайтным) ключом. Но видно, что работают какие-то оптимизации: например, при 75% работает последовательное чтение, а при внешней таблице с integerключом поиск по индексу, поэтому при 75% выборке enum заметно быстрее; при 24% и 1% выборках enum быстрее, чем select с внешней таблицей с помощью join, и сравним по скорости с select с подзапросом.
Пример планов запросов:
=> explain (costs false) select count(id) from sex1 where sex='женщина';
QUERY PLAN
-------------------------------------------------
Aggregate
-> Index Scan using sex1_btree on sex1
Index Cond: (sex = 'женщина'::sex_enum)
(3 rows)
=> explain (costs false) select count(id) from sex2 where sex_char=sex(txt=>'женщина');
QUERY PLAN
----------------------------------------------
Aggregate
-> Index Scan using sex2_btree on sex2
Index Cond: (sex_char = 'f'::"char")
(3 rows)
smallint и integer
- Между двухбайтовым smallint и четырёхбайтовым integer (serial) нет разницы с точки зрения времени выполнения. Возможно, это связано с тем, что PostgreSQL как-то выравнивает данные.
- Если в enum и "char" планировщик предпочел при выборке 75% использовать последовательное чтение таблицы, то в этом случае ошибочно идет поиск по индексу и виден проигрыш по производительности. Возможно, причина в том, что планировщик без выполнения запроса не может в этом случае предугадать, какая будет выборка. В случае с 1% и 24% он угадывает использовать индекс.
- При объединении таблиц с помощью join (Nested Loop) результат почему-то заметно хуже, чем в случае с подзапросом. Хотя, насколько я знаю, алгоритм там должен быть такой же. Т.е. это практически синонимы: подзапрос и Nested Loop. Наверное, тут есть окно возможностей для оптимизации Nested Loop до уровня подзапроса.
Для наглядности приведу планы запроса для 75% выборки, чтобы показать, что там не используется последовательное чтение. И план для запроса с подзапросом. Для 1% и 24% выборки планы точно такие же.
=> explain (costs false) select count(id) from sex3 join sex_t using (sex_t_id) where sex='мужчина';
QUERY PLAN
-------------------------------------------------------
Aggregate
-> Nested Loop
-> Seq Scan on sex_t
Filter: ((sex)::text = 'мужчина'::text)
-> Index Scan using sex3_btree on sex3
Index Cond: (sex_t_id = sex_t.sex_t_id)
(6 rows)
=> explain (costs false) select count(id) from sex3 where sex_t_id=(select t.sex_t_id from sex_t t where sex='мужчина');
QUERY PLAN
---------------------------------------------------
Aggregate
InitPlan 1 (returns $0)
-> Seq Scan on sex_t t
Filter: ((sex)::text = 'мужчина'::text)
-> Index Scan using sex3_btree on sex3
Index Cond: (sex_t_id = $0)
(6 rows)
varchar
- В отличие от предыдущего случая, планировщик работает, как ожидалось: при 75% последовательное чтение, при 1% и 24% — поиск по индексу.
- Результат поиска по текстовому полю с помощью btree-индекса заметно быстрее, чем при использовании объединения с внешней таблицей при помощи join, и сопоставим с объединением таблиц с помощью подзапроса. Бальзам на душу для любителей денормализации.
- Hash-индекс работает заметно хуже, чем btree (при таком распределении данных). Хотя ожидалось, что наоборот: в теории, hash-индекс именно в таком случае можно сделать очень быстрым. В теории, надо было бы создать три корзины с tuple ID и «специальную» hash-функцию. которая возвращала бы 1, 2 или 3, т.е. номер корзины. Видимо, что-то не так с hash-индексами у PostgreSQL, и более длительный результат как-то связана с очень большими размерами самого hash-индекса.
План для btree и hash-индекса.
=> explain (costs false) select count(id) from sex5 where sex='женщина';
QUERY PLAN
-----------------------------------------------------
Aggregate
-> Index Scan using sex5_btree on sex5
Index Cond: ((sex)::text = 'женщина'::text)
(3 rows)
=> explain (costs false) select count(id) from sex5h where sex='женщина';
QUERY PLAN
-----------------------------------------------------
Aggregate
-> Index Scan using sex5h_hash on sex5h
Index Cond: ((sex)::text = 'женщина'::text)
(3 rows)
json
- Здесь при 75% тоже поиск идёт последовательным чтением. Не знаю, как планировщик догадался, что здесь распределение будет 75%. Неужели строит гистограммы для внутренностей JSON? В старых версиях PostgreSQL в этом случае ошибочно использовался поиск по индексу. При 1% и 24% выборке PostgreSQL ожидаемо использует поиск по индексу.
- Поиск по хэшированным путям и значениям (индекс с jsonb_path_ops) заметно быстрее (в случае 1% — более, чем в полтора раза), чем по обычному GIN для JSON.
- Но, тем не менее, оба варианта с JSON — далеко отстающие аутсайдеры.
=> explain (costs false) select count(id) from sex6 where jdoc@>'{"sex":"мужчина"}';
QUERY PLAN
-------------------------------------------------------
Aggregate
-> Seq Scan on sex6
Filter: (jdoc @> '{"sex": "мужчина"}'::jsonb)
(3 rows)
=> explain (costs false) select count(id) from sex6 where jdoc@>'{"sex":"женщина"}';
QUERY PLAN
-----------------------------------------------------------------
Aggregate
-> Bitmap Heap Scan on sex6
Recheck Cond: (jdoc @> '{"sex": "женщина"}'::jsonb)
-> Bitmap Index Scan on sex6_gin
Index Cond: (jdoc @> '{"sex": "женщина"}'::jsonb)
(5 rows)
Выводы
Как ни странно, несмотря на всю кажущуюся неэффективность, официальный enum — лучшее решение для перечислений, он один из самых быстрых, и в то же время самый удобный в использовании. Но, я думаю, так получилось не потому, что 4 байтный enum очень хорошо продуман и оптимизирован, а потому, что поиск по таким типам данных как 1 байтный "char" и 2 байтный smallint недостаточно хорошо оптимизирован, как мог бы быть.
===========
Источник:
habr.com
===========
Похожие новости:
- [Open source, *nix, Виртуализация, Openshift] Создаем настраиваемые отчеты для оператора Metering
- [Microsoft SQL Server, ERP-системы] История одной миграции с SQL Server 2012 на SQL Server 2016+ в системе Microsoft Dynamics AX 2012
- [PostgreSQL] Этюд по PITR в PostgreSQL штатными средствами
- [Open source, PostgreSQL, Администрирование баз данных] Знакомство с pg_probackup. Третья часть
- [Разработка веб-сайтов, JavaScript, Проектирование и рефакторинг, Системы сборки] Как привести проект в чувство
- [.NET, C#] Дерево синтаксиса и альтернатива LINQ при взаимодействии с базами данных SQL (перевод)
- [PostgreSQL, Карьера в IT-индустрии] PostgreSQL: кому и зачем нужны продвинутые навыки работы?
- [SQL, Microsoft SQL Server, Администрирование баз данных] Шифрование в MySQL: ротация Master Key (перевод)
- [Карьера в IT-индустрии, DevOps, Системное администрирование, Серверное администрирование, Настройка Linux] DevOps Roadmap или пора бы автоматизироваться?
- [Python, Программирование] О полезности contextvars
Теги для поиска: #_postgresql, #_sql, #_postgresql, #_blog_kompanii_domklik (
Блог компании ДомКлик
), #_postgresql, #_sql
Вы не можете начинать темы
Вы не можете отвечать на сообщения
Вы не можете редактировать свои сообщения
Вы не можете удалять свои сообщения
Вы не можете голосовать в опросах
Вы не можете прикреплять файлы к сообщениям
Вы не можете скачивать файлы
Текущее время: 23-Ноя 01:06
Часовой пояс: UTC + 5
| Автор | Сообщение |
|---|---|
|
news_bot ®
Стаж: 6 лет 9 месяцев |
|
 Пролог Под перечислимым типом обычно понимают тип данных, который может принимать ограниченное и, как правило, небольшое число значений. Его выделяет то, что эти значения часто хардкодятся программистами в исходный код. И, как следствие, пользователи и операторы приложения не могут менять множество значений перечислимого типа. Их меняют только разработчики, зачастую с соответствующими исправлениями в коде и бизнес-логике приложения. Примерами перечислимых типов могут быть: времена года, месяцы, направление типа въезда/выезд или in/out, какие-нибудь типы или категории чего-нибудь, и так далее. В PostgreSQL подобную функциональность могут и реализуют различными способами. Этому посвящена статья. Лирическое отступление (или почему не boolean) В качестве примера перечислимого типа для всего последующего изложения я выбрал пол человека. Часто для хранения пола выбирают тип данных boolean. Что неправильно. Во-первых, придется объяснять феминисткам, почему мужской пол «истинный», а женский — «ложный». Во-вторых, boolean создавался совсем для другого, и все типы операций и функций, определенные для него, в этой задаче будут бессмысленными. Ну, разве что только XOR сохраняет здравый смысл. И в-третьих, помимо мужского и женского пола есть еще пол непонятный. Речь здесь не только про извращенцев вроде Кончиты Вурст, есть люди с генетической аномалией мозаицизм по половым хромосомам, когда даже на генетическом уровне нельзя сказать, какой пол у человека. Что гораздо важнее, такой тип пола, как "other", стандартизирован ИКАО для официальных документов, и встречается в официальных документах, предъявляемых на пограничных пунктах, к сожалению, гораздо чаще, чем того требует природа человека. А когда люди с такими документами пересекают российскую границу, наши православные пограничники тоже вынуждены указывать такой пол уже во внутрироссийских документах. И для этой цели нельзя использовать значение null в типе boolean. Значение null означает «значение неизвестно», например, не была заполнена графа "sex" в документе, и в действительности пол может оказаться неизвестно каким. А вот пол "other" — это совершенно точно известный факт, что человек чувствует и записывает в документах, что он «особенный». Поэтому для sex надо использовать не boolean, а перечислимый тип. Варианты Enum — встроенный в PostgreSQL официальный тип В PostgreSQL есть специальный тип данных, созданный для такого случая, называется enum. Вот пример его определения: CREATE TYPE sex AS ENUM ('мужчина', 'женщина', 'иное');
Пример использования: select id from table where sex='женщина';
То, что везде в примерах ищется женщина, это не сексизм, а олицетворение поговорки: "Cherchez la femme". Текстовые обозначения не могут быть длиннее 63 байт (если используем русский язык и UTF-8, то делите на два). В самой таблице значения будут занимать 4 байта. Потому что, по сути, этот тип данных — синтаксический сахар. На самом деле этот тип реализуется с помощью внешней таблицы, но планировщик выполняет некоторые оптимизации. Текстовые значения хранятся в таблице pg_enum, а ключом являются четырёхбайтные OID. Но это лучше, чем простое использование внешней таблицы. В запросах можно применять текстовые обозначения напрямую. И если в случае ошибки будет указано несуществующее значение, то будет поднят syntax error, в то время как при обычном использовании внешней таблицы никакой ошибки не было бы, запрос попросту вернул пустой результат. Также этот тип безопасен в том смысле, что его нельзя сравнивать не только с другими типами, но даже с разными типами enum. В качестве бонуса, этот тип поддерживает упорядочивание его элементов (определены операции сравнения и сортировки), и этим порядком можно управлять (например, менять с помощью ALTER TYPE). Недостатки: использовать 4 байта там, где можно было бы обойтись одним, кажется расточительством. И когда я написал Тому Лэйну об этом недостатке существующего решения, то получил обычный в мире Open Source ответ: «Раз ты такой умный, реализуй сам как считаешь лучше». Char — внутренний перечислимый тип PostgreSQL Но не смотря на то, что в PostgreSQL есть специальный перечислимый тип для пользователей, во внутренних таблицах используется тип "char" в качестве перечислимого типа. Кавычки обязательны, потому что без них он превратится в широко известный тип char(много букв). В тип "char" помещается ровно 1 байт в символьном виде, т.е. размер в 4 раза меньше, чем официальный enum. При кодировке UTF-8 в него влезут английские буквы, цифры и символы, а вот русские буквы — нет. Тип можно использовать, прямо указывая обозначения в виде букв, подобрав их по какому-нибудь мнемоническому правилу или стандарту. В нашем случае, в соответствии со стандартом ИКАО это будет m, f, x. Но это пока не так интересно: буквы, конечно, удобно хардкодить, но хочется иметь возможность работать и с текстовыми обозначениями. Для этого можно написать простые функции. Также можно усилить проверку типов, использовав domain с указанием допустимых значений. CREATE DOMAIN sex_char AS "char" CHECK (VALUE in ('m','f','x'));
CREATE FUNCTION sex(txt varchar, OUT ch sex_char) LANGUAGE plpgsql IMMUTABLE STRICT PARALLEL SAFE AS $sex$ BEGIN ch:= case txt when 'мужчина' then 'm'::sex_char when 'женщина' then 'f'::sex_char when 'иное' then 'x'::sex_char else null end; if ch is null then raise invalid_parameter_value; end if; END $sex$; CREATE FUNCTION sex(ch sex_char, OUT txt varchar) LANGUAGE plpgsql IMMUTABLE STRICT PARALLEL SAFE AS $sex$ BEGIN txt:= case ch when 'm'::sex_char then 'мужчина' when 'f'::sex_char then 'женщина' when 'x'::sex_char then 'иное' else null end; if txt is null then raise invalid_parameter_value; end if; END $sex$; Две этих функции, по сути, составляют одну полиморфную функцию. Примеры использования: => select sex(ch=>'f');
sex --------- женщина (1 row) => select sex(txt=>'женщина'); sex ----- f (1 row) Указывать имя аргумента (или типа данных) нужно потому, что парсер, видя текстовый литерал, не может определить тип аргумента и выбрать нужную функцию. В тех случаях, когда парсеру очевиден тип аргумента, его имя можно не указывать. Например, если применить функцию саму к себе, получится тривиальная: => select sex(sex(txt=>'женщина'));
sex --------- женщина (1 row) Примеры использования: select id from table where sex='f';
select id from table where sex=sex(txt=>'женщина'); Из достоинств этого метода: занимает 1 байт, нет внешних таблиц и ожидается хорошее быстродействие. Классическая внешняя таблица Классика нормализации. create table sex_t (
sex_t_id smallint primary key, sex varchar not null unique ); И эта таблица подключается куда надо как внешняя. Пример использования: select id from table join sex_t using (sex_t_id) where sex='женщина';
Очевидно, что всё это похоже на внутреннее устройство у официального enum. Из недостатков всё то, что в enum было перечислено как достоинства: приходится указывать в запросах внешнюю таблицу, что сильно загромождает запрос; нет синтаксических ошибок в случае, если кто-то неправильно запишет текстовое значение, и т.д. Достоинство одно: занимает 2 байта вместо 4 (т.е. в два раза меньше, чем официальный enum). Экзотика Можно еще упомянуть способы, к которым я не имею ни малейшего отношения. Но они встречаются. Видел пример, который выглядит как «классическая внешняя таблица», но для ключа вместо smallint использовался serial. Причем в связанном с ним sequence шаг умышленно выставлялся в 0 (чтобы вызвать ошибки при его использовании), и это не баг, а идеологическая фича (как мне объяснил разработчик): поскольку значения ключа захардкожены, при добавлении новых значений значения ключа должны были явно указываться программистом. И значений там было не больше 10. Другой способ, который любят поклонники денормализации, заключается в создании текстового поля с указанием в нём текстовых значений. Вдобавок можно сделать вспомогательную таблицу со списком допустимых значений, чтобы использовать, например, при создании комбобоксов. Когда структуру базы данных создают «веб-разработчики», перечислимый тип могут сохранять в виде текстовых значений, но не в текстовом поле, а внутри jsonb. Как правило, конечно, не в специально для этого созданном jsonb, а внутри одного большого jsonb, куда заложены все атрибуты данной таблицы. Всё это вызывает у меня скепсис, но такие варианты интересно рассмотреть при тестировании не потому, что они хороши, а потому, что интересно узнать, насколько они плохи. Описание эксперимента Идея Предположим, есть девелоперская контора, в которой трудится 75 % мужчин, 24 % женщин и еще 1 % неопределившихся существ. Отделу кадров на 23 февраля надо получить количество мужчин, чтобы закупить для них подарки, потом 8 марта получить количество женщин. А после кадровики задумываются, что меньшинство дискриминировать и оставлять без подарков нехорошо. И нужно количество иных, чтобы 1 апреля подарить подарки и им. Создам разные варианты таблиц, имитирующих список сотрудников с указанием пола, и замерю время выполнения всех трех запросов. Поскольку работу с винчестерами мерить не интересно (слишком большой элемент случайности, связанный с движением головок), то для начала «прогрею» таблицы, чтобы работать только с кэшем в ОЗУ. Чтобы уменьшить влияние на результат каких-нибудь сторонних процессов, которые могут возникать в операционке и вне её, измерения буду проводить сериями. И чтобы измерять эффективность типов данных, а не то, как планировщик PostgreSQL иногда ошибается, принимая решения по распараллеливанию запросов, распараллеливание будет отключено. В каждой таблице 10 000 000 записей, содержимое всех таблиц одинаковое (по составу). И поскольку запросы должны символизировать фильтрацию по полю перечисления и выдачу полезных данных из других полей, я решил отключить index only scan. Сделаю я это, изменив в запросах count(*) на count(id), т.е. явно укажу, что нужны данные, не входящие в индекс. Описание стенда Стенд сделал из того, что было: ноут MSI, операционка сообщает о 8 ядрах процессора, 16 Гб ОЗУ (hugepages 2 Мб на 14 Гб), 0 swap. Но поскольку тут интересно лишь относительное сравнение результатов измерений друг с другом, а не абсолютные значения, подробно расписывать железо не буду. CentOS 8, PostgreSQL 13 с shared_buffers (кэшем PostgreSQL) на 14 Гб. Было сделано 100 серий экспериментов, в каждой серии по 100 замеров каждого варианта, итого 10 000 замеров каждого варианта. Чтобы каждый мог повторить эксперимент, привожу все скрипты. postgresql.conf Этот файл инклюдится в стандартный postgresql.conf. # Минимальный уровень WAL чтобы уменьшить время на создание таблиц
wal_level = minimal max_wal_senders = 0 # Поскольку работаем с закэшированными таблицами, издержек на "случайный" доступ нет. random_page_cost = 1 # отключаем распараллеливание max_parallel_workers_per_gather=0 # Кэш PostgreSQL shared_buffers = 14GB prewarm.sql Прогреваю БД с помощью pg_prewarm. select pg_prewarm('sex1');
select pg_prewarm('sex1_btree'); select pg_prewarm('sex2'); select pg_prewarm('sex2_btree'); select pg_prewarm('sex3'); select pg_prewarm('sex3_btree'); select pg_prewarm('sex4'); select pg_prewarm('sex4_btree'); select pg_prewarm('sex5'); select pg_prewarm('sex5_btree'); select pg_prewarm('sex5h'); select pg_prewarm('sex5h_hash'); select pg_prewarm('sex6'); select pg_prewarm('sex6_gin'); select pg_prewarm('sex6h'); select pg_prewarm('sex6h_gin_hash'); test.sql Такими запросами проводится тестирование. И эти же запросы используются для дополнительного прогрева (pg_prewarm недостаточно). Напомню, что я использую count(id), чтобы отключить index only scan. КодSPLselect count(id) from sex1 where sex='мужчина';
select count(id) from sex1 where sex='женщина'; select count(id) from sex1 where sex='иное'; select count(id) from sex2 where sex_char=sex(txt=>'мужчина'); select count(id) from sex2 where sex_char=sex(txt=>'женщина'); select count(id) from sex2 where sex_char=sex(txt=>'иное'); select count(id) from sex3 join sex_t using (sex_t_id) where sex='мужчина'; select count(id) from sex3 join sex_t using (sex_t_id) where sex='женщина'; select count(id) from sex3 join sex_t using (sex_t_id) where sex='иное'; select count(id) from sex3 where sex_t_id=(select t.sex_t_id from sex_t t where sex='мужчина'); select count(id) from sex3 where sex_t_id=(select t.sex_t_id from sex_t t where sex='женщина'); select count(id) from sex3 where sex_t_id=(select t.sex_t_id from sex_t t where sex='иное'); select count(id) from sex4 join sex_t4 using (sex_t4_id) where sex='мужчина'; select count(id) from sex4 join sex_t4 using (sex_t4_id) where sex='женщина'; select count(id) from sex4 join sex_t4 using (sex_t4_id) where sex='иное'; select count(id) from sex4 where sex_t4_id=(select t.sex_t4_id from sex_t4 t where sex='мужчина'); select count(id) from sex4 where sex_t4_id=(select t.sex_t4_id from sex_t4 t where sex='женщина'); select count(id) from sex4 where sex_t4_id=(select t.sex_t4_id from sex_t4 t where sex='иное'); select count(id) from sex5 where sex='мужчина'; select count(id) from sex5 where sex='женщина'; select count(id) from sex5 where sex='иное'; select count(id) from sex5h where sex='мужчина'; select count(id) from sex5h where sex='женщина'; select count(id) from sex5h where sex='иное'; select count(id) from sex6 where jdoc@>'{"sex":"мужчина"}'; select count(id) from sex6 where jdoc@>'{"sex":"женщина"}'; select count(id) from sex6 where jdoc@>'{"sex":"иное"}'; select count(id) from sex6h where jdoc@>'{"sex":"мужчина"}'; select count(id) from sex6h where jdoc@>'{"sex":"женщина"}'; select count(id) from sex6h where jdoc@>'{"sex":"иное"}'; init.sql Скрипт первоначального создания БД для экспериментов: КодSPL-- заполняем таблицы, во всех таблицах одинаковые данные
\set table_size 10000000 -- удобный view для посмотра размера таблиц после их заполнения create or replace view disk as SELECT n.nspname AS schema, c.relname, pg_size_pretty(pg_relation_size(c.oid::regclass)) AS size, pg_relation_size(c.oid::regclass)/1024 AS size_KiB FROM pg_class c LEFT JOIN pg_namespace n ON n.oid = c.relnamespace ORDER BY (pg_relation_size(c.oid::regclass)) DESC LIMIT 20; begin; -- sex1 официальный enum CREATE TYPE sex_enum AS ENUM ('мужчина', 'женщина', 'иное'); create table sex1 (id float, sex sex_enum not null); -- sex2 "char" CREATE DOMAIN sex_char AS "char" CHECK (VALUE in ('m','f','x')); CREATE FUNCTION sex(txt varchar, OUT ch sex_char) LANGUAGE plpgsql IMMUTABLE STRICT PARALLEL SAFE AS $sex$ BEGIN ch:= case txt when 'мужчина' then 'm'::sex_char when 'женщина' then 'f'::sex_char when 'иное' then 'x'::sex_char else null end; if ch is null then raise invalid_parameter_value; end if; END $sex$; CREATE FUNCTION sex(ch sex_char, OUT txt varchar) LANGUAGE plpgsql IMMUTABLE STRICT PARALLEL SAFE AS $sex$ BEGIN txt:= case ch when 'm'::sex_char then 'мужчина' when 'f'::sex_char then 'женщина' when 'x'::sex_char then 'иное' else null end; if txt is null then raise invalid_parameter_value; end if; END $sex$; create table sex2 (id float, sex_char "char" not null); -- sex3 внешняя таблица c ключом smallint create table sex_t ( sex_t_id smallint primary key, sex varchar not null unique ); insert into sex_t (sex_t_id,sex) values (1,'мужчина'),(0,'женщина'),(-1,'иное'); create table sex3 (id float, sex_t_id smallint not null references sex_t); -- sex4 с serial, как бы это странно не выглядело, повторяю то, что видел в одной уважаемой компании create table sex_t4 ( sex_t4_id serial primary key, sex varchar not null unique ); insert into sex_t4 (sex_t4_id,sex) values (1,'мужчина'),(0,'женщина'),(-1,'иное'); create table sex4 (id float, sex_t4_id integer not null references sex_t4); -- текстовое поле create table sex_t5 ( sex varchar primary key ); insert into sex_t5 (sex) values ('мужчина'),('женщина'),('иное'); -- для btree индекса create table sex5 (id float, sex varchar not null references sex_t5); -- для hash индекса create table sex5h (id float, sex varchar not null references sex_t5); -- jsonb -- для обычного gin индекса create table sex6 (id float, jdoc jsonb not null); -- для gin индекса с хэш по ключам и значениям create table sex6h (id float, jdoc jsonb not null); -- вставка данных insert into sex1 (id,sex) select random, case when random<0.75 then 'мужчина'::sex_enum when random<0.99 then 'женщина'::sex_enum else 'иное'::sex_enum end from (select random() as random, generate_series(1,:table_size)) as subselect; insert into sex5 (id,sex) select id,sex::varchar from sex1; insert into sex2 (id,sex_char) select id,sex(sex) from sex5; insert into sex3 (id,sex_t_id) select id,sex_t_id from sex5 join sex_t using (sex); insert into sex4 (id,sex_t4_id) select id,sex_t4_id from sex5 join sex_t4 using (sex); insert into sex5h (id,sex) select id,sex from sex5; insert into sex6 (id,jdoc) select id,('{"sex": "'||sex||'"}')::jsonb from sex5; insert into sex6h (id,jdoc) select id,jdoc from sex6; -- создаем индексы create index sex1_btree on sex1(sex); create index sex2_btree on sex2(sex_char); create index sex3_btree on sex3(sex_t_id); create index sex4_btree on sex4(sex_t4_id); create index sex5_btree on sex5(sex); -- для текста используем hash create index sex5h_hash on sex5h using hash(sex); create index sex6_gin on sex6 using gin(jdoc); -- тут тоже, по сути, hash create index sex6h_gin_hash on sex6h using gin(jdoc jsonb_path_ops); commit; set role postgres; -- экстеншин для прогрева (заполнения кэша PostgreSQL) create extension if not exists pg_prewarm; -- удобный экстеншин для мониторинга заполнения кэша create extension if not exists pg_buffercache; create or replace view cache as SELECT n.nspname AS schema, c.relname, pg_size_pretty(count(*) * 8192) AS buffered, count(*) * 8 AS buffered_KiB, round(100.0 * count(*)::numeric / ((( SELECT pg_settings.setting FROM pg_settings WHERE pg_settings.name = 'shared_buffers'::text))::integer)::numeric, 1) AS buffer_percent, round(100.0 * count(*)::numeric * 8192::numeric / pg_table_size(c.oid::regclass)::numeric, 1) AS percent_of_relation FROM pg_class c JOIN pg_buffercache b ON b.relfilenode = c.relfilenode JOIN pg_database d ON b.reldatabase = d.oid AND d.datname = current_database() LEFT JOIN pg_namespace n ON n.oid = c.relnamespace GROUP BY c.oid, n.nspname, c.relname ORDER BY buffered_kib DESC LIMIT 20; -- заключительный vacuum vacuum freeze analyze; test Скрипт для тестирования: КодSPL#!/bin/sh
set -o errexit -o noclobber -o nounset -o pipefail #set -o errexit -o noclobber -o nounset -o pipefail -o xtrace # for pgbench PATH="$PATH:/usr/pgsql-13/bin" # config # database connection parameters readonly PGDATABASE='sex' readonly PGPORT=5432 export PGDATABASE PGPORT # output data file readonly data_csv='data.csv' # init data files readonly header='sex:,male,female,other' if [ ! -s "$data_csv" ] then echo "$header" >|"$data_csv" fi # prewarm to the cache psql --quiet -f prewarm.sql >/dev/null # more prewarm pgbench --no-vacuum --transaction 100 --file test.sql >/dev/null for i in $(seq 1 100) do echo -n "$i " date --iso-8601=seconds pgbench --no-vacuum --transaction 100 --report-latencies --file 'test.sql' | \ awk " /from sex1 where sex='мужчина';\$/ {printf "enum,%s,", \$1 >>"$data_csv";} /from sex1 where sex='женщина';\$/ {printf "%s,", \$1 >>"$data_csv";} /from sex1 where sex='иное';\$/ {printf "%s\\n", \$1 >>"$data_csv";} /from sex2 where sex_char=sex\(txt=>'мужчина'\);\$/ {printf "\\"char\\",%s,", \$1 >>"$data_csv";} /from sex2 where sex_char=sex\(txt=>'женщина'\);\$/ {printf "%s,", \$1 >>"$data_csv";} /from sex2 where sex_char=sex\(txt=>'иное'\);\$/ {printf "%s\\n", \$1 >>"$data_csv";} /from sex3 join sex_t using \(sex_t_id\) where sex='мужчина';\$/ {printf "smallint(join),%s,", \$1 >>"$data_csv";} /from sex3 join sex_t using \(sex_t_id\) where sex='женщина';\$/ {printf "%s,", \$1 >>"$data_csv";} /from sex3 join sex_t using \(sex_t_id\) where sex='иное';\$/ {printf "%s\\n", \$1 >>"$data_csv";} /from sex3 where sex_t_id=\(select t.sex_t_id from sex_t t where sex='мужчина'\);\$/ {printf "smallint(subsel),%s,", \$1 >>"$data_csv";} /from sex3 where sex_t_id=\(select t.sex_t_id from sex_t t where sex='женщина'\);\$/ {printf "%s,", \$1 >>"$data_csv";} /from sex3 where sex_t_id=\(select t.sex_t_id from sex_t t where sex='иное'\);\$/ {printf "%s\\n", \$1 >>"$data_csv";} /from sex4 join sex_t4 using \(sex_t4_id\) where sex='мужчина';\$/ {printf "integer(join),%s,", \$1 >>"$data_csv";} /from sex4 join sex_t4 using \(sex_t4_id\) where sex='женщина';\$/ {printf "%s,", \$1 >>"$data_csv";} /from sex4 join sex_t4 using \(sex_t4_id\) where sex='иное';\$/ {printf "%s\\n", \$1 >>"$data_csv";} /from sex4 where sex_t4_id=\(select t.sex_t4_id from sex_t4 t where sex='мужчина'\);\$/ {printf "integer(subsel),%s,", \$1 >>"$data_csv";} /from sex4 where sex_t4_id=\(select t.sex_t4_id from sex_t4 t where sex='женщина'\);\$/ {printf "%s,", \$1 >>"$data_csv";} /from sex4 where sex_t4_id=\(select t.sex_t4_id from sex_t4 t where sex='иное'\);\$/ {printf "%s\\n", \$1 >>"$data_csv";} /from sex5 where sex='мужчина';\$/ {printf "varchar(btree),%s,", \$1 >>"$data_csv";} /from sex5 where sex='женщина';\$/ {printf "%s,", \$1 >>"$data_csv";} /from sex5 where sex='иное';\$/ {printf "%s\\n", \$1 >>"$data_csv";} /from sex5h where sex='мужчина';\$/ {printf "varchar(hash),%s,", \$1 >>"$data_csv";} /from sex5h where sex='женщина';\$/ {printf "%s,", \$1 >>"$data_csv";} /from sex5h where sex='иное';\$/ {printf "%s\\n", \$1 >>"$data_csv";} /from sex6 where jdoc@>'{"sex":"мужчина"}';\$/ {printf "jsonb(gin),%s,", \$1 >>"$data_csv";} /from sex6 where jdoc@>'{"sex":"женщина"}';\$/ {printf "%s,", \$1 >>"$data_csv";} /from sex6 where jdoc@>'{"sex":"иное"}';\$/ {printf "%s\\n", \$1 >>"$data_csv";} /from sex6h where jdoc@>'{"sex":"мужчина"}';\$/ {printf "jsonb(gin+hash),%s,", \$1 >>"$data_csv";} /from sex6h where jdoc@>'{"sex":"женщина"}';\$/ {printf "%s,", \$1 >>"$data_csv";} /from sex6h where jdoc@>'{"sex":"иное"}';\$/ {printf "%s\\n", \$1 >>"$data_csv";} " done echo 'Done' Размер таблиц и индексов => \dt+
List of relations Schema | Name | Type | Owner | Persistence | Size | Description --------+--------+-------+-------+-------------+--------+------------- public | sex1 | table | olleg | permanent | 422 MB | public | sex2 | table | olleg | permanent | 422 MB | public | sex3 | table | olleg | permanent | 422 MB | public | sex4 | table | olleg | permanent | 422 MB | public | sex5 | table | olleg | permanent | 498 MB | public | sex5h | table | olleg | permanent | 498 MB | public | sex6 | table | olleg | permanent | 651 MB | public | sex6h | table | olleg | permanent | 651 MB | public | sex_t | table | olleg | permanent | 48 kB | public | sex_t4 | table | olleg | permanent | 48 kB | public | sex_t5 | table | olleg | permanent | 48 kB | (11 rows) => \di+ List of relations Schema | Name | Type | Owner | Table | Persistence | Size | Description --------+----------------+-------+-------+--------+-------------+--------+------------- public | sex1_btree | index | olleg | sex1 | permanent | 66 MB | public | sex2_btree | index | olleg | sex2 | permanent | 66 MB | public | sex3_btree | index | olleg | sex3 | permanent | 66 MB | public | sex4_btree | index | olleg | sex4 | permanent | 66 MB | public | sex5_btree | index | olleg | sex5 | permanent | 67 MB | public | sex5h_hash | index | olleg | sex5h | permanent | 448 MB | public | sex6_gin | index | olleg | sex6 | permanent | 21 MB | public | sex6h_gin_hash | index | olleg | sex6h | permanent | 10 MB | public | sex_t4_pkey | index | olleg | sex_t4 | permanent | 16 kB | public | sex_t4_sex_key | index | olleg | sex_t4 | permanent | 16 kB | public | sex_t5_pkey | index | olleg | sex_t5 | permanent | 16 kB | public | sex_t_pkey | index | olleg | sex_t | permanent | 16 kB | public | sex_t_sex_key | index | olleg | sex_t | permanent | 16 kB | (13 rows) Заметно, что при использовании типов данных размером 1 байт или 2 байта вместо типов данных размером 4 байта нет выигрыша ни в размере таблицы, ни в размере индекса. Видимо, это как-то связанно с выравниванием данных PostgreSQL по границам «слов». Более того, даже при использовании текстового поля проигрыш по размерам оказался не так велик, как ожидалось. Наверное, это связано с тем, что такое текстовое поле было одно (и строки небольших длин) и дополнительно есть много служебных полей в строке таблицы. Размеры таблицы при использовании JSON оказались ожидаемо хуже, потому что там не только значение хранится в текстовом виде, но и именование атрибута. Конечно, если атрибут всего один, его можно было бы не именовать, но тут имитируется модная среди веб-разработчиков ситуация, когда вообще все данные таблицы загоняются в общий JSON, да еще, как правило, в денормализованном виде. Удручают размеры hash-индекса, по размеру он как таблица, на основе которой построен. Хотя правильный hash-индекс (в теории) должен был бы показать хороший результат. Связано это с тем, что в PostgreSQL hash-индекс организован чтобы использовать универсальные hash функции и не так, как в описано теории. Написал письмо в PostgreSQL, без результата. Удивительно маленькие размеры у индексов, построенных на базе GIN (по сравнению с btree). Но результаты их использования, как покажу потом, наихудшие. Где-то читал, что GIN-индексы активно используют внутри себя сжатие данных, возможно, этим можно всё объяснить. Результаты Выборка 75% должна быть характерна тем, что тут планировщик должен предпочитать поиск последовательным чтением таблицы, а не использовать индекс. При выборке 24% он предпочитает использовать индекс, но это довольно экстремальный случай. Выборка 1% более типичный поиск по индексу. Данные потом были залиты в M$ Exel и там преобразованы в диаграммы «коробочки с усиками» (удобно, можно смотреть не только среднее значение или медиану, но также и распределение данных). То, что «коробочки с усиками» выглядят как горизонтальные полоски, говорит о том, что точность (повторяемость) замеров очень хорошая, разброса данных практический нет.    Сразу бросается в глаза что поиск по JSON примерно в несколько раз хуже всех остальных вариантов. Рассмотрим варианты подробнее: enum и "char"
Пример планов запросов: => explain (costs false) select count(id) from sex1 where sex='женщина';
QUERY PLAN ------------------------------------------------- Aggregate -> Index Scan using sex1_btree on sex1 Index Cond: (sex = 'женщина'::sex_enum) (3 rows) => explain (costs false) select count(id) from sex2 where sex_char=sex(txt=>'женщина'); QUERY PLAN ---------------------------------------------- Aggregate -> Index Scan using sex2_btree on sex2 Index Cond: (sex_char = 'f'::"char") (3 rows) smallint и integer
Для наглядности приведу планы запроса для 75% выборки, чтобы показать, что там не используется последовательное чтение. И план для запроса с подзапросом. Для 1% и 24% выборки планы точно такие же. => explain (costs false) select count(id) from sex3 join sex_t using (sex_t_id) where sex='мужчина';
QUERY PLAN ------------------------------------------------------- Aggregate -> Nested Loop -> Seq Scan on sex_t Filter: ((sex)::text = 'мужчина'::text) -> Index Scan using sex3_btree on sex3 Index Cond: (sex_t_id = sex_t.sex_t_id) (6 rows) => explain (costs false) select count(id) from sex3 where sex_t_id=(select t.sex_t_id from sex_t t where sex='мужчина'); QUERY PLAN --------------------------------------------------- Aggregate InitPlan 1 (returns $0) -> Seq Scan on sex_t t Filter: ((sex)::text = 'мужчина'::text) -> Index Scan using sex3_btree on sex3 Index Cond: (sex_t_id = $0) (6 rows) varchar
План для btree и hash-индекса. => explain (costs false) select count(id) from sex5 where sex='женщина';
QUERY PLAN ----------------------------------------------------- Aggregate -> Index Scan using sex5_btree on sex5 Index Cond: ((sex)::text = 'женщина'::text) (3 rows) => explain (costs false) select count(id) from sex5h where sex='женщина'; QUERY PLAN ----------------------------------------------------- Aggregate -> Index Scan using sex5h_hash on sex5h Index Cond: ((sex)::text = 'женщина'::text) (3 rows) json
=> explain (costs false) select count(id) from sex6 where jdoc@>'{"sex":"мужчина"}';
QUERY PLAN ------------------------------------------------------- Aggregate -> Seq Scan on sex6 Filter: (jdoc @> '{"sex": "мужчина"}'::jsonb) (3 rows) => explain (costs false) select count(id) from sex6 where jdoc@>'{"sex":"женщина"}'; QUERY PLAN ----------------------------------------------------------------- Aggregate -> Bitmap Heap Scan on sex6 Recheck Cond: (jdoc @> '{"sex": "женщина"}'::jsonb) -> Bitmap Index Scan on sex6_gin Index Cond: (jdoc @> '{"sex": "женщина"}'::jsonb) (5 rows) Выводы Как ни странно, несмотря на всю кажущуюся неэффективность, официальный enum — лучшее решение для перечислений, он один из самых быстрых, и в то же время самый удобный в использовании. Но, я думаю, так получилось не потому, что 4 байтный enum очень хорошо продуман и оптимизирован, а потому, что поиск по таким типам данных как 1 байтный "char" и 2 байтный smallint недостаточно хорошо оптимизирован, как мог бы быть. =========== Источник: habr.com =========== Похожие новости:
Блог компании ДомКлик ), #_postgresql, #_sql |
|
Вы не можете начинать темы
Вы не можете отвечать на сообщения
Вы не можете редактировать свои сообщения
Вы не можете удалять свои сообщения
Вы не можете голосовать в опросах
Вы не можете прикреплять файлы к сообщениям
Вы не можете скачивать файлы
Вы не можете отвечать на сообщения
Вы не можете редактировать свои сообщения
Вы не можете удалять свои сообщения
Вы не можете голосовать в опросах
Вы не можете прикреплять файлы к сообщениям
Вы не можете скачивать файлы
Текущее время: 23-Ноя 01:06
Часовой пояс: UTC + 5