Indexes are a basic structure type that apply to one or multiple columns in order to improve performance and speed up queries that: filter, sort or join data for a table.
this may improve performance for a query that uses last_name in a WHERE clause or an ORDER BY
CREATE INDEX idx_last_name ON employees (last_name);
you can also create composite indexes
CREATE INDEX idx_composite ON employees (last_name, first_name)
and also composite index for only active employees
CREATE INDEX idx_active_employees ON employees (status) WHERE status = 'active';
When to use indexes
Frequent filters WHERE - if you usually filter by a specific column (last_name for example), an index should improve performance.
JOIN for big tables - when you use JOIN with big tables through PKs or FKs.
ORDER BY or GROUP BY - queries that search for / group by a specific column also benefit from indexes.
Best practices
Don’t create indexes in every column. This slows things down on insert, delete or update operations.
Index are best used for big domain fields such as ids, names, surnames. Don’t use them for male/female (or boolean) fields.
Keep indexes optimized: operations where you mass update or mass delete items in your tables may fragment your indexes. You may need to periodically check them and REINDEX them.
ALWAYS MEASURE PERFORMANCE TIME before and after the creation of that index. If your index doesn’t improve performance, remove it as it causes overhead.
Reference(s)
https://stackoverflow.com/questions/7744038/decision-when-to-create-index-on-table-column-in-database
https://stackoverflow.com/questions/52444912/how-to-find-out-fragmented-indexes-and-defragment-them-in-postgresql
https://chatgpt.com/