PostgreSQL Index Internals and Optimization

1. What You'll Learn

• PostgreSQL's 6 index types: B-Tree / Hash / GIN / GiST / BRIN / SP-GiST
• CREATE INDEX / UNIQUE INDEX / CONCURRENTLY
• Partial indexes (WHERE condition) — a PostgreSQL specialty
• Expression indexes
• Composite indexes and the leftmost-prefix rule
• Reading execution plans with EXPLAIN / EXPLAIN ANALYZE
• REINDEX index maintenance
• Common scenarios where indexes fail to be used

2. The Story

Bob is the DBA for an e-commerce platform. Users report that the product search page keeps getting slower — a full scan of a JSONB column makes queries take 3 seconds.

After Bob adds a GIN index to the JSONB column, search drops to 300 ms. He then notices that active-user queries are also slow, yet 90% of users have already been deactivated, so building an index on the whole table wastes space. He uses a partial index that only indexes rows where status = 'active', shrinking the index size by 80% and making queries faster.

3. Concept: Index Type Overview

(1) The Six Index Types

▶ Example: View Existing Indexes on a Table

SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename = 'orders';

 indexname              | indexdef
------------------------+--------------------------------------------------
 orders_pkey            | CREATE UNIQUE INDEX ... ON orders USING btree (order_id)
 idx_orders_customer_id | CREATE INDEX ... ON orders USING btree (customer_id)

(2) Index Type Selection Decision

flowchart TD
    A{Query type?} -->|Equality/range/sort| B[B-Tree]
    A -->|Simple equality| C[Hash]
    A -->|Array/JSONB contains| D[GIN]
    A -->|Spatial/geometric/nearest| E[GiST]
    A -->|Large table seq scan| F[BRIN]
    A -->|Irregular partition structure| G[SP-GiST]

    B --> B1["Default index type<br/>90% of cases"]
    C --> C1["Rarely used<br/>B-Tree usually better"]
    D --> D1["JSONB @> ?<br/>array @> <br/>tsvector @@ "]
    E --> E1["PostGIS<br/>range overlap"]
    F --> F1["10M+ row time-series<br/>tiny size"]
    G --> G1["Phone prefix<br/>IP routing"]

    style A fill:#e1f5fe
    style B fill:#c8e6c9
    style D fill:#fff9c4
    style F fill:#fff9c4

flowchart TD
    A{Query type?} -->|Equality/range/sort| B[B-Tree]
    A -->|Simple equality| C[Hash]
    A -->|Array/JSONB contains| D[GIN]
    A -->|Spatial/geometric/nearest| E[GiST]
    A -->|Large table seq scan| F[BRIN]
    A -->|Irregular partition structure| G[SP-GiST]

    B --> B1["Default index type<br/>90% of cases"]
    C --> C1["Rarely used<br/>B-Tree usually better"]
    D --> D1["JSONB @> ?<br/>array @> <br/>tsvector @@ "]
    E --> E1["PostGIS<br/>range overlap"]
    F --> F1["10M+ row time-series<br/>tiny size"]
    G --> G1["Phone prefix<br/>IP routing"]

    style A fill:#e1f5fe
    style B fill:#c8e6c9
    style D fill:#fff9c4
    style F fill:#fff9c4

4. Concept: B-Tree Index

(1) B-Tree Features and Use Cases

B-Tree is PostgreSQL's default index type; it supports equality, range, sorting, IS NULL, and prefix LIKE queries.

▶ Example: Create a B-Tree Index

CREATE INDEX idx_orders_amount ON orders (amount);

CREATE INDEX idx_orders_date_amount ON orders (order_date, amount DESC);

Output:

CREATE TABLE

▶ Example: UNIQUE Index

CREATE UNIQUE INDEX idx_users_email ON users (email);

Output:

CREATE TABLE

A UNIQUE index guarantees both data uniqueness and query performance.

5. Concept: GIN Index

(1) GIN Index Principle

GIN (Generalized Inverted Index) is an inverted index: a mapping from an element to the rows that contain it. It fits "contains"-style queries.

▶ Example: GIN Index on a JSONB Column

CREATE INDEX idx_products_attrs ON products USING GIN (attrs);

SELECT product_id, name
FROM products
WHERE attrs @> '{"category": "electronics"}';

 product_id |    name
------------+------------
        101 | Laptop Pro
        205 | Smart Watch

▶ Example: GIN Index on an Array Column

CREATE INDEX idx_products_tags ON products USING GIN (tags);

SELECT product_id, name
FROM products
WHERE tags @> ARRAY['summer', 'sale'];

Output:

  result  
----------
   42.50
(1 row)

▶ Example: GIN Index for Full-Text Search

CREATE INDEX idx_articles_body ON articles USING GIN (to_tsvector('english', body));

SELECT id, title
FROM articles
WHERE to_tsvector('english', body) @@ to_tsquery('postgresql & index');

Output:

CREATE TABLE

(2) GIN vs B-Tree Comparison

