Index for column with two possible value

Question

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.

Answer 1

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.

Answer 2

The only query this would really benefit would be counting the male / female ratio. examples of queries your trying to run would be useful to give examples of what would help. the issue you have is an index that is basically a bit is that you put half of your data in one half, and half in the other half, there isn't actually any organisation to that index, it is barely any better than a heap,

Consider your table as a multiple deck of cards(distinguishable by different backs), and you're looking for the 6 of hearts, the fact that its red(female) only helps you find all the red cards in those decks, from there, you still need hearts, and the fact that its a six, and which colour back it is (address = colour of back, age = number) both those other factors will narrow down your search far more than is it red or black.

On the note of speed increase, technically yes your speed for inserting / deleting will increase (update shouldn't as you should very very rarely change someone's gender) the increase in speed will be minimal however since there isn't really any sorting going on to the index. I imagine the index is quite small since if it is M/F then every record is one character and the lookup to the main data.

Answer 3

Indexes exist to make finding small groups of records [much] quicker.

If this field contains only two values, then it's a poor "discriminator"; filtering by either of the values it can contain doesn't return you a significantly smaller number of rows than the table as a whole. This is why Postgres doesn't bother using it. Most likely, it's quicker for Postgres to "drag race" through the entire table, doing a quick comparison on each row, rather than looping through the index pages and then hopping across to the data pages for the other data you might need.

It might be that including this field in a composite index, containing other fields, might prove useful. YMMV.