Using CTE vs temporary tables

On Wed, Jul 11, 2018 at 9:30 AM, hmidi slim <hmidi.slim2@gmail.com> wrote:

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:
https://dba.stackexchange.com/questions/78253/postgresql-
common-table-expressions-vs-a-temporary-table

Any suggestion will be welcome.

​Consider views and functions too.

David J.

​Does temp tables also suffer from optimization fence we see in CTE.​

On Wed, Jul 11, 2018 at 9:35 AM, Ravi Krishna <sravikrishna3@gmail.com>
wrote:

​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