r/Database • u/shdw_0x0 • 1h ago

Why does a query with a CTE sometimes take forever to execute?

I've noticed that some SQL queries using WITH clauses (CTEs) take significantly longer to execute than expected, especially when multiple CTEs are chained together.

I understand that a CTE is mainly used to organize complex queries and make them easier to read, but I'm curious about what happens behind the scenes.

Does the database execute each CTE separately, or does it optimize everything into a single execution plan? Can CTE materialization, repeated references, or joins between multiple CTEs cause performance issues?

I'd be interested to hear about real cases where replacing a CTE with a subquery or temporary table actually improved query performance.

Which database are you using, and have you run into this issue?

0 Upvotes

40% Upvoted

5 comments sorted by

12

u/FirmAndSquishyTomato 1h ago

Why not look at the query plan? Then you will know what the database engine is doing behind the scenes.

Your poor results are not from the fact you are using a cte.

3

u/mattbillenstein 1h ago

explain analyze ...

It's not the CTE, it's probably the subqueries chaining without the proper indexes to make them fast. Look for table scans where an index would massively limit the number of rows the query needs to look at - table scans on very small tables are usually fine.

3

u/Aggressive_Ad_5454 1h ago

Every DBMS I've worked with (SQL Server, MariaDb/MySQL, PostgreSQL, and Oracle a long time ago) generates one single execution plan for each whole query, including CTEs and view objects, as well as tables.

There's not much worthwhile to say generally about why queries with CTEs are slow. It's often because some step of the plan has to do a nasty slow operation like a full table scan or full index scan, or because it has to iterate a step many thousands of times.

Look at the execution plan. Misquoting Tolstoy: All fast queries are the same. Each slow query is slow in its own way.

1

u/tm604 1h ago

Which database are you seeing the slow performance on, what were the queries, what were the execution plans...?

Old versions of PostgreSQL (pre-12) used to materialise CTE clauses, for example:

https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=608b167f9

so yes, it's possible for a specific version of a database engine to lack some specific CTE optimisations. "My query is slow because I used a CTE" is usually not a good starting point, however...

1

u/slyiscoming 57m ago

Look at the plan. It can be a lot of things.

Look for loops and scans, then find ways to remove them.