Postgresql 12.6, таблица svjul 18 млн. записей, 100Гб, 3 колонки id (число, ключ), enddate (дата), data (jsonb). Колонка data содержит поля OGRN и DataVip. По одному ОГРН возможно несколько записей. Нужно написать SQL-скрипт, который установит enddate = '2022-12-31' записям, у которых DataVip не максимальна для её ОГРН. Время отработки скрипта должно быть минимально.

На данный момент скрипт выглядит так - время неудовлетворительное

create index if not exists idx_deduplicate_svjul_btree_ogrn on svjul using btree ((data ->> 'OGRN'));

update
        svjul
set
        enddate = '2023-10-05'
where

        (
                svjul.data ->> 'OGRN',
                svjul.data ->> 'DataVip'
        )

        <>

        (
                select distinct on (svjul1.data ->> 'OGRN')
                        svjul1.data ->> 'OGRN',
                        svjul1.data ->> 'DataVip'
                from
                        svjul as svjul1
                where
                        svjul1.data ->> 'OGRN' = svjul.data ->> 'OGRN'
                order by
                        svjul1.data ->> 'OGRN',
                        svjul1.data ->> 'DataVip' desc
        )
;


Ваши предложения?

Покажите EXPLAIN

Ещё нужны объём данных: количество записей и гигабайт, а также время: что значит "неудовлетворительное"?

Да, ещё надо понять частоту запуска этого скрипта. Зачем всё это?

"QUERY PLAN"
"Update on svjul  (cost=0.00..114458012.59 rows=50028 width=132)"
"  ->  Seq Scan on svjul  (cost=0.00..114458012.59 rows=50028 width=132)"
"        Filter: (SubPlan 1)"
"        SubPlan 1"
"          ->  Unique  (cost=1142.64..1143.89 rows=500 width=64)"
"                ->  Sort  (cost=1142.64..1143.89 rows=500 width=64)"
"                      Sort Key: ((svjul1.data ->> 'DataVip'::text)) DESC"
"                      ->  Bitmap Heap Scan on svjul svjul1  (cost=12.29..1120.23 rows=500 width=64)"
"                            Recheck Cond: ((data ->> 'OGRN'::text) = (svjul.data ->> 'OGRN'::text))"
"                            ->  Bitmap Index Scan on idx_deduplicate_svjul_btree_ogrn  (cost=0.00..12.17 rows=500 width=0)"
"                                  Index Cond: ((data ->> 'OGRN'::text) = (svjul.data ->> 'OGRN'::text))"

Что такое svjul ?

(3) Вроде как индекс-то срабатывает, вот только приходится искать дубли 18 млн. раз. Неужто ситуация патовая

(5) это таблица юридических лиц РФ в топике написал техническую составляющую

(6) При "Update" индексы десятое дело, они только тормозят обычно. Хотя в Вашем случае 18 млн строк для обновления — это нонсенс. Зачем столько обновлять?

(7) Вы глупости делаете.

Обновляйте только своих контрагентов, не трогайте остальные 18 млн юрлиц РФ

Вытащить дату в отдельное поле и не аться с этими json

(11) Придётся обновлять отдельное поле.

(8) На урезанной таблице из 100000 строк индексы помогли. Поиск без них 100% будет по полчаса для каждой из 18 млн. записей работать.

Зачем? Чтобы оставить незакрытыми только самые актуальные согласно дате выписки записи.

(9) Ваши предложения?

(0) А вот скажите - какой большой смысл хранить в json/xml? Еще и индексировать его поля. Чтобы что, быстрее его выплевывать по запросу?

(14) Это отдельная история и длинная. Не в этой ветке. Но так надо.

(15) Вам надо ворочать 18 млн записей, Вы и трахайтесь.

(12) это быстро. Относительно.

(10) Надо именно всю базу.

(15) заведи отдельные поля или общий хэш поиска, заполни их из json, добавь автозаполнение в призаписи() и убери индексы  json

(19) Это всё не поможет. Он обновляет данные каждый день по всем 18 млн записям.

