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

HOME

Tots els índexs de les dues lliçons anteriors han estat B-tree, i no és casualitat: és el mètode per omissió i el que faràs servir el 95 % de les vegades. Però hi ha preguntes que un B-tree no sap respondre. Com busques '%oli%' sense prefix? Com indexes un document JSONB? Com indexes una taula històrica de tres mil milions de files sense que l'índex ocupi cent gigues? Per a això PostgreSQL té sis mètodes d'accés diferents, i la primera meitat d'aquesta lliçó és el mapa per triar entre ells.

La segona meitat és més important i gairebé mai no s'ensenya: quan no indexar. Perquè l'error car dels índexs no és oblidar-se'n d'un —això es detecta amb EXPLAIN i s'arregla en un minut—, sinó acumular-ne vint que no fa servir ningú, que engreixen cada escriptura i que alenteixen el manteniment. Veuràs el cost real d'un índex, els cinc casos en què fa nosa —inclòs el de BotigaVerda, demostrat—, la regla de selectivitat que decideix la majoria dels casos dubtosos, i una llista de comprovació per preguntar-te abans d'escriure CREATE INDEX.

Contingut

Els sis mètodes d'accés de PostgreSQL
B-tree, Hash i per què el segon gairebé mai no compensa
GIN: llistes de coses dins d'una columna
GiST, SP-GiST i BRIN
pg_trgm: accelerar LIKE '%text%' d'una vegada
Quant costa de debò un índex
Els cinc casos en què un índex no serveix o fa nosa
La regla de la selectivitat
Com decidir: partir de les consultes, no de la intuïció
Llista de comprovació: abans de crear un índex, pregunta't…
Errors habituals i consells
Exercicis
Conclusió

Els sis mètodes d'accés de PostgreSQL

SELECT amname FROM pg_am WHERE amtype = 'i' ORDER BY amname;

amname
brin
btree
gin
gist
hash
spgist

La taula que resumeix quan fer servir cadascun:

Mètode	Estructura	Operadors que admet	Mida relativa	Cas d'ús típic
btree	Arbre equilibrat ordenat	`=`, `<`, `<=`, `>`, `>=`, `BETWEEN`, `IN`, `LIKE 'abc%'`, `ORDER BY`, `MIN`/`MAX`	Mitjana	Tot el normal: claus, dates, preus, FK
hash	Taula hash	Només `=`	Petita-mitjana	Igualtat sobre valors molt llargs
gin	Índex invertit	`@>`, `?`, `&&`, `@@`, trigrames	Gran, lent de construir	Arrays, `JSONB`, text complet, `LIKE '%x%'`
gist	Arbre generalitzat amb solapament	`&&`, `@>`, `<<`, `<->` (veïns)	Mitjana	Rangs, geometria, cerca per proximitat
brin	Resum per blocs (mín/màx)	`=`, `<`, `>`, `BETWEEN`	Diminuta	Taules enormes amb ordre físic natural
spgist	Arbre particionat no equilibrat	`=`, `<<`, prefixos	Petita	Dades amb estructura jeràrquica o molt desigual

Llegeix-la amb aquesta idea: el B-tree indexa un valor per fila; els altres indexen una altra cosa. GIN indexa les parts d'un valor (les paraules d'un text, les claus d'un JSON, els trigrames d'una cadena). GiST indexa regions que es poden solapar. BRIN no indexa files en absolut: indexa blocs de disc.

B-tree, Hash i per què el segon gairebé mai no compensa

El B-tree ja el coneixes de 08-01: ordenat, logarítmic, serveix per a igualtats, rangs, prefixos, ordenacions i extrems. És el valor per omissió de CREATE INDEX i la resposta correcta tret que tinguis un motiu concret per a una altra cosa.

El Hash desa el resultat d'una funció hash de la clau. Això el fa molt ràpid per a =… i completament inútil per a tota la resta:

CREATE INDEX idx_clients_email_hash ON clients USING hash (email);

	B-tree	Hash
`email = '[email protected]'`	✅	✅
`email > 'm'`, `BETWEEN`, `LIKE 'a%'`	✅	❌
`ORDER BY email`	✅	❌
`MIN`/`MAX`	✅	❌
Índexs compostos	✅	❌ (una sola columna)
Pot ser `UNIQUE`	✅	❌
Mida amb claus llargues	Més gran	Més petita (desa 4 bytes, no el valor)

