PostgreSQL High Performance Cookbook

Chapter 12.  Database Indexing

In this chapter, we will cover the following recipes:

• Measuring query and index block statistics
• Index lookups
• Comparing indexed scans and sequential scans
• Clustering against an index
• Concurrent indexes
• Combined indexes
• Partial indexes
• Finding unused indexes
• Forcing a query to use an index
• Detecting missing indexes
• Concurrent indexes

Introduction

Indexes play a crucial role in a database as they improve the query response time by adding an amount of overhead for each write operation of a table. Creating or dropping an index is an important responsibility for a DBA as they need to make a choice by looking at the read and write ratio of the database tables. A wise move would definitely be to increase the database performance, ...