Should I remove indexes with cardinality=1 ?

[Arnaud Lesauvage]

Hi all !

We experienced very poor performance on a query.
This query is quite complex (4 tables and a subquery), so I won't post it here unless you really need it.

The point is that the query was running quite fast (~3 seconds), and just adding one "WHERE" criteria made it crawl almost to a stop.
We just added the criteria "AND table2.field3 = 1". This table2.field3 is indexed, and it appears that the cardinality of the index is 1 (~300k rows in the table, only two different values for this field).

What is surprising is that the query plan (showed by explain) is good in the first query, but when adding the new criteria, the table2 comes in first in the plan and the key "field3" is used.
How can this be, since the cardinality is 1 ? The planner should know that using this index is very bad, shouldn't it ?

I just dropped the bad index, and performance was back again.
So should I check all my indexes and remove all the ones with a low cardinality ?

Thanks for any advice !

Arnaud