CODEWITHSUNDEEP
sqlperformancepostgresql
Niels Kristian
Niels Kristian

Reputation: 8845

Does it affect the performance, if a table holds a lot of unused/unselected columns?

Background: I have a table called cars that holds a lot of used cars, aprox 1 million rows. The table has a little more than 170 columns. The table is indexed on individual columns alone. Most of the columns are booleans (e.g. has_automatic_gearbox etc.) and the rest is strings and numbers (e.g. color and price). The cars are shown in a view, where i use around 80 columns out of the total 170.

My question: So my question is, does it make a difference to the performance, whether I select only the 80 columns out of the table when doing a search, or on the other hand I made a new table ONLY consisting of those 80 columns that I need, instead of the total 170 columns? So in other words, does it make a difference to performance, that a table holds columns, that is not selected?

Upvotes: 3

Views: 3904

Answers (3)

Daniel Vérité
Daniel Vérité

Reputation: 61566

Also you may want to read the answers to a similar question on dba.SE:

Do the number of columns in a particular table affect the performance
of a query, when querying on a subset of that table?

In short: yes it does, because the non-selected columns generally live in the same disk blocks than the selected columns, so they're going to be read anyway.

Upvotes: 2

wildplasser
wildplasser

Reputation: 44250

Most of the attributes of your 1E6 cars will probably depend on some hidden factor, such as {brand,model,make,model_version}. You could queeze out these attributes into a separate table, and refer to that table in your main cars table. In the ultimate case, your main cars table will only contain attributes that belong to the particular instance of a car model (eg: {number, price, build_date, buy_date, (maybe) colour}.)

Upvotes: 0

Adam Robinson
Adam Robinson

Reputation: 185643

Andomar is correct in his comment that "it depends". However, if you question is something like this:

Can the number of columns in a table have an effect upon select queries?

Then the answer is YES. Whether or not they're "extra" or "unused" is a question of database design, and doesn't have anything to do with the question of performance.

All other things being equal, a row in a table with 100 columns is going to take up more space than a row in a table with 10 columns. Because the rows will be larger, your server will have to work (relatively) harder to go over an equal number of rows in the wider table than in the smaller table.

Things like page splitting will also occur more often in tables whose rows take up more space.

If your question is (and I think this might be more along the lines of what you're asking)

Would, all things being equal, a select query pulling 80 columns from a table with 170 columns be slower than a query pulling 80 columns from a table with 80 columns?

Then the answer should be NO.

Upvotes: 8

Related Questions