|
|
|
|
|
|
by ninetyninenine
660 days ago
|
|
|
No it's not. Many language are designed so certain performance aspects are invisible. Take golang and python: The garbage collector. The garbage collector is something you should not think about when programming and most programmers don't even need to know it exists. You only think about it when you need to seriously optimize things. For SQL it's a bad design choice because of what SQL is and what SQL is targeting. SQL is a high level abstraction that automatically determines a query plan based off of high level input. The problem here is that it can choose a bad query plan. So you need to "hack" the query in order to trick the query planner into doing what you want. It's also targeting the slowest part of the stack: Non volatile memory and IO. The slowest part of the stack should be targeted with a zero cost abstractions to maintain speed while the fastest part of the stack you can use a language like python for your web app it's fine because databases are magnitudes slower. |
|
|
For query plans, in nearly every plan flip I’ve encountered, the root cause came down to either poor table / column statistics (because they had let the table grown too large without appropriate tuning), having an enormous amount of joins beyond the deterministic level the planner could provide, or sub-optimal queries. The latter is growing less common as planners get better and better at rewriting on the fly, but it’s always good to know how to do things optimally.
A good example is semijoins / antijoins. With modern versions of both MySQL and Postgres, you’ll probably have “WHERE foo IN (…)” turned into “WHERE EXISTS (SELECT 1…)”, but it’s better to write it the optimal way in the first place.
I will grant you that indexing can be bewildering, with its many rules, caveats, and gotchas.