Advanced Indexing

The PostgreSQL documentation about indexes covers everything we need to know, in detail, including the following:

• Multicolumn indexes
• Indexes and ORDER BY
• Combining multiple indexes
• Unique indexes
• Indexes on expressions
• Partial indexes
• Partial unique indexes
• Index-only scans

There are more indexes so consider reading this PostgreSQL chapter in its entirety as the content is not repeated in this course. Still, we’ll need it to make informed decisions about our indexing strategy.

Adding indexes

Deciding which indexes to add is central to our indexing strategy. Not every query needs to be that fast, and the requirements are mostly user-defined. That said, a general system-wide analysis can be achieved thanks to the PostgreSQL extension pg_stat_statements.

Once this PostgreSQL extension is installed and deployed, ...