âš¡IntermediateViews & Indexes
Indexes: B-Tree, GIN, GiST, Hash
Speed up queries by creating the right type of index for your data and access patterns.
Overview
An index is a data structure that allows PostgreSQL to find rows matching a condition without scanning the entire table. PostgreSQL automatically creates a B-tree index on PRIMARY KEY and UNIQUE columns. For other columns you need to create indexes manually based on your query patterns.
Choosing the right index type matters: B-tree is the default and suits equality and range queries. GIN is for full-text search and array/JSONB containment. Hash is for pure equality only. GiST is for geometric and range types. An over-indexed table has slower writes; under-indexed tables have slow reads.
Key Points
- •CREATE INDEX ON table (column) creates a B-tree index
- •B-tree: equality (=), range (<, >, BETWEEN), prefix LIKE
- •GIN: full-text, JSONB containment (@>), array containment
- •Hash: fast equality only not suitable for ranges or ordering
- •Partial index: WHERE condition only indexes relevant rows
- •EXPLAIN (ANALYZE) shows whether your index is being used
Examples
Common index types
-- B-tree (default) equality and range queries
CREATE INDEX idx_orders_customer ON orders (customer_id);
CREATE INDEX idx_orders_date ON orders (created_at DESC);
-- Partial index only index pending orders
CREATE INDEX idx_pending_orders ON orders (created_at)
WHERE status = 'pending';
-- GIN for JSONB
CREATE INDEX idx_profile ON users USING GIN (profile);
-- Unique index (also enforces uniqueness constraint)
CREATE UNIQUE INDEX idx_email ON users (LOWER(email));Exercises
Write SQL to solve each exercise. Your progress is saved automatically.
Loading editor…
Completed: 0 / 5 exercises
Knowledge Check
Question 1 of 4