Derik Hammer takes us through query tuning a nasty job on Amazon Redshift:
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 [complexity?, ed.] 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.
Click through for more details and what Derik ended up doing.