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

HOME

A la lliçó anterior vas descobrir que BotigaVerda té onze índexs que ningú no va crear i li falten els de les seves onze claus foranes. Toca arreglar-ho. Aquí aprendràs la sintaxi completa de CREATE INDEX i, sobretot, les quatre variants que separen qui "crea índexs" de qui els dissenya: els compostos, amb la regla del prefix per l'esquerra que decideix en quin ordre van les columnes; els parcials, que indexen només les files que interessen; els d'expressió, que tanquen el problema de LOWER(email) que arrossegues des del mòdul 6; i els que porten INCLUDE per aconseguir un Index Only Scan.

La segona meitat és igual d'important i sol faltar als tutorials: gestionar els índexs que ja existeixen. Llistar-los, mesurar quant ocupen, reconstruir-los i detectar els dos problemes més cars d'una base de dades madura: els índexs que no fa servir ningú i els que són redundants amb un altre.

Avís sobre els objectes d'aquest mòdul. Tots els índexs que es creen al mòdul 8 són material didàctic. No formen part de l'esquema canònic de BotigaVerda definit a 01-06: si tornes a carregar botigaverda.sql, desapareixen, i els mòduls 9 a 12 no els donen per suposats.

Contingut

CREATE INDEX: la sintaxi completa i com anomenar-los
Els índexs que li falten a BotigaVerda
CREATE INDEX CONCURRENTLY
Índexs compostos i la regla del prefix per l'esquerra
Índexs parcials
Índexs sobre expressions
INCLUDE: índexs cobridors expressament
Ordre i nuls dins de l'índex
Gestió: llistar, mesurar, reanomenar, reconstruir i esborrar
Índexs no utilitzats i redundants
Errors habituals i consells
Exercicis
Conclusió

`CREATE INDEX`: la sintaxi completa i com anomenar-los

CREATE [UNIQUE] INDEX [CONCURRENTLY] [IF NOT EXISTS] nom
    ON taula [USING metode]
    ( columna_o_expressio [ASC | DESC] [NULLS {FIRST | LAST}] [, ...] )
    [INCLUDE (columna [, ...])]
    [WHERE condicio];

Element	Què fa	Quan l'utilitzaràs
`UNIQUE`	A més d'indexar, impedeix duplicats	Poques vegades a mà: val més declarar la restricció `UNIQUE` (05-01)
`CONCURRENTLY`	Construeix l'índex sense bloquejar les escriptures	En producció, sempre. Secció 3
`IF NOT EXISTS`	No falla si ja n'existeix un amb aquest nom	Scripts que s'executen diverses vegades
`USING metode`	`btree` (per omissió), `hash`, `gin`, `gist`, `brin`, `spgist`	Lliçó 08-03
Llista de columnes	Una o diverses, o expressions	Seccions 4 i 6
`ASC`/`DESC`, `NULLS FIRST/LAST`	Ordre dins de l'índex	Secció 8
`INCLUDE (...)`	Columnes desades només per llegir-les	Secció 7
`WHERE condicio`	Índex parcial: només indexa les files que compleixen	Secció 5

El cas mínim, i el 70 % dels índexs que crearàs a la teva vida:

CREATE INDEX idx_comandes_client_id ON comandes (client_id);

CREATE INDEX

Fixa't en el que no cal posar-hi: ni el mètode (btree és el valor per omissió) ni el tipus de dada ni la mida. I en el que sí que convé: el nom. Si l'omets, PostgreSQL genera <taula>_<columnes>_idx, i acabes amb comandes_client_id_data_comanda_idx1. La convenció més estesa —i la que farà servir el curs— és idx_<taula>_<columnes>:

Índex	Nom
`comandes (client_id, data_comanda)`	`idx_comandes_client_data`
`productes (categoria_id) WHERE actiu`	`idx_productes_categoria_actius`
`clients (LOWER(email))`	`idx_clients_email_lower`

Les raons són les mateixes de 05-01 amb les restriccions: missatges d'error i plans d'execució llegibles, migracions que poden referir-se a l'índex pel seu nom, i dos entorns que no acaben amb els noms intercanviats. Els índexs que donen suport a una PRIMARY KEY o a un UNIQUE són l'excepció: no els creïs a mà. Declara la restricció i deixa que el motor creï l'índex; si ho fas al revés, tindràs un índex sense restricció associada.

