Database Indexing: B-tree vs GIN vs GiST, EXPLAIN ANALYZE, and Partial Indexes

Bad indexing is the most common cause of PostgreSQL performance problems. A missing index on the right column can turn a 50ms query into a 30-second table scan. An index on the wrong column wastes storage and slows writes with no benefit.

Understanding index types and reading EXPLAIN ANALYZE output will let you diagnose and fix most slow queries in under an hour.

---

PostgreSQL Index Types

---

B-tree: The Default

B-tree handles 90% of use cases. Use it for primary keys, foreign keys, and any column you filter, sort, or join on:

-- Standard B-tree indexes
CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_orders_status ON orders(status);

-- Composite B-tree: column order matters
-- Put equality columns first, range columns last
CREATE INDEX idx_orders_tenant_created
  ON orders(tenant_id, created_at DESC);
-- Supports: WHERE tenant_id = $1 ORDER BY created_at DESC
-- Also supports: WHERE tenant_id = $1 (leftmost prefix rule)
-- Does NOT support: WHERE created_at > $1 (no tenant_id in prefix)

-- Index for covering query (avoids table access entirely)
CREATE INDEX idx_users_email_covering
  ON users(email) INCLUDE (id, name);
-- Query: SELECT id, name FROM users WHERE email = $1
-- → Index-only scan (never touches heap)

---

GIN Indexes: Arrays and JSONB

GIN (Generalized Inverted Index) indexes every element in an array or every key in JSONB, making containment queries fast:

-- GIN index on JSONB column
CREATE INDEX idx_products_metadata ON products USING GIN(metadata);

-- Supports containment operators
SELECT * FROM products
WHERE metadata @> '{"category": "electronics", "brand": "apple"}';
-- Without GIN: sequential scan. With GIN: fast index lookup.

-- GIN index on text[] array column
CREATE INDEX idx_articles_tags ON articles USING GIN(tags);

-- Find articles with specific tag
SELECT * FROM articles WHERE tags @> ARRAY['typescript'];
SELECT * FROM articles WHERE tags && ARRAY['typescript', 'react'];  -- overlap

-- GIN for full-text search
CREATE INDEX idx_articles_fts ON articles USING GIN(to_tsvector('english', title || ' ' || body));

SELECT * FROM articles
WHERE to_tsvector('english', title || ' ' || body) @@ plainto_tsquery('english', 'graphql federation');

---

Partial Indexes: Index Only What You Query

Partial indexes index only rows matching a WHERE condition. They're smaller, faster to maintain, and sometimes dramatically more effective:

-- 90% of orders are COMPLETED. You only query PENDING and PROCESSING.
-- Full index on status: 10M rows indexed, most never queried
-- Partial index: only the ~50K active rows indexed

CREATE INDEX idx_orders_active ON orders(created_at)
  WHERE status IN ('PENDING', 'PROCESSING');

-- Queries for active orders now use this tiny, fast index
SELECT * FROM orders
WHERE status = 'PENDING' AND created_at < NOW() - INTERVAL '1 hour';

-- Unique constraint only for active records (allows multiple soft-deleted)
CREATE UNIQUE INDEX idx_users_email_active ON users(email)
  WHERE deleted_at IS NULL;

-- Most queries on payments target the last 90 days
-- Partial index: only recent payments, much smaller
CREATE INDEX idx_payments_recent ON payments(created_at, status)
  WHERE created_at > NOW() - INTERVAL '90 days';
-- Note: this index must be rebuilt periodically (or use range partitioning instead)

---

EXPLAIN ANALYZE: Reading the Output

EXPLAIN ANALYZE runs the query and shows how PostgreSQL actually executed it:

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT o.id, o.total_cents, u.name
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.tenant_id = '550e8400-...'
  AND o.status = 'PENDING'
ORDER BY o.created_at DESC
LIMIT 20;

Sample output and how to read it:

Limit  (cost=0.56..458.91 rows=20 width=48) (actual time=0.187..1.234 rows=20 loops=1)
  ->  Nested Loop  (cost=0.56..45891.23 rows=2000 width=48) (actual time=0.185..1.229 rows=20 loops=1)
        ->  Index Scan using idx_orders_tenant_status_created on orders o
              (cost=0.43..1234.56 rows=2000 width=32)
              (actual time=0.112..0.892 rows=20 loops=1)
              Index Cond: ((tenant_id = '550e8400...') AND (status = 'PENDING'))
              Filter: (created_at > '2026-01-01')
              Rows Removed by Filter: 3
              Buffers: shared hit=24 read=4
        ->  Index Scan using users_pkey on users u
              (cost=0.13..0.15 rows=1 width=16)
              (actual time=0.016..0.016 rows=1 loops=20)
              Index Cond: (id = o.user_id)
              Buffers: shared hit=60

