4 ms·

Instead of doing this madness with complicated queries, I always advise to split complicated queries into many smaller simple queries and then merge the results

Instead of doing this madness with complicated queries, I always advise to split complicated queries into many smaller simple queries and then merge the results in the code. This way you don't need to debug the queries as all of them are very simple SELECTs and the code that merges the results is one or two for loops.

cube2222 5y ago
That's more custom logic and additional code to maintain though. In this case we're using the Datadog Agent with its custom_queries directive to automatically create metrics based on periodically evaluated SQL queries.
Tostino 5y ago
That can sometimes be a solution... Just keep in mind, you are manually writing a join algorithm every time you need to do that, and sometimes it will be the right one for the task, other times not so much. The query planner will adapt your join type for you instead, if you can trust it. As the shape of your data changes over time, your hard coded algorithm choice is not guaranteed to be the best one.
ccleve 5y ago
This. Plus, you can use temporary tables, which limits the amount of application code you have to write. Put your intermediate results in temp tables, and build indexes on them if you have to. It's a cheap way of forcing the optimizer to do things your way.
cultofmetatron 5y ago
in practice that means mroe data being sent to the application code that then has to process that. 99% of the time, you'll get more performance figuring out the right way to express the data structure you want in sql. The entire computation happens in the database so all the data is immediately available to process on disk and you only send what you need for bandwidth consumption. IF the query is slow, you need to think about how you setup your joins and being aggressive about your use of predicates in subqueries to reduce the size of temporary tables.
- chrisjc 5y ago
  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.
takeda 5y ago
That won't always work. Main reason for bigger queries is to get exact data you need, so you don't have to send unnecessary data over the wire which is slow.
jsumrall 5y ago
Sounds like just using CTEs. This is how I write some complicated queries, as a few CTEs with specific logic to get an overall query written in a more readable way.

cube2222 5y ago

That's more custom logic and additional code to maintain though. In this case we're using the Datadog Agent with its custom_queries directive to automatically create metrics based on periodically evaluated SQL queries.

Tostino 5y ago

That can sometimes be a solution... Just keep in mind, you are manually writing a join algorithm every time you need to do that, and sometimes it will be the right one for the task, other times not so much. The query planner will adapt your join type for you instead, if you can trust it. As the shape of your data changes over time, your hard coded algorithm choice is not guaranteed to be the best one.

ccleve 5y ago

This. Plus, you can use temporary tables, which limits the amount of application code you have to write. Put your intermediate results in temp tables, and build indexes on them if you have to. It's a cheap way of forcing the optimizer to do things your way.

cultofmetatron 5y ago

in practice that means mroe data being sent to the application code that then has to process that. 99% of the time, you'll get more performance figuring out the right way to express the data structure you want in sql. The entire computation happens in the database so all the data is immediately available to process on disk and you only send what you need for bandwidth consumption. IF the query is slow, you need to think about how you setup your joins and being aggressive about your use of predicates in subqueries to reduce the size of temporary tables.

chrisjc 5y ago
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.

takeda 5y ago

That won't always work. Main reason for bigger queries is to get exact data you need, so you don't have to send unnecessary data over the wire which is slow.

jsumrall 5y ago

Sounds like just using CTEs. This is how I write some complicated queries, as a few CTEs with specific logic to get an overall query written in a more readable way.