Naast de standaard B-tree biedt PostgreSQL verschillende gespecialiseerde indextypen — GIN, GiST, BRIN, Hash — elk geschikt voor verschillende data en querypatronen, plus partial en expression indexes voor flexibiliteit. Het kiezen van het juiste indextype kan de prestaties voor gespecialiseerde queries dramatisch verbeteren.
CREATE INDEX idx_users_email ON users(email); -- default = B-tree
-- great for: equality (=), ranges (<, >, BETWEEN), sorting (ORDER BY), most queries
B-tree is de standaard en behandelt de meerderheid van de gevallen — gelijkheid, bereiken en sortering op gewone kolommen.
-- GIN (Generalized Inverted Index) — for values CONTAINING multiple items
CREATE INDEX idx_attrs ON products USING GIN (attributes); -- JSONB
CREATE INDEX idx_tags ON posts USING GIN (tags); -- arrays
CREATE INDEX idx_search ON docs USING GIN (to_tsvector('english', body)); -- full-text
-- great for: JSONB containment, array membership, full-text search
GIN indexes zijn essentieel voor JSONB, arrays en full-text search — queries die containment of membership controleren.
-- GiST — geometric, range, nearest-neighbor (e.g. PostGIS, range types)
CREATE INDEX idx_geo ON places USING GiST (location);
-- BRIN (Block Range INdex) — tiny index for LARGE tables with naturally-ordered data
CREATE INDEX idx_time ON logs USING BRIN (created_at); -- great for huge time-series
-- tiny size, efficient for large append-only/ordered data
-- Hash — equality only (=); rarely needed (B-tree handles equality too)
CREATE INDEX idx_hash ON t USING HASH (col);
-- PARTIAL index — index only SOME rows (smaller, targeted)
CREATE INDEX idx_active ON users(email) WHERE active = true; -- only active users
-- EXPRESSION index — index a computed value
CREATE INDEX idx_lower_email ON users(LOWER(email)); -- for case-insensitive lookups
SELECT * FROM users WHERE LOWER(email) = '[email protected]'; -- uses the index
Partial indexes (index alleen relevante rijen — kleiner, sneller) en expression indexes (index een berekende waarde — enabling indexed function-based queries) zijn krachtige, flexibele gereedschappen.
Begrip van PostgreSQL's verscheidenheid aan indextypen is waardevolle kennis voor performance optimization, omdat het juiste indextype voor een gegeven data en querypatroon queries die de standaard B-tree slecht of helemaal niet behandelt dramatisch kan versnellen.
While B-tree (the default) covers most cases (equality, ranges, sorting), specialized types unlock performance for specialized data: GIN is essential for JSONB, arrays, and full-text search (containment/membership queries — making Postgres's powerful JSONB and array features performant); GiST handles geometric, range, and nearest-neighbor queries (PostGIS geospatial, range types); and BRIN provides tiny, efficient indexes for large naturally-ordered tables (like time-series/append-only data — a valuable option for huge tables where a full B-tree would be costly).
Het kennen van welk indextype in welk scenario past (GIN voor JSONB/arrays/full-text, GiST voor geometric/range, BRIN voor grote ordered data) is essentieel voor het optimaliseren van PostgreSQL's geavanceerde functies.
Evenals waardevol zijn partial indexes (indexering van alleen relevante rijen — kleiner en efficiënter wanneer u een subset queried, zoals actieve gebruikers) en expression indexes (indexering van berekende waarden — enabling fast function-based lookups zoals case-insensitive email search, wat een gewone index niet kan ondersteunen).
Daar indexkeuze query-prestaties aanzienlijk beïnvloedt, en aangezien PostgreSQL's gespecialiseerde indextypen (vooral GIN voor JSONB/arrays/full-text) zijn wat zijn geavanceerde gegevenstypen performant maakt, het begrijpen van de indextypen en wanneer elk te gebruiken — inclusief partial en expression indexes — is waardevolle, frequently-relevant kennis voor het optimaliseren van PostgreSQL-databases, wat ontwikkelaars onderscheidt die PostgreSQL snel kunnen maken voor diverse querypatronen en een belangrijk aspect van het benutten van de volledige prestatiemogelijkheden van de database.