[jpalomaki]

Sometimes easy performance trick is to split the CTE to separate queries, put the results to unlogged temporary tables and add whatever indexes the next step needs.

Obviously makes only sense for stuff like analytical queries that are not running constantly.

[taeric]

Worth underlying the OLAP versus OLTP divide you are speaking to on the close, there.

[andsbf]

An issue that has arise for me in some situations is that for more expensive/reporting queries we point to a db replica, where temporary tables are not an option.