Avoid over-indexing, duplicate indexes, low-value indexes, stale statistics, and maintenance mistakes.
Mistakes that waste storage and slow writes without helping queries much.
Do not keep several indexes that serve the same leading prefix needlessly.
Potential overlap:
INDEX (user_id)
INDEX (user_id, created_at)
The second index may already cover many uses of the first.Review actual query patterns before keeping both. Some overlap is justified, but not all of it.
A boolean by itself is often weak unless paired with something else or made partial.
-- Often weak by itself
INDEX (is_deleted)
-- Often better
INDEX (is_deleted, created_at)
-- or a partial index where supportedA low-cardinality flag alone may still leave too much work for the engine.
Every INSERT, UPDATE, and DELETE may need to update every relevant index.Unused indexes are not free and should be reviewed periodically.
Housekeeping that keeps planners and indexes healthy.
REINDEX INDEX idx_orders_user_id;Use with care and pair with engine-specific operational guidance in production.
ANALYZE TABLE users;Statistics help the optimizer estimate row counts more accurately.
VACUUM;Not an index command specifically, but often relevant for long-lived local databases after large deletions or churn.