Since all dbms engines are so very different from each other, it would be nearly impossible to figure out what index setup will be best.
But, with a good planner, at least the next best choice should be made when you run a query.
For instance, if you're returning 98% of your table, should you use an index? Of course not. what about 25% of the table? Or 1% or 65%? If you're returning 1% of a table it makes great sense to use an index. at some point, using an index actually slows things down. So, firstly, only index things that you'll be retrieving smaller portions of generally.
Also, larger tables tend to benefit more than smaller tables. A table with 3 rows of ~100 bytes each easily fits in a single page on most databases, and therefore an index would do no good.
Notice that you can also create partial or functional indexes that have great uses too. for instance, let's say you have an article table with a boolean to say if the articles are approved. An index on the boolean field for both approved and disapproved makes little sense, since 99% of the articles are likely to be approved, you'll get little help from there and a seq scan makes more sense if that's all you're searching on. so, an index like this makes sense:
create index dxname on table (id) where approved if false;
then
select * from table where id=123 and approved is false can use that index.
