Are there any advantages using WITH queries instead of simple SELECT queries using JOINs performance wise?

I am asking solely about reading data (from catalogues specifically).

  • Sometimes a CTE will make things slower, sometimes it will make it faster
    – user1822
    Commented Jul 8, 2019 at 17:49
  • @a_horse_with_no_name As I already suspected, my question seems to be too unspecific / broad. I can provide some more specific example regarding a query I saw from a colleagues code querying the ANSI information schema about column definitions (DEFAULT and sequences specifically) tomorrow. I am more used to get the information from the native pg_cataolg and build up queries as neccesary from there. I have no clue how queries will be efficient using (the views(?) provided for the ANSI information schema) might impact the performance. Commented Jul 8, 2019 at 18:25

1 Answer 1


Postgres can treat queries in the with clause as optimization fences. Meaning that an expensive subquery will not necessarily be recomputed twice when visited. https://www.2ndquadrant.com/en/blog/postgresql-ctes-are-optimization-fences/

  • That sounds promising, THX for your answer. Commented Jul 8, 2019 at 17:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.