There are some anti-patterns one should avoid when writing SQL queries. Sometimes these may seem like a shortcut, but in reality this can lead to bugs, problems, and brittle applications.
It's always better to use the explicit column list in the SELECT query than a * (star) wildcard. It not only improves the performance but also makes your code more explicit. It also helps you create maintainable code, which will not break when you add/remove columns from your table, especially if you have views that refer to the original table.
Doing a SELECT * may seem like a time-saver, but it's
actually setting you up for problems in the long run, specially when
database schema changes.
When you use SELECT * in views, then you create subtle bugs if a new column has been added ro an old one is removed from the table. Why? Because your view might break, or start returning an incorrect result.
When you use the SELECT * query in your application and have any dependency on order of column, which you should not, the ordering of the result set will change if you add a new column or change the order of columns.
When you use SELECT * in JOIN query, you can introduce complications when multiple tables have columns with the same name e.g. status, active, name, etc.
Due to this increase in data, your application may require more memory just to hold unnecessary data that it will not be using
SELECT * obviously returns more data than required to the client, which, in turn, will use more network bandwidth.
By using SELECT *, you can be returning unnecessary data that will just be ignored, but fetching that data is not free of cost.
One should default to using COUNT clauses with column
names, such as COUNT(id), since this will count values
which are non-NULL.
If NULL is explicitly wanted then one can choose for
COUNT(1) or COUNT(*). There is a negligible
performance difference, at least in PostgreSQL between 1 and * , so use
at your discretion.