El mòdul 7 es va tancar amb un canvi de pregunta. Durant set mòduls la pregunta ha estat això retorna el que vull?; a partir d'aquí és quant triga?. I la resposta gairebé sempre passa pel mateix objecte: l'índex, una estructura de dades auxiliar que el motor manté al costat de la taula i que converteix "mira totes les files fins a trobar-la" en "ves directament on és".

En aquesta lliçó no crearàs cap índex encara. Primer cal entendre quin problema resolen i com funcionen per dins, perquè gairebé tots els errors que es cometen amb índexs —crear els que no serveixen, no crear els que fan falta, estranyar-se que el motor els ignori— vénen de no tenir el model mental correcte. En acabar sabràs per què buscar entre 20 milions de files pot costar quatre accessos a disc, per què LIKE '%oli%' no en pot aprofitar cap, i —el que més et sorprendrà— quins índexs té ja BotigaVerda sense que ningú els hagi creat i quins li falten.

1. L'analogia de l'índex d'un llibre, i on es trenca
2. Com llegeix el motor una taula sense índex
3. L'estructura B-tree
4. Què desa realment un índex: la clau i el punter
5. Index Scan, Index Only Scan i índexs cobridors
6. Què accelera un B-tree i què no
7. Els índexs que BotigaVerda ja té
8. El forat: PostgreSQL no indexa les claus foranes
9. El preu d'un índex
10. Errors habituals i consells
11. Exercicis
12. Conclusió

Tens un manual de 800 pàgines i vols saber on es parla de HAVING. Hi ha dues maneres:

• Fullejar el llibre sencer fins a trobar-ho. Funciona sempre, i costa 800 pàgines.
• Anar a l'índex alfabètic del final, buscar "HAVING" —que està ordenat, així que el localitzes en segons— i llegir "pàg. 412".

L'índex del llibre és exactament un índex de base de dades: una còpia ordenada d'una part de la informació (els termes) juntament amb un punter a on és la resta (la pàgina). No conté el llibre; conté el just per saltar-hi.

L'analogia és bona, però convé marcar on es trenca, perquè és justament allà on comencen les decisions interessants:

Aquestes cinc diferències són, en realitat, el guió del mòdul sencer. La quarta és la més difícil d'acceptar: crear un índex no garanteix que s'utilitzi. Ho veuràs demostrat sobre BotigaVerda a la lliçó 08-05.

