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

HOME

Hi ha un reflex gairebé universal quan una consulta va lenta: crear un índex. I moltes vegades és la resposta equivocada, perquè l'índex no pot arreglar una consulta que està demanant feina innecessària. Una consulta que porta 200 columnes per mostrar-ne 3, que filtra després d'agrupar, que embolica la columna filtrada en una funció o que s'executa 200 vegades des del bucle de l'aplicació no té un problema d'índexs: té un problema d'escriptura, i cap CREATE INDEX no el resoldrà.

Aquesta lliçó és el catàleg d'aquestes tècniques. Primer, vuit regles d'escriptura amb el seu abans i el seu després, diverses de les quals tanquen promeses dels mòduls 2, 4 i 7. Després, com funcionen les estadístiques que alimenten el planificador i què passa quan es desfasen. I al final, la part que més vegades salva el dia: què fer quan el problema no és a la consulta, començant per l'antipatró més car i més freqüent de tots, l'N+1. L'ordre d'intervenció, que convé tenir gravat: primer es reescriu la consulta, després s'indexa, després es canvia l'arquitectura, i només al final es compra maquinari. Va del barat i reversible al car i permanent.

Contingut

Vuit regles d'escriptura
Sargabilitat, la regla que governa les altres
Estadístiques i el planificador
Estadístiques esteses per a columnes correlacionades
Quan el problema no és la consulta: l'N+1
Escalons següents quan ja no n'hi ha prou
Taula de diagnòstic: símptoma → causa probable → què provar
Errors habituals i consells
Exercicis
Conclusió

Vuit regles d'escriptura

Regla 1: no demanis columnes que no faràs servir

-- ⚠️ INCORRECTA
SELECT * FROM productes AS p WHERE p.categoria_id = 4;

-- ✅ CORRECTA
SELECT p.id, p.nom, p.preu FROM productes AS p WHERE p.categoria_id = 4;

id	nom	preu
14	Infusió de camamilla ecològica 20 u	3.25
15	Te verd matcha cerimonial 30 g	22.00
16	Kombutxa de gingebre 750 ml	4.95
17	Suc de taronja premsat en fred 1 L	5.40

Tres motius, en ordre d'importància. Impedeix l'Index Only Scan: si existís un índex (categoria_id) INCLUDE (nom, preu), la segona versió es resoldria sense tocar la taula, mentre que la primera obliga a anar a buscar les nou columnes fila a fila. Envia menys dades per la xarxa: una columna TEXT de 4 kB que ningú no mostra, per mil files, són 4 MB llençats. I és fràgil: un SELECT * canvia de forma quan algú afegeix una columna.

Regla 2: filtra com més aviat millor — `WHERE` abans que `HAVING`

Tanca el fil obert a 04-06. Les dues consultes donen el mateix:

-- ⚠️ INCORRECTA: agrupa els 15 clients i després llença 2 grups
SELECT c.pais, COUNT(*) AS clients FROM clients AS c GROUP BY c.pais HAVING c.pais = 'Espanya';

-- ✅ CORRECTA: descarta 4 files abans d'agrupar
SELECT c.pais, COUNT(*) AS clients FROM clients AS c WHERE c.pais = 'Espanya' GROUP BY c.pais;

pais	clients
Espanya	11

La diferència és d'ordre lògic (mòdul 2): WHERE s'aplica abans d'agrupar, HAVING després. La primera versió construeix tres grups i en descarta dos; amb quinze clients tant és, amb quinze milions el HashAggregate processa el triple de dades per no res. I a més, la condició del WHERE sí que pot aprofitar un índex; la del HAVING, mai.

La regla: HAVING és exclusivament per a condicions sobre agregats (HAVING COUNT(*) > 1). Si la condició es pot escriure sense agregat, va al WHERE.

Regla 3: la columna filtrada, sempre nua

-- ⚠️ INCORRECTA: EXTRACT sobre la columna anul·la qualsevol índex
SELECT COUNT(*) AS comandes_2025 FROM comandes AS co
WHERE EXTRACT(YEAR FROM co.data_comanda) = 2025;

-- ✅ CORRECTA: rang de dates, la columna apareix sola
SELECT COUNT(*) AS comandes_2025 FROM comandes AS co
WHERE co.data_comanda >= '2025-01-01' AND co.data_comanda < '2026-01-01';

comandes_2025
16

