Задать вопрос
Kwisatz
@Kwisatz
Больше web-приложений, хороших и разных

Как подружить анализатор PostgreSQL со своим составным типом?

Описан составной тип, для него же есть компараторы и операторы, описаны классы для btree и hash. Суть реализации не так важна, важен тот факт, что при запросе по таблице с 250к записей, предсказанное количество строк 0-170000, реальная оценка должна быть 0-70, а выбирается тестовым запросов вообще одна строка, что не проблема пока джоинов нет (скорость выборки, 0.1мс) но во всех остальных случаях порождает совершенно чудовищные планы запросов. Причем аналитика верная, гистограмма правильная (пересчет вручную дает те же результаты), MCV вроде в норме. Единственная на что упало подозрение, что у этого типа нет собственной функции аналитики, но ее можно написать только на C изза ограничений БД, пока до этой стадии не дошел.

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

Вопрос задаю из надежды если вдруг кто с опытом в таких вещах или с глубоким пониманием PostgreSQL Будет мимо проходить 8)
  • Вопрос задан
  • 1266 просмотров
Подписаться 2 Простой Комментировать
Решения вопроса 1
Kwisatz
@Kwisatz Автор вопроса
Больше web-приложений, хороших и разных
Сам спросил сам отвечу

Все дело было в том что оператору = необходимо было указать опции restrict и join.
Для этого надо внимательно читать не только
https://www.postgresql.org/docs/current/sql-create...
но так же
https://www.postgresql.org/docs/current/xoper-opti...

Прикладываю выдержку любезно выданную мне клодом.

RESTRICT = eqsel
RESTRICT — функция селективности для WHERE-условий
Логика работы eqsel:

Если значение есть в most_common_vals → берет частоту из most_common_freqs
Если нет статистики → использует формулу 1 / n_distinct
Для уникальных значений → возвращает очень малую селективность

JOIN = eqjoinsel
JOIN — функция селективности для JOIN-операций:
Анализирует статистику обеих таблиц
Оценивает количество совпадающих пар
Учитывает кардинальность (количество уникальных значений)

Так же приложу описание моего типа в полном объеме, возможно кому то понадобится.
Сделал я его потому, что для выборок стали делать текстовое generated поле которое объединяло 2.5 (на самом деле поля 3, но 2 из них это id/uid) через разделитель, чтобы искать через any($param::text[]) то есть по факту, если хранить id/uid разными полями, получалось 4 поля в каждой таблице и более сложные запросы. Сделав же составной тип и описав в php для него классы типы доктрины, все стало удобней и лаконичней и в бд и в коде.

create type entity_pointer as
(
    type_name varchar,
    id        int,
    uid       uuid
);

------------------------------------------------------------------------------
-- Функция для полного сравнения

create or replace function entity_pointer_cmp(entity_pointer, entity_pointer)
    returns integer as
$$
begin
    -- Обработка NULL значений
    if $1 is null and $2 is null then return 0; end if;
    if $1 is null then return -1; end if;
    if $2 is null then return 1; end if;

-- Сравнение через ROW конструктор
    return case
               when row (coalesce($1.type_name, ''), coalesce($1.id, 0), coalesce($1.uid, '00000000-0000-0000-0000-000000000000'::uuid)) <
                    row (coalesce($2.type_name, ''), coalesce($2.id, 0), coalesce($2.uid, '00000000-0000-0000-0000-000000000000'::uuid))
                   then -1
               when row (coalesce($1.type_name, ''), coalesce($1.id, 0), coalesce($1.uid, '00000000-0000-0000-0000-000000000000'::uuid)) >
                    row (coalesce($2.type_name, ''), coalesce($2.id, 0), coalesce($2.uid, '00000000-0000-0000-0000-000000000000'::uuid))
                   then 1
               else 0
        end;
end;
$$ language plpgsql immutable;

------------------------------------------------------------------------------
-- хэш функция для соответствующего индекса

create or replace function entity_pointer_hash(entity_pointer)
    returns integer as