L'avantatge real del hash és un de sol: amb claus molt llargues (una URL de 500 caràcters, un hash SHA-256 en text) desa 4 bytes en lloc del valor sencer, i l'índex surt força més petit. Fora d'aquest cas, el B-tree fa el mateix i moltíssim més per un cost semblant. (Context històric de la seva mala fama: fins a PostgreSQL 9.6 no s'escrivien al registre de transaccions, així que es corrompien després d'una caiguda i no es replicaven. Des de la 10 són segurs.)

GIN: llistes de coses dins d'una columna

GIN (Generalized Inverted Index) és un índex invertit, la mateixa idea que l'índex d'un llibre portada a l'extrem: en lloc d'una entrada per fila, desa una entrada per cada element contingut a la fila, i a cadascuna la llista de files on apareix. Serveix quan una columna conté moltes coses i vols buscar per una d'elles:

-- Text complet: buscar paraules dins dels comentaris de les ressenyes
CREATE INDEX idx_ressenyes_comentari_fts
    ON ressenyes USING gin (to_tsvector('catalan', comentari));

SELECT r.id, r.puntuacio, r.comentari
FROM ressenyes AS r
WHERE to_tsvector('catalan', r.comentari) @@ to_tsquery('catalan', 'envàs');

id	puntuacio	comentari
1	5	Oli excel·lent, gust intens i envàs molt cuidat.

Els seus tres terrenys naturals:

Tipus de dada	Operadors	Exemple
Arrays	`@>`, `<@`, `&&`	`etiquetes @> ARRAY['ecològic']`
`JSONB`	`@>`, `?`, `?&`	`atributs @> '{"origen":"Espanya"}'` — s'estudia a 10-06
Text complet	`@@`	`to_tsvector(...) @@ to_tsquery(...)`
Trigrames	`LIKE`, `ILIKE`, `%`	Secció 5

Els seus dos costos, que cal tenir presents: es construeix a poc a poc i s'actualitza a poc a poc, perquè una sola fila pot generar desenes d'entrades. Per a càrregues d'escriptura intensiva sobre columnes GIN, PostgreSQL amorteix amb una llista pendent (fastupdate), però el patró continua sent "escriu poc, llegeix molt".

GiST, SP-GiST i BRIN

GiST (Generalized Search Tree) és un arbre on cada node descriu una regió que conté els seus fills, i aquestes regions es poden solapar. Això el converteix en l'eina natural per a dades amb extensió:

-- Exemple puntual, no forma part de BotigaVerda:
-- impedir que dues promocions d'un producte se solapin en el temps
CREATE TABLE promocions (
    id          INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    producte_id INTEGER NOT NULL REFERENCES productes(id),
    vigencia    DATERANGE NOT NULL,
    EXCLUDE USING gist (producte_id WITH =, vigencia WITH &&)
);

Aquesta restricció EXCLUDE —que només es pot implementar amb GiST— rebutja qualsevol inserció el rang de dates de la qual se solapi amb una altra promoció del mateix producte. És una regla de negoci que ni UNIQUE ni CHECK no poden expressar. GiST és també la base de PostGIS (mapes, coordenades, "els deu magatzems més propers a València") i de l'operador de veïns <->.

SP-GiST (Space-Partitioned GiST) és el seu cosí per a estructures que es divideixen en trossos disjunts i molt desiguals: arbres de prefixos per a cadenes, quadtrees per a punts, adreces IP. És especialitzat; sabràs que el necessites quan el necessitis.

BRIN (Block Range INdex) mereix més atenció, perquè és el que més sorprèn. No desa una entrada per fila: desa, per cada grup de 128 blocs de disc, el valor mínim i el màxim de la columna. Res més.

-- Sobre la taula de proves que construiràs a 08-05, no sobre BotigaVerda
CREATE INDEX idx_comandes_grans_data_brin ON comandes_grans USING brin (data_comanda);

Amb un històric de comandes que s'insereix en ordre cronològic, el bloc 40.000 conté dates del juny del 2024 i només del juny del 2024. Així que per a WHERE data_comanda BETWEEN '2024-06-01' AND '2024-06-30' el motor descarta el 99,9 % dels blocs llegint un resum minúscul, i recorre seqüencialment els pocs que queden.

Sobre una taula de 2 milions de files	B-tree sobre `data_comanda`	BRIN sobre `data_comanda`
Mida aproximada	~45 MB	~48 kB
Temps de construcció	Segons a minuts	Gairebé instantani
Cost de manteniment	Alt	Mínim
Precisió	Localitza la fila exacta	Localitza el bloc; cal filtrar a dins
Requereix ordre físic	No	Sí, imprescindible

