CTEとは「Common Table Expressions」のこと。ざっくり言えば「With句」のこと。
PostgresでWith句を使用していると遅いケースがあったので、Temporary view 作って対応したほうがいいのだろうかと思い「CTE vs View」で情報収集。
そしたら、こちらのサイトにとても有益な情報が！すばらしい！
結論
Postgres12以降なら「パフォーマンスはCTE≒View」なので、再利用性が高いならView、それ以外ならCTEでいいと思います。 注意点もあるのでそこは詳細を見て判断ください。
なお、Postgres12は2019年10月にリリースされています。
要約
- Postgres11以前は、DTE（With句）を使用すると必ずマテリアライズ（メモリ、ディスクにクエリ結果を保存する）が起きる。
- Postgres12はオプティマイザが賢くなって、マテリアライズの要・不要を判定するようになった。
Postgres12なら勝手にパフォーマンスを改善してくれる！すばらしい！
マテリアライズ回避方法
どんな場合でもCTEを非マテリアライズ化するわけではありません。以下の条件をすべて満たしている必要があります。
1.同じCTEが2回以上使われていない
2.CTE中に非immutable関数が使われていない
(1)は、同じCTEを2回以上使うのであれば、マテリアライズしてそれを使い回したほうが良いだろう、というのが理由です(必ずしもそうとは言い切れないので、その場合は後述の方法でマテリアライズを避けることができます)。 非immutable関数を使用している場合は必ずマテリアライズされますが、同じCTEが2回以上使われている場合であれば、キーワードを付けることでマテリアライズを回避できます。
回避策も用意されている！すばらしい！
構文
公式サイト参照。（NOT MATERIALIZE キーワード）
Googleで「Postgres With句」と検索するとバージョン12以前の情報がHITすることもあるので、バージョン確認大事。
非immutable関数とは？
ついでに「非immutable関数」というものが何なのかというと、こちらの資料が参考になります。
www.slideshare.net
特定の関数が「非immutable関数」かどうかを調べたい場合は、こちらのクエリで確認できます。
select provolatile, proname from pg_proc
