databasepostgresqlindexingcomposite-index

Creating Composite indexing for postgres database fields


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.

  1. 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'

  2. 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 ?


Solution

  • 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.