Module lessons (3/4)
Composite and partial indexes
In previous lessons we covered using indexes for linear DBMS queries (WHERE country = 'Italy'). What about when we search on two combined fronts?
For example, in a real e-commerce we might want to find orders that belong to a specific customer ID AND fall within a given time range:
WHERE customer_id = 90 AND placed_at >= '2025-01-01'.
If you only have a base index on customer_id, Postgres will find the customer's 50 orders in no time using the binary tree, but it then has to iterate through all 50 of them manually and sequentially, lazily filtering the dates by eye.
Composite Indexes
We can tell the DBMS to order the "analytical dictionary" files not along a single key dimension, but along several.
CREATE INDEX idx_orders_customer_date ON orders(customer_id, placed_at);A "composite" index treats the order of its parameters as very important: here B-Tree nodes group and rigidly categorize customer_id in a block (the most discriminating one for separation); inside them lives the sub-tree branching out with the various placed_at time values in perfectly sorted order.
This structure brutally annihilates WHERE clauses with multiple nested predicates.
Partial Indexes (Filtered Indexes)
Every index costs disk space and CPU on user-input streams. What if the e-commerce required constantly sorting and retrieving a filter, but only for a minority of records? (For example: rapidly polling the unread-notifications check.) Creating an "is_read" index over all 10,000,000 notifications is incredibly tedious if "Unread" only represents 5 of them.
CREATE INDEX idx_unread_notifs ON notifications(user_id)
WHERE is_read = false; -- Questa aggiuntiva è l'"Indice Parziale"The database will build a lightning-fast B-Tree that stores quick access keys to the DB ONLY if the record is Unread! The disk will fall in love with an index of 5 rows (3 bytes) instead of one with 10 million. Fast SELECTs benefit fully:
SELECT * FROM notifications WHERE user_id = 2 AND is_read = false;
Your turn
We want to optimize the lookup of temporary passwords / reset tokens. We very frequently make SELECT calls looking for all customers signed up from a specific country combined with a known city.\nCreate a composite index named 'idx_cust_loc' on 'customers'. Pass the 'country' field first (which is more exclusive) and then the 'city' field.
Show hint
Syntax: CREATE INDEX name ON table(col1, col2);
Solution available after 3 attempts
Exploring conditional (Partial) indexes
Optimize a shopping user lookup: users pre-register but we only need to list them in their chronological signup order (create an index on 'signed_up_on' in 'customers') but LIMITED ONLY to customers from the exact full-string 'city': 'Milano'.
Show hint
It is declared normally on the target property; at the end you append the magic keyword WHERE city = 'Milano'.
Solution available after 3 attempts