Упрощение ГДЕ (НЕ) В (...) И ГДЕ (НЕ) В (...)
В этой демонстрации dbfiddle у меня есть DELETE FROM... WHERE в конце, например:
......
DELETE FROM data_table
WHERE
(location, param_id, ref_time, fcst_time) NOT IN (SELECT location, param_id, ref_time, fcst_time FROM sel1)
AND
(location, param_id, ref_time, fcst_time) NOT IN (SELECT location, param_id, ref_time, fcst_time FROM sel2);
Хотя он и работает, но кажется излишне многословным, а возможно, и не оптимальным с точки зрения производительности?
Есть ли способ упростить это, например. с помощью одного оператора NOT IN?
Из-за того, как они определены, между sel1 и sel2 нет пересечения/пересечения.
status_table имеет 8033 строки
data_table имеет 116432724 строки
sel1 имеет 61860084 строки
sel2 имеет 53706188 строк
Цифры не обязательно складываются, потому что это живая база данных, и данные поступают постоянно. И да, на данный момент есть несколько строк для удаления, если они вообще есть, потому что большинство/все данные актуальны.
Сначала я бы усомнился в том, что это действительно работает так, как ожидалось. NOT IN на пару однотонных колонок дело хитрое. Являются ли определения таблиц в вашей скрипке реальными? Все типы данных и ограничения отображаются? Все колонки NOT NULL? Ваша версия Postgres? Для оптимизации производительности нам также необходимо знать кардинальность и доступные индексы.
@ErwinBrandstetter определения таблиц в скрипке такие же, как мои реальные определения таблиц, и та же версия postgres (15.1).
@Zegarek Дополнительный бонус от их дедупликации достигается за счет более медленного запроса?
@drmrbrewer Похоже на то, но в том смысле, что дедупликация добавляет немного стоимости, это позже компенсируется, когда not in приходится проводить сравнение.
@Zegarek, когда я запускаю вашу демонстрацию, я делаю так, что запрос not in... union немного быстрее, чем запрос not in and not in?
@drmrbrewer Верно, но я бы не стал полагаться на один случайный пример, чтобы сформулировать правило. Там , not in and not in самый медленный, not in union all второй, not in union самый быстрый. Здесь и здесь союзы поменялись местами. На моей машине с 50 миллионами строк оба объединения занимают около 32 секунд, а первоначальная версия — 37 секунд.
Под «мощностью» я имею в виду примерное количество строк в каждой таблице и примерное количество строк, которые перекрываются между sel1 и sel2, а также примерное количество строк в data_table, которые будут удалены. Отредактируйте эту информацию в своем вопросе, пожалуйста.
@Zegarek Хотя на самом деле в моем случае нет пересечения / пересечения между sel1 и sel2 и, следовательно, никакой дедупликации. Это потому, что sel1 требует s.ready, а sel2 требует NOT s.settled... если это NOT s.settled, то это также NOT s.ready просто из-за того, как они определены.
@ErwinBrandstetter похоже, что я ответил на ваш вопрос о совпадении с моим ответом @Zegarek ... между sel1 и sel2 нет пересечения/пересечения.
Хороший. Что насчет остальных моих вопросов?
Я отредактировал вопрос, чтобы добавить количество строк для двух таблиц... Я выполнил запрос, чтобы попытаться подсчитать количество строк, которые будут удалены, но я прервал его после того, как он работал в течение часа: -0.. ... этот запрос может потребовать серьезной оптимизации или полного переосмысления.
После пристального взгляда на вашу скрипку можно с уверенностью сказать, что запрос, который вы показываете в вопросе, является лишь верхушкой айсберга, а основные проблемы скрыты ниже.
@ErwinBrandstetter есть какие-нибудь намеки на то, в чем заключаются основные проблемы?
Полной картины получить не удалось. Но после путаницы с ref_time + fcst_time = timestamp ПК работает с (ref_time, fcst_time), но SELECT DISTINCT ON работает с timestamp и, следовательно, не поддерживает индекс. Я подозреваю, что это более запутанно, чем нужно, и могут быть гораздо более быстрые решения, но здесь я отключаюсь...
Этот вопрос не ясен. Какое отношение status_table имеет к вопросу? Вы ищете кратчайший способ написания запроса или хорошую производительность?
@LaurenzAlbe Я добавил ссылку на количество строк status_table в ответ на комментарий выше. Некоторая информация из status_table переносится в data_tableINNER JOIN... надеюсь, в dbfiddle все понятно. Сейчас я пытаюсь выяснить, как оптимизировать индексы для этого сценария (т.е. в соответствии с jsfiddle), и я потерялся... это мой первый раз, когда я рассматриваю индексы, поэтому любое понимание, которое у вас есть, было бы полезно ... Если проще, я могу создать отдельный вопрос, потому что сейчас он выходит за рамки моего первоначального вопроса ... или могу перенести его в обсуждение. Спасибо!
Да, пожалуйста, оставьте вопрос простым и самодостаточным. Комментарии исчезают, поэтому вопрос должен быть понятен без них. Если вы чувствуете, что на самом деле это два вопроса, неплохо было бы начать со второго.
@LaurenzAlbe, конечно, я разместила новый вопрос здесь... было бы здорово, если бы вы могли взглянуть!
Ответы 2
Как насчет еще одного WITH вокруг всего?
WITH d AS (
WITH
stats AS (
SELECT ref_time
, max(updated) < (round(extract(epoch from now()) / 60) - 200) AS settled
, (count(*) FILTER (WHERE processed) = count(*)) AND (max(updated) < (round(extract(epoch from now()) / 60) - 200)) AS ready
FROM status_table
GROUP BY ref_time
),
min_ts AS (
SELECT ref_time FROM stats WHERE ready ORDER BY ref_time DESC LIMIT 1
),
sel1 AS (
-- records that would be selected by an actual data lookup (use same logic)... we need to keep these (don't delete)
SELECT DISTINCT ON (d.location, d.timestamp, d.param_id)
d.location, d.param_id, d.ref_time, d.fcst_time
FROM data_table AS d
INNER JOIN stats s USING (ref_time)
WHERE s.ready AND d.timestamp >= (SELECT ref_time FROM min_ts)
ORDER BY d.location, d.timestamp, d.param_id, d.ref_time DESC
),
sel2 AS (
-- also keep all records that are in-progress (not 'settled')
SELECT
d.location, d.param_id, d.ref_time, d.fcst_time
FROM data_table AS d
INNER JOIN stats AS s USING (ref_time)
WHERE NOT s.settled
)
SELECT data_table.*
FROM
data_table LEFT JOIN
sel1 ON sel1.location=data_table.location AND sel1.param_id=data_table.param_id AND sel1.ref_time = data_table.ref_time AND sel1.fcst_time = data_table.fcst_time LEFT JOIN
sel2 ON sel2.location=data_table.location AND sel2.param_id=data_table.param_id AND sel2.ref_time = data_table.ref_time AND sel2.fcst_time = data_table.fcst_time
WHERE
sel1.location IS NULL AND
sel2.location IS NULL
)
DELETE FROM data_table
WHERE (location, param_id, ref_time, fcst_time) IN (SELECT location, param_id, ref_time, fcst_time FROM d)
Отвечая на исходный вопрос в этой теме:
......
DELETE FROM data_table
WHERE
(location, param_id, ref_time, fcst_time)
NOT IN
(SELECT location, param_id, ref_time, fcst_time FROM sel1
UNION ALL
SELECT location, param_id, ref_time, fcst_time FROM sel2);
Замена WHERE (a) NOT IN (b) AND NOT IN (c) на
WHERE (a) NOT IN (b UNION c) не только сокращает выражение, но и обеспечивает некоторые улучшения производительности. В зависимости от того, насколько сильно пересекаются b и c, вы можете выбрать либо UNION, либо UNION ALL:
WHERE (a) NOT IN (b UNION c)удаляет дубликаты междуbиc, что сначала добавляет небольшую стоимость, но может ускорить процесс позже, когдаaсравнивается с фактически меньшим союзом.WHERE (a) NOT IN (b UNION ALL c)пропускает дедупликацию, поэтому, если по замыслу ее не может быть, это лучший выбор.
Вот демо, показывающее, как меняются планы. Локально я также протестировал удаление из 120-метровой таблицы строк против 2x 10-метровых таблиц с перекрытием 99% и получил 118 секунд для начальной версии, 105 секунд для union, 98 секунд для union all. При всех тех же подсчетах и без перекрытий получилось 118, 103, 95. Я ожидаю, что с большим количеством столбцов и более сложными типами разница будет более заметной.
Я решил превратить свой комментарий в ответ, добавив последующие тесты, оставив более широкую оптимизацию для обсуждения в новой теме.
Спасибо... было бы здорово, если бы вы могли высказать свои мысли по теме оптимизации :) Я застрял на этом, но начинаю задаваться вопросом, следует ли просто избегать SELECT DISTINCT ON любой ценой.
@drmrbrewer Я прокомментирую здесь, чтобы не мешать текущему обсуждению. Я бы разделил вашу data_table , подправил настройки параллельного рабочего процесса и сохранил кешированный белый список, если этот запрос вам нужно выполнять регулярно - вы также можете запустить это удаление из того же триггера, который поддерживает кешированный белый список. Пример кэширования на основе триггеров и как партиционирование помогает построить параллельный план здесь.
Вы можете оставить только один
not inпротив союза этих избранных. В качестве дополнительного бонуса union выполнит их дедупликацию.delete from t1 where col not in (select col from t2 union select col from t3);