The cost of indexing: it uses more storage to use the data structure. A table with eight indexes has nine things to keep warm in cache, not just one. The more indexes you have, the more options the query planner must weighs.

Why index is not working?

• composite indexes care about order
• functions defeat the index: case-insensitive does not match an index created on the fly. It applies to any function wrapping the column.

How to avoid these pitfalls? Measure.
There is the tool EXPLAIN in Postgres telling how it plans to run a query. Using it before the query explains it :) The Index Scan instruction is what is looked for.
EXPLAIN ANALYZE runs the query and reports what happened.

Things nobody shares:

functional indexes
CREATE INDEX ON pokemon (lower(name));
but why are the data not stored in lowercase in the first case

partial indexes
avoid a full index
CREATE INDEX ON pokemon (name) WHERE is_legendary = true;

covering indexes
If the index already contains every column the query needs, the database can answer the query from the index alone, never touching the table.
CREATE INDEX ON pokemon (name) INCLUDE (base_attack);. INCLUDE is a way to say “carry this column along for the ride, but don’t bother sorting by it”. Use it if the columns whose data types don’t have an appropriate operator class for the index type, or add columns to a unique index without changing its uniqueness semantic.