0

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.

  • Not enough info. What sort of index? How many rcords? Skew - i.e. how many males/females? Your record structre DML. Your query. – Vérace May 4 '16 at 11:17
  • For a condition that would select half of the rows an index lookup won't make things faster. And please read: wiki.postgresql.org/wiki/SlowQueryQuestions then edit your question and add the missing information – a_horse_with_no_name May 4 '16 at 11:19
  • Updated my post. – kunashir May 4 '16 at 11:31
  • Is your data skewed? – Vérace May 4 '16 at 11:52
  • @Vérace, not very much: male - 39973, female - 24346. – kunashir May 4 '16 at 11:58
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.

1

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.

  • Thank you for your explanation. I try next query Select participants.* FROM public.participations WHERE participations.gender = 'male' AND participations.number = 10, index still not use. – kunashir May 4 '16 at 11:41
  • Since the index is purely on the .gender then it will need to do a key lookup to the another index(or data) to pull out the .number value, at this point it would possibly easier to look for the .number then work out which of those remaining are male, even if that means looking over a full table scan it will more than likely be more efficient than finding half the values in your table and then running a keylookup to each of them, (using a deck of cards again) take all the red cards and you have 26 cards to find the 10s, if you take the 10s out, then you only have 4 cards to find the reds – Ste Bov May 4 '16 at 11:47
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.