Index
Query runtime and index
Equality vs. Range Predicates:
Indexes are most effective with equality predicates (e.g.,
WHERE column = value
). This allows the database to perform direct lookups using the index.Range predicates (e.g.,
WHERE column > value
orWHERE column BETWEEN min AND max
) can still benefit from indexes but may not be as efficient.
Avoid Functions on Columns:
Applying functions to columns in the WHERE clause can prevent the use of indexes. For example,
WHERE UPPER(column) = 'VALUE'
might not use an index on thecolumn
effectively.
Combine Index Columns:
If there are composite indexes (indexes on multiple columns), make sure to use the leading columns in the index in your predicates. For example, if you have an index on
(column1, column2)
, a query likeWHERE column1 = value
can benefit from the index, butWHERE column2 = value
might not.
Consider NULLs:
Be aware that indexes may not include rows with NULL values. If your query involves comparisons with NULL, check how your database handles this, and if necessary, design indexes accordingly.
Use SARGable Queries:
Write "sargable" (Search ARGument ABLE) queries, which are queries that can take advantage of indexes. Avoid complex expressions or operations in the WHERE clause that prevent the use of indexes.
Price to have an index for the table:
Slow down write operations (because not only data should be written to the table, but also to the index)
Index takes memory (sometimes similar or even more space than data itself, which is a bad sigh of index, this is wrong)
in Postgres when you need to change an index, you do not delete entries in index, you mark them for deletion and create new records. These marked entries are cleaned later by
vacuum
tool.This creates bloat
It is even advised to periodically recreate an index
Check list for creating index:
take into account only Production databases (load models)
have a statistical info about which requests
⭐pg_stat_statments (this is good choice)
❓pgBadger (be careful using it)
Last updated