3 ms·

The article doesn't mention performance. I haven't benchmarked this yet, but after my first experiences with somewhat complex data transformations in numpy and

The article doesn't mention performance. I haven't benchmarked this yet, but after my first experiences with somewhat complex data transformations in numpy and pandas, I was left with the feeling that despite them being optimized, any modern RDBMS would still have run circles around them. They've been optimized to this kind of stuff for decades, after all.

barrkel 7y ago
Most modern RDBMS are optimized for point-lookup queries and have a very hard time choosing good plans when you're selecting millions of rows amongst billions. Remove the assumption that the end result is going to be a row or ten (which usually have good obvious plans with clear indexes), and the query planner is forced to make decisions about choosing the best plan amongst very costly alternatives. The exact costs are sensitive to assumptions about distribution, I/O costs, memory costs etc. and those assumptions need delicate tuning, especially when you don't have hinting as a tool (e.g. Postgres basically doesn't let you give many hints - you can kind of force join order, but the biggest tool is the CTE optimization barrier - I can get 100x speedups by moving subqueries out into CTEs and ensuring they're opaque). To write efficient big queries, you need to think in terms of data flow and write declarative SQL with an execution plan in mind; it reminds me of my days tweaking source code to encourage a compiler to make certain register allocation decisions. PG is a bitch here. It's often easier to get good performance by fetching one query, transforming it into a comma-delimited string, and then injecting it into the subsequent query (possibly in batches) to be sure you're getting the plan you need. MySQL's planner is kind of stupid, but it's predictably stupid and there's lots of hinting tools available. I find it a lot easier to make it work fast for big queries. Sometimes it simply doesn't implement the best strategy (e.g. no hash joins), of course, so sometimes it's not as fast as you could get with PG, but it's usually easier to outperform.
- keanzu 7y ago
  > To write efficient big queries, you need to think in terms of data flow and write declarative SQL with an execution plan in mind; it reminds me of my days tweaking source code to encourage a compiler to make certain register allocation decisions. That mirrors my experience with Oracle. The only difference is that there's a hinting mechanism provided which will almost always allow you to force the execution plan you want.
  - iimblack 7y ago
    Ugh hints. The things they almost always recommend you don’t use because the optimizer should know better. Unsarcastically, this is usually true. More often I see people who think they’re being clever killing performance with undocumented hints instead of either letting the engine do its work or looking for other ways to optimize.
- oarabbus_ 7y ago
  >Most modern RDBMS are optimized for point-lookup queries and have a very hard time choosing good plans when you're selecting millions of rows amongst billions. Any column store warehouse (Redshift, Bigquery, Snowflake) is terrific for these kinds of queries, though.
jstrong 7y ago
> I haven't benchmarked this yet if you don't measure, you're just guessing. > after my first experiences with somewhat complex data transformations in numpy and pandas there are fast ways and slow ways to use numpy/pandas, but generally speaking, it's easy to get order of magnitude improvements using pandas compared to an RDBMS. In pandas, for example, the data from a column is a contiguous in-memory array. Generally RDBMS data is row-oriented, possibly in memory or possibly on disk. Performing some numerical operation on the contiguous array is going to play to the cpu's strengths much better.

barrkel 7y ago

Most modern RDBMS are optimized for point-lookup queries and have a very hard time choosing good plans when you're selecting millions of rows amongst billions. Remove the assumption that the end result is going to be a row or ten (which usually have good obvious plans with clear indexes), and the query planner is forced to make decisions about choosing the best plan amongst very costly alternatives. The exact costs are sensitive to assumptions about distribution, I/O costs, memory costs etc. and those assumptions need delicate tuning, especially when you don't have hinting as a tool (e.g. Postgres basically doesn't let you give many hints - you can kind of force join order, but the biggest tool is the CTE optimization barrier - I can get 100x speedups by moving subqueries out into CTEs and ensuring they're opaque). To write efficient big queries, you need to think in terms of data flow and write declarative SQL with an execution plan in mind; it reminds me of my days tweaking source code to encourage a compiler to make certain register allocation decisions. PG is a bitch here. It's often easier to get good performance by fetching one query, transforming it into a comma-delimited string, and then injecting it into the subsequent query (possibly in batches) to be sure you're getting the plan you need. MySQL's planner is kind of stupid, but it's predictably stupid and there's lots of hinting tools available. I find it a lot easier to make it work fast for big queries. Sometimes it simply doesn't implement the best strategy (e.g. no hash joins), of course, so sometimes it's not as fast as you could get with PG, but it's usually easier to outperform.

keanzu 7y ago
> To write efficient big queries, you need to think in terms of data flow and write declarative SQL with an execution plan in mind; it reminds me of my days tweaking source code to encourage a compiler to make certain register allocation decisions. That mirrors my experience with Oracle. The only difference is that there's a hinting mechanism provided which will almost always allow you to force the execution plan you want.
- iimblack 7y ago
  Ugh hints. The things they almost always recommend you don’t use because the optimizer should know better. Unsarcastically, this is usually true. More often I see people who think they’re being clever killing performance with undocumented hints instead of either letting the engine do its work or looking for other ways to optimize.
oarabbus_ 7y ago
>Most modern RDBMS are optimized for point-lookup queries and have a very hard time choosing good plans when you're selecting millions of rows amongst billions. Any column store warehouse (Redshift, Bigquery, Snowflake) is terrific for these kinds of queries, though.

jstrong 7y ago

> I haven't benchmarked this yet if you don't measure, you're just guessing. > after my first experiences with somewhat complex data transformations in numpy and pandas there are fast ways and slow ways to use numpy/pandas, but generally speaking, it's easy to get order of magnitude improvements using pandas compared to an RDBMS. In pandas, for example, the data from a column is a contiguous in-memory array. Generally RDBMS data is row-oriented, possibly in memory or possibly on disk. Performing some numerical operation on the contiguous array is going to play to the cpu's strengths much better.