Els índexs que li falten a BotigaVerda

Comencem pel que 08-01 va deixar identificat: les onze claus foranes sense índex. Aquests són els que de debò valen la pena en una BotigaVerda de mida real:

-- Índexs del mòdul 8. NO formen part de l'esquema canònic.
CREATE INDEX idx_linies_comanda_comanda_id  ON linies_comanda (comanda_id);
CREATE INDEX idx_linies_comanda_producte_id ON linies_comanda (producte_id);
CREATE INDEX idx_comandes_client_id         ON comandes (client_id);
CREATE INDEX idx_productes_categoria_id     ON productes (categoria_id);
CREATE INDEX idx_productes_proveidor_id     ON productes (proveidor_id);
CREATE INDEX idx_ressenyes_producte_id      ON ressenyes (producte_id);
CREATE INDEX idx_devolucions_comanda_id     ON devolucions (comanda_id);

Els dos primers són els més rendibles de tot l'esquema: linies_comanda és la taula amb més files, apareix a gairebé tots els JOIN del curs i el seu comanda_id té ON DELETE CASCADE, així que sense índex cada esborrat de comanda recorre la taula sencera.

I ara la comprovació que convé fer sempre, encara que faci mal:

SELECT pg_size_pretty(pg_relation_size('linies_comanda'))                 AS taula,
       pg_size_pretty(pg_relation_size('idx_linies_comanda_comanda_id')) AS index;

taula	index
8192 bytes	16 kB

L'índex ocupa el doble que la taula. No és un error: les 47 línies caben en una sola pàgina de 8 kB, mentre que un B-tree en necessita com a mínim dues —la pàgina de metadades i l'arrel—. És la demostració més neta que amb aquests volums l'índex no pot aportar res, i per això aquest mòdul necessita una taula de proves gran (la construiràs a 08-05). El que ve a continuació té sentit pensant en la BotigaVerda d'aquí a cinc anys, amb milions de línies.

`CREATE INDEX CONCURRENTLY`

Un CREATE INDEX normal bloqueja les escriptures de la taula mentre es construeix. Les lectures continuen funcionant; els INSERT, UPDATE i DELETE esperen. Amb 47 files això dura microsegons; amb 50 milions pot durar vint minuts, i aquests vint minuts són una caiguda de servei.

CREATE INDEX CONCURRENTLY idx_comandes_data_comanda ON comandes (data_comanda);

CONCURRENTLY construeix l'índex en dues passades sobre la taula, sense prendre el bloqueig que impedeix escriure. A canvi té tres pegues: és més lent (dos recorreguts complets, més una espera que acabin les transaccions obertes); pot deixar un índex INVALID si alguna cosa va malament, és a dir creat però inservible —ocupa espai, es manté a cada escriptura i el planificador no l'utilitza—; i no cap dins d'una transacció, així que BEGIN; CREATE INDEX CONCURRENTLY ...; dóna error.

Detectar un índex invàlid i curar-lo —la cura és sempre la mateixa, esborrar-lo i refer-lo—:

SELECT indexrelid::regclass AS index FROM pg_index WHERE NOT indisvalid;

DROP   INDEX CONCURRENTLY idx_comandes_data_comanda;
CREATE INDEX CONCURRENTLY idx_comandes_data_comanda ON comandes (data_comanda);

La regla, en una línia: en desenvolupament, CREATE INDEX; en producció sobre una taula amb trànsit, CONCURRENTLY sempre, i comprova després que l'índex és vàlid.

El mecanisme pel qual un CREATE INDEX normal impedeix escriure és el dels bloquejos, i amb ell vénen les transaccions i el model de concurrència de PostgreSQL. Tot això és el mòdul 9; aquí n'hi ha prou de saber que existeix i que CONCURRENTLY és la manera d'esquivar-lo. És la mateixa família de precaucions que vas veure a 05-06 amb els ALTER TABLE que reescriuen la taula.

Índexs compostos i la regla del prefix per l'esquerra

Un índex compost indexa diverses columnes alhora. No és el mateix que dos índexs separats: ordena primer per la primera columna i, dins de cada valor, per la segona, exactament com un ORDER BY client_id, data_comanda.

CREATE INDEX idx_comandes_client_data ON comandes (client_id, data_comanda);