Les mateixes 16 comandes del 2025, i la segona versió pot fer servir un índex sobre data_comanda. És l'exemple canònic de la secció 2. Fixa't en el detall del límit superior: < '2026-01-01', no <= '2025-12-31'. Amb un DATE són equivalents, però si demà la columna passa a TIMESTAMP, el <= perdria tot el que hagi passat el 31 de desembre després de mitjanit. El patró >= inici AND < inici_següent és correcte sempre; acostuma-t'hi.

El mateix amb les conversions de tipus —WHERE co.data_comanda::text LIKE '2025-03%' converteix la columna, així que anul·la l'índex, i s'ha d'escriure com el rang >= '2025-03-01' AND < '2025-04-01'— i amb els càlculs: WHERE p.preu * 1.21 > 15 no fa servir índex; WHERE p.preu > 15 / 1.21 sí, perquè el càlcul és al costat de la constant.

Regla 4: `LIMIT` amb `ORDER BY` sobre columna indexada

SELECT p.id, p.nom, p.preu
FROM productes AS p
ORDER BY p.preu DESC
LIMIT 5;

id	nom	preu
15	Te verd matcha cerimonial 30 g	22.00
6	Crema facial d'àloe vera 50 ml	18.90
20	Càpsules d'espirulina 120 u	16.40
8	Oli corporal d'ametlles 200 ml	14.25
13	Espelmes de cera de soja (pack 2)	13.75

Sense índex sobre preu, el motor ha d'examinar totes les files per saber quines són les cinc més grans. Amb moltes files fa servir el top-N heapsort que vas veure a 02-05, que evita ordenar-les totes però continua havent-les de llegir totes. Amb un índex sobre preu, llegeix cinc entrades des del final de l'arbre i s'atura: cost independent de la mida de la taula. És una de les millors relacions esforç/benefici que existeixen, perquè els "top 10" són a totes les pantalles d'inici de totes les aplicacions.

Regla 5: `EXISTS`, no `COUNT(*) > 0`

Reprenent 07-03:

-- ⚠️ INCORRECTA: compta les 5 línies del producte 1 per saber que n'hi ha alguna
SELECT p.id, p.nom FROM productes AS p
WHERE (SELECT COUNT(*) FROM linies_comanda AS lc WHERE lc.producte_id = p.id) > 0;

-- ✅ CORRECTA: s'atura a la primera coincidència
SELECT p.id, p.nom FROM productes AS p
WHERE EXISTS (SELECT 1 FROM linies_comanda AS lc WHERE lc.producte_id = p.id);

Els mateixos 17 productes venuts. COUNT(*) obliga a recórrer totes les línies de cada producte; EXISTS curtcircuita a la primera. Amb 5 línies la diferència és nul·la; amb 40.000 vendes d'un producte, és llegir-les totes enfront de llegir-ne una. I hi ha premi de pla: EXISTS es converteix en un semi-join (07-05), mentre que la subconsulta amb COUNT gairebé mai no es transforma.

Regla 6: `UNION ALL` quan no hi pot haver duplicats

-- ⚠️ INCORRECTA si saps que els conjunts són disjunts
SELECT id, data_comanda FROM comandes WHERE data_comanda <  '2026-01-01'
UNION
SELECT id, data_comanda FROM comandes WHERE data_comanda >= '2026-01-01';

-- ✅ CORRECTA
SELECT id, data_comanda FROM comandes WHERE data_comanda <  '2026-01-01'
UNION ALL
SELECT id, data_comanda FROM comandes WHERE data_comanda >= '2026-01-01';

Les mateixes 20 files (16 del 2025 + 4 del 2026), perquè una comanda no pot ser als dos costats d'una data. Però UNION elimina duplicats, i això obliga el motor a ordenar les 20 files o a construir una taula hash amb totes elles abans de retornar res; UNION ALL les concatena i prou. La regla: UNION ALL per defecte, UNION només quan de debò necessitis deduplicar. La deduplicació gratis no existeix.

Regla 7: `DISTINCT` no és un pedaç per a un `JOIN` mal plantejat

Tanca 02-04 i confirma el que es va dir a 07-05:

-- ⚠️ INCORRECTA: genera 47 files i en descarta 30
SELECT DISTINCT p.id, p.nom FROM productes AS p
JOIN linies_comanda AS lc ON lc.producte_id = p.id;

-- ✅ CORRECTA: retorna 17 des del principi
SELECT p.id, p.nom FROM productes AS p
WHERE EXISTS (SELECT 1 FROM linies_comanda AS lc WHERE lc.producte_id = p.id);

