Indexes are usually the highest-leverage tuning tool in PostgreSQL. The default access method is B-tree, created simply with CREATE INDEX ON t (col), and it serves equality and range queries well. For specialized data, other access methods shine: GIN supports full-text search on to_tsvector columns, JSONB containment (@>), and array membership; GiST handles geometric data, range types, and array containment as well. JSONB key lookups are best served by CREATE INDEX ON t USING GIN (col jsonb_path_ops), which is smaller and faster than the default jsonb_ops opclass for path-containment queries.
A few important patterns refine how indexes behave. A partial index adds a WHERE clause to make it smaller and faster, like CREATE INDEX active_users_email ON users(email) WHERE deleted_at IS NULL; it will only be picked up by queries whose WHERE matches exactly. A unique index that should ignore NULLs is implemented as a partial unique index: CREATE UNIQUE INDEX ON t (col) WHERE col IS NOT NULL, since PostgreSQL otherwise treats NULLs as distinct. A covering index uses CREATE INDEX ... INCLUDE (col) to attach extra columns to the leaf level, enabling index-only scans. Such scans require the visibility map to indicate that all tuples on the page are visible to all transactions, which in turn requires reasonably fresh VACUUM work. Finally, when designing a multi-column index, place the most selective and most equality-checked column first and respect the "equality before range" rule—if your WHERE does not include the leading column, a B-tree index cannot pick the query up except through loose-index-scan tricks.
Finding which indexes are missing or unused closes the loop. In pg_stat_user_tables, tables with many seq_scan events and few idx_scan events suggest opportunities, especially when combined with high-mean-time queries found in pg_stat_statements. Unused indexes themselves are easy to list by selecting relname and indexrelname from pg_stat_user_indexes WHERE idx_scan = 0, although unique and primary-key indexes that merely enforce constraints should be retained even if unused. Foreign-key columns deserve their own index to keep cascades from doing full child-table scans, and a slow-write workload with many zero-scan indexes is a classic symptom of over-indexing. A common JSONB pitfall to remember is that a GIN index answers containment but not arbitrary equality on a nested path; for that, an expression index on the specific key is the right tool.