3 ms·
You usually can manage this by dividing your query into subqueries each creating some temp table, so you have control over how joining actually happens.
by riku_iki 4y ago
You usually can manage this by dividing your query into subqueries each creating some temp table, so you have control over how joining actually happens.
- derefr 4y agoIt'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.)
- taffer 4y ago> 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]. [1] https://www.postgresql.org/docs/current/runtime-config-resource.html#GUC-TEMP-BUFFERS https://www.postgresql.org/docs/current/runtime-config-resou...
- riku_iki 4y agothat one is for some access buffers and not actual temp tables?
- taffer 4y agoNo temp_buffers is for temp tables and materialized CTEs. Maybe you are confusing it with shared_buffers?
- riku_iki 4y agoI 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.
- derefr 4y agoHuh, 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.
- SigmundA 4y agoOr there could just be a hint that says do it in source code order: https://learn.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-query?view=sql-server-ver16#force-order https://learn.microsoft.com/en-us/sql/t-sql/queries/hints-tr... There could also be hints that tell what kind of join to use and which indexes to use!
- aeyes 4y agoIf you do this be careful what your temp_buffers is set to so that your temp tables don't spill to disk. If you are on a network file system, for example on AWS RDS, writing big (a few hundred MB) temp tables to disk will stall all transactions. The same is of course true when you have big joins that don't fit in work_mem but default size of this will be much larger. I can usually fix bad plans with CTEs, no need to get much fancier. And the problem is often caused by schema design where you have a mapping table of two tables in the middle and your join is N-M-M where the planner has no information about the relationship between the two outer tables.
- riku_iki 4y ago> temp tables don't spill to disk my setup is local nvme ssd raid, so I hope this part won't be bottle neck. Also, if you are doing heavy join, where join order and method is need to be controlled, you temp table likely will be large, so you will need to be ready to have disk io.