17 productes en tots dos casos, però el primer produeix 47 files —una per línia de comanda, amb l'oli d'oliva repetit cinc vegades— i després les dedupliqua: feina feta per desfer-la. Un DISTINCT a la teva consulta és un senyal de diagnòstic: pregunta't quin JOIN està multiplicant files i si de debò el necessitaves. Quan el DISTINCT és legítim —"la llista de ciutats on tenim clients o empleats", 11 ciutats de 23 files— no hi ha res a corregir.

Regla 8: paginació per keyset, no per `OFFSET` gran

Tanca 02-06. Les dues retornen la quarta pàgina:

-- ⚠️ INCORRECTA amb offsets grans
SELECT id, data_comanda FROM comandes ORDER BY id LIMIT 5 OFFSET 15;

-- ✅ CORRECTA: recorda on s'ha quedat la pàgina anterior
SELECT id, data_comanda FROM comandes WHERE id > 15 ORDER BY id LIMIT 5;

id	data_comanda
16	2025-12-19
17	2026-01-13
18	2026-01-27
19	2026-02-09
20	2026-02-21

El problema de l'OFFSET és que no salta: llegeix i descarta. Per servir la pàgina 1.000 amb 20 elements, el motor llegeix 20.000 files i en llença 19.980.

	`OFFSET`	Keyset
Cost de la pàgina N	Creix linealment amb N	Constant
Aprofita l'índex	Només per ordenar	Per ordenar i per posicionar-se
Saltar a "la pàgina 500"	✅ Directe	❌ Cal encadenar
Files duplicades o perdudes si algú insereix	Sí	No
Ús típic	Paginadors numerats	Scroll infinit, API, exportacions

Amb ordre compost, el keyset fa servir la comparació de tuples —WHERE (data_comanda, id) < ('2026-01-13', 17) ORDER BY data_comanda DESC, id DESC LIMIT 5—, que és el que fan per dins els cursors de les API modernes.

Sargabilitat, la regla que governa les altres

Les regles 3, 4 i 8 són la mateixa idea amb tres disfresses, i aquesta idea té nom: sargabilitat. Ve de SARG, Search ARGument.

Una condició és sargable quan el motor la pot traduir a "situa't en un punt de l'índex i avança". A la pràctica: la columna apareix sola a un costat de la comparació, sense funcions, sense càlculs i sense conversions de tipus.

Condició	És sargable?	Reescriptura sargable
`EXTRACT(YEAR FROM data_comanda) = 2025`	❌	`data_comanda >= '2025-01-01' AND data_comanda < '2026-01-01'`
`LOWER(email) = '[email protected]'`	❌	Índex sobre `LOWER(email)` (08-02), o normalitzar en desar
`nom LIKE '%oli%'`	❌	`pg_trgm` + GIN (08-03)
`nom LIKE 'Oli%'`, `quantitat BETWEEN 2 AND 5`	✅	—
`preu * 1.21 > 15`	❌	`preu > 15 / 1.21`
`data_comanda::text LIKE '2025%'`	❌	Rang de dates
`id + 0 = 7`	❌	`id = 7`
`estat <> 'lliurat'`	⚠️ Tècnicament sí, inútil a la pràctica	`estat IN ('pendent','pagat','enviat','cancellat')`, o índex parcial

