Tell me about indexes in databases: what types are there, when is an index ignored, and when does the database perform a sequential scan?

Database indexes are data structures that speed up search and sorting on specific columns. Main types of indexes:

• B-tree — the most common, suitable for exact searches, ranges, sorting.
• Hash — fast for exact equality, but does not support ranges.
• Bitmap — effective for columns with low cardinality (e.g., gender).
• GIN/GiST — for full-text search, working with arrays and complex types.

An index can be ignored and a sequential scan (seq scan) performed in the following cases:

• If the query selectivity is low (e.g., the condition returns most of the table), then seq scan may be faster.
• If statistics are outdated or missing, the planner may choose seq scan.
• When using functions or expressions not covered by the index.
• If the index does not cover all needed columns and additional reading from the table is required.
• In case of locks or index issues.