3 ms·

I dont like it when people who cannot write efficient SQL/design efficient database schema start blaming "bad" query optimizer. pro SQL developers extremely ra

by slt2021 6y ago

I dont like it when people who cannot write efficient SQL/design efficient database schema start blaming "bad" query optimizer. pro SQL developers extremely rarely have a problem with query optimizer, and when they have it is extremely exotic. it is usually ORM users that have a problem with SQL performance and blame the optimizer

bawolff 6y ago
The comment i'm responding to is asking why two equivalent queries have different runtimes. The answer pretty obviously is because the optimizer is smart enough to chose the best query plan in one case and not the other. I think its fairly obvious that in theory a better optimizer could figure out the right plan in both cases. That's not to say that its impossible to work around or that in a real application that you would ever be "stuck" by this. At the end of the day you deal with the software you have, but the optimizer can still have weaknesses without it totally derailing your app.
- gfody 6y ago
  the optimizer is constrained, it still has to give you what you asked for. i can't even translate into english what's being asked for in query #2 but it's only a coincidence that it has the same result as the more precise and correct thing being asked for in the final query.
  - lixtra 6y ago
    There are two possible cases: a) with everything known to the database (i.e. ids not null) the two queries produce the same result. b) for some contents of the schema the results differ, but in the presented case some additional condition (not known to the db) holds that makes them equivalent. Only in case b) it can be called coincidence. In case a) it’s fair to ask if the optimizer can’t do better. After all it tries to avoid cartesian products for simple joins even though that’s the text book definition. “Not worth” to improve the optimizer is still a valid answer.
    - gfody 6y ago
      the query in question is definitely case b - it's a group by query with an explicit granularity, there's just no actual aggregates being asked for but the very presence or absence of a row in the results carries some meaning. the explicit grain is something like employee markouts plus customers from stores with at least one employee without markouts.
matwood 6y ago
I agree they shouldn't start there, but you don't necessarily need exotic queries to run into edge cases that work differently across say MySQL, MSSQL, and Oracle. The classic example being exists vs left join null.

bawolff 6y ago

The comment i'm responding to is asking why two equivalent queries have different runtimes. The answer pretty obviously is because the optimizer is smart enough to chose the best query plan in one case and not the other. I think its fairly obvious that in theory a better optimizer could figure out the right plan in both cases. That's not to say that its impossible to work around or that in a real application that you would ever be "stuck" by this. At the end of the day you deal with the software you have, but the optimizer can still have weaknesses without it totally derailing your app.

gfody 6y ago
the optimizer is constrained, it still has to give you what you asked for. i can't even translate into english what's being asked for in query #2 but it's only a coincidence that it has the same result as the more precise and correct thing being asked for in the final query.
- lixtra 6y ago
  There are two possible cases: a) with everything known to the database (i.e. ids not null) the two queries produce the same result. b) for some contents of the schema the results differ, but in the presented case some additional condition (not known to the db) holds that makes them equivalent. Only in case b) it can be called coincidence. In case a) it’s fair to ask if the optimizer can’t do better. After all it tries to avoid cartesian products for simple joins even though that’s the text book definition. “Not worth” to improve the optimizer is still a valid answer.
  - gfody 6y ago
    the query in question is definitely case b - it's a group by query with an explicit granularity, there's just no actual aggregates being asked for but the very presence or absence of a row in the results carries some meaning. the explicit grain is something like employee markouts plus customers from stores with at least one employee without markouts.

matwood 6y ago

I agree they shouldn't start there, but you don't necessarily need exotic queries to run into edge cases that work differently across say MySQL, MSSQL, and Oracle. The classic example being exists vs left join null.