CTEs have similar performance to derived tables. Not always materialized. Check execution plan. Use MATERIALIZED hint in PostgreSQL if needed.

CTEs have similar performance to derived tables - not inherently faster or slower

Check execution plan to see if CTE is materialized or inlined

Use MATERIALIZED hint (PostgreSQL) if CTE used multiple times

Filter early in CTEs to reduce data volume

Index columns in base tables used by CTEs

Don't assume CTE executes only once - check execution plan

For very large datasets with multiple uses, consider temp table with indexes

CTEs can't have indexes - optimization happens on base tables

Test performance with realistic data volumes

Profile before and after converting subqueries to CTEs

Assuming CTEs are always materialized (behavior varies by database)

Using cryptic names like cte1, cte2, temp (use descriptive names)

Overusing CTEs for simple queries that don't need them

Not testing individual CTEs before combining

Forgetting that later CTEs can reference earlier ones

Using multiple WITH keywords instead of comma separation

Not considering temp tables when indexes would help

Assuming CTEs are faster than subqueries (usually same performance)

Creating overly complex CTE chains that are hard to debug

Not checking execution plan to verify optimization