Обсуждение: Using CTE vs temporary tables
Hi,
I have a big query that used about 15 cte and its execution time is acceptable. I'm trying to optimize my query because it contains about 150 lines of code and becomes hard to understand it and add new filter or condition easily.
I think to change some cte with temporary tables and using indexes. I found this answer about performance between CTE and temp tables:
Any suggestion will be welcome.
Best regards.
Hi,I have a big query that used about 15 cte and its execution time is acceptable. I'm trying to optimize my query because it contains about 150 lines of code and becomes hard to understand it and add new filter or condition easily.I think to change some cte with temporary tables and using indexes. I found this answer about performance between CTE and temp tables:Any suggestion will be welcome.
Consider views and functions too.
David J.
Does temp tables also suffer from optimization fence we see in CTE.
Does temp tables also suffer from optimization fence we see in CTE.
I suppose it depends on how they end up being referenced in the query. It is not possible for the auto-vacuum daemon to vacuum/analyze them so if you aren't doing that manually there will be a different kind of problem (bad stats) preventing the query from being executed efficiently. But in terms of the "barrier", no, they are no different than any other table added to a query FROM list.
David J.
One thing to note, if this is a query you would like to run on a replica, temp tables are a non-starter.
I really wish that wasn't the case. I have quite a few analytical queries I had to optimize with temp tables and indexes, and I really wish I could run on my hot standby.
I in most cases I can't refactor them to use a CTE for performance reasons.
Anyways, my 2¢.
- Adam