Interioritzar aquesta paraula t'estalvia memoritzar la llista: cada vegada que escriguis un WHERE, mira si la columna està nua. Si no ho està, tens tres sortides, en aquest ordre de preferència: reescriure la condició, indexar l'expressió, o normalitzar la dada en desar-la (per exemple, desar l'email ja en minúscules i estalviar-te el LOWER per sempre).

Estadístiques i el planificador

El planificador no endevina: estima. I estima a partir d'unes estadístiques que PostgreSQL desa sobre cada taula i cada columna: el nombre de files i de pàgines, la fracció de nuls, el nombre de valors diferents (la cardinalitat de 08-03), els valors més freqüents amb la seva freqüència, i un histograma que reparteix la resta en trams per estimar rangs. Amb això respon la pregunta clau: "quantes files retornarà WHERE estat = 'lliurat'?". Si n'estima 14 de 20 (70 %), tria Seq Scan. Si n'estima 1 de 2.000.000, tria Index Scan. Tota la qualitat del pla depèn que aquesta estimació sigui raonable.

SELECT attname       AS columna,
       n_distinct    AS valors_diferents,
       null_frac     AS fraccio_nuls,
       most_common_vals AS mes_frequents
FROM pg_stats
WHERE tablename = 'comandes' AND attname IN ('estat', 'empleat_id');

columna	valors_diferents	fraccio_nuls	mes_frequents
estat	5	0	{lliurat,enviat,pagat,cancellat,pendent}
empleat_id	3	0.5	{4,5,6}

Aquí ho tens, en dues files, el que el planificador sap de comandes: que estat té 5 valors i que la meitat dels empleat_id són nuls (les deu comandes web de 01-06).

Com es recullen i quan es desfasen

Les recull ANALYZE taula; quan tu la llances, autovacuum quan una taula acumula cert percentatge de canvis, i VACUUM ANALYZE taula; alhora que neteja. El problema apareix quan es queden velles, i hi ha tres situacions clàssiques:

Just després d'una càrrega massiva. Insereixes 5 milions de files; fins que autovacuum passi, el planificador continua creient que la taula en té 100 i triarà Nested Loop sobre milions de files. Després de qualsevol càrrega gran, llança ANALYZE a mà.
Després d'una migració que canvia la distribució d'una columna (05-06).
En taules amb creixement ràpid, on el llindar d'autovacuum arriba tard.

El símptoma és inconfusible i el veuràs a 08-05: una divergència enorme entre les files estimades i les reals al pla d'execució. I quan el problema no és que siguin velles sinó que la mostra es queda curta, es puja el detall —default_statistics_target, que per omissió val 100 i controla quants valors freqüents i quants trams d'histograma es desen—:

ANALYZE comandes;                                                -- una taula
ALTER TABLE comandes ALTER COLUMN estat SET STATISTICS 500;      -- més detall, només aquesta columna
ANALYZE comandes;                                                -- imprescindible després

Pujar-lo millora les estimacions en columnes amb distribucions molt desiguals a canvi d'un ANALYZE més lent i un planificador una mica més lent. Puja'l per columna, mai de manera global "per si de cas", i només quan un pla t'hagi demostrat que l'estimació està malament.

Estadístiques esteses per a columnes correlacionades

Hi ha una errada d'estimació que cap quantitat de mostreig no arregla: el planificador suposa que les columnes són independents, i al món real gairebé mai no ho són. A BotigaVerda, categoria_id i proveidor_id estan clarament relacionades: la cosmètica ve de Maison Nature i de Verde Atlántico, l'alimentació de Huerta del Turia i BioSierra.

SELECT COUNT(DISTINCT categoria_id) AS categories,
       COUNT(DISTINCT proveidor_id) AS proveidors,
       (SELECT COUNT(*) FROM (SELECT DISTINCT categoria_id, proveidor_id FROM productes) AS x)
                                    AS combinacions_reals
FROM productes;

categories	proveidors	combinacions_reals
6	5	12

Sis per cinc són trenta combinacions possibles, però només n'existeixen dotze. Davant de WHERE categoria_id = 2 AND proveidor_id = 4, el planificador multiplica selectivitats com si fossin independents: 1/6 × 1/5 = 1/30, i estima menys d'una fila. La realitat són 3 productes. Amb 20 files és irrellevant; amb 20 milions, una subestimació així fa que el motor triï un Nested Loop on calia un Hash Join, i la consulta passa de segons a hores.

La solució són les estadístiques esteses, disponibles des de PostgreSQL 10, amb tres tipus: ndistinct captura el nombre real de combinacions diferents (12, no 30); dependencies, les dependències funcionals ("saber el proveïdor gairebé determina la categoria"); mcv, les combinacions concretes més freqüents.

CREATE STATISTICS stat_productes_cat_prov (ndistinct, dependencies)
    ON categoria_id, proveidor_id FROM productes;
ANALYZE productes;

SELECT statistics_name, attnames, kinds FROM pg_stats_ext;

statistics_name	attnames	kinds
stat_productes_cat_prov	{categoria_id,proveidor_id}	{d,f}

Els candidats típics són els parells que "van junts": codi postal i ciutat, marca i model, país i moneda, categoria i proveïdor. Crea-les quan un pla et mostri una estimació molt allunyada de la realitat, no abans.

Quan el problema no és la consulta: l'N+1

I ara el més car de tots, que ni tan sols es veu des de la base de dades. L'N+1 passa quan l'aplicació llança una consulta per obtenir una llista i després una consulta més per cada element d'aquesta llista.

1 consulta:    SELECT id, client_id, data_comanda FROM comandes;         -- 20 files
20 consultes:  SELECT nom, cognoms FROM clients WHERE id = ?;            -- una per comanda
------------------------------------------------------------------------
Total: 21 consultes per pintar una taula

Contra una consulta única:

SELECT co.id, co.data_comanda, c.nom || ' ' || c.cognoms AS client
FROM comandes AS co JOIN clients AS c ON c.id = co.client_id ORDER BY co.id;

Una consulta, 20 files. El problema no és el temps de cada consulta —cadascuna triga 0,2 ms— sinó el cost fix que es paga 21 vegades: viatge de xarxa d'anada i tornada, anàlisi sintàctica, planificació, execució, transferència. I escala fatal: una pantalla de 500 comandes són 501 consultes.

	N+1 (21 consultes)	Un `JOIN` (1 consulta)
Viatges de xarxa i planificacions	21	1
Temps amb 1 ms de latència	~25 ms	~2 ms
Amb 500 elements	~520 ms	~4 ms
Amb 500 elements i 20 ms de latència	~10 s	~25 ms

És dificilíssim de detectar mirant la base de dades, perquè cada consulta individual és rapidíssima: no apareix al rànquing de consultes lentes. Apareix a pg_stat_statements com una consulta amb un mean_exec_time mínim i un nombre de calls disbaratat — una altra raó per ordenar per temps total. Gairebé sempre surt d'un ORM: un bucle que recorre objectes i accedeix a una propietat relacionada, disparant una consulta per volta sense que es vegi al codi. Tots tenen el remei (JOIN FETCH a JPA, select_related/prefetch_related a Django, includes a Rails, Include a Entity Framework); el problema és recordar-se'n de fer-lo servir.

Els altres dos problemes d'aplicació són cosins seus. La manca d'agrupació en lots: inserir 10.000 files amb 10.000 INSERT en lloc d'un amb 10.000 tuples, o amb COPY — mateix mecanisme, cost fix multiplicat, i la raó per la qual l'script de 01-06 fa servir INSERT múltiples. I portar files que no es mostren mai: descarregar dos milions de comandes perquè l'aplicació es quedi amb 20. Si la pantalla en mostra 20, la consulta n'ha de demanar 20, amb LIMIT i amb el filtre fet en SQL; el filtratge en memòria de l'aplicació és el malbaratament més silenciós que existeix.

Escalons següents quan ja no n'hi ha prou

Quan la consulta ja està ben escrita, els índexs estan posats i continua sense ser suficient, queden aquests escalons — de menys a més invasiu:

Tècnica	Quan s'aplica	Cost / risc
Vistes materialitzades	Un informe car que es consulta moltes vegades i admet dades de fa unes hores	Cal refrescar-les; les dades no són instantànies → 10-01
Taules de resum	Agregats que es consulten constantment (vendes per dia i categoria)	Cal mantenir-les sincronitzades, amb `triggers` (10-05) o un procés programat
Memòria cau d'aplicació	Dades que canvien poc i es llegeixen moltíssim (el catàleg, les categories)	Invalidació: el problema difícil de debò
Particionat	Una taula enorme amb un criteri natural de tall, típicament la data	Canvia el DDL i les consultes han de filtrar per la clau de partició
Rèpliques de lectura	Moltes més lectures que escriptures; informes que competeixen amb l'operació	Retard de replicació: la rèplica va uns mil·lisegons per darrere
Sharding	Quan ni una màquina ni les rèpliques no basten	Enorme: reparteix les dades entre servidors i complica totes les consultes

Els dos últims mereixen una nota. El particionat divideix físicament una taula en trossos (per exemple, un per any de data_comanda) perquè una consulta acotada només llegeixi el que li toca i per poder esborrar un any sencer sense un DELETE massiu; és una eina d'administració avançada i aquest curs no la tracta. El sharding reparteix les dades entre diversos servidors i és l'última carta. I un advertiment general: aquests escalons no arreglen una consulta mal escrita, l'amaguen. Una vista materialitzada sobre un SELECT amb un DISTINCT innecessari continua tenint el DISTINCT innecessari, només que ara al procés de refresc.

Taula de diagnòstic: símptoma → causa probable → què provar

Símptoma	Causa probable	Què provar
Una consulta que anava bé s'ha tornat lenta sense canviar-la	Estadístiques desfasades després d'un creixement o una càrrega	`ANALYZE taula;` i comparar el pla
`Seq Scan` sobre una taula gran amb un filtre molt selectiu	Falta l'índex, o la condició no és sargable	Crear l'índex; revisar funcions sobre la columna
L'índex existeix però no s'utilitza	Baixa selectivitat, condició no sargable, expressió que no coincideix, estadístiques dolentes	`EXPLAIN` (08-05); comprovar quantes files retorna el filtre
Moltes consultes gairebé idèntiques i rapidíssimes	N+1 des de l'aplicació	Ordenar `pg_stat_statements` per `calls`; fer servir `JOIN` o càrrega anticipada
Lenta només a la pàgina 300 del llistat	`OFFSET` gran	Paginació per keyset
Lenta des que s'hi va afegir un `ORDER BY`	Ordenació sense índex, `Sort` que se'n va a disc	Índex que doni l'ordre; pujar `work_mem`; revisar `LIMIT`
L'`INSERT` va lent i abans no	Massa índexs, o índexs GIN	Revisar `pg_stat_user_indexes` i esborrar els morts
Estimacions molt allunyades de les files reals	Columnes correlacionades, o mostra curta	`CREATE STATISTICS`; pujar `STATISTICS` de la columna
Tot va lent alhora	No és la consulta: memòria, disc, bloquejos, `VACUUM` pendent	Mètriques del sistema; bloquejos → mòdul 9
`DELETE` d'una fila que triga segons	FK sense índex en una taula filla amb `CASCADE`	Índex sobre la columna FK (08-01)

Errors habituals i consells

Optimitzar abans de mesurar. Reescriure una consulta llegible per una de críptica basant-te en una intuïció et fa perdre llegibilitat i, gairebé sempre, no guanyar res. Mesura primer (08-05).
Posar al HAVING el que va al WHERE. HAVING és només per a condicions sobre agregats.
Fer servir <= al límit superior d'un rang de dates. < '2026-01-01' és correcte sempre, fins i tot si la columna passa a ser un TIMESTAMP.
Afegir DISTINCT per "arreglar" files repetides. El DISTINCT amaga el JOIN que sobra en lloc de treure'l.
Fer servir UNION per costum. Deduplicar costa una ordenació o una taula hash senceres.
Paginar amb OFFSET en una API. A més de lent, es salta i repeteix files quan algú insereix mentre l'usuari navega.
Oblidar ANALYZE després d'una càrrega massiva. És la causa número u de "la mateixa consulta que ahir volava, avui no acaba". I no pugis default_statistics_target globalment per arreglar dues columnes: fes-ho per columna.
Consell: compta les consultes, no només els mil·lisegons. Un comptador de consultes per petició detecta un N+1 en cinc minuts; el rànquing de consultes lentes no ho farà mai.
Consell: desa la consulta original comentada quan la reescriguis per rendiment, amb una nota de què va millorar. És la documentació més barata que existeix.
Consell: aplica les regles en ordre de cost. Reescriure és gratis i reversible; comprar maquinari és car i permanent.

Exercicis

Exercici 1

Reescriu aquestes quatre consultes per fer-les sargables o més eficients, i explica què millora cada canvi.

-- a)
SELECT * FROM comandes WHERE EXTRACT(MONTH FROM data_comanda) = 3
                         AND EXTRACT(YEAR FROM data_comanda) = 2025;
