splarv 29 окт 2020 в 17:14

Перечислимый тип и PostgreSQL

20 мин

11K

Пролог

Под перечислимым типом обычно понимают тип данных, который может принимать ограниченное и, как правило, небольшое число значений. Его выделяет то, что эти значения часто хардкодятся программистами в исходный код. И, как следствие, пользователи и операторы приложения не могут менять множество значений перечислимого типа. Их меняют только разработчики, зачастую с соответствующими исправлениями в коде и бизнес-логике приложения. Примерами перечислимых типов могут быть: времена года, месяцы, направление типа въезда/выезд или 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.

Код

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

Скрипт первоначального создания БД для экспериментов:

Код

-- заполняем таблицы, во всех таблицах одинаковые данные
\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

Скрипт для тестирования:

Код

#!/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 и там преобразованы в диаграммы «коробочки с усиками» (удобно, можно смотреть не только среднее значение или медиану, но также и распределение данных). То, что «коробочки с усиками» выглядят как горизонтальные полоски, говорит о том, что точность (повторяемость) замеров очень хорошая, разброса данных практический нет.

75% 24%

Сразу бросается в глаза что поиск по 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 недостаточно хорошо оптимизирован, как мог бы быть.

Теги:

postgresql

Хабы:

Если эта публикация вас вдохновила и вы хотите поддержать автора — не стесняйтесь нажать на кнопку

Перечислимый тип и PostgreSQL

Пролог

Лирическое отступление (или почему не boolean)

Варианты

Enum — встроенный в PostgreSQL официальный тип

Char — внутренний перечислимый тип PostgreSQL

Классическая внешняя таблица

Экзотика

Описание эксперимента

Идея

Описание стенда

postgresql.conf

prewarm.sql

test.sql

init.sql

test

Размер таблиц и индексов

Результаты

enum и "char"

smallint и integer

varchar

json

Выводы

Публикации

Информация