Samengestelde en gedeeltelijke indexen

In eerdere lessen hebben we het gebruik van indexen voor lineaire DBMS-query's besproken (WHERE country = 'Italy'). Hoe zit het als we op twee gecombineerde fronten zoeken?

In een echte e-commerce willen we bijvoorbeeld bestellingen vinden die bij een specifieke klant-ID horen EN die binnen een bepaald tijdsbereik vallen: BESCHERMD_INLINE_1.

Als je alleen een basisindex hebt op customer_id, zal Postgres de 50 bestellingen van de klant in een mum van tijd vinden met behulp van de binaire boom, maar moet het vervolgens alle 50 handmatig en opeenvolgend doorlopen, waarbij de datums lui op het oog worden gefilterd.

Samengestelde indexen

We kunnen het DBMS vertellen om de "analytische woordenboek"-bestanden niet volgens een enkele sleuteldimensie te ordenen, maar volgens meerdere.

CREATE INDEX idx_orders_customer_date ON orders(customer_id, placed_at);

Een "samengestelde" index beschouwt de volgorde van zijn parameters als zeer belangrijk: hier groeperen en categoriseren B-Tree-knooppunten customer_id strak in een blok (de meest onderscheidende voor scheiding); daarbinnen bevindt zich de subboom die zich vertakt met de verschillende placed_at tijdwaarden in perfect gesorteerde volgorde.

Deze structuur vernietigt op brute wijze WHERE clausules met meerdere geneste predikaten.

Gedeeltelijke indexen (gefilterde indexen)

Elke index kost schijfruimte en CPU op gebruikersinvoerstromen. Wat als de e-commerce het voortdurend sorteren en ophalen van een filter vereist, maar slechts voor een minderheid van de records? (Bijvoorbeeld: het snel controleren van de ongelezen meldingen.) Het creëren van een "is_read"-index over alle 10.000.000 meldingen is ongelooflijk vervelend als "Ongelezen" er maar vijf vertegenwoordigt.

CREATE INDEX idx_unread_notifs ON notifications(user_id)
  WHERE is_read = false; -- Questa aggiuntiva è l'"Indice Parziale"

De database zal een razendsnelle B-Tree bouwen die ALLEEN snelle toegangssleutels tot de DB opslaat als het record ongelezen is! De schijf zal verliefd worden op een index van 5 rijen (3 bytes) in plaats van één met 10 miljoen. Snelle SELECT's profiteren volledig:

SELECT * FROM notifications WHERE user_id = 2 AND is_read = false;

Jouw beurt

Voorwaardelijke (gedeeltelijke) indexen verkennen