Pensa en una guia telefònica ordenada per cognom i després per nom. Pots buscar "tots els García" i pots buscar "García, Ana". El que no pots fer és buscar "totes les Ana": hauries de llegir la guia sencera. Això és la regla del prefix per l'esquerra: un índex compost serveix per a les consultes que fan servir un prefix de la seva llista de columnes, començant per la primera.

Consulta	`(client_id, data_comanda)`	`(data_comanda, client_id)`
`WHERE client_id = 1`	✅ Ideal	❌ No
`WHERE data_comanda >= '2025-06-01'`	❌ No	✅ Ideal
`WHERE client_id = 1 AND data_comanda >= '2025-06-01'`	✅ Ideal	⚠️ Parcial: filtra per data i descarta per client
`WHERE client_id = 1 ORDER BY data_comanda`	✅ Ideal, sense `Sort`	❌ No
`ORDER BY client_id, data_comanda`	✅ Sense `Sort`	❌ No
`ORDER BY data_comanda`	❌ No	✅ Sense `Sort`

El cas estrella, sobre les dades del curs:

SELECT co.id, co.data_comanda, co.estat
FROM comandes AS co
WHERE co.client_id = 2
ORDER BY co.data_comanda DESC;

id	data_comanda	estat
11	2025-09-09	lliurat
2	2025-03-12	lliurat

Amb l'índex (client_id, data_comanda), el motor entra al bloc de client_id = 2 i llegeix les dues files ja ordenades, cap enrere: zero comparacions i zero ordenació. (Amb 20 comandes farà un Seq Scan, és clar; el raonament val per al cas gran.)

En quin ordre posar les columnes

És la decisió més important d'un índex compost, i hi ha una regla pràctica que encerta gairebé sempre:

Primer les columnes que es comparen per igualtat; després, la que es compara per rang; i al final, la que només apareix a l'ORDER BY.

El motiu és geomètric. Amb (client_id, data_comanda) i el filtre client_id = 1 AND data_comanda >= '2025-06-01', el motor salta a l'inici de "client 1, juny del 2025" i llegeix un tram contigu de fulles: troba l'única comanda de la Lucía posterior a aquesta data. Amb (data_comanda, client_id) hauria de llegir totes les comandes des del juny del 2025, de tots els clients, i anar descartant. Com abans es tanqui el rang, menys entrades es toquen. Dos corol·laris:

Tan bon punt una columna es compara per rang, les següents deixen de servir per filtrar (només per descartar sense anar a la taula). Per això el rang va l'últim dels filtres.
Un índex sobre (a, b) fa innecessari un índex sobre (a), però no sobre (b). És la base de la caça de redundants de la secció 10.

I un límit pràctic: poques vegades val la pena passar de tres columnes. Cada columna addicional engreixa les entrades, redueix el fanout (08-01) i serveix per a menys consultes.

Índexs parcials

Un índex parcial porta una clàusula WHERE i només indexa les files que la compleixen. Idea senzilla, dos efectes grans: l'índex és més petit —cap millor a la memòria i es recorre abans— i el seu manteniment és més barat, perquè les files excloses no el toquen en inserir-se.

CREATE INDEX idx_productes_categoria_actius ON productes (categoria_id) WHERE actiu;
CREATE INDEX idx_comandes_pendents ON comandes (data_comanda) WHERE estat <> 'lliurat';

La condició de l'índex ha d'implicar la de la consulta perquè el planificador el pugui utilitzar:

-- ✅ Fa servir idx_comandes_pendents: el WHERE inclou la condició de l'índex
SELECT co.id, co.data_comanda, co.estat
FROM comandes AS co
WHERE co.estat <> 'lliurat' AND co.data_comanda >= '2026-01-01';

-- ⚠️ NO el pot fer servir: la consulta no garanteix estat <> 'lliurat'
SELECT co.id FROM comandes AS co WHERE co.data_comanda >= '2026-01-01';

id	data_comanda	estat
17	2026-01-13	enviat
18	2026-01-27	pagat
19	2026-02-09	pagat
20	2026-02-21	pendent

