Samengestelde en gedeeltelijke indexen

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

Exploring conditional (Partial) indexes