Aquesta última fila és la condició i el parany: si les dades no estan físicament ordenades per la columna, un BRIN no serveix de res. Si cada bloc conté dates del 2019 al 2026, tots els resums se solapen i cal llegir-los tots. Per això BRIN és la resposta perfecta per a taules d'històric o de registre que només creixen pel final, i una mala idea per a una columna que s'actualitza sense ordre.

`pg_trgm`: accelerar `LIKE '%text%'` d'una vegada

Ha arribat el moment de tancar la promesa que 04-01 va deixar oberta. Un B-tree no pot resoldre LIKE '%oli%' perquè sense prefix no hi ha punt d'entrada en l'ordre. La solució és deixar d'indexar la cadena i començar a indexar els seus trigrames: tots els grups de tres caràcters consecutius.

CREATE EXTENSION IF NOT EXISTS pg_trgm;

SELECT show_trgm('Oli') AS trigrames;

trigrames
{" o"," ol",oli,"li "}

L'extensió normalitza a minúscules, afegeix dos espais al davant i un al darrere, i trosseja. Ara un índex GIN sobre aquests trigrames fa que buscar '%oli%' sigui buscar files que continguin tots els trigrames d''oli':

CREATE INDEX idx_productes_nom_trgm
    ON productes USING gin (nom gin_trgm_ops);

SELECT p.id, p.nom, p.preu
FROM productes AS p
WHERE p.nom ILIKE '%oli%'
ORDER BY p.id;

id	nom	preu
1	Oli d'oliva verge extra 500 ml	12.50
8	Oli corporal d'ametlles 200 ml	14.25

Dues files. Amb 20 productes el motor farà Seq Scan de tota manera; amb 40.000 referències al cercador de la botiga, la diferència és entre 300 ms i 3 ms. I hi ha un premi addicional: el mateix índex accelera la cerca per semblança, que és el que vols quan el client escriu malament el nom:

SELECT p.nom, ROUND(similarity(p.nom, 'oly oliva')::numeric, 3) AS semblanca
FROM productes AS p
WHERE p.nom % 'oly oliva'
ORDER BY semblanca DESC;

L'operador % significa "s'assembla prou" segons el llindar pg_trgm.similarity_threshold (0,3 per omissió). Sobre el dubte habitual, GIN o GiST per a trigrames: gin_trgm_ops busca més ràpid però ocupa més i s'escriu pitjor; gist_trgm_ops és més petit, més barat de mantenir i l'únic que admet la cerca per veïns amb <->. GIN per defecte; GiST si escrius molt o necessites ordenar per distància.

Nota de dialecte: això és territori molt divergent. MySQL 8 ofereix índexs FULLTEXT (amb MATCH ... AGAINST), que no són el mateix que trigrames i no acceleren un LIKE '%x%' genèric. SQL Server té Full-Text Search com a component a part. SQLite té el mòdul FTS5. Oracle té Oracle Text. Cap no replica exactament pg_trgm: si el teu producte depèn de la cerca difusa, és un factor real en l'elecció de motor.

Quant costa de debò un índex

Fins aquí, el catàleg. A partir d'aquí, la part que evita els desastres. Cada índex que crees cobra un peatge permanent:

Cost	En què consisteix	Ordre de magnitud orientatiu
Espai en disc	Una còpia ordenada de la columna més un punter per fila	10–40 % de la mida de la taula, per índex
`INSERT` més lent	Cal inserir a tots els índexs de la taula	Uns punts percentuals per índex; amb deu índexs, l'`INSERT` pot duplicar el seu cost
`UPDATE` més lent	S'actualitzen els índexs de les columnes tocades… i sovint tots	Igual o pitjor que l'`INSERT`
`DELETE` més lent	Cada índex acumula entrades mortes que `VACUUM` haurà de netejar	Diferit, però real
Planificador més lent	Més camins per avaluar abans de decidir el pla	Fraccions de mil·lisegon; només importa amb desenes d'índexs
Manteniment més lent	`VACUUM` fa una passada per cada índex; les còpies i les restauracions també	Proporcional al nombre d'índexs
Memòria	Els índexs competeixen amb les dades per la memòria cau	Un índex inútil ocupa memòria cau que a un altre li feia falta

