As you already realized, the problem is related to using operators other than equals. An index can only be used most efficiently for the leftmost columns that are compared with by equals (plus one range condition).
In your example:
create index i on t (a,b,c,d);
where a=1 and b=11 and c!=5 and d<8;
It can use the index only for a and b efficiently. That means the DB fetches all rows matching the a and b condition and then checks each row against the remaining conditions.
When you change the filter on c to equals, it fetches (potentially) less rows (only those matching a and b and c) and then checks those (fewer) rows against the d filter. Using the index is more efficient in this case.
In general, the PostgreSQL query planner evaluates both options: (1) using the index; (2) doing a SeqScan. For both, it calculates a cost value — the higher it is the worse is the expected performance. Consequently, it takes the one with the smaller cost value. This is how it decides to use the index or not, there is no fixed threshold.
Finally, is wrote "plus one range condition" above. That means that it can not only use the index in the most efficient way if you are using equals signs, but also for one single range condition.
Considering that you have one single range condition in your query, I'd suggest to change the index like this:
create index i on t (a,b,d,c);
Now it can use the the filters on a and b and d efficiently with the index and only needs to filter the rows away where c!=5. Although this index can be used more efficiently for your query as your original one, it doesn't automatically mean PG will use it. It depends on the cost estimates. But give it a try.
Finally, if this isn't fast enought and the value 5 you are using in the expression c!=5 is constant, you might consider a partial index:
create index i on t (a,b,d)
where c!=5;
You could do that with all other columns too, if the values you compare them against are constants.
References:
