5 ms·

I 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 gen

by jusssi 6y ago

I 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 ago
Isn'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 ago
  As 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 ago
    I 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.

by jusssi 6y ago

ethbr0 6y ago

Isn'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 ago
As 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 ago
  I 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.