tl;dr:"A rough guide on how to think through indexing strategy in most Postgres databases: (1) Every index has a read and write cost. Postgres considers these costs for each query to determine which indexes to use. (2) The more selective your index, the more efficient it becomes to read data from the table itself. But, more selective indexes (multicolumn indexes, sorted indexes, partial indexes) require specific query patterns to maximize this efficiency." And more.