How to build an efficient index for a column with low selectivity (for example, status: new/running/finish), when only the 'new' status is important?

For a column with low selectivity, for example, a status with three values (new, running, finish), a traditional index on the entire column will be inefficient because it does not significantly reduce the number of rows to search.

If only the "new" status is important, the following approaches can be used:

1. Partial Index — index only those rows where status = 'new'. This will reduce the size of the index and speed up queries filtering by this status.

   Example for PostgreSQL:

   CREATE INDEX idx_status_new ON table_name(column_name) WHERE status = 'new';
   

2. Bit masks or a separate boolean field — if the status changes frequently and only one of them is important, you can create a separate field is_new BOOLEAN and index it.

3. Materialized view or a separate table — if queries by 'new' status are very frequent, you can allocate such records to a separate table with an index.

4. Using bitmap indexes — in some DBMSs (e.g., Oracle), bitmap indexes work well with low-selectivity columns.

Thus, the most universal and simple solution is to create a partial index that indexes only records with the 'new' status. This will provide a significant performance gain when querying by this status.