5 ms·

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 o

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
      Imo, polymorphic associations are one of the key areas that the relational model in general struggles. You can do them in most RDBMS, but they’re always a bit janky. Even when you’re just modelling your schema, you really have to think quite hard about it, and you’ll really struggle to preserve simplicity.
      - Akronymus 6y ago
        In this case it was a left join sometable on sometable.someuid = isnull(someothertable.someuid, somethirdtable.someuid) I guess that is such an uncommon case that it tripped up the optimizer completely. Also: Thanks for writing "polymorphic associations". Not knowing that probably is why I struggled to find any info on it. Edit: Both tables were actually the same one, just retrieved via different joins, so different data.[1] [1]One was a company, the other was the company we need to send money to. This is for when deal with a daughter company but pay the parent company directly, for example.
        AmericanChopper 6y ago
        > Thanks for writing "polymorphic associations". Not knowing that probably is why I struggled to find any info on it. We might have had a similar experience with this. The first time I stumbled across this problem though I was specifically trying to figure out “what is the relational way to implement polymorphism”, so I pretty much lucked into the a rather productive series of google searches.
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.

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
    Imo, polymorphic associations are one of the key areas that the relational model in general struggles. You can do them in most RDBMS, but they’re always a bit janky. Even when you’re just modelling your schema, you really have to think quite hard about it, and you’ll really struggle to preserve simplicity.
    - Akronymus 6y ago
      In this case it was a left join sometable on sometable.someuid = isnull(someothertable.someuid, somethirdtable.someuid) I guess that is such an uncommon case that it tripped up the optimizer completely. Also: Thanks for writing "polymorphic associations". Not knowing that probably is why I struggled to find any info on it. Edit: Both tables were actually the same one, just retrieved via different joins, so different data.[1] [1]One was a company, the other was the company we need to send money to. This is for when deal with a daughter company but pay the parent company directly, for example.
      - AmericanChopper 6y ago
        > Thanks for writing "polymorphic associations". Not knowing that probably is why I struggled to find any info on it. We might have had a similar experience with this. The first time I stumbled across this problem though I was specifically trying to figure out “what is the relational way to implement polymorphism”, so I pretty much lucked into the a rather productive series of google searches.

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.