4 ms·
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
by 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 agothe 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 agoThere 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 agothe 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.