Common PostgreSQL indexing patterns and best practices, including B-tree, composite, partial, unique, expression, covering, GIN, BRIN, and index usage tips with practical examples.

sql-common-indexing-examples.sql

-- PostgreSQL Common Indexing Examples
--
-- Indexes speed up data retrieval by allowing PostgreSQL to locate rows
-- without scanning an entire table.
--
-- Every index has a cost:
-- - Additional storage
-- - Slower INSERTs
-- - Slower UPDATEs
-- - Slower DELETEs
--
-- Create indexes that support your most common queries, not every column.
--
-- Documentation:
-- https://www.postgresql.org/docs/current/indexes.html


/* --------------------------------------------------------------------------
   Example table
--------------------------------------------------------------------------- */

CREATE TABLE orders (
    id            BIGSERIAL PRIMARY KEY,
    customer_id   BIGINT NOT NULL,
    status        TEXT NOT NULL,
    total         NUMERIC(12,2),
    country       TEXT,
    email         TEXT,
    created_at    TIMESTAMPTZ NOT NULL,
    updated_at    TIMESTAMPTZ,
    metadata      JSONB
);


/* --------------------------------------------------------------------------
   1. Default primary key index
--------------------------------------------------------------------------- */

-- PostgreSQL automatically creates an index for PRIMARY KEY.

CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    email TEXT
);


/* --------------------------------------------------------------------------
   2. Basic B-tree index
--------------------------------------------------------------------------- */

CREATE INDEX idx_orders_customer
ON orders (customer_id);

-- Good for:
-- WHERE customer_id = ?
-- ORDER BY customer_id


/* --------------------------------------------------------------------------
   3. Composite index
--------------------------------------------------------------------------- */

CREATE INDEX idx_orders_customer_created
ON orders (
    customer_id,
    created_at
);

-- Good for:
--
-- WHERE customer_id = ?
-- ORDER BY created_at
--
-- WHERE customer_id = ?
-- AND created_at > ...


/* --------------------------------------------------------------------------
   4. Column order matters
--------------------------------------------------------------------------- */

-- This index:

(customer_id, created_at)

-- Works well for:

WHERE customer_id = ?

WHERE customer_id = ?
AND created_at > ?

-- Less useful for:

WHERE created_at > ?


/* --------------------------------------------------------------------------
   5. Unique index
--------------------------------------------------------------------------- */

CREATE UNIQUE INDEX idx_users_email
ON users (email);

-- Prevents duplicate email addresses.


/* --------------------------------------------------------------------------
   6. Partial index
--------------------------------------------------------------------------- */

CREATE INDEX idx_orders_pending
ON orders (created_at)
WHERE status = 'pending';

-- Much smaller than indexing every row.
-- Useful when most queries target one subset.


/* --------------------------------------------------------------------------
   7. Expression index
--------------------------------------------------------------------------- */

CREATE INDEX idx_users_lower_email
ON users (
    lower(email)
);

-- Enables:

SELECT *
FROM users
WHERE lower(email) = lower(?);


/* --------------------------------------------------------------------------
   8. Covering index (INCLUDE)
--------------------------------------------------------------------------- */

CREATE INDEX idx_orders_lookup
ON orders (
    customer_id
)
INCLUDE (
    status,
    total
);

-- Allows index-only scans for many queries.


/* --------------------------------------------------------------------------
   9. Descending index
--------------------------------------------------------------------------- */

CREATE INDEX idx_orders_recent
ON orders (
    created_at DESC
);

-- Useful for:

SELECT *
FROM orders
ORDER BY created_at DESC
LIMIT 20;


/* --------------------------------------------------------------------------
   10. Multi-column sorting
--------------------------------------------------------------------------- */

CREATE INDEX idx_sales_sort
ON orders (
    country,
    created_at DESC
);

-- Optimizes:

ORDER BY country,
         created_at DESC;


/* --------------------------------------------------------------------------
   11. JSONB GIN index
--------------------------------------------------------------------------- */

CREATE INDEX idx_orders_metadata
ON orders
USING GIN (metadata);

-- Good for:

metadata @> '{"priority":"high"}'


/* --------------------------------------------------------------------------
   12. Array GIN index
--------------------------------------------------------------------------- */

CREATE TABLE products (
    id BIGSERIAL PRIMARY KEY,
    tags TEXT[]
);

CREATE INDEX idx_products_tags
ON products
USING GIN (tags);

-- Supports:

WHERE tags @> ARRAY['sale']


/* --------------------------------------------------------------------------
   13. BRIN index
--------------------------------------------------------------------------- */

CREATE INDEX idx_orders_created_brin
ON orders
USING BRIN (created_at);

-- Excellent for very large append-only tables.


/* --------------------------------------------------------------------------
   14. Remove an unused index
--------------------------------------------------------------------------- */

DROP INDEX idx_orders_recent;


/* --------------------------------------------------------------------------
   15. Check execution plan
--------------------------------------------------------------------------- */

EXPLAIN

SELECT *
FROM orders
WHERE customer_id = 42;


EXPLAIN ANALYZE

SELECT *
FROM orders
WHERE customer_id = 42;

-- ANALYZE actually executes the query.


/* --------------------------------------------------------------------------
   16. Avoid indexing everything
--------------------------------------------------------------------------- */

-- Low-cardinality columns often perform poorly.

status

country

is_active

-- Unless they are used with:
-- partial indexes
-- composite indexes


/* --------------------------------------------------------------------------
   17. Prefer composite indexes over many single-column indexes
--------------------------------------------------------------------------- */

-- Better:

(customer_id, created_at)

-- Than:

(customer_id)

(created_at)

-- when your workload almost always filters on both.


/* --------------------------------------------------------------------------
   18. Don't wrap indexed columns in functions
--------------------------------------------------------------------------- */

-- Less efficient:

WHERE lower(email) = ...

-- Better:

Create an expression index:

CREATE INDEX idx_users_lower_email
ON users (lower(email));


/* --------------------------------------------------------------------------
   19. Index foreign keys
--------------------------------------------------------------------------- */

CREATE INDEX idx_orders_customer_fk
ON orders (customer_id);

-- Foreign keys are NOT automatically indexed.
-- Adding indexes often improves JOIN performance.


/* --------------------------------------------------------------------------
   20. Useful notes
--------------------------------------------------------------------------- */

-- B-tree
--   Default choice for equality, range, and sorting.

-- Hash
--   Equality only. Rarely needed because B-tree usually performs well.

-- GIN
--   JSONB, arrays, full-text search.

-- GiST
--   Geometry, ranges, PostGIS, nearest-neighbor queries.

-- BRIN
--   Huge tables with naturally ordered values.

-- Composite indexes
--   Put the most selective and commonly filtered columns first.

-- Partial indexes
--   Reduce index size and maintenance cost.

-- INCLUDE
--   Enables more index-only scans without affecting index ordering.

-- Use EXPLAIN ANALYZE to confirm an index actually improves the query.

-- Remove unused indexes. Every index slows writes and consumes storage.