El Projecte | Sobre nosaltres | Contribuir | Donacions | Llicència

HOME

Els índexs són una part fonamental de l'optimització de bases de dades. Ajuden a accelerar les consultes permetent un accés més ràpid a les dades. En aquest tema, aprendrem què són els índexs, com es creen, els diferents tipus d'índexs disponibles a PostgreSQL i les millors pràctiques per utilitzar-los.

Què és un índex?

Un índex és una estructura de dades que millora la velocitat de les operacions de recuperació de dades en una taula de base de dades. Els índexs es creen sobre una o més columnes d'una taula i permeten a PostgreSQL trobar les files més ràpidament.

Beneficis dels índexs:

Millora del rendiment de les consultes: Les consultes que utilitzen columnes indexades són molt més ràpides.
Reducció del temps de cerca: Els índexs redueixen el nombre de files que s'han de cercar per trobar les dades desitjades.

Desavantatges dels índexs:

Espai d'emmagatzematge addicional: Els índexs ocupen espai addicional a la base de dades.
Temps addicional d'inserció/actualització: Les operacions d'inserció, actualització i eliminació poden ser més lentes perquè els índexs s'han d'actualitzar.

Creant un índex

Per crear un índex en PostgreSQL, utilitzem la instrucció CREATE INDEX. A continuació es mostra la sintaxi bàsica:

CREATE INDEX nom_index
ON nom_taula (columna1, columna2, ...);

Exemple pràctic

Suposem que tenim una taula empleats amb les següents columnes: id, nom, cognom, departament, sou. Volem crear un índex sobre la columna departament per accelerar les consultes que filtren per aquesta columna.

CREATE INDEX idx_departament
ON empleats (departament);

Tipus d'índexs

PostgreSQL ofereix diversos tipus d'índexs, cadascun amb els seus propis avantatges i usos específics.

Índex B-tree

Aquest és el tipus d'índex per defecte i és adequat per a la majoria de les situacions. És eficient per a operacions d'igualtat i rang.

CREATE INDEX idx_btree_departament
ON empleats (departament);

Índex Hash

Els índexs hash són útils per a operacions d'igualtat, però no per a operacions de rang.

CREATE INDEX idx_hash_departament
ON empleats USING hash (departament);

Índex GIN (Generalized Inverted Index)

Els índexs GIN són útils per a columnes que contenen dades complexes com arrays, JSONB, i text complet.

CREATE INDEX idx_gin_tags
ON articles USING gin (tags);

Índex GiST (Generalized Search Tree)

Els índexs GiST són útils per a dades geoespacials, text complet i altres tipus de dades personalitzades.

CREATE INDEX idx_gist_location
ON llocs USING gist (ubicacio);

Millors pràctiques per utilitzar índexs

Indexar columnes utilitzades en condicions de cerca: Les columnes que apareixen freqüentment en les clàusules WHERE, JOIN i ORDER BY són bones candidates per a índexs.
Evitar indexar columnes amb molts valors únics: Les columnes amb molts valors únics poden no beneficiar-se tant dels índexs.
Utilitzar índexs compostos amb cura: Els índexs compostos (sobre múltiples columnes) poden ser útils, però només si les consultes utilitzen les columnes en l'ordre de l'índex.
Monitoritzar i mantenir els índexs: Utilitzar eines com EXPLAIN per analitzar les consultes i assegurar-se que els índexs s'utilitzen correctament. També és important mantenir els índexs amb operacions de VACUUM i REINDEX.

Exercicis pràctics

Exercici 1: Creació d'un índex

Crea un índex sobre la columna cognom de la taula empleats.

-- Solució
CREATE INDEX idx_cognom
ON empleats (cognom);

Exercici 2: Creació d'un índex compost

Crea un índex compost sobre les columnes departament i sou de la taula empleats.

-- Solució
CREATE INDEX idx_departament_sou
ON empleats (departament, sou);

Exercici 3: Anàlisi de consultes

Utilitza l'instrucció EXPLAIN per analitzar la següent consulta i determina si l'índex creat a l'exercici 1 s'està utilitzant.

EXPLAIN SELECT * FROM empleats WHERE cognom = 'Garcia';

Conclusió

Els índexs són una eina poderosa per millorar el rendiment de les consultes en PostgreSQL. És important comprendre els diferents tipus d'índexs disponibles i com utilitzar-los de manera efectiva. Recorda que, tot i que els índexs poden accelerar les consultes, també tenen un cost en termes d'espai d'emmagatzematge i temps d'actualització. Utilitza'ls amb cura i monitoritza el seu ús per assegurar-te que la teva base de dades funcioni de manera òptima.

Índexs

Què és un índex?

Beneficis dels índexs:

Desavantatges dels índexs:

Creant un índex

Exemple pràctic

Tipus d'índexs

Índex B-tree

Índex Hash

Índex GIN (Generalized Inverted Index)

Índex GiST (Generalized Search Tree)

Millors pràctiques per utilitzar índexs

Exercicis pràctics

Exercici 1: Creació d'un índex

Exercici 2: Creació d'un índex compost

Exercici 3: Anàlisi de consultes

Conclusió

Curs de PostgreSQL

Mòdul 1: Introducció a PostgreSQL

Mòdul 2: Operacions bàsiques de SQL

Mòdul 3: Consultes SQL avançades

Mòdul 4: Disseny de bases de dades i normalització

Mòdul 5: Funcionalitats avançades de PostgreSQL

Mòdul 6: Optimització i millora del rendiment

Mòdul 7: Seguretat i gestió d'usuaris

Mòdul 8: Treballant amb JSON i funcionalitats NoSQL

Mòdul 9: Extensions i eines avançades

Mòdul 10: Estudis de cas i aplicacions del món real