pghealth Imagepghealth

Indexing Tips & Tricks for PostgreSQL

Practical insights on PostgreSQL indexing — from B-Tree basics to advanced index types, selectivity, and performance tuning.

·4 minutes reading
Cover Image for Indexing Tips & Tricks for PostgreSQL

Indexes can be your best friend — or your worst enemy — depending on how you use them.

In this post, I’m sharing some of my favorite PostgreSQL indexing tips & tricks. These come from years of consulting work and a recent talk I gave (yes, even NASA heard this one 🚀). Whether you’re building high-performance APIs or managing huge datasets, these tips will help you index smarter.

1. What is an Index?

An index is a data structure that speeds up data retrieval at the cost of additional storage and write overhead.
But — and here’s the important part — indexes don’t always improve performance.

Before creating one, ask yourself:

  • Should I index every table? (No)
  • Should I index every column? (Definitely no)
  • Can indexes slow down inserts/updates? (Yes, they can)
  • Do I understand how this index will be used by the query planner? (Definitely Yes)

2. PostgreSQL Index Types

PostgreSQL supports several index types, each with its own purpose:

  • B-Tree (default) – Great for equality and range queries (=, <, <=, >, >=, BETWEEN, IN).
  • Hash – Optimized for equality checks; smaller than B-Trees, but with restrictions.
  • GIN – Perfect for JSONB, arrays, hstore, and full-text search (especially with pg_trgm).
  • GiST – Used for geometric data, text search, and nearest neighbor queries.
  • SP-GiST – Specialized for partitioned search spaces.
  • BRIN – Efficient for large, ordered datasets like time-series data.

Pro Tip: Choose the index type that matches your data pattern — one size doesn’t fit all.

3. Cardinality vs. Selectivity

Two key metrics for deciding on an index:

  • Cardinality – Number of distinct values in a column.
SELECT COUNT(DISTINCT state_name) FROM states;
  • Selectivity – distinct_values / total_rows.

-- If distinct_values=50 and total_rows=1000, selectivity = 0.05 (low selectivity)

Higher selectivity = more likely to benefit from an index.

4. Don’t Forget the Basics

Some quick wins:

  • Primary keys and unique constraints are indexed automatically — no need to add your own.
  • Use EXPLAIN / EXPLAIN ANALYZE to confirm that your index is actually being used.
  • Run VACUUM ANALYZE to keep planner statistics up to date.
  • Use CREATE INDEX CONCURRENTLY to avoid locking writes during index creation.

5. Multi-Column & Partial Indexes

When building multi-column indexes:

  • Put the most selective column first.
  • Consider partial indexes to reduce size and improve build time.

Example:

CREATE INDEX idx_delivery_delivered
ON delivery (delivery_status, accept_date)
WHERE delivery_status = 'DELIVERED';

Non Partial Index vs Partial index benefits:

  • Smaller size (e.g., 18MB vs. 2.6GB)
  • Faster creation time (150s vs. 197s)
  • Less maintenance overhead

Low-selectivity columns are poor candidates for standalone indexes — but can be useful in **partial indexes**.

6. Index Scans vs. Index-Only Scans

  • Index Scan – Reads from the index, then fetches the row from the table (extra I/O).
  • Index-Only Scan – Satisfies the query entirely from the index (faster, less I/O).

To take advantage of index-only scans:

  • Include all needed columns in the index.
  • Keep tables vacuumed so visibility maps are accurate.

Example:

-- Index-Only Scan candidate
CREATE INDEX idx_users_username_id
ON users (username, id);

SELECT username, id FROM users WHERE username = 'JERINA';

7. Special Use Cases

BRIN for time-series or IoT data

Efficient for ordered data like timestamps.

GIN + pg_trgm for wildcard text search

CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX trgm_idx ON test_trgm USING gin (t gin_trgm_ops);
SELECT * FROM test_trgm WHERE t LIKE '%foo%bar';

GiST for geospatial queries

SELECT * FROM places
ORDER BY location <-> point '(101,456)'
LIMIT 10;

Key Takeaways

✅ Index only what you need.

✅ Match the index type to your query pattern.

✅ Keep statistics fresh for the planner.

✅ Use EXPLAIN ANALYZE — guesswork is expensive.

✅ Smaller, targeted indexes often outperform massive ones.

Indexes are powerful — but only when used strategically. Over-indexing can be as bad as having no indexes at all.

Indexes are essential objects for maintaining the health of your database. We can say that a good system administrator is also an expert in indexing.

📂 Slides: You can download the Indexing Tips & Tricks presentation that forms the basis of this article here: 📥 NASA – Indexing Tips & Tricks (PowerPoint)

If you’d like more PostgreSQL performance tips, follow along on pghealth.io/blog. Your queries (and your CPU) will thank you. 🐘💨