On brillen de debò. A BotigaVerda, estat <> 'lliurat' són 6 comandes de 20 (30 %) i actiu són 19 productes de 20 (95 %): l'estalvi és nul o negatiu. Però aquestes proporcions es desplomen en una botiga real: d'aquí a cinc anys, les comandes no lliurades seran unes 200 de 2.000.000 (0,01 %) i els productes actius uns 3.000 de 40.000 (7,5 %). L'índex parcial de comandes pendents indexaria dues-centes files en lloc de dos milions: cabria sencer a la memòria i respondria en microsegons. És el patró clàssic de les cues de treball —"dóna'm el que falta per processar"— i un dels millors trucs de PostgreSQL. Un altre ús molt freqüent és indexar només el que no és nul: CREATE INDEX idx_comandes_empleat_id ON comandes (empleat_id) WHERE empleat_id IS NOT NULL; s'estalvia la meitat de les entrades, perquè deu de les vint comandes són web (01-06), i continua servint per a totes les consultes per comercial.

Nota de dialecte: els índexs parcials existeixen a PostgreSQL i SQLite amb aquesta mateixa sintaxi, i a SQL Server com a filtered indexes. MySQL no els té en absolut: és una de les mancances que més es noten en migrar-hi.

Índexs sobre expressions

Aquí es tanca l'avís del mòdul 6: una funció sobre la columna filtrada impedeix fer servir l'índex. La solució és indexar el resultat de la funció.

CREATE INDEX idx_clients_email_lower ON clients (LOWER(email));

SELECT c.id, c.nom, c.cognoms, c.email
FROM clients AS c
WHERE LOWER(c.email) = '[email protected]';

id	nom	cognoms	email
1	Lucía	Martínez Soler	[email protected]

I la regla d'or, d'on vénen gairebé tots els disgustos:

L'expressió de l'índex ha de coincidir literalment amb la de la consulta. El planificador compara expressions, no significats.

Índex	Consulta	Serveix?
`(LOWER(email))`	`WHERE LOWER(email) = '...'`	✅ Sí
`(LOWER(email))`	`WHERE email = '...'`	❌ No
`(LOWER(email))`	`WHERE LOWER(TRIM(email)) = '...'`	❌ No: l'expressió és una altra
`(EXTRACT(YEAR FROM data_comanda))`	`WHERE EXTRACT(YEAR FROM data_comanda) = 2025`	✅ Sí
`(EXTRACT(YEAR FROM data_comanda))`	`WHERE data_comanda >= '2025-01-01' AND ...`	❌ No

Aquest últim parell té substància. Les dues consultes retornen les mateixes 16 comandes del 2025, però cadascuna necessita el seu índex, i CREATE INDEX idx_comandes_any ON comandes (EXTRACT(YEAR FROM data_comanda)); només serveix per a preguntes anuals. La solució preferible no és aquesta: és reescriure la consulta com un rang sobre data_comanda i fer servir un índex normal, que servirà a més per a "les comandes del març", "les de l'última setmana" i per a ORDER BY data_comanda. És el criteri de la sargabilitat que desenvolupa 08-04.

Dos requisits tècnics: la funció ha de ser IMMUTABLE —per als mateixos arguments, sempre el mateix resultat; LOWER(text) ho és, NOW() no, i EXTRACT(YEAR FROM ...) sobre un TIMESTAMPTZ tampoc, perquè depèn de la zona horària de la sessió, encara que sobre un DATE com el de BotigaVerda sí que ho és— i es recalcula a cada escriptura, així que encareix els INSERT una mica més que un índex normal.

Nota de dialecte: Oracle els té des de fa dècades (function-based indexes) i SQLite des de la 3.9. SQL Server no els té directament: s'emula amb una columna calculada persistida més un índex sobre ella. MySQL 8 els admet amb parèntesis dobles: CREATE INDEX ... ((LOWER(email))).

`INCLUDE`: índexs cobridors expressament

INCLUDE afegeix columnes a l'índex que no formen part de la clau: no serveixen per buscar ni per ordenar, només es desen a les fulles per poder-les llegir sense anar a la taula. És la manera explícita de fabricar l'índex cobridor de 08-01 i provocar un Index Only Scan.

CREATE INDEX idx_comandes_client_inc ON comandes (client_id) INCLUDE (data_comanda, estat);

-- Les tres columnes viuen a l'índex: en una taula gran, ni es toca el heap
SELECT co.client_id, co.data_comanda, co.estat FROM comandes AS co WHERE co.client_id = 1;

	Columna a la clau: `(a, b)`	Columna a `INCLUDE`: `(a) INCLUDE (b)`