Un matís de PostgreSQL que convé conèixer: un UPDATE escriu una versió nova de la fila, no modifica l'existent. Si la fila nova cap al mateix bloc i cap columna indexada no ha canviat, el motor aplica una optimització anomenada HOT update que evita tocar els índexs. Però n'hi ha prou que un sol índex cobreixi una columna modificada per perdre aquesta optimització i haver d'actualitzar tots els índexs. És a dir: un índex mal triat pot encarir UPDATE que ni tan sols l'utilitzen.

Els cinc casos en què un índex no serveix o fa nosa

Cas 1: la taula és petita — i això és BotigaVerda

Aquest és el més fàcil de demostrar, i ja l'has vist insinuat dues vegades:

SELECT pg_size_pretty(pg_relation_size('productes'))                AS taula,
       pg_relation_size('productes') / 8192                         AS pagines,
       COUNT(*)                                                     AS files
FROM productes;

taula	pagines	files
8192 bytes	1	20

Els vint productes caben en una sola pàgina de 8 kB. Llegir aquesta pàgina costa un accés. Fer servir un índex costaria: llegir la pàgina de metadades de l'índex, llegir l'arrel, obtenir el ctid i tornar a llegir la pàgina de la taula. Com a mínim tres accessos per fer la feina d'un. Per això, encara que creïs l'índex més perfecte del món sobre productes.preu, el planificador l'ignorarà — i farà bé. Ho veuràs al pla real a la lliçó 08-05.

La frontera aproximada és a uns pocs centenars de files, o dit millor: mentre la taula càpiga en unes poques pàgines i visqui permanentment a la memòria cau, no hi ha res a optimitzar. Els índexs de BotigaVerda que has creat a 08-02 són una inversió per al futur, no una millora d'avui.

Cas 2: la columna té baixa cardinalitat

La cardinalitat és el nombre de valors diferents. Com més s'assembla al nombre de files, més útil és l'índex:

SELECT COUNT(DISTINCT estat)           AS estats,
       COUNT(DISTINCT metode_pagament) AS metodes,
       COUNT(*)                        AS comandes
FROM comandes;

estats	metodes	comandes
5	4	20

Columna	Valors diferents	Cardinalitat	Indexar?
`clients.email`	15 de 15	Màxima	✅ Ja hi és (`UNIQUE`)
`comandes.id`	20 de 20	Màxima	✅ Ja hi és (PK)
`comandes.client_id`	12 de 20	Alta	✅ Sí
`productes.categoria_id`	6 de 20	Mitjana	✅ Sí, és FK
`comandes.estat`	5 de 20	Baixa	⚠️ Només parcial
`clients.pais`	3 de 15	Baixa	❌ No
`productes.actiu`	2 de 20	Mínima	❌ No, tret que sigui parcial

El cas booleà és el més clar. productes.actiu té 19 certs i 1 fals. Un índex sobre aquesta columna tindria dos "blocs" d'entrades, i buscar per actiu = TRUE retornaria el 95 % de la taula: no hi ha res a descartar. L'única manera de treure partit d'una columna així és al revés, amb un índex parcial (08-02) que indexi només el costat minoritari o que faci servir actiu com a condició i una altra columna com a clau.

Cas 3: per aquesta columna no es filtra mai

productes.cost, comandes.despeses_enviament, linies_comanda.descompte, empleats.salari. Són columnes que es mostren, se sumen i es calculen, però per les quals ningú no posa un WHERE. Un índex sobre elles és cost pur: espai, escriptures i VACUUM a canvi de zero lectures accelerades.

Abans d'indexar una columna, la pregunta és literal: puc escriure la consulta real, amb el seu WHERE, que aquest índex accelerarà? Si no et surt, no el creïs.

Cas 4: la taula s'escriu molt més del que es llegeix

Una taula de registre d'esdeveniments, d'auditoria o de telemetria rep milers d'INSERT per segon i es consulta un cop al dia. Cada índex multiplica la feina del camí calent per beneficiar el camí fred. En aquests casos:

