PostgreSQL - Indexes

PostgreSQL - Indexes

Indexes are crucial database objects that can dramatically speed up data retrieval. An index acts like a book’s index: instead of scanning the entire book (the table) for a topic, the database can use the index to find the exact location of the relevant data quickly.

While indexes significantly accelerate SELECT queries with WHERE clauses and JOIN operations, they have a cost. Every INSERT, UPDATE, or DELETE operation on an indexed column requires a corresponding update to the index, which adds a small amount of overhead. The key is to index strategically.

Creating Indexes: The CREATE INDEX Command

The CREATE INDEX statement is used to create a new index. You must provide a name for the index and specify the table and column(s) it applies to.

CREATE INDEX index_name ON table_name (column_name, ...);

Index Types

PostgreSQL offers several index types, each using a different algorithm suited for specific query patterns. The default and most common type is B-Tree.

• B-Tree: The default. Excellent for equality (=) and range (<, >, BETWEEN) queries on sortable data. It’s the general-purpose workhorse.
• Hash: Only useful for simple equality comparisons (=). They are not WAL-logged and may need manual rebuilding after a crash. Generally, B-Tree is preferred.
• GiST (Generalized Search Tree): Used for indexing complex data types, such as geometric data (finding overlapping shapes) and full-text search.
• SP-GiST (Space-Partitioned GiST): An enhancement of GiST for specific non-balanced data structures like quad-trees and k-d trees.
• GIN (Generalized Inverted Index): Ideal for indexing composite values where elements can appear multiple times, such as the words in a tsvector for full-text search or elements in a JSONB array.
• BRIN (Block Range Index): Best for very large tables where data has a natural physical correlation with its value (e.g., a timestamp column in a log table). They are very small and efficient.

Common Indexing Strategies

Single-Column and Multi-Column (Composite) Indexes

Index the columns frequently used in WHERE clauses. If you often filter by a single column, a single-column index is sufficient. If you filter by two or more columns together, a composite index is much more effective.

-- Single-column index
CREATE INDEX idx_products_price ON products (price);

-- Composite index for queries filtering by both category and status
CREATE INDEX idx_products_category_status ON products (category_id, status);

Unique Indexes

A unique index enforces data integrity by ensuring that no two rows have the same value in the indexed column(s). A PRIMARY KEY constraint automatically creates a unique index.

-- Ensures every user has a unique email address
CREATE UNIQUE INDEX idx_users_email_unique ON users (LOWER(email));

Partial Indexes

A partial index covers only a subset of a table’s rows, defined by a WHERE clause. This is highly efficient for creating smaller indexes on a frequently queried subset of data.

-- Index only active, unpaid orders for a payment processing system
CREATE INDEX idx_orders_pending_payment ON orders (customer_id)
WHERE status = 'pending' AND is_paid = false;

Expression Indexes

You can index the result of a function or expression. This is extremely powerful for queries that use functions in the WHERE clause, like case-insensitive searches.

-- Speeds up case-insensitive searches for usernames
-- Without this index, `WHERE LOWER(username) = 'admin'` would be slow.
CREATE INDEX idx_users_username_lower ON users (LOWER(username));

Covering Indexes (INCLUDE Clause)

A covering index includes extra non-key columns. This allows for “index-only scans,” where PostgreSQL can answer the query using only the index, without ever visiting the table itself, providing a significant performance boost.

-- This query can be answered using only the index
-- SELECT email, last_login FROM users WHERE user_id = 123;
CREATE INDEX idx_users_user_id_cover_email ON users (user_id) INCLUDE (email, last_login);

Verifying Index Usage with EXPLAIN

How do you know if your index is being used? The EXPLAIN command shows the query execution plan. EXPLAIN ANALYZE executes the query and shows the plan along with actual timing.

-- Before creating an index, you might see a 'Seq Scan' (Sequential Scan)
EXPLAIN ANALYZE SELECT * FROM users WHERE id = 500000;

-- >> QUERY PLAN: Seq Scan on users (cost=0.00..18334.00 rows=1 width=80) (actual time=25.4..52.1 ms)

-- Create the index
CREATE INDEX idx_users_id ON users (id);

-- After creating the index, the plan should show an 'Index Scan'
EXPLAIN ANALYZE SELECT * FROM users WHERE id = 500000;

-- >> QUERY PLAN: Index Scan using idx_users_id on users (cost=0.43..8.45 rows=1 width=80) (actual time=0.04..0.05 ms)

Managing Indexes

Dropping an index is straightforward but should be done with caution, as it can severely degrade query performance.

DROP INDEX IF EXISTS idx_products_price;

You can list all indexes in the current database with the psql command \di.

When to Avoid or Reconsider Indexes

• On small tables: The overhead of an index scan might be greater than a simple sequential scan.
• On tables with heavy write operations: If a table has very frequent INSERT/UPDATE operations but infrequent reads, the cost of maintaining indexes might outweigh the benefits.
• On columns with low cardinality: Indexing a boolean or gender column is often ineffective, as the planner will likely opt for a table scan anyway.
• Redundant indexes: Avoid creating indexes that are covered by another existing index. For example, if you have an index on (a, b), you do not need a separate index on (a).