Aquesta és la lliçó que anunciem des del mòdul 5. Quan a 05-04 discutíem si desnormalitzar l'esquema de BiblioRed, la regla operativa era taxativa: els índexs són el primer que cal provar abans de desnormalitzar, i no es pot decidir una desnormalització sense haver llegit abans un pla d'execució. Aquí aprenem a fer les dues coses.

El problema concret ja té nom i té número. El llistat de préstecs vençuts per sucursal —el que el personal de taulell obre cada matí per trucar als socis que es retarden— triga catorze segons. Al portàtil de desenvolupament, amb 3.000 préstecs de prova, trigava 30 mil·lisegons. En producció, amb 2.841.077 files a prestecs, triga catorze segons i la persona que atén té un soci al davant mirant-la.

Catorze segons és un número interessant perquè és al pitjor lloc possible: és massa per treballar i massa poc perquè algú ho declari una avaria. Senzillament, "el sistema va lent". I d'aquella frase surten tots els redissenys innecessaris del món.

En aquesta lliçó veurem què és un índex i com aconsegueix convertir milions de comparacions en quatre lectures de disc; què costa un índex, perquè no són gratis; els tipus que existeixen i quan usar cadascun; què s'indexa i què no; com es llegeix un pla d'execució línia a línia; els antipatrons que anul·len un índex sense que ningú se n'adoni; i el cas complet d'aquells catorze segons, amb el seu diagnòstic, la seva solució i la seva mesura. Al final tindrem també l'ordre d'intervenció correcte davant d'una consulta lenta, que és el que tanca el cercle obert a 05-04.

1. Què és un índex: dues analogies que serveixen
2. Com funciona un B-tree
3. Els números: quantes lectures estalvia de debò
4. El cost d'un índex: espai, escriptures i planificació
5. CREATE INDEX: sintaxi, índexs únics i CONCURRENTLY
6. Índexs compostos i la regla del prefix més a l'esquerra
7. Índexs parcials
8. Índexs d'expressió
9. Panorama de tipus d'índex a PostgreSQL
10. Què s'indexa i què no
11. Llegir un pla d'execució: EXPLAIN i EXPLAIN ANALYZE
12. Els nodes que apareixen una vegada i una altra
13. Cost, estimacions i el senyal d'alarma
14. Les estadístiques i ANALYZE
15. Cas complet: els catorze segons del llistat de vençuts
16. Antipatrons que anul·len un índex
17. Com s'optimitza de debò: l'ordre d'intervenció
18. Manteniment i índexs fora de PostgreSQL

Definició. Un índex és una estructura de dades auxiliar, mantinguda automàticament pel gestor, que permet localitzar les files que compleixen una condició sense recórrer la taula sencera.

Dues analogies, i totes dues són d'una biblioteca, cosa que a BiblioRed ve molt a tomb.

La primera: l'índex d'un llibre. Per saber en quines pàgines es parla de "normalització" en un manual de 900 pàgines, no llegeixes les 900. Vas a l'índex analític del final, busques la paraula —que està ordenada alfabèticament, així que la trobes en segons— i llegeixes "normalització: 412, 418-431, 507". Tres dades que et porten directament al que busques.

Els tres elements d'aquella analogia són exactament els d'un índex de base de dades:

La segona: la mateixa biblioteca. Els 40.000 exemplars de BiblioRed estan col·locats a les prestatgeries en un ordre concret —per matèria i signatura—. Aquell ordre és un índex: permet trobar un llibre d'història sense recórrer les quatre sucursals. Però fixa't que només hi ha un ordre físic possible: els llibres no poden estar simultàniament ordenats per matèria, per autor i per any.

Per això les biblioteques tenen, a més, un catàleg: fitxes ordenades per autor, altres per títol, altres per matèria. Cada catàleg és un índex addicional que no canvia on és el llibre, només afegeix una manera més de trobar-lo. I cadascun s'ha d'actualitzar quan entra un exemplar nou.

Aquesta és exactament la situació d'una taula: les dades estan en un ordre (el que va imposar l'INSERT), i cada índex és un catàleg addicional que ofereix una altra manera d'arribar-hi, amb el seu cost d'espai i de manteniment.

El 95 % dels índexs que crearàs són B-tree (arbre B, en la seva variant B+). És el tipus per omissió a PostgreSQL i a pràcticament tots els gestors. Entendre com funciona explica gairebé tot el seu comportament.

Un B-tree és un arbre equilibrat en què:

• Cada node conté claus ordenades i punters.
• Els nodes interns només serveixen per dirigir la cerca: diuen "si busques alguna cosa menor que X, ves per aquí".
• Els nodes fulla contenen les claus i els punters a les files reals.
• Les fulles estan encadenades entre si, cosa que permet recorreguts per rang sense tornar a pujar.
• L'arbre està equilibrat: totes les fulles són a la mateixa profunditat, així que tota cerca costa el mateix.

Un índex sobre prestecs(data_devolucio_prevista), dibuixat a escala reduïda:

Per buscar data_devolucio_prevista = '2024-03-15':

1. A l'arrel: 2024-03-15 és entre 2023-04-01 i 2025-02-01 → baixa pel node intern del centre.
2. A l'intern: és entre 2023-11-01 i 2024-07-01 → baixa per la fulla corresponent.
3. A la fulla: busca la clau exacta i n'obté el ctid de la fila.
4. Llegeix aquella pàgina de la taula.

