Under what conditions does PostgreSQL automatically create indexes without explicit instruction from the user?

PostgreSQL automatically creates indexes in the following cases without explicit user instruction:

• When creating a primary key (PRIMARY KEY): a unique index is created to ensure uniqueness and fast searching.
• When creating a unique constraint (UNIQUE): a unique index is also created to enforce the uniqueness of values.
• When creating a foreign key (FOREIGN KEY): an index is not automatically created, but it is recommended to create one manually for optimization.

Example:

CREATE TABLE users (
  id SERIAL PRIMARY KEY, -- will automatically create an index on id
  email VARCHAR(255) UNIQUE -- will create a unique index on email
);

Thus, indexes are automatically created to ensure data integrity and speed up searches on key fields.