6. Concept: GiST / BRIN / SP-GiST / Hash

(1) GiST Index

GiST is a generalized search-tree framework that supports custom partitioning strategies. Its typical use is spatial data.

▶ Example: GiST Index on a Range Type

CREATE INDEX idx_events_time_range ON events USING GiST (time_range);

SELECT event_id, title
FROM events
WHERE time_range && daterange('2025-01-01', '2025-03-01');

Output:

CREATE TABLE

(2) BRIN Index

BRIN (Block Range Index) stores summary information for each data block (min/max values). It is extremely compact and suits physically ordered large tables.

▶ Example: BRIN Index on a Time-Series Table

CREATE INDEX idx_logs_created_at ON logs USING BRIN (created_at)
  WITH (pages_per_range = 32);

SELECT count(*) FROM logs
WHERE created_at BETWEEN '2025-06-01' AND '2025-06-30';

Output:

 count 
-------
     5
(1 row)

(3) SP-GiST Index

SP-GiST suits unbalanced partitioning structures, such as phone-number prefixes and IP routing.

▶ Example: SP-GiST Index on a Phone-Number Prefix

-- Enable the btree_gist extension first: CREATE EXTENSION IF NOT EXISTS btree_gist;
CREATE INDEX idx_customers_phone ON customers USING SP-GiST (phone prefix_range);

Output:

CREATE TABLE

(4) Hash Index

A Hash index only supports simple equality queries; it does not support range or sorting. Before PostgreSQL 10, Hash indexes had a WAL problem (now fixed), but B-Tree is generally the better choice.

7. Concept: Advanced Index Features

(1) Partial Index

A partial index contains only the rows that satisfy a WHERE condition, reducing index size and maintenance cost. This is a PostgreSQL specialty feature.

▶ Example: Index Only Active Users

CREATE INDEX idx_users_active_email ON users (email)
WHERE status = 'active';

Output:

CREATE TABLE

SELECT email FROM users WHERE status = 'active' AND email = 'alice@example.com';

This query hits the partial index. A query without the status = 'active' condition will not hit it.

▶ Example: Index Only Un-shipped Orders

CREATE INDEX idx_orders_pending ON orders (created_at)
WHERE shipped = false;

Output:

CREATE TABLE

(2) Expression Index

When a query condition wraps a column in a function or computation, a normal index can't be used. An expression index builds the index on the computed result.

▶ Example: Case-Insensitive Query

CREATE INDEX idx_users_email_lower ON users (LOWER(email));

SELECT * FROM users WHERE LOWER(email) = 'alice@example.com';

Output:

CREATE TABLE

▶ Example: Date-Truncation Query

CREATE INDEX idx_orders_date_trunc ON orders (DATE_TRUNC('day', created_at));

SELECT COUNT(*) FROM orders
WHERE DATE_TRUNC('day', created_at) = '2025-06-15'::date;

Output:

 count 
-------
     5
(1 row)

(3) Composite Index and the Leftmost Prefix

The query patterns a composite index (a, b, c) can serve:

▶ Example: Create a Composite Index

CREATE INDEX idx_orders_customer_date
ON orders (customer_id, order_date DESC);

Output:

CREATE TABLE

(4) CONCURRENTLY Online Index Build

Building an index takes an exclusive lock by default, blocking writes. CONCURRENTLY does not block writes, but builds more slowly.

▶ Example: Build an Index Online

CREATE INDEX CONCURRENTLY idx_orders_region
ON orders (region);

Output:

CREATE TABLE

8. Concept: EXPLAIN Execution Plan

(1) EXPLAIN Basics

▶ Example: View the Execution Plan

EXPLAIN
SELECT * FROM orders WHERE customer_id = 1;

                              QUERY PLAN
----------------------------------------------------------------------
 Index Scan using idx_orders_customer_id on orders  (cost=0.29..8.31 rows=1 width=72)
   Index Cond: (customer_id = 1)

▶ Example: EXPLAIN ANALYZE

EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 1;

                              QUERY PLAN
----------------------------------------------------------------------
 Index Scan using idx_orders_customer_id on orders
   (cost=0.29..8.31 rows=1 width=72) (actual time=0.015..0.016 rows=2 loops=1)
   Index Cond: (customer_id = 1)
 Planning Time: 0.085 ms
 Execution Time: 0.032 ms

(2) Key Scan Types

▶ Example: Index Only Scan

EXPLAIN
SELECT customer_id FROM orders WHERE customer_id = 1;

 Index Only Scan using idx_orders_customer_id on orders
   Index Cond: (customer_id = 1)

Only the index is read — no table lookup for the data rows — giving the best performance.

9. Index Maintenance and Failure

(1) REINDEX Rebuild

After many inserts/deletes over time, an index may bloat; REINDEX rebuilds it to reclaim space.

▶ Example: Rebuild an Index

REINDEX INDEX idx_orders_customer_id;

REINDEX INDEX CONCURRENTLY idx_orders_customer_id;

Output:

-- SQL statement executed successfully

(2) Common Index-Failure Scenarios

▶ Example: Implicit Type Mismatch Causes Index Failure

SELECT * FROM users WHERE phone = 13800138000;

SELECT * FROM users WHERE phone = '13800138000';

Output:

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

phone is VARCHAR. The implicit conversion in the first line causes the index to be unused; the second line hits the index.

10. Comprehensive Example

Bob's index optimization plan — JSONB product search + active-user partial index + composite index + BRIN time-series index:

CREATE INDEX CONCURRENTLY idx_products_attrs_gin
  ON products USING GIN (attrs);

CREATE INDEX CONCURRENTLY idx_users_active_email
  ON users (email, last_login_at)
  WHERE status = 'active';

CREATE INDEX CONCURRENTLY idx_orders_customer_date
  ON orders (customer_id, order_date DESC);

CREATE INDEX CONCURRENTLY idx_audit_log_created_brin
  ON audit_log USING BRIN (created_at)
  WITH (pages_per_range = 32);

EXPLAIN ANALYZE
SELECT p.product_id, p.name, p.attrs
FROM products p
WHERE p.attrs @> '{"category": "electronics", "in_stock": true}';

EXPLAIN ANALYZE
SELECT user_id, email
FROM users
WHERE status = 'active'
  AND email LIKE 'alice%'
ORDER BY last_login_at DESC
LIMIT 10;

11. Execution Flow

Index type selection and optimization flow:

flowchart TD
    A[Found slow query] --> B[EXPLAIN ANALYZE]
    B --> C{Scan type?}
    C -->|Seq Scan| D{Has suitable index?}
    C -->|Index Scan| E[Index hit<br/>optimize query/index]
    D -->|No| F{Data type?}
    D -->|Yes but miss| G[Check index invalidation]
    F -->|Scalar/sort| H[Create B-Tree]
    F -->|JSONB/array| I[Create GIN]
    F -->|Geometric/range| J[Create GiST]
    F -->|Large table seq| K[Create BRIN]
    F -->|Subset only| L[Create Partial Index]
    F -->|Function/computed| M[Create Expression Index]
    H --> N[CONCURRENTLY deploy]
    I --> N
    J --> N
    K --> N
    L --> N
    M --> N

    style B fill:#e1f5fe
    style G fill:#ffccbc
    style N fill:#c8e6c9

flowchart TD
    A[Found slow query] --> B[EXPLAIN ANALYZE]
    B --> C{Scan type?}
    C -->|Seq Scan| D{Has suitable index?}
    C -->|Index Scan| E[Index hit<br/>optimize query/index]
    D -->|No| F{Data type?}
    D -->|Yes but miss| G[Check index invalidation]
    F -->|Scalar/sort| H[Create B-Tree]
    F -->|JSONB/array| I[Create GIN]
    F -->|Geometric/range| J[Create GiST]
    F -->|Large table seq| K[Create BRIN]
    F -->|Subset only| L[Create Partial Index]
    F -->|Function/computed| M[Create Expression Index]
    H --> N[CONCURRENTLY deploy]
    I --> N
    J --> N
    K --> N
    L --> N
    M --> N

    style B fill:#e1f5fe
    style G fill:#ffccbc
    style N fill:#c8e6c9

❓ FAQ

📖 Summary

• PostgreSQL supports 6 index types; B-Tree fits about 90% of scenarios
• GIN indexes suit containment queries on JSONB / arrays / full-text search
• BRIN indexes are extremely compact and suit range scans on physically ordered large tables
• Partial indexes index only the rows that meet a condition, reducing size and maintenance cost
• Expression indexes index a function/computed result, fixing index failures caused by function-wrapped columns
• Composite indexes follow the leftmost-prefix rule; column order affects whether a query hits the index
• CONCURRENTLY builds indexes online without blocking writes, but cannot run inside a transaction
• EXPLAIN ANALYZE is the core tool for diagnosing slow queries
• Common causes of index failure: function wrapping, type mismatch, stale statistics

📝 Exercises

1. ⭐ Create a B-Tree index on the customer_id column of the orders table, and verify with EXPLAIN that the query hits the index.
2. ⭐ Create a GIN index on the JSONB column of the products table, and observe how the execution plan changes for a @> query.
3. ⭐⭐ Create a partial index: index only the created_at column of orders where status = 'pending', and compare the size difference against a full index.
4. ⭐⭐ Create an expression index on LOWER(email), verify a case-insensitive query hits the index; then create a composite index (region, created_at DESC) to support queries sorted by region and time.
5. ⭐⭐⭐ For a log table with tens of millions of rows, design a combined plan of BRIN index + B-Tree composite index + partial index, and use EXPLAIN ANALYZE to compare query time before and after optimization.

Web-Tutorial Tech Team

A team of developers maintaining programming tutorials. Each tutorial is written and reviewed by developers with expertise in that field. We work to keep our content accurate and reliable — if you spot an issue, please let us know.

About Us