Sense índex només hi ha una estratègia possible: el recorregut seqüencial (sequential scan, o Seq Scan en l'argot de PostgreSQL). El motor llegeix la taula bloc a bloc des del principi, comprova el WHERE a cada fila i descarta les que no compleixen.

Set files de vint. Per donar-te-les, el motor n'ha llegit vint. Amb 20 files això és gratis. La clau és com escala:

(Xifres d'intuïció, no de laboratori: el nombre exacte depèn de l'amplada de les files, de la memòria cau i del disc. El que importa és la forma de les dues columnes.)

La columna del Seq Scan creix linealment: el doble de files, el doble de temps. La de l'índex creix logarítmicament: multiplicar les files per mil hi afegeix un accés. Aquesta diferència entre O(n) i O(log n) és tot el mòdul en una línia, i explica per què una consulta que va perfecta amb les dades de desenvolupament pot tombar producció sis mesos després.

Un Seq Scan no és un error. És l'estratègia correcta quan la taula és petita o quan retornaràs bona part d'ella. Ho veuràs amb detall a 08-03 i demostrat a 08-05.

L'índex per defecte a PostgreSQL —i a tots els motors relacionals— és el B-tree (balanced tree, arbre equilibrat). És un arbre ordenat amb tres tipus de node:

• Arrel: un únic node, punt d'entrada de tota cerca.
• Nodes interns: contenen valors separadors i punters a nodes del nivell inferior. Diuen "els valors menors que 5,50 són per aquí".
• Fulles: contenen els valors reals de la clau, cadascun amb el punter a la fila de la taula. A més, les fulles estan enllaçades entre si en ordre, cosa que permet recórrer un rang sense tornar a pujar a l'arbre.

Així es veuria un índex B-tree sobre productes.preu, amb els 20 preus del catàleg repartits en cinc fulles de quatre entrades:

Busca el producte de 12,50 €: entres per l'arrel, veus que 12,50 està entre 9,90 i 14,25, baixes a la fulla 4 i el trobes. Dos accessos, no vint. I per a WHERE preu BETWEEN 9 AND 15 hi entres una vegada per 9,00 i després segueixes les fletxes puntejades entre fulles fins a passar-te de 15: això és el que fa que un B-tree serveixi igual de bé per a rangs que per a igualtats.

A PostgreSQL cada node de l'arbre és una pàgina de 8 kB. En aquesta pàgina hi caben moltes entrades: per a una clau entera de 4 bytes, de l'ordre de 250 entrades útils un cop descomptades les capçaleres i el marge que el motor deixa lliure. Aquest número s'anomena factor de ramificació (fanout), i és el que fa que l'arbre sigui ample i baix en lloc d'estret i alt.

Llegeix-ho a poc a poc, perquè és la dada que convé memoritzar del mòdul: una taula de quinze milions de files es recorre amb tres accessos. I d'aquests tres, els nivells superiors són gairebé sempre a la memòria cau perquè totes les consultes hi passen, així que a la pràctica el cost real acostuma a ser un accés a disc, o cap.

El fanout depèn de l'amplada de la clau: indexar un INTEGER dóna arbres molt amples; indexar un VARCHAR(150) amb noms llargs dóna entrades cinc vegades més grans, menys entrades per pàgina i, amb les mateixes files, un nivell més d'alçada. És el primer motiu pel qual indexar columnes estretes surt més barat.

L'"equilibrat" del nom significa que totes les fulles són a la mateixa profunditat, i el motor ho manté així dividint i fusionant pàgines en inserir i esborrar. Per això no existeixen "índexs desequilibrats" que calgui reconstruir a mà: el cost de mantenir-los equilibrats es paga a cada escriptura, i d'aquí ve bona part del preu del qual parla la secció 9.

Una entrada de fulla no conté la fila. Conté dues coses:

1. El valor de la clau (12.50).
2. Un punter físic a la fila, que a PostgreSQL s'anomena ctid i és un parell (bloc, posició dins del bloc).

El ctid és una columna de sistema que pots consultar:

Els vint productes són al bloc 0: hi caben de sobres en una sola pàgina de 8 kB. Aquest detall, aparentment anecdòtic, és la raó que cap índex no serveixi de res en aquesta taula, i tornarà a la secció 7 i a 08-05.

Compte: el ctid no és un identificador estable. Canvia quan la fila s'actualitza, perquè PostgreSQL n'escriu una versió nova en un altre lloc. No el desis mai en una columna ni el facis servir com a clau: per a això hi ha id. Aquí només l'utilitzem per veure la maquinària per dins.

La conseqüència que l'índex desi un punter i no la fila és important: una cerca per índex implica dos passos. Primer es baixa per l'arbre fins a la fulla i s'obté el ctid; després cal anar a la taula (el heap) a llegir la fila i recollir les columnes que has demanat. Aquest segon pas s'anomena heap fetch, i si la teva consulta retorna mil files, són mil salts a posicions possiblement disperses del disc.

D'aquí surten dos dels nodes de pla que veuràs constantment a 08-05:

El segon és la grossa: s'estalvia el heap fetch sencer. I s'aconsegueix amb el que s'anomena un índex cobridor (covering index): un índex que cobreix totes les columnes que la consulta necessita, tant per filtrar com per mostrar.

A la lliçó 08-02 veuràs com fabricar índexs cobridors expressament amb la clàusula INCLUDE, que afegeix columnes a l'índex només per llegir-les, sense fer-les servir per ordenar.

Matís honest: a PostgreSQL l'Index Only Scan no sempre evita el 100 % dels accessos a la taula. L'índex no sap si una fila és visible per a la teva sessió, així que consulta un mapa auxiliar (visibility map) i, per als blocs marcats com a no del tot nets, sí que va al heap. Això apareix al pla com a Heap Fetches: N. Si aquest número és alt, la taula necessita manteniment (VACUUM), i això es tracta a 08-05.

Un B-tree està ordenat. Tot el que es pugui expressar com "ves a un punt de l'ordre i avança" ho resol; tota la resta, no. Aquesta taula tanca tres promeses que el curs arrossega des dels mòduls 2, 4 i 6:

Les quatre files vermelles del final tenen la mateixa causa: per fer servir un índex, la columna ha d'aparèixer nua a un costat de la comparació. Tan bon punt l'emboliques en una funció, en un càlcul o en un comodí inicial, el motor ja no pot traduir la teva condició a "un punt de l'ordre i avança".

Dues conseqüències pràctiques que resoldràs a les properes lliçons, no ara:

• Les condicions de les files vermelles es poden reescriure gairebé sempre. EXTRACT(YEAR FROM data_comanda) = 2025 és idèntic a data_comanda >= '2025-01-01' AND data_comanda < '2026-01-01', i aquesta segona versió sí que fa servir l'índex: són les mateixes 16 comandes del 2025. Aquesta reescriptura té nom —sargabilitat— i és el cor de la lliçó 08-04.
• Quan no es poden reescriure, hi ha eines específiques: un índex sobre l'expressió per a LOWER(email) (08-02) i l'extensió pg_trgm amb un índex GIN per al LIKE '%oli%' que 04-01 va deixar pendent (08-03).

Aquí arriba la sorpresa: mai no has escrit un CREATE INDEX i BotigaVerda ja té onze índexs. A 05-01 es va dir de passada que PRIMARY KEY i UNIQUE "s'implementen creant un índex per sota, i les estructures es veuen al mòdul 8". Ha arribat el moment.

Un sol índex, productes_pkey, i btree escrit explícitament: és l'estructura de la secció 3. Per veure'ls tots de cop, la vista de catàleg pg_indexes:

Onze índexs: nou de les claus primàries i dos dels UNIQUE de categories.nom i clients.email. Tots són UNIQUE INDEX ... USING btree, i tots existeixen perquè una restricció els necessita: l'única manera raonable de comprovar "aquest valor no està repetit" abans d'acceptar un INSERT és tenir els valors ordenats. Per això la restricció i l'índex són a PostgreSQL el mateix objecte físic, i per això un INSERT a clients amb un email duplicat falla en microsegons i no llegint les 15 files.

Això explica també per què WHERE c.id = 7 o WHERE c.email = '[email protected]' han estat sempre ràpides al curs, encara que ningú no hagués parlat d'índexs.

I ara el punt més valuós de la lliçó. Mira un altre cop la llista: productes.categoria_id no hi apareix. Ni comandes.client_id. Ni linies_comanda.comanda_id.

PostgreSQL crea un índex automàticament per a PRIMARY KEY i per a UNIQUE, però NO per a les claus foranes. L'índex existeix al costat referenciat (la PK del pare), mai al costat referenciant (la columna filla).

Aquestes són les onze claus foranes de BotigaVerda, totes sense índex:

Per què fa mal, en dos fronts.

Primer, els JOIN. La consulta canònica del curs uneix linies_comanda amb comandes, clients i productes. Cada ON lc.comanda_id = co.id es pot resoldre en dues direccions: buscant a comandes per id (indexat, ràpid) o buscant a linies_comanda per comanda_id (no indexat, recorregut complet). Amb 47 línies tant és; amb 20 milions, "les línies de la comanda 8.412.006" sense índex significa llegir els 20 milions.

Segon, i menys conegut: els esborrats al pare. Quan executes DELETE FROM comandes WHERE id = 5, PostgreSQL està obligat a comprovar totes les taules que referencien comandes —linies_comanda i devolucions— per aplicar el CASCADE. Si aquestes columnes no tenen índex, cada esborrat d'una comanda provoca un recorregut complet de les taules filles. És una de les causes clàssiques d'"esborrar una fila triga 40 segons" en bases de dades grans, i el motor no t'avisa: la consulta que va lenta no és la que has escrit.

Nota de dialecte: això no és així a tots els motors. MySQL amb InnoDB crea un índex automàticament sobre cada columna de clau forana si no n'existeix ja un que serveixi, i de fet ho exigeix. Oracle i SQL Server es comporten com PostgreSQL: no el creen, i la seva documentació oficial recomana crear-lo a mà. Si véns de MySQL, aquesta és probablement la diferència de rendiment que més et mossegarà en migrar.

La regla pràctica que t'endus —i que aplicaràs a la lliçó següent— és curta: gairebé tota columna de clau forana vol el seu índex. "Gairebé", perquè hi ha excepcions (una taula filla diminuta, o una FK per la qual mai no es filtra ni s'esborra), i aquestes excepcions són el tema de 08-03.

