I am trying to improve the performance of one of our pipelines via redefining the distkey and sortkey on the primary source table. I was able to get a factor of 5 speed increase on the select and insert queries but am getting the inverse on our update queries and can’t figure out why.

Previous table definition

item_history (
  a text,
  b text,
  c text,
  ...
  from timestamp distkey,
  until timestamp
) sortkey(c)

Almost every query we run against the table uses both a and b in the where and/or join along with the from date filter, so I updated the table definition to:

item_history (
  a || '-' || b text distkey,
  a text,
  b text,
  c text,
  ...
  from timestamp,
  until timestamp
) sortkey(from)

The pipeline:

-- select relevant records from kafka stream mv
CREATE TABLE temp_streamed_records 
  diststyle key
  distkey (ab)
  sortkey (kafka_timestamp)
  AS
(
  with (set of CTE to target and transform data from the kafka_stream_mv)
  select
    a || '-' || b as ab
    a,
    b,
    ...
    kafka_timestamp
  from CTE
)

-- update records marked as expired or removed from the streamed data
UPDATE item_history
  set until = kafka_timestamp
FROM temp_streamed_records
WHERE
  item_history.ab = temp_streamed_records.ab
  and
  (e <> e or f <> f)
  and until is null

-- insert new records
INSERT INTO item_history
select 
  ...
FROM temp_streamed_records
LEFT JOIN item_history
  ON
    item_history.ab = temp_streamed_records.ab
    and
    e = e and f = f
    and until is null
where not found

The schema update resolved a ‘distributed a large number of rows…’ error on the select/insert queries. That warning does not appear for the updates (neither before nor after)

Looking at the update query details in the aws console from before and after the only possibly meaningful difference I see is that we went from ~60% disk usage to ~75% usage (duplicating the source tables). The hash condition and join filters are essentially unchanged

before: hash – a = a and b = b, join – (e <> e or f <> f)
after: hash – ab = ab, join – (e <> e or f <> f)

Why does this schema change improve the insert/select 5x better but make the update 5x worse?