Serveix per filtrar per `b`	✅ Sí (si `a` també hi és)	❌ No
Serveix per a `ORDER BY b`	✅ Sí	❌ No
Evita anar a la taula	✅ Sí	✅ Sí
Mida de les entrades	Més gran a tots els nivells	Més petita: només engreixa les fulles
Compatible amb `UNIQUE`	Canvia el significat del `UNIQUE`	✅ No el canvia

Aquesta última fila és l'ús més elegant: CREATE UNIQUE INDEX ... ON clients (email) INCLUDE (nom, cognoms) continua garantint "un email, un client" i a més resol "com es diu el propietari d'aquest email?" sense tocar la taula. INCLUDE existeix des de PostgreSQL 11, i també a SQL Server i Oracle; MySQL no el necessita igual, perquè el seu índex primari és agrupat.

Ordre i nuls dins de l'índex

Per omissió un índex B-tree es construeix ASC NULLS LAST, igual que l'ORDER BY del mòdul 2. I aquí convé estalviar-se feina: per a un ORDER BY d'una sola columna no cal declarar res, perquè PostgreSQL pot recórrer qualsevol índex cap enrere. Un índex (data_comanda) serveix igual per a ORDER BY data_comanda que per a ORDER BY data_comanda DESC.

L'ordre explícit només importa quan l'ORDER BY barreja direccions, perquè llavors cap lectura de l'índex, ni cap endavant ni cap enrere, produeix aquest ordre:

-- Un índex (estat, data_comanda) NO evita el Sort d'aquesta consulta
SELECT co.id, co.estat, co.data_comanda FROM comandes AS co
ORDER BY co.estat ASC, co.data_comanda DESC;

-- Aquest sí que l'evita
CREATE INDEX idx_comandes_estat_data_mix ON comandes (estat ASC, data_comanda DESC);

El mateix amb els nuls: si els teus informes fan ORDER BY empleat_id NULLS FIRST sobre les deu comandes web de BotigaVerda, un índex (empleat_id NULLS FIRST) evita l'ordenació; l'índex per omissió, no.

Gestió: llistar, mesurar, reanomenar, reconstruir i esborrar

A psql, \di llista tots els índexs i \d+ comandes els d'una taula concreta amb la seva definició; des de SQL, la vista pg_indexes de 08-01. Per mesurar:

SELECT relname                                       AS taula,
       pg_size_pretty(pg_relation_size(relid))       AS dades,
       pg_size_pretty(pg_indexes_size(relid))        AS indexs,
       pg_size_pretty(pg_total_relation_size(relid)) AS total
FROM pg_catalog.pg_statio_user_tables ORDER BY pg_total_relation_size(relid) DESC LIMIT 5;

taula	dades	indexs	total
comandes	8192 bytes	96 kB	112 kB
productes	8192 bytes	48 kB	64 kB
clients	8192 bytes	48 kB	64 kB
linies_comanda	8192 bytes	48 kB	56 kB
ressenyes	8192 bytes	32 kB	48 kB

(Una execució d'exemple després de crear els índexs d'aquesta lliçó; el total inclou a més les estructures auxiliars de cada taula.) La lectura és perfectament real: els índexs ocupen més de deu vegades el que ocupen les dades. Amb 20 files per taula és una anècdota; en una base de dades madura, que els índexs pesin més que les dades és un senyal d'alarma que cal investigar amb la secció 10.

ALTER INDEX idx_comandes_client_id RENAME TO idx_comandes_client;
REINDEX INDEX CONCURRENTLY idx_comandes_client;
DROP INDEX IF EXISTS idx_comandes_estat_data_mix;

REINDEX reconstrueix l'índex des de zero. S'utilitza en tres situacions: índex corromput, índex que ha crescut de més per acumulació d'espai mort (el bloat de 08-05), o canvi de la configuració regional que afecta l'ordre del text. En el dia a dia no cal reindexar per rutina: PostgreSQL manté els arbres equilibrats pel seu compte.

Esborrar un índex és instantani i no toca les dades; un DROP INDEX normal bloqueja la taula un instant i CONCURRENTLY ho evita. El que no pots esborrar així és un índex que dóna suport a una restricció:

ERROR:  cannot drop index clients_email_key because constraint clients_email_key on table clients requires it
HINT:  You can drop constraint clients_email_key on table clients instead.

