Describe what an index is and how it improves query performance.

Official answer

An index is an additional access structure that can help the database locate rows, support ordering, or enforce a uniqueness rule. A B-tree index can help equality and range lookups on suitable keys; other index types serve other operators and data.

For PostgreSQL, an index on orders(customer_id, ordered_at) may help queries for one customer's orders in a time range. Column order and the actual predicates matter. The planner can still choose a sequential scan when many rows qualify or index access would cost more; creating an index does not force its use.

Indexes require storage and maintenance on writes. Wide or redundant indexes can increase insertion/update cost, and building an index on a large live table needs an operational plan. Measure relevant query latency and plans alongside write load and storage.

Check representative parameters, data distribution and statistics before retaining an index. Avoid removing a seemingly unused unique index without understanding the constraint it enforces. An index's ordering also does not guarantee the order of an arbitrary SELECT result without ORDER BY.