-- b)
SELECT c.pais, COUNT(*) FROM clients c GROUP BY c.pais HAVING c.pais <> 'Espanya';
-- c)
SELECT DISTINCT c.id, c.nom
FROM clients c JOIN comandes co ON co.client_id = c.id JOIN linies_comanda lc ON lc.comanda_id = co.id;
-- d)
SELECT id, nom FROM productes ORDER BY id LIMIT 5 OFFSET 15;

Exercici 2

La pantalla "últimes comandes" de BotigaVerda mostra 20 comandes amb el nom del client, el del comercial i el nombre de línies de cadascuna. L'equip informa que triga 4 segons i que el registre mostra 61 consultes per càrrega de pàgina.

Quin antipatró és i d'on surten exactament les 61 consultes?
Escriu una sola consulta que retorni tot el que cal.
Quins índexs ajudarien, sabent el de 08-01 sobre les claus foranes?

Exercici 3

Un informe nocturn que trigava 20 segons ha passat a trigar 40 minuts. No ha canviat ni el SQL ni els índexs. L'única cosa que ha passat és que ahir a la nit es van carregar 12 milions de files històriques a comandes.

Formula la hipòtesi més probable i explica el mecanisme.
Digues què comprovaries, amb la consulta o l'ordre concrets.
Proposa la solució immediata i la mesura preventiva.

