Why Your PostgreSQL Queries Are Slow: An Indexing Deep Dive Beyond B-Tree

# Why Your PostgreSQL Queries Are Slow: An Indexing Deep Dive Beyond B-Tree

You created an index. The query planner said "Index Scan." And it's still slow.

I can't count how many times I've been called to a war room where the conversation goes like this:

> "We added an index, same as we always do — B-tree on the foreign key."

> "And?"

> "Query dropped from 12 seconds to 11.8."

The default index CREATE INDEX ON orders(customer_id) is a B-tree. And B-trees are phenomenal at what they do: equality checks, range queries, sorted access. But they are not a universal solution. PostgreSQL ships with five index types (seven if you count SP-GiST and bloom) for good reason — each solves a different class of problem.

If you've only used B-trees, you're leaving performance on the table. Worse, you're probably building indexes that hurt more than they help.

Let's fix that.

---

What B-Tree Actually Does Well (and What It Doesn't)

A B-tree organizes data in a balanced tree structure where each leaf node contains pointers to table rows sorted by the indexed column(s). This gives you:

• O(log n) lookups for equality: WHERE id = 42
• Efficient range scans: WHERE created_at > '2026-01-01'
• Sorted output for free: ORDER BY created_at
• Unique constraint enforcement

Here's when B-tree fails:

• Low-selectivity columns: Indexing a boolean is_active column where 80% of rows are true. PostgreSQL will correctly choose a sequential scan because reading the index + heap is more expensive than just scanning the table.
• Partial matches on text: WHERE description LIKE '%search_term%' — the leading wildcard kills the B-tree's sorted structure. It can't skip to a starting point.
• JSONB field access: WHERE data->>'email' = 'user@example.com' — even with a B-tree index on the expression, performance degrades as the JSON documents grow.
• Append-only timestamped data: If 99% of your queries target the last 24 hours of a table with 3 years of data, a full B-tree index on created_at is mostly dead weight.

The last one is especially painful. Let's call it the "hot zone" problem.

---

Hot Zone Problem: BRIN to the Rescue

I had a table tracking API request logs — 2.1 billion rows, growing at about 4 million rows per day. The query pattern was always the same:

SELECT method, path, status, duration_ms
FROM api_logs
WHERE occurred_at BETWEEN '2026-07-28 05:00:00' AND '2026-07-28 06:00:00'
  AND status >= 500;

The B-tree index on occurred_at was 48 GB. The table itself was 210 GB. Every insert required updating a random leaf page in that 48 GB B-tree, which meant write amplification was catastrophic. Vacuum couldn't keep up. Autovacuum was running constantly.

Enter BRIN (Block Range INdex).

A BRIN index doesn't index each row — it summarizes ranges of physical blocks. For each contiguous range of pages (default 128), it stores the minimum and maximum value of the indexed column.

CREATE INDEX idx_api_logs_occurred_at_brin
ON api_logs USING brin (occurred_at)
WITH (pages_per_range = 32);

The result?

Wait — the BRIN index was not just smaller, it was faster for the hot query. How? Because the B-tree was too large to fit in shared_buffers. Every query did a random walk through a 48 GB index. The BRIN index, at 8 MB, fit entirely in memory. The planner simply scanned the relevant block ranges.

The tradeoff? BRIN indexes are lossy. They return false positives — pages that might contain matching rows. PostgreSQL then rechecks each candidate row against the filter. For naturally clustered data like time-series logs, this is fine. For random UUIDs scattered across the table, BRIN is useless.

Rule of thumb: Use BRIN when your data is naturally correlated with physical order (time-series, auto-increment IDs, log tables) and your queries target a recent window.

---

Partial Indexes: The Free Performance You're Leaving Behind

Here's a scenario I see constantly. A SaaS application with a users table and a column deleted_at TIMESTAMP NULL. The application never queries deleted users — they're hidden from every view:

SELECT * FROM users WHERE deleted_at IS NULL AND email = 'user@example.com';

A naive index on email indexes every row — including the 340,000 deleted accounts nobody looks at. A partial index only indexes the rows that matter:

CREATE INDEX idx_users_active_email
ON users (email)
WHERE deleted_at IS NULL;

Let's look at the numbers from a real migration:

Seq Scan (no)

| B-tree on email (full) | 1,200,000 | 42 MB | Index Scan, 42 MB read |

| Partial (active only) | 860,000 | 28 MB | Index Scan, 28 MB read |

Smaller index means more of it fits in cache, which means faster lookups. It also means fewer index writes on INSERT/UPDATE — deleted rows don't touch the index.

You can get even more aggressive:

-- Index only users created in the last 90 days for a "recent signups" feature
CREATE INDEX idx_users_recent_signups
ON users (created_at)
WHERE created_at > NOW() - INTERVAL '90 days';

This index is never more than 90 days deep. Old rows automatically fall out. You never need to rebuild or reindex it.

Where partial indexes hurt: If your query doesn't match the WHERE clause exactly, the planner won't use the index. That includes queries with OR conditions that negate the predicate, or queries filtering on a different subset. Test with EXPLAIN ANALYZE before committing.

---

Covering Indexes: The Index-Only Scan Goldmine

The most expensive part of an index lookup isn't the index traversal — it's the heap fetch. After finding the matching row pointer in the index, PostgreSQL must read the actual table row (the "heap") to retrieve any columns not in the index.

If you can satisfy the query entirely from the index, PostgreSQL can do an Index Only Scan — no heap access needed.

-- Without covering: Index Scan + heap fetch
CREATE INDEX idx_orders_customer ON orders (customer_id);

EXPLAIN ANALYZE SELECT customer_id, total, status FROM orders WHERE customer_id = 123;
-- Index Scan using idx_orders_customer on orders  (cost=0.43..28.98 rows=12 width=64)
--   ->  Heap Fetches: 12 rows from table pages

-- With covering: Index Only Scan, zero heap fetches
CREATE INDEX idx_orders_customer_covering
ON orders (customer_id) INCLUDE (total, status);

EXPLAIN ANALYZE SELECT customer_id, total, status FROM orders WHERE customer_id = 123;
-- Index Only Scan using idx_orders_customer_covering on orders  (cost=0.43..4.45 rows=12 width=64)

Notice the cost dropped from 28.98 to 4.45. That's not a small improvement — that's a 6.5x reduction in cost units. On a query that runs thousands of times per second, this is transformative.

The cost of covering indexes: They're larger (you're storing more columns in the index) and inserts/updates are slower (more data to write per index entry). Use them for:

