Hacker News Reader

Comment by wswope | Hacker News Reader

> If you go with one view, you will eventually end up paying a performance cost for computations you actually aren't using in certain places (barring incredible and maybe impossible advancements in query optimization).

This is outdated; most modern DBs treat views as they would CTEs. If you don’t reference a defined field downstream, it doesn’t need to get calculated.

View on HN

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.)

At 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.

For 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.

That'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.)

With most engines, this can be optimized with indexing (or indexed views) very easily to the extent it would be negligible.

I'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.

Reply on news.ycombinator.com