(20) Слава богу НЕ каждый день. Разовая операция. Потом данные подливаем в БД по дню с ЕГРЮЛ. Только новые дедуплицировать

Уже потом

У вас там дурдом

(23) У нас тут МДМ - система, которая хранит и раздает по АПИ справочники РФ. ЕГРЮЛ самый проблемный. Скажите ещё спасибо, что нераспаковывающиеся архивы и битые ХМЛ перестали присылать.

(24) Спасибо.

(24) Java?

(26) Kotlin

В России 3+М ЮЛ и примерно столько же ИП. Предлагаю сначала создать вспомогательную таблицу с полями OGRN и DataVip. Далее по обстоятельствам, по крайней мере вместо двух сканов таблицы при апдейте будет один.

(28) А вот это кстати может сработать, временная таблица, с 2-мя полями весит уже не 100Гб, а где-то 500 Мб, что вполне влезет в ОЗУ. Если ещё проиндексировать поиск может значительно ускориться. Спасибо.

(29) тогда делай сначала с полями id, enddate, ogrn, datavip. Из нее ещё одну с ogrn, max(datavip). Апдейть первую через join по огрн с условием на даты, потом аналогично исходную через join по id с условием на дату.

Индексировать можно первую по id для второго join. Больше индексов не надо.

(30) Да, кстати ловчее всего будет в ВТ вытащить ИД-шники незакрываемых записей, а потом

update svjul set enddate='2022-12-31'
where not exists (select id from wtsvjul where wtsvjul.id = svjul.id)

Где wtsvjul - временная таблица.

(32) условия, отличные от "=" для множеств при апдейде плохо!
У тебя и в (0), и в (32) при апдейте используются операции с множествами в условии.
+ (30) Прочитай про секцию From тут https://postgrespro.ru/docs/postgresql/9.6/sql-update

(33) Я так и так попробую на урезанной таблице. Из каждого правила, я скажу, есть исключения. С JOIN как показывает практика далеко не так всё хорошо в Postgres и индекс с ним не всегда цепляется.

Ясно одно - проверки КАЖДОЙ из 18 миллионов записей никак не избежать. Вопрос лишь в том как её ускорить.

А мне ещё одна идея пришла. Проставить всем записям 2022-12-31, а потом только для тех что с нужными ID-шниками открыть. Вот это должно быть максимально шустро.

(35) В (0) каждый json разбирается дважды, да ещё для каждой строки идёт сравнение на неравенство с каждой столкой массива "актуальных данных (её и нужно индексировать)".
Я тебе предлагаю разобрать json'ы только 1 раз и вне транзакции апдейта.
Нюансы каждого шага зависят как от конкретного набора данных, так и от сервера. Тут я не подскажу.

(35) уверен, что затык в процессоре а не дисках? Хоть и будет две транзакции апдейта, но они у тебя будут идти последовательно и количество апдейтов будет существенно выше. Выйгрыш времени крайне сомнителен

(0) А данные получаете с сайта налог.ру? Просто если не жадничать и получать эти данные легально за плату, то там уже есть готовые файлы xml по ЕГРЮЛ как для полной базы на определённую дату, так и отдельные к ней обновления на каждый последующий день.

Если же сами написали приблуду по массовому скачиванию бесплатно по каждому отдельному ЕГРЮЛ, то это ловится.

Если же скачиваете с левого сайта, который уже вытащил базу и выложил её в открытый доступ, то не факт, что там данные свежие.

(38) Естественно в дисках проблема. Хранись хотя-бы правая часть условия отбора в ОЗУ в индексированном виде и используй его запрос по уму, таких тормозов бы не было. Даже плохенькое ОЗУ в сто раз быстрее любого жесткого диска.

(39) Да, выкачиваем зипованные ХМЛ-ки с официального источника ФНС. Затем загоняем в таблицу БД эти данные предварительно преобразовав эти данные в JSON. Ну а потом различными способами используем эти данные раздаем по REST и JMS.

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

