Speeding up a Redshift report when the query and schema are fixed

Most advice about a slow query starts with the query: rewrite the join, filter earlier, stop selecting columns nobody reads. The rest of the advice starts with the schema: denormalize, split the table, build a summary table and point the report at it.

I had a report that took over fifteen minutes to open, and neither of those moves was available. It now opens in two tenths of a second, and everything that made it faster happened below the query and the schema.

The client supplies fashion accessories to large American retail chains, and those chains read their own sales data through a BI tool: how their products are moving, where and when. This is reporting for customers, and the report is part of what the client sells.

That tied two hands at once. The queries belong to the BI tool, which generates the SQL from whatever the user clicks. There's no file to open and no statement to rewrite. You can read the queries, but you can't edit them.

The schema belongs to the customers, in practice if not legally, because their saved reports are built on it. Rename a column, split a table or change a type, and every customer with a saved report has to rebuild their work. For a product whose value is that customers can open their numbers whenever they like, that isn't a migration cost. It's a support conversation with every customer you have.

So the SQL arriving at the database had to stay identical, and so did the names and shapes it refers to. Everything below that line was fair game, and that turned out to be a lot. "We can't change anything" is almost never true once you write down exactly what is fixed.

A query names tables and columns. It says nothing about which node a row lives on, what order rows sit in on disk, whether an aggregate was computed in advance, or which engine answers. The query stays fixed, but almost everything about how it gets answered can change.

The data came from MySQL, and the heavy reports aggregated over more than 200 million products. That's a workload for a columnar warehouse, and the question was which one.

I chose Redshift, and not because of a feature comparison. Snowflake and BigQuery were just as capable. The client's direction decided it: they were moving every service to AWS, and a warehouse inside the same cloud as the rest of the system means simpler transfer, one account, one access model and one team maintaining it.

When candidates are this close on features, the difference you feel a year later comes from the number of accounts, access models and teams involved, and rarely from the feature matrix.

There was no migration in the usual sense either. Jobs that already existed export from the source system to object storage, and my layer picks those exports up and loads them:

1COPY sales.product_sales2FROM 's3://example-exports/product_sales/'3IAM_ROLE 'arn:aws:iam::123456789012:role/warehouse-load'4FORMAT AS CSV5GZIP6IGNOREHEADER 1;

Leaving the upstream jobs alone was the same constraint in a different place: the less the source side had to change, the fewer people had to agree before anything got faster.

The first thing still free to change is physical layout. In Redshift that's two decisions: the distribution key, which decides which node each row lives on, and the sort key, which decides the order rows are stored in, so that whole blocks can be skipped when a filter rules them out.

The tempting way to choose them is to read the schema: this is the primary key, that's a foreign key, this column looks like a date. That produces a warehouse that looks sensible and is merely expensive.

The right input is the queries the system runs. With a BI tool in front of the database this is easier than it sounds, because generated SQL is repetitive by nature. The same few shapes come back again and again, and the query history shows you which ones cost the most:

1-- what the BI tool actually sends, most expensive first2SELECT3  LEFT(query_text, 200)       AS query_start,4  COUNT(*)                    AS runs,5  AVG(elapsed_time) / 1000000 AS avg_seconds6FROM sys_query_history7WHERE user_id = (SELECT usesysid FROM pg_user WHERE usename = 'bi_reader')8  AND query_type = 'SELECT'9  AND start_time > DATEADD(day, -14, GETDATE())10GROUP BY 111ORDER BY COUNT(*) * AVG(elapsed_time) DESC12LIMIT 20;

From that list you read which columns the heavy queries join on and which they filter by.

Join columns are candidates for the distribution key. When both sides of a frequent join are distributed on the same column, matching rows already sit on the same node and the join doesn't shuffle data across the cluster.

Filter columns are candidates for the sort key. In customer-facing reporting that's usually who the customer is and which period they're looking at. Sort on those, and a query for one retailer's last quarter reads the blocks that hold it and skips the rest.

Neither change touches the schema as the BI tool sees it:

1ALTER TABLE sales.product_sales ALTER DISTKEY product_id;2ALTER TABLE sales.product_sales ALTER COMPOUND SORTKEY (retailer_id, sale_date);

The table, its columns and their types stay the same, so every saved report still resolves. The rows are simply somewhere better, and choosing where cost nothing but the time it took to read the query history.

Layout helps every query, but on its own it doesn't turn fifteen minutes into two tenths of a second. The heaviest reports aggregated over the full history every time someone opened them, so the fix had to stop that work from being redone on every open.

So I moved the heaviest reports onto materialized views. The aggregate is computed ahead of time and stored, and the report reads the result instead of recomputing it. The views refresh incrementally, so a refresh processes only what changed. Some are built as dependent views on top of others, so shared intermediate work is done once.

1CREATE MATERIALIZED VIEW reporting.mv_retailer_weekly2DISTKEY (retailer_id)3SORTKEY (retailer_id, week_start)4AS5SELECT6  s.retailer_id,7  DATE_TRUNC('week', s.sale_date) AS week_start,8  s.product_id,9  SUM(s.units)   AS units,10  SUM(s.revenue) AS revenue11FROM sales.product_sales s12GROUP BY 1, 2, 3;13 14-- after the daily load15REFRESH MATERIALIZED VIEW reporting.mv_retailer_weekly;

The refresh runs daily, because the data arrives daily. Refreshing more often would recompute the same numbers.

The catch is that the BI tool's SQL names the original tables. If you build a materialized view and nothing points at it, you have a fast object that no query reads.

The way around it is the idea behind the rest of the work: leave the names the tool and the customers see untouched, and change what they point at. In Redshift there are two general ways to do that without editing a single query. The engine can rewrite an incoming query to read from a materialized view that can answer it. Or the name the tool queries can itself be a view with exactly the same columns, reading from something else underneath. Either way the customer sees the same tables and columns, and what they resolve to has changed.

Whichever route you take, check that it works. Run EXPLAIN on the tool's own SQL, copied from the query history, not on a cleaned-up version you wrote yourself. If the plan doesn't read the precomputed data, the views aren't doing anything.

The failure I was most wary of was a fast report whose numbers had drifted from the source. That is worse than a slow report, because nobody notices, and a customer makes a decision with it.

Precomputation creates that risk. A view is a copy, and copies drift: a refresh fails without anyone looking, a late load lands after the refresh ran, or a definition changes on one side and not the other.

So a validation script proves the view and the source still agree. The check is simple: compute the same aggregate from the source and from the view, and list every place they differ.

1WITH src AS (2  SELECT retailer_id, SUM(units) AS units, SUM(revenue) AS revenue3  FROM sales.product_sales4  GROUP BY 15),6mv AS (7  SELECT retailer_id, SUM(units) AS units, SUM(revenue) AS revenue8  FROM reporting.mv_retailer_weekly9  GROUP BY 110)11SELECT12  COALESCE(src.retailer_id, mv.retailer_id) AS retailer_id,13  src.units   AS source_units,   mv.units   AS view_units,14  src.revenue AS source_revenue, mv.revenue AS view_revenue15FROM src16FULL OUTER JOIN mv ON mv.retailer_id = src.retailer_id17WHERE src.retailer_id IS NULL18   OR mv.retailer_id IS NULL19   OR src.units   <> mv.units20   OR src.revenue <> mv.revenue;

No rows means they agree. Any row means the report shouldn't be trusted until someone has looked. The check runs every day, so you know the view matches the source instead of assuming it does.

Below one second, people stop requesting a report and start exploring it: they change a filter, compare a period and ask the next question in the same conversation. That matters more than the number itself, because it makes the report a different product.

Daily refresh was right for this data, which arrives daily anyway. But the moment someone asks for fresher numbers, the design has no answer short of rebuilding the refresh path.

I'd build the rollup so the interval could change without reworking it. Freshness is a product decision that looks like a technical one, and the person who will eventually ask for a different interval isn't the person who chose the first one.