La via correcta és ALTER TABLE clients DROP CONSTRAINT clients_email_key;, que s'emporta restricció i índex alhora.

Índexs no utilitzats i redundants

En una base de dades amb anys de vida els índexs s'acumulen: un per a un informe que ja no existeix, un altre per a una consulta que es va reescriure, un tercer "per si de cas". Tots continuen cobrant el seu peatge a cada escriptura.

SELECT relname AS taula, indexrelname AS index, idx_scan AS vegades_usat,
       pg_size_pretty(pg_relation_size(indexrelid)) AS mida
FROM pg_stat_user_indexes WHERE schemaname = 'public'
ORDER BY idx_scan, pg_relation_size(indexrelid) DESC;

taula	index	vegades_usat	mida
comandes	idx_comandes_any	0	16 kB
productes	idx_productes_proveidor_id	0	16 kB
comandes	idx_comandes_client_data	0	16 kB
clients	clients_pkey	3	16 kB

(Una execució d'exemple: a BotigaVerda surten zeros perquè amb 20 files el planificador tria Seq Scan — 08-05.) Un idx_scan = 0 és un candidat a esborrar, amb quatre cauteles:

Els comptadors s'acumulen des de l'últim reinici d'estadístiques. Un índex utilitzat només pel tancament anual semblarà inútil al març.
Un índex que dóna suport a un UNIQUE o a una PK no s'esborra, encara que no s'utilitzi per buscar: està garantint una restricció.
Els índexs de claus foranes poden marcar 0 i ser imprescindibles: les comprovacions d'integritat referencial no sempre sumen al comptador.
En una rèplica de només lectura els comptadors són diferents. Revisa'ls tots dos.

I de la regla del prefix per l'esquerra se'n dedueix l'altre problema: si existeix un índex sobre (a, b), un sobre (a) és redundant.

Parella d'índexs	És redundant?
`(client_id)` i `(client_id, data_comanda)`	✅ Sobra el primer
`(data_comanda)` i `(client_id, data_comanda)`	❌ No: el segon no serveix per filtrar només per data
`(client_id)` i `(client_id) INCLUDE (estat)`	✅ Sobra el primer
`(client_id, data_comanda)` i `(data_comanda, client_id)`	❌ No: serveixen per a consultes diferents

El matís honest: l'índex curt és més petit i, si la teva consulta més freqüent només filtra per client_id, serà marginalment més ràpid. Gairebé mai no compensa mantenir-los tots dos.

Errors habituals i consells

Fer un CREATE INDEX normal en producció. Bloqueja les escriptures durant tota la construcció. CONCURRENTLY existeix justament per a això.
No comprovar que un CONCURRENTLY ha acabat bé. Un índex INVALID és el pitjor dels dos mons: costa mantenir-lo i no el fa servir ningú.
Posar les columnes de l'índex compost en l'ordre en què apareixen al WHERE. L'ordre el mana la regla igualtat → rang, no l'ordre en què vas escriure les condicions.
Crear (a) i (a, b). El primer sobra. És l'índex redundant més freqüent que existeix.
Crear un índex d'expressió que no coincideix amb la consulta. LOWER(email) no val per a LOWER(TRIM(email)). Copia i enganxa l'expressió des de la consulta.
Fer servir un índex d'expressió sobre l'any en lloc de reescriure la consulta com un rang. L'índex del rang serveix per a moltes més preguntes.
Crear a mà l'índex d'una clau UNIQUE. Declara la restricció; l'índex ve inclòs i continua associat a ella.
Consell: crea els índexs de les claus foranes d'entrada. És el conjunt d'índexs amb millor relació benefici/risc de qualsevol esquema PostgreSQL.
Consell: revisa pg_stat_user_indexes un cop per trimestre. Esborrar tres índexs morts pot accelerar les escriptures més que qualsevol optimització de consulta. I posa nom als índexs des del primer dia: un pla ple de comandes_client_id_data_comanda_idx1 és un pla que no llegeix ningú.

Exercicis

Exercici 1

L'aplicació de BotigaVerda executa aquestes tres consultes a la pantalla de fitxa de client:

-- a) Comandes del client, de la més recent a la més antiga
SELECT id, data_comanda, estat FROM comandes WHERE client_id = ? ORDER BY data_comanda DESC;
-- b) Comandes del client en un rang de dates
SELECT id, estat FROM comandes WHERE client_id = ? AND data_comanda BETWEEN ? AND ?;
-- c) Comandes pendents de tots els clients, per antiguitat
SELECT id, client_id FROM comandes WHERE estat = 'pendent' ORDER BY data_comanda;