Si els índexs fossin gratis, la resposta seria indexar-ho tot. No ho són, i convé tenir el cost present des del primer minut:

La conseqüència és que un índex és una aposta: acceleres les lectures que l'utilitzen a canvi d'encarir totes les escriptures de la taula. En una taula que es llegeix mil vegades per cada escriptura, l'aposta és excel·lent. En una taula de registre d'esdeveniments que s'escriu constantment i es consulta un cop al mes, és un mal negoci. Quantificar aquesta aposta —i la llista de casos en què no s'ha d'indexar— és tota la lliçó 08-03.

• Creure que crear un índex garanteix que s'utilitzi. El planificador decideix. Amb taules petites, amb filtres poc selectius o amb estadístiques desfasades, triarà el Seq Scan i farà bé.
• Pensar que l'índex conté la fila. Conté la clau i un punter. D'aquí el segon accés a la taula, l'Index Scan enfront de l'Index Only Scan i l'existència mateixa dels índexs cobridors.
• Donar per fet que les claus foranes estan indexades. A PostgreSQL no ho estan. És el descobriment més rendible d'aquesta lliçó.
• Indexar una columna i continuar filtrant amb una funció al damunt. LOWER(email) no fa servir l'índex sobre email. O reescrius la consulta, o crees un índex sobre l'expressió (08-02).
• Esperar miracles de LIKE '%text%'. Cap B-tree no et pot ajudar sense prefix. La solució existeix, però és una altra família d'índexs (08-03).
• Confondre el ctid amb un identificador. Canvia a cada UPDATE. Per identificar una fila hi ha la seva clau primària.
• Consell: pensa en "un punt de l'ordre i avançar". Si la teva condició es pot traduir a això, l'índex serveix. Si no, no. És el millor filtre mental que existeix per predir un pla sense executar-lo.
• Consell: mira sempre \d taula abans de crear un índex. És la manera més ràpida de descobrir que ja n'existeix un d'equivalent.
• Consell: memoritza la taula d'alçades. Saber que 15 milions de files són 3 accessos t'estalvia discussions senceres sobre si "la taula és massa gran per buscar-hi".

