Index

Query runtime and index

1. 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 or WHERE column BETWEEN min AND max) can still benefit from indexes but may not be as efficient.

2. 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 the column effectively.

3. 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 like WHERE column1 = value can benefit from the index, but WHERE column2 = value might not.

4. 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.

5. 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)