(40) Ну коль установили клиентам базу по ЕГРЮЛ на какую-то дату (20 млн) и есть обновления строк на сто, то почему просто не подгружать обновления?

Коллеги, что-то тут нечисто )
Something is rotten in the state of Denmark...

(42) Просто есть уже нагруженная таблица с дублями. Ну и чтоб не перегружать заново решили попробовать её дедуплицировать. Я предлагал перегрузить, но это долго. Вот и решали иной выход искать.

И конкурсы интересные.

(44) Очень. В рамках 1С у меня возникал вопрос где мне могут потребоваться навыки 1С:Эксперт. Так вот за пределеами 1С возникает другой вопрос - где это может НЕ потребоваться.

(40) Создавай промежуточную таблицу с полями Id, enddate и порциями апдейть svjul.

(46) физическую

(46) Эффекта по сравнению с вложенным запросом практически нет - проверял.

create index if not exists idx_deduplicate_svjul_btree_anyfields on svjulv1 using btree (
        (right(data ->> 'OGRN', 4)::integer),
        (data ->> 'OGRN'),
        (data -> 'SvUchetNO' ->> 'DataPostUch') desc,
        (data ->> 'DataVip') desc,
        id desc
);

drop procedure if exists svjulv1_deduplicate;
create or replace procedure svjulv1_deduplicate(closedate date) as $$
        begin
                for i in 0..9999 loop
                        if exists(
                                                select
                                                        1
                                                from
                                                        svjulv1
                                                where
                                                        right(data ->> 'OGRN', 4)::integer = i
                                                        and enddate = closedate
                                         )
                        then
                                raise info 'success early %', i;
                                continue;
                        end if;

                        update
                                svjulv1
                        set
                                enddate = closedate
                        where
                                right(svjulv1.data ->> 'OGRN', 4)::integer = i
                                and svjulv1.id not in (
                                        select distinct on (right(svjulv11.data ->> 'OGRN', 4)::integer, svjulv11.data ->> 'OGRN')
                                                svjulv11.id
                                        from
                                                svjulv1 as svjulv11
                                        where
                                                right(svjulv11.data ->> 'OGRN', 4)::integer = i
                                        order by
                                                right(svjulv11.data ->> 'OGRN', 4)::integer,
                                                svjulv11.data ->> 'OGRN',
                                                svjulv11.data -> 'SvUchetNO' ->> 'DataPostUch' desc,
                                                svjulv11.data ->> 'DataVip' desc,
                                                svjulv11.id desc
                                );

                        commit;
                        raise info 'success now %', i;
                end loop;
        end;
$$ language plpgsql;

call svjulv1_deduplicate('2022-12-31');

Я пробовал в update вложенный подзапрос из where на временную таблицу переделать - что так, что так время одинаковое. Кстати update ниже у меня отрабатывал мухой. Но на формирование временной таблицы все равно уходило то же время, что и на вложенный запрос в where. В итоге получил ту же минуту на обработку порции ОГРН по right(data ->> 'OGRN', 4)::integer

update svjulv1
set enddate = closedate
where id not in (select id from tempsvjul)

+(47) Да, я про DataPostUch в 0 не сказал, дабы упростить задачу. Но в принципе его наличие/отсутствие не особо меняет дело

Короче говоря вот этот кусок самый тормозной

select distinct on (right(svjulv11.data ->> 'OGRN', 4)::integer, svjulv11.data ->> 'OGRN')
                                                svjulv11.id
                                        from
                                                svjulv1 as svjulv11
                                        where
                                                right(svjulv11.data ->> 'OGRN', 4)::integer = i
                                        order by
                                                right(svjulv11.data ->> 'OGRN', 4)::integer,
                                                svjulv11.data ->> 'OGRN',
                                                svjulv11.data -> 'SvUchetNO' ->> 'DataPostUch' desc,
                                                svjulv11.data ->> 'DataVip' desc,
                                                svjulv11.id desc

(50) возможно, если покрутить параметры планировщика, удастся ускорить

(15) Когда говорят "Так надо", смирись со временем выполнения, первое "Так надо", задает тон всему :)

