I have the below table in postgresql database
ID | first_name | last_name | class | position |
--------------------------------------------------------
1 | Teemo | Shroomer | Specialist | Top |
2 | Cecil | Heimerdinger | Specialist | Mid |
3 | Annie | Hastur | Mage | Mid |
4 | Fiora | Laurent | Slayer | Top |
5 | Garen | Crownguard | Fighter | Top |
6 | Malcolm | Graves | Specialist | ADC |
7 | Irelia | Lito | Figher | Top |
8 | Janna | Windforce | Controller | Support |
And have 3 WHERE clauses in 3 different stored procedures.
WHERE first_name = 'Annie'
WHERE first_name = 'Annie' AND class = 'Mage'
WHERE first_name = 'Janna' AND class = 'Controller' AND position = 'Support'
And I created index as below for my table as below
CREATE INDEX first_name_index ON users (first_name);
CREATE INDEX first_name_class_index ON users (first_name, class);
Couple of question I have for the created index.
Since I already have composite index for first_name + class. Do I really need the single index for first_name field only. If I understand it correctly, then the search using first_name only, can be benefited from the composite index 'first_name_class_index'
Do I get any benefit using 'first_name_class_index' If I used the 3rd WHERE clause(with 3 fields). Will the indexing internally perform search using the composite index(first_name + class) first, then from the obtained list, searches the 'position'. Is it correct ?
The single-column index is mostly useless, and you should drop it. It can't do anything that the two-column index cannot do; its only benefit is that it is smaller, so PostgreSQL would prefer it for the first query.
The two-column index will also help with the third query. With enough data in the table, PostgreSQL will usually use an index scan and add the third condition as a filter.