5 ms·
It's more about the fact that a join can change the shape of the result set. Even if the columns being projected aren't surfaced, the DB still has to process th
by default-kramer 4y ago
It's more about the fact that a join can change the shape of the result set. Even if the columns being projected aren't surfaced, the DB still has to process the joins to make sure the result set has the correct shape. For example, a human might know that a join will always be one:one or one:zero-or-one, but the DB has no choice but to make sure. Perhaps using subqueries instead of joins would work, but that gets ugly too.
(Or maybe my knowledge is outdated and the optimizers have gotten way better than they were 3-4 years ago.)
- jdmichal 4y agoAt least PostgreSQL's query optimizer can and will drop `LEFT JOIN` clauses if the data is not actually being used. It can't do that for `INNER JOIN` because it must verify that a matching row exists.
- cm2187 4y agoFor LEFT join it would also need to know the combination of columns you are joining by are unique in the right table, which will be the case in many scenarios (joining on a primary key) but not in the general case.
- trimethylpurine 4y agoI'll add that MSSQL at least since 2019 will automatically modify the execution plan to avoid this by the second time the query is executed.
- jdmichal 4y agoThat's an excellent point. I hadn't thought of it because the joins where I witnessed this were all on primary keys, as $DIETY intended. (That's a joke.)
- trimethylpurine 4y agoWith most engines, this can be optimized with indexing (or indexed views) very easily to the extent it would be negligible.