CodeParkCREATE INDEX ON orders (created_at) WHERE status = 'pending';
CREATE INDEX ON users (lower(email));
CREATE INDEX ON orders (customer_id) INCLUDE (total);
CREATE INDEX ON products USING gin (attributes); -- jsonb
The first is a partial index. It only contains rows where status = 'pending'. If pending orders are a small share of all orders, the index is tiny and cheap to maintain. The planner uses it only if the query's WHERE implies the index's condition.
The second is an expression index. It indexes the result of lower(email). So case-insensitive lookups written as WHERE lower(email) = $1 can use it.
The third is a covering index. INCLUDE stores total in the leaf entries, but total is not part of the key. So SELECT total FROM orders WHERE customer_id = $1 can run as an index-only scan. This means PostgreSQL reads only the index and never visits the table.
The fourth is a GIN index. GIN is an inverted index: it indexes the elements inside a value, like a book index that maps words to pages. It supports:
- jsonb containment, such as
attributes @> '{"color": "red"}' - key existence checks
- array operators
- full-text search
- A partial index covers only some rows, and the query must match its condition.
- An expression index stores a computed value, like lower(email).
- INCLUDE adds extra columns to the index so queries can run as index-only scans.
- A GIN index is an inverted index for jsonb, arrays and full-text search.