Chapter 3

1. The answer is definitely no. If something is always good, it would be there by default. Indexes can speed up many operations, but they can also slow down things considerably. The only rule is this: think about what you are doing and what you are trying to achieve.
2. It depends on the type of index. BRIN indexes are really small and fairly cheap, while other indexes usually need a lot more space. B-trees, for example, are around 2,000 times bigger than B-trees. Trigram-based indexes are even larger in most cases.

3. The best way, in my view, is to take a look at pg_stat_statements, and pg_stat_user_tables. seq_tup_read in particular is a really valuable column. If you are reading a really high number of rows, there might be an index missing. In general, are -n-depth look at the query is necessary to find out what is really going on. In short: EXPLAIN...