Amazon Redshift Views, Sort Keys, and Outer Joins

My team built a process to load from a couple of base tables, in our Amazon Redshift enterprise data warehouse, into an other table which would act as a data mart entity. The data was rolled up and it included some derived fields. The SQL query had some complicity to it.

This process ran daily and was being killed by our operations team after running for 22 hours.

I stepped in to assist with performance tuning and discovered that join choices, such as INNER vs. OUTER joins have a big impact on whether Redshift can use its sort keys or not.

My troubleshooting path

I began with a view. It is large enough and proprietary enough, that I am not going to post it here. What is important, though, is that the execution plan had a couple DS_BCAST_INNERs and the total data size which would have been read was over 8 TBs. The tables were less than 1 TB each and we only wanted to query about 4% of the data in the tables.

Given that my process was going to be calling a single view, I bootstrapped all of my tests into a demo view and then experimented with various usages. I always structure my tests down to the atomic level to reach small conclusions and then piece them back together into the more complicated end result.

create or replace view demo
as
select s.*, p.payment_description
from derived.vw_f_order_sale s
left join derived.vw_f_order_payment p on p.message_key = s.message_key
	and p.week_ending_date = s.week_ending_date
;
explain
select *
from demo
where week_ending_date = '2020-02-04'

• I need a field out of the payment fact.
• I need all orders, even if they do not have a payment associated with them.
• Both tables have sort keys on week_ending_date.
• Both tables are distributed on message_key.

XN Hash Right Join DS_DIST_NONE  (cost=418040.16..3914975409.27 rows=790819533 width=1635)
  Hash Cond: (("outer".message_key = "inner".message_key) AND ("outer".week_ending_date = "inner".week_ending_date))
  ->  XN Seq Scan on f_order_payment  (cost=0.00..79081953.28 rows=7908195328 width=16)
  ->  XN Hash  (cost=298600.11..298600.11 rows=23888010 width=1631)
        ->  XN Seq Scan on f_order_sale  (cost=0.00..298600.11 rows=23888010 width=1631)
              Filter: (week_ending_date = '2020-02-04'::date)

Even though, my tables won’t have a 1-for-1 match, I should always have an order if I have a payment. You can see that 330 times the amount of payments are being read than orders. Given that the order payment table has around 7.9 billion records, clearly the sort key is not being used to optimize the filtering of the payment table.

This makes sense, given that the field I filtered by is f_order_sale’s week_ending_date. f_order_payment’s week_ending_date is not even being returned in the view.

create or replace view demo
as
select s.*, p.week_ending_date payment_week_ending_date, p.payment_description
from derived.vw_f_order_sale s
left join derived.vw_f_order_payment p on p.message_key = s.message_key
	and p.week_ending_date = s.week_ending_date
;
explain
select *
from demo
where week_ending_date = '2020-02-04'
	and payment_week_ending_date = '2020-02-04'
;

XN Hash Join DS_DIST_NONE  (cost=424961.17..12787697.68 rows=69134 width=1639)
  Hash Cond: (("outer".message_key = "inner".message_key) AND ("outer".week_ending_date = "inner".week_ending_date))
  ->  XN Seq Scan on f_order_sale  (cost=0.00..298600.11 rows=23888010 width=1631)
        Filter: (week_ending_date = '2020-02-04'::date)
  ->  XN Hash  (cost=303543.69..303543.69 rows=24283496 width=16)
        ->  XN Seq Scan on f_order_payment  (cost=0.00..303543.69 rows=24283496 width=16)
              Filter: (week_ending_date = '2020-02-04'::date)

Now that I have bubbled up the payment_week_ending_date and filtered on it, Redshift successfully filters both tables. This is not an acceptable solution, though. This method effectively turns my query into an INNER JOIN because my join predicates state that the two tables match, except for when my payment record is missing, making its week_ending_date NULL. I’ve just filtered out those orders.

In fact, the INNER JOIN query gives the same execution plan.

