И про безопасность я тоже указал. Раньше хорьки, выдры, зайцы, чужие коты и собаки часто на участок забирались.
Как раз по поведению кошек явно видно, что они ощущают себя с псом намного в большей безопасности, чем без него. Даже прячутся за ним, когда чужие появляются у ворот.
безопасность + скученность = причина перверсий и агрессии
Ваши 7 кошек гарантированно временами друг дружку задирали
Это слишком упрощённо. Когда я взял третью кошку, в деревне, где 30 соток, у них сразу начались драки. Но после того, как я взял пса, драки очень быстро прекратились, потому что пёс стал их пресекать. То есть, повышение безопасности (чужие и дикие животные перестали проникать на участок) и скученности (добавился пёс), привело к снижению агрессий.
При этом одна кошка любит спать рядом с псом на его матрасе. Другая с ним играет и ходит гулять с нами по лесам и полям за пределами деревни. А третья любит, когда пёс ей "выкусывает блох".
Рекурсивные CTE некоторые "дерзайнеры" знают. И даже латеральное связывание порой получается добиться. Но вот такое вряд ли какой "дерзайнер" осилит:
CREATE TABLE IF NOT EXISTS flatten.claim (
claimid integer PRIMARY KEY,
claimnumber varchar
);
CREATE TABLE IF NOT EXISTS flatten.clmotpr (
pk_id_clmotpr serial PRIMARY KEY,
claim_claimid integer
REFERENCES flatten.claim(claimid)
ON DELETE CASCADE ON UPDATE CASCADE,
otprnom integer,
otprtypename varchar
);
CREATE TABLE IF NOT EXISTS flatten.otprgraphpod (
pk_id_otprgraphpod serial PRIMARY KEY,
clmotpr_pk_id_clmotpr integer
REFERENCES flatten.clmotpr(pk_id_clmotpr)
ON DELETE CASCADE ON UPDATE CASCADE,
gppodnum integer,
gpcarcount integer
);
WITH level0 AS (
SELECT F.claimid, F.claimnumber, F.clmotpr
FROM unnest(ARRAY[
(1234, '0046604598'::varchar,
(SELECT ARRAY[
(5::integer, 'Полувагоны'::varchar,
(SELECT ARRAY[
(3::integer, 33::integer),
(3::integer, 12::integer)
])
),
(5::integer, 'Цистерны'::varchar,
(SELECT ARRAY[
(3::integer, 32::integer),
(3::integer, 16::integer)
])
)
])
),
(1333, '0032301522'::varchar,
(SELECT ARRAY[
(7::integer, 'Полувагоны'::varchar,
(SELECT ARRAY[
(4::integer, 13::integer),
(5::integer, 17::integer)
])
),
(8::integer, 'Цистерны'::varchar,
(SELECT ARRAY[
(6::integer, 43::integer),
(6::integer, 22::integer)
])
)
])
)
]) F(claimid integer, claimnumber varchar, clmotpr record[])
),
level1 AS (
SELECT nextval('flatten.clmotpr_pk_id_clmotpr_seq') AS pk_id_clmotpr, -- schema||'.'||table_name||'_'||column_name||'_seq'
L0.claimid AS claim_claimid,
F.otprnom, F.otprtypename, F.otprgraphpod
FROM level0 L0
CROSS JOIN unnest(L0.clmotpr) F(otprnom integer, otprtypename varchar, otprgraphpod record[])
),
level2 AS (
SELECT nextval('flatten.otprgraphpod_pk_id_otprgraphpod_seq') AS pk_id_otprgraphpod, -- schema||'.'||table_name||'_'||column_name||'_seq'
L1.pk_id_clmotpr AS clmotpr_pk_id_clmotpr,
F.gppodnum, F.gpcarcount
FROM level1 L1
CROSS JOIN unnest(L1.otprgraphpod) F(gppodnum integer, gpcarcount integer)
),
insert_l0 AS (
INSERT INTO flatten.claim (claimid, claimnumber)
SELECT L.claimid, L.claimnumber
FROM level0 L
ON CONFLICT (claimid) DO UPDATE SET
claimnumber=EXCLUDED.claimnumber
RETURNING claimid, CASE WHEN xmax=0 THEN 0 ELSE 1 END AS updated
),
cleanup AS (
DELETE FROM flatten.clmotpr F
USING insert_l0 L
WHERE F.claim_claimid=L.claimid AND updated=1
),
insert_l1 AS (
INSERT INTO flatten.clmotpr (pk_id_clmotpr, claim_claimid, otprnom, otprtypename)
SELECT L.pk_id_clmotpr, L.claim_claimid, L.otprnom, L.otprtypename
FROM level1 L
)
INSERT INTO flatten.otprgraphpod (pk_id_otprgraphpod, clmotpr_pk_id_clmotpr, gppodnum, gpcarcount)
SELECT L.pk_id_otprgraphpod, L.clmotpr_pk_id_clmotpr, L.gppodnum, L.gpcarcount
FROM level2 L;
Это упрощенный пример сохранения пакета записей элементарной иерархии трех таблиц при интеграции. ARRAY[] написан для примера. По факту - это параметр запроса, передаваемый в бинарном виде.
Запрос, написанный таким образом, что планировщик вынужден строить оптимальный план запроса.
Классический пример - любые отношения между таблицами, где статистики таблицы не отражают и не могут отражать реальной селективности данных в этих таблицах для конкретного запроса.
Можете взять любую схему из учебника, добавить во все первичные ключи первым полем SessionId и заполнить разные сессии данными с очень разной селективностью. Так, чтобы статистики содержали "среднюю температуру по больнице", но были очень далеки от реальных гистограмм внутри конкретных сессий. После чего сможете наконец-то начать "входить в тему", наблюдая насколько не оптимальные планы запросов возникают при написании в лоб запросов только по одной сессии.
Быстрее всего это обнаружите на "ромбе" вида:
CREATE TABLE one (s int, a int, b int,
CONSTRAINT one_pk_idx PRIMARY KEY (s, a, b));
CREATE TABLE two (s int, a int, c int,
CONSTRAINT one_pk_idx PRIMARY KEY (s, a, c));
CREATE TABLE three (s int, b int, d int,
CONSTRAINT one_pk_idx PRIMARY KEY (s, b, d));
CREATE TABLE four (s int, d int, c int,
CONSTRAINT one_pk_idx PRIMARY KEY (s, d, c));
Если в каждой из этих таблиц селективности a, b, c и d в разных сессиях будут различаться хотя бы на порядок, то уже при нескольких сессиях на миллионе записей в одной сессии сможете наблюдать совершенно неоптимальные планы запросов в рамках одной сессии.
то есть если бы я (или условный Sir Cam) весь этот дамп залили бы на ру-трекер, то у банка бы никаких проблем не было, только у меня одного в мире???
Если договор предусматривал передачу этого дампа - то именно так. Для примера, если данные Сбера, полученные у него при проведении аудита PWC в 2021 году, где-то опубликует сотрудник этого же PWC, то в первую очередь PWC будет отвечать за это в суде. А любые иски клиентов к Сберу будут автоматически порождать регресс к PWC (№ 152-ФЗ ст.6 п.5).
Если коротко, то материализация иногда позволяет получить более эффективный запрос.
Пример
CREATE TABLE IF NOT EXISTS tmp_tmp (
id serial PRIMARY KEY,
group_id integer NOT NULL,
val double precision NULL
);
INSERT INTO tmp_tmp (group_id, val)
SELECT G.n/1000, random()
FROM generate_series(1,10000000) G(n);
CREATE INDEX tmp_tmp_group_id_idx ON tmp_tmp(group_id, val);
-- Важно! Запретим параллелизм.
SET parallel_setup_cost = 100000;
EXPLAIN ANALYZE
WITH Perc AS (
SELECT group_id,
PERCENTILE_CONT(ARRAY[0.1,0.9])
WITHIN GROUP (ORDER BY val) AS limits
FROM tmp_tmp
WHERE group_id BETWEEN 200 AND 500
GROUP BY group_id )
SELECT T.group_id, AVG(T.val) FILTER (WHERE T.val BETWEEN P.limits[1] AND P.limits[2])
FROM tmp_tmp T
JOIN Perc P ON P.group_id=T.group_id
WHERE T.group_id BETWEEN 200 AND 500
GROUP BY T.group_id;
HashAggregate (cost=21394.02..21519.02 rows=10000 width=12) (actual time=191.085..191.176 rows=301.00 loops=1)
Group Key: t.group_id
Batches: 1 Memory Usage: 321kB
Buffers: shared hit=3152
-> Hash Join (cost=9713.40..18354.33 rows=303969 width=44) (actual time=54.194..118.495 rows=301000.00 loops=1)
Hash Cond: (t.group_id = p.group_id)
Buffers: shared hit=3152
-> Index Only Scan using tmp_tmp_group_id_idx on tmp_tmp t (cost=0.43..7843.12 rows=303969 width=12) (actual time=0.047..27.031 rows=301000.00 loops=1)
Index Cond: ((group_id >= 200) AND (group_id <= 500))
Heap Fetches: 0
Index Searches: 1
Buffers: shared hit=1576
-> Hash (cost=9587.96..9587.96 rows=10000 width=36) (actual time=54.124..54.126 rows=301.00 loops=1)
Buckets: 16384 Batches: 1 Memory Usage: 151kB
Buffers: shared hit=1576
-> Subquery Scan on p (cost=0.43..9587.96 rows=10000 width=36) (actual time=0.199..54.028 rows=301.00 loops=1)
Buffers: shared hit=1576
-> GroupAggregate (cost=0.43..9487.96 rows=10000 width=36) (actual time=0.198..53.988 rows=301.00 loops=1)
Group Key: tmp_tmp.group_id
Buffers: shared hit=1576
-> Index Only Scan using tmp_tmp_group_id_idx on tmp_tmp (cost=0.43..7843.12 rows=303969 width=12) (actual time=0.012..27.947 rows=301000.00 loops=1)
Index Cond: ((group_id >= 200) AND (group_id <= 500))
Heap Fetches: 0
Index Searches: 1
Buffers: shared hit=1576
Planning Time: 0.196 ms
Execution Time: 191.256 ms
EXPLAIN ANALYZE
WITH Perc AS MATERIALIZED (
SELECT group_id,
PERCENTILE_CONT(ARRAY[0.1,0.9])
WITHIN GROUP (ORDER BY val) AS limits
FROM tmp_tmp
WHERE group_id BETWEEN 200 AND 500
GROUP BY group_id )
SELECT T.group_id, AVG(T.val) FILTER (WHERE T.val BETWEEN P.limits[1] AND P.limits[2])
FROM tmp_tmp T
JOIN Perc P ON P.group_id=T.group_id
WHERE T.group_id BETWEEN 200 AND 500
GROUP BY T.group_id;
GroupAggregate (cost=9488.40..24520.38 rows=10000 width=12) (actual time=0.744..155.808 rows=301.00 loops=1)
Group Key: t.group_id
Buffers: shared hit=3152
CTE perc
-> GroupAggregate (cost=0.43..9487.96 rows=10000 width=36) (actual time=0.194..48.148 rows=301.00 loops=1)
Group Key: tmp_tmp.group_id
Buffers: shared hit=1576
-> Index Only Scan using tmp_tmp_group_id_idx on tmp_tmp (cost=0.43..7843.12 rows=303969 width=12) (actual time=0.027..25.388 rows=301000.00 loops=1)
Index Cond: ((group_id >= 200) AND (group_id <= 500))
Heap Fetches: 0
Index Searches: 1
Buffers: shared hit=1576
-> Merge Join (cost=0.43..11867.73 rows=303969 width=44) (actual time=0.210..96.627 rows=301000.00 loops=1)
Merge Cond: (p.group_id = t.group_id)
Buffers: shared hit=3152
-> CTE Scan on perc p (cost=0.00..200.00 rows=10000 width=36) (actual time=0.196..48.245 rows=301.00 loops=1)
Storage: Memory Maximum Storage: 38kB
Buffers: shared hit=1576
-> Index Only Scan using tmp_tmp_group_id_idx on tmp_tmp t (cost=0.43..7843.12 rows=303969 width=12) (actual time=0.012..23.601 rows=301000.00 loops=1)
Index Cond: ((group_id >= 200) AND (group_id <= 500))
Heap Fetches: 0
Index Searches: 1
Buffers: shared hit=1576
Planning Time: 0.277 ms
Execution Time: 155.869 ms
Здесь считаем среднее между первым и девятым децилем.
По моему опыту, даже Claude пока часто не в состоянии написать оптимальный запрос для PostgreSQL, если не запинывать его насильно подсказками на верный путь рассуждений. Куда уж там конструкторам или ORM.
Когда я заказываю виртуалку или железный сервер под Linux не в AD, то мне при завершении работ совершенно законно сообщают пароль sudo пользователя. Естественно, я его сразу же меняю и создаю уже аккаунты для себя, своего руководителя и того, кто меня будет заменять, законно сообщая им сгенерированные мной пароли.
Это вполне можно считать законным оборотом паролей.
Если договор доступ к данным в БД предусматривал, то остальное уже ваши проблемы. На проектах внедрения нам ещё не к таким данным доступ предоставляли. Я порой бекапы продуктивных БД на HDD и LTO возил в командировках. Но зашифрованные, во избежание проблем при их пропаже.
Вам не кажется, что там где начинается мещанство, заканчивается то будущее, о котором писали Стругацкие?
Счастливым можно быть голодным, уставшим, замерзая и задыхаясь, но покорив вершину. Локализовать и исправить надоевшую трудноуловимую багу - тоже счастье.
То есть, если имущества достаточно, чтобы выжить и не нанести при этом существенного вреда своему здоровью, то уже можно быть счастливым.
Вы даже не представляете, что бывает в компаниях США. В одной достаточно крупной компании из Сент-Льюиса я с удивлением обнаружил, что для некоторого сервиса использовался не доменный(!) аккаунт с правами администратора MS SQL Server и пароль хранился открытым текстом. А в качестве вишенки на торт, доменный аккаунт, под которым был запущен этот MS SQL Server, имел административные права в домене!
"Отель у погибшего альпиниста" как раз достаточно кинематографичен. Да и фильм 1979 года совсем неплох.
А вот по остальным двум произведениям снимают сериалы. Причем "Трудно быть богом" уже известно, что будет существенно расширен относительно книги, что резко снижает мой к нему интерес. "Жук в муравейнике" в качестве сериала тоже вызывает подобные опасения.
Я так понял, что под повторно имеется в виду несколько агрегаций с FILTER или оконных функций с разными OVER по полям CTE. В таких случаях иногда действительно бывает смысл в материализации.
PostgreSQL анализирует литерал 79991234567 и пытается определить его тип. Он не заключён в кавычки, значит, это числовой тип (numeric/bigint). Поскольку типы varchar и, например, bigint несовместимы, PostgreSQL приводит колонку: CAST(phone AS bigint).
С каких пор PostgreSQL стал сам приводить типы?
С PostgreSQL 12 по 19 вижу "operator does not exist: character varying = bigint. Hint: No operator matches the given name and argument types. You might need to add explicit type casts."
Однако в некоторых случаях выбранный план может быть далек от оптимального. Если статистика устарела или неполная, выбор плана может быть ошибочным, и запрос может выполняться долго.
Я бы всё же добавил, что статистика тут не панацея. Есть немало случаев, когда статистики в принципе не могут помочь планировщику даже при наличии правильных индексов и запинывать в оптимальный план его приходится вручную.
Временные таблицы в PostgreSQL уж никак не лучше CTE, так как их создание требует модификации Information Schema. И это не только распухание таблиц метаданных, но и блокировки.
А вот нежурналируемые постоянные таблицы и pg_variables - вполне разумная альтернатива CTE в ряде случаев.
Читайте полностью. У них очень хорошие отношения.
И про безопасность я тоже указал. Раньше хорьки, выдры, зайцы, чужие коты и собаки часто на участок забирались.
Как раз по поведению кошек явно видно, что они ощущают себя с псом намного в большей безопасности, чем без него. Даже прячутся за ним, когда чужие появляются у ворот.
Это слишком упрощённо. Когда я взял третью кошку, в деревне, где 30 соток, у них сразу начались драки. Но после того, как я взял пса, драки очень быстро прекратились, потому что пёс стал их пресекать. То есть, повышение безопасности (чужие и дикие животные перестали проникать на участок) и скученности (добавился пёс), привело к снижению агрессий.
При этом одна кошка любит спать рядом с псом на его матрасе. Другая с ним играет и ходит гулять с нами по лесам и полям за пределами деревни. А третья любит, когда пёс ей "выкусывает блох".
ISO/IEC Вам не указ? )))
Мелкими шагами такая интеграция обычным Sink выполнялась почти на два порядка(!) медленней.
Рекурсивные CTE некоторые "дерзайнеры" знают. И даже латеральное связывание порой получается добиться. Но вот такое вряд ли какой "дерзайнер" осилит:
Это упрощенный пример сохранения пакета записей элементарной иерархии трех таблиц при интеграции. ARRAY[] написан для примера. По факту - это параметр запроса, передаваемый в бинарном виде.
Но так и не вошли.
Запрос, написанный таким образом, что планировщик вынужден строить оптимальный план запроса.
Классический пример - любые отношения между таблицами, где статистики таблицы не отражают и не могут отражать реальной селективности данных в этих таблицах для конкретного запроса.
Можете взять любую схему из учебника, добавить во все первичные ключи первым полем SessionId и заполнить разные сессии данными с очень разной селективностью. Так, чтобы статистики содержали "среднюю температуру по больнице", но были очень далеки от реальных гистограмм внутри конкретных сессий. После чего сможете наконец-то начать "входить в тему", наблюдая насколько не оптимальные планы запросов возникают при написании в лоб запросов только по одной сессии.
Быстрее всего это обнаружите на "ромбе" вида:
Если в каждой из этих таблиц селективности a, b, c и d в разных сессиях будут различаться хотя бы на порядок, то уже при нескольких сессиях на миллионе записей в одной сессии сможете наблюдать совершенно неоптимальные планы запросов в рамках одной сессии.
Если договор предусматривал передачу этого дампа - то именно так. Для примера, если данные Сбера, полученные у него при проведении аудита PWC в 2021 году, где-то опубликует сотрудник этого же PWC, то в первую очередь PWC будет отвечать за это в суде. А любые иски клиентов к Сберу будут автоматически порождать регресс к PWC (№ 152-ФЗ ст.6 п.5).
Если коротко, то материализация иногда позволяет получить более эффективный запрос.
Пример
Здесь считаем среднее между первым и девятым децилем.
Это шутка? Или Вы вообще не в теме?
По моему опыту, даже Claude пока часто не в состоянии написать оптимальный запрос для PostgreSQL, если не запинывать его насильно подсказками на верный путь рассуждений. Куда уж там конструкторам или ORM.
Когда я заказываю виртуалку или железный сервер под Linux не в AD, то мне при завершении работ совершенно законно сообщают пароль sudo пользователя. Естественно, я его сразу же меняю и создаю уже аккаунты для себя, своего руководителя и того, кто меня будет заменять, законно сообщая им сгенерированные мной пароли.
Это вполне можно считать законным оборотом паролей.
Если договор доступ к данным в БД предусматривал, то остальное уже ваши проблемы. На проектах внедрения нам ещё не к таким данным доступ предоставляли. Я порой бекапы продуктивных БД на HDD и LTO возил в командировках. Но зашифрованные, во избежание проблем при их пропаже.
Вам не кажется, что там где начинается мещанство, заканчивается то будущее, о котором писали Стругацкие?
Счастливым можно быть голодным, уставшим, замерзая и задыхаясь, но покорив вершину. Локализовать и исправить надоевшую трудноуловимую багу - тоже счастье.
То есть, если имущества достаточно, чтобы выжить и не нанести при этом существенного вреда своему здоровью, то уже можно быть счастливым.
Вы даже не представляете, что бывает в компаниях США. В одной достаточно крупной компании из Сент-Льюиса я с удивлением обнаружил, что для некоторого сервиса использовался не доменный(!) аккаунт с правами администратора MS SQL Server и пароль хранился открытым текстом. А в качестве вишенки на торт, доменный аккаунт, под которым был запущен этот MS SQL Server, имел административные права в домене!
Надо ли объяснять, что такое xp_cmdshell?
Какая разница, называть его по фамилии или по прозвищу? Не убивал бы он на Саракше, то вряд ли убил бы и на Земле.
"Отель у погибшего альпиниста" как раз достаточно кинематографичен. Да и фильм 1979 года совсем неплох.
А вот по остальным двум произведениям снимают сериалы. Причем "Трудно быть богом" уже известно, что будет существенно расширен относительно книги, что резко снижает мой к нему интерес. "Жук в муравейнике" в качестве сериала тоже вызывает подобные опасения.
И тот же самый Странник застрелил в последствии Льва Абалкина. Планирование рисков - это одно, а их предотвращение любой ценой - совсем иное.
Я так понял, что под повторно имеется в виду несколько агрегаций с FILTER или оконных функций с разными OVER по полям CTE. В таких случаях иногда действительно бывает смысл в материализации.
С каких пор PostgreSQL стал сам приводить типы?
С PostgreSQL 12 по 19 вижу "operator does not exist: character varying = bigint. Hint: No operator matches the given name and argument types. You might need to add explicit type casts."
Я бы всё же добавил, что статистика тут не панацея. Есть немало случаев, когда статистики в принципе не могут помочь планировщику даже при наличии правильных индексов и запинывать в оптимальный план его приходится вручную.
Материализация CTE - один из методов оптимизации запросов, позволяющий порой направить планировщик на верный путь.
Временные таблицы в PostgreSQL уж никак не лучше CTE, так как их создание требует модификации Information Schema. И это не только распухание таблиц метаданных, но и блокировки.
А вот нежурналируемые постоянные таблицы и pg_variables - вполне разумная альтернатива CTE в ряде случаев.