Redshift, КРОМЕ гораздо медленнее, чем LEFT JOIN

Question

Я пытаюсь сравнить промежуточную таблицу ("новые данные") с другой таблицей ("существующие данные"), чтобы определить добавленные / измененные / удаленные строки и, в конечном итоге, переход. Это дорогостоящая операция - полный анализ большого набора данных. Я действительно хотел использовать EXCEPT команда для ясности синтаксиса, но у меня есть серьезные проблемы с производительностью с ним, и найти LEFT JOIN намного лучше.

Две таблицы имеют одинаковое количество строк и одну и ту же схему (почти - во второй таблице есть дополнительная created_date колонка).

Они оба разделяют distkey(date) а также sortkey(date, id1, id2); Я даже указываю столбцы в "правильном" порядке в пределах EXCEPT Заявление в помощь оптимизатору.

Планы запросов для каждого из подмножества данных размера тестирования приведены ниже.

explain
select date, id1, id2, id3, value, attr1, attr2, attr3 from new_data
except select date, id1, id2, id3, value, attr1, attr2, attr3 from existing_data;

XN SetOp Except  (cost=1000002817944.78..1000003266822.61 rows=1995013 width=1637)
  ->  XN Sort  (cost=1000002817944.78..1000002867820.09 rows=19950126 width=1637)
        Sort Key: date, id1, id2, id3, value, attr1, attr2, attr3
        ->  XN Append  (cost=0.00..399002.52 rows=19950126 width=1637)
              ->  XN Subquery Scan "*SELECT* 1"  (cost=0.00..199501.26 rows=9975063 width=1637)
                    ->  XN Seq Scan on new_data  (cost=0.00..99750.63 rows=9975063 width=1637)
              ->  XN Subquery Scan "*SELECT* 2"  (cost=0.00..199501.26 rows=9975063 width=1636)
                    ->  XN Seq Scan on existing_data  (cost=0.00..99750.63 rows=9975063 width=1636)

Сравните с моим гораздо более уродливым LEFT JOIN

explain
select t1.* from new_data t1 
left outer join existing_data t2 on     
    t1.date = t2.date
    and t1.id1 = t2.id1
    and coalesce(t1.id2, -1) = coalesce(t2.id2, -1)
    and coalesce(t1.id3, -1) = coalesce(t2.id3, -1)
    and coalesce(t1.value, -1) = coalesce(t2.value, -1) 
    and coalesce(t1.attr1, '') = coalesce(t2.attr1, '')
    and coalesce(t1.attr2, '') = coalesce(t2.attr2, '')
    and coalesce(t1.attr3, '') = coalesce(t2.attr3, '')
where t2.id1 is null;

XN Merge Left Join DS_DIST_NONE  (cost=0.00..68706795.68 rows=9975063 width=1637)
  Merge Cond: (("outer".date = "inner".date) AND (("outer".id1)::bigint = "inner".id1))
  Join Filter: (((COALESCE("outer".id2, -1))::bigint = COALESCE("inner".id2, -1::bigint)) AND ((COALESCE("outer".id3, -1))::bigint = COALESCE("inner".id3, -1::bigint)) AND ((COALESCE("outer".value, -1::numeric))::double precision = COALESCE("inner".value, -1::double precision)) AND ((COALESCE("outer".attr1, ''::character varying))::text = (COALESCE("inner".attr1, ''::character varying))::text) AND ((COALESCE("outer".attr2, ''::character varying))::text = (COALESCE("inner".attr2, ''::character varying))::text) AND ((COALESCE("outer".attr3, ''::character varying))::text = (COALESCE("inner".attr3, ''::character varying))::text))
  Filter: ("inner".id1 IS NULL)
  ->  XN Seq Scan on new_data t1  (cost=0.00..99750.63 rows=9975063 width=1637)
  ->  XN Seq Scan on existing_data t2  (cost=0.00..99750.63 rows=9975063 width=1636)

Стоимость запроса 1000003266822.61 против 68706795.68, Я знаю, что не должен сравнивать запросы, но это доказано временем выполнения. Любые идеи, почему EXCEPT Заявление намного медленнее, чем LEFT JOIN Вот?

Answer 1

left join генерирует кучу перекрестно соединенных строк для каждого (предположительно упорядоченного) значения ключа, а затем отфильтровывает те, которые ему не нужны, через on; он также может останавливаться, когда (предположительно упорядоченные) значения старых ключей превышают значения новых ключей, поскольку совпадений больше не может быть, что также подразумевает некоторые выводы через некоторые coalesce SARG Smarts. except сначала все сортирует. В этом случае сортировка стоит больше, чем генерация и отбрасывание строк, умножение прохода по строкам каждой клавиши в правой таблице. Конечно, оптимизатор может включать outer join идиома в своем except планирование - но это, очевидно, нет.

Связанный: PostgreSQL: НЕ ВНЕ против разницы в производительности