Use B-Tree for most general-purpose indexing needs like equality and range queries, GIN for full-text search and complex array/JSONB operations, GiST for specialized spatial/geometric or range data, and BRIN for very large tables with naturally ordered data.
Here's a breakdown of when to use each index type:
B-Tree (Balanced Tree):
When to use: This is PostgreSQL's default and most versatile index type. It's ideal for equality (=), range (<, >, <=, >=), LIKE (for non-leading wildcard patterns), and ORDER BY clauses. B-Tree indexes are efficient for single-column and multi-column indexes across various data types.
Considerations: High write overhead can occur for columns frequently updated, inserted, or deleted.
Example:
```sql
CREATE INDEX idx_users_email ON users (email);
SELECT FROM users WHERE email = 'test@example.com';
```
GIN (Generalized Inverted Index):
When to use: Best for columns containing multiple values per entry, such as arrays (text[]), jsonb documents, or tsvector for full-text search. GIN indexes excel at finding rows where a specific element or key exists within the indexed column (containment queries).
Considerations: GIN indexes are typically slower to build and update than B-Tree indexes, but offer significantly faster lookups on complex data types.
Example:
```sql
CREATE INDEX idx_docs_content ON documents USING GIN (to_tsvector('english', content));
SELECT id FROM documents WHERE to_tsvector('english', content) @@ plainto_tsquery('english', 'search term');
CREATE INDEX idx_products_tags ON products USING GIN (tags); -- tags is text[]
SELECT FROM products WHERE tags @> ARRAY['electronics'];
```
GiST (Generalized Search Tree):
When to use: Suitable for complex data types that define custom indexing strategies, particularly for spatial data (e.g., PostGIS geometry types), range types (int4range, daterange), and k-nearest neighbor (k-NN) searches. GiST supports operators like overlap (&&), containment (@>, <@), and exclusion constraints.
Considerations: While it can handle some array/JSONB containment, GIN is generally more efficient for those specific use cases. GiST shines with geometric and spatial operations.
Example:
```sql
CREATE EXTENSION postgis;
CREATE TABLE places (id serial, geom geometry(Point, 4326));
CREATE INDEX idx_places_geom ON places USING GiST (geom);
SELECT FROM places WHERE geom && ST_MakeEnvelope(-74, 40, -73, 41, 4326);
```
BRIN (Block Range Index):
When to use: For very large tables (potentially terabytes) where data is naturally ordered on disk. This often applies to columns like timestamp in an append-only log table or a primary key in a clustered index scenario. BRIN indexes store minimum and maximum values for physical block ranges, making them extremely compact and fast for identifying relevant blocks.
Considerations: BRIN is ineffective if data is not well-ordered. It relies on VACUUM operations to update its statistics and maintain efficiency.
Example:
```sql
CREATE INDEX idx_logs_timestamp ON logs USING BRIN (log_timestamp);
SELECT FROM logs WHERE log_timestamp BETWEEN '2025-01-01' AND '2025-01-02';
```
Always use EXPLAIN ANALYZE to confirm if your chosen index is being used effectively and to measure its actual performance impact on your queries.