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


    Пролог


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


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

    ДомКлик
    Место силы

    Комментарии 50

      +1

      Имхо, достаточно было сделать запрос вроде,


      SELECT
          pg_column_size(row(0::float, 'a'::"char")),
          pg_column_size(row(0::float, 0::smallint)),
          pg_column_size(row(0::float, 0::oid))

      чтобы предвосхитить отсутствие существенного отличия в этом синтетическом тесте. А вот в реальных условиях, когда в таблице не пара полей, а гораздо больше и их количество с перечислениями равно как и других оптимизаций тоже, то картина уже совсем другая. Представьте, что у вас помимо пола есть ещё рабочие дни недели, где "char"[] vs oid[]. И уже былая околонулевая разница переваливает за 20%. Тут всё может иметь значение и уж тем более выравнивание:


      SELECT
          pg_column_size(row(0::int2, 0::int8, 0::int2, 0::int8)), -- 56
          pg_column_size(row(0::int8, 0::int8, 0::int2, 0::int2)) -- 44
        0

        Тогда бы не было интриги. :) А как же интриги и расследования?


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


        В общем спасибо за ценный комментарий прекрасно дополняющий эту статью.

          0

          даже если бы размер поля отличался, postgresql же не колоночная БД, случайный доступ всё нивелирует (практически нет разницы прочитать 100500 раз по 1 байту или 100500 раз по 4 байта)

            +1

            Разница все равно есть. Конечно, скорее всего можно пренебречь процессорным временем, сравнивается ли в регистрах 1 байт или 4 байта. Но меньше размер — меньше размер таблицы и индекса -> они лучше влазят во все уровни кэшей, их больше попадает в различные write ahead в различные кэши и т.д. Это может быть таблица читается случайным образом, но индекс то нет. Да и таблица может искаться последовательным чтением. И на это я тоже тестировал.


            Согласен с тем, что разница, скорее всего, будет небольшая. Это можно увидеть на примерах с enum и varchar. Данные в varchar тоже в несколько раз больше 4 байт в enum, точно так же как 4 байтный enum больше 1 байтных данных. Но это стало понятно только после того как я задался этим вопросом и померил эмпирический. Тогда то и стало понятно точно, какой выигрыш есть и на сколько. Больше всего разница как раз в поиске последовательном чтении, при поиске по индексу (1% выборка) она небольшая, примерно 3%, но она все же есть. А теоретический рассуждать за и против, приводить доводы одни и другие можно было бы долго.

              0
              Это может быть таблица читается случайным образом, но индекс то нет.

              ну вообще-то и индексы обычно читаются совсем не последовательно.


              Да и таблица может искаться последовательным чтением.

              может. но изменение размера одного поля на несколько байт не особо повлияет на размер таблицы.

                0
                ну вообще-то и индексы обычно читаются совсем не последовательно.

                Не совсем так. Мы же говоря абстрактно про индекс имеем в виду btree, ведь так? Движение по дереву да, непоследовательно. Но потом же в конце концов попадаем в листовые страницы, а там уже последовательное чтение. :) Подробнее можете почитать здесь:
                https://habr.com/ru/company/postgrespro/blog/330544/


                может. но изменение размера одного поля на несколько байт не особо повлияет на размер таблицы.

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


                И так, sex1 это таблица enum с 4 байтными oid, sex5 таблица с varchar. Cлово "мужчина" (как наиболее часто встречаемое) 7 букв в UTF-8, т.е. 14 байт. Ну а в реальности:
                => select pg_column_size(sex) from sex1 limit 1;
                pg_column_size
                4
                (1 row)
                => select pg_column_size(sex),sex from sex5 limit 1;
                pg_column_size | sex
                15 | мужчина
                (1 row)
                т.е. разница почти в 4 раза, столько же, сколько разница между enum и гипотетическим 1 байтовым типом данных. Поэтому можно использовать разницу между sex1 и sex5 для того, чтобы спрогнозировать выигрыш от использования 1 байтных данных. Хотя, конечно, зависимость не будет пропорциональной, но качественно можно.


                Размер таблицы и индекса можете посмотреть в статье. Индексы почти не отличаются, таблицы по размерам отличаются более заметно. Но размеры таблиц я упомянул только в теоретических рассуждениях. Интересно же было время выполнения запроса. При поиске последовательном чтением разница заметна, не в разы, но визуально в десятки процентов. Кстати, только заметил, при последовательном чтении даже очевидно, что "char" немного быстрее, чем enum, хотя размеры у них одинаковые, причем разница очевидна даже с учетом погрешности измерений (высота полосок). В случае поиска по индексу разница не столь существенна, но все равно она есть и её можно оценить.


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

          0

          Хотя нет, зря я не проверил.
          => SELECT
          -> pg_column_size(row(0::float, 'a'::"char")),
          -> pg_column_size(row(0::float, 0::smallint)),
          -> pg_column_size(row(0::float, 0::oid));
          pg_column_size | pg_column_size | pg_column_size
          ----------------+----------------+----------------
          33 | 34 | 36
          (1 row)
          Все таки так разница есть, на столько байт, сколько и должно было бы быть. А вот размеры таблиц точно равны.

            0

            Я пытался сказать, что даже без "tuple alignment" разница в размерах кортежа очень мала и такой тест в принципе теряет смысл. А с ним таблицы вообще одинаковы. Индексы не стоило сравнивать в принципе, при такой дискретности значений это не имеет никакого смысла.


            И моё опасение в том, что стоит только добавить ещё одно enum/"char"/smallint-поле (то есть сделать таблицу более приближённой к реальности) и картина тут же поменяется. А значит ваши выводы работают только для синтетической ситуации, когда в таблице только один enum и не других коротких полей и тем более других enum. Вы и без того проделали хорошую работу, я думаю имеет смысл её расширить. Пусть таблица состоит не только из пола, но скажем возраста и страны происхождения. Картина довольно типичная, но в случае с 2"char" + smallint мы остаёмся в пределах 4 байт, а 2enum + smallint — это уже 12 байт (в обоих случаях учитываем tuple alignment).

              0
              Я пытался сказать, что даже без "tuple alignment" разница в размерах кортежа очень мала и такой тест в принципе теряет смысл.

              Нет, не теряет, тем более в принципе. Именно благодаря тесту стало очевидно и что размеры таблиц одинаковы и что разница во времени выполнения запросов очень мала. Но больше всего меня удивило, что не смотря на все сказанное, при последовательном чтении таблиц разница между запросом по enum и "char" оказалось достаточно большой, чтобы превысить погрешность измерения. Так вас послушать, то и экспериментальная физика в принципе теряет всякий смысл. Зачем? Ведь теоретики все уже придумали. :) В общем если вы не видите смысл, это не значит что смысла нет, это значит что вы его не видите. :) Да и про индексы не понял, да значений всего три, но ведь распределение у этих значений не равновероятное, а 75%, 24%, 1%.


              И моё опасение в том, что стоит только добавить ещё одно enum/"char"/smallint-поле (то есть сделать таблицу более приближённой к реальности) и картина тут же поменяется.

              Да не сильно. Ведь поиск будет по индексу, а в индексе скорее всего будет только один enum. Не, конечно можно придумать всякие синтетические случаи, но думаю, это не интересно.


              Пусть таблица состоит не только из пола, но скажем возраста и страны происхождения.
              Категорический против. :) Пол в качестве enum это еще куда ни шло. А вот возраст… ни в какие ворота. Обычно возраст это дата. Но и то не всегда. :) У иностранцев в паспортах вполне может указан только год рождения. Или год и месяц. Потому что арабы или еще кто кочевал в это время по пустыни и не знали точную дату рождения. :) И как оформлять в таком случае дату рождения в БД это отдельный гемор, тоже знаю как минимум два способа. Все же такое, говорят, встречается (или встречалось?) только у пожилых, у молодые как правило свою точную дату рождения знают. Но возраст как enum??? Как это?

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


              Но суть вашего предложения я понял. Что будет, если в таблице будет несколько enum?


              => create table multisex1 (id float, sex1 sex_enum not null, sex2 sex_enum not null, sex3 sex_enum not null);
              CREATE TABLE
              => create table multisex2 (id float, sex_char1 "char" not null, sex_char2 "char" not null, sex_char3 "char" not null);
              CREATE TABLE
              => insert into multisex1 (id,sex1,sex2,sex3) select id,sex,sex,sex from sex1;
              INSERT 0 10000000
              => insert into multisex2 (id,sex_char1,sex_char2,sex_char3) select id,sex_char,sex_char,sex_char from sex2;
              INSERT 0 10000000
              => vacuum full freeze analyze multisex1, multisex2;
              VACUUM
              \dt+
              => \dt+
                                          List of relations
               Schema |   Name    | Type  | Owner | Persistence |  Size  | Description
              --------+-----------+-------+-------+-------------+--------+-------------
               public | multisex1 | table | olleg | permanent   | 498 MB |
               public | multisex2 | table | olleg | permanent   | 422 MB |
               public | sex1      | table | olleg | permanent   | 422 MB |
               public | sex2      | table | olleg | permanent   | 422 MB |
               public | sex5      | table | olleg | permanent   | 498 MB |

              Да, размер таблицы стал различаться, а в случае тремя enum (multisex1) таблица ровно такая же, как… таблица с одним varchar (sex5). А в случае с тремя "char" размер таблицы не изменился вовсе. Т.е. выравнивание там скорее всего не по столбцам, а по строкам. Мне кажется про то как размер небольших полей, которых в таблице несколько, влияет на размеры таблиц и на производительность запросов, на эту тему можно писать другую статью. :)


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

                0
                Нет, не теряет, тем более в принципе. Именно благодаря тесту стало очевидно и что размеры таблиц одинаковы и что разница во времени выполнения запросов очень мала. Но больше всего меня удивило, что не смотря на все сказанное, при последовательном чтении таблиц разница между запросом по enum и "char" оказалось достаточно большой, чтобы превысить погрешность измерения. Так вас послушать, то и экспериментальная физика в принципе теряет всякий смысл. Зачем? Ведь теоретики все уже придумали. :) В общем если вы не видите смысл, это не значит что смысла нет, это значит что вы его не видите. :)

                Вы меня читали? Я всеми руками поддержал эксперименты, только предложил перейти от идеальных тел (таблицы с одним полем), если продолжать аналогию физикой, к реальным ;) Если вы так сведущи в экспериментальных науках, то должны понимать, насколько скрупулёзно они относятся к условиям исследования. И ваше игнорирование выравнивания, которое по сути выравнивает буфера, IO операции и т.п., совершенно бессмысленно. Вы взяли миллиметровую линейку, пытаетесь ею проводить субмиллиметровые измерения и заявляете, что с вашей точки все атомарные тела одинакового размера :) И при этом всю дорогу удивляетесь:


                Видимо, это как-то связанно с выравниванием данных PostgreSQL по границам «слов».
                Возможно, это связано с тем, что PostgreSQL предпочитает выравнивать данные по размерам «слов».

                Вы прикиньте — да, связано))


                Да и про индексы не понял, да значений всего три, но ведь распределение у этих значений не равновероятное, а 75%, 24%, 1%.

                Распределение здесь не причём. Если вы вооружитесь bt_page_items и get_raw_page, то увидите, что на одно значение enum приходятся за сотню физических указателей. То есть на одной странице индекса у вас всего раз 7 встретится ваш "female", занимая эдак байт 28, а все остальные >8K будут забиты tid'ами соответствующих записей. То есть колебание размера типа привело бы к колебанию размера индекса на жалкие доли процента.


                Да не сильно. Ведь поиск будет по индексу, а в индексе скорее всего будет только один enum. Не, конечно можно придумать всякие синтетические случаи, но думаю, это не интересно.

                По индексу мы находим только tid (у нас же не include-индекс) и с этим tid'ом вы идёте к реальной строке в таблицу. Вот вытащили 24%*10M tid'ов из индекса, как вы того хотели, и пошли с ними в страницы с данными. А в зависимости от размера кортежа они могут быть на 1000 страницах, а могут быть на 1200. Вот вам дополнительных 200 IOPS. Когда в вашем однопользовательском тесте всё болтается в shared buffers вы этого не замечаете, а в проде… вы это либо уже понимаете, либо пока не поймёте.


                Пол в качестве enum это еще куда ни шло. А вот возраст… ни в какие ворота. Обычно возраст это дата. Но и то не всегда. :) У иностранцев в паспортах вполне может указан только год рождения.

                По тексту упоминался smallint, в итогом счёте брался как smallint. Откуда enum? Вы совсем не читаете оппонента? Уже предвосхищаю критику, что до smallint люди не доживают :) Это раз.


                Во-вторых, да какая разница? Пусть будет год рождения. Вы правда сути не видите или вам на критику надо обязательно ответить контр-критикой, чтобы не так обидно было? ;) Никогда не видели в таблицах полей с выравниванием != 4 байт? А они есть.


                Т.е. да, бизнес логика может быть завязана на страну. Но я бы это реализовал через структуру связанных таблиц.

                Так enum это и есть связанная таблица. Вы сами меня в это убедили))


                Но суть вашего предложения я понял.

                Если поняли, то зачем тратили своё и моё время на всю эту ерунду выше придираясь к каждой мелочи?)


                Что будет, если в таблице будет несколько enum?

                Да поставьте год рождения (int2). Могут быть любые "select * from pg_type where typlen % 4 > 0 and typalign in ('c', 's')", я приводил примеры и массивами, и со smallint и в первом же сообщении показывал как даже порядок следования исключительно числовых полей может кардинально повлиять на размеры.


                Т.е. выравнивание там скорее всего не по столбцам, а по строкам.

                Не "скорее всего" и не "или то, или это", а выравнивание как по столбцам (type alignment), так и по строкам (tuple alignment).


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

                Тогда я вообще не понимаю ваш тест. Создали условия когда и enum и char выравниваются до одинаковых размеров, сравнили одинаковые размеры, из их равенства сделали вывод что enum и char эквивалентны. Если считаете, что в статью нечего добавить — я не буду настаивать. Просто хотел помочь немного с вашим контентом и вашими скиллами))

                  +1

                  Даже не знаю, какого признания вы хотите от меня добиться. :) Я признаюсь, что я разработчик на PostgreSQL, а не разработчик PostgreSQL, а это, как вы, наверное, догадываетесь, две большие разницы. До эксперимента я не знал, что PostgreSQL выравнивает строки по машинным словам. Никогда не нужно было, да и в документации для разработчиков на PostgreSQL об этом не упоминается. Узнал я об этом только в результате эксперимента, что было для меня маленьким открытием. И это знание мне показалось полезным и интересным. И не только это, я думаю многие мои маленькие "открытия" разработчики PostgreSQL могли бы предугадать, зная всю эту кухню изнутри.


                  Более того, статья адресована даже не разработчикам на PostgreSQL, а в первую очередь веб разработчикам, типа тех, что в качестве enum используют внешнюю таблицу с ключом serial (и нулевым шагом у sequence) или создают таблицы из двух полей: bigserial primary key и jsonb, куда упихивают абсолютно все данные в денормализованном виде, а потом удивляются результам.


                  А по поводу ваших пожеланий, то я вам могу ответить примерно теми же словами, что мне ответил Том Лейн: "Если думаете, что можете сделать лучше, сделайте." :)

                    0
                    Даже не знаю, какого признания вы хотите от меня добиться. :)

                    Несколько раз говорил:


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

                    Обычно авторы просто апдейтят статью и все в выигрыше. А а вы пустились в какую-то демагогию, начали изобретать сегрегацию разработчиков. Странно, но дело хозяйское. Перефразиуря Лейна: "Не хотите делать лучше, не делайте" :)

                      0

                      Апдейтить на что? Я не вижу в этом смысла. Более того, я так и не понял, как именно вы хотите "улучшить". Таблицу с несколькими enum я еще представить себе могу, но вот запрос по всем сразу с общим индексом… Это настолько редкая и специфичная тема, что не вижу смысла придумывать какие-то общеполезные тесты, потому что там их попросту нет. Если вы сами понимаете, чего вы хотите — сделайте. Для вас адаптировать мои скрипты (или написать свои еще лучше) это плевое дело. Даже не понимаю, почему вы пускаетесь в демагогию. Кода бы пришлось напечатать гораздо меньше, чем букв, что вы уже напечатали. Или вы только умничать умеете?


                      Что значит "изобретать сегрегацию разработчиков"? Вы даже этого не понимаете? Разработчики на PostgreSQL пишут на SQL и plpgsql, разработчики PostgreSQL пишут на С. Первые работают с внешним интерфейсом БД, вторые лезут во внутренности. Это совершенно разные люди, с разными навыками и знаниями.


                      То есть на одной странице индекса у вас всего раз 7 встретится ваш "female", занимая эдак байт 28, а все остальные >8K будут забиты tid'ами соответствующих записей. То есть колебание размера типа привело бы к колебанию размера индекса на жалкие доли процента.

                      Да, это объясняет.


                      Так enum это и есть связанная таблица. Вы сами меня в это убедили))

                      Есть концептуальная разница. enum редактируется с помощью DDL, внешняя таблица с помощью DML. DDL это прерогатива программиста (ну или другими словами database owner), обычный юзер работает с DML и DDL используется только для временных объектов. На планете около 200 стран, причем ситуация постоянно меняется. Страны разделяются, сливаются, происходят революции и переименовываются. А еще возникают непризнанные признанные Россией республики, с которыми у нас особые отношения в том числе по правилам пересечения границы и законодательству. И сейчас наблюдаем как одна из непризнанных республик может исчезнуть. Что программистам каждый раз переписывать код в случае изменения политических раскладов? Поэтому список стран лучше сделать в виде внешней таблицы, а не enum. Чтобы сами пользователи могли его редактировать.


                      Вы лучше вот что расскажите, раз вы так хорошо знаете внутренности PostgreSQL. Правда интересно. Почему запросы с join (nested loops) настолько заметно медленнее, чем запросы с подзапросом? Планы же у них одинаковые. И почему хэш индекс в PostgreSQL настолько трешовый? А переписать его можете? Ведь правда, очень позорно выглядит.

          –4
          Речь здесь не только про извращенцев вроде Кончиты Вурст

          А вот пол «other» — это совершенно точно известный факт, что человек чувствует и записывает в документах, что он «особенный»

          CREATE TYPE sex AS ENUM ('мужчина', 'женщина', 'иное');

          Не иное, а другой
          Иное звучит как оскорбление.

          Откуда столько неприязни к людям с другим набором хромосом?
          На хабре все же цивилизованное общество
            0

            Ну, во-первых, там совершенно очевидно, что по правилам русского языка будет средний пол (называть таких людей в мужском роде это жуткий сексизм), во-вторых у всех людей другой набор хромосом, чем у меня, а в-третьих "иное" и "другое" это синонимы, но "другое" это как-то не по-русский. Ничего не имею против людей с мозаицизмом по половым хромосомам, но вот любовь к Кончите Вурст не является признаком цивилизованности. Тут вы ошибаетесь.

              –1
              Ну вот вы в графе пол допустили три значения, а в графе отношения почему-то только любовь и неприязнь.
                0

                Нету там графы "отношения", статья не про это.


                Не волнуйтесь, к извращенцам я совершенно равнодушен. Кроме тех случаев, когда они пытаются меня соблазнить, а такое, к сожалению, бывает. И тех случаев когда они к технической статье пишут комментарии об их тяжелой судьбе, мол они все такие из себя "цивилизованные", а никто этого не ценит. Вот я, например, не пишу к каждой технической статье комменты, что я обычный мужчина и предпочитаю женщин. Почему вам надо обязательно громко заявить, что вы предпочитаете использовать задний проход не по назначению или вообще себе яйца отрезать? Писать об этом в интернете, ходить на "парады" и т.д. Если по каким-то причинам не хотите брать пример с нормальных людей, так берите пример людей с другими отклонениями: зоофилов, некрофилов, копрофилов. Они никакие парады не устраивают и нигде о свои пристрастия не афишируют, просто уединяются где-то со своим объектом любви и тихо радуются жизни.

                  0
                  «Почему вам надо обязательно громко заявить…»
                  Нам? Вам кажется, что Вас в комментах уже окружают и соблазняют? Без паники, всё спокойно.
                    0

                    К счастью, в комментах ничего не кажется. Но озабоченных понабежало, это точно. Такое ощущение, что есть люди, которые постоянно мониторят хабр запуская запросы поисковые "sex" и "извращенцы".

                      +1
                      Я про Postgres почитать зашёл. Странно, что про него можно говорить такими словами.
                        0

                        Во-первых вы критикуете не статью про PostgreSQL, а какой-то столбец "любовь и неприязнь", которой в статье не было.
                        А во-вторых если вы "про него" имеете в виду Томаса Нойвирта (а такие слова были только про него), то про него такими словами говорить можно.
                        В-третих, судя по вашим комментам вы искали вовсе не PostgreSQL.

                          0
                          Столбец «любовь и неприязнь» не в статье, а в Вашем понимании, продемонстрированном в диалоге выше:
                          — Откуда столько неприязни к людям с другим набором хромосом? На хабре все же цивилизованное общество.
                          — …Любовь к Кончите Вурст не является признаком цивилизованности.
                          Из чего следует, что в столбце отношения к людям у Вас присутствует только неприязнь и любовь, никаких перечислений. Этот факт составил забавный контраст теме статьи.
                            0

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

                              0
                              А про Вурст, стало быть, не оффтопик был?
                                0

                                Нет, конкретный пример, кто может использовать пол other указанный в стандарте ИКАО. В контексте, почему не надо использовать boolean для пола, что чрезвычайно широко распространено.

                                  0
                                  Не юлите, это был не только пример, но и оценочное суждение.
                                    0

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


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


                                    https://youtu.be/bYraAP4UbF4


                                    Не весь, но первые минута пять секунд там как раз про это.
                                    На этом разговор закончен.

                                      0
                                      Не волнуйтесь, я не переживаю. Я просто удивился, что кому-то потребовалось втащить в технический вопрос свои личные антипатии.
                                        0

                                        А при чем тут антипатии? :D Это вот это уже смешно. До сих пор в техническом вопросе антипатии проявляли только вы: "это я люблю, это я не люблю". Вот если бы я вас назвал извращенцем, не имея на то оснований, только подозрения, вот это да, было бы проявлений эмоций или антипатий. Потому что было бы оскорбление. А в контексте Кончиты Вурст это не было ни оскорблением, ни проявлением каких-либо эмоций. Это было техническим термином. Как бы вам объяснить. Вот если бы назвать мужчину женщиной, это да, оскорбление. А называть мужчину мужчиной, женщину женщиной, извращенца извращенцем тут нет никаких оскорблений. Это объективная характеристика человека. И использовал я её для указания типа, к кому может применяться пол other, там как бы два случая, по медицинским показаниям и извращенцы, которые сами себя так представляют.

                                          0
                                          Использование давно устаревших терминов — это именно и есть личное предпочтение, а не объективность.
                                            0

                                            :D Это общепринятый термин.
                                            https://ru.wiktionary.org/wiki/извращенец

                                              0
                                              Вам помета «разг.» ни о чём не говорит? Конечно, она же так обычна для технических терминов!
                                                0

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

                                                  0
                                                  Вы смешиваете два разных понятия. Я не говорил, что это устаревшее слово. Я говорил, что это слово устарело в качестве термина. Для терминов в словарях свои пометы и это отнюдь не «разг.».
                                0
                                Не люблю перечисления. Во всех случаях, когда сталкивался с ними в Postgres, это было только лишнее неудобство в разработке. Модуль для ведения по общему образцу не слепишь, на схеме толком не изобразишь, столбцов с информацией не добавишь. Не гибкое решение, для каких-то нужд, которые исчерпывающе известны заранее, а это идеальная ситуация.
                                  0

                                  Такой тип данных как перечисление есть во многих языках программирования. Есть и у PostgreSQL. Да, остро заточенный инструмент с конкретным предназначением.


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

                                    0
                                    Я же изложил чисто рациональную аргументацию. Может, Вам забыть про Вурст уже?
                                      0

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

                                        0
                                        Аргументируйте.
              +1
              А почему прогрева pg_prewarm может быть недостаточно?
                0

                Не знаю, экспериментальный факт. :) После pg_prewarm видел, что запросы сначала выполняются несколько более медленно, но со временем "ускоряются". Уточню, это видел я еще на 11 PostgreSQL, не знаю как сейчас. Сейчас я просто "обновил" старый эксперимент на 13 PostgreSQL и выложил в хабр.


                Если так интересно, то можете сами все проверить.

                +2
                Наконец и тут появились статьи про sex — по меньшей мере 139 упоминаний :) PornHabr прям.
                  0

                  Вы нашли эту статью через поиск хабр по слову sex? :) Часто статьи на эту тему попадаются?

                    0
                    Вы нашли эту статью через поиск хабр по слову sex? :)

                    Нет:)

                    Часто статьи на эту тему попадаются?

                    Нет.
                  +1
                  Не могли не ляпнуть про «извращенцев», да? Стыдно, что на Хабре статьи с такой лексикой.
                    –3

                    А мне стыдно читать комменты, где вместо того чтобы обсуждать технические вопросы, пишут всякие подозрительные "цивилизованные" люди, которые обижаются на то, что я извращенцев называл извращенцами. Вот если бы я нормальных людей называл извращенцами, тогда бы да, это было бы оскорбление. А извращенцев называть извращенцами это констатация факта. Ну это все равно что женщину называть женственной или человека с диагнозом имбицил называть имбицилом. Ну или как еще можно охарактеризовать бородатого мужика, который надевает на себя женское платье и идет отлить в женский туалет?

                      +1
                      Не позорьтесь дальше
                        –3

                        Это просто смешно. :) Но позоритесь как раз вы. :) Не я же извращенец. А вы почему-то этот пример воспринимаете слишком лично. Поэтому я предлагаю вместо того чтобы обсуждать почему вас так сильно оскорбило то, что я Кончиту Вурст (точнее Томаса Нойвирта) называл извращенцем вернуться к PostgreSQL.

                    0
                    Затронута интересная тема, но мне кажется, она раскрыта немного не до конца.
                    Насколько я помню, в постгре есть сложности во воремя миграций енумов.
                    Например, если таблица использующая енум сожержит 300-400 миллионов записей, то добавление нового значения в енум приедет к пересчетку всех записей, что может минут на 10 залочить базу данных.

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

                      Может и не до конца. О такой проблеме впервые слышу. Теоретический такого быть не должно. Добавление нового значения enum это добавление одной новой строчки в служебную таблицу pg_enum. Откуда там может взяться такая блокировка? Откуда такая инфа?

                    Только полноправные пользователи могут оставлять комментарии. Войдите, пожалуйста.

                    Самое читаемое