Упрощение ГДЕ (НЕ) В (...) И ГДЕ (НЕ) В (...)

В этой демонстрации 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 против союза этих избранных. В качестве дополнительного бонуса union выполнит их дедупликацию. delete from t1 where col not in (select col from t2 union select col from t3);

Zegarek 19.11.2022 13:41

Сначала я бы усомнился в том, что это действительно работает так, как ожидалось. NOT IN на пару однотонных колонок дело хитрое. Являются ли определения таблиц в вашей скрипке реальными? Все типы данных и ограничения отображаются? Все колонки NOT NULL? Ваша версия Postgres? Для оптимизации производительности нам также необходимо знать кардинальность и доступные индексы.

Erwin Brandstetter 19.11.2022 13:58

@ErwinBrandstetter определения таблиц в скрипке такие же, как мои реальные определения таблиц, и та же версия postgres (15.1).

drmrbrewer 19.11.2022 14:01

@Zegarek Дополнительный бонус от их дедупликации достигается за счет более медленного запроса?

drmrbrewer 19.11.2022 16:24

@drmrbrewer Похоже на то, но в том смысле, что дедупликация добавляет немного стоимости, это позже компенсируется, когда not in приходится проводить сравнение.

Zegarek 19.11.2022 16:32

@Zegarek, когда я запускаю вашу демонстрацию, я делаю так, что запрос not in... union немного быстрее, чем запрос not in and not in?

drmrbrewer 19.11.2022 16:41

@drmrbrewer Верно, но я бы не стал полагаться на один случайный пример, чтобы сформулировать правило. Там , not in and not in самый медленный, not in union all второй, not in union самый быстрый. Здесь и здесь союзы поменялись местами. На моей машине с 50 миллионами строк оба объединения занимают около 32 секунд, а первоначальная версия — 37 секунд.

Zegarek 19.11.2022 16:48

Под «мощностью» я имею в виду примерное количество строк в каждой таблице и примерное количество строк, которые перекрываются между sel1 и sel2, а также примерное количество строк в data_table, которые будут удалены. Отредактируйте эту информацию в своем вопросе, пожалуйста.

Erwin Brandstetter 19.11.2022 16:48

@Zegarek Хотя на самом деле в моем случае нет пересечения / пересечения между sel1 и sel2 и, следовательно, никакой дедупликации. Это потому, что sel1 требует s.ready, а sel2 требует NOT s.settled... если это NOT s.settled, то это также NOT s.ready просто из-за того, как они определены.

drmrbrewer 19.11.2022 16:50

@ErwinBrandstetter похоже, что я ответил на ваш вопрос о совпадении с моим ответом @Zegarek ... между sel1 и sel2 нет пересечения/пересечения.

drmrbrewer 19.11.2022 16:53

Хороший. Что насчет остальных моих вопросов?

Erwin Brandstetter 19.11.2022 16:57

Я отредактировал вопрос, чтобы добавить количество строк для двух таблиц... Я выполнил запрос, чтобы попытаться подсчитать количество строк, которые будут удалены, но я прервал его после того, как он работал в течение часа: -0.. ... этот запрос может потребовать серьезной оптимизации или полного переосмысления.

drmrbrewer 19.11.2022 17:48

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

Erwin Brandstetter 19.11.2022 18:22

@ErwinBrandstetter есть какие-нибудь намеки на то, в чем заключаются основные проблемы?

drmrbrewer 19.11.2022 18:25

Полной картины получить не удалось. Но после путаницы с ref_time + fcst_time = timestamp ПК работает с (ref_time, fcst_time), но SELECT DISTINCT ON работает с timestamp и, следовательно, не поддерживает индекс. Я подозреваю, что это более запутанно, чем нужно, и могут быть гораздо более быстрые решения, но здесь я отключаюсь...

Erwin Brandstetter 19.11.2022 18:30

Этот вопрос не ясен. Какое отношение status_table имеет к вопросу? Вы ищете кратчайший способ написания запроса или хорошую производительность?

Laurenz Albe 20.11.2022 12:39

@LaurenzAlbe Я добавил ссылку на количество строк status_table в ответ на комментарий выше. Некоторая информация из status_table переносится в data_tableINNER JOIN... надеюсь, в dbfiddle все понятно. Сейчас я пытаюсь выяснить, как оптимизировать индексы для этого сценария (т.е. в соответствии с jsfiddle), и я потерялся... это мой первый раз, когда я рассматриваю индексы, поэтому любое понимание, которое у вас есть, было бы полезно ... Если проще, я могу создать отдельный вопрос, потому что сейчас он выходит за рамки моего первоначального вопроса ... или могу перенести его в обсуждение. Спасибо!

drmrbrewer 20.11.2022 12:47

Да, пожалуйста, оставьте вопрос простым и самодостаточным. Комментарии исчезают, поэтому вопрос должен быть понятен без них. Если вы чувствуете, что на самом деле это два вопроса, неплохо было бы начать со второго.

Laurenz Albe 20.11.2022 12:50

@LaurenzAlbe, конечно, я разместила новый вопрос здесь... было бы здорово, если бы вы могли взглянуть!

drmrbrewer 20.11.2022 13:05
Шаблоны Angular PrimeNg
Шаблоны Angular PrimeNg
Как привнести проверку типов в наши шаблоны Angular, использующие компоненты библиотеки PrimeNg, и настроить их отображение с помощью встроенной...
Создайте ползком, похожим на звездные войны, с помощью CSS и Javascript
Создайте ползком, похожим на звездные войны, с помощью CSS и Javascript
Если вы веб-разработчик (или хотите им стать), то вы наверняка гик и вам нравятся "Звездные войны". А как бы вы хотели, чтобы фоном для вашего...
Документирование API с помощью Swagger на Springboot
Документирование API с помощью Swagger на Springboot
В предыдущей статье мы уже узнали, как создать Rest API с помощью Springboot и MySql .
Начала с розового дизайна
Начала с розового дизайна
Pink Design - это система дизайна Appwrite с открытым исходным кодом для создания последовательных и многократно используемых пользовательских...
Шлюз в PHP
Шлюз в PHP
API-шлюз (AG) - это сервер, который действует как единая точка входа для набора микросервисов.
14 Задание: Типы данных и структуры данных Python для DevOps
14 Задание: Типы данных и структуры данных Python для DevOps
проверить тип данных используемой переменной, мы можем просто написать: your_variable=100
3
19
115
2
Перейти к ответу Данный вопрос помечен как решенный

Ответы 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 22.11.2022 13:06

Другие вопросы по теме