The author and publisher columns were just String pointers to actual Author and Publisher references that were on another system meaning that classical foreign keys couldn't be used and that these dudes were just normal columns.

I needed to get an idea how the table would perform with lots of data, so first up some simple SQL to put in lots of test data:

As can be seen, Postgres is doing a Seq Scan, aka a table scan. I wanted to speed things up.
There was only one index on the table which was for the id. This was just a conventional B-Tree index which would be useless in this query since it wasn't even in the where clause. Some of options I was thinking about:

As can be seen Postgres performs an index scan and the total cost is much lower than the same query which uses a table scan. What about the multiple column index approach? Surely, since both are used in the query it should be faster again, right?

There are lots of rows, but every row has a unique author value and a unique publisher value. That would mean the author index for this query should perform just as well. An analogy would be, you go into a music shop looking for a new set of loudspeakers someone has told you to buy that have a particular cost and a particular power output (number of watts). When you enter the shop, you see the speakers are nicely ordered by cost and you know what? No two sets of loudspeakers have the same cost. Think about it. Are you going to find the speakers any faster if you use just use the cost or you use the cost and the loudspeaker?

Now, imagine the case if lots of the loudspeakers were the same cost. Then of course using both the
cost and the power will be faster.

Now, let's take this point to the extremes in our test data. Suppose all the authors were the same. The author index becomes useless and if we don't have the author / publisher combination index we would go back to table scan.

So we can conclude from this that single column indexes for combination searches can perform as well as combinational indexes when there is a huge degree of variance in the data of that single column. However, when there isn't, they won't perform as well and a combinational index should be used. Yes, I have tested by going to extremes but that is the best way to make principles clear.And please note: For the case when there is maximum variance in data, adding another index to the other column in the where clause, publisher made no difference. This is as expected.

Ok, let's stick with the case when there is massive variance in data values in the column. Consider the case of maximum variance and the query only ever involves exact matching. In this case, all authors values are guaranteed to be unique and you never have any interest in doing anything like less than or greater than. So why not use a hash index instead of a B-Tree index?

Interesting, we have gone faster again. Not a massive difference this time around but an improvement nonetheless that could be more relevant with more data growth and / or when a more complex query with more computation is required. We can safely conclude from this part that yeah if you are only interested in exact matches then the hash index beats the b-tree index.
Until the next time take care of yourselves.
References:

About Me

Alex Staveley is a software professional passionate about software
engineering and technical architecture. He blogs about architectural
approaches, Java topics, web solutions and various technical bits and
pieces.