Solucions

Solució 1

-- a) Sargable: un únic rang sobre la columna nua.
SELECT id, client_id, data_comanda, estat
FROM comandes
WHERE data_comanda >= '2025-03-01' AND data_comanda < '2025-04-01';

-- b) El filtre no fa servir cap agregat: va al WHERE, abans d'agrupar
SELECT c.pais, COUNT(*) AS clients FROM clients AS c WHERE c.pais <> 'Espanya' GROUP BY c.pais;

-- c) Pregunta d'existència: EXISTS en lloc de dos JOIN i un DISTINCT
SELECT c.id, c.nom FROM clients AS c
WHERE EXISTS (SELECT 1 FROM comandes AS co WHERE co.client_id = c.id);

-- d) Keyset en lloc d'OFFSET
SELECT id, nom FROM productes WHERE id > 15 ORDER BY id LIMIT 5;

A a) es corregeixen dues coses: el SELECT * i les dues crides a EXTRACT, que juntes impedeixen qualsevol índex. A b), amb tres països el guany és simbòlic, però l'hàbit és el correcte i el WHERE sí que pot fer servir un índex. A c), el segon JOIN amb linies_comanda no aporta cap columna al resultat i multiplica cada client per les seves línies: el JOIN+DISTINCT generaria 47 files per retornar 12 clients compradors; l'EXISTS en retorna 12 directament i no necessita linies_comanda en absolut. A d), amb 20 files tant és; amb 2 milions, OFFSET 1000000 llegeix i descarta un milió de files.

