Advantage of 'with' queries over plain 'select' and joins?

Question

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, 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. — πάντα ῥεῖ, Jul 8, 2019 at 18:25

Michael B · Accepted Answer · 2019-07-08 17:44:59Z

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/

answered Jul 8, 2019 at 17:44

Michael B

5192 silver badges12 bronze badges

Add a comment |

1 Answer 1