• Hot queries that run against narrow column sets
• Dashboard/reporting queries that always access the same 3-4 columns
• Foreign key lookups where you always read id, name, status together

Don't use covering indexes on heavy write tables or columns that change frequently, unless the read benefit dramatically outweighs the write cost.

---

GIN Indexes: When B-Tree Can't Handle the Structure

JSONB: The "I'll Just Add a JSONB Column" Trap

Everybody starts with JSONB and an expression-based B-tree:

CREATE INDEX idx_users_metadata_email ON users ((metadata->>'email'));

This works fine for simple top-level field lookups. But the moment your JSON documents become nested or array-based, B-tree breaks down. Consider:

SELECT * FROM products
WHERE attributes @> '{"tags": ["wireless", "bluetooth"]}';

That @> operator (contains) can't use a B-tree. It needs a GIN index:

CREATE INDEX idx_products_attributes_gin
ON products USING gin (attributes jsonb_path_ops);

The jsonb_path_ops variant creates a more compact index that's faster for @> queries, at the cost of not supporting some operators like ? (key exists). Choose based on your query patterns.

Here's what GIN enables for JSONB:

Full-Text Search Without Elasticsearch

You don't need Elasticsearch for search on moderately-sized datasets. PostgreSQL's full-text search with GIN indexes handles millions of documents comfortably:

-- Add a tsvector column (or use a generated column in PG 12+)
ALTER TABLE articles ADD COLUMN search_vector tsvector
GENERATED ALWAYS AS (
  setweight(to_tsvector('english', title), 'A') ||
  setweight(to_tsvector('english', body), 'B')
) STORED;

-- GIN index on the tsvector
CREATE INDEX idx_articles_search ON articles USING gin (search_vector);

-- Query
SELECT title, ts_rank(search_vector, query) AS rank
FROM articles, plainto_tsquery('english', 'distributed systems consistency') query
WHERE search_vector @@ query
ORDER BY rank DESC
LIMIT 20;

This query returns ranked results in under 50 ms on a table with 2 million articles. It supports stemming, stop word removal, and weighted ranking out of the box.

GIN index tradeoffs: GIN indexes are slow to build — building one on a large table can take minutes to hours. Writes are also slower because GIN must maintain an inverted list structure. And GIN indexes are large, often 2-3x the size of a B-tree on the same data. Use them for read-heavy, search-based workloads, not high-write OLTP tables.

---

GiST Indexes: When Your Data Has Natural Geometry

GiST (Generalized Search Tree) indexes are the Swiss Army knife of PostgreSQL indexing. They support:

• Geometric/geospatial queries: Points, polygons, bounding boxes
• Range types: Overlapping date ranges, numeric ranges
• Full-text search (alternative to GIN): Better for ranking, worse for AND queries
• Trigram similarity: Fuzzy text matching

The most practical day-to-day use is exclusion constraints on range types:

-- Prevent double-booking
CREATE EXTENSION IF NOT EXISTS btree_gist;

CREATE TABLE bookings (
  room_id INTEGER NOT NULL,
  booked_during TSTZRANGE NOT NULL,
  EXCLUDE USING gist (room_id WITH =, booked_during WITH &&)
);

-- This will error:
-- ERROR: conflicting key value violates exclusion constraint
INSERT INTO bookings VALUES (1, '[2026-07-29 14:00, 2026-07-29 15:00)');
INSERT INTO bookings VALUES (1, '[2026-07-29 14:30, 2026-07-29 15:30)'); -- BOOM

This is the && (overlaps) operator — PostgreSQL uses the GiST index to check if any existing row has both the same room_id and an overlapping time range. No application-level locking, no race conditions, no separate validation step.

For nearest-neighbor queries (top-10 closest restaurants), GiST is the only option:

CREATE INDEX idx_locations ON venues USING gist (location);

SELECT name, location <-> point(18.0686, 59.3293) AS distance
FROM venues
ORDER BY distance
LIMIT 10;

GiST tradeoffs: GiST indexes are slower to build than B-tree, larger, and only support a subset of operators (mostly inequality/range operators, not equality). For simple equality lookups on integers, always use B-tree.

---

Hash Indexes: Actually Good Now (PG 10+)

For a long time, hash indexes were transactional nightmares — not WAL-logged, not crash-safe. Since PostgreSQL 10, they're fully crash-safe and replicated properly.

A hash index is useful for one thing: equality lookups on a single column where the column has high cardinality and you never do range scans or sorting.

CREATE INDEX idx_sessions_token_hash ON sessions USING hash (token);

EXPLAIN ANALYZE SELECT * FROM sessions WHERE token = 'abc-123-def';
-- Hash Index Scan using idx_sessions_token_hash  (cost=0.00..2.01 rows=1 width=128)

For this exact use case — UUID or token lookup — a hash index is about 15-20% smaller than a B-tree and marginally faster for lookups.

When NOT to use hash: Any query with ORDER BY, > / < / BETWEEN, IS NULL, or pattern matching. Hash indexes only support = and IN. That's it.

---

Putting It All Together: A Decision Framework

Here's the flowchart I use with teams:

---

The One Thing That Changes Everything: Monitoring

None of this matters if you don't know which queries are slow. Before you build any index:

-- Find your top queries by total time
SELECT queryid, query, calls, total_exec_time / calls AS avg_ms,
       rows, shared_blks_hit, shared_blks_read
FROM pg_stat_statements
WHERE query NOT LIKE '%pg_%'
ORDER BY total_exec_time DESC
LIMIT 20;

Look for queries with high shared_blks_read (disk reads) relative to shared_blks_hit (cache hits). That ratio tells you which indexes are worth optimizing. A query reading 10,000 blocks from disk and only hitting 1,000 in cache screams for a covering or partial index.

Then enable track_io_timing and check:

SELECT pg_stat_statements_reset();
-- Run your workload for 15 minutes
SELECT query, total_exec_time / calls AS avg_ms,
       (blk_read_time / calls)::numeric(6,2) AS read_wait_ms
FROM pg_stat_statements
WHERE blk_read_time > 0
ORDER BY blk_read_time DESC
LIMIT 10;

If read_wait_ms is more than 30% of avg_ms, your index strategy is broken. The query is spending more time waiting for the disk to feed the index than doing actual work.

---

What to Do Next

1. Audit your current indexes with this query — it finds duplicate, unused, and never-used indexes:

SELECT schemaname, tablename, indexname, idx_scan, idx_tup_read,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC;

Any index with idx_scan = 0 is dead weight. Drop it. Any index where idx_tup_read / GREATEST(idx_scan, 1) > 100 is a candidate for a covering index — you're reading many tuples per scan, which means heap fetches dominate.

1. Start with partial indexes — they're the highest-impact, lowest-risk change. Add a WHERE clause that matches your query filters. Watch your cache hit ratio improve as the index shrinks.

1. Add BRIN to one time-series table — if you have a created_at-ordered table with more than 10 million rows, BRIN is a free lunch. Make sure your queries target recent rows.

1. Profile for a week before touching GIN or GiST — those indexes have real write overhead. Make sure the read pattern justifies the cost.

PostgreSQL gives you more tools than B-tree. It's time to use them.