(52) Да ты умеешь обрадовать)

(50) А зачем бить по 4 последним знакам ОГРН!?

(54) это чтобы на 10000 транзакций разбить все множество обрабатываемых объектов

(48) Временная таблица нужна что бы в JSON второй раз за ОГРН не лазить.

(55) Бей по интервалам ID

Задача прекрасно распараллеливается
Кто мешает запустить в нужное число потоков?
А еще в PGSQL есть кластеризация с репликацией - это если потоков на одном сервере маловато будет

(57) Не пойдет - надо в группе из ОГРН выбирать

(59) Зачем? В (0) ты в JSON лазил для каждой строки дважды, а в (50) уже 10000 раз при проверке условия отбора в транзакцию и 10001 при апдейте.

Автор, держи:
https://disk.yandex.ru/i/iCCKPIt61mTuVA
https://disk.yandex.ru/i/Uoi3cRZHX1nXdw

(61) Не, так не интересно. Мы тут за академический интерес к Postgre.

(61)  (62) Индекс по полям jsonb почему-то медленно сканируется. А вот индекс по колонкам шустрее. По ходу придется мне ход конем делать - сделать колонки с ОГРН и датой выписки, заполнить их через update перебирая порционно по 10000 все id. И только потом применять что-то наподобие (61).

Jsonb по ходу довольно серьезно замедляет все.

(63)
заполнить их через update перебирая порционно по 10000 все id

MS SQL сделает это за одну минуту. Максимум. Одной порцией.

(63) см (11)

(63) В (61) только один кластерный индекс по id. Другие индексы не нужны, если у нас затронуто 12 млн строк из 18 млн. Какие нафиг тут индексы?

(65) Хотя нет, я не в курсе, как быстро mssql извлечет данные из json.

(68) Тут беда не только в количестве записей, они ещё и очень жирные 10-30 килобайт каждая и идут в jsonb. Даже простейший update svjul set code = data ->> 'OGRN' будет целый день работать. Ну а если данные в колонки перетащить то всё бодрее становится значительно.

Это плата за отсутствие возни по раскладке сотен атрибутов егрюла в отдельные колонки.

(69) Обычно все это бинарное хранится отдельно от данные таблицы, в специально обученных страницах данных. Поэтому обновление твоей таблицы будет быстрым и незаметным.

(70) Это понятно, я уже писал выше предположение.

(71) [быстрым и незаметным] увы, практика показывает обратное

(72) Я вот не пойму на кой хрен постгрес лазиет по этим бинарникам,если вся нужная инфа для выборки есть в индексе

(73) Забудь про ОГРН при апдейте! В (48) ты уже заметил скорость обновления по id.

(74) В индексе ему нужны все поля в приемлемом виде, а ты везде в апдейтах ему ОГРН из JSON'а суешь в условие.

(75) Так ведь его придется хоть в колонку то вынуть. Пока он в jsonb работать невозможно. Потом уже норм будет по крайней мере в селекте.

(0) Автор, у Вас низкая самооценка и высокая ответственность. Не торопитесь принимать решения и не психуйте.
Возьмите паузу.

См. Выше, лень искать. Его и дату надо один раз дёрнуть из json.

(74) (73) Все, о чем я пишу - относится к MS SQL. Возможно в Postgree по другому.

Но общие принципы должны же соблюдаться.

(78) Благодарю за внимание. Всё под контролем, не переживайте.

(79) Это сейчас и делается скриптом. К утру вынесение данных из jsonb в колонки закончится. А на сегодня всё.

Как я слышал, постгрес внутре версионник, а для любых версионников update в разы более тяжелая операция, чем delete+insert.

Ветка в топе

Немного академического интереса https://habr.com/ru/companies/oleg-bunin/articles/597187/ и https://habr.com/ru/companies/oleg-bunin/articles/646987/
Потом автор не реагирует на распараллеливание, хотя и в предыдущей его ветке как ускорить запрос и в этой ему об этом говорили.

(85) к распараллеливанию скептично отношусь, ибо записывающая/читающая головка диска одна

