Are PostgreSQL subqueries / CTE cached?

user_2010246 0
Viewed 2357

By cached I mean if there is a call to subquery for every fetched row?

Few examples

-- Simple query
SELECT * FROM (
    SELECT * FROM accounts
) AS subquery;

-- Subquery with function
SELECT * FROM (
    SELECT AVG(id_groups) FROM accounts
) AS subquery;

-- More messy queries
SELECT * FROM (
  SELECT id_groups, AVG(id_accounts) OVER (PARTITION BY id_groups)
  FROM accounts
) AS subquery
GROUP BY subquery.id_groups, subquery.avg;

And moreover

Is there a difference between

SELECT * FROM (
    SELECT * FROM accounts
) AS subquery;

And

WITH everything_about_accounts AS (
    SELECT * FROM accounts
)
SELECT * FROM everything_about_accounts;
subquery query-performance common-table-expression postgresql
1 Answers

About the subqueries in the FROM clause:

The subquery will not be executed for each row returned; that would make no sense in these cases.

In the three cases you present, PostgreSQL will even flatten the subquery: the optimizer realizes that the subquery is unnecessary and transforms the queries accordingly.

Use EXPLAIN with your queries to see that in action.

About the CTE:

Different from subqueries in the FROM clause, a CTE acts as an optimization barrier, that is, the optimizer does not try to flatten it or push conditions into it.

Rather, the CTE is executed and the result materialized, and the query executes a CTE scan on the materialized result.

Again, use EXPLAIN to see it in action.

There are efforts in the PostgreSQL community to remove the limitation (or feature, because it is a way to guide the optimizer) that CTEs are always materialized.

user_64643080
Related