Здравствуйте, Есть табличка на 80млн records: --postgres 9.6 CREATE TABLE user_project (

id varchar(255) NOT NULL,
user_email varchar(255) NULL,
-- и еще около 20 колонок
CONSTRAINT user_project_pkey PRIMARY KEY (id)
);
хочу добавить табличку:
create table app_user (
id varchar default uuid_generate_v4(),
email varchar not null
);
и заменить в user_project колонку user_email на user_id которая будет foreign key на app_user.
в результате навалял скрипт который очень медленный:
-- наполняем app_user существующими имейлами, их получиться ~10 млн.
-- возможно ли наполнить табличку быстрее чем здесь?
insert into app_user(email) select distinct user_email from user_project;
alter teble app_user add constraint idx_unique_email unique (email);
alter table app_user add constraint pk_id_app_user primary key (id);
-- делаем копию таблицы с новой колонкой user_id
-- тут вопрос, что должно быть быстрее: join или вложеный select?
create table new_user_project as SELECT user_project.*, (select u.id from app_user as u where user_email = u.email) as user_id FROM user_project;
alter table new_user_project drop column user_email;
--восстановляем индексы

А что именно тормозит (какой этап)? Т.е. в зависимости от этого стоит оптимизировать. > решили что хуже так как ресурсы или проекты следуючего юзера можно будет по id выцепить если где то с секюрити беда По-моему, это shitcurity, Вы меня извините. Т.е. Вы используете UUID не по назначению (за его "стоимость" не получая никаких реальных преимуществ).

инсерт в app_user ~3 часа

А покажите EXPLAIN select distinct user_email from user_project; Да и \dt+ user_project неплохо бы...

QUERY PLAN | -----------------------------------------------------------------------------------------------------------------| Unique (cost=0.56..2286451.29 rows=601016 width=22) | -> Index Only Scan using idx_user_project_user on user_project (cost=0.56..2138610.90 rows=59136156 width=22)|

А что за индекс idx_user_project_user? Вообще, легче "\d+ user_project" показать, мне кажется.

CREATE TABLE public.user_project ( id varchar(255) NOT NULL, is_archived bool NULL, creation_time timestamp NULL, last_access_time timestamp NULL, modification_time timestamp NULL, modified_by varchar(255) NULL, is_preview_archived bool NULL, product_group_name varchar(255) NULL, "location" varchar(255) NULL, s3_location varchar(255) NULL, sku_name varchar(255) NULL, user_email varchar(255) NULL, storage_id varchar(255) NULL, application_type varchar(255) NULL, design_theme_name varchar(255) NULL, is_incomplete bool NULL, mergedata_filename varchar(255) NULL, "name" varchar(255) NULL, product_profile varchar(255) NULL, related_project_id varchar(255) NULL, target_id varchar(255) NULL, template_name varchar(255) NULL, "type" varchar(255) NULL, "usage" varchar(255) NULL, application_id varchar(255) NULL, CONSTRAINT user_project_pkey PRIMARY KEY (id), CONSTRAINT fk5yfr73bfdu6qm35hnylpkspqn FOREIGN KEY (application_id) REFERENCES application(application_id), CONSTRAINT fk_u1461udx5gt5qubyxpst3yby FOREIGN KEY (storage_id) REFERENCES storage(id) ); CREATE INDEX idx_gin_user_project_id ON public.user_project USING gin (id gin_trgm_ops); CREATE INDEX idx_gin_user_project_name ON public.user_project USING gin (name gin_trgm_ops); CREATE INDEX idx_gin_user_project_product_group ON public.user_project USING gin (product_group_name gin_trgm_ops); CREATE INDEX idx_gin_user_project_sku_name ON public.user_project USING gin (sku_name gin_trgm_ops); CREATE INDEX idx_gin_user_project_user_email ON public.user_project USING gin (user_email gin_trgm_ops); CREATE INDEX idx_user_project_creation_time ON public.user_project USING btree (creation_time); CREATE INDEX idx_user_project_id ON public.user_project USING btree (id); CREATE INDEX idx_user_project_last_access_time ON public.user_project USING btree (last_access_time); CREATE INDEX idx_user_project_modification_time ON public.user_project USING btree (modification_time); CREATE INDEX idx_user_project_related_project_id ON public.user_project USING btree (related_project_id); CREATE INDEX idx_user_project_sku_name ON public.user_project USING btree (sku_name); CREATE INDEX idx_user_project_target_id ON public.user_project USING btree (target_id); CREATE INDEX idx_user_project_type ON public.user_project USING btree (type); CREATE INDEX idx_user_project_user ON public.user_project USING btree (user_email);

