Under what circumstances does PostgreSQL automatically create an index during certain operations or conditions?

PostgreSQL automatically creates indexes in the following cases:

• When creating a primary key (PRIMARY KEY) or a unique constraint (UNIQUE), the DBMS automatically creates a unique index to ensure fast searching and uniqueness checking.
• When using certain types of foreign keys, an index may be automatically created to speed up referential integrity checks.

However, PostgreSQL does not automatically create indexes for regular columns or when creating other constraints such as CHECK. To optimize queries, indexes need to be created manually.

Example of creating a primary key that will automatically create an index:

CREATE TABLE users (
    id SERIAL PRIMARY KEY,  -- index will be created automatically
    name TEXT
);