Why SELECT * is bad for SQL performance

In every dbms I have used count(*) is optimized to just count rows a fast as possible. count(column name) might actually be slower as it has to check for null values.

Same goes for EXISTS(select * from table)

I have seen many use count(1) and EXISTS (select 1 from table) this is mostly superstition and ends up getting optimized to the same as * at least now days.