я думал в докладах все врут, да тут индексы индексов...

Хмм... а какой размер таблицы / этого индекса (\dt+ / \di+)? Не должно это (именно SELECT) тормозить, казалось бы — что там за "железо" (может, медленные HDD)?

я не спец, но... application_type varchar(255) NULL, . . application_id varchar(255) NULL,

Но вообще да, это ужас. Прямо можно в пример worst practice приводить (можно посчитать кол-во нарушений https://wiki.postgresql.org/wiki/Don%27t_Do_This в одной этой таблице). И ещё и дубликаты индексов. :(

это развернутый локальный снепшот бд, железо так себе і5 4го поколения и ссд, должны сделать копию РДС на амазоне как мне говорили "скоро", пока так колупаю

Ладно, там GIN и BTREE... Метод доступа разный.

Вы размеры из принципа не показываете? ;) В любом случае, мне кажется, что при таком железе можно и EXPLAIN (ANALYZE, BUFFERS) этого SELECT попробовать — не должен он очень долго работать, по идее.

спасибо почитаю

Viktor Fedinchuk

Покажите EXPLAIN (ANALYZE, BUFFERS) select distinct user_email from user_project; Опять-таки, это не должно быть очень долго, по идее (если не выполнится за приемлемое время — просто отмените).

QUERY PLAN | -----------------------------------------------------------------------------------------------------------------------------------------------------------------------| Unique (cost=0.56..2286451.29 rows=601016 width=22) (actual time=13.347..18427.660 rows=10077119 loops=1) | Buffers: shared hit=35288332 read=310747 | -> Index Only Scan using idx_user_project_user on user_project (cost=0.56..2138610.90 rows=59136156 width=22) (actual time=13.344..13604.696 rows=59136155 loops=1)| Heap Fetches: 0 | Buffers: shared hit=35288332 read=310747 | Planning time: 0.197 ms | Execution time: 18693.525 ms |

да тут и запросы веселые

Так, т.е. "тормозит" именно INSERT, получается. Вы пробовали советы из https://www.postgresql.org/docs/9.6/populate.html ? Причины торможения, связанные с WAL, можно отбросить (или убедиться, что проблема именно там), если (только для эксперимента!) создать таблицу как UNLOGGED. Но, вообще, может и просто генерация этих "прекрасных" текстовых UUID-ов тормозить.

я попробую как ранее писал @Warstone сделать только без uuid, что бы исключить этот вариант

Вообще это не то чтобы shitcurity, а вполне адекватная практика. При достаточно рандомных UUID никтотне может, зная какое-то одно значение, даже не может прикинуть куда в диапазоне из этих 128бит прицеливаться для поиска перебором. Блин, да даже гуглодрайв имеет вариант расшарить папку или файл для всех, кто сможет по сгенерированному уникальному url постучаться.

> Вообще это не то чтобы shitcurity, а вполне адекватная практика. Кто это сказал? UUID-ы для этого никогда не предназначались, по крайней мере. И, опять-таки, почему именно этот размер "нормален" для всех применений? > При достаточно рандомных UUID никто не может И с "достаточно рандомными" тоже бывали (и запросто ещё будут) проблемы, кстати (можно погуглить) — но это если не говорить только о PostgreSQL. > Блин, да даже гуглодрайв Хмм... это что, аргумент? ;)

