Questions

Do indexes always improve performance?

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: Thing about what you are doing and what you are trying to achieve.

Does an index use a lot of space?

It depends on the type of indexes. BRIN indexes are really small and fairly cheap, while other indexes usually need a lot more space. btrees for example are around 2.000 times bigger than btrees. Trigram based indexes are even larger in most cases.

How can I find missing indexes?

The best way in my judgement is to take a look at pg_stat_statements and pg_stat_user_tables. Especially seq_tup_read is a really valuable column. If you are reading a really high number of rows, there might be an index missing. In general a deep look at the query is necessary what is really going on. In short: EXPLAIN is your friend under all circumstances.

Can indexes be built in parallel?

Yes, since PostgreSQL 11 we have support for parallel index creation. It can speed up index creation considerably.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset