**What are general rules when deciding on index?**
- If selectivity of condition is low (almost all rows match) – there is no point in putting the column in index
- If you have condition that you “always" use (or, at least very often) – you can put it in WHERE part of index definition
- Don't add too many columns to index – it will make it larger, and less efficient. If you can get 2 columns to match 1000 rows out of million, and adding 3rd will reduce the match to 900 – don't add the 3rd – it will be, most likely, better in the long term to do index scan on these two columns only, and filter the rest normally.
- Remember that generally speaking you can have only one inequality condition in query being efficiently handled by index scan
- Remember that any columns in index after column used for inequality comparison, will be most likely not compared against index, but rather will be filtered out

[Why is it hard to automatically suggest what index to create?](https://www.depesz.com/2021/10/22/why-is-it-hard-to-automatically-suggest-what-index-to-create/)

What are general rules when deciding on index?

相關文章