Postgresql – Index for column with two possible value

indexpostgresql

I use PostgreSQL 9.4.

I have a database where a table have index in column which can contains two possible value only(gender: male/female). When I execute a query with condition on this column index was not use.
It's bad idea use index in such column?

If index useless in this case, will I have update for this index when add new record. In other words: Will I have increase insert speed if I remove this index?

SQL for index:

CREATE INDEX index_participations_on_gender
ON participations
USING btree
(gender COLLATE pg_catalog."default");

My query:

SELECT 
  participations.last_name, 
  participations.first_name, 
  participations.birthday, 
  participations."number", 
  participations.chip_id, 
  participations.gender
FROM 
  public.participations
WHERE 
  participations.gender = 'male';

Rows in the table: 64982

Thanks.

Best Answer

The optimizer evaluates the possible gains of using indexes vs doing a full scan (filtering unwanted rows on the fly). As the ratio of filtered and total rows gets closer to 1 the benefit of using index decreases. The exact tipping point is dependent on the actual data, query, etc. so it's hard to say an exact number when it becomes useless. Generally speaking a 50/50 split is not sufficient for a B+TREE index to work efficiently and most cases full scan will be preferred over using indexes. (Unless it can be used for index-only scans)

The index is still getting updated regardless of its usefulness in queries.

Related Question