Indexing every WHERE column is not PostgreSQL optimization

Source: Dev.to

One PostgreSQL indexing mistake I see often:

“The query filters on A, B and C, so let’s create an index on A, B, C.”
That may work, but it may also be the wrong index.

Composite B‑tree index considerations

• Predicate type (equality vs. range)
• Column order
• Selectivity
• Table size
• The actual execution plan

Predicate ordering

Equality predicates usually belong before range predicates. Placing a range predicate first can prevent the planner from using the later columns efficiently.

Statistics (n_distinct)

The n_distinct value from column statistics influences selectivity estimates. An index that looks good on paper can be useless if the planner’s statistics suggest otherwise.

When a good index is ignored

Even a theoretically optimal index may be ignored if the planner never deems it beneficial for the given query plan.

Automated recommendation with pgAssistant

pgAssistant automates the index recommendation workflow by:

1. Running EXPLAIN ANALYZE on the query.
2. Inspecting planner statistics.
3. Suggesting the most appropriate index based on the observed execution plan.

Full write‑up

https://beh74.github.io/pgassistant-blog/post/query_advisor/