One can classify indexes on a high level as follows:

• Partial index: A partial index indexes only a subset of the table data that meets a certain predicate; the WHERE clause is used with the index. The idea behind a partial index is to decrease the index size, thus making it more maintainable and faster to access.
• Unique index: The unique index guarantees the uniqueness of a certain value in a row across the whole table. In the account table, the email column has a unique check constraint. This is implemented by the unique index, as shown by the d meta command:

d account
                              Table "car_portal_app.account"
   Column | Type | Collation | Nullable | Default 
------------+---------+-----------+----------+---------------------------------------------
 account_id | integer | | not null | nextval('account_account_id_seq'::regclass)
 first_name | text | | not null | 
 last_name | text | | not null | 
 email | text | | not null | 
 password | text | | not null | 
Indexes:
    "account_pkey" PRIMARY KEY, btree (account_id)
    "account_email_key" UNIQUE CONSTRAINT, btree (email)

• A multicolumn index can be used for a certain pattern of query conditions. Suppose a query has a pattern similar to the following: SELECT * FROM table WHERE column₁ = constant₁ and column₂= constant₂ AND … column_n = constant_n; in this case, an index can be created on column₁, column₂,…, and column_n, where n is less than or equal to 32.
• Index on expression: As stated earlier, an index can be created on a table column or multiple columns. One can also be created on expressions and function results.

One can also mix different categories together, for example, one can create a unique partial index, and so on. This gives the developer greater flexibility to meet business requirements or to optimize for speed. For example, as shown in later chapters, one can use index to optimize a case-insensitive search by simply creating an index using the function lower() or upper() as follows: 

car_portal=> CREATE index on account(lower(first_name));
CREATE INDEX

The index we just saw, will speed the performance of searching an account by names in a case-insensitive way, as follows:

SELECT * FROM account WHERE lower(first_name) = lower('foo');

Another usage for expression indexes is to filter rows by casting a data type to another data type. For example, the departure time of a flight can be stored as a timestamp ; however, we often search for a date and not a time. 

As we said earlier, one can also use unique and partial indexes together. For example, let's assume that we have a table called employee, where each employee must have a supervisor, except for the company head. We can model this as a self-referencing table, as follows:

CREATE TABLE employee (employee_id INT PRIMARY KEY, supervisor_id INT);
ALTER TABLE employee ADD CONSTRAINT supervisor_id_fkey FOREIGN KEY
(supervisor_id) REFERENCES employee(employee_id);

To guarantee that only one row is assigned to a supervisor, we can add the following unique index:

CREATE UNIQUE INDEX ON employee ((1)) WHERE supervisor_id IS NULL;

The unique index on the constant expression (1) will allow only one row with a null value. With the first insert of a null value, a unique index will be built using the value 1. A second attempt to add a row with a null value will cause an error because the value 1 is already indexed:

car_portal=> INSERT INTO employee VALUES (1, NULL);
INSERT 0 1
car_portal=> INSERT INTO employee VALUES (2, 1);
INSERT 0 1
car_portal=> INSERT INTO employee VALUES (3, NULL);
ERROR: duplicate key value violates unique constraint "employee_expr_idx"
DETAIL: Key ((1))=(1) already exists.

Currently, only B-tree, GIN, GIST and BRIN support multi-column indexes. When creating a multi-column index, the column order is important. Since the multi-column index size is often big, the planner might prefer to perform a sequential scan rather than reading the index.