Partiële indexen en expressie-indexen
Leer indexen te maken op een subset van rijen of op het resultaat van een expressie voor gerichte optimalisatie.
Partiële indexen en expressie-indexen is een gratis Prestaties en queryoptimalisatie in PostgreSQL-les op CoddyKit. Dit is les 2 van 4. Je kunt 3 lessen uit dit leerpad gratis volledig lezen — daarna ontgrendelt CoddyKit PRO alle lessen, plus praktische oefeningen met een ingebouwde code-editor en een AI-tutor die 24/7 beschikbaar is. Deze les maakt deel uit van het leertraject Prestaties en queryoptimalisatie in PostgreSQL. Je voortgang wordt gesynchroniseerd op het web en in de CoddyKit-app. De cursus Prestaties en queryoptimalisatie in PostgreSQL bevat in totaal 4 lessen.
Inleiding tot gerichte indexen
Welkom! In deze les behandelen we twee krachtige, gespecialiseerde indextypen in PostgreSQL: Partial Indexes en Expression Indexes.
Met deze indexen kun je zeer gericht optimaliseren: je richt je op specifieke subsets van gegevens of op de resultaten van berekeningen, in plaats van op volledige kolommen.
Gericht werken met partiële indexen
Een Partial Index is een index die slechts een deel van de rijen in een tabel bevat. Je definieert deze subset met een WHERE-clausule tijdens het maken van de index.
Zie het als filteren van je index. Alleen rijen die aan de voorwaarde in WHERE voldoen, worden in de indexstructuur opgenomen.
Voordelen van partiële indexen
Waarom zou je een partiële index gebruiken?
- Kleiner formaat: Ze nemen minder schijfruimte en geheugen in beslag dan volledige indexen.
- Snellere updates: Minder gegevens om te onderhouden betekent snellere bewerkingen met
INSERT,UPDATEenDELETEop de geïndexeerde tabel. - Minder opgeblazen indexen: Ze kunnen indexbloat aanzienlijk verminderen in tabellen met vaak bijgewerkte rijen die niet aan de
WHERE-clausule van de index voldoen.
Ze zijn vooral nuttig wanneer een kleine subset van rijen zeer vaak wordt opgevraagd, zoals 'actieve' gebruikers of 'openstaande' bestellingen.
Partiële indexen maken
De syntaxis voor een partiële index is eenvoudig. Je voegt gewoon een WHERE-clausule toe aan je standaardinstructie CREATE INDEX.
De voorwaarde in de WHERE-clausule moet overeenkomen met de voorwaarde die je in je query's gebruikt, zodat de index effectief kan worden gebruikt.
CREATE INDEX index_name
ON table_name (column_name)
WHERE condition;Partiële index in actie
Laten we een partiële index in actie bekijken. We maken een index op order_date, specifiek voor bestellingen waarvan status de waarde 'pending' heeft. Dit komt vaak voor in e-commerce, waar bestellingen met de status 'pending' snel aandacht nodig hebben.
Voer de code uit om te zien hoe dit werkt:
CREATE TABLE orders (
order_id SERIAL PRIMARY KEY,
customer_id INT,
order_date DATE,
status VARCHAR(20)
);
INSERT INTO orders (customer_id, order_date, status) VALUES
(101, '2023-01-15', 'completed'),
(102, '2023-01-16', 'pending'),
(103, '2023-01-17', 'completed'),
(104, '2023-01-18', 'pending'),
(105, '2023-01-19', 'completed'),
(106, '2023-01-20', 'completed'),
(107, '2023-01-21', 'pending');
CREATE INDEX idx_pending_orders_date
ON orders (order_date)
WHERE status = 'pending';
EXPLAIN ANALYZE SELECT order_id, order_date
FROM orders
WHERE status = 'pending' AND order_date > '2023-01-01';Expressies indexeren
Een Expression Index (ook wel een op functies gebaseerde index genoemd) indexeert het resultaat van een functie of expressie, in plaats van alleen de onbewerkte kolomwaarde.
Dit is bijzonder nuttig wanneer je query's vaak functies op kolommen gebruiken, bijvoorbeeld om tekst om te zetten naar kleine letters voor hoofdletterongevoelige zoekopdrachten.
De kracht van expressie-indexen
Expressie-indexen bieden veel flexibiliteit:
- Hoofdletterongevoelig zoeken: Indexeer
LOWER(column)ofUPPER(column)om query's zoalsWHERE LOWER(column) = 'value'te versnellen. - Datum- en tijdsverwerking: Indexeer
DATE_TRUNC('month', timestamp_column)om query's te optimaliseren die op maand worden gegroepeerd of gefilterd. - Complexe berekeningen: Indexeer wiskundige resultaten of aangepaste functies als deze deel uitmaken van veelgebruikte queryvoorwaarden.
Zonder een expressie-index zou PostgreSQL de functie tijdens een scan voor elke rij moeten berekenen, waardoor dit traag wordt.
Expressie-indexen maken
Om een expressie-index te maken, vervang je de kolomnaam in je instructie CREATE INDEX eenvoudig door de gewenste functie of expressie.
De belangrijkste regel is dat de expressie in de WHERE-clausule van je query exact moet overeenkomen met de expressie in de indexdefinitie, zodat de index kan worden gebruikt.
CREATE INDEX index_name
ON table_name (expression);Voorbeeld van een expressie-index
Laten we een expressie-index maken voor snelle, hoofdletterongevoelige zoekopdrachten op e-mailadressen. Dit is een veelvoorkomende toepassing.
Let erop dat de uitvoer van EXPLAIN ANALYZE een 'Index Scan' moet tonen waarbij onze nieuwe index idx_lower_email wordt gebruikt.
CREATE TABLE users (
user_id SERIAL PRIMARY KEY,
username VARCHAR(50),
email VARCHAR(100)
);
INSERT INTO users (username, email) VALUES
('Alice', 'alice@example.com'),
('Bob', 'BOB@example.com'),
('Charlie', 'Charlie@example.com'),
('David', 'david@example.com');
CREATE INDEX idx_lower_email ON users (LOWER(email));
EXPLAIN ANALYZE SELECT user_id, username
FROM users
WHERE LOWER(email) = 'bob@example.com';
-- This query would NOT use the index:
-- EXPLAIN ANALYZE SELECT user_id, username
-- FROM users
-- WHERE email = 'bob@example.com';Pas je kennis toe
Nu je meer weet over Partial en Expression Indexes, kun je je begrip testen.
Samenvatting van de les
Goed gedaan! Je hebt twee krachtige geavanceerde indexeringstechnieken geleerd:
- Partial Indexes: Indexeer alleen een subset van rijen op basis van een
WHERE-clausule. Zo bespaar je ruimte en versnel je schrijfbewerkingen. - Expression Indexes: Indexeer het resultaat van een functie of expressie om query's te optimaliseren die deze functies in hun voorwaarden gebruiken.
Met deze gerichte indexen kun je de prestaties van specifieke, kritieke query's in je PostgreSQL-database aanzienlijk verbeteren.
Leer SQL met een AI-tutor — gratis
Schrijf echte code en voer die uit in je browser, krijg direct hulp van een AI-tutor die 24/7 beschikbaar is en ga verder waar je gebleven bent op het web of in de app.
- Cursussen
- 22
- Lessen
- 88
Veelgestelde vragen
Is de les “Partiële indexen en expressie-indexen” gratis?
Ja — je kunt hier op het web alle 3 lessen van het leerpad Prestaties en queryoptimalisatie in PostgreSQL, waaronder “Partiële indexen en expressie-indexen”, gratis volledig lezen. Daarna ontgrendelt CoddyKit PRO alle lessen, plus interactieve oefeningen met een ingebouwde code-editor en een AI-tutor die 24/7 beschikbaar is. De cursus Prestaties en queryoptimalisatie in PostgreSQL bevat in totaal 4 lessen.
Wat leer ik in “Partiële indexen en expressie-indexen”?
Leer indexen te maken op een subset van rijen of op het resultaat van een expressie voor gerichte optimalisatie. Je oefent met Prestaties en queryoptimalisatie in PostgreSQL door code rechtstreeks in de browser uit te voeren. Een AI-begeleider die 24/7 beschikbaar is beantwoordt je vragen terwijl je de les doorwerkt.
Heb ik ervaring nodig om met Prestaties en queryoptimalisatie in PostgreSQL te beginnen?
Ervaring vooraf is niet nodig. Prestaties en queryoptimalisatie in PostgreSQL op CoddyKit is opgebouwd voor beginners tot gevorderden, zodat je hier of bij het begin kunt starten en in je eigen tempo kunt leren. Dit is les 2 van 4.
Hoe lang duurt de les “Partiële indexen en expressie-indexen”?
De meeste lessen van CoddyKit duren ongeveer 5–10 minuten. Elke les is kort en interactief, zodat je gestaag vooruitgaat en op het web en in de app precies verdergaat waar je was gebleven.
Kan ik code schrijven en uitvoeren in deze les over Prestaties en queryoptimalisatie in PostgreSQL?
Ja. Elke les over Prestaties en queryoptimalisatie in PostgreSQL bevat een ingebouwde code-editor, zodat je rechtstreeks in je browser echte code kunt schrijven en uitvoeren en direct feedback van AI krijgt — lokale installatie is niet nodig.
Alle lessen in deze cursus
- Hash-, GIN- en GiST-indexen
- Partiële indexen en expressie-indexen
- Dekkende indexen en index-only scans
- BRIN-indexen voor grote sequentiële gegevens