Planning Time: 0.432 ms
Execution Time: 1.312 ms

Key things to look for:

cost=0.43..1234.56   → Estimated cost (startup..total). Large total = slow estimate
actual time=0.112..0.892  → Actual milliseconds. Compare to estimate.
rows=2000 → estimated   vs   rows=20 → actual   ← BAD: estimate is 100× off
  → Run ANALYZE on the table to update statistics
  
Buffers: shared hit=24 read=4
  → hit: from memory cache (fast)
  → read: from disk (slow). High "read" means data isn't in shared_buffers.

Seq Scan on orders → NO INDEX USED. This is usually the problem to fix.

Filter: ... Rows Removed by Filter: 3000 
  → Index was used but then filtered many rows. Consider a partial index.

Detecting slow queries automatically:

-- Enable in postgresql.conf (or RDS parameter group)
-- log_min_duration_statement = 1000  -- log queries > 1 second

-- Or query pg_stat_statements for top slow queries
SELECT
  query,
  calls,
  round(total_exec_time / calls) AS avg_ms,
  round(total_exec_time) AS total_ms,
  rows / calls AS avg_rows
FROM pg_stat_statements
ORDER BY avg_ms DESC
LIMIT 20;

---

Index Maintenance

Indexes need maintenance. PostgreSQL handles most of this automatically with autovacuum, but you should know when to intervene:

-- Check index bloat (indexes with dead tuples eating space)
SELECT
  schemaname,
  tablename,
  indexname,
  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
  idx_scan,
  idx_tup_read,
  idx_tup_fetch
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC;

-- Find unused indexes (never scanned since last stats reset)
SELECT
  schemaname || '.' || tablename AS table,
  indexname,
  pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND schemaname NOT IN ('pg_catalog')
ORDER BY pg_relation_size(indexrelid) DESC;

-- Rebuild a bloated index without locking
REINDEX INDEX CONCURRENTLY idx_orders_user_id;

---

Index Strategy by Query Pattern

-- Pattern: tenant-scoped time-range queries (most SaaS workloads)
-- Query: WHERE tenant_id = $1 AND created_at BETWEEN $2 AND $3
CREATE INDEX idx_orders_tenant_created ON orders(tenant_id, created_at);

-- Pattern: status filtering on a large table
-- Query: WHERE status = 'active' (small subset of rows)
CREATE INDEX idx_subscriptions_active ON subscriptions(tenant_id, expires_at)
  WHERE status = 'active';

-- Pattern: JSONB tag/property search
-- Query: WHERE metadata @> '{"plan": "enterprise"}'
CREATE INDEX idx_tenants_metadata ON tenants USING GIN(metadata);

-- Pattern: Full-text search
-- Query: WHERE to_tsvector(...) @@ plainto_tsquery(...)
CREATE INDEX idx_docs_fts ON documents
  USING GIN(to_tsvector('english', coalesce(title,'') || ' ' || coalesce(body,'')));

-- Pattern: Covering index for hot read path
-- Query: SELECT id, email, name FROM users WHERE id = $1
CREATE INDEX idx_users_id_covering ON users(id) INCLUDE (email, name);
-- Enables index-only scan (no heap access at all)

---

Cost of Over-Indexing

Each index adds write overhead. On a table with 8 indexes:

-- Every INSERT must update 8 index structures (not just the table)
-- Every UPDATE to indexed columns must update those index structures
-- VACUUM must maintain 8 index structures

-- Find tables with many indexes
SELECT
  tablename,
  count(*) AS index_count,
  string_agg(indexname, ', ') AS indexes
FROM pg_indexes
WHERE schemaname = 'public'
GROUP BY tablename
HAVING count(*) > 5
ORDER BY index_count DESC;

If a table has > 8 indexes, review usage. Drop indexes with idx_scan = 0 in pg_stat_user_indexes (after ensuring stats period is representative).

---

Working With Viprasol

We conduct database performance reviews — identifying missing indexes, explaining slow queries, designing optimal index strategies for multi-tenant SaaS workloads, and setting up pg_stat_statements monitoring.

→ Talk to our team about database optimization and performance.

---

See Also

• Database Migrations — adding indexes safely with CONCURRENTLY
• Multi-Tenancy Patterns — RLS and tenant-scoped query patterns
• Event Sourcing — append-only patterns that interact differently with indexes
• Database Sharding — when indexing isn't enough and you need to scale out
• Web Development Services — database architecture and optimization