I am working on a reporting system that will require large select queries, but is based on a database that is only filled once. The database management system is Microsoft SQL Server 2017. There is probably a better way to design a system like this, but let's approach this theoretically.

Theoretically speaking:

  1. If we have a very large database (150M+ rows on several tables)
  2. And we can assume the database will only be populated once.

Could indexing every possible column combination have a negative performance impact on a select query?

  • 4
    Every possible combination is impractical most oft the time. A more sensible approach is to index manually but very generously. That definitely can make sense. – usr Jun 13 at 18:49
  • 12
    I suggest rewording either your title or your bolded text so they are consistent. At a glance I was confused by the highest voted answer "Yes" – aaaaaa Jun 13 at 20:01
  • 150M rows is large for a single table, but is not large for a database. Practically speaking, reporting systems only use a small subset of possible column combinations, it is best to focus on the key combinations at least initially, and then get more complex only as needed. – pojo-guy Jun 14 at 15:27
up vote 37 down vote accepted

Yes, it will influence initial plan compile time as the optimizer will have many extra access paths to the data to consider.

Since you're on SQL Server 2017, loading once, and running reports, why not just use a clustered column store index instead?

That seems to be the ideal solution to your need to index every possible column combination.

Columnstore indexes - Overview

  • Columnstore is where I would go too, but I am just wondering... isn't the optimizer working just the opposite to what you described? I mean instead of scanning available indexes and "wondering" which of them could be useful doesn't it egzamin the query and "think of" a perfect index for that query, then it checks if it exists? (If it doesn't then a missing index message is generated.) If I am right (I don't know, just guessing), then even if there are thousends of indexes it should't be noticeably longer time than having just several of them. – Limonka Aug 20 at 7:07

If you have N columns in a table, every possible column combination is 2^N-1 (removing the empty set). For 10 columns that would mean 1023 indexes, for 20 columns we end up with a whopping 1048575 indexes. Most of the indexes will never be used but will have to be taken into consideration by the optimizer. It is possible that the optimizer will choose a sub-optimal index instead of a better one. I would not take the path of generating all sorts of indexes, instead of trying to figure out what indexes that would actually be beneficial.

EDIT corrected number of possible indexes

As Jeff points out it's even worse than 2^N (power-set) since (3,2,1) is clearly different than (1,2,3). For N columns we can choose the first position in an index that contains all columns in N ways. For the second position in N-1 ways, etc. We, therefore, end up with N! different indexes of full size. None of these indexes is subsumed by another index in this set. In addition, we can't add another shorter index so that it is not covered by any full index. The number of indexes is therefore N!. The example for 10 columns, therefore, becomes 10! = 3628800 indexes and for 20 (drumroll) 2432902008176640000 indexes. This is a ridicously large number, if we put a dot for each index one mm a part, it will take a lightbeam 94 days to pass all dots. All and all, dont;-)

  • 6
    Even worse: the order of columns in the index can be important. Therefore you get a maximum of N! indexes. – Jeff Jun 13 at 18:33
  • 2
    But you don't need indexes that are prefixes of other indexes. – Barmar Jun 13 at 18:48
  • 3
    It's even worse. There are ASC and DESC combinations for every index. – ypercubeᵀᴹ Jun 13 at 20:38
  • 2
    And far worse, there are INCLUDE indexes. – ypercubeᵀᴹ Jun 13 at 20:39
  • 2
    And a huge number of partial indexes. – ypercubeᵀᴹ Jun 13 at 20:39

No.

It's not practical to index "everything", but you can index "most" of it.

Here's the thing. If a table has N columns, then the number of possible indexes is N!. Let's say a table has 10 columns, then you don't only have 10 possible indexes, but 10!. That is... 3,628,800... on a single table. That's a lot of disk space, disk I/O, cache, and seek times.

Why? A few reasons:

  • Lightwwight indexes are usually cached, something that makes them lightinng fast. If you have 3 million of them, they are NOT going to be cached.

  • The SQL optimizer may take a lot of time deciding which one is better to use, specially when using joins.

  • The SQL optimizer may give up on using the comprehensive algorithm, and try a heuristic algorithm instead. This may be "less than optimal". PostgreSQL, for example, has different options for "less-than-8 table queries", and "more-than-8 table queries".

  • Indexes are supposed to be lighter than the heap. If you are indexing everything, then the index becomes as heavy as the heap... something that defeats the purpose of the index.

  • Isn't the number 2^10? Each column is either included or excluded from a given index. Does the order matter? – RemcoGerlich Jun 14 at 8:52
  • 2
    @RemcoGerlich yes, the order matters. – ypercubeᵀᴹ Jun 14 at 9:30

No, it probably won't have negative impact to the SELECT queries, but

  • It will cause a high disk usage.
  • It will hugely increase the INSERT costs.
  • Most of your indices won't be ever used.
  • Many WHERE condition expressions still won't use indices, mainly the more complex ones.
  • The count of the required indices will increase exponentially with the count of the columns. I.e. if you have, for example, 8 columns, you need 256 indices for all the possible combinations.
  • It can totally cause an issue for compile time. – sp_BlitzErik Jun 13 at 17:48
  • @sp_BlitzErik Do you think to the ORM in the app? – peterh Jun 13 at 17:50
  • No, see my answer. – sp_BlitzErik Jun 13 at 17:51
  • @sp_BlitzErik Wow, nice to see! – peterh Jun 13 at 17:58

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.