Привет Подскажите в какую сторону копать. Есть две таблицы: с данными

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

Нужно вытащить из справочника все недостающие значения, которые прикреплены к определенной ссылке и саму эту ссылку пробовал через left join, но тогда нуллы проставляются вместо ссылки

Написал такой рабочий скрипт, но он очень долгий. Как его можно ускорить?

select
items.authority_id,
types.code
from dict.land_project_authority_item_types types,
data.land_project_authority_items items
where code not in (
select type from data.land_project_authority_items sub
where sub.authority_id = items.authority_id
) and types.parent_item_type is null
group by items.authority_id, types.code;

https://t.me/pgsql/303899

PostgreSQL 10.17 on x86_64-redhat-linux-gnu, compiled by gcc (GCC) 8.3.1 20191121 (Red Hat 8.3.1-5), 64-bit Group (cost=165160732.79..165161461.04 rows=15428 width=48) " Group Key: items.authority_id, types.code" -> Sort (cost=165160732.79..165160975.54 rows=97100 width=48) " Sort Key: items.authority_id, types.code" -> Nested Loop (cost=0.00..165150938.72 rows=97100 width=48) Join Filter: (NOT (SubPlan 1)) -> Seq Scan on land_project_authority_items items (cost=0.00..1675.21 rows=10221 width=16) -> Materialize (cost=0.00..6.47 rows=19 width=32) -> Seq Scan on land_project_authority_item_types types (cost=0.00..6.38 rows=19 width=32) Filter: (parent_item_type IS NULL) SubPlan 1 -> Seq Scan on land_project_authority_items sub (cost=0.00..1700.76 rows=13 width=28) Filter: (authority_id = items.authority_id)

/d - это DDL?

Не очень понял, зачем cross join и подзапрос в where вместо нормального inner join по authority_id?

чтоб исключить из словаря type, который уже есть в data.land_project_authority_items

Да. Только \d -- команда в psql такая. И да, explain надо было именно с (analyze, buffers). Можно ещё чего-нибудь туда добавить -- например, verbose и timings, но не стоило удалять если запрос вообще завершается за какое-то время.

не, не выполняется =( ни вместе, ни по отдельности

> Seq Scan on land_project_authority_items sub (cost=0.00..1700.76 rows=13 width=28) Filter: (authority_id Что-то, впрочем, сказать ужэ можно. У вас нет индэкса на land_project_authority_items.authority_id ! Правда, почему он не сделал hash join я непонимаю. Возможно, дело в подзапросе -- кажэтся, сейчас postgres ужэ такое разворачивает, 5 лет назад, возможно, этого ещё не было. В общем, на самом деле, в любом случае лучшэ вставить индэкс и не париться.

А, слушайте, у вас критэрия соединения для items нет, это тупо безсмысленный запрос. UPD Не, увидел внутри подзапроса, да.

да, я заметил. Что запрос в частном случае правильно сработал а в целом нет Индекс помог. Большое спасибо!

А как тогда вычесть результат один из другого при этом сохранить authority_id?

Думаю, запрос через join будет такой: ``` SELECT items.authority_id, types.code FROM dict.land_project_authority_item_types types INNER JOIN data.land_project_authority_items items ON true LEFT JOIN data.land_project_authority_items sub ON sub.authority_id=items.authority_id AND types.code=sub.type WHERE sub.authority_id IS NULL and types.parent_item_type is null GROUP BY items.authority_id, types.code; ``` Но это всё тэстировать надо. Вообще, странная какая-то структура, я как-то так и не понял -- что имелось в виду и зачем такие данные.

Я так и не понял -- что вы хотите. Какие таблицы с данными, кто из них справочник, почему вы описываете три таблицы, а в запросе -- две.

Третья не фигурирует на нее есть только ссылка Нужно получается выбрать все коды, которых нет в data.land_project_authority_items принадлежащих определенному authority_id Укороченный ддл create table dict.land_project_authority_item_types ( code varchar(100) not null primary key unique, parent_item_type text ); create table data.land_project_authority_items ( id uuid not null primary key, authority_id uuid not null constraint lpai_authority_id_fkey -- тут один ко многим references data.land_project_authorities on delete cascade, type varchar(100) constraint lp_authority_items_lp_authority_item_types_fkey references dict.land_project_authority_item_types, );

>Укороченный ддл Не очень полезен, и дажэ вреден. Именно укороченностью. > нет в data.land_project_authority_items принадлежащих определенному authority_id Кем определённому authority_id ?

А, и в items нет вообще поля code, так что непонятно -- каких кодов там не должно быть. Точнее, там и так никаких нет.

айтему, есть айтемы определенных типов. В справочник добавились новые типы, сейчас нужно создать недостающие типы для authority. Тип authority не особо важно. Инсертить решил что проще всего при помощи селекта code в dict == type в дата. Не я проектировал, поэтому умываю руки =)

Всё тожэ самое, и с начала. Поскольку куда вставлять кусок фразы "айтему, есть айтемы определённых типов" я не понял.

кмк это уже пора в закреп :)

Кмк не поможэт ни разу.

постараюсь с начала =) Не судите строго dict.land_project_authority_item_types - таблица справочник с типами айтемов Айтемы лежат в таблице data.land_project_authority_items и ссылаются на сущность authority, с которой они связаны многие к одному, т е у authority может быть много items количество items у authority должно строго совпадать с количеством типов в dict.land_project_authority_item_types Добавились новые типы в dict.land_project_authority_item_types и задача в следующем: нужно добавить items с новыми типами.

>количество items у authority должно строго совпадать с количеством типов в dict.land_project_authority_item_types Всего? То есть в types у вас там 12345 записей, и для каждой authority_id из items должно быть ровно 12345 записей? ЗЫ Сейчас уезжаю, отвечу поздно.

не, там небольшое количество около 50 типов. Добавилось 10 их и нужно добавить

Ладно, зайдём с другой стороны. По каким полям таблица items соотносится с таблицэй types?

items.type = types.code

Зачем ты тогда что-то рассказываешь про authority_id ?

это ведь признак по которому могу определить недостающие типы айтемов или можно как то иначе понять недостающие айтемы authority?

С хера они недостающие, если в types они вообще никак не упомянуты и с types никак не связаны?

они нет, authority_items связяаны.

Начинай описывать эту связь. Я её непонимаю, и, пока что, нахожу невозможной.

Словами описывать. Я во-первых обычно не смотрю картинки, а во-вторых — дажэ не знаю, на каком языке у тебя это описано.

думал, что так наглядней. Внутри авторити есть айтемы, в базе у айтемов есть поле авторити_ид. У авторити много айтемов, у айтема только один авторити. По ссылке на авторити я понимаю сколько у этого авторити есть айтемов, у этих айтемов вытаскиваю их типы. Эти типы нужно чтоб вытащить из справочника с типами

Причём тут types?