3 ms·
I’ve seen a -lot- of engineering time spent trying to adapt to transient quirks of sql query planners, where the programmer expected a filtered range scan and t
by jeffbee 6y ago
I’ve seen a -lot- of engineering time spent trying to adapt to transient quirks of sql query planners, where the programmer expected a filtered range scan and the database elected for a materialized temp table, or similar disasters. Each and every one of those people would have been better served if they could have just specified the op tree that they wanted to be executed.
- kamranjon 6y agoUse EXPLAIN ANALYZE and save yourself some time. It's not that difficult to figure out what the planner is doing and tweak it. I think SQL is the best we got but I'd love to hear what you think is a better alternative.
- jusssi 6y agoI don't think parent was saying they haven't used EXPLAIN ANALYZE or it's equivalent. Explain tools are quite good at showing what the db chose to do. They generally suck at explaining why that particular plan was chosen, or why a particular strategy was not used (i.e. why the table scan when there's a perfectly good index).
- ethbr0 6y agoIsn't that inherent in the abstraction represented by SQL? In that, it's optional that the DBS explains why it chooses what it chose (although it can happily tell you what). Were it to do so, that'd be a pretty big crack in the abstraction layer to peer through, and would likely cause more footgunning by (1) developers peeking at the why, (2) developers assuming stability of the why, & (3) ossification of the underlying DBS engines, as now program functioning depends on their internals behaving a certain way. (Although I guess it's a general win for SQL that we're discussing performance differences, rather than correctness differences)
- jusssi 6y agoAs it is, people already have to look into "why" for practical considerations. A database failing to perform will lead to a timeout somewhere else and a 500 error served to an end user. With the lack of tooling support, information is extracted by modifying the query by trial and error (seeing what needs to be changed to flip it over to desired behavior), from dark corners of the internet, and by reading the source code of the storage engine. My latest expedition into these matters was a case where a table scan was technically faster (and favored by the query planner) than a low-cardinality index, but used a lot more CPU. So when the DB was hit with several cases of that particular query simultaneously, the server would run out of CPU and everything slowed to a crawl.
- jeffbee 6y agoI find the faith on display in this thread in database query planners to be charming, in the same way that toddlers who believe in the tooth fairy are charming. I guess I'm the only person who has ever needed to debug why MySQL creates on-disk temporary ISAM files for UNION statements producing 2 rows? There are infinite edge cases in these DBMS engines.
- ineedasername 6y agoExamine the execution plan, use "hints". This is pretty common DBA stuff. If by "engineer" you mean a non-dba programmer, then that's the problem: lack of domain expertise.
- ako 6y agoThe right way to execute the query depends on the data in the database, and parameters in your query. I think overall a lot of database time and development time has been saved by depending on query planners.
- CaptainZapp 6y agoDon't forget index statistics, which can turn any query into a full fledged catastrophy if they're not up to date. In my experience it's extremely rare for an optimizer to go awry on a well maintained database. And for the rare case where your data - and access patterns are so weird that it does happen you can always employ hints. Which is a bit nasty, granted. But it's not that you would use them excessively on a well designed and maintained database. It's probably obvious that I very much agree with your take.
- ethbr0 6y agoHints are required in an efficient, general purpose system. Either you can have everything fully-specified at all times (a waste of time an effort, as parent noted), or you can limit general application (no overriding defaults), or you can allow a method for overriding defaults (when necessary). Of those, it seems shortsighted for people to complain about the last, given it's strongly arguably the best of the three options.
- sharadov 6y agoAbsolutely agree.
- sharadov 6y agoOnly cases I've seen that when the stats are out of whack, because the vacuum strategy is not right.