Quatre accessos. Sobre 2,8 milions de files.

Cada node d'un B-tree ocupa una pàgina de disc —8 KB a PostgreSQL— i en aquella pàgina hi caben moltes claus. Amb una data (8 bytes) més un punter (6 bytes), en 8 KB hi caben de l'ordre de 500 entrades per node. Aquell número s'anomena factor de ramificació.

Amb tres nivells s'adrecen 125 milions de files. Els 2,8 milions de prestecs caben còmodament en tres nivells, i a la pràctica els nivells superiors són permanentment a la memòria cau (el gestor de buffers de 01-04), així que la cerca real costa una o dues lectures de disc, no quatre.

La fórmula: el nombre de nivells creix amb el logaritme en base 500 del nombre de files. Multiplicar les dades per 500 afegeix un sol nivell. Aquesta és la raó per la qual un índex continua sent ràpid quan la taula creix: no escala amb el nombre de files, escala amb el seu logaritme.

Posem xifres a prestecs, amb 2.841.077 files i uns 120 bytes per fila.

La diferència no és d'un 20 %: és de quatre ordres de magnitud. Aquesta és la raó per la qual la primera reacció davant d'una consulta lenta ha de ser mirar si hi ha un índex, i no redissenyar l'esquema.

Però hi ha una lletra petita fonamental, i explica moltes decisions aparentment estranyes del planificador:

Un índex només compensa si la consulta torna una fracció petita de la taula. Si tornarà el 40 % de les files, és més ràpid recórrer la taula sencera seqüencialment que fer un milió de salts aleatoris.

La raó és física: llegir 52.736 pàgines seguides aprofita la lectura anticipada del sistema i del disc; llegir 400.000 pàgines saltejades no aprofita res. El punt d'equilibri a PostgreSQL sol estar entre el 5 % i el 10 % de la taula, i el decideix el planificador amb les estadístiques de l'apartat 14.

Per això veuràs plans amb Seq Scan sobre taules indexades i pensaràs que l'índex "no s'usa". Sovint és que el planificador ha calculat, amb raó, que no val la pena.

Aquí hi ha el motiu pel qual la resposta a "indexo aquesta columna?" no és sempre que sí.

Quatre índexs sobre una taula de 412 MB sumen 244 MB: més de la meitat de la mida de les dades. En una base de dades amb molts índexs és habitual que els índexs ocupin més que les taules. Això no només costa disc: costa memòria cau, que és un recurs molt més escàs, i cada índex hi competeix amb les dades.

Aquest és el cost que de debò importa.

Una mesura típica sobre una càrrega de 100.000 préstecs:

Més del triple. I prestecs és una taula que s'escriu constantment durant l'horari de taulell.

D'aquí surt la pràctica estàndard de les càrregues massives: eliminar els índexs, carregar, recrear-los. Recrear un índex de zero sobre dades ja presents és molt més ràpid que mantenir-lo fila a fila.

Cada índex addicional és una alternativa més que el planificador ha d'avaluar. Amb dos o tres índexs és imperceptible; amb quinze a la mateixa taula, el temps de planificació comença a notar-se en consultes que s'executen milers de vegades per minut.

Un índex no és gratis. Es crea per a una consulta concreta que s'executa amb prou freqüència per justificar el seu cost d'escriptura, i s'elimina quan aquella consulta deixa d'existir.

I per saber si sobra, PostgreSQL en porta el compte:

Aquell idx_prestecs_data_prestec amb idx_scan = 0 és un índex que només costa: 61 MB de disc, un arbre per mantenir a cada INSERT, i zero benefici. Candidat clar a DROP INDEX, prèvia comprovació que les estadístiques cobreixen un període representatiu (no l'eliminis l'endemà de reiniciar el servidor, ni sense mirar si l'usa l'informe anual).

La forma bàsica:

Convenció de noms: idx_<taula>_<columnes>. No és obligatòria —PostgreSQL genera un nom si no l'hi dónes— però un índex sense nom recognoscible és un índex que ningú no gosarà esborrar d'aquí a tres anys.

Aquí convé aclarir una relació que confon molta gent, i que enllaça amb les restriccions de 04-04:

Tota restricció UNIQUE i tota PRIMARY KEY s'implementen internament amb un índex únic. En declarar la restricció, l'índex es crea sol.

La recomanació: usa la restricció UNIQUE quan expressis una regla de negoci —queda documentada a l'esquema, i altres taules la poden referenciar— i l'índex únic només quan necessitis el que la restricció no pot donar: expressions o condicions parcials.

Crear un índex sobre una taula en producció té un problema seriós:

Aquesta instrucció pren un bloqueig SHARE sobre prestecs durant tota la seva execució. Amb 2,8 milions de files pot trigar un minut, i durant aquell minut ningú no pot escriure a la taula: els quatre taulells es queden parats. És el mecanisme de bloqueig de la lliçó 06-02 en acció.

L'alternativa:

Triga força més —fa dues passades sobre la taula— però no bloqueja les escriptures. Les seves condicions:

• No es pot executar dins d'una transacció explícita (recorda les excepcions al DDL transaccional de 06-01).
• Si falla a la meitat, deixa un índex invàlid que cal eliminar i refer. Detectar-los:

En producció, sempre CONCURRENTLY. En un entorn de desenvolupament o en una finestra de manteniment, la forma normal és més ràpida.

Un índex pot abastar diverses columnes:

I aquí apareix la regla que més confusió genera de tota la lliçó.

Regla del prefix més a l'esquerra. Un índex compost sobre (A, B, C) es pot usar per a consultes que filtrin per A, per A i B, o per A, B i C. No es pot usar eficaçment per a consultes que filtrin només per B, només per C, o per B i C.

Perquè l'índex està ordenat primer per A, i dins de cada valor d'A, per B. És exactament l'ordre d'una guia telefònica ordenada per cognom i després per nom:

• Buscar "Alsina, Marta" és immediat.
• Buscar tots els "Alsina" és immediat.
• Buscar totes les "Marta" de la guia obliga a recórrer-la sencera, perquè les Martes estan disperses per totes les pàgines.

Un matís honest: PostgreSQL pot usar un índex compost sense el prefix mitjançant un recorregut complet de l'índex (index scan sense condició d'inici), si l'índex és molt més petit que la taula. Però és un recurs desesperat i molt més lent que un índex adequat. La regla pràctica continua sent vàlida.

Exemple aplicat. Aquestes dues consultes de BiblioRed:

Un sol índex (soci_id, data_prestec) serveix per a totes dues: la igualtat primer, el rang després. Crear a més un índex sobre (soci_id) seria redundant i només costaria.

PostgreSQL permet afegir columnes a l'índex que no participen en la cerca però sí en el resultat:

Serveix per aconseguir un Index Only Scan (apartat 12): si totes les columnes que la consulta necessita són a l'índex, no cal llegir la taula. És una optimització notable en consultes molt repetides.

Índex parcial. Índex que només inclou les files que compleixen una condició WHERE. És més petit, més ràpid i més barat de mantenir.

És probablement la característica de PostgreSQL més infrautilitzada, i és exactament el que BiblioRed necessita.

Observa els números:

De 2,8 milions de préstecs, només 42.017 estan oberts: l'1,5 %. I totes les consultes del taulell —préstecs vigents d'un soci, vençuts per sucursal, avisos de devolució— filtren per data_devolucio IS NULL. Els altres 2,8 milions de files són història que ningú no consulta en el dia a dia.

Comparació de mides:

61 MB enfront de menys d'1 MB. Un índex 60 vegades menor que:

• cap sencer a la memòria cau i es consulta pràcticament sense tocar el disc;
• només s'actualitza quan es crea un préstec o es retorna, no a cada modificació de files històriques;
• i respon a la consulta calenta igual de bé.

PostgreSQL només usarà l'índex parcial si pot demostrar que la consulta implica la seva condició. La condició de l'índex ha d'aparèixer al WHERE de manera recognoscible:

Regla mental: si una consulta freqüent porta sempre el mateix filtre sobre un estat, una bandera o un IS NULL, aquell filtre hauria de ser a la definició de l'índex, no només a la consulta.

Un índex normal sobre titol no serveix per buscar LOWER(titol), perquè l'índex desa els títols tal com estan escrits i la consulta pregunta per una altra cosa. La solució és indexar l'expressió:

I la consulta ha d'usar exactament la mateixa expressió:

Altres casos habituals:

Compte amb els parèntesis dobles de l'últim exemple: quan l'expressió no és una crida a funció, cal envoltar-la en parèntesis propis.

I un advertiment: la funció ha de ser immutable (IMMUTABLE), és a dir, tornar sempre el mateix per a la mateixa entrada. Per això no es pot indexar now() ni una funció que depengui de la configuració regional de la sessió:

A l'apartat 16 veurem la cara fosca d'això: una funció aplicada al WHERE sobre una columna indexada anul·la l'índex, i és un dels antipatrons més freqüents.

B-tree resol gairebé tot, però convé saber què hi ha disponible i per a què:

El catàleg web de BiblioRed permet buscar per paraules del títol i del resum. Amb LIKE '%paraula%' no hi ha índex que valgui (apartat 16). Amb cerca de text complet, sí:

La columna generada manté el vector actualitzat sense activadors —és la tècnica de 04-04— i l'índex GIN el fa consultable en mil·lisegons.

Un índex BRIN no desa una entrada per fila, sinó un resum per bloc de pàgines: el valor mínim i el màxim de cada rang. És diminut, i funciona bé quan l'ordre físic de les files es correspon amb el valor de la columna, que és just el que passa amb una taula d'historial on les files s'insereixen cronològicament.

61 MB enfront de 48 kB. A canvi, és menys precís: descarta blocs sencers, no files, així que després cal filtrar. Per a l'informe anual de préstecs per trimestre és perfecte; per buscar un préstec concret, no.

Regla: si no saps quin triar, és B-tree. Els altres tipus responen a necessitats molt concretes i es reconeixen perquè B-tree no pot accelerar l'operador que necessites.

Això sorprèn gairebé tothom, i és una de les causes més freqüents de lentitud inexplicable:

PostgreSQL crea un índex automàticament per a la PRIMARY KEY i per a les restriccions UNIQUE, però NO per a les claus foranes. L'índex és al costat referenciat (la clau primària del pare), no al costat que referencia.

Conseqüències de no indexar prestecs.soci_id:

1. Cada JOIN amb socis recorre prestecs sencera.
2. Cada DELETE o UPDATE de la clau a socis recorre prestecs sencera per comprovar la integritat referencial de 02-06. Esborrar un soci en una taula de 2,8 milions de files pot trigar segons.

Trobar les claus foranes sense índex:

Quatre claus foranes sense índex a BiblioRed. Les quatre són candidates immediates.

Regla pràctica: indexa totes les claus foranes, llevat que hagis comprovat que la taula filla és petita i no s'usa en JOIN.

socis.actiu és un booleà. De 12.000 socis, 10.900 estan actius: el 91 %.

El planificador ignora l'índex, i fa bé: recuperar el 91 % de les files per índex significa saltar per gairebé tota la taula en ordre aleatori, cosa que és més lenta que llegir-la de cap a cap. L'índex existeix, ocupa espai, es manté a cada escriptura i no s'usa mai.

La mateixa columna amb la selectivitat invertida sí que serveix, i per això l'índex parcial és la resposta:

Aquesta segona forma és la bona. La columna actiu no aporta selectivitat, però restringeix l'índex a les files interessants, i les columnes indexades són les que de debò es busquen.

El pla d'execució és l'estratègia que l'optimitzador —aquella caixa del diagrama de 01-04— ha triat per respondre la consulta. Llegir-lo és l'habilitat central d'aquesta lliçó: sense ella, optimitzar és endevinar.

Avís important. EXPLAIN ANALYZE sobre un DELETE esborra les files. Per analitzar una instrucció d'escriptura sense efectes, embolcalla-la en una transacció i desfés-la:

BEGIN;
EXPLAIN ANALYZE DELETE FROM prestecs WHERE prestec_id = 88301;
ROLLBACK;

La forma completa que convé usar sempre:

Un pla és un arbre de nodes, i es llegeix de dins cap enfora i de baix a dalt:

• Cada -> marca un nivell d'imbricació.
• Els nodes més indentats s'executen primer.
• Cada node consumeix les files que produeixen els seus fills i lliura files al seu pare.
• La primera línia és l'últim pas: el que es torna al client.

El Bitmap Heap Scan mereix explicació perquè desconcerta. Quan la consulta tornarà, diguem, 40.000 files de 2,8 milions, un Index Scan faria 40.000 salts aleatoris pel disc. El bitmap resol el problema en dues fases: primer recull de l'índex totes les adreces, després les ordena per posició física i llegeix la taula de principi a fi visitant només les pàgines necessàries. És el punt intermedi entre índex i recorregut seqüencial, i veure'l sol significar que el planificador està fent el que toca.

El senyal d'alarma més comú: un Nested Loop el costat extern del qual torna moltes més files de les estimades. Si el planificador esperava 5 i n'hi ha 50.000, executarà 50.000 cerques en lloc de 5. És la causa número u de consultes que "de cop" passen de mil·lisegons a minuts.

Quan vegis external merge Disk: 84320kB, l'ordenació se n'ha anat a disc. Sovint s'arregla pujant work_mem per a aquella consulta:

Cada node porta dos blocs de números:

Tres advertiments imprescindibles sobre el cost:

1. El cost no són mil·lisegons. És una unitat arbitrària en què 1,0 equival, per conveni, a llegir una pàgina seqüencialment. Serveix per comparar plans entre si, no per predir temps.
2. Els costos són acumulatius: el d'un node inclou el dels seus fills. El cost total de la consulta és el de la primera línia.
3. Amb loops > 1, els temps i les files són PER ITERACIÓ. Un node amb actual time=0.012..0.014 rows=1 loops=42017 no va trigar 0,014 ms: va trigar uns 590 ms en total. És l'error de lectura més freqüent i el que fa que la gent busqui el problema al lloc equivocat.

Compara sempre rows= estimat amb rows= real a cada node. Una diferència de més d'un ordre de magnitud és el diagnòstic més valuós que dóna un pla d'execució.

Quan el planificador estima 5 files i n'hi ha 50.000, totes les seves decisions posteriors estan mal fonamentades: va triar Nested Loop perquè creia que iteraria cinc vegades. I el pla no és dolent per l'algorisme, és dolent per la informació.

El planificador no mira les dades: mira un resum estadístic de les dades. Si el resum està malament, el pla està malament.

Què desa PostgreSQL de cada columna:

El procés autovacuum executa ANALYZE automàticament quan una taula acumula prou canvis (per omissió, un 10 % de les files). Cal forçar-lo a mà en tres situacions:

1. Després d'una càrrega massiva. Les estadístiques són d'abans de carregar.
2. Després de crear un índex d'expressió. Necessita estadístiques pròpies de l'expressió.
3. Abans de mesurar un pla, per no diagnosticar sobre informació obsoleta.

Si una columna té una distribució molt irregular, se li pot demanar més detall:

El valor per omissió és 100 (100 valors freqüents i 100 cubetes d'histograma). Pujar-lo millora les estimacions i encareix una mica l'ANALYZE.

El planificador suposa que les columnes són independents. Quan no ho són, s'equivoca per molt. Exemple real de BiblioRed: exemplars.sucursal_id i exemplars.estat no són independents —la sucursal Est és petita i presta poc, així que gairebé tots els seus exemplars estan disponibles—.

És una eina poc coneguda i molt eficaç quan el símptoma és "l'estimat i el real se separen només quan filtro per dues columnes alhora".

Anem al problema real, de principi a fi.

Execution Time: 14025.663 ms — El número que cal baixar. 14 segons.

Seq Scan on prestecs p ... (actual time=0.048..13755.902 rows=42017 loops=1) — Aquí hi ha el 98 % del temps. El node triga 13,7 dels 14 segons ell sol. Tota la resta és soroll.

Rows Removed by Filter: 2799060 — La línia més eloqüent del pla. PostgreSQL ha llegit 2.841.077 files i n'ha descartat 2.799.060. Ha fet el 98,5 % de la feina per no res.

Buffers: shared hit=1284 read=51452 — 51.452 pàgines llegides de disc (read) i només 1.284 trobades a la memòria cau (hit). Són 402 MB de disc per una consulta que torna 312 files.

rows=41960 estimat enfront de rows=42017 real — Aquí no hi ha problema d'estadístiques: l'estimació és excel·lent. El planificador sabia perfectament el que feia; va triar Seq Scan perquè no tenia cap alternativa. No hi ha cap índex que serveixi.

Seq Scan on exemplars e ... Rows Removed by Filter: 30002 — El segon problema, molt menor: recorre els 40.000 exemplars per quedar-se amb els 9.998 de la sucursal 1. Són 29 ms, no és el drama, però també hi falta un índex.

Sort Method: quicksort Memory: 76kB — L'ordenació és irrellevant: 312 files a memòria.

Hash Join — Correctes. Amb 42.017 files per un costat i 9.998 per l'altre, sense índexs, el hash és l'elecció adequada.

Un diagnòstic s'escriu en una frase:

La consulta recorre els 2,8 milions de préstecs per quedar-se amb 42.017 oberts i vençuts —l'1,5 %—, perquè no existeix cap índex sobre data_devolucio ni sobre data_devolucio_prevista. Secundàriament, recorre els 40.000 exemplars per falta d'índex a sucursal_id.

Fixa't en el que no és el problema: no són els quatre JOIN, no és l'esquema normalitzat, no és l'ORDER BY, no és "la taula és molt gran". És un índex que falta.

Per què parcial? Perquè les consultes del taulell sempre porten data_devolucio IS NULL, i així l'índex passa de 61 MB a menys d'1 MB, cap sencer a memòria i només es toca en prestar i en retornar, no a cada modificació de les files històriques.

Catorze segons convertits en quaranta mil·lisegons. Sense tocar l'esquema. Sense desnormalitzar. Sense una sola taula nova. Dos CREATE INDEX.

Dues observacions sobre el pla nou:

• El Hash Join s'ha convertit en Nested Loop. En abaratir-se l'accés a prestecs, el planificador ha canviat d'estratègia: ara recorre els 42.017 préstecs oberts i, per cadascun, busca el seu exemplar per clau primària. És un exemple perfecte que crear un índex no només accelera un node: canvia el pla sencer.
• loops=42017 al node d'exemplars. Recorda l'apartat 13: aquell actual time=0.000..0.000 és per iteració. El node s'executa 42.017 vegades, i la seva contribució real són uns 17 ms dels 39. Està bé, però és el lloc on miraríem si calgués continuar baixant.

Suposem que el taulell necessités baixar de 40 ms —cosa dubtosa, però serveix per tancar el cercle de 05-04—. El pas següent no seria crear una taula de resum. Seria observar que la consulta filtra per sucursal i que la sucursal és a exemplars, no a prestecs, cosa que obliga a recórrer els 42.017 préstecs de les quatre sucursals per quedar-se amb els d'una.

La solució seria duplicar sucursal_id a prestecs —la tècnica 3 de 05-04, "duplicar un atribut per evitar un JOIN"— i indexar (sucursal_id, data_devolucio_prevista) WHERE data_devolucio IS NULL. Això portaria la consulta a l'ordre del mil·lisegon.

I fixa't en l'ordre en què hi hem arribat: mesurar, índex, mesurar altra vegada, i només aleshores plantejar tocar l'esquema, amb el número exacte del que es guanya. Aquest és exactament el procediment que 05-04 exigia i que l'apartat següent formalitza.

Existeix l'índex, és el correcte, i tot i així el pla mostra Seq Scan. Gairebé sempre és un d'aquests cinc.

L'índex desa dates; la consulta pregunta per anys. Són coses diferents.

L'alternativa, si la reescriptura no fos possible, és un índex d'expressió sobre extract(year FROM data_prestec). Però reescriure és gairebé sempre millor: el rang serveix a més per a qualsevol altre període.

Variant molt freqüent del mateix error:

Nota el matís: aquí la funció no desapareix, però coincideix amb la de l'índex, i per això funciona.

Un B-tree ordena pel principi de la cadena. Buscar pel que hi ha al mig obliga a mirar-ho tot, igual que buscar en un diccionari totes les paraules que continguin "apa".

Un detall important per al català: perquè LIKE 'El mapa%' usi l'índex quan la base de dades no està en la col·lació C, cal crear l'índex amb la classe d'operadors adequada:

És el mateix parany de l'antipatró 1 disfressat: soci_id::text és una funció sobre la columna.

Passa molt amb controladors mal configurats que envien tots els paràmetres com a text, i amb columnes que desen números en VARCHAR —motiu addicional per triar bé els tipus, com deia 04-04—.

PostgreSQL de vegades resol l'OR amb un BitmapOr sobre dos índexs, i aleshores no cal reescriure. Quan no ho fa, el UNION és la sortida.

Cas relacionat, i molt habitual:

44 pàgines enfront de 4, i Heap Fetches: 0 confirma que no s'ha tocat la taula. A més, SELECT * transfereix columnes que ningú no usa, trenca les aplicacions quan algú afegeix una columna, i fa il·legible què necessita realment cada consulta.

Aquí tanquem el cercle obert a 05-04.

No s'optimitza el que no s'ha mesurat. Trobar la consulta culpable, no la sospitosa. L'eina és l'extensió pg_stat_statements:

Mira la taula anterior amb atenció, perquè conté la lliçó sencera d'aquest apartat:

La segona fila és la que sorprèn. Una consulta d'1,36 ms sembla perfecta, però s'executa 884.102 vegades i suma vint minuts de servidor. Baixar-la a 0,4 ms estalviaria més que optimitzar l'informe trimestral quaranta vegades.

I la tercera ensenya el contrari: un informe de dos segons sembla un escàndol, però s'executa 44 vegades l'any. Optimitzar-lo és temps perdut, per molt lleig que sigui el seu SQL.

La mètrica que decideix és total_exec_time, no mean_exec_time. Cost total = cost unitari × freqüència.

De menys invasiva a més. No passis al següent esglaó sense haver esgotat l'anterior i sense haver mesurat.

Els quatre primers esglaons no toquen les dades, no poden introduir inconsistència i es desfan en un minut. Del cinquè endavant, cadascun afegeix una obligació permanent de manteniment.

I el cas d'aquesta lliçó ho confirma: catorze segons resolts a l'esglaó 3, amb dues instruccions reversibles. La temptació de saltar directament al 6 —"fem una taula de préstecs vençuts que s'actualitzi cada nit"— hauria costat un activador, un procés nocturn, un risc de descompensació, dades amb hores d'antiguitat i una discussió eterna sobre per què el llistat no inclou el préstec que acaba de vèncer. Tot això per ser més lent que un índex parcial de 992 kB.

Dues tècniques que de vegades es proposen com a solució a una consulta lenta i que gairebé mai no ho són:

• El particionat divideix una taula gran en fragments per rang o per llista. Ajuda amb el manteniment (esborrar un any sencer és eliminar una partició) i amb consultes que filtren per la clau de partició. No substitueix un índex.
• La replicació reparteix la càrrega de lectura entre diversos servidors, i és la tècnica que vam veure a 03-01. No fa més ràpida una consulta: permet executar més consultes alhora. Enviar l'informe mensual a una rèplica de només lectura és una idea excel·lent; no farà que trigui menys.

Cap de les dues no arregla una consulta lenta per falta d'índex. Només multipliquen el maquinari necessari per continuar fent-ho malament.

Amb el MVCC de 06-02, els índexs també acumulen entrades mortes i s'inflen. Un índex inflat ocupa més del que hauria i la seva cerca toca més pàgines.

Estimar el problema:

Reconstruir:

El CONCURRENTLY és essencial: sense ell, REINDEX bloqueja les escriptures de la taula, amb l'efecte de 06-02 sobre els quatre taulells.

Compte: reconstruir índexs no és una tasca rutinària. Es fa quan s'ha comprovat que hi ha inflament, no "per si de cas".

El concepte és idèntic; canvia la sintaxi.

I l'equivalent d'EXPLAIN ANALYZE:

El que cal mirar és la mateixa idea que a PostgreSQL: COLLSCAN en lloc de IXSCAN és el Seq Scan de MongoDB, i totalDocsExamined molt més gran que nReturned és l'equivalent de Rows Removed by Filter.

La conclusió important: els índexs no són una peculiaritat relacional. Són la resposta universal al problema de trobar pocs elements entre molts, i tots els sistemes de dades, sense excepció, en tenen la seva versió.

Indexar-ho tot "per si de cas". Cada índex costa espai, escriptures, memòria cau i temps de planificació. Revisa idx_scan a pg_stat_user_indexes i elimina els que porten mesos sense usar-se.

No indexar les claus foranes. PostgreSQL no ho fa per tu. És la causa més freqüent de JOIN lents i d'esborrats que triguen segons. Executa la consulta de detecció de l'apartat 10 sobre la teva base de dades avui mateix.

Crear l'índex en producció sense CONCURRENTLY. Bloqueja les escriptures durant tota la creació. En una taula gran, és una aturada de servei autoinfligida.

Confondre cost amb mil·lisegons. El cost és una unitat interna per comparar plans. El temps és a actual time, i només amb ANALYZE.

Oblidar que amb loops > 1 els temps són per iteració. Multiplica sempre per loops abans de decidir que un node és innocent.

Optimitzar la consulta més lletja en lloc de la més costosa. Ordena per total_exec_time a pg_stat_statements i ataca el de dalt, encara que el seu SQL sigui impecable.

Envoltar la columna en una funció i esperar que l'índex funcioni. WHERE extract(year FROM data) = 2026 no usa l'índex sobre data. Reescriu-ho com a rang.

Usar EXPLAIN ANALYZE sobre un UPDATE o un DELETE en producció sense transacció. L'executa de debò. BEGIN; ... ROLLBACK;.

Mesurar amb dades de desenvolupament. Els 3.000 préstecs del teu portàtil caben a memòria i fan que tots els plans semblin bons. Els plans només són significatius amb volum i estadístiques realistes.

Crear un índex i no mesurar després. De vegades el planificador continua sense usar-lo —per selectivitat, per tipus o per estadístiques— i et quedes amb el cost sense el benefici. Torna a executar EXPLAIN ANALYZE sempre.

Consell final: escriu el número. "Abans 14.025 ms, després 39 ms, amb idx_prestecs_oberts." Aquella frase al registre de canvis val més que qualsevol explicació, permet comprovar d'aquí a un any si l'índex encara fa falta, i és l'única cosa que converteix una intuïció en una decisió d'enginyeria.

Aquestes tres consultes són les més executades del catàleg web i del taulell de BiblioRed. Per a cadascuna, indica quin índex crearies, per què en aquell ordre de columnes, i si seria parcial o no.

Interpreta aquest pla d'execució. Indica: quin és el node problemàtic, quant temps real consumeix, quin és el diagnòstic i quina intervenció proposaries (amb l'esglaó de l'apartat 17 al qual correspon).

Aquestes tres consultes de BiblioRed no usen cap índex tot i que existeixen els índexs adequats. Identifica l'antipatró de cadascuna i reescriu-la. Els índexs disponibles són: prestecs(data_prestec), socis(lower(email)), materials(titol text_pattern_ops) i materials USING gin (cerca).

Solució 1

(a) Historial de préstecs d'un soci

• Ordre de columnes: soci_id primer perquè és la condició d'igualtat i és molt selectiva (11.842 valors diferents sobre 2,8 milions de files). data_prestec després perquè intervé a l'ORDER BY.
• DESC a l'índex: permet que l'ORDER BY ... DESC es resolgui recorrent l'índex en el seu ordre natural, eliminant el node Sort. Amb LIMIT 20, el gestor llegeix 20 entrades i para.
• No parcial: és un historial, i consulta explícitament préstecs ja retornats. Restringir-lo als oberts trencaria el cas d'ús.

Verificació esperada:

Sense node Sort: és el senyal que l'ordre de l'índex s'ha aprofitat.

(b) Exemplars disponibles d'un material en una sucursal

• Ordre: material_id primer per ser molt més selectiu (milers de materials enfront de quatre sucursals). Amb només quatre sucursals, sucursal_id gairebé no descarta files i no ha d'anar davant.
• Parcial sobre estat = 'disponible': és la condició fixa de la consulta calenta del catàleg web, i estat és de baixa cardinalitat, així que com a columna indexada seria inútil (apartat 10). Com a condició de l'índex és perfecta: el redueix als exemplars realment prestables i evita mantenir-lo quan canvien els estats dels altres.
• No cal incloure estat entre les columnes: la condició de l'índex ja ho garanteix.

(c) Multes pendents d'un soci

• Ordre: soci_id (igualtat, selectiu) i data_emissio (per a l'ORDER BY).
• Parcial: de les 48.000 multes històriques només unes 300 estan pendents. L'índex passa de megabytes a kilobytes i només es toca en emetre o cobrar una multa.
• S'hi podria afegir INCLUDE (motiu, import) per aconseguir un Index Only Scan, ja que la consulta només demana aquelles columnes. Amb 300 files el guany és marginal, però és el raonament correcte.

Solució 2

Node problemàtic: l'Index Scan using idx_prestecs_soci on prestecs p.

Temps real consumit: aquí hi ha el parany. El node mostra actual time=224.402..224.622 amb loops=41. Aquells 224 ms són per iteració, així que el total és 41 × 224,6 ≈ 9.209 ms, és a dir, pràcticament els 9.215 ms de tota la consulta. Qui llegeixi "224 ms" i ho doni per acceptable buscarà el problema on no és.

Diagnòstic: l'índex idx_prestecs_soci s'està usant per a la condició d'unió, però el filtre data_devolucio IS NULL s'aplica després, sobre les files ja recuperades de la taula. La línia Rows Removed by Filter: 218 ho diu: per cadascun dels 41 socis es llegeixen 219 préstecs de la taula per quedar-se amb 1. Són 8.979 accessos a pàgines, la majoria a disc.

Hi ha un segon problema, menor: el Seq Scan on socis amb lower(cognoms) recorre els 12.000 socis (4 ms). No és el drama, però delata que falta un índex d'expressió.

Intervenció proposada, esglaó 3 de l'apartat 17 (crear un índex):

Amb l'índex parcial, cada iteració del bucle torna directament el préstec obert sense llegir els 218 retornats. El node passaria de 224 ms a microsegons, i la consulta sencera a l'ordre d'un mil·lisegon.

Observació addicional: també s'aprecia que l'estimació (rows=12) es queda curta enfront de la realitat (rows=41) al Seq Scan on socis. No és greu aquí, però si la diferència creixés, el Nested Loop deixaria de ser l'elecció adequada. Un ANALYZE després de crear l'índex d'expressió millorarà també aquella estimació.

Solució 3

(a) Funció sobre la columna (antipatró 1)

to_char(data_prestec, 'YYYY-MM') converteix la data en text, així que l'índex sobre data_prestec no pot intervenir.

Nota sobre el límit superior: s'usa < '2026-08-01' i no <= '2026-07-31'. Si la columna fos TIMESTAMP en lloc de DATE, <= '2026-07-31' deixaria fora tot l'últim dia a partir de les 00:00:01. El rang mig obert és correcte en tots dos casos, i és un costum que evita errors silenciosos.

(b) Índex d'expressió amb expressió diferent (variant de l'antipatró 1)

L'índex és sobre lower(email), però la consulta compara email tal qual, i a més amb el valor en majúscules. No coincideixen.

O, millor encara, normalitzant el valor a l'aplicació abans d'enviar-lo:

L'expressió del WHERE ha de ser idèntica a la de l'índex. És la regla que fa funcionar els índexs d'expressió, i la que els trenca quan s'oblida.

(c) LIKE amb comodí inicial (antipatró 2)

L'índex text_pattern_ops serveix per a prefixos (LIKE 'temps%'), no per a subcadenes.

Avantatge afegit: la cerca de text complet aplica lematització en català, així que "mapa" troba també "mapes", i no troba falsos positius com "contratemps" que sí que retornaria LIKE '%temps%'.

Si de debò calgués la subcadena literal, l'alternativa és un índex de trigrames:

Els catorze segons ja no existeixen. I l'important no és que ara siguin quaranta mil·lisegons, sinó com hi hem arribat: mesurant, llegint un pla d'execució, trobant la línia que deia Rows Removed by Filter: 2799060, i creant dos índexs reversibles. Sense tocar l'esquema, sense desnormalitzar, sense una taula nova i sense una discussió d'arquitectura.

Hem entès el mecanisme des de dins. Un B-tree amb un factor de ramificació d'unes 500 entrades per node adreça 125 milions de files en tres nivells, i aquesta és la raó matemàtica que un índex continuï sent ràpid quan la taula creix: no escala amb el nombre de files, escala amb el seu logaritme. També hem vist l'altra cara, que s'oblida més: un índex costa espai, costa memòria cau, triplica el temps d'una càrrega massiva i, si ningú no l'usa, només costa. Per això idx_scan = 0 a pg_stat_user_indexes és una invitació a DROP INDEX.

Hem recorregut les eines: els índexs únics i la seva relació exacta amb les restriccions UNIQUE de 04-04; els compostos i la regla del prefix més a l'esquerra, que s'entén d'una vegada per sempre amb la guia ordenada per cognom i nom; els parcials, que a BiblioRed van convertir 61 MB en 992 kB perquè només l'1,5 % dels préstecs està obert; els d'expressió, amb la condició que el WHERE repeteixi l'expressió exacta; i el panorama de GIN per al cercador del catàleg, GiST per als solapaments d'esdeveniments que ja coneixíem de 04-04, i BRIN per a l'historial ordenat per data, amb els seus 48 kB enfront de 61 MB.

Hem après a llegir un pla de baix a dalt i de dins cap enfora; a distingir Seq Scan d'Index Scan, d'Index Only Scan i de Bitmap Heap Scan; a reconèixer quan un Nested Loop és una bona idea i quan és una catàstrofe; a no confondre el cost amb els mil·lisegons; a multiplicar per loops abans d'absoldre un node; i sobretot a mirar el senyal d'alarma més valuós de tots: la distància entre les files estimades i les reals, que gairebé sempre apunta a estadístiques velles o a columnes correlacionades.

I hem formalitzat el mètode. Mesurar amb pg_stat_statements i atacar la consulta amb més temps total, no la de pitjor aspecte —aquella consulta d'1,36 ms que s'executa 884.102 vegades costa més que l'informe trimestral de dos segons—. Després, l'ordre d'intervenció: reescriure → estadístiques → índex → configuració → esquema → desnormalitzar → memòria cau, sense saltar-se esglaons i mesurant entre l'un i l'altre. Els quatre primers no poden espatllar les dades; del cinquè endavant, cadascun afegeix una obligació permanent. Aquest és exactament el compromís que vam adquirir a 05-04, i ara ja tenim les eines per complir-lo.

Queda una última cosa, i és la que separa una base de dades d'un accident esperant a passar.

El sistema de BiblioRed és ara correcte —l'esquema està normalitzat i les transaccions garanteixen que les operacions passen senceres— i és ràpid. Però continua havent-hi dues preguntes de la regidoria de Vallmar sense respondre des del primer dia d'aquest mòdul: qui pot consultar els telèfons i els correus dels 12.000 socis, i què passaria exactament si el disc morís aquesta nit.

Cap de les dues no s'arregla amb un índex. La lliçó 06-04, Seguretat, Permisos i Còpies de Seguretat, tanca el mòdul i el bloc teòric del curs: l'autenticació i els rols de PostgreSQL, amb pg_hba.conf i per què el mètode trust no ha de sortir del teu portàtil; l'autorització amb GRANT i REVOKE, i el principi de mínim privilegi aplicat a tres rols concrets de BiblioRed —taulell, direcció i aplicació web—; les vistes i la seguretat a nivell de fila perquè cada sucursal vegi només el seu; la injecció SQL, com es produeix al cercador del catàleg i per què les consultes parametritzades són l'única defensa que funciona; el xifratge en trànsit i en repòs, i el tractament correcte de les contrasenyes; les dades personals dels socis, amb el que la tècnica pot aportar i el que correspon a un professional de compliance; i el bloc de còpies de seguretat, on el registre WAL que vam estudiar a 06-01 reapareixerà convertit en l'eina que permet recuperar la base de dades a l'instant anterior al desastre.