"Не предназначались" не означает их неприменимость для данной задачи. Про "нормальный размер" я ничего не говорил. Просто рандомный id в 128 бит лучше последовательного в 64 бита с данной позиции и всё. Проблемы возможны, да. Но, далеко не сразу и вряд ли у автора наберётся достаточно много миллиардов записей учеток пользователей для их проявления. Зы: Гугл в плане обеспечения безопасности для меня лично вполне аргумент, они, в общем-то, в этом неплохо разбираются. Да и , в конце концов, те же ключи всякого сессионного шифрования работают по аналогичному принципу "охренеешь подбирать наугад", разве что они банально длиннее будут.

> "Не предназначались" не означает их неприменимость для данной задачи. Дело в том, что генерация UUID-ов обычно (в т.ч. в PostgreSQL) просто использует системный генератор случайных чисел, какого он качества (в данный момент) — кто его знает (к примеру, https://security.stackexchange.com/a/186097 ). Т.е. это выбор решения "как повезёт" с тем размером, который дают (128 бит) вместо надёжного решения задачи "скрыть идентификаторы / сделать так, чтобы нельзя было подобрать". И делается это потому, что "просто и почти наверняка сработает на практике", а не из каких-то "достойных" соображений, IMHO. ;) "Цена" этого решения может быть выше, чем нормального (индексирование и доступ к случайным данным — не сахар), кстати. > Гугл в плане обеспечения безопасности для меня лично вполне аргумент, они, в общем-то, в этом неплохо разбираются. Хмм... откуда это известно?

Блин, я не говорю что это "идеальная практика", я говорю "нормальная практика" и "при достаточно рандомных UUID". Ежу понятно, что, если urandom выдаёт, условно говоря, три варианта значений, то всё это превращается в тыкву. Ящитаю у автора в компании люди в состоянии оценить риски и качество выхлопа системного генератора.

Ну есть же генераторы энтропии для этого (haveged)

> Блин, я не говорю что это "идеальная практика" Вот и я говорю то же самое — это shitcurity. > Ящитаю у автора в компании люди в состоянии оценить риски и качество выхлопа системного генератора. А считаю, что на самом деле оценить качество выхлопа системного генератора.могут только профессиональные криптографы, а прочие просто [очень] самонадеянны. ;)

Если на входе генераторов энтропии источники оставляют желать лучшего, то и на выходе тоже будет такое себе. Не просто так в том же CloudFlare стену из лава ламп снимают на камеру высокого разрешения, а другие чуваки из детекторов дыма наковыряли материалов для счётчика радиоактивных распадов и это на вход заливают. Я не так давно захотел собрать ГСЧ на бананах (бананы содержат много калия, в том числе и калия-40, который нестабилен и имеет детектируемый β−-распад . Только оказалось что для таких невысоких энергий очень сложно найти и купить подходящий детектор и идея заглохла(

Распределение значений на большой выборке посмотреть - не rocket science.

Если задача "скрыть идентификаторы / сделать так, чтобы нельзя было подобрать" — зачем для этого какие-то генераторы? Хороший симметричный шифр перед передачей их "наружу" (а сами они могут быть и просто значениями из sequence), с неизвестным "снаружи" / своим для каждого идентификатора ключом чем не подходит?

Да оно даже у очень плохих ГПСЧ с виду "ничего". Т.е. это просто несерьёзно.

sexxst
Nope. Вот типичный "плохой". Всё глазами видно бук...

Нет да. ;) Вы в TAOCP видели прекрасные с виду графики явно "плохих" ГПСЧ (там их было несколько, если правильно помню), например? > Кроме того есть даже софт Вот это — другое дело. Но тут уже нужно что-то знать. ;) > Но это уже спор за пределами темы группы, предлагаю оставить его. Хорошо.