Redueix els índexs al mínim imprescindible.
Si l'accés és per data i la taula només creix pel final, BRIN en lloc de B-tree: pràcticament gratis de mantenir.
Considera crear l'índex només quan s'hagi de fer servir (abans de l'informe mensual) i esborrar-lo després, encara que soni estrany.

Cas 5: ja existeix un altre índex que el cobreix

És el cas de la secció 10 de 08-02: (a) sobra si existeix (a, b). Val la pena repetir-ho aquí perquè és l'índex inútil més freqüent en bases de dades reals: es va crear el simple, mesos després algú va crear el compost per a una altra consulta, i ningú no va esborrar el primer.

La regla de la selectivitat

Els cinc casos anteriors es resumeixen en un sol criteri quantitatiu, i és el que fa servir el mateix planificador:

Si la consulta retornarà més d'un 5–10 % de les files de la taula, el recorregut seqüencial acostuma a guanyar.

Sona contraintuïtiu fins que se n'entén el motiu, que és purament físic:

Un Seq Scan llegeix blocs contigus. El disc (i sobretot la lectura anticipada del sistema operatiu) està optimitzat per a això: llegir 1.000 blocs seguits no costa 1.000 vegades llegir-ne un.
Un Index Scan produeix ctid en l'ordre de l'índex, no en el del disc. Cada fila pot ser en un bloc diferent i en qualsevol posició: són accessos aleatoris. PostgreSQL ho modela amb dos paràmetres, seq_page_cost = 1.0 i random_page_cost = 4.0: un accés aleatori s'estima quatre vegades més car que un de seqüencial.

Amb aquests números, si el teu filtre retorna la meitat de la taula, anar per l'índex significa fer mig milió d'accessos aleatoris a quatre vegades el preu per evitar llegir un milió de blocs seqüencials. Perds.

Aplicat a BotigaVerda:

Filtre	Files retornades	Selectivitat	Veredicte
`WHERE id = 7`	1 de 20	5 %	Índex, sens dubte
`WHERE client_id = 2`	2 de 20	10 %	Índex (en una taula gran)
`WHERE preu > 10`	7 de 20	35 %	`Seq Scan`
`WHERE estat = 'lliurat'`	14 de 20	70 %	`Seq Scan`, clarament
`WHERE pais = 'Espanya'` (clients)	11 de 15	73 %	`Seq Scan`, clarament

Sobre random_page_cost: aquest 4.0 per omissió ve de l'època dels discos mecànics. En un SSD la diferència entre accés seqüencial i aleatori és molt menor, i la recomanació habitual és baixar-lo a 1.1. És un dels ajustos de configuració que més canvien els plans triats, i explica per què la mateixa consulta pot fer servir l'índex en un servidor i no en un altre. Veuràs com consultar-lo amb EXPLAIN (SETTINGS) a 08-05.

Com decidir: partir de les consultes, no de la intuïció

L'antipatró té nom propi: "indexem totes les columnes per si de cas". Sona prudent i és exactament el contrari, perquè canvia un problema visible i fàcil (una consulta lenta que EXPLAIN t'assenyala en deu segons) per un d'invisible i difícil (escriptures un 40 % més lentes, VACUUM que no acaba, memòria cau malgastada, i un pla mal triat de tant en tant).

El mètode correcte va al revés: de les consultes als índexs. I la font de veritat és l'extensió pg_stat_statements, que registra totes les sentències executades amb els seus temps:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

SELECT calls,
       ROUND(total_exec_time::numeric, 1)          AS ms_total,
       ROUND(mean_exec_time::numeric, 2)           AS ms_mitjana,
       rows,
       LEFT(query, 60)                             AS consulta
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

Ordena per temps total, no per temps mitjà: una consulta de 5 ms executada un milió de vegades al dia fa més mal que una de 10 segons que es llança una vegada. Aquest rànquing et dóna la llista real de candidates, i sobre cadascuna hi apliques EXPLAIN ANALYZE (08-05).

En quatre passos:

Mesura quines consultes consumeixen el temps (pg_stat_statements).
Diagnostica cadascuna amb EXPLAIN ANALYZE i busca el Seq Scan sobre una taula gran amb un filtre selectiu.
Prova l'índex i torna a mesurar: ha canviat el pla?, ha baixat el temps?
Revisa al cap d'unes setmanes amb pg_stat_user_indexes: l'està fent servir algú?

Llista de comprovació: abans de crear un índex, pregunta't…