Solució 2

1. És un N+1, doblat. Les 61 consultes són: 1 per a la llista de comandes, 20 per al nom del client de cadascuna, 20 per al del comercial i 20 per comptar les línies. 1 + 20 + 20 + 20 = 61. Cadascuna triga menys d'un mil·lisegon; el que costa 4 segons són els 61 viatges d'anada i tornada.

2. Una sola consulta. Dues decisions que no són de rendiment sinó de correcció, i que vénen del mòdul 3: LEFT JOIN amb empleats, perquè deu de les vint comandes són web i tenen empleat_id NULL (un INNER JOIN les faria desaparèixer), amb COALESCE per mostrar "Web"; i LEFT JOIN amb linies_comanda, perquè una comanda sense línies compti 0 en lloc de desaparèixer.

SELECT co.id, co.data_comanda,
       c.nom || ' ' || c.cognoms AS client,
       COALESCE(e.nom || ' ' || e.cognoms, 'Web') AS comercial,
       COUNT(lc.id) AS linies
FROM comandes            AS co
JOIN clients             AS c  ON c.id  = co.client_id
LEFT JOIN empleats       AS e  ON e.id  = co.empleat_id
LEFT JOIN linies_comanda AS lc ON lc.comanda_id = co.id
GROUP BY co.id, co.data_comanda, c.nom, c.cognoms, e.nom, e.cognoms
ORDER BY co.data_comanda DESC
LIMIT 20;

3. Índexs sobre les claus foranes implicades: comandes(client_id), comandes(empleat_id) —millor parcial, WHERE empleat_id IS NOT NULL, perquè la meitat són nuls— i sobretot linies_comanda(comanda_id), que és el que evita recórrer la taula sencera per comptar. I per a l'ORDER BY ... LIMIT 20, un índex sobre comandes(data_comanda): amb ell, el motor llegeix 20 entrades des del final de l'arbre i s'atura.

Solució 3

1. La hipòtesi: les estadístiques estan desfasades. Amb 12 milions de files noves i sense ANALYZE, pg_stats continua descrivint la taula anterior. El planificador creu que comandes és petita, estima que un filtre retornarà desenes de files quan en retornarà centenars de milers, i tria un Nested Loop —perfecte per a poques files, catastròfic per a moltes— en lloc d'un Hash Join. La consulta no ha canviat; el pla sí.

2. Què comprovar:

SELECT relname, n_live_tup, last_analyze, last_autoanalyze
FROM pg_stat_user_tables WHERE relname = 'comandes';

Si last_analyze és anterior a la càrrega i n_live_tup continua mostrant el recompte vell, la hipòtesi està confirmada. La prova definitiva és EXPLAIN ANALYZE sobre l'informe i comparar rows estimades amb rows reals: una divergència de diversos ordres de magnitud és la signatura exacta d'aquest problema (08-05).

3. La solució i la prevenció. Immediata: ANALYZE comandes;, que triga segons i retorna el pla bo. Preventiva: incloure ANALYZE al final de tot procés de càrrega massiva, com un pas més del guió, i no confiar que autovacuum hi arribi a temps: els seus llindars estan pensats per al degoteig del dia a dia, no per a una càrrega de 12 milions de files de cop.

Conclusió

Ja tens el catàleg complet del que es pot fer sense tocar cap índex:

Vuit regles d'escriptura: no demanis columnes de més, filtra amb WHERE i no amb HAVING, deixa la columna nua a la condició, recolza LIMIT en un ORDER BY indexat, EXISTS en lloc de COUNT(*) > 0, UNION ALL per defecte, DISTINCT només quan és legítim, i keyset en lloc d'OFFSET gran.
Totes es resumeixen en una paraula: sargabilitat. Si la columna filtrada apareix sola, l'índex es pot fer servir; si va embolicada en una funció, un càlcul o una conversió, no.
El planificador estima a partir de les estadístiques, i quan es desfasen tria plans dolents amb dades correctes. ANALYZE després de cada càrrega massiva és obligatori; default_statistics_target es puja per columna; i les estadístiques esteses arreglen el supòsit d'independència — a BotigaVerda, 6 × 5 = 30 combinacions possibles de categoria i proveïdor de les quals només n'existeixen 12.
Moltes vegades el problema no és a la consulta: l'N+1 converteix una pantalla en 21 o 501 consultes, cadascuna rapidíssima i cap sospitosa, i no es veu des del rànquing de consultes lentes.
Quan ja no n'hi ha prou, hi ha escalons: vistes materialitzades (10-01), taules de resum, memòria cau, particionat, rèpliques de lectura i, en última instància, sharding. Cap no arregla una consulta mal escrita: l'amaga.

Tot el que portes llegit al mòdul té la mateixa pega: són regles. Bones regles, però regles al capdavall, i en rendiment les regles s'equivoquen. De debò està fent servir aquell índex la teva consulta? De debò l'EXISTS es converteix en un semi-join, com va prometre 07-05? De debò les estadístiques estan malament? A la lliçó 08-05, Anàlisi del rendiment de les consultes, deixes de suposar: EXPLAIN i EXPLAIN ANALYZE, com es llegeix un pla d'execució node a node, què signifiquen cost, rows, width, actual time i loops, el senyal que delata unes estadístiques desfasades, i una demostració de debò —sobre una taula de dos milions de files construïda per a l'ocasió— del que canvia un índex.

Tècniques d'optimització de consultes

Contingut

Vuit regles d'escriptura

Regla 1: no demanis columnes que no faràs servir

Regla 2: filtra com més aviat millor — `WHERE` abans que `HAVING`

Regla 3: la columna filtrada, sempre nua

Regla 4: `LIMIT` amb `ORDER BY` sobre columna indexada

Regla 5: `EXISTS`, no `COUNT(*) > 0`

Regla 6: `UNION ALL` quan no hi pot haver duplicats

Regla 7: `DISTINCT` no és un pedaç per a un `JOIN` mal plantejat

Regla 8: paginació per keyset, no per `OFFSET` gran

Sargabilitat, la regla que governa les altres

Estadístiques i el planificador

Com es recullen i quan es desfasen

Estadístiques esteses per a columnes correlacionades

Quan el problema no és la consulta: l'N+1

Escalons següents quan ja no n'hi ha prou

Taula de diagnòstic: símptoma → causa probable → què provar

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

Tècniques d'optimització de consultes

Contingut

Vuit regles d'escriptura

Regla 1: no demanis columnes que no faràs servir

Regla 2: filtra com més aviat millor — WHERE abans que HAVING

Regla 3: la columna filtrada, sempre nua

Regla 4: LIMIT amb ORDER BY sobre columna indexada

Regla 5: EXISTS, no COUNT(*) > 0

Regla 6: UNION ALL quan no hi pot haver duplicats

Regla 7: DISTINCT no és un pedaç per a un JOIN mal plantejat

Regla 8: paginació per keyset, no per OFFSET gran

Sargabilitat, la regla que governa les altres

Estadístiques i el planificador

Com es recullen i quan es desfasen

Estadístiques esteses per a columnes correlacionades

Quan el problema no és la consulta: l'N+1

Escalons següents quan ja no n'hi ha prou

Taula de diagnòstic: símptoma → causa probable → què provar

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

Regla 2: filtra com més aviat millor — `WHERE` abans que `HAVING`

Regla 4: `LIMIT` amb `ORDER BY` sobre columna indexada

Regla 5: `EXISTS`, no `COUNT(*) > 0`

Regla 6: `UNION ALL` quan no hi pot haver duplicats

Regla 7: `DISTINCT` no és un pedaç per a un `JOIN` mal plantejat

Regla 8: paginació per keyset, no per `OFFSET` gran