$$
begin
    -- комбинируем значения хешей всех полей
    return coalesce(hashtext($1.type_name), 0) #
           coalesce(hashint4($1.id), 1) #
           coalesce(hashtext($1.uid::text), 2);
end;
$$ language plpgsql immutable
                    strict;

------------------------------------------------------------------------------
-- Функции сравнения (нужны потому что для операторского класса функции должны возвращать bool)

create or replace function entity_pointer_equal(entity_pointer, entity_pointer)
    returns boolean as
$$
begin
    return entity_pointer_cmp($1, $2) = 0;
end;
$$ language plpgsql immutable;


create or replace function entity_pointer_ne(entity_pointer, entity_pointer)
    returns boolean as
$$
begin
    return entity_pointer_cmp($1, $2) <> 0;
end;
$$ language plpgsql immutable;


-- Функции для других операторов сравнения (необходимы для B-tree)

create or replace function entity_pointer_lt(entity_pointer, entity_pointer)
    returns boolean as
$$
begin
    return entity_pointer_cmp($1, $2) < 0;
end;
$$ language plpgsql immutable;

create or replace function entity_pointer_le(entity_pointer, entity_pointer)
    returns boolean as
$$
begin
    return entity_pointer_cmp($1, $2) <= 0;
end;
$$ language plpgsql immutable;

create or replace function entity_pointer_gt(entity_pointer, entity_pointer)
    returns boolean as
$$
begin
    return entity_pointer_cmp($1, $2) > 0;
end;
$$ language plpgsql immutable;

create or replace function entity_pointer_ge(entity_pointer, entity_pointer)
    returns boolean as
$$
begin
    return entity_pointer_cmp($1, $2) >= 0;
end;
$$ language plpgsql immutable;

------------------------------------------------------------------------------
-- Создание операторов

create operator = (
    leftarg = entity_pointer,
    rightarg = entity_pointer,
    function = entity_pointer_equal,
    commutator = =,
    negator = <>,
    restrict = eqsel,
    join = eqjoinsel,
    hashes,
    merges
    );

create operator <> (
    leftarg = entity_pointer,
    rightarg = entity_pointer,
    function = entity_pointer_ne,
    commutator = <>,
    negator = =,
    restrict = neqsel,
    join = neqjoinsel
    );

create operator < (
    leftarg = entity_pointer,
    rightarg = entity_pointer,
    function = entity_pointer_lt,
    commutator = >,
    negator = >=,
    restrict = scalarltsel,
    join = scalarltjoinsel
    );

create operator <= (
    leftarg = entity_pointer,
    rightarg = entity_pointer,
    function = entity_pointer_le,
    commutator = >=,
    negator = >,
    restrict = scalarltsel,
    join = scalarltjoinsel
    );

create operator > (
    leftarg = entity_pointer,
    rightarg = entity_pointer,
    function = entity_pointer_gt,
    commutator = <,
    negator = <=,
    restrict = scalargtsel,
    join = scalargtjoinsel
    );

create operator >= (
    leftarg = entity_pointer,
    rightarg = entity_pointer,
    function = entity_pointer_ge,
    commutator = <=,
    negator = <,
    restrict = scalargtsel,
    join = scalargtjoinsel
    );

------------------------------------------------------------------------------

create operator class btree_entity_pointer_ops
    default for type entity_pointer using btree as
    operator 1 < ,
    operator 2 <= ,
    operator 3 = ,
    operator 4 >= ,
    operator 5 > ,
    function 1 entity_pointer_cmp(entity_pointer, entity_pointer);

create operator class hash_entity_pointer_ops
    default for type entity_pointer using hash as
    operator 1 = ,
    function 1 entity_pointer_hash(entity_pointer);


PS сложность средний, ага, смешная шутка...
PSPS кстати, клод еще упоминает likesel но его я вообще в документации не нашел
Ответ написан
Пригласить эксперта
Ваш ответ на вопрос

Войдите, чтобы написать ответ

Похожие вопросы