4 ms·

TBH this is a good case for Linq-style queries, over SQL-based ones. Linq style allows you to narrow the query deterministically vs Sql eg. Mytable1.select().wh

TBH this is a good case for Linq-style queries, over SQL-based ones. Linq style allows you to narrow the query deterministically vs Sql eg. Mytable1.select().where().join(mytable2) This applies the filter before the join, compared with: Select from mytable1 join mytable2 on... Where... Which relies on the query plan to resolve the correct application of where, and hope it puts the where before the join. This becomes more important in the cloud, where Iceberg tables cannot be benchmarked in the same way as single-cluster relational DBs.

brudgers 1y ago
When you are willing to pay for your RDBMS, you are likely to get an optimizer that does algebra for you (and does algebra pretty well). On the other hand, if you are hand coding the query, you are hand coding whether you use SQL, Linq, or anything else. And a strength of SQL is a robust ecosystem of documentation, tools, employment candidates, and consultants.
- gigatexal 1y ago
  exactly, one often gets what they pay for. databases are complicated beasts and tens of thousands if not more working years of the smartest people have been working on them since the 70s
  - hyperpape 1y ago
    > databases are complicated beasts and tens of thousands if not more working years of the smartest people have been working on them since the 70s Yes, but they've also set themselves a much harder problem. Instead of "find the proper execution for this particular query, armed with all the knowledge of the database you have" they have the problem of "find the proper execution for all possible queries, using only information that's encoded in DB statistics." So it's no surprise that it's quite easy to find places the optimizer does silly things. To take one instance that bit me recently: or clauses representing different tables cannot be optimized well at all in postgres (https://www.cybertec-postgresql.com/en/avoid-or-for-better-performance/ https://www.cybertec-postgresql.com/en/avoid-or-for-better-p...). I'd still rather have optimizers than not--the optimizer does well most of the time, and my current work has far too many queries to hand-optimize. But you do end up seeing a lot of poorly optimized queries.
    - gigatexal 1y ago
      That’s right. The better one is able to help the optimizer the better life is. It’s an art not a science.
gigatexal 1y ago
but SQL execution even in a dumb planner is FROM then WHERE then .... and then finally SELECT and ORDER so I don't get it
- gonzalohm 1y ago
  But a smart query planner would move the where condition into the JOIN condition
  - Sesse__ 1y ago
    Depending on the join type, moving it up into the join (instead of keeping it on the table) would be either irrelevant or actively harmful.
hobofan 1y ago
Both SQL and Linq-style queries end up in the same in-memory representation once they hit the query engine/query planner. Filter pushdown ("correct application of where, and hope it puts the where before the join") is table stakes for a query planner and has been for decades. And no, Iceberg tables are not special in any way here. Iceberg tables contain data statistics, just like the ones described in the article to make the optimizer choose the right query plan.
- Sesse__ 1y ago
  Filter pushdown is surprisingly subtle, when you start getting into issues of non-inner joins and multiple equalities. I had no idea until I had to implement it myself. (It's also not always optimal, although I don't offhand know of any optimizer that understands the relevant edge cases.)
adamzochowski 1y ago
This depends on the join type. select ... from table1 left join mytable2 on ... where .. If you move contents of where clause to the join/on clause, you will change meaning of the query. If someone has a complex query or complex performance, they could do either subselects select ... from ( select ... from table1 where ... ) as table1_filtered inner join mytable2 on ... or CTEs with table1_filtered as ( select ... from table1 where ... ) select ... from table1_filtered inner join mytable2 on ...

brudgers 1y ago

When you are willing to pay for your RDBMS, you are likely to get an optimizer that does algebra for you (and does algebra pretty well). On the other hand, if you are hand coding the query, you are hand coding whether you use SQL, Linq, or anything else. And a strength of SQL is a robust ecosystem of documentation, tools, employment candidates, and consultants.

gigatexal 1y ago
exactly, one often gets what they pay for. databases are complicated beasts and tens of thousands if not more working years of the smartest people have been working on them since the 70s
- hyperpape 1y ago
  > databases are complicated beasts and tens of thousands if not more working years of the smartest people have been working on them since the 70s Yes, but they've also set themselves a much harder problem. Instead of "find the proper execution for this particular query, armed with all the knowledge of the database you have" they have the problem of "find the proper execution for all possible queries, using only information that's encoded in DB statistics." So it's no surprise that it's quite easy to find places the optimizer does silly things. To take one instance that bit me recently: or clauses representing different tables cannot be optimized well at all in postgres (https://www.cybertec-postgresql.com/en/avoid-or-for-better-performance/ https://www.cybertec-postgresql.com/en/avoid-or-for-better-p...). I'd still rather have optimizers than not--the optimizer does well most of the time, and my current work has far too many queries to hand-optimize. But you do end up seeing a lot of poorly optimized queries.
  - gigatexal 1y ago
    That’s right. The better one is able to help the optimizer the better life is. It’s an art not a science.

gigatexal 1y ago

but SQL execution even in a dumb planner is FROM then WHERE then .... and then finally SELECT and ORDER so I don't get it

gonzalohm 1y ago
But a smart query planner would move the where condition into the JOIN condition
- Sesse__ 1y ago
  Depending on the join type, moving it up into the join (instead of keeping it on the table) would be either irrelevant or actively harmful.

hobofan 1y ago

Both SQL and Linq-style queries end up in the same in-memory representation once they hit the query engine/query planner. Filter pushdown ("correct application of where, and hope it puts the where before the join") is table stakes for a query planner and has been for decades. And no, Iceberg tables are not special in any way here. Iceberg tables contain data statistics, just like the ones described in the article to make the optimizer choose the right query plan.

Sesse__ 1y ago
Filter pushdown is surprisingly subtle, when you start getting into issues of non-inner joins and multiple equalities. I had no idea until I had to implement it myself. (It's also not always optimal, although I don't offhand know of any optimizer that understands the relevant edge cases.)

adamzochowski 1y ago

This depends on the join type. select ... from table1 left join mytable2 on ... where .. If you move contents of where clause to the join/on clause, you will change meaning of the query. If someone has a complex query or complex performance, they could do either subselects select ... from ( select ... from table1 where ... ) as table1_filtered inner join mytable2 on ... or CTEs with table1_filtered as ( select ... from table1 where ... ) select ... from table1_filtered inner join mytable2 on ...