Advanced Indexing
Learn how to apply advanced indexing methods in PostgreSQL to improve database query speed and efficiency. Understand how to analyze query plans, use extensions for performance monitoring, and optimize SQL queries to reduce unnecessary data processing.
We'll cover the following...
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, ...