create or replace view demo
as
select s.*, p.payment_description
from derived.vw_f_order_sale s
inner join derived.vw_f_order_payment p on p.message_key = s.message_key
	and p.week_ending_date = s.week_ending_date
;
explain
select *
from demo
where week_ending_date = '2020-02-04'

XN Hash Join DS_DIST_NONE  (cost=424961.17..12787697.68 rows=69134 width=1635)
  Hash Cond: (("outer".message_key = "inner".message_key) AND ("outer".week_ending_date = "inner".week_ending_date))
  ->  XN Seq Scan on f_order_sale  (cost=0.00..298600.11 rows=23888010 width=1631)
        Filter: (week_ending_date = '2020-02-04'::date)
  ->  XN Hash  (cost=303543.69..303543.69 rows=24283496 width=16)
        ->  XN Seq Scan on f_order_payment  (cost=0.00..303543.69 rows=24283496 width=16)
              Filter: ('2020-02-04'::date = week_ending_date)

What I want is for Redshift to understand that, because I am filtering by f_order_sale.week_ending_date and I am joining on week_ending_date between the two tables, then my f_order_payment cannot ever produce records which do not match my filter.

Since, I am not getting what I want, let’s play with alternatives.

create or replace view demo
as
select s.*, p.week_ending_date payment_week_ending_date, p.payment_description
from derived.vw_f_order_sale s
left join derived.vw_f_order_payment p on p.message_key = s.message_key
	and p.week_ending_date = s.week_ending_date
;
explain
select *
from demo
where week_ending_date = '2020-02-04'
	and (payment_week_ending_date = '2020-02-04' OR payment_week_ending_date IS NULL)
;

XN Hash Right Join DS_DIST_NONE  (cost=418040.16..3914975579.71 rows=790819533 width=1639)
  Hash Cond: (("outer".message_key = "inner".message_key) AND ("outer".week_ending_date = "inner".week_ending_date))
  Filter: (("outer".week_ending_date = '2020-02-04'::date) OR ("outer".week_ending_date IS NULL))
  ->  XN Seq Scan on f_order_payment  (cost=0.00..79081953.28 rows=7908195328 width=16)
  ->  XN Hash  (cost=298600.11..298600.11 rows=23888010 width=1631)
        ->  XN Seq Scan on f_order_sale  (cost=0.00..298600.11 rows=23888010 width=1631)
              Filter: (week_ending_date = '2020-02-04'::date)

In that one, I tried to save the outer join nature of the query but still filter on the payment_week_ending_date. This could work. Redshift’s query processor is still not filtering on the sort key but it could. My query is giving it enough valid information to do the right thing. There are three row possibilities with the payment table and this query.

1. Row returned on payment for valid match to order and week_ending_date is within the filter.
2. week_ending_date is NULL in the payment table but returns no row anyways because NULL can not be equated to f_order_sale’s week_ending_date.
3. Row not returned on payment because no match was found against order and payment_week_ending_date is NULL, due to the mismatch.

Unless I was to add additional or different join predicates, the filtering remains logically valid but not supported by Redshift.

Conclusion

In the end, I do not have a way to solve this directly. I ended up deciding to break my view into two views and have the process pre-filter both of them before joining them together.

What I’d like to highlight is that the disconnect between what Redshift is doing and what I want is linked directly to the join predicate on week_ending_date. If I only joined on message_key, Redshift’s behavior makes the most sense, because the system cannot predict that my week_ending_dates will ever match. I could have terribly illogical data in the tables. Therefore, it cannot make any logical jump to filtering the other table.

What I demonstrated with the filter column, payment_week_ending_date, which is only used for filtering and not displaying, is an effective tactic in many cases. Such as, when you are performing an inner join but the filter column is not one of the join predicates.

The real challenge is when you have sub-queries that you wish to be filtered within the child query or when you are performing GROUP BYs and wish the data to be pre-filtered. In the case of a view, I do not know if there is anything you can do there. However, I do plan to experiment for a while to figure it out.

Stay tuned for my success or failures in that area.