How to Test Queries in Amazon Redshift

Why test queries in Amazon Redshift?

Testing reveals performance issues before users feel them. It also prevents runaway scans that lock clusters or inflate costs. Redshift provides EXPLAIN and EXPLAIN ANALYZE to preview or benchmark execution plans.

What does EXPLAIN do?

EXPLAIN parses SQL, builds a plan, and returns step-by-step operations with estimated rows, width, and cost. The query never runs, so data remains untouched.

When should I add ANALYZE?

EXPLAIN ANALYZE runs the query and appends actual row counts and timing to each step. Use it on limited datasets or with a LIMIT clause for safe benchmarking.

How do I write the syntax?

EXPLAIN [ANALYZE] [VERBOSE] your_query;

Add VERBOSE to show predicate filters and table statistics.

Example: join Orders and OrderItems

EXPLAIN ANALYZE
SELECT o.id,
o.order_date,
SUM(oi.quantity * p.price) AS order_total
FROM Orders o
JOIN OrderItems oi ON oi.order_id = o.id
JOIN Products p ON p.id = oi.product_id
WHERE o.order_date >= '2024-01-01'
GROUP BY o.id, o.order_date
ORDER BY order_total DESC
LIMIT 10;

The output highlights scan types (DS_DIST_BOTH, HASH JOIN), row counts, and total query runtime.

Best practices for testing

• Always start with plain EXPLAIN to check distribution and join types.
• Add LIMIT to reduce execution time when using ANALYZE.
• Compare estimated vs. actual rows; a large gap signals stale statistics—run ANALYZE or VACUUM.
• Watch for DS_DIST_* steps; redistribute keys or sort keys to minimize.

Common mistakes

Running heavy ANALYZE in production

EXPLAIN ANALYZE executes the query. Without LIMIT, it can consume cluster resources. Use plain EXPLAIN first or test in a development cluster.

Ignoring cost units

Redshift cost numbers are relative, not milliseconds. Focus on step order and high row counts. Combine with STL_QUERY runtime data for full insight.

How to read EXPLAIN output fast?

Start at the deepest indented line—that’s the first operation. Follow upward to see join order. Large row counts early indicate expensive scans.

Can I automate query tests?

Yes. Store SQL in a test suite table and iterate with PL/pgSQL or Python scripts, capturing EXPLAIN plans into logging tables for regression tracking.