> “Select ” will almost certainly be optimized away by the execution plan

What do you mean? Ask for all the columns, you're gonna get all the columns in a resultset. Only in a subquery could the planner know what is unused.

'select ' is _perhaps_ tolerable for creating a view--under the conditions that the database transforms the query into the unambiguous named columns at view creation time--and that's about it.

You’re right. When I wrote that, I was thinking higher up the chain in an orm layer. Sending “select *” to the db can’t be “optimized away” at that layer, it will have to return all the columns. Unless, like you said, the scope of the “select *” contains it in a sub query where the columns used can be known... “it depends :)”

>> binding issues, in code, when you modify or add columns.

This is very common IME, and it is a very bad code smell. It tends to break things very low in the architecture, so even capturing the error makes the application essentially not work in a fundamental way.

And, not to be a curmudgeon, but, most apps don't really need that level of optimization. In 90% of apps it's fine. The 10% that really need the performance are hopefully doing it right with well tuned indices, careful denormalization, and other things. It is unimaginable to me, that someone with a higher performance SQL/RDBMS backed app can't run a query planner and tame ORMs for performance and identify things like wildcard column select. But what do I know :)

Very true. Also, along those lines, if you have fewer than 10k rows, it’s likely the dB will keep the whole table in memory after the first access, anyway.

In an ideal situation, agreed. But surely that depends entirely on how many tables are being queried (how many tables already exist in the cache), the size of the rows, how much RAM the host has / how big the cache is, plus a few other variables most probably.

What I mean is: like all other things, it depends. And with assumptions like this, it is generally better to measure.

he's not really talking about the performance of the DB though, focusing more on size of the resultant payload thatr gets sent over the wire. In the actual database which is traditionally very challenging to scale, projections aren't very expensive. The other things you and GP mention are way more likkely to move the performance needle.