Types of indexes
PostgreSQL has several index types, each suited to different kinds of queries.
In this page:
Syntax
CREATE INDEX index_name ON table_name USING btree (column);
CREATE INDEX index_name ON table_name USING gin (column);Types of indexes
B-tree is the default for equality and ranges. Hash handles equality only. GIN indexes composite values such as JSONB, arrays and full-text search.
GiST supports geometry and range types, SP-GiST partitioned data, and BRIN stores tiny summaries for huge, naturally ordered tables. Partial and expression indexes narrow what is indexed.
Note:
Use GIN for JSONB and array containment queries.
Example: Types of indexes
shop=# CREATE INDEX idx_orders_created ON orders (created_at); -- B-tree
shop=# CREATE INDEX idx_docs_data ON docs USING GIN (data); -- JSONB
shop=# CREATE INDEX idx_places_geo ON places USING GIST (location); -- geometry
shop=# CREATE INDEX idx_log_ts ON logs USING BRIN (ts); -- huge ordered table
shop=# CREATE INDEX idx_active ON users (email) WHERE active; -- partial
shop=# CREATE INDEX idx_lower_email ON users (lower(email)); -- expression⚠️ Run this in your own terminal or Node.js environment.
Related Topics
Common Mistakes
- Using B-tree where GIN is needed
- Ignoring BRIN for huge time-ordered tables
- Creating redundant indexes
Chapter Summary
- B-tree is the default
- GIN for JSONB, arrays and full-text
- GiST for geometry and ranges
- BRIN for huge ordered tables
🔒
Chapter Quiz — Complete all 7 topics to unlock
0/7 topics done
Complete these topics first: