Tot el que portes llegit al mòdul són regles, i en rendiment les regles s'equivoquen. De debò fa servir l'índex aquella consulta? De debò IN (SELECT ...) es converteix en un semi-join, com va prometre 07-05? De debò el problema són les estadístiques? Hi ha una sola manera honesta de respondre, i és l'eina que converteix aquest mòdul sencer en un mètode: EXPLAIN. Aquí aprendràs a llegir un pla d'execució node a node, a distingir el pla estimat del real, a reconèixer el senyal que delata unes estadístiques desfasades, i a interpretar els quinze nodes que apareixen al 95 % dels plans. I faràs la demostració central del mòdul: construiràs una taula de proves de dos milions de files —perquè amb els 20 productes de BotigaVerda no hi ha res a demostrar— i veuràs amb els teus ulls la diferència entre recórrer dos milions de files i saltar directament a deu.

1. EXPLAIN i EXPLAIN ANALYZE
2. Com es llegeix un pla
3. cost, rows, width, actual time i loops
4. El senyal més útil: estimat enfront de real
5. Els nodes que més veuràs
6. Opcions útils d'EXPLAIN
7. El banc de proves: dos milions de files
8. La demostració: sense índex i amb índex
9. I a la BotigaVerda real: per què s'ignora l'índex
10. Verificant la promesa de 07-05
11. Mesurar en altres motors i eines de visualització
12. Manteniment: VACUUM, ANALYZE i el bloat
13. Un mètode en sis passos
14. Errors habituals i consells
15. Exercicis
16. Conclusió del mòdul

El primer costa microsegons i et diu quin camí triaria; el segon costa el que costi la consulta i et diu què va passar de debò.

⚠️ L'avís imprescindible. EXPLAIN ANALYZE executa la consulta de debò. Amb un SELECT és inofensiu; amb un UPDATE, un DELETE o un INSERT, modifica les dades. La manera segura d'analitzar-los és embolicar-los en una transacció que es desfà:

El pla es mostra, les files s'esborren… i el ROLLBACK ho desfà tot. BEGIN, COMMIT i ROLLBACK són el mòdul 9; aquí n'hi ha prou de fer servir aquest patró com una precaució obligatòria. És la mateixa xarxa de seguretat que ja vas veure a 05-04 abans d'un DELETE sense WHERE.

Un pla és un arbre de nodes: cadascun rep files dels seus fills, hi fa alguna cosa i les passa al seu pare. La sortida de text ho representa amb sagnat i fletxes ->, i es llegeix d'una manera concreta:

De dins cap a fora i de baix cap a dalt. El node més sagnat s'executa primer; el de la primera línia, l'últim, i és el que produeix el resultat final.

En text, aquest mateix arbre s'imprimeix al revés: Limit a dalt de tot, els dos Seq Scan al fons. Les dues preguntes que respon la forma de l'arbre són sempre les mateixes: per on entra el motor a les dades? (els nodes de les fulles) i com les combina? (els nodes d'unió).

Cada línia d'un pla porta els seus números. Amb EXPLAIN a seques només hi ha estimacions; amb ANALYZE s'hi afegeix la segona meitat entre parèntesis:

El cost d'arrencada distingeix dues famílies de nodes: els que emeten files a mesura que les llegeixen (Seq Scan, Index Scan, Nested Loop) tenen arrencada ≈ 0; els que necessiten totes les files abans de retornar la primera (Sort, Hash, HashAggregate) el tenen alt. Això importa molt amb LIMIT.

El parany de loops. actual time i rows són mitjanes per execució, no totals. Un node amb actual time=0.05..0.08 rows=3 loops=20000 no va trigar 0,08 ms: va trigar 0,08 × 20.000 = 1,6 segons, i va produir 60.000 files. Multiplica sempre per loops abans de decidir on és el problema.

Al final del pla apareixen Planning Time (el que va trigar a decidir el pla) i Execution Time (el que va trigar a executar-lo). Si són comparables, tens una consulta trivial executada moltíssimes vegades: el cas de les sentències preparades i, sovint, de l'N+1 de 08-04.

Si només t'endús una cosa d'aquesta lliçó, que sigui aquesta: compara rows= estimades amb rows= reals a cada node. Una divergència gran —d'un ordre de magnitud o més— és la causa arrel de gairebé tots els plans dolents.

El planificador esperava 42 files i en van sortir 284.561. Amb 42 files, encadenar un Nested Loop és la decisió perfecta; amb 284.561, és una catàstrofe. El pla no està mal triat: està ben triat per a una realitat que no existeix.

Les quatre causes, en ordre de freqüència: estadístiques desfasades després d'una càrrega o un creixement, que s'arreglen amb ANALYZE taula;; columnes correlacionades que el planificador suposa independents, amb CREATE STATISTICS; un predicat que no sap estimar (expressió complexa, funció pròpia), que cal reescriure o indexar com a expressió; i una mostra massa petita per a una distribució molt desigual, amb ALTER TABLE ... SET STATISTICS. Les quatre es desenvolupen a 08-04. Quan estimat i real s'assemblen, el pla acostuma a ser el millor disponible: si tot i així va lent, el problema és d'índexs o de volum, no del planificador.

Dues línies auxiliars que convé mirar sempre: Filter: amb el seu Rows Removed by Filter: (files llegides i llençades — si són milions, falta un índex o el filtre hauria d'anar abans) i Index Cond: (la part de la condició que sí que va resoldre l'índex; el que quedi a Filter és el que es va haver de comprovar fila a fila).

Van entre parèntesis i separades per comes: EXPLAIN (ANALYZE, BUFFERS, VERBOSE) SELECT ...;.

Per què BUFFERS importa tant: el temps depèn de si la màquina està ocupada, de si la dada era a la memòria cau i de què més s'executa alhora. Els blocs llegits, no. Un pla que llegeix 16.250 blocs en llegirà 16.250 avui, demà i al portàtil del teu company. En una màquina compartida —un servidor d'integració, un contenidor, el núvol— els blocs són l'única mètrica reproduïble que tens.

Aquí cal ser honest: amb BotigaVerda no es pot demostrar res d'això. Vint productes i quaranta-set línies caben en una pàgina de disc, i el planificador farà Seq Scan sempre i amb tota la raó. Ensenyar-te un pla inventat sobre aquestes taules seria mentir-te. Així que construirem una taula gran i reproduïble amb generate_series.

⚠️ BANC DE PROVES DEL MÒDUL 8. La taula comandes_grans no forma part de l'esquema canònic de BotigaVerda (01-06). No té claus foranes, no està relacionada amb cap altra taula i cap mòdul posterior no la fa servir. Quan acabis la lliçó, esborra-la: DROP TABLE comandes_grans;.

(Una execució d'exemple. La mida exacta depèn de la versió i de l'alineació dels tipus; l'ordre de magnitud, no.) Enfront de l'única pàgina de productes, aquí n'hi ha setze mil dues-centes cinquanta. Ara sí que hi ha alguna cosa a optimitzar.

Comencem sense índex: client_id no en té cap — només en té id, per la seva clau primària.

(Una execució d'exemple: els mil·lisegons i els costos depenen de la teva màquina. El que cal llegir són les proporcions i els tipus de node.) Tres coses que aquest pla crida: Parallel Seq Scan, perquè no hi ha índex i cal recórrer la taula sencera —és tan gran que PostgreSQL reparteix la feina entre tres processos i els recull amb Gather—; Rows Removed by Filter: 666663 per treballador, és a dir dos milions de files llegides i llençades per quedar-se'n amb deu; i Buffers: shared hit=16250, les 16.250 pàgines de la taula, totes.

Ara l'índex, i la mateixa consulta:

La comparació, que és la raó de ser del mòdul sencer:

I fixa't en Index Cond enfront de Filter: al segon pla no hi ha Filter. L'índex no va filtrar després de llegir: es va posicionar directament a les deu files. Aquesta és la diferència entre buscar i descartar.

Amb un filtre que retorna força files disperses, el planificador tria un camí intermedi:

Es llegeix de baix a dalt: el Bitmap Index Scan recorre l'índex i construeix un mapa de bits dels blocs que contenen coincidències; el Bitmap Heap Scan els llegeix en ordre físic, una sola vegada cadascun. Amb 2.010 files repartides per gairebé 2.000 blocs diferents, un Index Scan faria 2.010 salts aleatoris; el bitmap els converteix en un recorregut gairebé seqüencial de 1.988 blocs. És la resposta del planificador a "moltes files, però no tota la taula".

Torna al món de les 20 files i crea l'índex més raonable del catàleg, sobre productes.preu:

Seq Scan, amb l'índex acabat de crear i sense estrenar. I el planificador té tota la raó. Mira'n el cost: 1,25. Es descompon així: 1 pàgina × 1,0 (llegir l'únic bloc de la taula) + 20 files × 0,01 (processar-ne cadascuna) + 20 × 0,0025 (avaluar el filtre) = 1,25. No hi ha res més barat que això, perquè la taula sencera és un sol accés.

El podem obligar a fer servir l'índex per veure què hauria passat:

Cost 12,35 enfront d'1,25: deu vegades més car. I el temps real, el doble. El motiu és exactament el de 08-01: fer servir l'índex obliga a llegir la pàgina de metadades, baixar per l'arbre, obtenir set ctid i tornar a llegir la mateixa pàgina de la taula que el Seq Scan hauria llegit d'una tirada. Tres o quatre accessos per fer la feina d'un. Tres conclusions, que tanquen la primera lliçó del mòdul: un índex sobre una taula que cap en una pàgina no compensa mai, i el planificador ho sap; enable_seqscan = off és una eina de diagnòstic, no una solució, i serveix per respondre "què hauria fet amb l'índex?" i res més; i els índexs que vas crear a 08-02 no acceleren res avui — són correctes, estan ben dissenyats i serien decisius amb volum real, però amb 20 files no hi ha cap problema a resoldre.

07-05 va afirmar que PostgreSQL converteix IN (SELECT ...) en un semi-join, i que per això el seu rendiment és equivalent al d'un JOIN. Comprovem-ho:

Hash Semi Join. Vas escriure una subconsulta i el motor va executar un join — un join especial que s'atura a la primera coincidència de cada fila esquerra, i per això retorna 12 clients i no 20 files. La promesa era certa, i ara no t'ho creus perquè ho vas llegir: ho has vist. Prova de canviar IN per NOT IN i veuràs que el Semi Join desapareix i apareix un filtre amb una subconsulta executada a part: la confirmació visual de per què 07-05 desaconsellava NOT IN.

Nota de dialecte: els conceptes viatgen (recorregut seqüencial, accés per índex, tipus de join, estimat enfront de real), però el vocabulari i les unitats no. El cost de PostgreSQL no és comparable amb el de MySQL ni amb el d'Oracle. I hi ha diferències de fons: SQL Server i MySQL/InnoDB fan servir índexs agrupats, on la taula és l'índex primari, així que el seu equivalent de l'Index Scan no necessita el segon accés del qual parlava 08-01. El SET STATISTICS IO ON de SQL Server és el parent proper de BUFFERS, i per la mateixa raó: compta lectures lògiques, que són reproduïbles.

I quatre eines que fan llegible un pla de cent línies: explain.dalibo.com, on enganxes el pla (millor en FORMAT JSON) i el dibuixa com un arbre ressaltant el node més car i les estimacions errònies; pev2, el mateix visualitzador integrable a les teves pròpies eines; auto_explain, un mòdul del servidor que registra automàticament el pla de tota consulta que superi un llindar de temps —imprescindible per al que només falla en producció—; i pg_stat_statements, el rànquing de quines consultes analitzar (08-03).

Hi ha una part del rendiment que no depèn de les teves consultes. A PostgreSQL, un UPDATE no modifica la fila: escriu una versió nova i marca la vella com a morta. Un DELETE tampoc no esborra: marca. Les versions mortes s'acumulen i engreixen taules i índexs sense aportar res: és el bloat. Els seus efectes són mesurables: una taula amb un 60 % d'espai mort ocupa el triple de pàgines de les necessàries, així que cada Seq Scan en llegeix el triple, la memòria cau ret un terç i els Index Only Scan perden eficàcia (pugen els Heap Fetches).

En condicions normals, autovacuum se n'encarrega sol. Es converteix en un problema quan una taula rep moltíssimes actualitzacions, o quan una transacció fa hores que és oberta i impedeix netejar files mortes que encara podria necessitar. I aquí arribem al límit d'aquest mòdul: el perquè de tot això —per què el motor desa diverses versions de cada fila i per què una transacció oberta bloqueja la neteja— és el model MVCC, i s'estudia al mòdul 9. De moment queda't amb l'operatiu: ANALYZE després de qualsevol càrrega massiva, i n_dead_tup disparat com a senyal d'alarma.

Això és el que de debò cal endur-se del mòdul. Davant d'una consulta lenta:

1. Reprodueix i mesura. Aconsegueix la consulta exacta amb els seus paràmetres reals i executa-la amb EXPLAIN (ANALYZE, BUFFERS). Sense mesura no hi ha diagnòstic.
2. Localitza el node culpable. Busca el node amb més actual time multiplicat per loops, i el que més blocs llegeix. Gairebé sempre és un de sol i és a baix de tot.
3. Compara estimat amb real. Si rows estimades i reals divergeixen molt, el problema són les estadístiques: ANALYZE, i torna al pas 1. No continuïs optimitzant sobre un pla basat en dades falses.
4. Mira què està llegint de més. Seq Scan sobre una taula gran amb un Rows Removed by Filter enorme = falta un índex o la condició no és sargable. Sort ... Disk = falta un índex d'ordenació o memòria. Nested Loop amb milers de loops = mala estimació a la branca esquerra.
5. Aplica UNA sola intervenció i torna a mesurar. Reescriure (08-04) abans que indexar (08-02); indexar abans que canviar l'arquitectura. Un canvi cada vegada, o no sabràs quin va funcionar.
6. Verifica que el pla ha canviat, no només que el temps ha baixat (pot haver baixat per la memòria cau). I setmanes després, comprova a pg_stat_user_indexes que l'índex se segueix utilitzant.

• Llançar EXPLAIN ANALYZE sobre un UPDATE o un DELETE sense transacció. L'executa de debò. BEGIN ... ROLLBACK, sempre.
• Llegir el pla de dalt a baix com si fos codi. Es llegeix de dins cap a fora: el node més sagnat va primer. I no oblidis multiplicar per loops: un node de 0,08 ms amb 20.000 voltes són 1,6 segons, i sol ser el culpable.
• Comparar el cost de dos motors, o de dos servidors amb configuració diferent. Són unitats arbitràries i relatives.
• Refiar-se només del temps en una màquina compartida. Fes servir BUFFERS: els blocs llegits són reproduïbles. I no mesuris una sola vegada: la primera execució omple la memòria cau.
• Deixar enable_seqscan = off posat. És per diagnosticar, mai per a producció: obligues el planificador a triar malament la resta de les seves decisions. I no optimitzis sense ANALYZE previ: si les estimacions estan malament, tot el que hi facis a sobre està malament.
• Consell: desa el pla d'abans. Un fitxer amb l'EXPLAIN previ al canvi és l'única prova objectiva que has millorat alguna cosa.
• Consell: analitza amb dades reals de producció. Un pla sobre 100 files de desenvolupament no diu absolutament res sobre 20 milions, i els problemes que només apareixen a les tres de la matinada no es reprodueixen a mà: per a això hi ha auto_explain.

Llegeix aquest pla, tret d'una BotigaVerda de mida real (2 milions de comandes, no el banc de proves d'aquesta lliçó):

1. En quin ordre s'executen els nodes?
2. Quin és el problema principal i quin senyal el delata?
3. Quant temps total consumeix realment l'Index Scan?
4. Proposa dues intervencions, en l'ordre en què les provaries.

Sobre comandes_grans ja creada i indexada per client_id, prediu abans d'executar quin node triarà el planificador en cada cas, i després comprova-ho amb EXPLAIN ANALYZE.

Escriu la seqüència completa d'ordres —incloses les precaucions— per analitzar el rendiment d'aquest DELETE sense modificar les dades, i digues què buscaries al pla.

Solució 1

1. L'ordre. Primer el Seq Scan sobre comandes (el node més profund de la branca esquerra); per cada fila que produeix, el Nested Loop executa l'Index Scan sobre clients; el Nested Loop emet el resultat.

2. El problema és una estimació catastròficament errònia. El senyal és al Seq Scan: rows=38 estimades enfront de rows=18422 reals, gairebé 500 vegades més. Amb 38 files, un Nested Loop és l'elecció perfecta; amb 18.422 es converteix en 18.422 cerques indexades. I hi ha dos símptomes de suport: Rows Removed by Filter: 1981578 (es llegeixen dos milions de files per quedar-se amb el 0,9 %, així que falta un índex sobre estat) i Buffers: shared hit=1204885, més d'un milió de blocs llegits per retornar 18.422 files.

3. El temps de l'Index Scan. actual time és per execució: 0,004 ms × 18.422 loops ≈ 74 ms. És a dir, l'Index Scan no és el problema: dels 9.421 ms, 1.842 són el Seq Scan, 74 l'Index Scan, i la resta se la menja la maquinària de repetir el bucle 18.422 vegades. Oblidar-se de multiplicar per loops és l'error de lectura més comú.

4. Les intervencions, en ordre. Primer, ANALYZE comandes; i tornar a mirar el pla: és gratis, instantani i ataca la causa arrel — si l'estimació passa a ser correcta, el planificador canviarà sol el Nested Loop per un Hash Join i el temps caurà sense tocar res més. Després, si l'estimació ja era correcta, un índex per a les pendents, millor parcial: CREATE INDEX ... ON comandes (client_id) WHERE estat = 'pendent';, perquè són menys de l'1 % i així l'índex és minúscul (08-02).

Solució 2

Solució 3

La precaució clau és el BEGIN ... ROLLBACK: EXPLAIN ANALYZE executa el DELETE de debò, i sense la transacció perdries les files. Què buscar al pla:

• Com localitza les files. Sense índex sobre data_comanda, un Seq Scan amb un Rows Removed by Filter enorme. Amb índex, un Bitmap Heap Scan — i com que les dades es van generar en ordre cronològic, les files a esborrar són físicament juntes, que és el millor escenari possible.
• Quantes files esborra. El rows real del node d'esborrat: uns 285.000 (365 dies × 782 al dia). I la comparació estimat/real, com sempre.
• Una consideració que aquest mòdul no pot resoldre: un DELETE de 285.000 files deixa 285.000 versions mortes que VACUUM haurà de netejar, i manté bloquejos durant tota la transacció. Per això els esborrats massius es fan per lots, i per això el particionat (08-04) és tan atractiu per als històrics: esborrar un any sencer és desconnectar una partició, no executar un DELETE.

Tanques el mòdul 8 amb el mètode, no només amb les regles:

• EXPLAIN estima; EXPLAIN ANALYZE executa i mesura — i per això un UPDATE o un DELETE cal embolicar-los en BEGIN ... ROLLBACK.
• Un pla és un arbre que es llegeix de dins cap a fora. cost són unitats relatives, rows i width són estimacions, i actual time i rows són mitjanes per execució: cal multiplicar-les per loops.
• El senyal més útil de tots és la divergència entre les files estimades i les reals. Quan és gran, el pla està ben triat per a una realitat que no existeix, i la causa acostuma a ser ANALYZE.
• Saps reconèixer els quinze nodes habituals, des del Seq Scan fins al Gather, i què significa que apareguin Rows Removed by Filter, Heap Fetches o Sort Method: external merge Disk. I saps que BUFFERS és millor mètrica que el temps en qualsevol màquina compartida: els blocs llegits són reproduïbles.
• Ho has vist mesurat, no explicat: sobre dos milions de files, la mateixa consulta passa de 16.250 blocs i ~142 ms a 13 blocs i ~0,09 ms. Mentre que sobre els 20 productes de BotigaVerda l'índex costa deu vegades més que el Seq Scan, i el planificador encerta ignorant-lo. I la promesa de 07-05 queda verificada amb el seu nom al pla: Hash Semi Join.

I amb això es tanca el mòdul 8. En cinc lliçons has passat de "no sé per què això va lent" a tenir un procediment: saps què és un B-tree i per què 15 milions de files caben en 3 nivells; saps que BotigaVerda tenia onze índexs que ningú no va crear i onze claus foranes sense cobrir, perquè PostgreSQL no les indexa; saps crear índexs compostos, parcials, d'expressió i cobridors, i triar entre B-tree, hash, GIN, GiST, BRIN i SP-GiST; saps —i això és el més rar de trobar— quan no indexar; saps reescriure una consulta per fer-la sargable, detectar un N+1 i actualitzar les estadístiques del planificador; i saps llegir un pla d'execució i decidir amb dades.

Però hi ha una suposició que hem mantingut durant vuitanta i escaig lliçons sense dir-la en veu alta: que som l'únic usuari de la base de dades. Tot el que has mesurat aquí suposa que ningú més no està llegint ni escrivint alhora, que cap fila no canvia mentre la consultes i que cap UPDATE no competeix amb el teu per la mateixa línia de comanda. Això és fals en qualsevol sistema real: a BotigaVerda, un client confirma una comanda mentre el magatzem actualitza l'estoc del mateix producte i l'analista llança l'informe mensual sobre aquestes mateixes taules. Al mòdul 9, Transaccions, veuràs què és una transacció i les quatre garanties ACID, com es controlen amb BEGIN, COMMIT, ROLLBACK i SAVEPOINT, què són els nivells d'aïllament i quines anomalies permet cadascun, i com funcionen els bloquejos i els interbloquejos — inclòs, per fi, el model MVCC que explica el bloat d'aquesta lliçó i per què CREATE INDEX CONCURRENTLY havia d'existir.