Per a cada condició, digues si un índex B-tree sobre la columna implicada es podria fer servir, i justifica-ho en una frase.

Sense executar res, respon:

1. Quants índexs té avui la taula linies_comanda i sobre quines columnes?
2. Quines de les seves columnes són claus foranes i quines d'aquestes estan indexades?
3. DELETE FROM comandes WHERE id = 5 va esborrar en cascada tres línies a la lliçó 01-06. Descriu què ha de fer el motor a linies_comanda per localitzar-les, i com canviaria amb 20 milions de línies.

Un company proposa: "Com que els índexs acceleren les consultes, crearem un índex sobre cada columna de comandes: client_id, empleat_id, data_comanda, estat, metode_pagament i despeses_enviament."

1. Dóna dos arguments tècnics en contra, fent servir el que has vist a les seccions 6 i 9.
2. Quines d'aquestes sis columnes et semblen candidates raonables i quines no? Justifica-ho amb la naturalesa de les dades de BotigaVerda.

Solució 1

Solució 2

1. Un de sol: linies_comanda_pkey, un índex únic B-tree sobre id, creat per la PRIMARY KEY.

2. Té dues claus foranes, comanda_id (→ comandes, ON DELETE CASCADE) i producte_id (→ productes, ON DELETE RESTRICT), i cap de les dues no està indexada. És la taula amb més volum de l'esquema i la que apareix pràcticament a tots els JOIN del curs: és la primera candidata a índex de tota BotigaVerda.