#	Pregunta	Si la resposta és…
1	Puc escriure la consulta concreta que accelerarà?	Si no, no el creïs
2	Quantes files retorna aquest filtre sobre el total?	Més del 10 %, probablement no compensi
3	La columna apareix nua a la condició?	Si va dins d'una funció, necessites un índex d'expressió o reescriure la consulta
4	Ja existeix un índex el prefix del qual serveixi?	Si sí, no en creïs un altre
5	És una clau forana sense índex?	Gairebé sempre sí, crea'l
6	Quant s'escriu en aquesta taula enfront del que es llegeix?	Escriptura intensiva: mínim indispensable, o BRIN
7	El puc fer parcial perquè sigui més petit?	Gairebé sempre que hi hagi una condició constant a la consulta
8	Amb quin tipus d'índex?	B-tree tret que hi hagi un motiu explícit (text, JSONB, geometria, històric)
9	Com comprovaré que s'utilitza?	`EXPLAIN` abans i després, i `idx_scan` setmanes més tard
10	El crearé amb `CONCURRENTLY`?	En producció, sempre

Errors habituals i consells

Fer servir hash "perquè les cerques per igualtat són més ràpides". El B-tree resol la igualtat gairebé igual de bé i a més serveix per a rangs, ordre i UNIQUE.
Posar un BRIN sobre una columna sense ordre físic. Si els valors estan repartits per tota la taula, els resums se solapen i l'índex no descarta res.
Esperar que un GIN acceleri les escriptures. És el mètode més car de mantenir; el seu lloc és "escriu poc, llegeix molt".
Indexar un booleà. Dos valors diferents no descarten res. La versió útil és un índex parcial amb aquest booleà al WHERE.
Crear un índex sense una consulta concreta al darrere. És la definició de l'antipatró "per si de cas".
Confondre "l'índex existeix" amb "l'índex s'utilitza". Només EXPLAIN i pg_stat_user_indexes responen a la segona cosa.
Ordenar pg_stat_statements per temps mitjà. El mal real el fa el temps total: crides × cost.
Consell: compta les files abans d'indexar. SELECT COUNT(*) FROM taula WHERE <el teu filtre> enfront del total et dóna la selectivitat en cinc segons i decideix la meitat dels casos.
Consell: quan dubtis entre dos índexs, crea'n un, mesura i esborra el pitjor. DROP INDEX és instantani i no toca les dades: experimentar surt barat.
Consell: apunta al mateix esquema per a què es va crear cada índex. COMMENT ON INDEX idx_comandes_pendents IS 'Panell de gestió: comandes per processar'; fa que d'aquí a dos anys algú el pugui esborrar amb criteri.

Exercicis

Exercici 1

Tria el mètode d'accés adequat per a cada necessitat i escriu el CREATE INDEX:

El cercador de la botiga permet escriure qualsevol tros del nom d'un producte.
Un històric de 500 milions d'esdeveniments, inserit sempre en ordre de data, que es consulta per rangs de dies.
La columna atributs JSONB d'un producte, on es busca per parells clau-valor.
El camp email d'una taula de 50 milions d'usuaris, consultat sempre per igualtat exacta i mai ordenat.
Un calendari de reserves on dues reserves de la mateixa sala no es poden solapar en el temps.

Exercici 2

Per a cada proposta, digues si crearies l'índex i per què. Fes servir dades reals de BotigaVerda.

-- a)
CREATE INDEX idx_productes_actiu ON productes (actiu);
-- b)
CREATE INDEX idx_clients_pais ON clients (pais);
-- c)
CREATE INDEX idx_linies_comanda_comanda_id ON linies_comanda (comanda_id);
-- d)
CREATE INDEX idx_comandes_despeses_enviament ON comandes (despeses_enviament);
-- e)
CREATE INDEX idx_comandes_client_id ON comandes (client_id);   -- ja existeix (client_id, data_comanda)

Exercici 3

Una aplicació amb una taula esdeveniments de 800 milions de files rep 4.000 INSERT per segon i té onze índexs. L'equip es queixa que les insercions van cada vegada més lentes i que VACUUM no acaba mai.

Explica la relació entre els onze índexs i els dos símptomes.
Proposa un mètode per decidir quins esborrar, amb les consultes concretes.
Quina alternativa hi ha per a l'índex de la columna data si la taula només creix pel final?

Solucions

Solució 1

-- 1. GIN amb trigrames: és l'únic que resol LIKE '%text%'
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX idx_productes_nom_trgm ON productes USING gin (nom gin_trgm_ops);

-- 2. BRIN: ordre físic natural per data, índex diminut i gairebé gratis de mantenir
CREATE INDEX idx_esdeveniments_data_brin ON esdeveniments USING brin (data);

