Assuming a multi-read/multi-write CTE, what exactly is held in memory during the life of the statement?
Is all data except RETURNING data discarded at the end of each sub-statement?
PostgreSQL – Memory Usage During Life of a CTE
memoryperformancepostgresqlpostgresql-performance
Related Question
- PostgreSQL Performance – Understanding Prepared Statement Overhead
- Sql-server – What does Page Life Expectancy say about the instance
- Postgresql – Query for a table with paging and filtering vs. CTE (common table expression)
- PostgreSQL – Memory Usage on SELECT for Large Number of Rows
- PostgreSQL – Recommended Way to Join Junction Tables for Efficient Ordering/Pagination
- SQL Server Performance – High Checkpoint Pages/sec and Memory Pressure
Best Answer
A CTE term's output is accumulated into a TupleStore. A TupleStore may internally be in-memory or may spill to disk. See
src/include/utils/tuplestore.handsrc/backend/utils/sort/tuplestore.c, particularly the comment at the top oftuplestore.c.For a wCTE that'll be the
RETURNINGoutput for that term.To determine whether anything else is retained requires analysis of the memory contexts in which each CTE term is executed, as PostgreSQL uses a hierarchical memory context based allocator. Here you need to look at
src/backend/executor/nodeCtescan.c. It's pretty complicated, but a read ofCteScanNextsuggests that the queries are often executing concurrently, with one query consuming the results of another.So. While memory might be freed at the end of each sub-statement (CTE term) there's no gurarantee that any of the CTE terms finish at any particular time.
Rephrasing your question:
the answer is "no".
However, that doesn't mean PostgreSQL will keep piles of data in memory. It frequently discards rows as soon as it's done with them and never needs to see them again. It can spill big temporary results to tempfiles on disk. If it materialises, sorts or aggegates query output it will generally throw away the un-aggregated/un-sorted data after it finishes. etc.