Skip to main content

Redshift CTAS slower than SELECT

Introduction

Redshift CREATE TABLE AS SELECT (CTAS) operations can take longer than the equivalent SELECT query because CTAS has to write and materialise the result set as a table. In Kleene, this can make a query appear slow or stuck even when the SQL execution itself is performing well. The delay is often caused by table creation and data writing rather than the logic of the query.

Issue description

A Redshift query completed quickly when run as a normal SELECT, but took much longer when run as CREATE TABLE AS SELECT:

SELECT completed in <1 minute
CREATE TABLE AS SELECT exceeded the 2-minute SQL console timeout

The issue was not that the query logic was slow. The investigation showed that the query execution completed quickly, but the CTAS operation needed additional time to write the output table.

Signs

You may be dealing with this issue if a SELECT query returns results quickly, but the same SQL takes much longer when wrapped in a CTAS statement. This is especially likely when the query exceeds a console timeout, but does not necessarily fail with a SQL error.

The key sign is that query execution and table materialisation are being treated as one operation. The SELECT portion may complete quickly, while the table creation step continues running in the background or exceeds the interactive console limit.

Basic troubleshooting steps

Start with the following checks to narrow down the cause of the delay:

  • Run the core query as a normal SELECT and confirm how long it takes to return results.
  • Compare that runtime with the CREATE TABLE AS SELECT runtime.
  • Confirm whether the delay happens during query execution or during output table writing.
  • Review the size of the result set being materialised.
  • Check whether the destination table creation step is writing a large amount of data.
  • If available, run the same logic as a transform rather than through the SQL console.

Note: the SQL console has a fixed 2-minute timeout on queries.

Common causes and how to fix them

CTAS includes table materialisation

A normal SELECT only needs to execute the query and return results. A CTAS statement must also create a table and write the full output into storage. This extra materialisation step can make CTAS slower than the equivalent SELECT.

How to fix it: treat CTAS runtime as query execution plus table writing. If the SELECT is fast, focus the investigation on the output size, table creation process, and execution environment rather than immediately rewriting the query.

SQL console timeout

In one case, the CTAS operation exceeded the 2-minute SQL console timeout. This does not necessarily mean the query itself was inefficient. It may simply mean the console is not the right place to run a table-writing operation.

How to fix it: run the CTAS as a transform instead of through the SQL console. Transforms are designed for longer-running materialisation tasks and are not limited by the same short timeout.

Large output table

Even when a query is fast, writing the final output table can take longer if the result set is large. The database needs to allocate storage, write rows, and complete the table creation process.

How to fix it: check the number of rows and columns being written. If the output is larger than expected, reduce the result set, filter earlier, or materialise only the required fields.

Misinterpreting CTAS runtime as query runtime

A CTAS runtime can be misleading because it combines two stages: generating the result and writing the result. If the CTAS is slow, it is important to determine which stage is causing the delay.

How to fix it: test the SELECT independently first. If the SELECT completes quickly, the bottleneck is likely table materialisation rather than query execution.

Practical troubleshooting workflow

  1. Run the SQL as a plain SELECT and record how long it takes to return results.
  2. Run the same logic as CREATE TABLE AS SELECT and compare the runtime.
  3. Check whether the CTAS exceeds an interactive SQL console timeout.
  4. If the SELECT is fast, treat the query logic as likely healthy and focus on the table-writing step.
  5. Review the expected size of the output table.
  6. Run the CTAS as a transform where possible, especially if the SQL console has a short timeout.
  7. Confirm that the transform completes successfully outside the console timeout.
  8. If the transform is still slow, investigate output size, distribution, sorting, and any downstream table-writing constraints.

Best practices to avoid CTAS timeout confusion

  • Test the query logic with a plain SELECT before running CTAS.
  • Remember that CTAS includes both query execution and table materialisation.
  • Avoid using short-timeout SQL consoles for operations that need to write full tables.
  • Use transforms for CTAS jobs that may take longer than the interactive console limit.
  • Check output row counts before materialising large result sets.
  • Keep result tables limited to the fields and rows actually needed.
  • Document when a slow CTAS is expected because the bottleneck is writing the table, not executing the query.

Additional information

For this issue, the recommended fix was to run the CTAS as a transform rather than through the SQL console. The investigation concluded that the query execution itself was fast, and the delay was caused by table materialisation.

If the same pattern appears again, first compare SELECT runtime with CTAS runtime. If the SELECT completes quickly but CTAS times out, the issue is likely the table-writing step or the console timeout rather than a problem with the SQL logic.

Related to

Powered by Zendesk