(85) Какая то наркомания.

(86) Головка диска там не причем. Диск тебе прочитает под 300 Гб в сек. нужных данных. Причем именно нужных.
А потом поделка Postgree будет строить цепочки toast, и разжимать их. Операция неконкурентная, блокировок быть не должно, паралельность должна быть.

(85) (88) Хотите сказать, распараллеливание реально может в разы ускорить? Я просто не пробовал. А вы?

(86) Головка одна, но она пролетает много раз над одной дорожкой. Просто оставьте это дискам, а сами задайте нагрузку и смотрите.

(88) только извлечённые данные нужно куда-то складывать для дальнейшей обработки и (86) не забывать про поддержку транзакционной целостности.

(86) Умеренному скептицизму всегда может найтись место https://postgrespro.ru/docs/postgresql/15/when-can-parallel-query-be-used
Но попробовать же можно.
Пример из статьи https://habr.com/ru/articles/305662/ воспроизводится хорошо(с чего бы было плохо?).
Кстати, редакция СУБД(12.6) давно устарела. Там же все непрерывно меняется, улучшается и совершенствуется.

Вот select distinct исследовали https://www.percona.com/blog/introducing-postgresql-15-working-with-distinct/
В PG15 гораздо лучше выглядит, даже parallel distinct может быть.

Неясна суть программно-аппаратного окружения, в котором это все пытаются ускорить. Настройки СУБД, лежит ли вся база в shared buffers,  и прочее потребление ресурсов памяти.
Как-то слишком трогательно смотрится забота о всего одной головке диска..

(77) Пока он в jsonb работать невозможно...ага, а на операциях а на реляц.операциях с полями,  jsonb работает еще и меделеннее чем json

(93) Если distinct начнет херовничать я просто заменю его на курсор, который будет перебирать упорядоченный результат запроса и каждый раз когда ОГРН будет отличаться от предыдущего складывать id в специальную таблицу.

К сожалению пока что нам не дадут 15 поставить по той простой причине, что на нем ПО не тестировалось.

(96) чем тебе не угодил  max()   group by?

(97) ну на самом деле полей несколько и не так с этим просто... хотя возможно

А можно ли в postgresql каким-то образом передавать функции в качестве параметров других функций без преобразования вызова в строки?

(99) используй  PL/Python

В итоге удалось мне добиться заполнения поля enddate за 2 часа. Скрипт из 0 работал бы неделю минимум. Скрипт теперь вот такой.

-- 0 minute
drop table if exists tempsvjulv11;
create table tempsvjulv11 (id bigint not null, code text not null, datapostuch text, datavip text, constraint tempsvjulv11_pkey primary key (id));

-- 1 hour 55 minute
insert into tempsvjulv11(id, code, datapostuch, datavip) select id, data->>'OGRN', data -> 'SvUchetNO' ->> 'DataPostUch', data ->> 'DataVip' from svjulv1;

-- 1 minute
create index idx_deduplicate_tempsvjulv11_btreev1_codedpostdvipid on tempsvjulv11 using btree (code, datapostuch desc, datavip desc, id desc);

-- 0 minute
drop table if exists tempsvjulv2;
create table tempsvjulv2(id bigint not null, constraint tempsvjulv12_pkey primary key (id));

-- 2 minute
insert into tempsvjulv12(id) select distinct on (code) id from tempsvjulv11 order by code, datapostuch desc, datavip desc, id desc;

-- 0 minute
-- Не могу понять то ли эта махинация 4 строчек кода ниже
-- то ли дроп всех индексов сокращают последующий update с 24 часов до 4 минут
alter table if exists svjulv1 drop column code;
alter table if exists svjulv1 drop column enddate;
alter table if exists svjulv1 add column code text not null default '???';
alter table if exists svjulv1 add column enddate date;

-- 4 minute
update svjulv1 set
    code = (select coalesce(tempsvjulv11.code, '???') from tempsvjulv11 where tempsvjulv11.id = svjulv1.id),
    enddate = case when exists(select 1 from tempsvjulv12 where tempsvjulv12.id = svjulv1.id) then null else '2022-12-31'::date end;

