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 – a_horse_with_no_name Jul 8 '19 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. – πάντα ῥεῖ Jul 8 '19 at 18:25

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/

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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