Dissenya el conjunt mínim d'índexs que les cobreixi bé, escriu els CREATE INDEX i justifica l'ordre de les columnes de cadascun.

Exercici 2

Un equip ha acumulat aquests cinc índexs sobre linies_comanda:

CREATE INDEX idx_lc_1 ON linies_comanda (comanda_id);
CREATE INDEX idx_lc_2 ON linies_comanda (comanda_id, producte_id);
CREATE INDEX idx_lc_3 ON linies_comanda (producte_id);
CREATE INDEX idx_lc_4 ON linies_comanda (producte_id) INCLUDE (quantitat, preu_unitari);
CREATE INDEX idx_lc_5 ON linies_comanda (quantitat);

Quins són redundants i per què?
Quin és gairebé segur inútil, i quina consulta caldria veure per confirmar-ho?
Deixa el conjunt en dos índexs i escriu els DROP INDEX corresponents.

Exercici 3

Escriu els índexs que resolen aquestes tres situacions i explica en una frase per què tries aquesta variant:

L'aplicació busca clients escrivint l'email sense distingir majúscules i minúscules.
El panell d'administració mostra contínuament les comandes encara no lliurades, que en producció són unes 200 de 2 milions.
Un informe llista client_id, data_comanda i despeses_enviament d'un client concret, i vols que no toqui la taula.

Solucions

Solució 1

CREATE INDEX idx_comandes_client_data ON comandes (client_id, data_comanda);
CREATE INDEX idx_comandes_pendents    ON comandes (data_comanda) WHERE estat = 'pendent';

El primer cobreix a) i b). client_id va primer perquè es compara per igualtat, i data_comanda després perquè és el rang i a més el criteri d'ordenació: el motor llegeix un tram contigu i ja ordenat, sense Sort. I com que ORDER BY data_comanda DESC es resol recorrent l'índex cap enrere, no cal declarar DESC.

El segon cobreix c). Es podria fer amb un índex normal sobre (estat, data_comanda), però el parcial és molt millor: a BotigaVerda avui indexaria 1 fila de 20, i en producció unes 200 de 2 milions. La columna indexada és data_comanda perquè estat ja està fixat per la condició de l'índex: repetir-la seria malgastar espai. No cal un índex solt sobre client_id: seria redundant amb el primer.

Solució 2

1. Redundants: idx_lc_1 sobra perquè idx_lc_2 comença per comanda_id i cobreix tot el que ell resol. idx_lc_3 sobra perquè idx_lc_4 té la mateixa clau (producte_id) i a més inclou dues columnes per evitar l'accés a la taula.