-- 0 minute
drop table if exists tempsvjulv11;
drop table if exists tempsvjulv12;
drop procedure if exists  svjulv1_setogrnenddate;

АНДР
ansh15
H A D G E H O G s

Спасибо огромное, что так тщательно пытались разобраться! Интересное было исследование.

Волшебник

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

(101) дроп всех индексов сокращают последующий update

(102) Не всегда. Чтобы делать update - надо искать записи, которые обновляются.

(103) Именно чтобы не было надобности индексы на целевой таблице держать я вынес нужные данные в облегченные вспомогательные таблички.

(102) Проведу ещё опыт с индексами и без, чтобы точно установить что же влияет - пересоздание колонок или всё же индекс.

(103) Согласен. Но если UPDATE массовый и поиск чисто по ID, то дальше индексы только тормозят запись.

Кстати, я в базе мисты тоже на такое натыкался. Мне нужно было сделать UPDATE большой таблицы, но там были лишние индексы и обновление зависало. Я удалил индексы и UPDATE прошёл за вменяемое время.

(105) У мисты база на Postgres?

(106) У нас MariaDB (бывший MySQL). Но это общие принципы для всех СУБД и всех баз.

(105) Попробовал на полной копии таблицы без индексов скрипт ниже. Сработало менее чем за один час без всяких пересозданий колонок.

-- 0 minute
drop table if exists tempsvjul1;
create table tempsvjul1 (id bigint not null, code text not null, datapostuch text, datavip text, constraint tempsvjul1_pkey primary key (id));

-- 30 minute
insert into tempsvjul1(id, code, datapostuch, datavip) select id, data->>'OGRN', data -> 'SvUchetNO' ->> 'DataPostUch', data ->> 'DataVip' from svjul;

-- 1 minute
create index idx_deduplicate_tempsvjul1_btree_codedpostdvipid on tempsvjul1 using btree (code, datapostuch desc, datavip desc, id desc);

-- 0 minute
drop table if exists tempsvjul2;
create table tempsvjul2(id bigint not null, constraint tempsvjul2_pkey primary key (id));

-- 2 minute
insert into tempsvjul2(id) select distinct on (code) id from tempsvjul1 order by code, datapostuch desc, datavip desc, id desc;

-- 4 minute
update svjul set
    code = coalesce((select tempsvjul1.code from tempsvjul1 where tempsvjul1.id = svjul.id), '???'),
    enddate = case when exists(select 1 from tempsvjul2 where tempsvjul2.id = svjul.id) then null else '2023-10-05'::date end;

-- 0 minute
drop table tempsvjul1;
drop table tempsvjul2;

Волшебник вы правы.

(103) Не могу представить ситуацию, при которой нельзя заменить индексы таблицы, к которой применяется update обращениями к вспомогательным таблицам. Можете привести пример?

(107) А вы случайно не используете микросервисную архитектуру или бессерверное приложение? Я реально удивляюсь насколько шустро форум работает.

(105) Теперь решил обнулить code, enddate, построить индексы и повторить update для интереса - update, работавший без индексов 3-4 минуты теперь висит больше 2 часов. За это время индексы успевали построиться и ANALYZE пересчитать задранную статистику по всем полям до 1000.

(109) Нет, микросервисов не держим-с...
У нас мощное железо (нагрузка 1%) и новый очень быстрый движок (PHP 8.2 со своим встроенным кэшированием) + кэширование тела ветки (зелёную галочку вверху видели? это оно).

(111) [У нас мощное железо] ну от запросов к большим таблицам с отбором/сортировкой по неиндексированным полям, я скажу, никакое железо не спасет. 200 Гб ОЗУ улетят только в путь.

(112) Всё верно. Для каждого запроса нужен анализ того, как он будет выполняться, как часто, какие ресурсы задействует.

(111) > зелёную галочку вверху видели? это оно).
Не видно, а где она?

(114) Вам лучше спросить здесь OFF: Обсуждение нового движка форума