3. Per aplicar el CASCADE, el motor ha de trobar totes les files amb comanda_id = 5. Sense índex sobre comanda_id, l'única manera és un recorregut seqüencial complet de linies_comanda: llegir les 47 files, quedar-se amb les 3 i esborrar-les. Amb 20 milions de línies, aquest mateix DELETE d'una fila a comandes obligaria a llegir 20 milions de files —i altre tant a devolucions, que també referencia comandes amb CASCADE—. La consulta lenta no seria la que has escrit, sinó la comprovació d'integritat que dispara: per això aquest cas és tan difícil de diagnosticar sense EXPLAIN (08-05).

Solució 3

1. Dos arguments:

• Cada índex encareix totes les escriptures de la taula. Amb sis índexs, un INSERT a comandes passa d'actualitzar una estructura (la PK) a actualitzar-ne set. Un pic de comandes per Nadal es converteix en un problema d'escriptura que abans no existia.
• Diversos d'aquests índexs no s'utilitzarien mai. Un índex només serveix si el planificador el tria, i per a això el filtre ha de ser selectiu. estat té 5 valors per a 20 comandes i metode_pagament en té 4: filtrar per un d'ells retorna una fracció enorme de la taula, i llegir-la sencera seqüencialment és més barat que anar a l'índex i tornar a la taula fila a fila.

2. El repartiment:

Fixa't en el criteri que hi apunta: no s'indexa una columna perquè existeixi, sinó perquè hi ha consultes reals que filtren, uneixen o ordenen per ella i retornen poques files. Aquest criteri és el fil de les tres lliçons següents.

Ja tens el model mental complet:

• Un índex és una còpia ordenada d'una columna més un punter a la fila, com l'índex alfabètic d'un llibre, tret que n'hi pot haver molts, cal mantenir-los vius i els utilitza el planificador, no tu.
• Sense índex només hi ha Seq Scan, el cost del qual creix linealment. Un B-tree creix logarítmicament: amb un fanout d'unes 250 entrades per pàgina, 15 milions de files caben en 3 nivells, i els nivells alts viuen a la memòria cau.
• L'índex desa la clau i el ctid, així que normalment cal un segon accés a la taula: això separa l'Index Scan de l'Index Only Scan, i d'aquí neix el concepte d'índex cobridor.
• Un B-tree serveix per a igualtats, rangs, IN, prefixos LIKE 'abc%', ORDER BY, MIN i MAX; no serveix per a LIKE '%abc', per a funcions o càlculs sobre la columna filtrada ni per a <>. La columna ha d'aparèixer nua a la condició.
• BotigaVerda ja té onze índexs que ningú no va crear —nou de les PK i dos dels UNIQUE—, i li falten els de les seves onze claus foranes, perquè PostgreSQL no les indexa automàticament (MySQL/InnoDB sí). Això penalitza els JOIN i, molt en particular, els esborrats en cascada.
• Un índex es paga en espai i en escriptures més lentes, així que és una aposta que cal guanyar.

A la lliçó 08-02, Creació i gestió d'índexs, passes a l'acció: la sintaxi completa de CREATE INDEX, la variant CONCURRENTLY que no bloqueja la taula, els índexs compostos i la regla del prefix per l'esquerra que decideix en quin ordre posar les columnes, els índexs parcials que només indexen les files que t'interessen, els índexs sobre expressions que tanquen el problema de LOWER(email), i com llistar-los, mesurar-ne la mida i detectar els que no fa servir ningú. Començant, és clar, per les onze claus foranes que acabes de descobrir sense cobrir.