-- 3. GIN: JSONB amb l'operador de contenció @>  (vegeu 10-06)
CREATE INDEX idx_productes_atributs ON productes USING gin (atributs);

-- 4. Hash: és l'únic cas on compensa, per la mida de la clau
CREATE INDEX idx_usuaris_email_hash ON usuaris USING hash (email);

-- 5. GiST: és l'únic mètode que admet una restricció EXCLUDE amb solapament
ALTER TABLE reserves ADD EXCLUDE USING gist (sala_id WITH =, periode WITH &&);

Al 4, un B-tree seria igual de vàlid i més versàtil; el hash només guanya en mida, i únicament perquè mai no s'ordena ni es busca per rang. Si hi hagués el mínim dubte, B-tree.

Solució 2

#	Veredicte	Raó
a) `productes (actiu)`	❌ No	Booleà amb 19 certs i 1 fals: cardinalitat mínima i selectivitat del 95 %. La versió útil seria un índex parcial com ara `... (categoria_id) WHERE actiu`
b) `clients (pais)`	❌ No	Tres valors per a 15 files, i `'Espanya'` són 11 de 15 (73 %): molt per sobre del llindar de selectivitat
c) `linies_comanda (comanda_id)`	✅ Sí	Clau forana sense índex, `ON DELETE CASCADE`, sobre la taula amb més files de l'esquema i present a gairebé tots els JOIN. És el millor índex de BotigaVerda
d) `comandes (despeses_enviament)`	❌ No	Ningú no filtra per import de ports: és una columna que es mostra i se suma. A més, només té 6 valors diferents
e) `comandes (client_id)`	❌ No	Redundant: l'índex `(client_id, data_comanda)` ja comença per `client_id` i resol tot el que aquest resoldria

Solució 3

1. La relació. Cada INSERT ha d'escriure a la taula i als onze índexs: és un factor multiplicador sobre les 4.000 insercions per segon, i explica que el ritme es degradi a mesura que els arbres creixen i hi ha més divisions de pàgina. I VACUUM recorre cada índex per separat per netejar les entrades mortes: amb onze índexs sobre 800 milions de files, cada passada és onze vegades la feina. Els dos símptomes són la mateixa causa.

2. El mètode:

-- Índexs que no ha fet servir ningú, ordenats pel que ocupen
SELECT relname, indexrelname, idx_scan,
       pg_size_pretty(pg_relation_size(indexrelid)) AS mida
FROM pg_stat_user_indexes
WHERE schemaname = 'public' AND relname = 'esdeveniments'
ORDER BY idx_scan, pg_relation_size(indexrelid) DESC;

Amb les quatre cauteles de 08-02: comprovar des de quan s'acumulen els comptadors (que no falti l'informe trimestral), no tocar els que donen suport a una PK o a un UNIQUE, revisar els de claus foranes, i mirar també les rèpliques. Després, contrastar amb pg_stat_statements quines consultes s'executen de debò, i buscar redundàncies per la regla del prefix: qualsevol (a) que convisqui amb un (a, b) sobra.

3. L'alternativa. Un BRIN sobre data. Si la taula només creix pel final, l'ordre físic coincideix amb el cronològic, que és exactament el seu requisit. Passaries d'un B-tree de desenes de gigabytes —que cal mantenir a cadascun dels 4.000 INSERT per segon i recórrer sencer a cada VACUUM— a un índex d'uns pocs megabytes amb cost de manteniment gairebé nul, conservant la capacitat de filtrar per rangs de dies.

Conclusió

Ja tens el mapa complet i, sobretot, el criteri per no fer-lo servir:

PostgreSQL ofereix sis mètodes d'accés. El B-tree resol el 95 % dels casos; el hash gairebé mai no compensa; GIN indexa les parts d'un valor (arrays, JSONB, text, trigrames); GiST indexa regions solapables (rangs, geometria, EXCLUDE); BRIN resumeix blocs i és diminut però exigeix ordre físic; SP-GiST és per a estructures jeràrquiques.
pg_trgm + GIN tanca la promesa de 04-01: LIKE '%oli%' i ILIKE per fi tenen índex, i de regal la cerca per semblança amb similarity() i l'operador %.
Un índex costa: espai, INSERT/UPDATE/DELETE més lents, VACUUM més lent, memòria cau ocupada. I pot encarir fins i tot els UPDATE que no l'utilitzen, en impedir l'optimització HOT.
No indexis taules petites —els 20 productes de BotigaVerda caben en una pàgina, i l'índex ocupa més que la taula—, columnes de baixa cardinalitat, columnes per les quals mai no es filtra, taules d'escriptura massiva, ni res que ja cobreixi un altre índex.
La regla de la selectivitat: per sobre del 5–10 % de files retornades, guanya el Seq Scan, perquè un accés aleatori s'estima quatre vegades més car que un de seqüencial.
El mètode correcte va de les consultes als índexs, amb pg_stat_statements ordenat per temps total, i mai al revés.

