3 ms·

Not in every case. Some DBs allow you to reference the results and use the result reference in subsequent queries. As a result, data stays in the DB until you'r

Not in every case. Some DBs allow you to reference the results and use the result reference in subsequent queries. As a result, data stays in the DB until you're actually ready to pull it. Or of course, you can just use temp tables.

cultofmetatron 5y ago
thats a nice feature. does postgres have it? in that case, its even MORE performant to push your data processing into the database via a query
- chrisjc 5y ago
  I'm not too sure about Postgres honestly. I was specifically talking about Snowflake, although there might be others that do it too. https://docs.snowflake.com/en/sql-reference/functions/result_scan.html https://docs.snowflake.com/en/sql-reference/functions/result... Sometimes you can follow this pattern and never actually pull data into your application, which is especially true when you're doing ETLs (table --> table).
- jdmichal 5y ago
  That's pretty much equivalent to just using materialized WITH clauses, no? Those act as an optimization barrier and are materialized in order before the query is run.
  - chrisjc 5y ago
    > materialized WITH clauses Are you talking about CTEs? And if so, are you suggesting that each "temp table" in the CTE is calculated/materialized a step at a time? If so, I see the query optimizer tear these barriers down and fusing all of the SQL together on a daily basis.
    - jdmichal 5y ago
      Not if you use `MATERIALIZED`: https://www.postgresql.org/docs/14/queries-with.html#id-1.5.6.12.7 https://www.postgresql.org/docs/14/queries-with.html#id-1.5.... > However, if a WITH query is non-recursive and side-effect-free (that is, it is a SELECT containing no volatile functions) then it can be folded into the parent query, allowing joint optimization of the two query levels. By default, this happens if the parent query references the WITH query just once, but not if it references the WITH query more than once. You can override that decision by specifying MATERIALIZED to force separate calculation of the WITH query, or by specifying NOT MATERIALIZED to force it to be merged into the parent query. The latter choice risks duplicate computation of the WITH query, but it can still give a net savings if each usage of the WITH query needs only a small part of the WITH query's full output. Before PG12 this used to be the only way CTEs worked. Now most CTEs don't need to be materialized, but you can force it and reestablish the optimization barrier.

cultofmetatron 5y ago

thats a nice feature. does postgres have it? in that case, its even MORE performant to push your data processing into the database via a query

chrisjc 5y ago
I'm not too sure about Postgres honestly. I was specifically talking about Snowflake, although there might be others that do it too. https://docs.snowflake.com/en/sql-reference/functions/result_scan.html https://docs.snowflake.com/en/sql-reference/functions/result... Sometimes you can follow this pattern and never actually pull data into your application, which is especially true when you're doing ETLs (table --> table).
jdmichal 5y ago
That's pretty much equivalent to just using materialized WITH clauses, no? Those act as an optimization barrier and are materialized in order before the query is run.
- chrisjc 5y ago
  > materialized WITH clauses Are you talking about CTEs? And if so, are you suggesting that each "temp table" in the CTE is calculated/materialized a step at a time? If so, I see the query optimizer tear these barriers down and fusing all of the SQL together on a daily basis.
  - jdmichal 5y ago
    Not if you use `MATERIALIZED`: https://www.postgresql.org/docs/14/queries-with.html#id-1.5.6.12.7 https://www.postgresql.org/docs/14/queries-with.html#id-1.5.... > However, if a WITH query is non-recursive and side-effect-free (that is, it is a SELECT containing no volatile functions) then it can be folded into the parent query, allowing joint optimization of the two query levels. By default, this happens if the parent query references the WITH query just once, but not if it references the WITH query more than once. You can override that decision by specifying MATERIALIZED to force separate calculation of the WITH query, or by specifying NOT MATERIALIZED to force it to be merged into the parent query. The latter choice risks duplicate computation of the WITH query, but it can still give a net savings if each usage of the WITH query needs only a small part of the WITH query's full output. Before PG12 this used to be the only way CTEs worked. Now most CTEs don't need to be materialized, but you can force it and reestablish the optimization barrier.