2. Gairebé segur inútil: idx_lc_5, sobre quantitat. És una columna de baixa cardinalitat (valors d'1 a 8 a les 47 línies) i ningú no busca línies "per quantitat": és una dada que es mostra i se suma, no per la qual es filtra. Per confirmar-ho, pg_stat_user_indexes i el seu idx_scan, amb les quatre cauteles de la secció 10.

3. DROP INDEX idx_lc_1; DROP INDEX idx_lc_3; DROP INDEX idx_lc_5;. Queden idx_lc_2 sobre (comanda_id, producte_id), que serveix per a "les línies d'aquesta comanda" i per a "aquest producte dins d'aquesta comanda", i idx_lc_4 sobre (producte_id) INCLUDE (quantitat, preu_unitari), que resol "totes les vendes d'aquest producte" amb Index Only Scan. Les dues claus foranes queden cobertes, que era l'objectiu de partida.

Solució 3

-- 1. Índex d'expressió: la consulta filtra per LOWER(email), no per email
CREATE INDEX idx_clients_email_lower ON clients (LOWER(email));

-- 2. Índex parcial: indexa 200 files en lloc de 2.000.000
CREATE INDEX idx_comandes_no_lliurades ON comandes (data_comanda) WHERE estat <> 'lliurat';

-- 3. Índex cobridor amb INCLUDE: les tres columnes viuen a l'índex
CREATE INDEX idx_comandes_client_inc ON comandes (client_id) INCLUDE (data_comanda, despeses_enviament);

D'expressió, perquè l'índex ha de contenir exactament el que compara la consulta. Compte amb la regla d'or: si l'aplicació passa a fer servir ILIKE o a normalitzar amb TRIM, aquest índex deixa de servir sense que ningú se n'assabenti.
Parcial, perquè la condició és constant i minoritària: l'índex cap a la memòria i no es toca en inserir les comandes que ja neixen lliurades.
INCLUDE en lloc de (client_id, data_comanda, despeses_enviament), perquè despeses_enviament no s'utilitza per filtrar ni per ordenar: posant-la a INCLUDE només s'engreixen les fulles i no els nivells superiors, i l'arbre conserva el seu fanout.

Conclusió

Ja saps construir índexs, no només demanar-los:

CREATE INDEX té vuit peces, i les importants són CONCURRENTLY, la llista de columnes, INCLUDE i WHERE. El mètode per omissió és btree.
CONCURRENTLY és obligatori en producció: un CREATE INDEX normal bloqueja les escriptures. A canvi és més lent i pot deixar un índex INVALID que cal esborrar i refer. El model de bloquejos que hi ha al darrere és el mòdul 9.
Un índex compost serveix per als prefixos per l'esquerra de la seva llista de columnes. L'ordre es decideix amb una regla: igualtat primer, rang després, ordenació al final. I (a, b) fa redundant (a).
Els parcials indexen només un subconjunt de files: minúsculs, ràpids i barats de mantenir. Són l'eina ideal per a les cues de treball i per excloure nuls.
Els d'expressió tanquen l'avís del mòdul 6, amb una regla d'or que no admet matisos: l'expressió de l'índex ha de coincidir literalment amb la de la consulta, i la funció ha de ser IMMUTABLE.
INCLUDE fabrica índexs cobridors sense engreixar la clau, i és l'única manera d'afegir columnes a un índex UNIQUE sense canviar el que garanteix.
Gestionar és tan important com crear: \di i pg_indexes per llistar, pg_relation_size per mesurar, REINDEX per reconstruir, DROP INDEX per esborrar — i pg_stat_user_indexes per descobrir els que no fa servir ningú i la regla del prefix per descobrir els redundants.

Amb això saps crear qualsevol índex B-tree que necessitis. Però el B-tree no és l'únic: PostgreSQL ofereix sis mètodes d'accés, i hi ha problemes —LIKE '%text%', JSONB, rangs de dates, taules històriques de milers de milions de files— que un B-tree no resol bé o no resol en absolut. A la lliçó 08-03, Tipus d'índex i quan no indexar, veuràs la taula comparativa dels sis mètodes, tancaràs la promesa de pg_trgm que 04-01 va deixar pendent i —la part més important i la que menys s'ensenya— aprendràs quan un índex no serveix o fa nosa: taules petites, columnes de baixa cardinalitat, filtres poc selectius, taules d'escriptura intensiva, i l'antipatró d'indexar-ho tot per si de cas.

Creació i gestió d'índexs

Contingut

`CREATE INDEX`: la sintaxi completa i com anomenar-los

Els índexs que li falten a BotigaVerda

`CREATE INDEX CONCURRENTLY`

Índexs compostos i la regla del prefix per l'esquerra

En quin ordre posar les columnes

Índexs parcials

Índexs sobre expressions

`INCLUDE`: índexs cobridors expressament

Ordre i nuls dins de l'índex

Gestió: llistar, mesurar, reanomenar, reconstruir i esborrar

Índexs no utilitzats i redundants

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

Creació i gestió d'índexs

Contingut

CREATE INDEX: la sintaxi completa i com anomenar-los

Els índexs que li falten a BotigaVerda

CREATE INDEX CONCURRENTLY

Índexs compostos i la regla del prefix per l'esquerra

En quin ordre posar les columnes

Índexs parcials

Índexs sobre expressions

INCLUDE: índexs cobridors expressament

Ordre i nuls dins de l'índex

Gestió: llistar, mesurar, reanomenar, reconstruir i esborrar

Índexs no utilitzats i redundants

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

`CREATE INDEX`: la sintaxi completa i com anomenar-los

`CREATE INDEX CONCURRENTLY`

`INCLUDE`: índexs cobridors expressament