Amb les tres primeres lliçons saps què és un índex, com es crea i quan no crear-lo. Però un índex és només una de les eines del rendiment, i moltes vegades ni tan sols la que cal: hi ha consultes que van lentes per com estan escrites, i cap índex del món no les arregla. A la lliçó 08-04, Tècniques d'optimització de consultes, veuràs les regles d'escriptura que sí que importen —començant per la sargabilitat, aquell WHERE EXTRACT(YEAR FROM data_comanda) = 2025 que cal convertir en un rang—, com funcionen les estadístiques que alimenten el planificador i què passa quan es desfasen, i el problema que més vegades hi ha darrere d'una pantalla lenta i que no s'arregla a la base de dades en absolut: l'N+1 de l'aplicació.

Tipus d'índex i quan no indexar

Contingut

Els sis mètodes d'accés de PostgreSQL

B-tree, Hash i per què el segon gairebé mai no compensa

GIN: llistes de coses dins d'una columna

GiST, SP-GiST i BRIN

`pg_trgm`: accelerar `LIKE '%text%'` d'una vegada

Quant costa de debò un índex

Els cinc casos en què un índex no serveix o fa nosa

Cas 1: la taula és petita — i això és BotigaVerda

Cas 2: la columna té baixa cardinalitat

Cas 3: per aquesta columna no es filtra mai

Cas 4: la taula s'escriu molt més del que es llegeix

Cas 5: ja existeix un altre índex que el cobreix

La regla de la selectivitat

Com decidir: partir de les consultes, no de la intuïció

Llista de comprovació: abans de crear un índex, pregunta't…

Errors habituals i consells

Exercicis

Exercici 1

Exercici 2

Exercici 3

Solucions

Conclusió

Curs de SQL

Mòdul 1: Introducció a SQL

Mòdul 2: Consultes bàsiques de SQL

Mòdul 3: Treballar amb múltiples taules

Mòdul 4: Filtratge avançat de dades

Mòdul 5: Manipulació de dades

Mòdul 6: Funcions avançades de SQL

Mòdul 7: Subconsultes i consultes imbricades

Mòdul 8: Índexs i optimització del rendiment

Mòdul 9: Transaccions i concurrència

Mòdul 10: Temes avançats

Mòdul 11: SQL a la pràctica

Mòdul 12: Projecte final

Tipus d'índex i quan no indexar

Contingut

Els sis mètodes d'accés de PostgreSQL

B-tree, Hash i per què el segon gairebé mai no compensa

GIN: llistes de coses dins d'una columna

GiST, SP-GiST i BRIN

pg_trgm: accelerar LIKE '%text%' d'una vegada

Quant costa de debò un índex

Els cinc casos en què un índex no serveix o fa nosa

Cas 1: la taula és petita — i això és BotigaVerda

Cas 2: la columna té baixa cardinalitat

Cas 3: per aquesta columna no es filtra mai

Cas 4: la taula s'escriu molt més del que es llegeix

Cas 5: ja existeix un altre índex que el cobreix

La regla de la selectivitat

Com decidir: partir de les consultes, no de la intuïció

Llista de comprovació: abans de crear un índex, pregunta't…

Errors habituals i consells

Exercicis

Exercici 1

Exercici 2

Exercici 3

Solucions

Conclusió

Curs de SQL

Mòdul 1: Introducció a SQL

Mòdul 2: Consultes bàsiques de SQL

Mòdul 3: Treballar amb múltiples taules

Mòdul 4: Filtratge avançat de dades

Mòdul 5: Manipulació de dades

Mòdul 6: Funcions avançades de SQL

Mòdul 7: Subconsultes i consultes imbricades

Mòdul 8: Índexs i optimització del rendiment

Mòdul 9: Transaccions i concurrència

Mòdul 10: Temes avançats

Mòdul 11: SQL a la pràctica

Mòdul 12: Projecte final

`pg_trgm`: accelerar `LIKE '%text%'` d'una vegada