It's a shame, then, that there's no way to define at-first-purely-in-memory tables, which only "spill" to disk if they cause your query to exceed work_mem.
Within PL/pgSQL, CREATE TEMPORARY TABLE is still (sometimes a lot!) slower than just SELECTing an array_agg(...) INTO a variable, and then `SELECT ... FROM unnest(that_variable) AS t` to scan over it. (And CTEs with MATERIALIZED are really just CREATE TEMPORARY TABLE in disguise, so that's no help.)
> It's a shame, then, that there's no way to define at-first-purely-in-memory tables, which only "spill" to disk if they cause your query to exceed work_mem.
But isn't this exactly how temp tables work? A temp tabe lives in memory and only spills to disk if it exceeds temp_buffers[1].
Huh, I think you're right... but it's still slower! I've definitely measured this effect in practice.
Just spitballing here — I think the difference might come from where the metadata required to treat the table "as a table" in queries has to be entered into, and the overhead (esp. in terms of locking) required to do so.
Or, perhaps, it might come from the serialization overhead of converting "view" row-tuples (whose contents might be merged together from several actual material tables / function results) into flattened fully-materialized row-tuples... which, presumably, emitting data into an array-typed PL/pgSQL variable might get to skip, since the handles to the constituent data can be held inside the array and thunked later on when needed.
I believe the metadata about them is still written to various system catalog tables. Creating lots of temp tables will cause autovacuum activity on tables like pg_attr, for example.
I am trying to say that your link says it is for access buffer for accessing temp table, and it doesn't say actual temp table is stored in that buffer and not flushed on the disk.
Within PL/pgSQL, CREATE TEMPORARY TABLE is still (sometimes a lot!) slower than just SELECTing an array_agg(...) INTO a variable, and then `SELECT ... FROM unnest(that_variable) AS t` to scan over it. (And CTEs with MATERIALIZED are really just CREATE TEMPORARY TABLE in disguise, so that's no help.)