Select * vs Selecting All Column Names

Question

I've been reading up on this and I understand that it's better to explicitly list the column names, I still have a question.

Let's pretend the following scenario (which is very close to my real life scenario):

1. New columns will never be added

2. I am not using any blob data

3. I actually do want to return all of the columns

4. I have a table with roughly 140 million rows

5. I don't actually need all 140 million rows, but let's just pretend I do for the sake of argument

6. I am not using any joins and nobody else will

7. The queries below are literally exactly how I will be running the queries.

Is there a performance difference between the following queries:

SELECT * FROM <table_name>

VS

SELECT <every_column_name...> FROM <table_name>

Edit: I understand there are a million questions on this topic. BUT FOR THIS PARTICULAR SCENARIO is there any sort of performance difference? Is select * still bad or will both queries have the same performance?

From what I can tell, based on the results of using explain, there is no difference, for this particular case.

Answer 1

This is an elaboration on my comment.

There is definitely no harm in using select * for an ad hoc query. It is a great convenience and common.

The problem arises when you want to run the same query over time. In particular, if the query is compiled, then changes to the underlying table can cause unexpected problems. I "fondly" recall spending about 10 hours debugging a problem (starting at 6:00 a.m.) caused by a select * in a view, when a column type in the underlying table was changed. The code was not recompiled and the offsets in the data records were off.

Even this situation can be fixed by forcing a re-compile. And, I commonly use SELECT * in ad hoc queries.

Your question summarizes some of the key points, such as wide columns slowing down a query.

Answer 2

There isn't any performance difference between them. When the DBMS analyzes the two statements, it produces the same query. That is, * is a shortcut for every_column_name.

Answer 3

In PostgreSQL, the performance of selecting all columns with and without * is almost the same. You can see my answer experimenting it with 10 columns which each column has 10 million rows in PostgreSQL.