4 ms·

> Predictable performance - this will always be not only implementation-dependent but data-dependent as well This immediately jumped out at me from the parent

> Predictable performance - this will always be not only implementation-dependent but data-dependent as well This immediately jumped out at me from the parent comment. It would be entirely possible to implement a query language where you specify a plan for your query. But then you’d immediately lose the “better than SQL” competition, because your complexity and maintainability problems would skyrocket. I’ve had to deal with this problem as an Oracle DBA, and it’s a complete nightmare. It starts with a statistics refresh ruining a couple of execution plans, so you start specifying them manually with the plan manager. Then it gets worse over time, because stats refreshes become a big risk and you don’t want to do them anymore. Eventually you get to the point where you pretty much only run verified plans. Then your verified plans slowly degrade overtime, because the underlying cardinality of every table is constantly changing. You’ve replaced the query optimiser with yourself, which is not only tedious work, but it’s simply not possible to do the job as well as any mainstream DB engine could.

CaptainZapp 6y ago
I wish that I could upvote your comment more than once, because this rings so true. There certainly are (rare) situations, where you need to provide hints in one form or another, but it's really a bloody nightmare to maintain and may completely bork, when you - say - upgrade to a new version of the database engine. I work with relational databases since the early 90s and can give you a no-bullshit money back guarantee that you (not you personally, obviously) are not smarter than the optimizer. Usually there are weird data patterns involved if you absolutely must provide hints. But basically: Don't do it!
- mnsc 6y ago
  I think one of the challenges with sql is that beginner developers can create naive sql queries that "work" but are extremely complicated for the optimizer to "get right". So in some cases (talking from own experience) the developer can, with the use of hints, "be better" than the optimizer when the problem all along was the overall structure of the query. Edit: don't do it
  - AmericanChopper 6y ago
    The relational model can _usually_ save the day here, without a huge amount of effort. SQL certainly has its share of anti-patterns and footguns. But most of the awful SQL I’ve seen over my career hasn’t come from poor mastery of SQL, it’s come from poorly normalized schemas. If you have a properly normalized schema, then you can do a huge amount with very simple SQL. When it’s poorly normalized, you end up with all sorts of strange and inefficient design patterns in your SQL. This could come across as me saying “well it’s easy if you do it right”, but the thing is, normalizing a schema is incredibly simple. I would expect a relatively inexperienced software engineer to be able to pick it up literally just from reading the Wikipedia page. In my experience, the more common underlying problem is that inexperienced engineers (even if they’re only inexperienced in terms of SQL and RDBMS knowledge), don’t actually know what normal forms are, or why they’re useful. Data structures and concurrency control is just fundamentally useful computer science, but for some reason it seems to be a topic a lot of people don’t pay enough attention too. Maybe it’s just my personal pet peeve, but I’ve seen too many projects start with “wow NoSQL is great”, and a few months later end up with giant nested loops in their lookups, and some poorly built custom implementation of MVCC in their business logic. (NoSQL is great btw, just not for relational data)
    - Akronymus 6y ago
      >footguns Ran into one recently. Where a table was joined either to one or the other table, based on if a value was null in the first one. This was fine, until we added a where clause to a, through multiple joins, base table for both options. This tanked the performance >1000x.[1] If we just returned the value it had basically no impact. We tried solving it with using the result set as a base for a select where we did the filtering. This also resulted in the slow performance. In the end I solved it by wrapping the column in a function call, which solved it. And I still don't know why. My guess is that somehow without the function call, it optimizes it into one query, which results in basically the original case, while a function forces the evaluation of the subquery first. [1]Sub 1sec to over 15 minutes
      - AmericanChopper 6y ago
- bmh100 6y ago
  I've found with distributed postgreSQL databases that the optimizer needs more help than you might initially expect. After looking at how the optimizer inefficiently implemented a query, I found a different, but equivalent, way to rewrite the query that could then be efficiently optimized. It's more complicated in the distributed case, because the shard distribution rules have a strong impact on performance. The time needed to re-balance data across shards for a query can be significant.
quietbritishjim 6y ago
I've had the opposite problem. In a system I've been working on a lot, I know exactly what indices I want used in what order to read the data. Admittedly the database often figures that out first time, but sometimes it doesn't, then I have to go through an infuriating process of changing parameters or tweaking the query into a mathematically equivalent form until it guesses what I want. In one case I was even forced to write a procedural loop in PL/pgSQL (yes, I'm fully aware this is an antipattern). I don't want to give hints to the optimiser. I want to specify precisely how the data will be read and joined. I appreciate this is this is the exception rather than the rule compared to how most developers would prefer to work. And this system is a bit unusual in that it's using a database for passing messages, which it isn't well suited for (especially not the way they're storing the messages.)

CaptainZapp 6y ago

I wish that I could upvote your comment more than once, because this rings so true. There certainly are (rare) situations, where you need to provide hints in one form or another, but it's really a bloody nightmare to maintain and may completely bork, when you - say - upgrade to a new version of the database engine. I work with relational databases since the early 90s and can give you a no-bullshit money back guarantee that you (not you personally, obviously) are not smarter than the optimizer. Usually there are weird data patterns involved if you absolutely must provide hints. But basically: Don't do it!

mnsc 6y ago
I think one of the challenges with sql is that beginner developers can create naive sql queries that "work" but are extremely complicated for the optimizer to "get right". So in some cases (talking from own experience) the developer can, with the use of hints, "be better" than the optimizer when the problem all along was the overall structure of the query. Edit: don't do it
- AmericanChopper 6y ago
  The relational model can _usually_ save the day here, without a huge amount of effort. SQL certainly has its share of anti-patterns and footguns. But most of the awful SQL I’ve seen over my career hasn’t come from poor mastery of SQL, it’s come from poorly normalized schemas. If you have a properly normalized schema, then you can do a huge amount with very simple SQL. When it’s poorly normalized, you end up with all sorts of strange and inefficient design patterns in your SQL. This could come across as me saying “well it’s easy if you do it right”, but the thing is, normalizing a schema is incredibly simple. I would expect a relatively inexperienced software engineer to be able to pick it up literally just from reading the Wikipedia page. In my experience, the more common underlying problem is that inexperienced engineers (even if they’re only inexperienced in terms of SQL and RDBMS knowledge), don’t actually know what normal forms are, or why they’re useful. Data structures and concurrency control is just fundamentally useful computer science, but for some reason it seems to be a topic a lot of people don’t pay enough attention too. Maybe it’s just my personal pet peeve, but I’ve seen too many projects start with “wow NoSQL is great”, and a few months later end up with giant nested loops in their lookups, and some poorly built custom implementation of MVCC in their business logic. (NoSQL is great btw, just not for relational data)
  - Akronymus 6y ago
    >footguns Ran into one recently. Where a table was joined either to one or the other table, based on if a value was null in the first one. This was fine, until we added a where clause to a, through multiple joins, base table for both options. This tanked the performance >1000x.[1] If we just returned the value it had basically no impact. We tried solving it with using the result set as a base for a select where we did the filtering. This also resulted in the slow performance. In the end I solved it by wrapping the column in a function call, which solved it. And I still don't know why. My guess is that somehow without the function call, it optimizes it into one query, which results in basically the original case, while a function forces the evaluation of the subquery first. [1]Sub 1sec to over 15 minutes
    - AmericanChopper 6y ago
bmh100 6y ago
I've found with distributed postgreSQL databases that the optimizer needs more help than you might initially expect. After looking at how the optimizer inefficiently implemented a query, I found a different, but equivalent, way to rewrite the query that could then be efficiently optimized. It's more complicated in the distributed case, because the shard distribution rules have a strong impact on performance. The time needed to re-balance data across shards for a query can be significant.

quietbritishjim 6y ago

I've had the opposite problem. In a system I've been working on a lot, I know exactly what indices I want used in what order to read the data. Admittedly the database often figures that out first time, but sometimes it doesn't, then I have to go through an infuriating process of changing parameters or tweaking the query into a mathematically equivalent form until it guesses what I want. In one case I was even forced to write a procedural loop in PL/pgSQL (yes, I'm fully aware this is an antipattern). I don't want to give hints to the optimiser. I want to specify precisely how the data will be read and joined. I appreciate this is this is the exception rather than the rule compared to how most developers would prefer to work. And this system is a bit unusual in that it's using a database for passing messages, which it isn't well suited for (especially not the way they're storing the messages.)